MySQL数据库管理与SQL操作实战指南-优快云博客

第一节 Mysql数据库：

对数据的管理

当想让某个用户可以让任何用户都访问得到，将他的ip地址设置为%

1.DCL --sql语句：

创建用户和修改密码：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yelQ4v6r-1681790228534)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658136585932.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vhlIf2DR-1681790228535)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658136617978.png)]

一个叫ssfaf的用户访问cgboy，密码也为root，cgboy的ip为任何可连接，所以连接成功。

授权和撤销授权：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CWpfbB7e-1681790228536)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658138747874.png)]

查看授权：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0zZbIB4u-1681790228536)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658139160967.png)]

删除用户：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AyI6dZZN-1681790228536)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658139195247.png)]

第二节 DDL语法（数据定义语言）：

主要是用在定义或改变表（table）的结构，主要的命令有create，alter，drop等。

创建表的基本语法：

常用的数据类型：

1.整形：

tinyint 1字节（-128 ~ 127）

smallint 2字节（-32768 ~ 32767）

mediumint 3字节

int 4字节

bigint 8字节

**unsigned 😗*无符号（取值范围全为正数），如tinyint unsigned，它的取值范围为255，其他的以此类推

signed :有符号（取值范围包括负数），默认为有符号的

int(4):4代表宽度，宽度的作用需要配合zerofill进行使用：

如： int（4） unsigned zerofill 查询结果：0001 0002 1000

三个数据的宽度为4位，若不加如 zerofill 则为：1 2 1000

2.浮点型：

float（m, d）4字节，m总长度，d小数位

double（m,d）8字节

decimal(m ,d ) ：存储为字符串的浮点数，对应java的Bigdecimal

例如：float（5， 3）：数据总长五位，小数三位

如123.456789，最后显示为99.999，总数长为5为，满足小数为3位，但整数就有三位，所以系统会自动取个值，我们在设置时要格外注意。

12.3456显示为： 12.346，小数末位进行四舍五入。

字符串数据类型（重点）：

（1）char 和 varchar：

char：（每个数据的长度一般都是相同的）

char (n): n代表标准固定长度几个字符（定长的），255字节，最多255个字符。

char(10)，定义了长度位10，当输入“abc”，那么这个数据还是占了10个字符的空间（内部有七个空字节），当输入的字符数超过了定长的长度时，char会自动截取超出的字符，而且当存储char值时，Mysql会自动删除输入的字符串末尾的空格。

char适合存储长度较短，一般固定长度的字符串，如性别。

varchar：（数据的长度不同）

varchar（n）： n代表最多可以存储几个字符（可变长的）65535字节，最多65535个字符。

因为是变长的：var（10）；输入”abc“，那么它的实际长度是3个字节。

varchar还需要用1个或2个额外字节来记录字符串的长度，如果varchar最大长度小于等于255，则用1个字节表示长度，否则使用2个字节表示长度.

char类型每次修改的数据长度相同，空间不会发生太大改变，所以修改效率高。

varchar类型每次修改的的数据长度不同，假设修改的数据长度的变大了，那么又要重新分配空间，所以修改效率低。

varchar和text（文本类型,最大65535字节）：

text不可以设置默认值。varchar可以。

text类型长度都不能大于65535字节，所以它支持溢出存储，text只会存储前768字节在数据页中，而剩余的数据则会存储在溢出段（溢出段：硬盘分配的空间）中。而varchar在特别大的情况下，也会进行溢出存储。

根据存储的实现：可以考虑使用varchar代替text，因为varchar存储更具弹性，存储数据少的话性能更高。

varchar的存储超过255后，存储机制和text的存储机制相同.

一般情况能用cha就用char,cahr不合适就用varchar,如果文本数据实在太大,

如果存储的数据大于64k后，就必须使用mudiumtext(16 MB)，longtext(4GB)了。

日期和时间数据类型:

date: 日期 2022-4-1

time: 时间 18:52

datetime: 日期时间 2022-4-1 18:52

timestamp: 自动存储记录修改的时间

year: 年

3. 建表约束:

(1)非空约束:(NOT NULL)

表中的某一列设置了非空约束就必须要填入数据,不然报错.

(2)唯一约束:(UNIQUE)

表中的某一列设置了唯一约束,该列的数据不能重复,不然报错.

(3)主键约束: (PRIMARY KEY),通过主键来找到唯一的信息

自带非空,唯一,索引约束,去除索引.

(4)默认约束(DEFAULT)

如果设置的该列没有填入数据,系统则会填入一个默认值

(5)外建约束(FORIGN KEY)

假设一个表A中设置好了订单编号,如果另外表B或者本表中某一列使用外键约束,那么这一列中只能填入表A的订单编号,否则报错,这个约束将表和表的关系连接了起来.

3.1创建表(重点)😗**********************

-- 创建作者表
create table author(
    
-- 给id列设置主键约束:
aut_id int primary key,
-- 另一种写法:
-- primary key(aut_id)

-- 给名字列设置非空约束
aut_name varchar(50) not null,

-- 给性别列设置默认约束
gander char(1) default '男',

-- 国家列
country varchar(50),

-- 生日列,每一个列和列之间加逗号,最后一列不加逗号
birthday datetime 
 );

刷新后, 此时navicat显示出表:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-08Kl81VN-1681790228536)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658214380292.png)]

-- ydlclass`.`book`保证选定数据库是ydlclass,并创建一个book表
create table `ydlclass`.`book`(
  -- 给id列设置了自动递增的主键约束
	id int primary key auto_increment,
-- 书名列
`name` varchar(50) not null,
 -- 因为条形码列是唯一的不能重复的,且必须要存在,所以设置非空,和唯一约束
	bar_code varchar(30) not null unique,
-- 作者id列,
`aut_id` int  not null,
-- 让作者的id出自上面的作者表(author),所以需要外键约束
-- 使用外键引用了作者表的作者id
foreign key (aut_id) references author(aut_id)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0mXcJK00-1681790228536)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658215944123.png)]

4.主键约束(primary key):

是mysql使用最频繁的约束,一般都会在表中设置一个主键,主键分为单字段主键和多字段联合主键:

单字段主键:

primary key(aut_id),

aut_id int primary key,

多字段联合主键:

primary key(aut_id, aut_name),

每个表只能定义一个主键

主键值必须唯一,不能为null,不能重复(非空型,唯一性)

一个字段名只能在联合主键字段表中出现一次,

联合主键不能包含不必要的多余字段,当把联合主键的某一字段删除后,如果剩下的字段构成的主键仍然满足唯一性原则,那么这个联合主键是不正确的,这时最小化原则.

4.修改表的结构(alter):

(1)增加列:

-- 给 anthor表增加爱好,地址列
alter table author add (hooby varchar(20),address varchar(50)),

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xpGGsG5a-1681790228537)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658216970934.png)]

(2)修改属性:

-- 修改列属性:修改地址列的varchar的长度
alter table author modify address varchar(100);

点击设计表,显示出address的varchar字长为100:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iqqyclPg-1681790228537)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658217245278.png)]

-- 修改表名和属性:将author表中address改为addr,且varchar长度改为50
alter table author change address addr varchar(50);

刷新后点击设计表显示出:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5R5fjTdZ-1681790228537)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658217568848.png)]

(3)删除列:

-- 删除表中的addr列
alter table author drop addr;

刷新后点击author表显示:addr被删除了:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ooRwc8L7-1681790228537)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658218106712.png)]

(4)修改表名:

-- 修改表名:将表author名字改为authors
alter table author rename authors;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U7VCskNp-1681790228537)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658218258083.png)]

(5)删除表(重点)😗*****************

-- 删除表:将user表删除
drop table `user`;

(6)查看表结构:

-- 查看表结构:
desc authors;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xbSzddpz-1681790228538)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658218512081.png)]

(7)查看所有的表:************

-- 查看当前所有的表:
show tables;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tKoQ3e3q-1681790228538)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658218647535.png)]

5 . DML数据操作语言:(重点):

该语言用来对表记录进行操作(增,删,改),不包含查询.

查询属于DQL语言:做了解:

select * from authors;

(1)插入数据:(insert)

-- 插入数据
insert into `authors` (aut_name,gander,country,birthday,hooby) values('ccg','女','漂亮国','1995-5-20','吃饭');//因为id是自动递增的,所以不用写

简洁的写法:必须把所有参数写上:

insert into `authors`  values(2,'ccg','女','漂亮国','1995-5-20','吃饭');

给多个插入数据(更方便):

insert into `authors`  values(1,'cc','男','漂亮国','1995-5-20','吃饭'),(2,'cc','男','漂亮国','1995-5-20','吃饭');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KClQr9Pm-1681790228538)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658221137672.png)]

(2)修改数据:(update)

-- 修改数据(会将表中的名字都改成了bob)
update `authors` set aut_name = 'bob';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5axvr1Xa-1681790228538)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658221063582.png)]

使用where关键字解决:

-- 标注了在那个id下修改
update `authors` set aut_name = 'cc',country = '丑国' where aut_id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oYf9cQM3-1681790228538)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658221399535.png)]

(3)where的用法:

将id>1的修改了:大于小于等于一次类推:

update `authors` set aut_name = 'cc',country = '丑国' where aut_id > 1;

将id1和3修改: 不是区间表达

update `authors` set aut_name = 'pupu',country = 'China',hooby = '红领巾' where aut_id in (1,3); (in关键字还可运用到字符串上)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-twtvzUJg-1681790228538)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658222250587.png)]

将男的都修改了:

update `authors` set aut_name = 'pupu',country = 'China',hooby = '红领巾' where gander = '男';

其他的此参数一次类推:

注意(*************************):

在判断表中的有的数据是否为空的时候不能写成= null,必须是is null

update `authors` set aut_name = 'pupu',country = 'China',hooby = '红领巾' where birthday is null;

还可用于逻辑运算:

与:and ,或 :or

将国家是cg,爱好是红领巾的修改

update `authors` set aut_name = 'pupu',country = 'China',hooby = '领巾' where country = 'cg' and hooby = '红领巾';

将国家是cg或者爱好红领巾的修改

update `authors` set aut_name = 'pupu',country = 'China',hooby = '领巾' where country = 'cg' or hooby = '红领巾';

将id 1 到3之间的修改:(包括1和3)开区间表示

update `authors` set aut_name = 'pupu',country = 'China',hooby = 'd领巾' where aut_id between 1 and 3;

(4)删除数据:(delete from)

全删:

delete from authors;

删除指定的:

delete from authors where aut_id = 1;

使用where删除与修改类似,一次类推.

(5)删除表(truncate):

删除表后,会迅速重新建立一个一模一样表,但是数据都没有了,类似与初始化了

truncate table authors;

第三章 DQL数据查询语言

重点：DQL是我们每天接触编写最多也是最难的sql，该语言用来查询记录，不会修改数据库和表结构。

一. 单表查询（重点）

1.列的相关操作：

（1）查询

1> 查询所有列

其中＊表示查询所有列，而不是所有行的意思:

select * from `student`;

2> 查询指定的id，name列：

select 	`id`,`name` from `student`;

数量类型的列，查询的过程还可以乘除加减运算：

查询过程中，给年龄+1：

select 	`id`,`name`,`age`+1,`gander` from `student`;

– 将表中一个数据的age设置为了null，null加任何数只能等于null。字符串加任何数字，都是那个数字，字符串会被当作0

-- ifnull（`age`,0）函数：如果age为null,则age为0，
-- mysql中任何书加null都为null，字符串加任何数字，都是那个数字，字符串会被当作0
select 	`id`,`name`,ifnull(`age`,0),`gander` from `student`;

3> 别名：

-- 别名：可以把某一列起个方便记的别名：
-- 如,ifnull(`age`,0)看着很不好记忆，则
-- ifnull(`age`,0) as `age`,别名为age
select 	`id`,`name`,ifnull(`age`,0) as `age`,`gander` from `student`;

-- 给id，name其别名：编号，姓名（ifnull是函数结构所以用as来进行别名）
select 	`id`'编号',`name`'姓名',ifnull(`age`,0) as `age`,`gander` from `student`;
-- 也可以不加引号，直接写别名：
select 	`id`编号,`name`姓名,ifnull(`age`,0) as `age`,`gander` from `student`;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0jN7UqX2-1681790228539)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658370721430.png)]

4> 条件控制查询：使用where（where一般是放在from后面），between

查id为3的数据：

select * from `student` where id = 3;

查1到3之间，且age>15的人：

select * from `student` where id between 1 and 3 and age > 15;

查1到7之间，或age>15的人：

select * from `student` where id between 1 and 3 or age >15;

5> 模糊查询：like

如：将名字姓张且名字只有两个的数据查询：like关键字

select * from `student` where name like '张_';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hAzs5koV-1681790228539)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658371794172.png)]

如：将姓张的都查出来：

select * from `student` where name like '张%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sf7gfMwJ-1681790228539)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658372101986.png)]

2.distinct关键字：去重

做去重的工作，如果数据中有完全相同，一模一样的数据，那么使用该关键字就能去重复：

如图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7fWtpIeD-1681790228539)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658372795640.png)]

使用distinct关键字后：

select distinct `name`,`age`,`gander` from `student`;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IOY0XcfL-1681790228539)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658372810757.png)]

3.排序：order by关键字

-- 按照年龄排序，order by默认是正序排列
select * from `student` order by age;

注意： order by默认是正序排列（asc，可以不写）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zUbLxwtr-1681790228540)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658373082244.png)]

倒序排列：（desc）

select * from `student` order by age desc;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DJnyJDhB-1681790228540)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658373188481.png)]

4 . 聚合函数

把多条数据聚集在一起，形成我想要的结果，名字不能聚合

将所有的学生的年龄聚合起来，得出一个最大年龄的学生

select max(age) from student ;

-- 最小年龄
select min(age) from student ;
-- 所有年龄求和
select sum(age) from student ;
-- 年龄平均值
select avg(age) from student ;

也能与where等连用：

将所有id大于5的学生聚合起来，得出一个年龄最大的学生：

select max(age) from student where  id > 5;

我们如果统计真实的表记录条数，最好不要用可以为空的列：

查询列有多少行：（11行）

select count(*) from student;

5 . 分组查询：group by

注意：分组一般要配合聚合。分组之后，查询条件不能跟其他的列

将所有学生分成男女两组，在聚合算出两组各有多少人

select gander, count(*) from student group by gander;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zqfUTwoI-1681790228540)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658376583042.png)]

学生分成两组，并将两组分别聚合得出最大年龄的学生：

select gander ,max(age) from student group by gander;

6.对分组的结果进行筛选：having关键字，

学生按性别分组后，两组分别聚合得出最大的年龄的学生，在筛选出年龄最大的一组，并显示：

having一般是放在group by的后面，having是对group by 分组后的结果进行筛选。

select gander ,max(age) ,count(*) from student group by gander having gander = '男';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3Q4slpp9-1681790228540)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658379892587.png)]

学生先查找到性别为男的学生数，找到年龄最大的，在进行分组，

where一般是放在from后面，where是对查找的条件进行筛选。

select gander,max(age),count(*) from student where  gander = '男'group by gander;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g16cX2EQ-1681790228540)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658379892587.png)]

注意： select进行分组筛选，后面只能跟group by的分组字段和聚合函数。

7 . LIMIT关键字：完成分页相关操作

将表中1，2，3行都显示出：

-- 从第0条开始查询3条数据，随后显出第一页索要的数据
select * from student limit 0,3;
-- 从第3条开始查询3条数据，随后显出第二页索要的数据
select * from student limit 3,3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5mLHlY7s-1681790228540)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658381370735.png)]

-- 从第3条开始查询3条数据，随后显出第二页索要的数据
select * from student limit 3,3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4EaupFqg-1681790228541)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658381389832.png)]

limit也可以只有一个参数使用，表示查询头几条数据

-- 查询表头三条数据
select * from student limit 3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OUOafbOV-1681790228541)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658381684197.png)]

-- 先进行年龄倒叙排序，在读出前三条数据
select * from student order by age desc limit 3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OlvlquFc-1681790228541)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658381667090.png)]

第四章多表查询（重难点）

第一节内连接：（inner join 或join）

内连接使用inner join 或join 关键字来连接两张表。，使用inner join 或join 关键字后要使用 on 关键字来确定连接条件，（也可用where，on和where使用的效果相同，但是on是声明连接条件的，where是整理的筛选条件）。

如：where和jion on的区别：

where是根据筛选条件进行查询

on 是被驱动表根据驱动表的结果，使用连接条件进行查询

注意驱动表只会被扫描一次，被驱动表会被扫描多次。

-- 内连接
-- 给课程表和老师表分别起了别名c，t方便记忆和操作，
-- 使用inner join将两张表连接起来，使两个表的id相等
-- 以course为驱动表，先找到自己的id=1，再去找连接的teacher表（被驱动表）的id，
-- 根据连接条件c.t_id = t.id,t.id也等于1，
-- 两个数据拼接在一起形成第一条数据,以此类推，不会形成笛卡尔积
select * from course c inner join teacher t on c.t_id = t.id;



-- 查找出课程和老师表的数据全部放在一起，拼接成一个笛卡尔积，
-- 在经过筛选笛卡尔积的数据，根据筛选条件c.t_id = t.id
select * from course c ,teacher t where c.t_id = t.id;

内连接不满足条件的数据不会被记录在最后的结果显示表中（不显示）

如：课程表内容

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FGySwgJU-1681790228541)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658386424889.png)]

老师表内容：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5PR0xsTN-1681790228542)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658386453802.png)]

两表中课程表中有null的，在进行内连接筛选后：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7REQrPu7-1681790228542)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658387148515.png)]

只将符合条件的数据显示在结果表中。

第二节外连接（常用，重点）

1.内连接与外连接的区别：

（1）对于内连接中的两个表，若驱动表中的记录在被驱动表中使用连接条件找不到与之匹配的记录是，则不会将该记录加入到结果表中。

（1）对于外连接的两个表，即使驱动表中的记录在被驱动表中使用连接条件找不到与之匹配的记录时，也要将该记录加入到结果表中。

2 .左连接和右连接：

针对不同的驱动表的选择，又将外连接分为左连接和右连接。

（1）左连接（左外连接）：left outer join：

驱动表选择了左边的表的数据使用连接条件进行查询

-- 左外连接，选取左边的表（left outer join的左边的表：course）当作驱动表
-- 通过course使用连接条件c.t_id = t.id去查询teacher表中与之对应的数据
-- 就算php没有与之对应的数据，依然显示在结果表中（只显示驱动表的所有数据）
select * from course c left outer join teacher t on c.t_id = t.id;

代码中的outer可省略

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SINRhLFp-1681790228542)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658388595275.png)]

（2）右连接（右外连接）：right outer join：

驱动表选择了右边的表的数据使用连接条件进行查询

-- 右外连接，选取右边的表（right outer join的右边的表： teacher）当作驱动表
-- 通过teacher使用连接条件c.t_id = t.id去查询course表中与之对应的数据
-- 就算八戒没有与之对应的数据，依然显示在结果表中（只显示驱动表的所有数据）
select * from course c right outer join teacher t on c.t_id = t.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RZ8aODyy-1681790228542)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658388629439.png)]

（3）全连接（也是外连接一种）：full outer join

不还是驱动表还是被驱动表，所有的数据u都要保留显示在结果表中（mysql不支持，通过其他方法达到需求）。

同过左连接和右连接结合在一起达到全连接的效果（联合：union all 和 union关键字）

union all：将两表无脑拼接在一起，不考虑数据重复的问题


select * from course c left outer join teacher t on c.t_id = t.id
union all
select * from course c right outer join teacher t on c.t_id = t.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O0Oz50d6-1681790228542)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658389176149.png)]

union：将量表拼接在一起，将重复的数据过滤掉


select * from course c left outer join teacher t on c.t_id = t.id
union
select * from course c right outer join teacher t on c.t_id = t.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WaQMIFMH-1681790228542)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658389220716.png)]

第三节子查询（重点）：

按行列数的不同分为：

标量子查询：结果集只有一行一列

列子查询：结果集一列多行

行子查询：结果集一行多列

表子查询：结果集多行多列

1.where/having型子查询：（查询放在where条件里的）

where（适合标量子查询，列子查询，行子查询）：将学生中age>连宇栋的查询出来：

select * from student where age >(
select age from student where name= '连宇栋'
);

2.列子查询（一列多行）：

-- 找出学科分数大于80的学生
select * from student where id in (
 select distinct s_id from scores where score >80 
);

3.行子查询：（使用很少）：

select * from student
where gander = '男' and age = (
 select max(age) from student  
 group by gander having gander = '男'
) ;

4.from型子查询：（查询放在from后的）

查找所有学生中数学成绩前五的，按成绩正序排列

select * from (
select s.id , s.name sname , r.score,c.name canme from student s 
left join scores r on s.id = r.s_id 
left join course c on r.c_id = c.id
where c.name = '数学' 
order by  r.score desc
limit 5) t order by t.score

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mBaOqg8p-1681790228543)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658401876001.png)]

5.select子查询：select关键字后的子查询只能是标量子查询。

查询老师的信息和他代课的数量

select  t.id,t.name,(
	select count (*) from course c where c.t_id = t.id
) as `代课数量` from teacher t

6.exists型子查询：判断子查询是否右返回值，有返回true，反之false

查询有课程的老师：

select * from teacher t where exists(
	select * from course c where c.t_id = t.id
) ;

sql语句练习：

-- 查询1好学生的姓名和各科成绩
select student.id, student.`name`,course.`name`,scores.score from student 
left join scores on scores.s_id = student.id
left join course on course.id = scores.c_id2
where student.id = 1

-- 查询各科的平均成绩,最高成绩
-- 先把所有学科和成绩找出来，再按照学科和成绩进行分组，最后找出平均成绩,最高成绩
select course.id,course.`name` ,student.id,student.`name`
,avg(scores.score),max(scores.score)from course 
left join scores on scores.s_id = course.id
left join student on student.id = scores.s_id
group by course.id,course.`name`;

-- 查询每个学生的最高成绩及科目名称
-- 先把所有学生中成绩最高的表找出来，再根据该表找到对应的课程
select s.id,s.`name` ,c.`name`,c.id,(
select  max(score) from scores r where r.s_id = s.id
) d from student s
left join course c on c.id = s.id

-- 查询所有学生的课程及分数
select student.id, student.`name`,course.`name`,scores.score from student 
left join scores on scores.s_id = student.id
left join course on course.id = scores.c_id

-- 查询课程编号为1且课程成绩在60分以上的学生的学号和姓名
select student.id, student.`name`,course.`name`,scores.score from student 
left join scores on scores.s_id = student.id
left join course on course.id = scores.c_id
where course.id = 1 and scores.score>60

-- 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select student.id,student.`name`,avg(score) from student
left join scores on scores.s_id = student.id
left join course on course.id = scores.s_id
group by student.id,student.`name` 
having avg(score) >= 80

-- 
 select * from student s
 left join(
 select r.s_id,avg(r.score) from scores r group by r.s_id) t
 on  s.id = t.s_id

-- 查询有不及格课程的同学信息
select student.id,student.`name`,course.id,course.`name` from student
left join course on course.id = student.id   
left join scores on scores.c_id = course.id
where scores.score<60

-- 求每门课程的学生人数
-- 每个学生学的课程都有对应的课程id
 select  scores.c_id,count(*) from scores group  by scores.c_id
 
 
 
 
 
  select * from course
 left join 
 (select  scores.c_id,count(*) from scores group  by scores.c_id) t
on course.id = t.c_id






select course.id,course.`name`,count(*) from course
left join scores on scores.c_id = course.id
group  by course.id,course.`name`

-- 查询每门课程的平均成绩，结果按平均成绩降序排列,平均成绩相同时，按课程编号升序排列
select course.id,course.`name`,avg(score) from course
left join scores on scores.c_id = course.id
group by course.id,course.`name` 
order by avg(score) desc, course.id asc

-- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select student.id,student.`name`,avg(scores.score),course.`name` from student 
left join scores on scores.s_id = student.id
left join course on course.id = scores.c_id
group by student.id,student.`name` having avg(scores.score)>=60


select  student.id,student.`name`, t.cc from student
left join 
( 
	select scores.s_id,avg(score)  cc from scores group by scores.s_id having avg(score) > 60
) t  on t.s_id = student.id

-- 查询老师的信息和他所带科目的平均分
select teacher.id,teacher.`name`,course.id,course.`name`,avg(score) from teacher
left join course on course.t_id = teacher.id
left join scores on scores.c_id = course.id
group by teacher.id,teacher.`name`,course.id,course.`name`

-- 查询每个同学的最好成绩的科目名称。
-- 先找出学生的最高成绩，和学生信息的结果表，因为可能学生有多个课程最高成绩相同
-- 根据结果表左连接分数表，找到对应的分数的出结果表，在根据结果表左连接课程表。找到对应课程
select t.id,t.name,scores.c_id,course.id , course.`name` from  
(select student.id,student.`name`,max(score) score from student
left join scores on scores.s_id = student.id
group by student.id,student.`name`) t
left join scores on scores.s_id = t.id and scores.score= t.score
left join course on course.id = scores.c_id

-- 查询被“张楠”和‘‘李子豪’教的课程的最高分和平均分
select teacher.id,teacher.`name`,course.id,course.`name`,avg(score) ,max(score)from teacher
left join course on course.t_id = teacher.id
left join scores on scores.c_id = course.id
group by teacher.id,teacher.`name`,course.id,course.`name` having teacher.`name` = '张楠' or teacher.`name` = '李子豪'

-- 查询有且仅有一门课程成绩在90分以上的学生信息 
select * from student where id in (
select  scores.s_id from scores where scores.score>90
group by scores.s_id having count(*) = 1
)

select  student.id,student.`name` from student 
left join scores on scores.s_id = student.id
where scores.score > 90 
group by student.id,student.`name` having count(*) = 1

-- 查询出只有三门课程的全部学生的学号和姓名
select * from student where id in (
select scores.s_id from scores group by scores.s_id having count(*) = 3
)




select student.id,student.`name`,student.gander from student 
left join scores on scores.s_id = student.id
group by student.id,student.`name` having count(*) = 3

-- 检索至少选修4门课程的学生学号
select * from student where id in (
select scores.s_id from scores group by scores.s_id having count(*)>= 4
)

select * from student
left join scores on scores.s_id = student.id
group by student.id,student.`name` having count(*) >= 4

-- 查询没有学全所有课程的同学的信息
-- 先查课程一共几门，再看每个学生学了多少门课程
select student.id,student.`name`,count(*) from student
left join scores on scores.s_id = student.id
group by student.id,student.`name` having count(*) < (
select count(*) from course )

-- 查询学全所有课程的同学的信息
select student.id,student.`name`,count(*) from student
left join scores on scores.s_id = student.id
group by student.id,student.`name` having count(*) = (
select count(*) from course )

-- 查询各学生都选了多少门课
select student.id,student.`name`,count(*) from student
left join scores on scores.s_id = student.id
group by student.id,student.`name`

-- 查询课程名称为”java”，且分数低于60的学生姓名和分数
select * from student 
left join scores on scores.s_id = student.id
left join course on course.id = scores.c_id
where course.`name` = 'java' and scores.score < 60

-- 查询学过”张楠”老师授课的同学的信息
select * from student 
left join scores on scores.s_id = student.id
left join course on course.id = scores.c_id
left join teacher on teacher.id = course.id
where teacher.`name` = '张楠'

-- 查询没学过“张楠”老师授课的同学的信息
select * from student where id not in 
(select student.id from student 
left join scores on scores.s_id = student.id
left join course on course.id = scores.c_id
left join teacher on teacher.id = course.id
where teacher.`name` = '张楠')

第五章 mysql常用函数介绍

第一节数值型函数


-- 数值型函数：
-- 向上取整数（1.2向上取整数：2）
select ceiling(1.2)  
-- 向下取整数（1.2向下取整数：1）
select floor(1.2)
-- 四舍五入：1.2543取两位小数，1.25
select round (1.2543,2),
-- PI：3.1415926
select PI(),
-- rand:生成一个随机数
select rand();

查询学生的所有平均成绩，并保留一位小数：


select student.id ,student.`name` ,round(avg(score),1)from student
left join scores on scores.s_id = student.id
left join course on course.id = scores.s_id
group by student.id ,student.`name`

第二节字符串函数：

length（）函数：计算字符串长度的函数，返回字符串的字节长度

-- 返回值为： 4
select length('cccs');

concat函数：连接多个字符串，形成一个新的字符串

-- 输出：c  ss
select concat('c','  ','ss');

lower():变小写，upper（）：变大写

-- aacc
select lower('AAcc');
-- ACS
select upper('acs');

left（）和right（）：

select upper('acs');
-- 返回字符串从左边数的一个：a
select left('acs',1);
-- 返回字符串从右边数的两个：cv
select right('scv',2);

trim（）：去掉字符串前后的空格

-- s  a
select trim('  s  a  ');

replace（）：替换

-- 将a，换成w
-- www.mysql.com
SELECT REPLACE('aaa.mysql.com','a','w');

substring():截取，

-- 注意：不变：abcd，从第一个开始截取
select substring('abcd',1);
-- bcd，从第二个开始截取，截取三个
select substring('abcd',2,3);

reverse():反转

-- bca
select reverse('acb');

第三节日期和时间函数

curdate（）：获得当前日期：

curtime（）：获得当前时间，

sysdate（）：获得当前日期时间

now（）：获得当前日期时间

select curdate(),curtime(),sysdate(),now();

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GQlsA5QF-1681790228543)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658569239867.png)]

unix_timestamp():拿到时间戳

from_unixtime():将时间戳转换为时间

select UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6QMh5bBy-1681790228543)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658569435922.png)]

month（日期）：获取指定日期的月份

monthname（日期）：获取指定日期中月份的英文名称

select MONTH( curdate()),MONTHNAME( curdate());

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tlreg60H-1681790228544)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658569718318.png)]

dayname():获取指定日期对应的周几的英文名称

dayofweek():指定日期对应一周的索引位置（西方周日为第一天，周一第二天以此类推）

-- 7
select DAYOFWEEK(CURDATE())；

week();指定日期是一年中的第几周：

-- 29
select WEEK(CURDATE());

year（）：获取年份

-- 2022
select YEAR(CURDATE());

adddate():加时间

-- 原时间：2022-07-23
-- 添加后：2022-08-23
select ADDDATE(CURDATE(),INTERVAL 1 year),ADDDATE(CURDATE(),INTERVAL 1 month);

sub.date():与adddate（）相反，减时间

datadiff(): 计算两个日期相差多少天：

-- 建国日与现在相差多少天：26593
select datediff(CURDATE(),'1949-10-1');

date_format():格式话指定的日期，，根据参数返回指定的格式的值

-- 2022年07月23日
select DATE_FORMAT(now(),'%Y年%m月%d日');

练习：

查询本月过生日的学生：

-- 查询本月过生日的学生
select * from student where MONTH(birthday) = MONTH(NOW());

通过当前日期和学生生日计算年龄：

select * , year(NOW()) - YEAR(birthday) from student;

第四节加密函数(不重要)

MD5（）函数：


-- 'abc'进行加密:900150983cd24fb0d6963f7d28e17f72
select MD5('abc');

第五节流程控制函数

if:

-- 如果是true，返回1，false返回2
select if(true,1,2);

ifnull:

-- 如果abc为空则返回cc反之返回abc：abc
select ifnull('abc','cc');

nullif:

-- 两参数若相同，则返回null，反之返回第一个参数
select nullif('ab','ab');

case when … then…

when… …then …

end as…

类似if…else if…else if…else

-- 例1 输出学生各科的成绩，以及评级，60以下是D,60-70是C，71-80：是B ，80以上是A
select *,
case 
	when score < 60 then 'D'
	when score >= 60 and score < 70 then 'C'
	when score >=70 and score < 80 then 'B'
	when score >= 80 then 'A'
end as '评级'
	from mystudent

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iA7rv4Iz-1681790228544)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658577651859.png)]

-- 例2  由上述的各个学生的成绩 进行行转列案例
select user_name,
-- 拿出张三的课程，如果不是语文，则返回该课程成绩为0，如果是语文，则返回语文的成绩，那么其他的课程成绩都为0，语文的成绩不为0，取最大值就是语文的成绩了，其他科目一次类推
		max(case course when '数学' then score else 0 end) as '数学',
		max(case course when '语文' then score else 0 end) as '语文',
		max(case course when '英语' then score else 0 end) as '英语'
from mystudent  group by user_name;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NNXouWu6-1681790228544)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1658577913329.png)]

第六章数据库设计

第一节三范式

减少数据冗余，但增加了sql的编写难度。

第一范式：要求有主键，并要求每一个字段原子性不可再分

第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖（表中有很多冗余，导致对表的操作会造成很多不变操作，解决方法就是将大表，做成多张小表，这些小表减少数据冗余，缺点是查询时会变慢，但是安全）

第三范式：所有非主键字段和主键字段之间不能产生传递依赖（在第二范式的基础上，假设表中一个学生的专业名称列中，将有关的专业名称都传入该列，就可能产生传递依赖，可能会造成数据冗余，解决方法仍然是将大表拆分为多个子表，将专业中相关的写入多个子表中是数据更加清晰，减少传递依赖，数据冗余）

第二节常见的表关系

一对一（一表对应一表，用的不多，一般都将这种合并为一张表）

一对多（第三范式类型，将两张大表其中一张拆分成多个小表，使用id作为外键管理）

多对多（第二范式类型，将一张大表拆分成多个小表，其中要做一张中间表）

Mysql 进阶部分

linux中mysql设置的密码：‘root’

第一章 mysql架构

第一节 mysql的系统架构

1 . 数据库和数据库实例：

数据库：按照数据结构来组织，存储和管理数据的仓库

数据库管理软件：数据库管理系统软件

数据库实例：启动数据库软件，内存中运行一个独立进程，用来操作数据，就是一个数据库实例。（可以有多个数据实例，每次启动运行一个mysql软件，就会产生一个数据库实例，每一个实列都要管理一个数据库）

2.mysql架构：

mysql向外提供的交互接口（用于连接），管理服务组件和工具组件（操作：备份，恢复，管理等），连接池组件，sql接口组件（接收用户sql命令），查询分析器（分析sql语法合理性），优化器组件（对sql逻辑进行优化），缓存组件，mysql存储引擎

（1）查询流程：

首先客户端建立连接和Mysqlsever连接起来，第一步进行查询缓存（内部是类似hashmap，key代表sql语句，value代表sql语句具体的结果集），如果查询缓存内没有结果，那么就传入解析器对mysql进行解析生成解析树，mysql觉得有更好的解决逻辑，然后mysql会对解析树进行处理生成新解析树，再到查询优化器进行优化，如果，按照mysql更好的逻辑得到的结果与程序员的sql的结果相同，那么就形成执行计划，进入到查询执行引擎（通过api接口查询，将数据存到数据库（大部分存到磁盘））

（2）查询缓存：

优点：在大量相同数据访问时，在第一次查询缓存时就会记录该数据，在之后的相同数据再次访问时，查询缓存会进行哦按段，如果已经有过该数据访问了，那么直接会返回之前相同数据访问时得到的结果给查询执行引擎，然后再返回结果给客户端，查询执行引擎传入存储引擎（数据库），这样会极大节省内存（类似到人们到市政反映问题，市政办公室说已经知道这个问题了，已经有了处理结果）。

（3） mysql8.0为什么取消了查询缓存？

因为查询缓存内部是一个hash表的结构，sql语句代表了key，sql语句的查询结果代表了values值，由于hash算法的特点是极微小的改动也能引起整体巨大的改变。假设第一次查询缓存接收的sql语句：select * from user，hash算法产生了hash值，然后第二次收到的sql：select * from user，sql语义没有变化，但是多了几个空格，查询缓存因为hash算法，多了几个空格，那么就会产生不同的hash值，那么就会出现不同的结果。

（1）虽然hsah计算来进行查询效率高，但如果是上千万的sql语句同时来进行查询（高并发），那么开销巨大，很耗内存。

（2）查询的sql语句的字符大小写，空格的多少，注释的不同都会导致hash算法的hash值发生变化，都会被认为是不同的查询。

（3）当向某个表写入数据时，必须将和这个表线管的所有缓存设置为失效，如果缓存内容过多，消耗太大会导致系统僵死。

那么查询缓存是适合那种情况呢？

数据信息基本不会改变的：今日头条的十大热搜（今天以内不会改变），这个月的考试第一名，

3 . finallyshell备份一张表的数据：

4.字符集和排序规则

_ai

-- 指定数据库
create database user character set 字符集  collate 比较规则
-- 
create table 表名(
	列名 列类型 
) character set 字符集 collate 比较规则

create table 表名(
	列名 列类型 [character set 字符集] [collate 比较规则]
)

第二章 Mysql修改配置的方法

修改配置文件的信息一定要mysql重启后才生效。

第一节修改全局，会话的变量

Mysql启动时，会提供my.ini或my.cnf的配置文件，文件内是一段一段的配置项，在mysql启动时会将配置项加载到内存（也是一段一段的存在内存），mysql在运行过程中，要修改配置项信息时，需要去读取配置项，有两种方式去读取，一种是直接去内存读取配置项并修改（这样子虽然修改了，但是mysql重启时，在内存中修改的配置信息就失效了），一种是去读取配置文件修改（在运行过程中修改，mysql的实列是不会再去读取配置文件的，所以配置文件里修改是不生效的，除非重启mysql）。

1.会话：

当两个连接建立就会产生会话如客户端和服务端建立连接。会话是客户端访问服务器期间存放在服务器上的所有与客户端访问状态有关的信息

当多个连接建立时，如多个客户端和服务端连接时，每个客户端的连接的产生的会话的配置项是不同的，一旦会话结束，配置项也会失效。

2.会话的变量：

客户端和服务端的连接建立前提调用方法，连接保持没有断开则代表该方法一直在运行着，在这个方法运行里定义的变量就是会话变量。

3.全局变量：

在mysql实例运行过程中定义的变量，且所有的会话都可以使用就是全局变量。

Mysql实例：每个实例使用不同的服务端口，通过不同的socket监听；物理上，每个实例拥有独立的参数配置文件及数据库。

等待的超时时间：客户端和服务端连接建立后，双方都接收不到消息（可能是该做的做完没有服务了，或者是其他原因使网络断开了连接，而服务器没有感知到），所以在这种情况设置了一个时间，达到这个时间后，会断开双方的连接。

-- 查询等待的超时时间的全局变量
show global variables like 'wait_timeout';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XDag7aHr-1681790228545)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659270650586.png)]

-- 设置等待超时时间：
set gobal wait_timeout = 1000;

第三章 I/O和存储

Mysql的存储速度很慢，因为一直将数据从磁盘上进行存储和读取的（磁盘的读取和存储太慢了，磁盘的机械臂首先要找到对应磁道，找到磁道后，再旋转找到对应的扇区，再进行操作），会进行I/O操作就会产生I/O成本。

i/o成本：将数据存储到磁盘，或者数据从磁盘加载到内存来进行读取数据的过程消耗的时间

cpu成本：对数据的操作（读取，排序，查询等）需要用cpu资源，这些损耗的时间成本。

第一节 I/O成本

要想减少I/O成本（也就是怎样快速的从磁盘上获取，存储数据）：

1 . 将磁盘换成固态硬盘，速度会快很多

2 . 使用Oracle。

第二节数据存储

1 . Innodb存储

对于innodb而言，数据是存储在表空间的，表空间是个抽象概念，它对应着硬盘上的一个或多个文件。表空间的存储数据单位是页，表空间类似一个大大的本子，本子里是一页页的数据。不同的页存放不同的数据。每页大概占用16k空间。

表空间分为系统表空间和独立表空间，

系统表空间：系统一般默认不会将表的数据存在系统表空间。

系统表空间包含了很多公共数据，也是一个共享表空间，可以被多个表共享的。

独立表空间：每有一张表，就会独立建立一个表空间。

区：每个表空间保存了大量的页，为了更好的管理提出了区的概念：64个页就是一个区（约1M）。

段：分为索引段，数据段，回滚段。是为了区分不同的数据类型，相同的段，存的数据类型是相同的，一个段就是256个区（256M）。

第三章缓冲池 buffer pool

select * from user where id = 10 between 10000;

在磁盘中紧紧相连的数据会存放在相同的页当中：如上述代码的user信息10到10000，所以进行查询时，首先会将这一页的数据都加载到内存当中（缓存到内存中），拿到id为10的数据，之后再查询时直接就拿到id后面到1000的数据了，如果这期间还没到10000时，读到1000该页的数据读完了，那么从磁盘中找到对应1000到10000的页，加载到内存继续查找。节省了大量的I/O操作。

简而言之就是，当遇见热点数据时（经常性访问），会将该数据缓存到内存中，方便随时的I/O操作，而不是要通过磁盘来进行I/O操作。

第一节 buffer pool内部结构

整个buffer pool由缓冲页和控制块组成。

1 . 缓冲页：

buffer pool 中存放的数据页就是缓冲页，和磁盘上的数据页一一对应，都是16kb，缓冲页的数据是从磁盘上加载到buffer pool当中的一个完成页（都是从磁盘上一页一页的加载）。

2 . 控制块：

每个缓冲页都有它的一些描述信息（表空间编号，数据页地址，数据页编号等），每一个控制块负责保存一个缓冲页的描述信息。

buffer pool的前部分存储控制块，后部分存储缓冲页。（类似一本书，目录就是控制块，目录指向的内容就是缓冲页），如果有未利用的空间就是内存碎片。

buffer pool初始化后，会向系统申请内存，内部会将控制块与缓冲页的内存结构划分好，他们一一对应，此时的buffer pool什么内容都没有。

3 . free链：（双向链表）

记录哪些缓冲页可以被使用，也就是哪些缓冲页是空白的可使用的。空闲的缓冲链通过控制块来进行标记和管理，将所有空闲的缓冲页和所对应的控制块作为一个个的节点，形成一个链表，这就是free链（控制块指向一个缓冲块）。内部有个基础节点连接首尾节点，还有个count记录链表结点个数。

如何知道数据页是否被缓存?

在表空间中使用【表空间号+页号】就能确定一个唯一的页。设计一个hash表，key代表【表空间号+页号】当作key，控制块地址（该控制块保存的是缓存的数据页的描述信息）做value，每次查询通过key就能迅速定位到控制块，然后找到对应的缓存了的数据页，找到后返回结果。

若查询key没有找到对应控制块，那么说明该数据页没有被缓存，就从磁盘把该数据页加载到buffer pool中，在buffer pool中加载到free链中。

4 . flush链：（双向链表）

（1）脏页

在sql的执行过程中，无论是增删查改，都是优先在buffer pool中进行的。但是如果在对一个缓冲页中的内容进行了修改，可磁盘中的对应的数据页的数据没有被更改，导致磁盘的数据页与buffer pool中的缓冲页数据不一致，那么此时的缓冲页就是脏页。解决方法就是将脏页的数据刷入磁盘，使数据更新并保持一致。

flush链表中所有结点都是脏页。

5 . 刷盘时机：

每隔一段时间就会吧flush链表中的脏页刷入磁盘中，刷新的熟读取决与当前系统的繁忙程度，这万一在系统崩溃的情况下，会导致有的没有刷入成功，那么就会在成数据丢失，Mysql通过日志系统和解决问题。

第二节 LRU链表（Least Recently Used，最近很少使用）

内存是有限的，在buffer poll中内存更是有限的，所以在数据量很大，bufferpool内存不够用时，会清内存，清掉那些很少使用的缓冲页。LRU链表的作用就是干这个。LRU链表将越是被经常使用的缓冲页，就放到表的前端，随着缓冲页的使用，就会使不常使用的缓冲页渐渐的在链表的后端。分为冷热数据区。经常使用的放到热数据区，不常使用的放在冷数据区。

当首次使用的时间与之后使用的时间大于1s，则放入热区。反之冷区。

第四章 MYSQL临时表

保存临时数据。虚无缥缈的，用show table查询不到该表。只要断开连接临时表就会消失

使用create temporary table 表名，创建外部临时表，只要断开连接临时表就会消失。

create temporary  table useer(

id int;

name varchar(10);

)

内部临时表：存储某些操作中的中间结果。把分组后产生了计算个数后的一个中间表，最后排序。

explain select count(*) from user group by age order by age;

第五章 Mysql事务（重点）

sql语句要么执行时全部执行成功，只要有一条执行失败，那么就全部失败，之后回滚到执行前状态。

第一节事务的分类

1 . 显示事务和隐式事务，由autocommit（全局变量）决定。

隐式事务：默认的事务叫隐式事务

显式事务：由我们自己控制，手动开启的事务，手动提交的事务。

-- 查看 autocommit的值：ON代表1
show variables like 'autocommit';

-- 修改autocommit的值尾0：OFF
set autocommit = 0;

-- 关闭自动提交后，在对student表删除操作：此时表中id=1的数据并没有删除
delete from student where id = 1;

-- 加入commit后代表提交事务,此时表中数据成功删除
commit;

2 . 开启一个显式事务（begin，commit，rollback关键字）：

begin：开启事务

start transaction：开启事务（与begin作用相同）

-- 删除id= 1 的teacher：
begin；
delete from teacher1 where id = 1;
-- 此时已经认定为删除了id为1的teacher，但是在表中的信息刷新后，并没有被删除

select * from teacher1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iVDMgSeL-1681790228545)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659404692866.png)]

但是在表中的信息刷新后没有改变：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tnFiSnjJ-1681790228546)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659404719609.png)]

这是因为没有提交事务，需要手动提交：

commit;

此时刷新表中数据后，表中的id为1的teacher已经被删除：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-13yhVhPZ-1681790228546)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659404831129.png)]

rollback的使用：

begin ;
delete from book where id = 1;
rollback;
select * from book；

此时表中的数据和显式的数据都是没有将id为1的数据删除。回滚到原先的状态。

start transactiom 的使用：

-- 开启事务后，只能存在读，在只读事务当中只能读取数据，如果执行修改删除的操作那么直接报错
start transaction read only;
select * from book;
rollback;

进行选择性的回滚：

建立一个保存点。

START TRANSACTION;
DELETE FROM teacher where id = 1;
-- 建立保存点
SAVEPOINT a;
DELETE FROM teacher where id = 2;
ROLLBACK to a;
-- 提交事务后，id为1的删除掉了，id为2的没有删除
-- 因为在删除id2前设置了保存点，
-- 删除id为2的数据后，又进行回滚，回滚到删除id为2的数据前的地方

-- 事务提交后，表中显式id1被删除，id没有被删除
COMMIT;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DRYR6jFO-1681790228546)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659408431746.png)]

3 . 事务的四大特性：

（1）原子性

不可分割，一个事务要么全部完成，要么全部失败。事务执行过程中出现错误，那么事务就会回滚到事务执行前的状态。

（2）一致性

事务执行前到结束以后，数据库的完整性没有被破坏，数据库的状态与业务规则保持一致，（如a对b转账，虐能a扣了钱，b没有收到，也不可能a和b的总金额在事务前后发生变化，产生数据不一致）。

（3）隔离性

数据库允许多个并发事务同时对数据进行读取和修改，隔离性可以防止在并发修改数据时产生数据不一致的问题。（如上锁解决）。

（4）持久性

事务结束后，对数据的修改是永久的，计时系统故障数据也不会丢失。

4 . 事物的隔离性----事务的隔离级别

读未提交（read uncommited）：一个事务在执行过程中能够读到未提交的修改的数据

读已提交(read commited)：一个事务在执行过程中能够读到已提交的修改的数据

可重复读(repeatable read)：（暂时不讲）

串行化(serializable)：本来多个事务能一起进行，将其改成串行一个接一个的执行（性能太差）。

（1）事务的隔离级别对应存在着不同的问题

脏读不可重复读幻读

读未提交 1 1 1

读已提交 0 1 1

可重复读 0 0 1

串行话 0 0 0

由表可知：读未提交最低级别，存在三大问题，其次读已提交能解决脏读的问题，然后可重复读能解决脏读，不可重复读的问题，串行化能解决着三大问题属于最高级（但性能太差）。

（2）设置事务的隔离级别：

查看当前事务的隔离级别：

show variables like 'transaction_isolation';

设置下一个事务的隔离级别：(针对一个事务)


-- 设置下一个事务的隔离级别：当前这个事务执行完后，下一个事务的隔离级别也是这个：
set transaction isolation level read uncommited;//此后后面的一个事务的隔离级别就是这个读未提交

设置当前会话的隔离级别：（针对一个会话事务）

会话事务：对会话进行的操作

-- 设置当前会话的隔离级别为：读未提交：加入session
set session transaction isolation level read uncommited;

设置全局事务的隔离级别：（针对一个全局事务）

-- 设置全局事务的隔离级别：读未提交 ，加入global,该全局事务的子事务的隔离级别都是读未提交
set global transaction isolation level read uncommited;

优先使用设置下一个事务的隔离级别，然后前会话的隔离级别，最后全局事务的隔离级别。

（3）读未提交read uncommited

脏读：读取了未提交的数据。未提交意味着可以回滚到原来的状态。（给老婆打钱100，老婆余额500，打了之后600，并没有提交，老婆查帐户发现是600就推出了，此时我再回滚，老婆的账户余额变为了500）。

可能出现脏读，不可重复读，幻读的问题。

幻读：读取到未提交中修改的了的数据（因为我之前对余额做了修改，又进行了回滚，我回滚后没有提交，老婆账户为6 00，此时老婆又来查看账户发现账户为500，此时就是幻读）。一个事务进行查询，事务提交前，有另一个事务插入了满足条件的其他的数据（做了插入等操作），再次使用相同条件查询时，发现数据变多了，就像出现幻觉一样。

（4）读已提交：read commited

不可重复读：事务A修改了事务B读取过的数据，事务B再第二次读取时发现数据不一致，也就是说一个原子性操作的事务两次读取相同的数据，数据却不一致，数据不能被重复读取（主要是uodate操作导致），同一事务当中，再次读取相同的数据时，发生数据不一致。

例如：我余额1000块，取商场买东西刚好1000块，消费时我的余额显示1000，此时老婆将我卡里1000块转走了200，并在我之前提交了事务，我在要消费时显示余额不足。

(5)可重复读：repeatable read

两次读取数据时，数据时一致的。

（6）串行化：serializable

内部给事务上锁，当有人使用了user表，那么其他的人要想使用user，必须要等先使用的人提交事务后才能使用。

第六章索引（重点）

引入：当我们面对千万级的数据量时，我们使用查询名字的方式擦护照某个数据时，需要很长的时间，而如果我们时按照id来进行查询数据时那么就会相对快得多，因为内部是使用的B+树的数据结构，我们要了解B+树，就要先清楚B-树的数据结构。

第一节 B-树（多路自平衡搜索树）

在该数据结构中，每一个结点代表的是一页数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GnYcEfLo-1681790228546)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659516570652.png)]

由上图B-树结构，每个结点都是代表的一个数据页，数据页内部的是很多数据的，里面的数据都是按照id来进行有序的排列的（每个id对应一个数据data）。数据页中假设根结点排列好的id范围：25，50，75，进行查找某个数据时，如果查找id10，那么就会在跟节点数据页中比较10比25小，那么就会去25更小的子结点去寻找，如果大于25小于50，就会在25-50的区间去寻找，大于50的就在50-75区间寻找，大于75就在大于75的区间寻找，在子结点中仍然是与根结点相同的数据结构排列范围，继续按照上述方法，查找。（类似二叉树的查找法，只不过数据范围变大了，每个节点也有范围罢了）。

mysql使用id进行查找时，因为上述数据结构可以很快地找到目标数据，而如果是使用名字进行查找，那么内部会先将所有的数据都遍历出来，在进行比较是否相等来进行查找，效率极低。

mysql为什么选择b+树？

可以存储更多的数据（减少了树的层数），减少io操作，查询效率更高，叶子节点存数据非叶子节点存索引，且节点内的索引都是有序排列。叶子节点组成一个双向链表方便范围查询，全表扫描。

第二节 B+树（叶子节点存数据，非叶子节点存编号）

B+树容量：假设一层10，两层就为100个，，n层为10的n次方

B+树是B-树的变体，节点内的数据都是有序排列的，它所有的数据全部存储在在叶子节点，所有叶子节点使用双向指针进行关联。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yVBfN1j7-1681790228547)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659517627544.png)]

非叶子节点存储的都是id。一个节点只能存一页数据（16k）,如果按照B-树的方式，存储时每个id和id对应的数据都要存储，当数据量够大时，一个节点只能存少量的数据（假设30个）。而B+树让根结点全部存id，那么就能包含到更大的范围（远大于30）。而且在I/O操作B-树结构只能取到30个，而B+树能取到最招式比30个多的多的。B+树这样能减少大量的I/O操作，在性能，速度上要快的多。

在所有的数据都放在叶子节点以后，而且让每个叶子节点都使用双向指针让他们相互连接，有利于我们执行范围扫描，比如在我们要扫描15到30的数据，因为是有序且相互连接，那么就能拿直接去扫描15到30的数据。

第三节发现索引（b+树，叶子节点连上一个双向链表，数据存在叶子节点上的，非叶子节点保存数据的编号，减少i/o操作）

索引：首先B+树已将表中所有数据保存。索引的数据结构跟B+树相同，不同的是，如果把name来作为排序，将所有的名字存入索引的B+树的根结点（只存名字，不存id，数据），整个树的结构的子结点只保存名字和id，不保存数据（节点与节点间仍然用双向指针相连接），那么能容纳更多的数据。名字仍是按照有序排列的，在查找时，与上述B+树方法类似，不过在子结点到某个目标时，会根据保存它的id回表到B+树查找真正的数据。

第四节索引的分类

1 . 聚簇索引和非聚簇索引：

（1）聚簇索引：

主键和数据共存的索引成为聚簇索引（只存在Innodb引擎中）。其他的为非聚簇索引（辅助索引，二级索引）（在MyIsam引擎中）。

在Innodb中的聚簇索引的主键索引和辅助索引：辅助索引按照id进行条件搜索，如果找到了直接就能查到该数据，如果没有找到，通过主键索引直接从B+树中找到数据，再回表到B+树找到对应id的数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TDUR8dcP-1681790228547)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659523691224.png)]

在myisam中的非聚簇索引的主键索引和辅助索引：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bPhSeh4I-1681790228547)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659523787528.png)]

MyIsam中的索引没有跟Innodb中的索引相似；而是所有的索引都是相同的。它的子结点保存的都是每个数据的所在的行地址，根据地址找到目标数据。辅助主键不需要回表去寻找，而是直接就能找到数据。

注意：聚簇索引默认使用主键索引（如果有主键，就按照主键的方式组织一个聚簇索引），通常我们都要求定义一个主键。

如果涉及到大数据量的排序，全表扫描，count之类的操作的话，MyIsam相对占优势一点运行的要快点，因为Myisam的索引所占的内存相比Innodb虽有所占内存要小一点。

问题：聚簇索引中,如果id没有特殊要求主键为什么建议使用自增id？

聚簇索引中，数据一段段的紧凑存放在一页中，如果不用自增id，没有一个数据插入，就要自己新定义一个id，在使用id索引当中，每个数据都是紧凑的有序排列在一起的，如果这时从中间插入一个数据到里面，那么在索引里要么将目标点后的索引往后移，要么新开辟空间定义指针指向，太耗资源，操作复杂。

如果使用自增主键，那么每次插入的数据对应的id索引都是自动递增有序排列的，在B+树中也会自动有序排列起来，不需要操作。

2 . 二级索引

二级索引分为：普通索引、唯一索引、复合索引等。

（1）普通索引

普普通通的索引，没什么特殊要求。

-- 创建学生表的索引 index_userid为索引的名字，student为表明，id为表student的列名
create index index_userid on student (id);

以id建立索引后在进行id的查询：速度提升巨大。

-- 创建学生表的索引 index_userid为索引的名字，student为表明，id为表student的列名
create index index_userid on student (id);
-- 建立索引后查询数据的速度是原来的很多很多倍
select * from student where id = 6;

删除索引的语法：

-- 删除索引：index_userid索引名，student表名
drop index index_userid on student;

修改索引：

-- 给book表的id索引修改为以name做索引
alter table book add index idx_name(id);

如果一个表中的列的名字字数特别长，建议使用短索引。

短索引：当要以某列作为索引，该列字符数太长时，可以做短索引来进行操作，这样可以使一页能装下更多的索引信息，减少更多的I/O操作，提升效率。

-- 短索引：以书名做索引，书名太长可以设置书名的前4个字符来做索引排序
create index bookname on book (name(4));

（2）唯一索引

对列的要求：要求该列的值必须唯一，索引列的值不能重复

c创建表的同时，创建索引：

-- 创建boy表，创建索引名为boy_id,unique嗲表唯一索引
create table boy (
id int,
name varchar(10),
unique index boy_id(id)
);

单独创建唯一索引：

-- 给course表创建唯一索引
create unique index courese_name on courese (name);

修改唯一索引：

-- 给course表将id的索引修改以name做唯一索引
alter table course add unique index idx_name(id);

1>唯一索引和主键的区别：

唯一索引允许有空值，主键不能

主键列在创建时已经默认为非空+唯一索引了。

主键可悲其他表引用作外键，唯一索引不能。

2>唯一索引和唯一约束的区别：

唯一约束和唯一索引，都可以实现该列的值不重复，该列的值可为null

创建唯一约束，会自动创建一个唯一索引。

（3）复合索引（联合索引）

适合多条件查询，使用符合索引能够极大的减少回表的次数。，复合索引会优先按照第一列排序，第一列相同的情况下会按照第二列排序，以此类推。适用环境：查找条件比较多的情况：如查找陈性的人且年龄大于20，且为男性，那么就对姓氏作第一列索引，再对年龄作第二列索引，性别作第三列索引。

-- 创建了一个复合索引，查找时先按照id来找，然后username，最后password（password是以前2个字符做的短索引）
create index idx_id_name_password on user(id,username,password(2));
-- 复合索引的查询适用：如果不进行查询第一列id索引，直接进行username和password列索引进行查询，那么效率极低，相当于没有运用到索引使用的全表扫描。

-- 使用复合索引查找
select * from user where id= 2 and username = 'xun' and password = '456';

-- 覆盖索引，因为查找username时已将用户全部排列，此时在此基础上查找密码和id不用回表查询,更建议这样写
select `password`,id from user where username= 'xun';

复合索引遵循最左前缀原则：假设以a=1 and b=2 and c>3 and d=4的条件建立索引（a,b,c,d）我们应该将范围类型的查询放到最后索引，因为如果放到中间进行范围查询后，就不是有序排列了，那么后面的查找就不是有序的布恩那个使用索引了，只能全表扫描，那就没有意义了。所上述条件的复合索引应该为（a,b,d,c）或者（a,d,b,c）或者（b,d,a,c）或者（d,b,a,c）;也就是等号的都可以乱序；将所有的有序排列的索引完成后再进行范围查询的索引。

（4）全文索引（fulltext）

类似百度搜索菲菲，会将所有有关菲菲字样的全部呈现出来。类似使用like ‘菲菲’，但是要比like快非常非常多。

-- 在book表的内容里创建全文索引
create fulltext index ftt on book (bookcontent);
-- 查找bookcontent内容中的胜负多少字段
select * from book where match(nookcontent) against('胜多负少');

（5）hash索引

只支持Memory存储引擎，内部建立一个hash表，若以列中的name作为索引列，将name作为key值，value是行的地址。对name进行hash计算找到对应的槽位，然后找到对应的行地址。

创建表和hash索引：

create table `hash_user`  (
  `user_id` bigint(20) not null auto_increment comment '用户ID',
  `user_name` varchar(30) character set utf8mb4 collate utf8mb4_0900_ai_ci not null comment '用户账号',
) engine = Memory character set = utf8mb4 collate = utf8mb4_0900_ai_ci comment = '用户信息表' row_format = Dynamic;

单独创建hash索引：

create index hash_id using hash on user(id);

第五节 explain的用法

当我们写了sql语句，创建了索引，再进行操作时，执行速度还是很慢，我们不知道我们创建的索引是否有倍利用到，使用explain来解决。可以很好的分析SQL语句或表结构的性能瓶颈。

使用方法：在执行的sql语句前加入 exolain关键字即可。

第六节使用索引的问题：

（1）哪些情况适合建立索引

1>频繁使用where语句查询时需要建立索引。

2>关联字段需要建立索引：驱动表和被驱动表之间，驱动表查找某个数据时可能会多次去被驱动表中查找，这时需要建立索引提高效率。

3>分组，排序也可建立索引提高效率。如下：

未给age添加索引时，进行分组后会形成一个临时表（Extra里的）在进行排序，

select * from student group by age order by age;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6pwVwWAX-1681790228548)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659608826659.png)]

给age建立索引后：没有产生临时表,也没有使用额外的排序，在建立索引后就有序排列了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r0BOmddQ-1681790228548)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659608882172.png)]

（2）哪些情况不适合建立索引

1>频繁更新的字段不适合建立索引(频繁更新的字段，那么索引也要频繁更新，消耗内存太大)

2>where中用不到的字段不适合建立索引

3>表数据可以确定较少的情况不适合建立索引

4>数据重复且分布均匀的表不适合建立索引

（3）能用复合索引就用复合索引

（4）null值也是可以走索引的

（5）尽量使用短索引

（6）排序的索引问题

数据库默认排序符合要求情况下不要使用排序操作，尽量不要包含多个列的排序，非要排序就用复合索引。

第七节索引失效的情况

（1）条件中有or，即使条件中有哦索引也不会走索引，除非所有条件都有索引

（2）复合索引不满足最左原则，索引失效

（3）like查询以%开头

（4）存在列计算的情况：

explain select * from student where age = (18-1)

（5）类型转换

-- 索引不失效
explain select * from student where age = '18'  
explain select * from ydl_user where login_date = '2008-05-31 17:20:54'
-- 索引失效 本来是字符串，你使用数字和他比较
explain select * from student where gander = 1

索引最左匹配：

已知username，nickname，email为一个复合索引（从左到右的顺序为：username，nickname，email），mysql查询时只走一个索引最左边的索引（最左匹配），查询后通过拿到的结果去查询nickname，之后再去拿到的结果查email（索引下推）

覆盖索引：

当只有一个索引时为什么还要使用复合索引？（覆盖索引）

防止回表扫，直接通过索引查到数据而不是回表查询，如果没有通过索引找到数据才会回表扫描。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7Uyi4wZx-1681790228548)(C:\Users\陈刚\AppData\Roaming\Typora\typora-user-images\image-20230222223319903.png)]

短索引：

对字符串的列创建索引，如果可能，应该指定一个前缀长度较短的索引，例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。

推荐使用短索引，相对长索引存储的大小较小，查询效率更快。

第七章锁机制（重点）

表锁：开销小，上锁快，不会出现死锁，发生锁冲突率低。但是并发度较低。

行锁：开销大，上锁慢，会出现死锁，发生锁冲突率高。但是并发度高。

第一节 InnoDB的锁类型

主要有读锁（共享锁），写锁（排他锁），意向锁和MDL锁。

1 . s锁（share lock:共享锁）

读锁，一个事务获取了一个数据行的读锁，其他事务也能获得该行对应的读锁。即一个事务在读取一个数据行时，其他事务也能读取，但是不能对该数据增删改操作。可以多个事务共同的读取数据，但只能一个事务写。

获取读锁：

begin;
select * from user where id=1 lock in share mode;

2 . x锁（排他锁，独占锁）

一个事务获取了一个数据行的写锁，既可以读该数据行，也可以修改该数据行。但其他事务不能再获取该行的其他任何锁，包括s锁，直到当前事务将锁释放。只能一个事务持有。

3 . 记录锁（行锁，record lock）

所得数据中的一行的数据。当对一行数据进行修改时且这行数据有索引则会触发行锁把这一行数据锁起来，如果没有索引会触发锁住整个表。

（1）两个事务修改【同一行】记录，该场景下，where条件中的列不加索引。

事务一：

begin;
update ydl_student set score = '77' where name = 'jack';

事务二：

begin;
update ydl_student set score = '80' where name = 'jack';

发现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

（2）两个事务修改同表【不同行】记录，此时where条件也不加索。

事务一：

begin;
update ydl_student set score = '76' where name = 'hellen';

事务二：

begin;
update ydl_student set score = '66' where name = 'jack';

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，表被【锁】住了。

（3）两个事务修改【同一行】记录，where条件加索引

事务一：

begin;
update ydl_student set score = '99' where name = 'jack';

事务二：

begin;
update ydl_student set score = '79' where name = 'jack';

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

（4）两个事务修改同表【不同行】记录，此时where条件加索。

事务一：

begin;
update ydl_student set score = '77' where name = 'hellen';

事务二：

begin;
update ydl_student set score = '77' where name = 'jack';

发现都可以顺利修改，说明锁的的确是行。

**证明：**行锁是加在索引上的，这是标准的行级锁。

4 . 间隙锁（gap lock）

间隙锁锁定的是记录范围，不包含记录本身，也就是不允许在某个范围内插入数据。

间隙锁生成的条件：

(1)A事务使用where进行范围检索时未提交事务，此时B事务向A满足检索条件的范围内插入数据。

(2)where条件必须有索引。

事务一：

begin;
select * from ydl_student where id between 3 and 7 lock in share mode;

事务二：

begin;
insert into ydl_student values (5,'tom',66,'d');

发现卡住了，第一个事务会将id在3到7之间的数据全部锁定，不允许在缝隙间插入。

5 . 临键锁（间隙锁和记录锁的组合）

避免幻读。

6 . MDL锁（元数据锁）

表开启了事务后，无论在表中加入了上述的锁，都会加入一个元数据锁（为了保护表结构：如你上锁后，你又在navicat中对表的结构进行了添加，或者删除，或者修改列字段等操作，你点击保存时发现保存不能点击，卡住了，没有反应，只有在提交事务释放锁后，你才能保存成功。）

begin;
update user set username = 'cc' where id = 3;

提交后就能保存成功：

commit;

当一个事务对表进行操作时就已经上了锁，防止其他事务对表的结构做出修改，造成数据错误。

7 . 死锁（重点）

多个事务同时持有对方所需要的锁，同时要去获取对方持有的锁才提交叫事务，这样就造成大家一直都在等待对方提交事务释放锁好去获取到锁，无法正常的结束。

如下代码：当两个事务同时运行时就会造成死锁。

-- 事务1
begin;
-- 获取到user表id=1行的写锁（独占锁）
select * from user where id = 1 for update;
-- 获取到user表id=2的写锁再提交事务释放锁
select * from user where id = 2 for update;
commit;

-- 事务2
begin;
-- 获取到user表id=2的写锁
select * from user where id = 2 for update;
-- 获取到user表id=1行的写锁（独占锁）再提交事务释放锁
select * from user where id = 1 for update;
commit;

尽量让数据表中的数据检索都通过索引来完成，避免无效索引导致行锁升级为表锁。
合理设计索引，尽量缩小锁的范围。
尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。
尽量控制事务的大小，减少一次事务锁定的资源数量，缩短锁定资源的时间。如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。

第二节表锁

在I你弄DB中大部分使用行锁，只有在特殊情况下才使用表锁。

（1）事务需要更新大部分或全部的数据（极可能发生锁冲突，死锁问题，那么就可以把整个表锁住，这样其他的事务就不能干预）

（2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。（一次性锁住涉及的表，避免死锁）

如下主动上表锁的方式：

-- 给teacher表上写锁，sudent上读锁
lock tables teacher write ，student read;
-- 开启事务
begin;
-- 执行操作的语句
select* from techer;
-- 提交事务
commit;
-- 把所有的表的表锁释放掉
unlock tables;

第三节以另一个角度对锁的分类

1 . 乐观锁

乐观锁大多是基于数据【版本记录机制】实现，一般是给数据库表增加一个"version"字段。

读取数据时，将此版本号一同读出，

更新时，对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

事务一：

select * from ydl_student where id = 1;

事务二：

select * from ydl_student where id = 1;
update ydl_student set score = 99,version = version + 1 where id = 1 and version = 1;
commit;

事务一：

update ydl_student set score = 100,version = version + 1 where id = 1 and version = 1;
commit;

发现更新失败，应为版本号被事务二、提前修改了，这使用了不加锁的方式，实现了一个事务修改期间，禁止其他事务修改的能力。

2 . 悲观锁

手动上锁的都是悲观锁，不管三七二十一，先上锁，在操作。

悲观锁依靠数据库提供的锁机制实现。MySQL中的共享锁和排它锁都是悲观锁。数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。此处不赘述。

第八章日志系统

第一节 bin log日志（二进制日志）

1 . 概述

以事件形式记录了对mysql数据库执行更改的所有操作。bin log的日志文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志。

bin log 又两个常用的场景：

主从复制，数据恢复。

2 . 数据恢复

drop table mystudent;
-- （1）确认binlog开启，log_bin变量的值为ON代表binlog是开启状态：
show variables like '%log_bin%';
-- （2）为了防止干扰，我们flush刷新log日志，自此刻会产生一个新编号的binlog日志文件：
flush logs;
-- （3）查看所有binlog日志列表：
show master logs;

将ydl_student表中将数据都删除后，Mysql的Data中日志会产生一个最新的日志文件：类似binlog.0000的文件。

执行指令 C:\Program Files\MySQL\MySQL Server 8.0\data>mysqlbinlog -v binlog.000010 会出现该日志下的信息，如果你做了删除表操作，里面会记录对表的删除操作

执行恢复，通过上一步的操作，我们找到了删除的位置在一个 at = 3328，执行指令恢复数据：

C:\Program Files\MySQL\MySQL Server 8.0\data>mysqlbinlog -v binlog.000010 --stop-position=3228 -v | mysql -uroot -p

3 . binlog主从服务

服务器宕机，会导致业务停顿，影响客户体验。
服务器损坏，数据丢失，不能及时备份，造成巨大损失。
读写操作都在同一台服务器，在并发量大的情况下性能存在瓶颈。

第二节 redolog

1 . redo log(重做日志)

为了防止因系统崩溃而导致的数据丢失。

1>每次提交事务之前，必须将所有和当前事务相关的【buffer pool中的脏页】刷入磁盘，但是，这个效率比较低，可能会影响主线程的效率，产生用户等待，降低响应速度，因为刷盘是I/O操作，同时一个事务的读写操作也不是顺序读写。

2>把当前事务中修改的数据内容在日志中记录下来，日志记录是顺序写，性能很高。执行事务中，每执行一条语句，就可能有若干redo日志，并按产生的顺序写入磁盘，redo日志占用的空间非常小，当redo log空间满了之后又会从头开始以循环的方式进行覆盖式的写入。

（1）MTR（最小事务）

在Innodb中，有很多操作，必须具有原子性，type：redo log的类型。

以一组一组的形式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DiHbEMvn-1681790228549)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659768617684.png)]

单个redo log的形式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ChGh6tp4-1681790228549)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659768666761.png)]

事务、sql、MTR、redolog的关系如下：

一个事务包含一条或多条sql
一条sql包含一个或多个MTR
一个MTR包含一个或多个redo log

（2）log buffer

任何可能产生大量I/O的操作，都会涉及缓冲层（因为没记录一次log日志，就输入一次磁盘，是很耗内存的），所设计缓冲层在合适的时机一起刷盘。

当log buffer空间不足时就会刷盘

提交事务时刷盘

后台有独立的线程时每隔一秒就刷盘一次

正常关闭服务器时刷盘

做checkpoint时刷盘

（3）checkpoint

redolog日志文件容量是有限的，需要循环使用，redo log的作用仅仅是为了在崩溃时恢复脏页数据使用的，当空间不够用时是通过覆盖旧的redo log日志文件来解决的。

1>Isn：

lsn（log sequence number）是一个全局变量。mysql在运行期间，会不断的产生redo log，日志的量会不断增加，innodb使用lsn来记录当前总计写入的日志量,lsn的初始值不是0，而是8704，原因未知。系统在记录lsn时是按照【偏移量】不断累加的。lsn的值越小说明redo log产生的越早。

2>flush_to_disk_lsn:

也是一个全局变量，表示已经刷入磁盘的redo log的量，他小于等于lsn

3>flush链中的lsn:

要保证数据不丢失，核心的工作是要将buffer pool中的脏页进行刷盘，但是刷盘工作比较损耗性能，需要独立的线程在后台静默操作。

（4）系统崩溃的影响

（1）**log buffer中的日志丢失，**log buffer中的日志会在每次事务前进行刷盘，如果在事务进行中崩溃，事务本来就需要回滚。

（2）buffer pool中的脏页丢失，崩溃后可以通过redo log恢复，通过checkpoint操作，我们可以确保，内存中脏页对应的记录都会在redo log日志中存在。

第三节 undo log（撤销日志或者回滚日志）

redo log保证了崩溃后，数据不丢失，但是一个事务进行中，如果一部分redo log已经刷盘时崩溃，那么在恢复数据时哪些已经输入磁盘的数据也会倍恢复（这些已经输入磁盘的数据是可以不用再恢复的）。此时可用undo log日志。

他的主要作用是为了实现回滚操作。同时，他是MVCC多版本控制的核心模块。undo log保存在共享表空间【ibdata1文件】中。

1 . 事务id（trx_id）

在innodb的行数据中，会自动添加两个隐藏列，一个是【trx_id】，一个是【roll_pointer】，本章节会详细介绍这两列的具体作用，如果该表中没有定义主键，也没有定义【非空唯一】列，则还会生成一个隐藏列【row_id】，这个我们之间也讲过，是为了生成聚簇索引使用的。

事务id是一个自增的全局变量，如果一个【事务】对任意表做了【增删改】的操作，那么innodb就会给他分配一个独一无二的事务id。

2 . roll_pointer(回滚指针)

undo log在记录日志时是这样记录的，每次修改数据，都会将修改的数据标记一个【新的版本】，同时，老的数据就会标记成一个历史版本。版本和版本之间使用roll_pointer指针相连接形成一个版本列。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yoQmYKhl-1681790228549)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659771832087.png)]

innodb将undo log分为两类：

一类日志只记录插入类型的操作（insert）对应幻读
一类日志只记录修改类型的操作（delete，update）对应不可重复读

第九章隔离级别和MVCC（常问重点）

MVCC:多版本并发控制（多个事务进行操作）。提高并发性能，更好的处理读写冲突

第一节读视图（read view）

当前读：读最新的数据。像select lock in share mode（锁）、 select for update、 update、insert、delete（排他锁）这些操作都是【当前读】，他读取的是记录的【最新版本】，读取时还要保证其他【并发事务】不能修改当前记录，会对读取的记录进行加锁。

快照读：像不加锁的select操作就是快照读，即不加锁的【非阻塞读】；快照读的前提是【隔离级别不是串行级别】，串行级别下的快照读会【退化成当前读】，顾名思义，快照读读取的是【快照】，他是通过readView实现的。

1、实现原理

Read View就是事务进行【快照读】的时候生产的【读视图】(Read View)，在该事务【执行快照读】的那一刻，会生成数据库系统当前的一个快照。

注意：【快照】不是说将数据库复制一份，【Read View】的主要作用是做【可见性判断】，快照的实现逻辑是通过undo log的【版本链】，配合一些可见性判断的【参数】，比如事务id，来确定当前事务可以被读取的版本。

当执行了不上锁的select语句时，就会产生一个Read View，Read View由四个变量组成的（Read View像一个结构体或者java类的实例和属性）：

2、readView的结构

举一个列子，当前有事务id为12、13、14、16、20的五个事务，他们在同时修改一条数据，此时，事务13发生读取行为，在【事务13】读取之前【事务14】已经提交，当前场景下，将产生一个readview如下：

一个readView就是一个【结构体】，你甚至可以理解成为java里的实例（readview）和属性，包含属性如下：

m_ids：生成该readview时，当前系统中【活跃的事务】id列表。对于当前案例，因为14已经提交，就不活跃了，所以该变量的值为[12,13,16,20]。
min_trx_id：当前系统【活跃事务】中最小的【事务id】，他也是m_ids的最小值，当前案例的值就是12。
max_trx_id：当前系统中计划分配给下一个事务的id，他可能是m_ids的最大值+1，也可能比他大。当前案例值假设为22。
creator_trx_id：生成这个readView的事务id，当前案例的值为12。

以上readview配合undo log就可以形成一个【快照】，那他是怎么读取的呢？

3 . 快照都原理解析：

在一个事务读取数据时，会根据当前数据形成一个readview，读取时会按照以下逻辑进行读取：

如果【被访问数据的事务trx_id】和readView中的【creator_trx_id值】相同，意味着自己在访问自己修改过的记录，当然可以被访问。
如果【被访问数据的事务trx_id】小于readView中的【min_trx_id】值，说明生成这个版本数据的事务，在生成readview前已经提交，这样的数据也可以访问。

**通俗一点：**这个数据之前被其他的事务修改过，但是事务已经提交，所以这个版本的数据是可以使用的，这样不会产生脏读。
如果【被访问数据的事务trx_id】大于等于readView中的max_trx_id值，说明生成这个版本数据的事务，是在生成readview后开启，这样的数据不应该被访问。

**通俗一点：**你读取数据之后，有人修改了当前数据，那人家后边修改的数据，你也不能读。
如果【被访问数据的事务trx_id】如果在min_trx_id和max_trx_id范围内，则需要判断是不是在【m_ids】中（目的是判断这个数据是不是已经提交）。如果在，说明生成readView时，这个版本的事务还是活跃的，没有提交的事务产生的数据当然不能读，如果不在，说明事务已经提交，该数据可以被访问。

**通俗一点：**这个数据被现在活跃的其他事务正在修改中，读取时要看此时这个事务是不是已经提交，目的也是为了不要读取别人未提交的事务。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZJiUshyP-1681790228549)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659777770611.png)]

最开始原始数据为tom 30 100 100 和回滚指针，此时由三个事务201，202，203同时并发对该数据进行操作，

201把age改为24，产生最新版本，历史版本1和最新版本使用回滚指针连接。202又将名字改为jerry，产生最新版本，此时201的最新版本变为历史版本2，203又将名字改为alice，产生最新版本，202的最新版本变为历史版本3.

此时事务202提交事务，之后事务201开始读取数据，此时生成了一个Read View：由于事务202已经提交了，所以不活跃了，所以m_ids为：201，203，min_trx_id:201，max_trx_id:204（系统下一次要分配的事务id，当i前最大为203，暂时设想为204），creator_trx_id：201.

此时当前数据的事务id是203，在最大和最小id之间，且在m_ids，说明该书屋没有提交，那么就不能读取该事务的数据。就去找历史版本，因为事务202在最大和最小事务id之间，但不在m_ids中，说明该版本已经被提交可以读取，所以就把该版本作为读取的对象。

第二节解决脏读和不可重复读

对于RU隔离级别的事务来说，由于可以读取到未提交的事务，所有直接读取【最新的记录】（当前读）就可以，对于serializable的事务来说，必须使用加锁的方式来访问。

1、解决脏读

先思考一个问题，脏读指的是在当前事务中读取到了其他事务未提交的数据，那解决的思路是什么：

（1）没有undo log+mvcc

一个事务读取了数据之后，立马给这个数据加写锁，不允许其他事务进行修改，这是加锁解决脏读。

（2）使用undo log+mvcc

所有事务对数据的修改，记录成版本链，使用readview进行版本选择，每个事务只能读取满足条件的数据，这个过程不需要加锁。

使用mvcc很好的解决了【读写操作】的并发执行，而且采用了无锁机制。

2、解决可重复读和不可重复读

（1）RC隔离级别，同一个事务中【每次读取数据时都生成一个新的ReadView】，两次读取时，如果中间有其他事务进行提交，可能会生成两个不同的readview，导致当前事务中，两次读取的数据不一致，这就是不可重复读。具体的执行流程如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nD2ttAqD-1681790228550)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659779003467.png)]

如上图，仍然是201，202，203事务并发执行对数据操作，不过没有提交事务，所以m_ids里为201，202，203，其他的不变，此时201读取数据会进行版本选择，当前事务id为203，在最大和最小之间，也在m_ids

中，说明没有提交，不能读取，历史版本3的事务id为202，在m_ids中，也在最大和最小id之间，说明该事务未提交，不能读取，历史版本2事务id是201，与自己id相同，说明是自己修改的数据的历史版本，可以读取。

之事务202提交数据。然后事务201又读取数据，有进行版本选择，当前最新版本为203的，203在最大最小id之间且在m_ids中，说明该事务没有提交，不能读取，历史版本3为事务202的，202在最大最小id之间，不存在m_ids中，说明已经提交事务，可以读取.

因为随着生成Read View的时机不同,那么选择的版本数据也不同,那么就会造成不可重复读的情况(由上可知,第一次选的历史版本2,第二次读取选的历史版本3).与此同时解决了可重复读.

（2）RR隔离级别，同一个事务中【只在第一次读取数据时生成一个ReadView】，以后这个事务中一直使用这个readview，那么同一个事务中就能保证多次读取的数据是一致的，具体的执行流程如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P55g8uuX-1681790228550)(C:\Users\CG\AppData\Roaming\Typora\typora-user-images\1659779376141.png)]

即使202

，提交仍然使用的之前的ReadView，仍然是读取的201版本. 上图可知解决了不可重复读.

3 . 解决幻读

他是通过间隙锁实现的，一旦锁定某一个范围的数据，就会对这个范围的数据加锁，间隙锁保证我们不能在这个范围内插入新的数据。

12、13、14、16、20的五个事务，他们在同时修改一条数据，此时，事务13发生读取行为，在【事务13】读取之前【事务14】已经提交，当前场景下，将产生一个readview如下：

一个readView就是一个【结构体】，你甚至可以理解成为java里的实例（readview）和属性，包含属性如下：

m_ids：生成该readview时，当前系统中【活跃的事务】id列表。对于当前案例，因为14已经提交，就不活跃了，所以该变量的值为[12,13,16,20]。
min_trx_id：当前系统【活跃事务】中最小的【事务id】，他也是m_ids的最小值，当前案例的值就是12。
max_trx_id：当前系统中计划分配给下一个事务的id，他可能是m_ids的最大值+1，也可能比他大。当前案例值假设为22。
creator_trx_id：生成这个readView的事务id，当前案例的值为12。

以上readview配合undo log就可以形成一个【快照】，那他是怎么读取的呢？

3 . 快照都原理解析：

在一个事务读取数据时，会根据当前数据形成一个readview，读取时会按照以下逻辑进行读取：

如果【被访问数据的事务trx_id】和readView中的【creator_trx_id值】相同，意味着自己在访问自己修改过的记录，当然可以被访问。
如果【被访问数据的事务trx_id】小于readView中的【min_trx_id】值，说明生成这个版本数据的事务，在生成readview前已经提交，这样的数据也可以访问。

**通俗一点：**这个数据之前被其他的事务修改过，但是事务已经提交，所以这个版本的数据是可以使用的，这样不会产生脏读。
如果【被访问数据的事务trx_id】大于等于readView中的max_trx_id值，说明生成这个版本数据的事务，是在生成readview后开启，这样的数据不应该被访问。

**通俗一点：**你读取数据之后，有人修改了当前数据，那人家后边修改的数据，你也不能读。
如果【被访问数据的事务trx_id】如果在min_trx_id和max_trx_id范围内，则需要判断是不是在【m_ids】中（目的是判断这个数据是不是已经提交）。如果在，说明生成readView时，这个版本的事务还是活跃的，没有提交的事务产生的数据当然不能读，如果不在，说明事务已经提交，该数据可以被访问。

**通俗一点：**这个数据被现在活跃的其他事务正在修改中，读取时要看此时这个事务是不是已经提交，目的也是为了不要读取别人未提交的事务。

[外链图片转存中…(img-ZJiUshyP-1681790228549)]

最开始原始数据为tom 30 100 100 和回滚指针，此时由三个事务201，202，203同时并发对该数据进行操作，