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

B站MySQL数据库视频

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.当多表相关联时，删除其中一个表，其他相关联表中对应的数据也会被删除

3.数据库的设计

设计数据库，会间接影响软件开发的难易程度，数据库设计的越合理，软件开发速度越快

1.多表之间的关系

1）分类：

1.一对一的关系：

如：人和身份证的关系

分析：一个人只有一张身份证，一张身份证只能对应一个人

2.一对多(或多对一)的关系

如：部门和员工

分析：一个部门有多个员工，一个员工只能对应一个部门

3.多对多的关系

如：学生和课程

分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

2）实现关系：

1.一对多（多对一）：

如：部门和员工

实现方式：在多的一方建立外键，指向一的一方的主键

2.多对多：多对多关系实现需要借助第三张中间表

实现方式：中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张主表的主键。

3.一对多的关系：

如：人和身份证的关系

实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键（若不是唯一外键，就变成了一对多情况）

3）案例

例如途牛旅游，一个旅游路线分类（起点、终点）可以对应多个旅游线路（水、陆、空），所以是一对多的关系，多条旅游线路可以被多个用户收藏，所以是多对多的关系，多对多要借助中间表，至少要对两张主表的主键列为中间表的列，再进行多多对应。

-- 创建旅游线路分类表，tab_category
-- cid 路由路线分类主键约束，自动增长
-- cname 旅游路线分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
               cid INT PRIMARY KEY AUTO_INCREMENT,
               cname VARCHAR(100) NOT NULL UNIQUE
                          );

-- 添加旅游线路分类数据
INSERT INTO tab_category (cname) VALUES ('周边游'),('出境游'),('国内游'),('港澳游');
SELECT * FROM tab_category;

-- 创建旅游线路表 tab_category
/* rid 旅游线路主键约束，自动增长
rname 旅游线路名称非空，唯一，字符串100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类（对上面表cid的外键约束）（下面未指定外键名称，但会默认生成唯一的一个外键名称）
*/
CREATE TABLE tab_route(
  rid int primary key auto_increment,
  rname varchar(100) not null unique,
  price double,
  rdate date,
  cid int,
  foreign key (cid) references tab_category(cid)
                      );

-- 创建用户表tab_user
/* uid 用户主键，自增长
username 用户长度100，唯一，非空
password 密码长度30，非空
name 真实姓名，长度100
birthday 生日
sex 性别，定长字符串1
telephone 手机号，字符串11
email 邮箱，字符串长度200 
*/
CREATE TABLE tab_user (
    uid int primary key auto_increment,
    username varchar(100) not null unique,
    password varchar(30) not null,
    name varchar(100),
    birthday date,
    sex char(1) default '男',
    telephone varchar(11),
    email varchar(100)
);

/* 创建收藏表tab_favorite
rid 旅游路线id，外键
date 收藏时间
uid 用户id，外键
rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREAT
    date datetime,
    uid int, -- 用户id
    -- 创建复合主键
    PRIMARY KEY(rid,uid), -- 联合主键的操作
    foreign key (rid) references tab_route(rid),
    foreign key (uid) references tab_user(uid),
            );

2.数据库设计的范式

1）概念

设计数据库时需要遵循的一些规范，要遵循后边的范式，必须先遵循前边的所有范式要求

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小

关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）

2）分类

类似布置作业，第一擦桌子，第二擦桌子+扫地，第三擦桌子+扫地+擦黑板，这就是前面所说的递次规范，要干后面的，前面比如要先干完

第一范式（1NF）：每一列都是不可分割的原子数据项

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

概念：

a.函数依赖：A--->B,如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称B依赖于A

例如：学号-->姓名（通过学号就可以确定姓名）（属性组，例如通过学号没办法确定分数，因为学号和分数的对应不唯一，例如张无忌有三个分数，但可以通过学号和课程名称确定分数，所以就称学号和课程名称为属性组）

b.完全函数依赖：A--->B，如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值

例如：（学号，课程名称）---> 分数

c.部分函数依赖：如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值既可

例如：（学号或课程名称）---> 姓名

d.传递函数依赖：A--->B，B--->C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值，可以确定唯一C属性的值，则称C传递函数依赖与A

例如：由学号查找到系名，可以说系名依赖了学号，再由系名可以查找到系主任，就可以说系主任传递依赖于学号

e.码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码

例如：该表中码为：属性组（学号和课程名称）

主属性：码属性组中的所有属性

非主属性：除过码属性组的属性

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

3）详细介绍

1.与第一范式进行对照，以上是一张很常见的表，我们与第一范式进行对照，发现不符合第一范式，因为系的列又进行了分割，把表进行调整，变成第一范式类型的表

但是上表还存在问题：

1.存在非常严重数据冗余，学号，姓名，系名，系主任等过多重复

2.数据添加存在问题，例如新开设的系和系主任时，还没有学生，添加数据不合法

3.数据删除也存在问题，例如张无忌毕业了，删除数据，会将系名、系主任的数据一起删除

2.第二范式

上表解决了数据冗余问题，但还存在问题：

1.数据添加存在问题，例如新开设的系和系主任时，还没有学生，添加数据不合法

2.数据删除也存在问题，例如张无忌毕业了，删除数据，会将系名、系主任的数据一起删除

3.第二范式

上表解决了数据添加和数据删除的问题

4.数据库的备份和还原

1）命令行的方式

备份语法：mysqldump -u用户名 -p密码数据库的名称 > 保存的路径/文件的自定义名称

还原语法：

a.登录数据库

b.创建数据库

c.使用数据库

d.执行文件，source 文件路径

a.备份过程

C:\Users\马俊南>mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
........ # 此处登录信息
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;# 查看当前数据库有多少库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)
mysql> exit
Bye
C:\Users\马俊南>mysqldump -uroot -proot db1 > d:/a.sql

导出的备份文件都是使用过的SQL语句，如下部分SQL展示：

-- MySQL dump 10.13  Distrib 5.5.40, for Win64 (x86)    # 安装的系统
--
-- Host: localhost    Database: db1                     # 本地安装、数据库名称
-- Server version5.5.40                          # 数据库版本号
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 */;
.......                                                 # 此处省略SQL语句
-- Dump completed on 2022-11-13 23:59:46                # 导出备份文件的时间

b.还原过程

C:\Users\马俊南>mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
........ # 此处登录信息
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql>  drop database db1;  # 删除DB1数据库
Query OK, 4 rows affected (0.02 sec)
mysql> show databases;      # 确认已删除DB1数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database db1;  # 先创建DB1数据库
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> source d:/a.sql;     # 使用备份文件，source还原数据DB1
Query OK, 0 rows affected (0.00 sec)
......                      # 中间多条SQL执行成功信息
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;         # 之前数据库中删除的文件已还原
+---------------+
| Tables_in_db1 |
+---------------+
| department    |
| employee      |
| stu           |
| student       |
+---------------+
4 rows in set (0.00 sec)