MySQL数据库—表的基本操作

1. 表的创建

表的创建命令需要：

1. 表的名称
2. 字段名称
3. 定义每个字段(类型、长度等)

格式如下：

CREATE TABLE table_name (column_name column_type);

例如：

mysql> create table student(
    ->     id int not null auto_increment primary key,
    ->     sex char(2),
    ->     age int,
    ->     name char(20),
    ->     birthday date
    -> );
Query OK, 0 rows affected (0.04 sec)

注意：

1. 字段使用not null属性，是因为我们不希望这个字段的值为NULL。 因此，如果用户将尝试创建具有NULL值的记录，那么MySQL会产生错误。
2. 字段的auto_increment属性告诉MySQL自动增加id字段下一个可用编号。
3. 关键字primary key用于定义此列作为主键。可以使用逗号分隔多个列来定义主键。

2. 查看表的结构

有以下两种方法：

方法一：

mysql> desc student;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| sex      | char(2)  | YES  |     | NULL    |                |
| age      | int(11)  | YES  |     | NULL    |                |
| name     | char(20) | YES  |     | NULL    |                |
| birthday | date     | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

方法二：

mysql> show create table student;
+---------+-----------------------------------------------+
| Table   | Create Table                                  |
+---------+-----------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` char(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------+
1 row in set (0.00 sec)

3. 修改字段属性

格式：

alter table 表名 modify 列名 属性;

例：将name属性改为非空

mysql> alter table student modify name char(20) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. 添加一列

格式：

alter table 表名 add column 列名 属性;

使用上述可以在一个已经建好的表中添加一列

例：在student表添加一列自我描述info

mysql> alter table student add info varchar(100);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

5. 删除一列

格式：

alter table 表名 drop column 列名;

例如：删除Info列

mysql> alter table Student drop info;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 修改列名

格式：

alter table 表名 change column 列名 新列名 属性;

例如：

mysql> alter table student change name stuname varchar(20);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

当列名不变时，可以修改列的属性，效果与使用modify相同

mysql> alter table student change name name varchar(25);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

7. 修改表名

格式：

alter table 表名 rename 新表名;

例如：将student表表名改为Student

mysql> alter table student rename Student;
Query OK, 0 rows affected (0.02 sec)

8. 添加索引

格式：

alter table 表名 add indesx(列名);

例如：为name添加一个索引

mysql> alter table Student add index(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

9. 查看索引

格式：

show index from 表名;

例如：查看Student表中的索引

mysql> show index from Student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| Student |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

10. 增加外键约束

添加外键约束有两种方法，一种是直接在建表的时候就增加外键约束，另一种是使用alter语句增加

(1) 建表时加外键

mysql> create table class(
    -> id int not null primary key auto_increment,
    -> naem char(20));
Query OK, 0 rows affected (0.02 sec)

mysql> create table student(
    -> id int not null primary key auto_increment,
    -> name char(20),
    -> sex char(2),
    -> class_id int,
    -> foreign key(class_id) references class(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

(2) 使用alter语句添加外键约束

格式：

alter table 从表 add constraint 外键名称 foreign key 从表(外键字段) references 主表(主表字段);

例1：默认索引名（student_ibfk_1）

mysql> alter table student add foreign key (class_id) references class(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

例2：重命名索引名

mysql> alter table student add constraint stu_class foreign key(class_id) references class(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

11. 查看外键约束

mysql> show create table student;
+---------+---------------------------------------------------------------------------+
| Table   | Create Table                                                           	  |
+---------+---------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `stu_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------=---------+
1 row in set (0.00 sec)

12. 删除外键

mysql> alter table student drop foreign key stu_class;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0