DQL查询与多表操作与事务和索引

数据库开发-MySQL

1. 单表查询

1
2
3
4
5
-- 单表查询练习
-- 按 job 升序排序,相同,则按入职时间降序排序 (job和entrydate任意一个都不能是null)
SELECT * FROM tb_emp WHERE not (job is null or entrydate is null) ORDER BY job,entrydate desc;

SELECT * FROM tb_emp WHERE job is not null and entrydate is not null ORDER BY job,entrydate desc;

1.1 排序查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。

语法：

1
2
3
4
5
select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

  • 排序方式：

    • ASC ：升序（默认值）
    • DESC：降序

案例1：根据入职时间, 对员工进行升序排序

1
2
3
4
5
6
7
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate ASC; -- 按照entrydate字段下的数据进行升序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by  entrydate; -- 默认就是ASC（升序）

注意事项：如果是升序, 可以不指定排序方式ASC

案例2：根据入职时间，对员工进行降序排序

1
2
3
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate DESC; -- 按照entrydate字段下的数据进行降序排序

案例3：根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序

1
2
3
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate ASC , update_time DESC;

注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

1.3 分页查询

分页操作在业务系统开发时，也是非常常见的一个功能，日常我们在网站中看到的各种各样的分页条，后台也都需要借助于数据库的分页操作。

image-20221206183310586

分页查询语法：

1
select  字段列表  from   表名  limit  起始索引, 查询记录数 ;

案例1：从起始索引0开始查询员工数据, 每页展示5条记录

1
2
3
4
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 0 , 5; -- 从索引0开始，向后取5条记录
或者 limit 5; -- 如果查询的是第1页数据，起始索引可以省略，直接简写为：limit 条数

案例3：查询 第2页 员工数据, 每页展示5条记录

1
2
3
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 5 , 5; -- 从索引5开始，向后取5条记录

案例4：询 第3页 员工数据, 每页展示5条记录

1
2
3
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 10 , 5; -- 从索引10开始，向后取5条记录

注意事项:

  1. 起始索引从0开始。 计算公式 ： 起始索引 = （查询页码 - 1）* 每页显示记录数

  2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

  3. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数

1.3 案例

1.3.1 案例一

案例：根据需求完成员工管理的条件分页查询

image-20221206212240773

分析：根据输入的条件，查询第1页数据

  1. 在员工管理的列表上方有一些查询条件：员工姓名、员工性别，员工入职时间(开始时间~结束时间)

    • 姓名：张
    • 性别：男
    • 入职时间：2000-01-01 ~ 2015-12-31

  2. 除了查询条件外，在列表的下面还有一个分页条，这就涉及到了分页查询

    • 查询第1页数据（每页显示10条数据）

  3. 基于查询的结果，按照修改时间进行降序排序

结论：条件查询 + 分页查询 + 排序查询

SQL语句代码：

1
2
3
4
5
6
7
8
9
10
11
12
-- 根据输入条件查询第1页数据（每页展示10条记录）
-- 输入条件：
   -- 姓名：张 （模糊查询）
   -- 性别：男
   -- 入职时间：2000-01-01 ~ 2015-12-31
-- 分页： 0 , 10
-- 排序： 修改时间  DESC
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name like '张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0 , 10;

1.3.2 案例二

案例：根据需求完成员工信息的统计

image-20221206210536118

分析：以上信息统计在开发中也叫图形报表(将统计好的数据以可视化的形式展示出来)

  • 员工性别统计：以饼状图的形式展示出企业男性员人数和女性员工人数
    • 只要查询出男性员工和女性员工各自有多少人就可以了
  • 员工职位统计：以柱状图的形式展示各职位的在岗人数
    • 只要查询出各个职位有多少人就可以了

员工性别统计：

1
2
3
4
-- 查询所有员工的姓名和职位,如果没有职位,显示成"无"
SELECT
    name,IFNULL(job,'无') 职位
FROM tb_emp;
1
2
3
4
-- if(条件表达式, true取值 , false取值)
select if(gender=1,'男性员工','女性员工') AS 性别, count(*) AS 人数
from tb_emp
group by gender;

if(表达式, tvalue, fvalue) ：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue

员工职位统计：

1
2
3
4
5
6
7
8
9
10
11
-- 使用case语句优化,当查询的职位是1的时候,展示班主任,2的时候展示讲师,3.....
SELECT COUNT(*) 人数,
       case job
           WHEN 1 then '班主任'
           WHEN 2 then '讲师'
           WHEN 3 then '竹里馆'
           WHEN 4 then '财务'
           else '其他岗位'
           end
                职位
FROM tb_emp GROUP BY job

case 表达式 when 值1 then 结果1 [when 值2 then 结果2 …] [else result] end

1
2
3
4
5
6
7
8
9
10
-- 添加一条记录,创建时间和修改时间,设置为当前时间
INSERT INTO tb_emp
VALUES
    (null,'zhangsianfeng','123456','张sif丰',1,'6.jpg',6,SUBSTR(NOW(),1,10),NOW(),NOW());

-- 查询所有人的姓名的时候,去掉第一个字
SELECT SUBSTR(name,2) FROM tb_emp;

-- 查询所有人的姓名的时候,仅保留最后两个字,最后的2可以省略
SELECT SUBSTR(name,-2,2) FROM tb_emp;

执行顺序

DQL语句在执行时的执行顺序，也就是先执行那一部分，后执行那一部分。

image.png

验证：

查询年龄大于15的员工姓名、年龄，并根据年龄进行升序排序。

1
select name , age from emp where age > 15 order by age asc;

在查询时,我们给emp表起一个别名 e,然后在select 及 where中使用该别名。

1
select e.name , e.age from emp e where e.age > 15 order by age asc;

执行上述SQL语句后,我们看到依然可以正常的查询到结果,此时就说明: from 先执行, 然后where 和 select 执行。

那 where 和 select 到底哪个先执行呢?

此时,此时我们可以给select后面的字段起别名,然后在 where 中使用这个别名,然后看看是否可以执行成功。

1
select e.name ename , e.age eage from emp e where eage > 15 order by age asc;

执行上述SQL报错了:
image.png

由此我们可以得出结论: from 先执行,然后执行 where , 再执行select 。

接下来,我们再执行如下SQL语句,查看执行效果:

1
select e.name ename , e.age eage from emp e where e.age > 15 order by eage asc;

结果执行成功。

那么也就验证了: order by 是在select 语句之后执行的。

综上所述，我们可以看到DQL语句的执行顺序为： from … where … group by …
having … select … order by … limit …

2. 多表操作-设计

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多(多对一)
  • 多对多
  • 一对一

2.1 一对多

2.1.1 表设计

需求：根据页面原型及需求文档 ，完成部门及员工的表结构设计

  • 员工管理页面原型：（前面已完成tb_emp表结构设计）

  • 部门管理页面原型：

image-20221206224149094

经过上述分析，现已明确的部门表结构：

  • 业务字段 ： 部门名称
  • 基础字段 ： id(主键)、创建时间、修改时间

部门表 - SQL语句： 

1
2
3
4
5
6
7
8
9
10
11
12
# 建议：创建新的数据库（多表设计存放在新数据库下）
create database db03;
use db03;

-- 部门表
create table tb_dept
(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(10) not null unique  comment '部门名称',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '部门表';

部门表创建好之后，我们还需要再修改下员工表。为什么要修改员工表呢？

是因为我们之前设计员工表(单表)的时候，并没有考虑员工的归属部门。

员工表：添加归属部门字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 员工表
create table tb_emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用户名',
    password    varchar(32) default '123456' comment '密码',
    name        varchar(10)      not null comment '姓名',
    gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image       varchar(300) comment '图像',
    job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
    entrydate   date comment '入职时间',
    
    dept_id     int unsigned comment '部门ID', -- 员工的归属部门
    
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '修改时间'
) comment '员工表';

测试数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 部门表测试数据
insert into tb_dept (id, name, create_time, update_time) values
(1,'学工部',now(),now()),
(2,'教研部',now(),now()),
(3,'咨询部',now(),now()),
(4,'就业部',now(),now()),
(5,'人事部',now(),now());

-- 员工表测试数据
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',1,'2007-02-01',1,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',1,'2008-08-18',1,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',2,'2012-11-01',2,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

员工表 - 部门表之间的关系：

image-20221206230156403

一对多关系实现：在数据库表中多的一方，添加字段，来关联属于一这方的主键。

2.1.2 外键约束

  • 表结构创建完毕后，我们看到两张表的数据分别为：

image-20220831201844375

现在员工表中有五个员工都归属于1号部门(学工部)，当删除了1号部门后，数据变为：

image-20220831202111247

1号部门被删除了，但是依然还有5个员工是属于1号部门的。 此时：就出现数据的不完整、不一致了。

问题分析

目前上述的两张表(员工表、部门表)，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的

问题解决

想解决上述的问题呢，我们就可以通过数据库中的 外键约束 来解决。

外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。

对应的关键字：foreign key

外键约束的语法：

1
2
3
4
5
6
7
8
9
10
-- 创建表时指定
create table 表名(
	字段名    数据类型,
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (主表列名)	
);


-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key(外键字段名) references 主表(主表列名);

那接下来，我们就为员工表的dept_id 建立外键约束，来关联部门表的主键。

方式1：通过SQL语句操作

1
2
3
-- 修改表： 添加外键约束
alter table tb_emp  
add  constraint  fk_dept_id  foreign key (dept_id)  references  tb_dept(id);

方式2：图形化界面操作

image-20221206232750376

当我们添加外键约束时，我们得保证当前数据库表中的数据是完整的。 所以，我们需要将之前删除掉的数据再添加回来。

当我们添加了外键之后，再删除ID为1的部门，就会发现，此时数据库报错了，不允许删除。

外键约束（foreign key）：保证了数据的完整性和一致性。

物理外键和逻辑外键

  • 物理外键

    • 概念：使用foreign key定义外键关联另外一张表。
    • 缺点：
      • 影响增、删、改的效率（需要检查外键关系）。
      • 仅用于单节点数据库，不适用与分布式、集群场景。
      • 容易引发数据库的死锁问题，消耗性能。

  • 逻辑外键

    • 概念：在业务层逻辑中，解决外键关联。
    • 通过逻辑外键，就可以很方便的解决上述问题。

在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。 甚至在一些数据库开发规范中，**会明确指出禁止使用物理外键 foreign key **

2.2 一对一

一对一关系表在实际开发中应用起来比较简单，通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。

一对一的应用场景： 用户表(基本信息+身份信息)
image-20221207104508080

  • 基本信息：用户的ID、姓名、性别、手机号、学历
  • 身份信息：民族、生日、身份证号、身份证签发机关，身份证的有效期(开始时间、结束时间)

如果在业务系统当中，对用户的基本信息查询频率特别的高，但是对于用户的身份信息查询频率很低，此时出于提高查询效率的考虑，我就可以将这张大表拆分成两张小表，第一张表存放的是用户的基本信息，而第二张表存放的就是用户的身份信息。他们两者之间一对一的关系，一个用户只能对应一个身份证，而一个身份证也只能关联一个用户。

那么在数据库层面怎么去体现上述两者之间是一对一的关系呢？

其实一对一我们可以看成一种特殊的一对多。一对多我们是怎么设计表关系的？是不是在多的一方添加外键。

同样我们也可以通过外键来体现一对一之间的关系，我们只需要在任意一方来添加一个外键就可以了。

image-20221207105632634

一对一 ：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

SQL脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 用户基本信息表
create table tb_user(
    id int unsigned  primary key auto_increment comment 'ID',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1 男  2 女',
    phone char(11) comment '手机号',
    degree varchar(10) comment '学历'
) comment '用户基本信息表';
-- 测试数据
insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'),
                        (2,'青翼蝠王',1,'18812340002','大专'),
                        (3,'金毛狮王',1,'18812340003','初中'),
                        (4,'紫衫龙王',2,'18812340004','硕士');

-- 用户身份信息表
create table tb_user_card(
    id int unsigned  primary key auto_increment comment 'ID',
    nationality varchar(10) not null comment '民族',
    birthday date not null comment '生日',
    idcard char(18) not null comment '身份证号',
    issued varchar(20) not null comment '签发机关',
    expire_begin date not null comment '有效期限-开始',
    expire_end date comment '有效期限-结束',
    user_id int unsigned not null unique comment '用户ID',
    constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户身份信息表';
-- 测试数据
insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),
        (2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
        (3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),
        (4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);

2.3 多对多

多对多的关系在开发中属于也比较常见的。比如：学生和老师的关系，一个学生可以有多个授课老师，一个授课老师也可以有多个学生。在比如：学生和课程的关系，一个学生可以选修多门课程，一个课程也可以供多个学生选修。

案例：学生与课程的关系

  • 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

image-20221207113341028

SQL脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 学生表
create table tb_student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
-- 学生表测试数据
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');

-- 课程表
create table tb_course(
   id int auto_increment primary key comment '主键ID',
   name varchar(10) comment '课程名称'
) comment '课程表';
-- 课程表测试数据
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');

-- 学生课程表（中间表）
create table tb_student_course(
   id int auto_increment comment '主键' primary key,
   student_id int not null comment '学生ID',
   course_id  int not null comment '课程ID',
   constraint fk_courseid foreign key (course_id) references tb_course (id),
   constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生课程中间表';
-- 学生课程表测试数据
insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

3. 多表操作-多表查询

3.1 数据准备

SQL脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
#建议：创建新的数据库
create database db04;
use db04;

-- 部门表
create table tb_dept
(
    id          int unsigned primary key auto_increment comment '主键ID',
    name        varchar(10) not null unique comment '部门名称',
    create_time datetime    not null comment '创建时间',
    update_time datetime    not null comment '修改时间'
) comment '部门表';
-- 部门表测试
insert into tb_dept (id, name, create_time, update_time)
values (1, '学工部', now(), now()),
       (2, '教研部', now(), now()),
       (3, '咨询部', now(), now()),
       (4, '就业部', now(), now()),
       (5, '人事部', now(), now());

-- 员工表
create table tb_emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用户名',
    password    varchar(32) default '123456' comment '密码',
    name        varchar(10)      not null comment '姓名',
    gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image       varchar(300) comment '图像',
    job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
    entrydate   date comment '入职时间',
    dept_id     int unsigned comment '部门ID',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '修改时间'
) comment '员工表';
-- 员工表测试数据
INSERT INTO tb_emp(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) 
VALUES 
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

3.2 介绍

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;

查询用户表和部门表中的数据：

1
select * from  tb_emp , tb_dept;

此时,我们看到查询结果中包含了大量的结果集，总共85条记录，而这其实就是员工表所有的记录(17行)与部门表所有记录(5行)的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

image-20221207155509696

在多表查询时，需要消除无效的笛卡尔积，只保留表关联部分的数据

在SQL语句中，如何去除无效的笛卡尔积呢？只需要给多表查询加上连接查询的条件即可。

1
select * from tb_emp , tb_dept where tb_emp.dept_id = tb_dept.id ;

image-20221207164518904

由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到。

3.3 分类

多表查询可以分为：

  1. 连接查询

    • 内连接：相当于查询A、B交集部分数据

    image-20221207165446062

  2. 外连接

    • 左外连接：查询左表所有数据(包括两张表交集部分数据)

    • 右外连接：查询右表所有数据(包括两张表交集部分数据)

  3. 子查询

3.3.1 内连接

内连接查询：查询两表或多表中交集部分数据。

内连接从语法上可以分为：

  • 隐式内连接
  • 显式内连接

隐式内连接语法：

1
select  字段列表   from   表1 , 表2   where  条件 ... ;

显式内连接语法：

1
select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

案例：查询员工的姓名及所属的部门名称

  • 隐式内连接实现
1
2
3
4
5
6
7
8
9
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select e.name 姓名,d.name 部门
from tb_emp e,tb_dept d
where e.dept_id = d.id; -- 消除笛卡尔积

-- 隐式写 查询入职时间在2010年之前的员工的姓名 , 及所属的部门名称
select e.name 姓名,d.name 部门,e.entrydate 入职日期
from tb_emp e,tb_dept d
where year(e.entrydate) < 2010 and e.dept_id = d.id
  • 显式内连接实现
1
2
3
4
5
6
7
8
9
10
-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
select e.name 姓名,d.name 部门
from tb_emp e join tb_dept d
on e.dept_id = d.id;

-- B.显式内连接实现
select e.name 姓名,d.name 部门,e.entrydate 入职日期
from tb_emp e  join tb_dept d
on e.dept_id = d.id   -- 消除笛卡尔积
where year(e.entrydate) < 2010

多表查询时给表起别名：

  • tableA as 别名1 , tableB as 别名2 ;
  • tableA 别名1 , tableB 别名2 ;

注意事项:

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

3.3.2 外连接

外连接分为两种：左外连接 和 右外连接。

左外连接语法结构：

1
select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接语法结构：

1
select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

案例：查询员工表中所有员工的姓名, 和对应的部门名称

1
2
3
4
5
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
select
	e.name,d.name
from tb_emp e left join tb_dept d
	on e.dept_id = d.id;

image-20221207181204792

案例：查询部门表中所有部门的名称, 和对应的员工名称 

1
2
3
4
5
-- 右外连接  即使某个部门没有关联的员工,我们也要查询到它
select
    e.name,d.name
from tb_emp e right join tb_dept d
    on e.dept_id = d.id;

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以了。

而我们在日常开发使用时，更偏向于左外连接。

1
2
3
4
5
6
7
8
9
10
11
12
-- 使用mysql的 union 或 union all 模拟全外链接的效果
select
    e.name,d.name
from tb_emp e left join tb_dept d
    on e.dept_id = d.id

union all

select
    e.name,d.name
from tb_emp e right join tb_dept d
    on e.dept_id = d.id

3.3.3 子查询

3.3.3.1 介绍

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

1
2
3
4
5
6
7
SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );
-- ========================= 子查询 ================================
-- 查询入职时间最早的员工信息
select *from tb_emp where entrydate = (select min(entrydate) from tb_emp);

select min(entrydate)
from tb_emp

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

根据子查询结果的不同分为：

  1. 标量子查询（子查询结果为单个值[一行一列]）

  2. 列子查询（子查询结果为一列，但可以是多行）

  3. 行子查询（子查询结果为一行，但可以是多列）

  4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

  1. where之后
  2. from之后
  3. select之后
3.3.3.2 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，这种子查询称为标量子查询。

常用的操作符： = <> > >= < <=

案例1：查询”教研部”的所有员工信息

可以将需求分解为两步：

  1. 查询 “教研部” 部门ID
  2. 根据 “教研部” 部门ID，查询员工信息
1
2
3
4
5
6
7
-- 1.查询"教研部"部门ID
select id from tb_dept where name = '教研部';    #查询结果：2
-- 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;

-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

案例2：查询在 “方东白” 入职之后的员工信息

可以将需求分解为两步：

  1. 查询 方东白 的入职日期
  2. 查询 指定入职日期之后入职的员工信息
1
2
3
4
5
6
7
-- 1.查询"方东白"的入职日期
select entrydate from tb_emp where name = '方东白';     #查询结果：2012-11-01
-- 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate > '2012-11-01';

-- 合并以上两条SQL语句
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
3.3.3.3 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符 描述
IN 在指定的集合范围之内,多选一
NOT IN 不在指定的集合范围之内
ANY 子查询返回列表中,有任意一个满足即可
SOME 与ANY等同,使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足

案例：查询”教研部”和”咨询部”的所有员工信息

分解为以下两步：

  1. 查询 “教研部” 和 “咨询部” 的部门ID
  2. 根据部门ID, 查询员工信息
1
2
3
4
5
6
7
-- 1. 查询 "教研部" 和 "咨询部" 的所有员工信息
select id from tb_dept where name = '教研部' or name = '咨询部';    #查询结果：3,2
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);

-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
3.3.3.4 行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

案例：查询与”韦一笑”的入职日期及职位都相同的员工信息

可以拆解为两步进行：

  1. 查询 “韦一笑” 的入职日期 及 职位
  2. 查询与”韦一笑”的入职日期及职位相同的员工信息
1
2
3
4
5
6
7
-- 查询"韦一笑"的入职日期 及 职位
select entrydate , job from tb_emp where name = '韦一笑';  #查询结果： 2007-01-01 , 2
-- 查询与"韦一笑"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);

-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');
3.3.3.5 表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

常用的操作符:IN

案例： 查询每个部门下的人数和部门名称

分解为两步执行：

  1. – 查询每个部门下的人数和部门名称
  2. – 查询每个部门下的人数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 查询每个部门下的人数和部门名称
-- 查询每个部门下的人数
select count(*) 人数,dept_id
from tb_emp where dept_id is not null group by  dept_id

-- 把上面查询的结果,当成一个临时表,与部门表关联查询部门的名称和对应的人数
select ls.人数 人数,d.name 部门名称
from (select count(*) 人数,dept_id from tb_emp where dept_id is not null group by  dept_i) ls,tb_dept d 
where ls.dept_id = d.id

-- 直接使用内连接,不用子查询实现
select count(*) 人数,d.name 部门名称
from tb_emp e,tb_dept d where e.dept_id = d.id
group by d.name

-- 某个部门没有人数,显示为 0
select ifnull(ls.人数,0) 人数,d.name 部门名称
from tb_dept d left join (
    select count(*) 人数,dept_id
    from tb_emp where dept_id is not null group by  dept_id
) ls on ls.dept_id = d.id

-- 直接使用左外连接  不用子查询
select ifnull(count(e.id) ,0) 人数,d.name 部门名称
from tb_dept d left join  tb_emp e on d.id = e.dept_id
group by d.name

-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*,d.name
from tb_emp e,tb_dept d
where e.dept_id = d.id and e.entrydate > "2006-01-01"
1
2
3
4
5
6
7
8
9
10
11
12
-- 查询密码大于同部门内平均密码的人的信息
-- 按部门分组,查询每个组的平均密码  ,把查询的结果当成临时表,与 员工内连接查询即可
select avg(password) 平均密码,dept_id
from tb_emp group by dept_id

select e.name,e.password,ls.dept_id,ls.平均密码
from tb_emp e,
     (
         select avg(password) 平均密码,dept_id
         from tb_emp group by dept_id
     ) ls
where e.dept_id = ls.dept_id and e.password > ls.平均密码

3.4 案例

基于之前设计的多表案例的表结构，我们来完成今天的多表查询案例需求。

准备环境

将资料中准备好的多表查询的数据准备的SQL脚本导入数据库中。

image-20221208143318921

  • 分类表：category
  • 菜品表：dish
  • 套餐表：setmeal
  • 套餐菜品关系表：setmeal_dish

image-20221208143312292

需求实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
select
    d.name,d.price,c.name
from
    dish d,category c
where d.category_id = c.id and d.price < 10

-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
select
    d.name,d.price,c.name
from
    dish d left join category c --  (即使菜品没有分类 , 也需要将菜品查询出来)
on d.category_id = c.id
where d.price between 10 and 50 and d.status = 1

-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
select
    max(d.price),c.name
from
    dish d,category c
where c.id = d.category_id
group by  c.name

-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
select c.name,count(d.id) 数量
from
    category c,dish d
where c.id = d.category_id
and d.status = 1
group by c.name
having 数量 >= 3

-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
select
    s.name,s.price,d.name,d.price,sd.copies
from
    setmeal s,setmeal_dish sd,dish d
where
    s.id = sd.setmeal_id and sd.dish_id = d.id and s.name = '商务套餐A';

-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
select avg(price) from dish

select name,price
from dish where price < (select avg(price) from dish)

--  6. 查询出每个分类下低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
-- 分别求每个分类下的平均价格
select avg(price) 平均价格,category_id from dish group by category_id

-- 把上面的结果与菜品表内连接查询
select d.name,d.price,ls.category_id,ls.平均价格
from
    dish d,(select avg(price) 平均价格,category_id from dish group by category_id) ls
where d.category_id = ls.category_id and d.price < ls.平均价格

4. 事务

事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。

场景：学工部整个部门解散了，该部门及部门下的员工都需要删除了。

  • 操作：

    1
    2
    3
    4
    5
    -- 删除学工部
    delete from dept where id = 1;  -- 删除成功

    -- 删除学工部的员工
    delete from emp where dept_id = 1; -- 删除失败（操作过程中出现错误：造成删除没有成功）

  • 问题：如果删除部门成功了，而删除该部门的员工时失败了，此时就造成了数据的不一致。

​ 要解决上述的问题，就需要通过数据库中的事务来解决。

4.1 介绍

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

4.2 操作

MYSQL中有两种方式进行事务的操作：

  1. 自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交）
  2. 手动提交事务：先开启，再提交

事务操作有关的SQL语句：

SQL语句 描述
start transaction; / begin ; 开启手动控制事务
commit; 提交事务
rollback; 回滚事务

手动提交事务使用步骤：

  • 第1种情况：开启事务 => 执行SQL语句 => 成功 => 提交事务
  • 第2种情况：开启事务 => 执行SQL语句 => 失败 => 回滚事务

使用事务控制删除部门和删除该部门下的员工的操作：

1
2
3
4
5
6
7
8
-- 开启事务
start transaction ;

-- 删除学工部
delete from tb_dept where id = 1;

-- 删除学工部的员工
delete from tb_emp where dept_id = 1;
  • 上述的这组SQL语句，如果如果执行成功，则提交事务
1
2
-- 提交事务 (成功时执行)
commit ;
  • 上述的这组SQL语句，如果如果执行失败，则回滚事务
1
2
-- 回滚事务 (出错时执行)
rollback ;

4.3 四大特性

面试题：事务有哪些特性？

  • 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

事务的四大特性简称为：ACID

image.png

  • 原子性（Atomicity） ：原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败。

  • 一致性（Consistency）：一个事务完成之后数据都必须处于一致性状态。

​ 如果事务成功的完成，那么数据库的所有变化将生效。

​ 如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。

  • 隔离性（Isolation）：多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。

​ 一个事务的成功或者失败对于其他的事务是没有影响。

  • 持久性（Durability）：一个事务一旦被提交或回滚，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。

4.4 事务隔离级别引发的问题

image-20260304152529176

1). 赃读: 一个事务读到另外一个事务还没有提交的数据。

image.png

比如B读取到了A未提交的数据。

2). 不可重复读: 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。

image.png

事务A两次读取同一条记录,但是读取到的数据却是不一样的。

3). 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 “幻影”。

image.png

事务隔离级别

为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:

隔离级别 脏读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable Read(默认) × × √
Serializable × × ×

1). 查看事务隔离级别

1
SELECT @@TRANSACTION_ISOLATION;

2). 设置事务隔离级别

1
2
3
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |

READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低。

5. 索引

5.1 介绍

索引(index)：是帮助数据库高效获取数据的数据结构 。

  • 简单来讲，就是使用索引可以提高查询的效率。

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。

针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构。

image.png

此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

备注：这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，后面会详细介绍。

添加索引后查询：

1
2
3
4
5
-- 添加索引
create index idx_sku_sn on tb_sku (sn);  #在添加索引时，也需要消耗时间

-- 查询数据（使用了索引）
select * from tb_sku where sn = '100000003145008';

image-20221209120107543

优点：

  1. 提高数据查询的效率，降低数据库的IO成本。
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

缺点：

  1. 索引会占用存储空间。
  2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

5.2 结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构 描述
B+Tree索引 最常见的索引类型,大部分引擎都支持 B+ 树索引
Hash索引 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引) 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

上述是MySQL中所支持的所有的索引结构,接下来,我们再来看看不同的存储引擎对于索引结构的支持情况。

索引 InnoDB MyISAM Memory
B+tree索引 支持 支持 支持
Hash 索引 不支持 不支持 支持
R-tree 索引 不支持 支持 不支持
Full-text 5.6版本之后支持 支持 不支持

注意: 我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。

在没有了解B+Tree结构前，我们先回顾下之前所学习的树结构：

二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

image-20221208174135229

当我们向二叉查找树保存数据时，是按照从大到小(或从小到大)的顺序保存的，此时就会形成一个单向链表，搜索性能会打折扣。

image-20221208174859866

可以选择平衡二叉树或者是红黑树来解决上述问题。（红黑树也是一棵平衡的二叉树）

image-20221209100647867

但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。

思考：采用二叉搜索树或者是红黑树来作为索引的结构有什么问题？

答案 最大的问题就是在数据量大的情况下，树的层级比较深，会影响检索速度。因为不管是二叉搜索数还是红黑数，一个节点下面只能有两个子节点。此时在数据量大的情况下，就会造成数的高度比较高，树的高度一旦高了，检索速度就会降低。

说明：如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO，那么100万用户，那么会造成效率极其低下。所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存一个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：

image.png

知识小贴士: 树的度数指的是一个节点的子节点个数。

image.png

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

特点：

  • 5阶的B树，每一个节点最多存储4个key，对应5个指针。
  • 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
  • 在B树中，非叶子节点和叶子节点都会存放数据。

下面我们来看看**B+Tree(多路平衡搜索树)**结构中如何避免这个问题：

image-20221208181315728

B+Tree结构：

  • 每一个节点，可以存储多个key（有n个key，就有n个指针）
  • 节点分为：叶子节点、非叶子节点
    • 叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上
    • 非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针
  • 为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询

非叶子节点都是由key+指针域组成的，一个key占8字节，一个指针占6字节，而一个节点总共容量是16KB，那么可以计算出一个节点可以存储的元素个数：16*1024字节 / (8+6)=1170个元素。

  • 查看mysql索引节点大小：show global status like ‘innodb_page_size’; – 节点大小：16384

当根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：1170*1170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。

对于第二层每个元素有指针，那么会找到第三层，第三层由key+数据组成，假设key+数据总大小是1KB，而每个节点一共能存储16KB，所以一个第三层一个节点大概可以存储16个元素(即16条记录)。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W*16结果就是2000W+的元素个数。

结合上述分析B+Tree有如下优点：

  • 千万条数据，B+Tree可以控制在小于等于3的高度
  • 所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找

我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html

拓展：

MySQL中除了支持B+Tree索引,还支持一种索引类型—Hash索引。

1). 结构

哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。

image.png

如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。

img

2). 特点

A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）

B. 无法利用索引完成排序操作

C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

3). 存储引擎支持

在MySQL中,支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能,hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

思考题： 为什么InnoDB存储引擎选择使用B+tree索引结构?

A. 相对于二叉树，层级更少，搜索效率高；

B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

C. 相对Hash索引，B+tree支持范围匹配及排序操作；

5.3 语法

创建索引

1
create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;

案例：为tb_emp表的name字段建立一个索引

1
create index idx_emp_name on tb_emp(name);

在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一约束

image-20221209105846211

查看索引

1
show  index  from  表名;

案例：查询 tb_emp 表的索引信息

1
show  index  from  tb_emp;

删除索引

1
drop  index  索引名  on  表名;

案例：删除 tb_emp 表中name字段的索引

1
drop index idx_emp_name on tb_emp;

注意事项：

  • 主键字段，在建表时，会自动创建主键索引
  • 添加唯一约束时，数据库实际上会添加唯一索引

超通俗事务 + 隔离级别详解

我会用银行转账 + 学生表两个最生活化的例子，从 “什么是事务” 讲到 “为什么需要隔离级别”，全程不用晦涩术语，看完你就能彻底搞懂。

一、先搞懂：事务到底是什么？

一句话定义：事务是一组要么全部成功、要么全部失败的数据库操作，是一个不可分割的整体。

最经典的例子：银行转账

假设你（账户 A）要给朋友（账户 B）转 1000 元，这个过程包含 2 步数据库操作：

  1. 你的账户 A 余额 -1000 元
  2. 朋友的账户 B 余额 +1000 元

如果没有事务会发生什么？

  • 第一步执行成功（你扣了 1000）
  • 第二步执行失败（比如数据库突然崩溃、网络断了）
  • 结果：你平白无故少了 1000，朋友一分没收到，钱凭空消失了！

有了事务就不一样了：

这两步会被打包成一个事务。只要有任何一步失败，数据库会自动回滚（撤销所有已经执行的操作），回到转账前的状态；只有两步都成功，才会提交（永久保存数据）。

二、事务的四大特性（ACID）

这是事务必须满足的 4 个基本要求，用转账例子就能全懂：

表格

特性 通俗解释 转账例子
原子性（Atomicity） 事务是最小单位，不可拆分，要么全成，要么全败 扣钱和加钱不能只做一半
一致性（Consistency） 事务执行前后，数据的逻辑完整性保持不变 转账前后，A+B 的总金额永远是 2000 元（A 原来 1000，B 原来 1000）
隔离性（Isolation） 多个事务同时执行时，互相不能干扰 你给 B 转账的同时，C 也给 B 转账，两个转账不能互相影响
持久性（Durability） 事务一旦提交，数据就永久保存，不会丢失 转账成功后，哪怕数据库立刻崩溃，重启后你的钱还是少了 1000，B 的钱还是多了 1000

三、为什么需要 “隔离级别”？并发事务的 3 个大坑

如果多个事务同时操作同一份数据，又没有任何隔离措施，就会出现你提到的 3 个严重问题：脏读、不可重复读、幻读。

我用同一个银行账户和同一个学生表，一步步给你演示每个问题是怎么发生的。

1. 脏读：读到了别人 “还没提交” 的垃圾数据

定义：一个事务读到了另一个事务未提交的数据。

核心问题：别人可能会回滚，你读到的是无效数据。

表格

时间 事务 A（你） 事务 B（银行柜员）
1 开始事务 开始事务
2 你的账户余额：1000 元
3 给你的账户存 500 元（执行 update，余额变成 1500）
4 查余额：1500 元（脏读！读到了 B 未提交的数据）
5 发现存错了，回滚事务（余额变回 1000）
6 你以为自己有 1500，花了 1200，结果扣款失败

你明明查到有 1500，却花不了，因为那 500 根本就没真正存进来，是 “脏数据”。

2. 不可重复读：同一个事务里，两次读同一条数据，结果不一样

定义：一个事务先后读取同一条记录，但两次读取的数据不同。

核心问题：别人修改并提交了数据，导致你同一个事务内前后读取结果不一致。

表格

时间 事务 A（你） 事务 B（你老婆）
1 开始事务 开始事务
2 查余额：1000 元（准备买个 800 元的东西）
3 取走 500 元（执行 update，余额变成 500，提交事务）
4 再查余额：500 元（不可重复读！同一个事务两次读同一条记录，结果不一样）
5 你懵了：刚才还有 1000，怎么突然就剩 500 了？

注意：不可重复读是针对同一条记录的修改（update）。

3. 幻读：同一个事务里，按条件查询，结果 “凭空多了 / 少了一条”

定义：一个事务按照条件查询数据时，没有对应的数据行，但插入时却发现已经存在；或者第一次查到 N 条，第二次查到 N+1 条，像出现了 “幻影”。

核心问题：别人新增 / 删除了符合你查询条件的数据，导致你同一个事务内查询结果的行数不一样。

表格

时间 事务 A（教务处老师） 事务 B（另一个老师）
1 开始事务 开始事务
2 查询学号为 “2026001” 的学生：没有这条记录
3 准备插入学号 “2026001” 的学生张三
4 插入了学号 “2026001” 的学生李四（提交事务）
5 执行插入操作：报错！主键冲突，该学号已存在（幻读！）
6 老师懵了：我刚才明明查过没有啊？怎么突然就有了？

不可重复读 vs 幻读 关键区别：

  • 不可重复读：针对同一条记录的修改（update），两次读的是同一行，值不一样
  • 幻读：针对新增 / 删除的记录（insert/delete），两次读的是行数不一样

四、事务隔离级别：解决上面 3 个问题的方案

数据库提供了 4 种隔离级别，从低到高，隔离性越来越强，性能越来越差。你可以根据业务需求选择合适的级别。

4 种隔离级别总览

表格

隔离级别 能解决的问题 不能解决的问题 常用程度
读未提交（Read Uncommitted） 无 脏读、不可重复读、幻读 几乎不用
读已提交（Read Committed） 脏读 不可重复读、幻读 Oracle、SQL Server 默认
可重复读（Repeatable Read） 脏读、不可重复读 幻读（MySQL 特殊处理后基本解决） MySQL InnoDB 默认，最常用
串行化（Serializable） 脏读、不可重复读、幻读 无 性能极差，几乎不用

逐个详解每个隔离级别

1. 读未提交（最低级别）

  • 规则：一个事务可以读到其他所有事务未提交的数据
  • 问题：会出现脏读、不可重复读、幻读
  • 场景：几乎没有任何业务会用这个级别，因为太不安全了

2. 读已提交

  • 规则：一个事务只能读到其他事务已经提交的数据

  • 解决：脏读（再也读不到别人没提交的垃圾数据了）

  • 问题：还是会出现不可重复读和幻读

  • 例子：回到前面不可重复读的例子，在这个级别下：

    • 你老婆取走 500 元提交后，你才能读到余额变成 500
    • 但你同一个事务内还是会出现两次读余额不一样的情况

3. 可重复读（MySQL 默认，重点掌握）

  • 规则：一个事务内，多次读取同一条记录的结果永远一致

  • 解决：脏读 + 不可重复读

  • 关于幻读：MySQL InnoDB 引擎通过间隙锁（一种特殊的锁机制），很大程度上解决了幻读问题，所以日常开发中用这个级别就足够了

  • 例子：还是不可重复读的例子，在这个级别下：

    • 你第一次查余额是 1000 元
    • 之后不管你老婆怎么取、存、提交，只要你的事务还没结束，你再查余额永远是 1000 元
    • 只有当你提交了自己的事务，下次再查才能看到最新的余额

4. 串行化（最高级别）

  • 规则：所有事务排队串行执行，一个事务执行完了，下一个才能开始
  • 解决：所有并发问题
  • 问题：性能极差，就像银行只有一个窗口，所有人都要排队，人多了会卡死
  • 场景：只有对数据一致性要求极高，且并发量极低的业务才会用（比如银行核心账户）