MySQL基础

1. 数据库

1.1 数据库的概念

  1. 存储数据的仓库
  2. 以文件的形式存储在服务器电脑上，本质上是一个文件系统

  3. 关系型数据库可以使用SQL语句进行管理数据库系统

    1.2 数据库的特点

  4. 持久化存储数据的。其实数据库就是一个文件系统

  5. 方便存储和管理数据
  6. 使用了统一的方式操作数据库 — SQL

    1.3 数据库管理系统

DataBase Management System(DBMS):操作和管理数据库的大型软件，用于建立，维护和使用数据库，对数据库进行统一管理和控制。用户可以通过数据库管理系统访问数据库中表里的数据。

  1. 一个数据库服务器中包含多个库(database)
  2. 一个数据库中包含多张表(table)
  3. 一个表里包含多条数据(data)

1.4 SQL

1.4.1 什么是SQL

SQL: Structure Query Language(结构化查询语言)

1.4.2 SQL的作用
  1. 是一种所有关系型数据库的查询规范，不同的数据库都支持。
  2. 通用的数据库操作语言，可以用在不同的数据库中。
  3. 不同的数据库 SQL 语句有一些区别

1.4.3 SQL语句分类
  1. DDL数据定义语言(Data Definition Language):建库，建表
  2. DML数据操作语言(Data Manipulation Language):对库中数据进行增删改
  3. DQL数据查询语言(Data Query Language)：对表中的数据进行查询
  4. DCL数据控制语言(Data Control Language):设置用户权限

2. MySQL数据库管理软件

2.1 MySQL数据库登录

  1. # 登录
  2. mysql -u root -p root
  3. # ip地址：127.0.0.1（本机地址）
  4. mysql -hip 127.0.0.1 -u root -p root
  5. # 另一种
  6. mysql --host=127.0.0.1 -user=root --password=root
  8. # 退出mysql
  9. exit or quit

2.2 DDL：数据库定义操作

2.2.1 DDL操作数据库

创建数据库：Create
  1. # 创建数据库
  2. CREATE DATABASE 数据库名；
  3. # 判断数据库是否存在，不存在则创建
  4. CREATE DATABASE if not exists 数据库名;
  5. # 指定字符集创建数据库
  6. CREATE DATABASE 数据库名 CHARACTER set 数据库名;

查询数据库：Retrieve
  1. # 查询所有数据库
  2. SHOW DATABASES;
  3. # 查看某个数据库定义信息
  4. SHOW CREATE DATABASE 数据库名;

修改数据库：Update
  1. # 修改数据库为默认的字符集
  2. ALTER DATABASE 数据库名 DEFAULT CHARACTER 字符集;

删除数据库：Delete
  1. DROP DATABASE 数据库名;

使用/切换数据库
  1. # 查看正在使用的数据库
  2. SELECT DATABASE();
  3. # 切换数据库
  4. USE 数据库名;

2.2.2 DDL操作表

创建表
  1. CREATE TABLE 表名(
  2.     字段名1 字段类型2，  # 数据间用逗号隔开
  3.     字段名2 字段类型2    # 最后一行不要逗号
  4. );
  5. #查询数据库中所有表的名称
  6. SHOW TABLES;
  7. # 查看表的结构
  8. DESC 表名;
  9. # 查看创建表的SQL语句
  10. SHOW CREATE TABLE 表名;
  11. # 复制表，快速创建一个与player结构相同的表
  12. CREATE TABLE 表名2 LIKE 表名;

MySQL数据类型
  1. int, double, float
  2. varchar(m): 长度可变；char(m):长度不变，无论使用几个字符都占满全部
  3. date：日期，只包含年月日， yyyy-MM-dd
    datatime:包含年月日分秒，yyyy-MM-dd HH:mm:ss
  4. timestamp:时间戳类型，包含年月日分秒，yyyy-MM-dd HH:mm:ss

PS:timestamp类型的日期，如果不给字段赋值，或赋值为null，默认使用当前的系统时间

  1. # 创建一个球员表
  2. CREATE TABLE player(
  3.     序号 INT,
  4.     名字 VARCHAR(10),
  5.     年龄 INT,
  6.     国家 VARCHAR(10),
  7.     球队 VARCHAR(10),
  8.     日期 TIMESTAMP
  9. );
  11. #查询数据库中所有表的名称
  12. SHOW TABLES;
  14. # 查看表的结构
  15. DESC player;
  17. # 复制表，快速创建一个与player结构相同的表
  18. CREATE TABLE player2 LIKE player;
  20. # 查看创建表的SQL语句
  21. SHOW CREATE TABLE player;
  22. -- ->查看结果:
  23. CREATE TABLE `player` (
  24.   `序号` int(11) DEFAULT NULL,
  25.   `名字` varchar(10) DEFAULT NULL,
  26.   `年龄` int(11) DEFAULT NULL,
  27.   `国家` varchar(10) DEFAULT NULL,
  28.   `球队` varchar(10) DEFAULT NULL,
  29.   `日期` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  30.   `城市` varchar(10) DEFAULT NULL
  31. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

capture_20210705173632001.bmp

删除表
  1. # 直接删除
  2. DROP TABLE 表名;
  3. # 判断是否存在表，若存在，则删除
  4. DROP TABLE IF EXISTS 表名;
  1. # 直接删除
  2. DROP TABLE player2;
  4. # 判断是否存在表，若存在，则删除
  5. DROP TABLE IF EXISTS player2;

修改表
  1. # 修改表名
  2. ALTER TABLE 表名 RENAME TO 新表名;
  3. # 修改表的字符集
  4. ALTER TABLE 表名 CHARACTER SET 字符集;
  5. #添加一列 ADD
  6. ALTER TABLE 表名 ADD 列名 数据类型;
  7. # 修改列名&类型
  8. ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
  9. # 修改列的类型
  10. ALTER TABLE 表名 MODIFY 列名 新数据类型;
  11. # 删除列
  12. ALTER TABLE 表名 DROP 列名;
  1. # 修改表名
  2. ALTER TABLE player RENAME TO fbplayer;
  4. # 修改表的字符集
  5. ALTER TABLE fbplayer CHARACTER SET utf8;
  7. #添加一列 ADD
  8. ALTER TABLE fbplayer ADD 城市 VARCHAR(10);
  10. # 修改列名&类型
  11. ALTER TABLE fbplayer CHANGE 城市 所在地 VARCHAR(10);
  12. # 修改列的类型
  13. ALTER TABLE fbplayer MODIFY 所在地 CHAR(10);
  15. # 删除列
  16. ALTER TABLE fbplayer DROP 所在地;

2.3 DML：增删改表中数据

添加数据
  1. # 表中添加数据
  2. INSERT INTO 表名 VALUES('值n1','值2',...,'值n');
  3. # 插入部分列，必须写列名
  4. INSERT INTO fbplayer(列名1,列名2,...列名n) VALUES('值n1','值2',...,'值n');
  1. # 表中添加数据
  2. INSERT INTO fbplayer VALUES(2, "梅西", 33, "阿根廷", '巴塞罗那', NULL);
  4. # 插入部分列，必须写列名
  5. INSERT INTO fbplayer(序号, 名字, 年龄, 国家, 球队) VALUES(1, "萨卡", 19, "英格兰", '阿森纳'),(2, "梅西", 33, "阿根廷", '巴塞罗那'),(3,'C罗',35,'西班牙','尤文图斯'),(4,'德布劳内',35,'比利时','曼城');
  7. # 查看列表
  8. SELECT * FROM fbplayer;

capture_20210705202346133.bmp

  • PS:
  1. 列名和值要一一对应。
  2. 如果表名后，不定义列名，则默认给所有列添加值insert into 表名 values(值1,值2,…值n);
  3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来

删除数据
  1. # 删除表中记录
  2. # 不带条件删除
  3. DELETE FROM 表名;
  4. # 带条件删除
  5. DELETE FROM 表名表名 WHERE 列名=值;
  6. # truncate删除表中所有数据等价于不带条件删除
  7. TRUNCATE TABLE 表名;
  1. # 删除表中记录
  2. # 不带条件删除， 删除所有记录
  3. DELETE FROM fbplayer;
  5. # 带条件删除
  6. DELETE FROM fbplayer WHERE 序号=1;
  8. # truncate删除表中所有数据等价于不带条件删除
  9. TRUNCATE TABLE fbplayer;
  • truncate 和 delete 的区别：
  • truncate 相当于删除表的结构，再创建一张表, 效率更高

修改数据
  1. # 语法
  2. UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2,... [WHERE 列名 = 值];
  4. # 更改数据
  5. UPDATE fbplayer SET 年龄=30 WHERE 序号=4;

capture_20210705203316348.bmp

PS: 若不加条件，所有的列都将会被修改

2.4 DQL:查询表中的数据

2.4.1 简单查询

  1. # 使用*表示所有列
  2. SELECT * FROM 表名;
  3. # 查询指定序列
  4. SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;
  5. # 对列指定别名
  6. SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名; 
  7. # 对列和表同时指定别名
  8. SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名;
  9. # 查询指定列并且结果不出现重复数据
  10. SELECT DISTINCT 字段名 FROM 表名;
  11. # 某列数据和固定值运算
  12. SELECT 列名 1 + 固定值 FROM 表名;
  13. # 某列数据和其他列数据参与运算
  14. SELECT 列名 1 + 列名 2 FROM 表名;

查询所有列
  1. INSERT INTO fbplayer VALUES(5, "扎卡", 30, "瑞士", '阿森纳', NULL),(6, "扎卡利亚", 30, "瑞士", '门兴', NULL),(7, "卡拉斯科", 30, "比利时", '马竞', NULL), (8, "萨拉赫", 30, "埃及", '利物浦', NULL);
  3. # 添加列
  4. ALTER TABLE fbplayer ADD 过人 INT;
  5. ALTER TABLE fbplayer ADD 头球 INT;
  6. # 更改数据
  7. UPDATE fbplayer SET 过人=100, 头球=90;
  9. UPDATE fbplayer SET 过人=99, 头球=60 WHERE 序号=2;
  11. UPDATE fbplayer SET 过人=80, 头球=99 WHERE 序号=3;
  12. UPDATE fbplayer SET 过人=85, 头球=80 WHERE 序号=4;
  13. UPDATE fbplayer SET 过人=60, 头球=60 WHERE 序号=5;
  14. UPDATE fbplayer SET 过人=55, 头球=60 WHERE 序号=6;
  15. UPDATE fbplayer SET 过人=90, 头球=70 WHERE 序号=7;
  16. UPDATE fbplayer SET 过人=91, 头球=60 WHERE 序号=8;
  19. # 查看列表
  20. SELECT * FROM fbplayer;

capture_20210705205551634.bmp

查询指定列

  1. # 查询指定序列
  2. SELECT 名字, 国家, 球队 FROM fbplayer;

capture_20210705210009107.bmp

指定别名进行查询AS

  1. # 指定别名
  2. SELECT 名字 AS NAME, 国家 AS country, 球队 AS club FROM fbplayer; 
  3. # 指定列名和表名
  4. SELECT 名字 AS NAME, 国家 AS country, 球队 AS club FROM fbplayer AS player;

capture_20210705210736509.bmp

  • 使用别名的好处： 显示的时候使用新的名字，并不修改表的结构。
  • 表使用别名的原因：用于多表查询操作

去除重复数据DISTINCT

  1. # 查询球员来自哪个国家
  2. SELECT 国家 FROM fbplayer;
  3. # 去除重复的国家
  4. SELECT DISTINCT 国家 FROM fbplayer;

查询结果参与运算

  1. # 某列数据与固定值运算
  2. SELECT *,过人+10 AS 能力 FROM fbplayer;
  4. # 某列数据与其它列数据运算
  5. SELECT *,过人+头球 AS 能力 FROM fbplayer;
  7. # AS可以省略
  8. SELECT *,过人+头球 能力 FROM fbplayer;

capture_20210705214916481.bmp

  • 注意: 参与运算的必须是数值类型

2.4.2 条件查询

  1. 条件查询语法
    SELECT 字段名 FROM 表名 WHERE 条件*;
  2. 运算符
    >、< 、<= 、>= 、= 、<>
    BETWEEN…AND
    IN( 集合)
    LIKE：模糊查询
    占位符：
    _:单个任意字符
    %：多个任意字符
    IS NULL
    AND 或 &&
    OR 或 ||
    NOT 或 ! ```sql — 查询年龄大于30岁的球员 SELECT 名字,年龄 FROM fbplayer WHERE 年龄>30;

— 查询年龄等于30岁的球员 SELECT 名字,年龄 FROM fbplayer WHERE 年龄=30;

— 查询年龄小于于30岁的球员 SELECT 名字,年龄 FROM fbplayer WHERE 年龄<30;

— 查询年龄不等于30岁的球员 SELECT 名字,年龄 FROM fbplayer WHERE 年龄!=30;

— 查询年龄不等于30岁的球员 SELECT 名字,年龄 FROM fbplayer WHERE 年龄<>30;

— 查询年龄大于20岁小于30岁的球员 SELECT 名字,年龄 FROM fbplayer WHERE 年龄>20 AND 年龄<30;

SELECT 名字,年龄 FROM fbplayer WHERE 年龄 BETWEEN 20 AND 30;

— 查询年龄等于19, 28, 30岁的球员 SELECT 名字,年龄 FROM fbplayer WHERE 年龄=30 OR 年龄=19 OR 年龄=28;

SELECT 名字,年龄 FROM fbplayer WHERE 年龄 IN (19, 28, 30);

— IS NULL: NULL不能用=或者!=, 只能用is null或 is not null SELECT * FROM fbplayer WHERE 头球 IS NOT NULL

  2. 3. 模糊查询
  4. ```sql
  5. -- 查询姓萨的人
  6. SELECT * FROM fbplayer WHERE 名字 LIKE '萨%';

capture_20210705222409710.bmp

  1. -- 查询第二个字是卡的人
  2. SELECT * FROM fbplayer WHERE 名字 LIKE '_卡%';

capture_20210705222619787.bmp

  1. -- 查询名字含有卡的人
  2. SELECT * FROM fbplayer WHERE 名字 LIKE '%卡%';

capture_20210705222746066.bmp

通配符

  1. %：匹配任意多的字符串（0个或多个）
  2. _:匹配一个字符（占位符）

2.4.3 排序查询

语法： order by 排序字段1 排序方式1，排序字段2 排序方式2…

  1. 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

排序方式：
ASC：升序，默认
 DESC：降序

  1. # DESC:降序排列
  2. SELECT * FROM fbplayer ORDER BY 年龄 DESC;
  3. # ASC：升序排列，默认值
  4. SELECT * FROM fbplayer ORDER BY 年龄 ASC, 过人 DESC;

2.4.4 聚合函数查询

聚合函数：将一列数据作为一个整体，进行纵向的计算。
1. count：计算个数
1. 一般选择非空的列：主键
2. count(*)
2. max：计算最大值
3. min：计算最小值
4. sum：计算和
5. avg：计算平均值

  1. # 1. count：计算个数，一般选非空的列即主键
  2. SELECT COUNT(序号) AS 总人数 FROM fbplayer;
  4. INSERT INTO fbplayer(名字) VALUES("塔瓦雷斯");
  5. SELECT COUNT(*) AS 总人数 FROM fbplayer;
  7. SELECT COUNT(序号) AS 总人数 FROM fbplayer;
  8. #于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，
  9. # 但如果需要把 NULL统计进去，则使用 0 代替
  10. SELECT IFNULL(序号, 0)  FROM fbplayer;
  11. SELECT COUNT(IFNULL(序号, 0)) AS 总人数 FROM fbplayer;
  13. # 2. min& max:计算最小值&最大值
  14. SELECT 名字, MAX(年龄) FROM fbplayer;
  15. SELECT MIN(过人) FROM fbplayer;
  17. SELECT COUNT(序号) AS 个数 FROM fbplayer;
  19. SELECT SUM(过人) AS 总分 FROM fbplayer;
  21. SELECT AVG(过人) AS 平均分 FROM fbplayer;
  23. SELECT SUM(过人)/COUNT(过人) AS 平均分 FROM fbplayer;

注意：聚合函数的计算，排除null值。
解决方案：1} 选择不包含非空的列进行计算; 2}IFNULL函数

2.4.5 分组查询

分组是将条件中字段相同内容的放在一组，并返回每一组的第一条数据

所以单组分组没有意义，分组的目的是为了统计，一般将分组和聚合函数放在一起使用

语法：group by 分组字段

SELECT 字段 1, 字段 2… FROM 表名 GROUP BY 分组字段 [HAVING 条件**];

  1. SELECT 序号, 年龄, 名字, 国家 FROM fbplayer GROUP BY 年龄 HAVING 年龄>29;
  3. # 统计各年龄段过人分数
  4. SELECT 年龄, AVG(过人) FROM fbplayer GROUP BY 年龄;
  6. # 添加一列
  7. ALTER TABLE fbplayer ADD gender VARCHAR(10); 
  9. UPDATE fbplayer SET gender = "male";
  10. UPDATE fbplayer SET gender = "female" WHERE 序号 % 2 = 1;
  12. # 分组是将条件中字段相同内容的放在一组，并返回每一组的第一条数据
  13. # 所以单组分组没有意义，分组的目的是为了统计，一般将分组和聚合函数放在一起使用
  14. SELECT gender, AVG(过人) FROM fbplayer GROUP BY gender;
  16. -- 1. 查询男女个有多少人
  17. SELECT gender, COUNT(序号) FROM fbplayer GROUP BY gender;
  18. -- 2. 查询年龄大于 25 岁的人,按性别分组,统计每组的人数
  19. SELECT gender, 年龄, COUNT(*) FROM fbplayer GROUP BY gender HAVING 年龄>25;
  20. -- * 1} 先过滤小于25岁的人
  21. -- * 2} GROUP BY分组
  22. -- * 3} 统计人数
  23. SELECT gender, COUNT(*) FROM fbplayer WHERE 年龄>25 GROUP BY gender;
  26. # where与having的区别
  27. -- * 1} where 先过滤后分组，having 先分组后过滤
  28. -- * 2} where 后面不可以使用聚合函数，having 后面可以使用聚合函数
  29. -- ps: having后面跟的条件，必须是分组之后表里包含的项
  31. -- 查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据
  32. SELECT gender, COUNT(序号) FROM fbplayer WHERE 年龄>25 GROUP BY gender HAVING 头球>2;

ps:where与having的区别

— 1} where 先过滤后分组，having 先分组后过滤
— 2} where 后面不可以使用聚合函数，having 后面可以使用聚合函数
— ps: having后面跟的条件，必须是分组之后表里包含的项

2.4.6 分页查询

LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句] [HAVING 子句] [ORDER BY 子 句] [LIMIT 子句];

LIMIT offset,length;

offset ：起始行数，从 0 开始计数，如果省略，默认就是 0; 开始的索引=(当前的页码-1)*每页显示的条数

length ： 返回的行数

  1. -- 开始的索引=(当前的页码-1)*每页显示的条数
  2. -- 第一页
  3. SELECT * FROM fbplayer LIMIT 3;
  4. -- 第二页
  5. SELECT * FROM fbplayer LIMIT 3,3;
  6. -- 第三页    
  7. SELECT * FROM fbplayer LIMIT 6,3;

3. MySQL数据库备份与还原

3.1 备份

mysqldump -u 用户名 -p 密码 数据库 > 文件的路径

  1. -- 将数据库huang备份至O://dump.sql目录下
  2. mysqldump -uroot -proot huang > O://dump.sql;

3.2 还原

登陆后再操作

USE 数据库；

SOURCE 导入文件的路径 ;

  1. use huang2;
  2. source O://dump.sql;

4. MySql约束

4.1 约束

约束的作用

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束的分类

  1. 主键约束：primary key
  2. 非空约束：not null
  3. 唯一约束：unique
  4. 外键约束：:foreign key

1.2 主键约束

主键的作用

用来标识数据库中的每一条记录

主键的选择

通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

  1. 含义：非空且唯一
  2. 一张表只能有一个字段为主键
  3. 主键就是表中记录的唯一标识

创建主键

  1. 在创建表的时候给字段添加主键
    字段名 字段类型 PRIMARY KEY
  2. 在已有表中添加主键
    ALTER TABLE 表名 ADD PRIMARY KEY( 字段名 ); 
  2. -- 1.1 创建表时添加主键
  3. CREATE TABLE stuu(
  4.     id INT PRIMARY KEY,   -- 给id添加主键
  5.     NAME VARCHAR(20)
  6. );
  7. -- 1.2 给已经存在的表添加主键
  8. ALTER TABLE fbplayer ADD PRIMARY KEY(序号); 
  10. -- -- 删除主键
  11. ALTER TABLE stuu DROP PRIMARY KEY;
  13. -- 添加了主键约束后，主键就不可以再重复
  14. INSERT INTO fbplayer VALUES (NULL, "塔瓦雷斯", 21, "葡萄牙", "阿森纳",NULL, 90, 90, "male");

删除主键

ALTER TABLE 表 DROP PRIMARY KEY;

主键自增

AUTO_INCREMENT 表示自动增长 ( 字段类型必须是整数类型 )

  1. -- 创建表时设置主键自增
  2. CREATE TABLE stu2(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.     NAME VARCHAR(10)
  5. )AUTO_INCREMENT=10;
  7. INSERT INTO stu2 VALUES(NULL, "saka");
  8. INSERT INTO stu2 VALUES(NULL, "rowe");
  10. SELECT * FROM stu2;
  12. -- 创建好的设置过主键自增的表修改起始值
  13. ALTER TABLE stu2 AUTO_INCREMENT=20;
  14. INSERT INTO stu2 VALUES(NULL, "messi");
  15. INSERT INTO stu2 VALUES(NULL, "C R");
  16. -- ps: 1} 主键自增，修改主键起始值必须大于表的最后一个值
  17. --     2} 没有添加主键自增的表，创建之后是不可以添加的，也就是说主键自增只能再创建时添加，添加后可以修改起始值
  19. -- delete与truncate对自增长的影响
  20. -- delete 删除所有的记录后对自增长没有影响
  21. DELETE FROM stu2;
  22. SELECT * FROM stu2;
  24. INSERT INTO stu2 VALUES(NULL, "messi");
  25. INSERT INTO stu2 VALUES(NULL, "C R");
  26. INSERT INTO stu2 VALUES(NULL, "messi");
  27. INSERT INTO stu2 VALUES(NULL, "C R");
  29. -- truncate 删除所有记录后，主键值重新开始增长
  30. TRUNCATE TABLE stu2;
  31. INSERT INTO stu2 VALUES(NULL, "messi");
  32. INSERT INTO stu2 VALUES(NULL, "C R");
  33. INSERT INTO stu2 VALUES(NULL, "messi");
  34. INSERT INTO stu2 VALUES(NULL, "C R");
  35. SELECT * FROM stu2;

1.3 唯一约束

  1. 唯一约束：表中一列不能出现重复的值
  1. -- 唯一约束：表中一列不能出现重复的值
  2. CREATE TABLE stu3(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.     NAME VARCHAR(10) UNIQUE    -- 唯一约束，不可以出现同名的学生
  5. );
  6. INSERT INTO stu3 VALUES(NULL, "messi");
  7. INSERT INTO stu3 VALUES(NULL, "C R");
  8. INSERT INTO stu3 VALUES(NULL, NULL);
  9. -- !!!
  10. -- <e>查询：INSERT INTO stu3 VALUES(NULL, "messi")
  11. -- 错误代码： 1062
  12. -- Duplicate entry 'messi' for key 'name'
  14. -- ps:null没有数据，可以重复出现

1.4 非空约束

什么是非空约束：某一列不能为 null。

字段名 字段类型 NOT NULL

  1. # 3. 非空约束
  2. CREATE TABLE stu4(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.     NAME VARCHAR(10) UNIQUE NOT NULL   -- 唯一约束，非空约束
  5. );
  7. INSERT INTO stu4 VALUES(NULL, "C R");
  8. INSERT INTO stu4 VALUES(NULL, NULL);
  9. -- 错误代码： 1048
  10. -- Column 'NAME' cannot be null
  12. -- 默认值 default
  14. CREATE TABLE stu5(
  15.     id INT PRIMARY KEY AUTO_INCREMENT,
  16.     NAME VARCHAR(10) UNIQUE NOT NULL,   -- 唯一约束，非空约束
  17.     address VARCHAR(10) DEFAULT "安徽"
  18. );
  19. INSERT INTO stu5 VALUES(NULL, "C R", DEFAULT);
  20. INSERT INTO stu5 VALUES(NULL, "messi", DEFAULT);
  22. SELECT * FROM stu5;

默认值

字段名 字段类型 DEFAULT 默认值

疑问:如果一个字段设置了非空与唯一约束，该字段与主键的区别?

  1. 主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列；即联合主键。
  2. 自增长只能用在主键上

1.5 外键约束

外键：让两张表之间的数据建立链接，保证两张表数据的一致性

主表：一对多中的一方，用于约束别人

从表：多对一的多方，被主表所约束

创建外键

  1. 新建表时增加外键：

**[CONSTRAINT] [** **外键约束名称** **] FOREIGN KEY(** **外键字段名** **) REFERENCES** **主表名** **(** **主键字段名** **)**

  1. 已有表增加外键：

**ALTER TABLE** **从表** **ADD [CONSTRAINT] [** **外键约束名称** **] FOREIGN KEY (** **外键字段名** **) REFERENCES** **主表** **(** **主键字段名** **);**

删除外键

**ALTER TABLE** **从表** **drop foreign key** **外键名称** **;**

外键级联

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

ON UPDATE CASCADE : 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新

ON DELETE CASCADE : 级联删除

  1. -- 外键约束
  3. -- 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据
  4. CREATE TABLE emp (
  5.   id INT PRIMARY KEY AUTO_INCREMENT,
  6.   NAME VARCHAR(30),
  7.   age INT,
  8.   dep_name VARCHAR(30),
  9.   dep_location VARCHAR(30)
  10. );
  11. -- 添加数据
  12. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
  13. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
  14. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
  15. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
  16. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
  17. INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
  19. -- 单表的问题
  20. -- 1} 数据冗余
  21. -- 2} 增删改出现问题
  23. -- 分成两张表
  24. -- 1. 创建部门表：主表，用来约束别人的表，只有一方
  25. CREATE TABLE department(
  26.     id INT PRIMARY KEY AUTO_INCREMENT,
  27.     dep_name VARCHAR(10),
  28.     dep_location VARCHAR(10)
  29. );
  31. -- 2. 创建员工表：从表，被别人约束的表，有多方
  32. CREATE TABLE employee(
  33.     id INT PRIMARY KEY AUTO_INCREMENT,
  34.     NAME VARCHAR(10) UNIQUE NOT NULL,
  35.     age INT NOT NULL,
  36.     dep_id INT NOT NULL   -- 外键对应的主键
  37. );
  39. -- 3. 添加两个部门
  40. INSERT INTO department VALUES (NULL, "研发部", "广州"), (NULL, "销售部", "深圳");
  42. -- 4. 添加员工，dep_id表示员工所在的部门
  43. -- 添加数据
  44. INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
  45. INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
  46. INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
  47. INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
  48. INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
  49. INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
  51. -- 5. 添加外键约束，使dep_id只能是department主键中存在的id；
  52. -- 外键：从表中与主表中主键对应的那一列，employee中的dep_id即是
  54. -- 1} 新建从表时添加外键
  55. CREATE TABLE employee2(
  56.     id INT PRIMARY KEY AUTO_INCREMENT,
  57.     NAME VARCHAR(10) UNIQUE NOT NULL,
  58.     age INT NOT NULL,
  59.     dep_id INT NOT NULL,   -- 外键对应的主键
  60.     -- 创建外键约束, dep_id对应于主表department的id约束😂
  61.     CONSTRAINT emp_dep_id_fk FOREIGN KEY (dep_id) REFERENCES department(id)
  62. )
  64. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('张三', 20, 1);
  65. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('李四', 21, 1);
  66. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('王五', 20, 1);
  67. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('老王', 20, 2);
  68. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('大王', 22, 2);
  69. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('小王', 18, 2);
  71. -- 2} 已有表添加外键
  72. ALTER TABLE employee ADD CONSTRAINT emp_dep_id_fk_2 FOREIGN KEY (dep_id) REFERENCES department(id);
  74. -- 插入外键中不存在的部门
  75. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('小王8', 18, 3);
  76. -- Cannot add or update a child row: a foreign key constraint fails (`huang`.`employee2`, CONSTRAINT `emp_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
  78. -- 删除外键
  79. ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1;
  82. -- 级联操作
  83. -- 在修改和删除主表的主键时，从表的外键值同时跟着更新或删除，称为级联操作
  85. -- 删除employee2表，重新创建employee2表，添加级联更新和级联删除
  86. -- delete只是将表清空，并没有将表删除，删除需要使用drop
  87. DELETE FROM employee2;
  89. DROP TABLE employee2;
  91. CREATE TABLE employee2(
  92.     id INT PRIMARY KEY AUTO_INCREMENT,
  93.     NAME VARCHAR(10) UNIQUE NOT NULL,
  94.     age INT NOT NULL,
  95.     dep_id INT NOT NULL,   -- 外键对应的主键
  96.     -- 创建外键约束,并添加级联操作
  97.     CONSTRAINT emp_dep_id_fk_3 FOREIGN KEY (dep_id) REFERENCES department(id) 
  98.     ON UPDATE CASCADE ON DELETE CASCADE 
  99. )
  101. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('张三', 20, 1);
  102. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('李四', 21, 1);
  103. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('王五', 20, 1);
  104. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('老王', 20, 2);
  105. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('大王', 22, 2);
  106. INSERT INTO employee2 (NAME, age, dep_id) VALUES ('小王', 18, 2);
  108. SELECT * FROM department;
  109. SELECT * FROM employee;
  110. SELECT * FROM employee2;
  112. SELECT * FROM emp;

5. 数据库的设计

5.1 多表之间的关系

一对多：最常用的关系 部门和员工
多对多：学生选课表 和 学生表， 一门课程可以有多个学生选择，一个学生选择多门课程
一对一：相对使用比较少。员工表 简历表， 公民表 护照表

  1. -- 建立系表:主表主键
  2. CREATE TABLE depart(
  3.     系名 VARCHAR(10) PRIMARY KEY NOT NULL,  -- 学生表对应的主键
  4.     系主任 VARCHAR(10)
  5. );
  7. -- 建立新的学生表
  8. -- 相对于系表来书说：是从表；相对于课表来说：是主表
  9. CREATE TABLE newStudent(
  10.     学号 INT PRIMARY KEY NOT NULL, -- 课表对应的主键
  11.     姓名 VARCHAR(10),
  12.     系名 VARCHAR(10),   -- 外键到系表
  13.     -- 外键约束
  14.     CONSTRAINT nd_fk FOREIGN KEY (系名) REFERENCES depart(系名)
  15.     ON UPDATE CASCADE ON DELETE CASCADE 
  16. );
  18. -- 创建课表
  19. CREATE TABLE course(
  20.     学号 INT  NOT NULL,
  21.     课程名称 VARCHAR(10),
  22.     分数 VARCHAR(10),
  23.     创建时间 TIMESTAMP,
  24.     -- 设置外键约束
  25.     CONSTRAINT sc_fk FOREIGN KEY (学号) REFERENCES student(学号)
  26.     ON UPDATE CASCADE ON DELETE CASCADE,
  27.     CONSTRAINT newsc_fk FOREIGN KEY (学号) REFERENCES newStudent(学号)
  28.     ON UPDATE CASCADE ON DELETE CASCADE 
  29. );
  31. -- 修改课表，将学生与选课改成多对多的关系
  32. CREATE TABLE courseName(
  33.     course_id INT PRIMARY KEY AUTO_INCREMENT, 
  34.     课程名称 VARCHAR(10)
  35. );
  37. -- 创建选课表，中间表
  38. CREATE TABLE selectCourse(
  39.     学号 INT  NOT NULL,
  40.     课程 INT,
  41.     -- 设置课程表外键约束
  42.     CONSTRAINT cs_fk FOREIGN KEY (课程) REFERENCES courseName(course_id)
  43.     ON UPDATE CASCADE ON DELETE CASCADE,
  44.     分数 VARCHAR(10),
  45.     创建时间 TIMESTAMP,
  46.     -- 设置学生表外键约束
  47.     CONSTRAINT newsc_fk FOREIGN KEY (学号) REFERENCES newStudent(学号)
  48.     ON UPDATE CASCADE ON DELETE CASCADE  
  49. )
  52. INSERT INTO coursename(课程名称) VALUES("高等数学"),("大学英语"),("计算机基础"),("法理学"),("法律社会学");
  54. INSERT INTO selectCourse VALUES(10010, 1,     95, NULL),
  55.                                (10010,    2, 87, NULL),
  56.                                (10010,    3, 65, NULL),
  57.                                (10011,     4, 77, NULL),
  58.                                (10011,      1, 87, NULL),
  59.                                (10011,     5,65, NULL),
  60.                                (10012,     5,95,NULL),
  61.                                (10012,    4, 97,NULL),
  62.                                (10012,     2, 99,NULL);
  64. SELECT * FROM courseName;
  65. SELECT * FROM selectCourse;
  68. -- 插入信息时，要先插入主表中的主键信息，创建表时，也要先创建主表
  70. INSERT INTO depart VALUES  ("经济系",    "张三丰"),
  71.                            ("法律系",    "任我行");
  73. INSERT INTO newStudent VALUES (10010,    "张无忌", "经济系"),
  74.                               (10011,    "令狐冲", "法律系"),
  75.                               (10012,    "杨过",    "法律系");

capture_20210725100006175.bmp

5.2. 范式

范式：好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式

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

满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

5.2.1 第一范式(1NF)

定义：每一列都是不可分割的原子数据项

capture_20210723163510822.bmp

MySQL实现

  1. CREATE DATABASE stuManager;
  3. USE stuManager;
  5. CREATE TABLE stu(
  6.     学号 INT,
  7.     姓名 VARCHAR(10),
  8.     系名 VARCHAR(10),
  9.     系主任 VARCHAR(10),
  10.     课程名称 VARCHAR(10),
  11.     分数 INT
  12. );
  14. DESC stu;
  16. -- 添加一列创建时间
  17. ALTER TABLE stu ADD 创建时间 TIMESTAMP;
  19. -- 添加记录
  21. INSERT INTO stu VALUES (10010,    "张无忌", "经济系",    "张三丰", "高等数学", 95, NULL); 
  23. INSERT INTO stu VALUES (10010,    "张无忌", "经济系",    "张三丰", "高等数学", 95, NULL),
  24.                        (10010,    "张无忌", "经济系",    "张三丰", "大学英语", 87, NULL),
  25.                        (10010,    "张无忌", "经济系",    "张三丰", "计算机基础", 65, NULL),
  26.                        (10011,    "令狐冲", "法律系",    "任我行", "法理学",    77, NULL),
  27.                        (10011,    "令狐冲", "法律系",    "任我行", "大学英语",    87, NULL),
  28.                        (10011,    "令狐冲", "法律系",    "任我行", "法律社会学",    65, NULL),
  29.                        (10012,    "杨过",    "法律系","任我行","法律社会学",95,NULL),
  30.                        (10012,    "杨过",    "法律系","任我行","法理学",97,NULL),
  31.                        (10012,    "杨过",    "法律系","任我行","大学英语",99,NULL);
  33. SELECT * FROM stu;

问题：

  1. 1.   <font color="red">存在严重的数据冗余，数据重复</font>
  2. 2.   <font color="red">数据添加存在问题，开设新的系添加进去，数据不合法</font>
  3. 3.   <font color="red">数据删除存在问题， 删除一个同学的信息，他所在系的信息也被删除</font>

5.2.2 第二范式(2NF)

定义：在1NF的基础上，非码属性必须完全依赖于码，

几个概念：

  1. 函数依赖：A—>B: 如果通过A属性(属性组)的值可以唯一确定B属性的值，则B依赖于A
    eg: 学号—>姓名: 通过学号可以唯一的确定姓名，即称姓名依赖于学号；（学号，课程名称）—> 分数
  2. 完全函数依赖：A—>B: 若A是一个属性组，属性B的值完全依赖属性组A中的所有属性
    eg: (学号，课程名称) —> 分数
  3. 部分函数依赖：A—>B: 若A是一个属性组，属性B的值只需要依赖于属性组A中的部分值即可
    eg: (学号，课程名称) —> 姓名
    ps:第二范式就是要消除表里的部分函数依赖
  4. 传递函数依赖：A—>B, B—>C: 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
    eg: 学号—>系名， 系名—>系主任
  5. 码：在一张表中，一个属性或属性组被其他所有属性完全依赖，则称这个属性（属性组）为该表的码。
    eg: 表中的码：（学号，课程名称）只有通过学号+课程名称才能够唯一确定其他的所有属性
    主属性：码属性组中的所有属性
    非主属性：除主属性以外的属性
    capture_20210723191306275.bmp
  1. -- 建立两张表，学生表与选课表
  3. -- 1. 建立主表学生表
  4. CREATE TABLE student(
  5.     学号 INT PRIMARY KEY NOT NULL,
  6.     姓名 VARCHAR(10),
  7.     系名 VARCHAR(10),
  8.     系主任 VARCHAR(10)
  9. );
  12. -- 2. 建立从表选课表
  13. -- 外键对应的主键是在从表中设置的
  14. CREATE TABLE course(
  15.     学号 INT  NOT NULL,
  16.     课程名称 VARCHAR(10),
  17.     分数 VARCHAR(10),
  18.     创建时间 TIMESTAMP,
  19.     -- 设置外键约束
  20.     CONSTRAINT sc_fk FOREIGN KEY (学号) REFERENCES student(学号)
  21.     ON UPDATE CASCADE ON DELETE CASCADE 
  22. );
  24. INSERT INTO student VALUES (10010,    "张无忌", "经济系",    "张三丰"),
  25.                            (10011,    "令狐冲", "法律系",    "任我行"),
  26.                            (10012,    "杨过",    "法律系","任我行");
  28. INSERT INTO course VALUES  (10010, "高等数学",     95, NULL),
  29.                            (10010,    "大学英语", 87, NULL),
  30.                            (10010,    "计算机基础", 65, NULL),
  31.                            (10011,     "法理学", 77, NULL),
  32.                            (10011,      "大学英语", 87, NULL),
  33.                            (10011,     "法律社会学",65, NULL),
  34.                            (10012,     "法律社会学",95,NULL),
  35.                            (10012,    "法理学", 97,NULL),
  36.                            (10012,     "大学英语", 99,NULL);
  39. SELECT * FROM student;
  40. SELECT * FROM course;

capture_20210724094005078.bmp

问题：

1. 存在严重的数据冗余，数据重复:解决

  1. 数据添加存在问题，开设新的系添加进去，数据不合法
  2. 数据删除存在问题， 删除一个同学的信息，他所在系的信息也被删除

5.2.3 第三范式(3NF)

定义：在第二范式的基础上，任何非主属性不依赖于其他非主属性，即消除非主属性间的传递依赖

  1. -- 建立三张表，将上面的student主表拆成两张表
  2. -- 建立系表:主表主键
  3. CREATE TABLE depart(
  4.     系名 VARCHAR(10) PRIMARY KEY NOT NULL,  -- 学生表对应的主键
  5.     系主任 VARCHAR(10)
  6. );
  8. -- 建立新的学生表
  9. -- 相对于系表来书说：是从表；相对于课表来说：是主表
  10. CREATE TABLE newStudent(
  11.     学号 INT PRIMARY KEY NOT NULL, -- 课表对应的主键
  12.     姓名 VARCHAR(10),
  13.     系名 VARCHAR(10),   -- 外键到系表
  14.     -- 外键约束
  15.     CONSTRAINT nd_fk FOREIGN KEY (系名) REFERENCES depart(系名)
  16.     ON UPDATE CASCADE ON DELETE CASCADE 
  17. );
  19. -- 建立课表
  20. CREATE TABLE course(
  21.     学号 INT  NOT NULL,
  22.     课程名称 VARCHAR(10),
  23.     分数 VARCHAR(10),
  24.     创建时间 TIMESTAMP,
  25.     -- 设置外键约束
  26.     CONSTRAINT sc_fk FOREIGN KEY (学号) REFERENCES student(学号)
  27.     ON UPDATE CASCADE ON DELETE CASCADE,
  28.     CONSTRAINT newsc_fk FOREIGN KEY (学号) REFERENCES newStudent(学号)
  29.     ON UPDATE CASCADE ON DELETE CASCADE 
  30. );
  34. -- 插入信息时，要先插入主表中的主键信息，创建表时，也要先创建主表
  36. INSERT INTO depart VALUES  ("经济系",    "张三丰"),
  37.                            ("法律系",    "任我行");
  39. INSERT INTO newStudent VALUES (10010,    "张无忌", "经济系"),
  40.                               (10011,    "令狐冲", "法律系"),
  41.                               (10012,    "杨过",    "法律系");
  43. INSERT INTO course VALUES  (10010, "高等数学",     95, NULL),
  44.                            (10010,    "大学英语", 87, NULL),
  45.                            (10010,    "计算机基础", 65, NULL),
  46.                            (10011,     "法理学", 77, NULL),
  47.                            (10011,      "大学英语", 87, NULL),
  48.                            (10011,     "法律社会学",65, NULL),
  49.                            (10012,     "法律社会学",95,NULL),
  50.                            (10012,    "法理学", 97,NULL),
  51.                            (10012,     "大学英语", 99,NULL);                              
  54. -- 存在的问题：
  55. -- 1.存在非常严重的数据冗余(重复)：姓名、系名、系主任
  56. # 第二范式解决
  57. -- 2.数据添加存在问题：添加新开设的系和系主任时，数据不合法
  58. # 第三范式解决
  59. -- 添加计算机系，系主任
  60. INSERT INTO depart VALUES ("计算机系", "吴恩达");
  62. -- 3.数据删除存在问题：张无忌同学毕业了，删除数据，会将系的数据一起删除。
  64. -- 张无忌同学毕业了，删除数据
  65. DELETE FROM newstudent WHERE 姓名 = "张无忌";
  67. SELECT * FROM newStudent;
  68. SELECT * FROM depart;
  70. DROP TABLE newStudent;
  71. DROP TABLE depart;
  72. ALTER TABLE newStudent DROP FOREIGN KEY nd_fk;

capture_20210724121758357.bmp

问题：

  1.         ~~1.   存在严重的数据冗余，数据重复:解决~~
  3.         ~~2. 数据添加存在问题，开设新的系添加进去，数据不合法:解决~~
  5.         ~~3. 数据删除存在问题， 删除一个同学的信息，他所在系的信息也被删除：解决~~

6. MySQL多表查询

6.1 多表查询

  • 数据准备 ```sql

    创建部门表

    CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) );

INSERT INTO dept (NAME) VALUES (‘开发部’),(‘市场部’),(‘财务部’);

创建员工表

CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), gender CHAR(1), — 性别 salary DOUBLE, — 工资 join_date DATE, — 入职日期 dept_id INT, FOREIGN KEY (dept_id) REFERENCES dept(id) — 外键，关联部门表(部门表的主键) ) );

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘孙悟空’,’男 ‘,7200,’2013-02-24’,1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘猪八戒’,’男 ‘,3600,’2010-12-02’,2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘唐僧’,’男’,9000,’2008-08-08’,2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘白骨精’,’女 ‘,5000,’2015-10-07’,3); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(‘蜘蛛精’,’女 ‘,4500,’2011-03-14’,1);

  1. ![image.png](https://cdn.nlark.com/yuque/0/2022/png/25887408/1646710083922-0e55f9ea-d1fd-487e-b969-41c1294d218a.png#clientId=u813a368f-8b3f-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=106&id=u5da21a80&margin=%5Bobject%20Object%5D&name=image.png&originHeight=106&originWidth=172&originalType=binary&ratio=1&rotation=0&showTitle=false&size=5121&status=done&style=none&taskId=u39671015-ee94-4e9d-945a-ac89340a9b4&title=&width=172)![image.png](https://cdn.nlark.com/yuque/0/2022/png/25887408/1646710129522-829967b3-d56c-41a5-ab11-89ed61a0972a.png#clientId=u813a368f-8b3f-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=110&id=ue0bc4732&margin=%5Bobject%20Object%5D&name=image.png&originHeight=110&originWidth=473&originalType=binary&ratio=1&rotation=0&showTitle=false&size=13013&status=done&style=none&taskId=ub5d9812d-314e-4586-bc9b-6e154a8fd3c&title=&width=473)<br />多表查询的作用： <br />比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。 <br />如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。
  3. - **多表查询的分类**
  5. ![image.png](https://cdn.nlark.com/yuque/0/2022/png/25887408/1646710201996-dfd8bdfc-356c-4e01-aec6-120fb31a05c4.png#clientId=u813a368f-8b3f-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=143&id=u82ae5754&margin=%5Bobject%20Object%5D&name=image.png&originHeight=143&originWidth=333&originalType=binary&ratio=1&rotation=0&showTitle=false&size=21633&status=done&style=none&taskId=ucd608032-9d88-4003-8bcb-52520c1905f&title=&width=333)
  6. <a name="w5ivF"></a>
  7. ### 6.2 笛卡尔积现象
  8. 什么是笛卡尔积：<br />-- 需求：查询所有的员工和所有的部门  _select * from emp,dept;_<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/25887408/1646710291246-00514614-e2c3-43e8-b7e2-c3dfcd61ea23.png#clientId=u813a368f-8b3f-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=142&id=u3c98a2d4&margin=%5Bobject%20Object%5D&name=image.png&originHeight=142&originWidth=609&originalType=binary&ratio=1&rotation=0&showTitle=false&size=87888&status=done&style=none&taskId=u4e83d68d-4e72-49aa-8082-c8e9de135ff&title=&width=609)<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/25887408/1646710371849-140a0f8e-87dd-43a4-a500-06df0b8590b9.png#clientId=u813a368f-8b3f-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=379&id=u7e0d5411&margin=%5Bobject%20Object%5D&name=image.png&originHeight=379&originWidth=619&originalType=binary&ratio=1&rotation=0&showTitle=false&size=51013&status=done&style=none&taskId=u33a2f795-4ffd-40b9-b5dd-c2c75e8a541&title=&width=619)<br />我们发现不是所有的数据组合都是有用的，只有**员工表.dept_id = 部门表.id **的数据才是有用的。所以需要通过条件过滤掉没用的数据
  9. <a name="yU6ga"></a>
  10. ### 6.3 内连接 
  11. 用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：**从表.外键=主表.主键**
  12. <a name="ApjZ4"></a>
  13. #### 6.3.1 隐式内连接
  14. 隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定<br />**SELECT 字段名 FROM 左表, 右表 WHERE 条件**
  15. ```sql
  16. -- 隐式内连接
  17. SELECT * FROM emp, dept WHERE emp.`dept_id` = dept.`id`;

6.3.2 显式内连接

显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

  1. 确定查询哪些表

    1. SELECT * FROM emp INNER JOIN dept;

    image.png

  2. 确定连接条件

    1. SELECT * FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id`;

    image.png

  3. 确定查询条件

    1. SELECT * FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id` WHERE e.`name`="唐僧";

    image.png

    6.3.3 总结内连接查询步骤：

    1) 确定查询哪些表
    2) 确定表连接的条件
    3) 确定查询的条件
    4) 确定查询的字段

    6.4 左外连接

  • 左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

  1. -- 使用左外连接查询: 在内连接的基础上保证左表的数据全部显示
  2. SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.`dept_id` = d.`id`;

image.png

6.5 右外连接

  • 右外连接：使用 RIGHT OUTER JOIN … ON，OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

  1. -- 使用右外连接查询: 在内连接的基础上保证右表的数据全部显示
  2. SELECT * FROM emp e RIGHT JOIN dept d ON e.`dept_id` = d.`id`;

点击查看【music163】

MySQL高级

7. MySQL事务

事务

  1. 什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。

面试问题

  • 事务定义：在实际开发过程中，一个业务通常由多条SQL语句访问数据库才能完成，事务的执行是一个整体，所有的SQL语句都必须执行成功，如果有一条SQL语句出现异常，所有的SQL语句都要回滚，整个业务都执行时报错

事务的四大特性（ACID）

  1. 原子性（Atomicity）： 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。（转账时，一个人转出多少，收款方就增加多少）
  2. 一致性（Consistency）：事务完成时，所有的数据都必须保持一致状态（转账前后，两个人总金额不变）
  3. 隔离性（Isolation）：数据库系统需要提供隔离机制，保证事务不受并发操作的影响，独立的运行
  4. 持久性（Durability）：事务一旦提交或者回滚，在数据库中的改变就是永久的（数据是写在磁盘上的）

并发事务

并发事务问题是指多个并发事务执行过程中出现的为脏读、不可重复读、幻读等问题

  1. 脏读：一个事务读取到了另外一个事务还没有提交的数据
  2. 不可重复读：一个事务两次读取到的数据不相同
  3. 幻读：一个事物按照条件没有读到对应的事务，没有对应的数据行，但是插入数据时，发现了主键冲突，再次查询时仍然插不到数据

    脏读

    image.png

    不可重复读

    image.png

    幻读

    image.png

    事务的隔离级别

    为了解决并发事务所引发的问题，引入了数据库的隔离级别
    主要有4种：读未提交，读已提交，可重复读，序列化
  • 读未提交是最低级的隔离级别，脏读，不可重复读，幻读都会出现
  • 读已提交解决了脏读问题
  • 可重复读解决了不可重复读的问题，但还是会出现幻读的问题
  • 串行化解决了所有的并发事务问题

mysql的默认隔离级别是可重复读
image.png
事务隔离级别越高，数据越安全，但是性能越低。

8. MySQL存储引擎

1 MySQL体系结构

image.png
mysql体系结构分为四个部分：连接层、服务层、引擎层、存储层

  • 连接层：用于客户端和mysql服务端的链接服务，完成链接处理。授权认证及相关的安全方案
  • 服务层：完成mysql大多数的核心功能，包括sql接口，解析器，查询优化器和缓存
  • 引擎层：负责mysql数据的存储与提取，与存储引擎进行通信
  • 存储层：数据存储层，数据真正存储的位置，将数据、索引、log等文件写在磁盘上

2 MySQL存储引擎

1. InnoDB存储引擎

image.png

  1. create table innodb(
  2.     username varchar(10),
  3.     password varchar(10)
  4. )engine=innodb;

a1.png

  1. show engines;

Snipaste_2022-04-11_22-02-53.png

InnoDB逻辑存储结构
image.png

2. InnoDB与MyISAM的区别面试问题

  • InnoDB支持事务，MyISAM不支持事务
  • InnoDB支持表锁和行锁，MyISAM只支持表锁不支持行锁
  • InnoDB支持外键，MyISAM不支持外键
  • InnoDB的B+树主键索引叶子结点存储的是数据本身，MyISAM的叶子结点存储的是数据的物理地址

3. InnoDB存储引擎

9. MySQL索引

1. 索引介绍

索引：索引是一种数据结构，用于帮助mysql快速的获取数据
在没有索引的情况下，按条件查询一条数据就要扫描整张表！

索引的优势：

  1. 使用索引可以提高数据检索的效率，减少磁盘的IO成本
  2. 通过索引对数据排序更加高效，可以降低排序时CPU的消耗

索引的劣势：

  1. 索引需要额外占用内存空间的
  2. 使用索引在进行增删改时，既要对更新原表，又要更新索引，这样就降低的数据更新时的效率

由于对于内存不值钱，而且对于数据库的读操作远多于增删改的操作，所以使用索引性价比更高

2. 索引结构

mysql最常用的索引包括B+树索引和hash索引
image.png

1. BST索引

image.pngimage.png
二叉搜索树的平均时间复杂度为O(log n)
使用二叉搜索树作为索引会有两个缺点：

  1. 当数据顺序存储时，二叉树会退化为链表，时间复杂度为O(n),查询性能大大降低
  2. 数据量较大时，树会非常深，树越深IO次数越频繁，因为mysql中数据是存在磁盘里的，cpu在查找数据时都要进行一次IO，因为树是按链表结构存在磁盘里的，不能够一次加载一大片连续的内存！

我们知道选择红黑树可以解决二叉树退化倾斜的问题，因为红黑树可以自平衡，但是大量的数据还是会导致树过深，还是会出现磁盘IO频繁的问题
image.png

2. B树索引

B树是一种多路平衡查找树，也就是多叉树，多叉树的每个结点会有多个孩子，同样的数据量，结点的分叉越多，最后形成的数越矮胖
image.pngimage.png

B树

B树的一个结点有多个孩子结点，加入一个B树的分叉数为3时，每个结点上最多可以存储2个数据，超过2个的话，就会分裂结点
MySQL - 图36
一个三叉的B树，查询过程如下：
MySQL - 图37
我们可以看到上面的查询只需要3次，因此只需要发生3次磁盘IO，因此存储磁盘中的数据使用B树的查询效率要比二叉树高的多

B树的问题
  1. B树中索引和记录都存在一个结点中，用户的记录数据的大小可能远远大于索引，因此在一页内存中能够存储的索引就会很少，导致B树的分叉很少，又会使得树很深！
  2. 我们在查询一条记录时，如记录A，在与非A结点比较时，只需要它的索引数据就行了，但是B树存储了记录，因此会把大量没用的记录读进内存里
  3. B树在进行范围查询时，需要使用中序遍历，这样又会频繁的进行磁盘IO操作！

注意：在Linux操作系统中，内存从磁盘中读取数据的最小操作是页，一页是4KB；也就是说CPU从内存中读取的数据是4KB，即连续的一页！
B+树就解决了B树存在的上述问题

3. B+树索引

B+树

B+树是B树的升级，与B树相比，特点如下：

  1. 为了解决结点中存储大量的无用数据，B+树的非叶子结点中只存储索引
  2. 所有的索引都会在叶子结点中出现，结点中间由双向链表所链接，便于范围查询

注意：

  • 非叶子结点的索引也会在叶子结点中，并且是叶子结点中的最小索引或最大索引
  • 非叶子结点中有多少个子节点，就有多少个索引

image.png

B+树的性能
  1. 单点查询效率

B树最快可以在O(1)的时间代价内查到，B+树所有的数据都要在O(logn)的级别才能查到，从平均时间复杂度 上来看B树的单点查询会稍快一点。但是B树的查询波动较大，有的数据存在非叶子结点上，访问到非叶子结 点就可以查到数据；有的数据存在叶子结点上，就得查到叶子结点才能访问到索引! 而且B树会比B+树更深， 磁盘的IO次数要多一些，所以实际上单点查询B树的效率不一定比B+树高！

  1. 插入和删除效率

    B+树中存储了大量的荣誉结点，在删除一个结点是，可以直接从非叶子结点删除，甚至可以不动非叶子结
    点删除的非常快，如下面从B+树删除0004结点所示：
    MySQL - 图39
    直接删除，树的形状都没有改变🤤

但是B树的删除就会造成树复杂的形状变化😶，如下图所示：
MySQL - 图40
B+树的插入也是如此，插入可能会造成结点的分裂，但是最多只会更改树的一条路径，由于B+树的结点分
裂，B+树可以自动平衡，而无须红黑树的复杂旋转操作！
因此B+树的插入和删除效率高于B树的效率

  1. 范围查询效率

B+树的非叶子结点中使用双向链表进行链接，范围查询只需要从左到右进行遍历即可
B树的范围查询需要使用树的后序遍历才可，这样就会涉及到多次的磁盘IO
因此B+树的范围查询效率高于B树的范围查询效率

4. 从数据页的角度看B+树

InnoDB中数据的存储结构

mysql中数据是按行存储的，但是内存从数据库中读取数据却不是按行来读取的，如果每次IO都只读一行数据的话，效率未免也太低了🛴。在InnoDB引擎中，数据是数据页为单位进行读写的！InnoDB数据页的默认大小是16KB,也就是说数据库的IO操作最小单位为16KB！

一个16KB的数据页的结构如下图所示：
image.pngimage.png

文件头中存储两个指针，用来指向上一个数据页和下一个数据页，即链表的前后指针
image.png

用户记录User Record存储数据库中真正的数据，数据库中的数据按照主键索引排序组成一个单链表
PS： mysql中数据页之间采用双链表进行链接，数据页间的数据使用单链表进行链接

当定位到数据库中的一个数据页时，CPU就会将这页数据读到内存中，然后再链表中查询数据。我们知道单链表的查询时间复杂度为O(n),为了使得检索的速度更快，数据页中维护了一个页目录加速索引！
image.png

一个数据页中存储的多条数据使用单链表链接，在将所有的数据分成不同的组，维护一个页目录来索引这些分组，在查询某个特定的数据时，先使用二分查找定位到哪一个分组，然后再遍历分组里的链表来定位记录，使用分组二分查找，可以加速但单链表的遍历！

5. mysql为什么要使用B+树作为索引

  1. B+树相较于普通的二叉树，树的深度更低，磁盘IO更少，搜索效率会更高
  2. B+树相较于B树，B+树的非叶子结点只存放索引，B树既存放索引又存放数据，因此在数据相同的情况下，B+树的深度高低，更加的矮胖，磁盘的IO次数更少
  3. B+树中存放着大量的冗余结点，插入删除操作对树的形状变化不大，而B树的插入删除操作会涉及到复杂的形状变换
  4. B+树的叶子结点中存放数据，并使用双链表进行链接，范围查询时，只要遍历双链表即可，B树中非叶子结点中也存放数据，范围查找时，需要后序遍历，涉及到多次的磁盘IO效率不如B+树

3. 索引分类

1. 索引分类

按数据结构来分

mysql常见索引有3种：B+树索引，hash索引，Full-text索引
image.png

按物理存储来分

索引分为聚簇索引和二级索引

  1. 聚簇索引：聚簇索引又叫作主键索引，聚簇索引将数据和索引放在一个B+树中，B+树的叶子结点存储了索引和数据；数据表中必须有一个聚簇索引，而且只能有一个！
  2. 二级索引：二级索引中B+树的叶子结点存储的是索引值和主键值，要查到对应的数据，必须再次根据主键值查询聚簇索引，这个过程称之为回表

image.png

覆盖索引：如果在二级索引中可以直接查询到结果，就无须回表查询，这个过程称为覆盖索引

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引

  1. 主键索引：就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
  2. 唯一索引：建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
  3. 普通索引：就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

  4. 前缀索引：对字符串前几个字符创建的索引

    按字段个数分类

    从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

  5. 建立在单列上的索引称为单列索引，比如主键索引

  6. 建立在多列上的索引称为联合索引

    4. 索引失效

    6种情况下索引可能会失效：

  7. 在使用左模糊匹配或者左右模糊匹配时，也就是like %xx或者like %xx%时，索引会失效

    因为左侧模糊不确定，导致找不到对应的索引只能做全表扫描

  8. 在查询条件中对索引使用函数时，索引也会失效

索引中保存的是原始字段，没有对字段使用函数的索引，索引自然会失效

  1. 对索引进行表达式运算时，索引也会失效
  2. 联合索引需要遵循最左匹配原则，按照最左优先的原则对索引进行匹配，否则索引会失效
  3. 查询条件中包含or时，or前面的条件有索引，or后面的没有索引，索引会失效
  4. 字符串类型字段使用，查询条件不加引号，索引会失效

最左匹配原则：联合索引在匹配时，会优先匹配最左侧索引，最左侧匹配成功才会匹配后面的索引，最左边的索引必须存在，这与SQL中查询条件的顺序无关！

5. 索引优化

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？
我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
使用覆盖索引的好处：不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

不过，前缀索引有一定的局限性，例如：

  • order by 就无法使用前缀索引；
  • 无法把前缀索引用作覆盖索引；

    主键索引最好是自增的

  1. 如果我们是用主键自增，在添加一条新的数据时，会直接添加到当前索引结点后面的位置，不需要移动到其他位置，当页面写满时，直接开辟一个新的页。每次的添加都是追加操作，不会发生页分裂
  2. 如果使用非主键自增，每次插入的索引值都是随机的，可能会页分裂。造成大量的内存碎片，影响查询效率

索引最好设置为NOT NULL

防止索引失效

count(*)与count(1)

image.png

索引范围：

  • ALL（全表扫描）；
  • index（全索引扫描）；
  • range（索引范围扫描）；
  • ref（非唯一索引扫描）；
  • eq_ref（唯一索引扫描）；

10. MySQL锁

1. 锁概述

mysql中的锁按锁的力度可以分为3类：全局锁、表级锁、行级锁

  1. 全局锁：锁住数据库中的所有表
  2. 表级锁：每次对表操作时，锁住整张表
  3. 行级锁：每次对行操作时，所住对应的行

    2. 全局锁

    全局锁：就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语
    句，已经更新操作的事务提交语句都将被阻塞。

全局锁的应用场景：做全库的逻辑备份，对所有的表进行锁定，保证数据的完整性！

全局锁的特点：
数据库中加全局锁，是一个比较重的操作，存在以下问题：

  1. 如果在主库上备份，备份期间不能执行更新操作，业务基本上就会停摆
  2. 如果在从库上备份，备份期间不能执行主库同步来的bin log文件，导致主从延迟


既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

3. 表级锁

表级锁：mysql表级锁每次操作锁住整张表，锁的粒度大，并发度最低，表级锁主要分为三类：表锁、元数据锁、意向锁

1. 表锁

表锁分为两类：

  1. 表共享读锁（read lock）
  2. 表独占写锁（write lock）

语法：

  • 加锁：lock tables 表名... read/write。
  • 释放锁：unlock tables / 客户端断开连接 。

读锁

读锁会阻塞其他客户端的写操作，不会阻塞读操作！
image.png

同时当前客户端给表加上读锁，自己也不能够对表进行写操作，但它不是阻塞住，而是报错

  1. mysql> update user set head_img='xxx' where id = 18;
  2. ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
  3. mysql> unlock tables;

其他客户端被阻塞的写操作会在锁释放后执行

  2. mysql> update user set head_img='xxx' where id = 18;
  3. -----阻塞中----
  5. -----锁被释放掉---------
  6. mysql> update user set head_img='xxx' where id = 18;
  7. Query OK, 1 row affected (34.73 sec)        # 阻塞了34秒
  8. Rows matched: 1  Changed: 1  Warnings: 0

写锁

写锁同时阻塞其他客户端的读操作和写操作
image.png
但是写锁自己的写操作是可以正常进行的，不会报错！

结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

2. 元数据锁

元数据锁：Meta Data Lock,元数据锁主要保护对数据库结构结构修改时数据的一致性
元数据锁加锁由系统自动控制，在无需显示使用，在访问一张表时会自动加上，可以避免DML和DDL的冲突
image.png

  • 当执行SELECT等DQL语句时，添加共享读锁，修改表结构的DDL语句将会被阻塞
  • 当执行INSERT等DML语句时，添加共享写锁，修改表结构的DDL语句将会被阻塞
  • 当执行ALTER等DDL语句时，添加排他锁，所有DQL，DML语句都将被阻塞

3. 意向锁

意向锁：避免行锁和表锁的冲突
如果一张表中有一行被加上了行锁，这是再为他加上表锁时，就要逐行检查当前的表锁与行锁是否冲突，这样性能比较低。mysql在加上行锁时，在表上加一个意向锁，这样再加表锁时，直接判断表锁与行锁是否冲突即可！

意向锁分为：

  1. 意向共享锁：InnoDB的行上加上共享锁之前，会给表添加一个意向共享锁，与表锁的共享读锁兼容，与独占写锁排斥
  2. 意向排它锁：InnoDB得到上加上独占锁之前，回给表添加一个意向排它锁，与表锁的读锁写锁都排斥

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

意向锁的目的是为了快速判断表里是否有记录被加锁

4. 行级锁

行级锁：每次操作行时，锁住相应的行数据，锁的粒度小，并发度高，应用在InnoDB引擎中
InnoDB中行锁加在索引上，而不是数据上！

行级锁分为3类：行锁，间隙锁，临键锁

  1. 行锁（Record Lock）：锁住单个记录，防止其他事务进行update和delete

image.png

  1. 间隙锁（Gap Lock）：锁定索引记录的间隙，确保索引的间隙不变，防止其他事务insert，产生幻读

image.png

  1. 临键锁（Next-Key Lock）：所锁住单个数据和前面的间隙，是行锁和间隙锁的组合

image.png

行锁

InnoDB的行锁包含两种类型：

  1. 共享锁：允许一个事务读一行，阻止其他事务的排它锁
  2. 排它锁：允许获取排他锁的事务更新数据，阻止其他失去的共享锁和排它锁

image.png

常见的SQL语句加锁的情况如下：
image.png

间隙锁与临键锁

对记录加锁时，加锁的基本单位是临键锁 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

11. MySQL日志

mysql日志主要是为了解决事务的四大特性！

  1. redo log: 重做日志，记录事务提交时数据页的物理修改，用来实现事物的持久性！
  2. undo log: 回滚日志，记录数据被修改前的数据，实现了事务的原子性和多版本并发控制
  3. bin log: 归档日志，用于数据备份和主从复制,类似于redis的AOF

其中使用redo log和undo log两份日志就可以实现事务的原子性、一致性与持久性，使用锁和多版本并发控制（MVCC）就可以实现事务的隔离性！
image.png

1. redo log

redo log: 重做日志，记录事务提交时数据页的物理修改，用来实现事物的持久性！

在一个事务中每执行一条DML语句之后就会将数据页中的物理变化记录在redo log buffer中，在事务提交前将redo log刷到磁盘中，然后磁盘就可以根据redo log恢复数据！

:::info 为什么要用redo log？ :::

  1. redo log的作用：redo log记录了数据页的物理变化，事务提交后数据页所有的信息都记录其中，在后台线程将缓冲池中的脏页刷到磁盘中出现错误时，就可以使用redo log来恢复数据，这样就可以在mysql出现错误的情况下实现事务的持久性！
  2. 在InnoDB引擎的内存中，使用buffer pool缓冲池来缓存数据页，在对磁盘中的一个数据行进行修改时，会将整个数据页从磁盘中读到缓冲池中去，然后再缓冲池内修改对应的数据页，并将对应的数据页设置为脏页，mysql后台进程会批量的将缓冲池中的脏页刷到磁盘中去！如果脏页刷新成功，就将redo log中的记录删除，刷新失败，就使用redo log来恢复数据

image.png
image.png

:::info 为什么InnoDB不在事务中直接将脏页刷到磁盘中，而使用redo log呢？ :::

  1. InnoDB没有在事务中直接将脏页刷盘，而是启用后台线程将脏页刷到磁盘中，这是因为我们操作的脏页一般都是随机读取磁盘的，而且脏页数据较大，直接在事务中刷盘会耗时太久，导致一个事务长时间不能结束！所以使用后台线程刷盘，但是后台线程如果出现了错误，会导致已经提交的事务操作的数据没有写到磁盘里，出现了错误！这是就需要使用redo log了
  2. redo log实在事务中刷到磁盘中的，那redo log的刷盘不会耗时很久吗？与脏页比肯定会耗时很短，因为redo log记录的是也中数据行的变化，记录的是日志文件，写入磁盘是顺序写的，效率一定比数据量更大，随机写的脏页刷到磁盘中效率更改！

2. undo log

undo log: 回滚日志，记录数据被修改前的数据，实现了事务的原子性和多版本并发控制

与redo log不同，undo log记录的不是数据的物理变化，而是逻辑日志。例如事务里insert一条数据，就会在undo log中记录一条对应delete语句

当事务执行出现错误，发生回滚时，就读取undo log中的数据，执行里面的操作就可以恢复原来的数据了

undo log的两大作用：

  1. 实现事务回滚，保证事务的一致性
  2. undo log是实现多版本并发控制MVCC的关键因素之一

3. bin log

bin log: 归档日志，用于数据备份和主从复制,类似于redis的AOF

bin log中记录的是逻辑日志，记录了mysql中执行的SQL语句，它的文件格式有3种：STATEMENT、ROW、MIXED，多条SQL语句操作同一行数据STATEMENT只会记录最后一条，ROW会记录所有的SQL语句，MIXED就是STATEMENT和ROW的混合使用

:::info 如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？ :::

  1. 不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。
  2. 因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。
  3. binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

mysql主从复制

  1. 写入bin log: 主库在收到客户端提交事务的请求后，会先将bin log写入磁盘中，提交事务并更新磁盘数据
  2. 同步bin log: 主库通过网络IO将bin log文件发送至从库，从库创建一个专门的IO线程接收主库发来的bin log并写入中继日志中，在返回给主库同步成功的响应
  3. 回放bin log: 更新存储引擎中的数据

image.png

bin log刷盘
事务执行过程中，会把日志先写在bin log cache中，事务提交的时候，在将bin log cache刷到bin log文件中

4. MVCC

1. MVCC基本概念

当前读

当前读读取的是记录的最新版本，也就是数据库中真实存储的数据
语法：select * from used in share mode

快照读

快照读读取的是当前事务中数据的可见版本，其他事务修改的版本快照读不可见
语法：select * from user

在可重复读RR隔离级别下，使用快照读可以读取到当前事务下可见的数据版本，隔离开其他事务对数据库的更改

MVCC

MVCC:多版本并发控制（Multi-Version Concurency Control）,指InnoDB引擎维护一个数据的多个版本，从而使得多个事务间的续写没有冲突！MVCC的实现主要依赖于于数据库记录的三个隐藏字段、undo log日志和readView!

隐藏字段

image.png
当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了
这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

  1. DB_TRX_ID: 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
  2. DB_ROLL_PTR: 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
  3. DB_ROW_ID: 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

2. undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即
被删除。

版本链

有一张表原始数据为：
image.png

  • DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是

自增的。

  • DB_ROLL_PTR ： 由于这条数据是才插入的，没有被更新过，所以该字段值为null。

然后，有四个并发事务同时在访问这张表。

第1步：事务2修改数据

image.png
事务2执行修改语句时，会记录undo log日志，定在记录中更新事务ID，并更新回滚事务，即上一个事务ID为1
image.png
image.png

第2步：事务3修改数据

image.png
事务3修改数据时，也会记录undo log日志，更新记录，并记录本次事务的ID，更新回滚事务的ID

image.png
image.png

第3步：事务4修改数据

image.png

image.png
image.png :::info 最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条
记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。 :::

readview

readview:读视图，它是快照读SQL执行时读取MVCC哪个版本数据的依据，记录当前获取事务的id

readview中包含了四个核心字段：

  1. m_ids: 当前活跃的事务ID集合
  2. min_trx_id: 最小活跃事务ID
  3. max_trx_id: 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
  4. creator_trx_id: ReadView创建者的事务ID

在readview中就规定了版本链数据的访问规则：
trx_id 代表当前undolog版本链对应事务ID
image.png

不同的隔离级别，生成ReadView的时机不同：

  • READ COMMITTED ：RC级别下，在事务中每一次执行快照读时生成ReadView。
  • REPEATABLE READ：RR级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

3. MVCC原理

RC隔离级别（读已提交）

我们就来分析事务5中，两次快照读读取数据，是如何获取数据的?

在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读
都会生成一个ReadView，那么两次生成的ReadView如下：
image.png
两份readview的m_ids不同，导致读取的undo log日志文件版本不同，进而导致同一个事务中两次读取数据不一致，即不可重复读！

第一次快照读的读取过程

image.pngimage.png
image.png
在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

  1. 先匹配image.png：这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条！
  2. 再匹配第二条image.png：这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。
  3. 再匹配第三条image.png：这条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。

第二次快照读的读取过程

image.pngimage.png
image.png

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

  1. 先匹配image.png：这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条！
  2. 再匹配第二条image.png：这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。也就是将3带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据。

RR隔离级别（可重复读）

可以看到：
在读已提交隔离级别下，就是因为readview记录的活跃事务id集合不同，导致读取undo log版本链中的日志文件不同，进而导致两次读到的数据不一致

在RR级别下，一个事务中复用同一个readview，这样从undo log中读到的日志文件版本一定一直，多次读取读到的结果也一定是一样的!
image.png

MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。
而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。

image.png