B站 MySQL 陈长宏老师讲课笔记day2

标签：name dep MySQL day2 stu sec mysql 陈长宏 id

1.DQL查询语句的使用

1）排序查询

语法：order by 字句 order by 排序字段1 排序方式1，排序字段2 排序方式2...... 排序方式： ASC：升序，默认的 DESC：降序注意：如果有多个排序条件，则当前面的条件值一样时，才会判断第二条件按照数学成绩排名（默认升序） SELECT * FROM student ORDER BY math; SELECT * FROM student ORDER BY math ASC; 按照数学成绩降序排名 SELECT * FROM student ORDER BY math DESC; 按照数学成绩升序排名，数学成绩一样的，按照英语成绩的降序排名 SELECT * FROM student ORDER BY math ASC , english DESC;

2）聚合查询

将一列数据作为一个整体，进行纵向的计算。 count：计算个数一般选择非空的列：选择主键 count（*）：只要这一列数据有一个不为null，就能够算一个记录 max：计算最大值 min：计算最小值 sum：计算和 avg：计算平均值注意：聚合函数的计算，会排除非null值解决方案：1.选择不包含非空的列进行计算。 2.使用IFNULL函数。计算学生总人数 SELECT COUNT(NAME) FROM student; 计算英语学科人数（非空被排除） SELECT COUNT(english) FROM student; 解决办法： SELECT COUNT(IFNULL(english,0)) FROM student; 计算数学成绩的最大值 SELECT MAX(math) FROM student; 计算数学成绩的最小值 SELECT MIN(math) FROM student; 计算数学成绩的和 SELECT SUM(math) FROM student; 计算数学和英语的总和 SELECT SUM(math+english) FROM student; 计算数学的平均分 SELECT AVG(math) FROM student;

3）分组查询

语法：group by 分组字段; 注意： 1.分组之后只能查询的字段：分组字段、聚合函数 2.where和having的区别 1.where 在分组之前限定，如果不满足条件，则不参与分组，having在分组之后进行限定，如果不满足条件，则不会被查询出来 2.where后不可以跟聚合函数，having可以进行聚合函数的判断按照性别分组，分别查询男、女同学的平均分。 SELECT sex ,AVG(math) FROM student GROUP BY sex; 按照性别分组，分别查询男、女同学数学的平均分，人数 SELECT sex ,AVG(math),COUNT(id) FROM student GROUP BY sex; 按照性别分组，分别查询男、女同学数学的平均分、人数，要求分数低于70分的人不参与分组 SELECT sex ,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex; 按照性别分组，分别查询男、女同学数学的平均分，人数，要求，分数低于70分的人不参与分组，分组后人数大于两个（分完组后对结果集，再限定一次） SELECT sex ,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; 按照性别分组，分别查询男、女同学数学的平均分，人数，要求，分数低于70分的人不参与分组，分组后人数大于两个，人数是起别名方式，AS省略 SELECT sex ,AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

4）分页查询

1.语法：limit 开始的索引，每页查询的条数 2.公式：开始的索引 =（想看的页码 - 1）* 每页显示的条数每页显示三条记录，查看第一页内容 SELECT * FROM student LIMIT 0,3; 查看第二页内容 SELECT * FROM student LIMIT 3,3; 3.limit语法是一个MySQL “方言”，既limit只在MySQL中是分页的语法。

2.约束

1）概念：

对表中的数据进行限定，从而保证数据的正确性、有效性和完整性例如上面的学生表写入数据可以不写姓名，而写入其他内容，但是查询时是没有此人的，要对此进行约束约束分类: 主键约束：primary key 非空约束：not null 唯一约束：unique 外键约束：foreign key

2）非空约束：not null

1.创建表时添加非空约束 CREATE TABLE stu ( id INT, NAME VARCHAR(20) NOT NULL -- name为非空约束 );

mysql> INSERT INTO stu(id,name) VALUES(1,'zhangsan');# 向表中写入数据
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;# 正常写入
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> INSERT INTO stu(id) VALUES(2);# 当向表中写入id数据而不写入name时报错
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> select * from stu;# 数据没有写入
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

2.删除非空约束

ALTER TABLE 表名 MODIFY 列号类型;

mysql> ALTER TABLE stu MODIFY NAME VARCHAR(20);# 修改约束
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> INSERT INTO stu(id) VALUES(2);# 写入数据只有id未报警
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu;# 写入到表中
+------+----------+
| id   | NAME     |
+------+----------+
|    1 | zhangsan |
|    2 | NULL     |
+------+----------+
2 rows in set (0.00 sec)
mysql> desc stu;# 查看规则，name列是可以为空的
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| NAME  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.创建表完后添加非空约束

mysql> ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;# 对name添加约束条件
ERROR 1265 (01000): Data truncated for column 'NAME' at row 2
mysql> select * from stu;# 报错是因为将要约束非空的name列有一空行
+------+----------+
| id   | NAME     |
+------+----------+
|    1 | zhangsan |
|    2 | NULL     |
+------+----------+
3 rows in set (0.00 sec)
mysql> DELETE FROM stu WHERE id=2;# 删除name列为空的行
Query OK, 2 rows affected (0.01 sec)
mysql> select * from stu;
+------+----------+
| id   | NAME     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;# 再次添加约束规则，添加成功
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc stu;# 查看规则，name列不能为空
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| NAME  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3）唯一约束：unique

1.创建表时添加唯一约束 CREATE TABLE stu( id int, phone_number varchar(20) unique );

mysql> desc stu;# 唯一约束下面phone_number有显示
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | YES  |     | NULL    |       |
| phone_number | varchar(20) | YES  | UNI | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> INSERT INTO stu(id,phone_number) VALUES(1,1111);# 写入一条数据
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO stu(id,phone_number) VALUES(2,1111);# 再写入一行数据phone_number相同，出现唯一报错
ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number'
mysql> SELECT * FROM STU;# 第二次数据也没有写入
+------+--------------+
| id   | phone_number |
+------+--------------+
|    1 | 1111         |
+------+--------------+
1 row in set (0.00 sec)
mysql> INSERT INTO stu(id,phone_number) VALUES(2,1112);# 把phone_number号更改后可以写入
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM STU;
+------+--------------+
| id   | phone_number |
+------+--------------+
|    1 | 1111         |
|    2 | 1112         |
+------+--------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO stu(id) VALUES(3);# id为3，没有phone_number
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO stu(id) VALUES(4);# id为4，也没有phone_number，但可以写入，可以理解为null代表着不确定性，两个null都不确定，互相之间不重复，所以与唯一性不冲突
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM STU;
+------+--------------+
| id   | phone_number |
+------+--------------+
|    1 | 1111         |
|    2 | 1112         |
|    3 | NULL         |
|    4 | NULL         |
+------+--------------+
4 rows in set (0.00 sec)
mysql> desc stu;# 唯一约束被删除
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | YES  |     | NULL    |       |
| phone_number | varchar(20) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

2.删除唯一约束（唯一约束也叫索引约束）

ALTER TABLE 表名 drop index 列号;

mysql> ALTER TABLE stu MODIFY phone_number VARCHAR(20);# 使用非空约束的修改方式，显示成功了
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> INSERT INTO stu(id,phone_number) VALUES(5,1111);# 但是写入数据，仍然出现报错
ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number'
mysql> SELECT * FROM STU;# 未写入数据
+------+--------------+
| id   | phone_number |
+------+--------------+
|    1 | 1111         |
|    2 | 1112         |
|    3 | NULL         |
|    4 | NULL         |
+------+--------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE stu drop index phone_number;# 删除索引约束，显示成功
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> INSERT INTO stu(id,phone_number) VALUES(5,1111);# 重复数据可以写入，唯一约束被删除
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM STU;
+------+--------------+
| id   | phone_number |
+------+--------------+
|    1 | 1111         |
|    2 | 1112         |
|    3 | NULL         |
|    4 | NULL         |
|    5 | 1111         |
+------+--------------+
5 rows in set (0.00 sec)

3.创建表完后添加唯一约束

mysql> ALTER TABLE stu MODIFY phone_number VARCHAR(20) unique;# 添加规则，发现报错，与非空约束一样，原表中有重复数据111
ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number'
mysql> SELECT * FROM STU;
+------+--------------+
| id   | phone_number |
+------+--------------+
|    1 | 1111         |
|    2 | 1112         |
|    3 | NULL         |
|    4 | NULL         |
|    5 | 1111         |
+------+--------------+
5 rows in set (0.00 sec)
mysql> DELETE FROM stu WHERE id=5;# 删除重复数据
Query OK, 1 row affected (0.01 sec)
mysql> ALTER TABLE stu MODIFY phone_number VARCHAR(20) unique;# 创建表后添加唯一约束
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> INSERT INTO stu(id,phone_number) VALUES(5,1111);# 测试，重复数据无法写入，唯一约束添加成功
ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number'

4）主键约束：primary key

1.注意： 1.含义：非空且唯一（上面非空约束唯一约束的集合） 2.一张表只能有一个字段为主键 3.主键就是表中的唯一标识 2.在创建表时，添加主键约束 CREATE TABLE stu( id int primary key ,-- 给ID添加主键约束 name varchar(20) );

mysql> use db1
Database changed
mysql>
mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE stu(id int primary key, -- 给ID添加主键约束
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT stu(id,name) VALUES(1,'zhangsan');# 添加一条数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu;# 数据添加成功
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
mysql> INSERT stu(id,name) VALUES(1,'lisi');# 测试id是否唯一约束，id=1已有，添加不成功

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> INSERT stu(name) VALUES('wangwu');# 测试id是否非空约束，id不得为空，添加不成功
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> INSERT stu(id,name) VALUES(5,'maliu');# 唯一的id，并且不为空可以添加成功
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  5 | maliu    |
+----+----------+
2 rows in set (0.00 sec)
mysql> desc stu;# 查看key约束为PRI
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.删除主键约束

ALTER TABLE 表名 DROP primary key;

mysql> ALTER TABLE stu MODIFY id int;# MODIFY连唯一约束都删除不了，自然更删除不了主键约束
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> INSERT stu(id,name) VALUES(1,'lisi');# 仍然无法写入数据
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> desc stu;# 主键约束依然存在
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE stu DROP primary key;# 删除不需要指定列名，是因为主键约束在一张表中只能有一个，所以不用指定列名
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> INSERT stu(id,name) VALUES(1,'lisi');# 可以写入重复数据
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM stu;# ID=1为重复数据
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  5 | maliu    |
|  1 | lisi     |
+----+----------+
3 rows in set (0.00 sec)
mysql> desc stu;# 主键约束已被删除
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

4.创建完表后添加主键约束

mysql> SELECT * FROM stu;# 原表数据
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  5 | maliu    |
|  1 | lisi     |
+----+----------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE stu MODIFY id int primary key;# 添加主键约束，发现添加不成功，因为id不唯一
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> delete from stu where name='lisi';# 删除其中一个id，使id=1，成为唯一值
Query OK, 1 row affected (0.00 sec)
mysql> INSERT stu(id,name) VALUES(1,'lisi');# 添加id=1的值，无法添加
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> desc stu;# 主键约束又添加成功
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

5.自动增长auto_increment

1.概念：如果某一列是数值类型的，使用auto_increment 可以来完成自动增长比如输入id，让他完成自增长，而不是每次都去查询，然后写入，并且自增长只和上一条数据有关系。 2.在创建表时，添加主键约束，并且完成主键自增长 CREATE TABLE stu(id int primary key auto_increment,name varchar(20) ); -- 给ID添加主键约束以及自增长

mysql> CREATE TABLE stu(id int primary key auto_increment,name varchar(20) );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT stu(id,name) VALUES(1,'zhangsan');# 添加第一行数据
Query OK, 1 row affected (0.01 sec)
mysql> INSERT stu(id,name) VALUES(null,'zhangsan');# 第二行不输入id，未报错
Query OK, 1 row affected (0.01 sec)
mysql> INSERT stu(id,name) VALUES(null,'lisi');# 第三行也为输入id
Query OK, 1 row affected (0.01 sec)
mysql> INSERT stu(id,name) VALUES(9,'wangwu');# 直接输入id=9
Query OK, 1 row affected (0.01 sec)
mysql> INSERT stu(name) VALUES('liiuliu');# 直接输入数据，未写id，未写null
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;# null和不写，都会自动自增1生成ID号，自增是在上一个id基础上自增的。
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | lisi     |
|  9 | wangwu   |
| 10 | liiuliu  |
+----+----------+
5 rows in set (0.00 sec)

3.删除自增长，再删除主键约束

ALTER TABLE stu MODIFY id int; ALTER TABLE stu DROP primary key; 4.创建完表后添加自增长 ALTER TABLE stu MODIFY id int auto_increment; # 一般自增长和主键约束一起使用

5）外键约束：foreign key

1.外键约束介绍

CREATE TABLE emp ( -- 创建 emp 表
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    dep_name VARCHAR(30), -- 部门名称
    dep_location VARCHAR(30) -- 部门地址
);
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('张三',20,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('李四',21,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('王五',20,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('老王',20,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('大王',22,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('小王',18,'销售部','深圳');

上表中发现重复的数据有很多，比如广州、深圳，我们将重复的数据称为冗余数据

当要修改部门或者地点一条数据的时候，要将所有冗余数据都修改，非常不方便解决方法：做一个表的拆分，一张表存放员工的信息，另外一张表专门存放部门的信息，然后让员工表和部门表相关联创建部门表(id,dep_name,dep_location) 一方，主表

CREATE TABLE department ( 
    id INT PRIMARY KEY AUTO_INCREMENT,
    dep_name VARCHAR(20),
    dep_location VARCHAR(30)
);

创建员工表(id,name,dep_id)

多方，从表

CREATE TABLE employee ( 
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age int,
    dep_id INT -- 外键对应主表的主键
);

添加两个部门

INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');

添加员工，dep_id 表示员工所在部门

INSERT INTO employee (NAME,age,dep_id) VALUES('张三',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('李四',21,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('王五',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('老王',20,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('大王',22,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('小王',18,2);

SELECT * FROM employee;
SELECT * FROM department;

我们可以通过两个表知道对应信息，但是当开发项目完毕后，要删除开发部门时，直接就可以删除，张三李四王五还在开发部门，但开发部删除后已经看不到他们属于哪个部门了，所以我们要设定先把这个部门的人删除或转移后，才能删除部门，这就需要用到外键约束了。

2.外键约束：

foreign key：让表与表产生关系，从而保证数据的正确性。 1.在创建表时，可以添加外键语法:

create table 表名(
                ...... # 内容
                外键列 
                constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
                         );

先删除原先的表

DROP TABLE employee;
DROP TABLE emp;
DROP TABLE department;

CREATE TABLE department (
       id INT PRIMARY KEY AUTO_INCREMENT,
       dep_name VARCHAR(20),
       dep_location VARCHAR(30)
                        );
# 因为外键约束是employee表添加到department表中，所以要先创建department表
CREATE TABLE employee (
       id INT PRIMARY KEY AUTO_INCREMENT,
       NAME VARCHAR(20),
       age INT,
       dep_id INT, -- 外键对应主表的主键(注意：因为加了一行，所以此行不是最后一行，要加,)
       CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
INSERT INTO employee (NAME,age,dep_id) VALUES('张三',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('李四',21,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('王五',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('老王',20,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('大王',22,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('小王',18,2);

mysql> DELETE FROM department WHERE ID=1;# 再次删除，发现报警，报警内容是这一行被外键引用
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (db1.employee, CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id))
mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,5);# 主表也不能添加除外键约束的其他内容，比如：id=5就想添加但是添加不成功
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,2);# 当添加主表有的约束的数据，就可以添加成功了
Query OK, 1 row affected (0.01 sec)
mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

2.删除外键

ALTER TABLE 主表 DROP FOREIGN KEY 列名;

mysql> ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;# 删除外键约束
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,5);# 添加内容，id号不被约束
Query OK, 1 row affected (0.01 sec)

3.创建表后，添加外键

ALTER TABLE 主表 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 主表(列名);

mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);# 因为dep_id=5，副表中没有，所以报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db1.#sql-4b4c_4, CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id))
mysql> select * from employee;# 查看一下确实dep_id中有5
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 寮犱笁   |   20 |      1 |
|  2 | 鏉庡洓   |   21 |      1 |
|  3 | 鐜嬩簲   |   20 |      1 |
|  4 | 鑰佺帇   |   20 |      2 |
|  5 | 澶х帇   |   22 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
|  8 | wang   |   22 |      2 |
|  9 | wang   |   22 |      5 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
mysql> DELETE FROM employee WHERE ID=9;# 删除dep_id=5的数据
Query OK, 1 row affected (0.01 sec)
mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,5);# 再次添加出现报错，证明外键约束添加成功
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

3.级联操作

级联操作就是一些联动的操作，可以在已添加外键约束的情况下，添加级联操作，上面中当设置外键约束后，id=5就不能够添加到附表中了，但是又有一些需要更改id=5的操作，所以可以设置级联，可以在添加外键时设置级联，也可以创建表后更新级联 1.以下是未使用级联操作时，更改数字的操作

mysql> SELECT * FROM employee;
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 寮犱笁   |   20 |      1 |
|  2 | 鏉庡洓   |   21 |      1 |
|  3 | 鐜嬩簲   |   20 |      1 |
|  4 | 鑰佺帇   |   20 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
+----+--------+------+--------+
5 rows in set (0.00 sec)
mysql> UPDATE employee SET dep_id = NULL WHERE dep_id = 1;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> SELECT * FROM employee;# 外键约束后，可以为null，但不可以为外键约束值外的其他值
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 寮犱笁   |   20 |   NULL |
|  2 | 鏉庡洓   |   21 |   NULL |
|  3 | 鐜嬩簲   |   20 |   NULL |
|  4 | 鑰佺帇   |   20 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
+----+--------+------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM department;# 此时外键约束1和2还在约束主表的1，2，但不约束null
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | 研发部    |广州        |
|  2 | 销售部    | 深圳       |
+----+-----------+--------------+
2 rows in set (0.00 sec)
mysql> UPDATE department SET id = 5 WHERE id = 1;# 因为主表没有1了，把副表约束id=1改为id=5
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM department;# 当附表约束id=5后，主表就可以更改null为5了
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  2 | 销售部    | 深圳       |
|  5 | 研发部    |广州        |
+----+-----------+--------------+
2 rows in set (0.00 sec)
mysql> UPDATE employee SET dep_id = 5 WHERE dep_id IS NULL;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> SELECT * FROM employee;# 先更改主表为null，再改副表为值，最后再把主表由null改为附表的值
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 寮犱笁   |   20 |      5 |
|  2 | 鏉庡洓   |   21 |      5 |
|  3 | 鐜嬩簲   |   20 |      5 |
|  4 | 鑰佺帇   |   20 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
+----+--------+------+--------+
5 rows in set (0.00 sec)

2.级联操作

1.在添加外键时设置级联更新

mysql> ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;# 删除外键约束
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;# 添加外键约束+级联更新
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM employee;# 原主表
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 寮犱笁   |   20 |      5 |
|  2 | 鏉庡洓   |   21 |      5 |
|  3 | 鐜嬩簲   |   20 |      5 |
|  4 | 鑰佺帇   |   20 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
+----+--------+------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM department;# 原副表
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  2 | 销售部    | 深圳       |
|  5 | 研发部    |广州        |
+----+-----------+--------------+
2 rows in set (0.00 sec)
mysql> UPDATE department SET dep_id = 1 WHERE dep_id = 5;# 当对副表添加外键约束和级联更新后
ERROR 1054 (42S22): Unknown column 'dep_id' in 'where clause'
mysql> UPDATE department SET id = 1 WHERE id = 5;# 更新副表
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM department;# 副表id=5发生更改id=1
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | 研发部    |广州        |
|  2 | 销售部    | 深圳       |
+----+-----------+--------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM employee;# 同时，未对主表id进行任何操作，主表对应的副表中约束内容也变成了副表更改后的内容
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 寮犱笁   |   20 |      1 |
|  2 | 鏉庡洓   |   21 |      1 |
|  3 | 鐜嬩簲   |   20 |      1 |
|  4 | 鑰佺帇   |   20 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
+----+--------+------+--------+
5 rows in set (0.00 sec)

2.级联删除，当副表中id=1的行删除，相对应的主表中对应关联的行也会被删除

mysql> ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;# 删除外键约束
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;# 添加级联删除
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM employee;
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  1 | 寮犱笁   |   20 |      1 |
|  2 | 鏉庡洓   |   21 |      1 |
|  3 | 鐜嬩簲   |   20 |      1 |
|  4 | 鑰佺帇   |   20 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
+----+--------+------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | 研发部    |广州        |
|  2 | 销售部    | 深圳       |
+----+-----------+--------------+
2 rows in set (0.00 sec)
mysql> DELETE FROM department WHERE id = 1;# 当删除副表中id=1行时
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM department;# 副表中id=1的行已删除
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  2 | 销售部    | 深圳          |
+----+-----------+--------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM employee;# 主表中id=1的行也被删除
+----+--------+------+--------+
| id | name   | age  | dep_id |
+----+--------+------+--------+
|  4 | 鑰佺帇   |   20 |      2 |
|  6 | 灏忕帇   |   18 |      2 |
+----+--------+------+--------+
2 rows in set (0.00 sec)

4.级联操作总结

1.添加级联操作语法：ALTER TABLE 主表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (主表关联列名) REFERENCES 副表(关联列名) ON UPDATE CASCADE ON DELETE CASCADE; 2.分类 1.级联更新：ON UPDATE CASCADE 2.级联删除：ON DELETE CASCADE 优势与弊端：1.方便，2.当多表相关联时，删除其中一个表，其他相关联表中对应的数据也会被删除

标签：name,dep,MySQL,day2,stu,sec,mysql,陈长宏,id
From： https://www.cnblogs.com/junnan/p/16875387.html