总结

一、建表的时候添加
– 1.alter…add…
– 2.alter…modify…
– 3.alter…drop…

二、使用alter修改表结构

alter table user add primary key(id);
alter table user drop primary key;

三、使用modify修改字段，添加约束

mysql> alter table user modify id varchar(20) primary key;
Query OK, 1 row affected (1.48 sec)
Records: 1  Duplicates: 0  Warnings: 0

索引：帮助mysql高效获取数据的一种数据结构

优点：检索块，降低IO成本；排序快，降低CPU消耗

缺点：降低表更新速度；占用空间：索引也是一张保存主键和索引字段（实体表记录）的表

1.何时需要建立索引？

 主键→唯一
 频繁查询某一字段
 外键关系
 单键/组合索引→组合索引
 查询中排序的字段
 查询中统计分组的字段

2.何时不需要建立索引？

 记录太少
 增删改表字段
 where条件用不到的字段
 过滤性不好的（例如性别只有，1，0反例：身份证号）

一、主键约束(primary key)

1.单一主键：[字段] primary key
2.联合主键：primary key([字段],[字段])

能够唯一确定一张表中的一条记录，给某个字段添加约束，使该字段’不重复且不为空’。
联合主键：主键值加起来不重复就可以，任一不为空。

二、自增约束(primary key auto_increment)

对整型数据进行自增。

mysql> create table user(
    ->  id int primary key auto_increment,
    ->  name  varchar(20) not null,
    ->  class_id varchar(20));
Query OK, 0 rows affected (1.13 sec)

三、唯一约束unique(id,name)

这两个组合不重复就好，约束修饰的字段的值不可以重复，若为空值则可以重复。

 alter table user add unique(name);

– 删除唯一约束

alter table user drop index name;

– 添加唯一约束

alter table user modify name varchar unique;.

四、非空约束(not null)

修饰的字段不能为空

mysql> create table user(
    ->  id int primary key auto_increment,
    ->  name  varchar(20) not null,
    ->  class_id varchar(20));
Query OK, 0 rows affected (1.13 sec)

五、默认约束default [值]

插入字段值时候，若没有插入值则使用默认值

mysql> alter table user modify name varchar(20) default '张三';
Query OK, 0 rows affected (1.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

六、外键约束(foreign key([字段]) references [父表[字段]])

涉及到两个表：父表classes与子表students，添加外键后若子表中包含已经引用的父表的数据，则不能删除父表中对应的值。

1.查看两表结构，父表class中的id是子表的外键，id<–>class_id：

mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.向两表分别插入数据

mysql> insert into classes values(1,'一班');
Query OK, 1 row affected (0.05 sec)

mysql>  insert into classes values(2,'二班');
Query OK, 1 row affected (0.07 sec)

mysql>  insert into classes values(3,'三班');
Query OK, 1 row affected (0.12 sec)

mysql>  insert into students values(1001,'张三',1);
Query OK, 1 row affected (0.19 sec)

mysql>  insert into students values(1002,'张三',2);
Query OK, 1 row affected (0.05 sec)

mysql>  insert into students values(1003,'张三',3);
Query OK, 1 row affected (0.10 sec)

mysql>  insert into students values(1004,'张三',4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

3.查看表数据：

mysql> select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
|  3 | 三班   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 张三   |        1 |
| 1002 | 张三   |        2 |
| 1003 | 张三   |        3 |
+------+--------+----------+
3 rows in set (0.00 sec)

4.尝试删除父表id=3的班级，此时子表students包含class_id=3的数据，失败：

mysql> delete from classes where id =3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`t`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

5.将子表class_id=3的学生数据删除，此时子表students不包含class_id=3的数据，再尝试删除父表id=3的班级，成功：

mysql> delete from students where class_id=3;
Query OK, 1 row affected (0.10 sec)

mysql> delete from classes where id=3;
Query OK, 1 row affected (0.19 sec)

6.查看数据

mysql> select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
+----+--------+
2 rows in set (0.00 sec)

注：整理自 https://www.bilibili.com/video/BV1Vt411z7wy?p=8