提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

---

一、多表设计

1 多表设计-概述

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

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

下面我们也从这三个方面开始学习。

2 三种多表关系

一对多（多对一）

（1）无外键约束（逻辑外键）：建立独立的部门表和员工表（一个部门下面可以有很多员工，这就是典型的一对多）

参考视频

• step1：创建一个一对多的结构：部门表 —> 员工表 （一个部门里面有很多员工）

create table tb_emp(id int 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 int not null comment '性别, 1:男  2:女',   -- 1:男  2:女image varchar(300) comment '头像的URL',     -- http://www.baidu.com/a.jpgjob int comment '职位, 1:班主任  2:讲师  3:学工主管 4：教研主管',entry_date date comment '入职日期',  -- 2020-01-01 年月日dept_id int comment '归属的部门ID',  -- 其实就是部门表的主键 id 字段create_time datetime not null comment '创建时间', -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment '更新时间'  -- 2020-01-01 10:10:10 年月日时分秒
) comment '员工表';create table tb_dept(id int primary key auto_increment comment '主键ID,唯一标识',name varchar(10) not null unique comment '部门名称',create_time datetime not null comment '创建时间', -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment '更新时间'  -- 2020-01-01 10:10:10 年月日时分秒
) comment '部门表';

• step2：往两张表里面插入数据

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, entry_date, 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());

• 问题分析：这样我们建立了部门表和员工表，但是有一个问题：

  • 部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。
    e.g. 例如我们删除了部门表中的一个部门，按道理来说对应部门下的员工数据都要删除，但是我们上面那种创建表的方式，并不会从员工表中删除对应部门员工数据。
    也就是说我们上面那种方式创建的表其实还是相互独立的
    

  • 解决方案：
    （1）逻辑外键：不在数据库层进行映射，在其他业务或者应用层进行映射，不考虑修改一张表，另一张表也要对应变化（互联网大厂都是采用这种做法，其实就是我们这里的这种非外键约束的方式）
    （2）外键约束（物理外键）：采用外键约束将表联系起来（外键约束互联网大厂都禁止使用，因为和优点比起来缺点更难处理）
    

（2）外键约束：建立依赖的部门表和员工表（一个部门下面可以有很多员工，这就是典型的一对多）（也学一下，虽然互联网大厂数据库层面都禁止使用外键约束）

参考视频

• 外键约束的弊端：

  • 改、删时要考虑外键：每次做DELETE 或者UPDATE都必须考虑外键约束，不方便。
  • 表级锁导致并发差：并发问题外键约束会启用行级锁主表写入时会进入阻塞
  • 级联删除问题：删除主表的一条记录，该记录外键关联的从表记录也会随之删除，导致数据不可控。例如删除“订单表”的一条订单，关联的“订单详情表”的一条记录也会随之删除。
  • 耦合高、迁移麻烦：主表从表之间互相耦合，主表数据量过大要分表并迁移数据时，就必须先删除外键，不然你刚删完主表的一条记录，从表关联记录也级联删除了，导致数据丢失。
    

• 语法解释：

  • 外键约束添加是添加在子表的语法中的，一定要注意
  • 其中【外键名称】：就是你给这个外键约束自己取一个名字，自己随便写就是了
  • 主表：就是对应一对多中对应“一”的表
  • 子表：就是对应一对多中对应“多”的表

• step1：创建一个一对多的结构：部门表 —> 员工表 （一个部门里面有很多员工）

  • 创建子表里面写外键约束语法
  • 父表要在子表前创建出来，不然子表中外键约束会报错
  • 【注】也可以先用常规方法创建好两张表后再使用
    alter table tb_emp add constraint tb_emp_fk_dept_id foreign key (dept_id) references tb_dept (id);

create table tb_dept(id int primary key auto_increment comment '主键ID,唯一标识',name varchar(10) not null unique comment '部门名称',create_time datetime not null comment '创建时间', -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment '更新时间'  -- 2020-01-01 10:10:10 年月日时分秒
) comment '部门表';create table tb_emp(id int 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 int not null comment '性别, 1:男  2:女',   -- 1:男  2:女image varchar(300) comment '头像的URL',     -- http://www.baidu.com/a.jpgjob int comment '职位, 1:班主任  2:讲师  3:学工主管 4：教研主管',entry_date date comment '入职日期',  -- 2020-01-01 年月日dept_id int comment '归属的部门ID',  -- 其实就是部门表的主键 id 字段create_time datetime not null comment '创建时间', -- 2020-01-01 10:10:10 年月日时分秒update_time datetime not null comment '更新时间',  -- 2020-01-01 10:10:10 年月日时分秒constraint fk_dept_id foreign key (dept_id) references tb_dept(id)    -- 外键约束,将子表的dept_id字段与父表的id字段进行关联) comment '员工表';

• step2：往两张表里面插入数据

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, entry_date, 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());

• step3：删除部门表中一个部门数据，验证一下外键约束

-- 删除部门表中id = 1的记录,其实就是删除学工部
delete from tb_dept where id = 1;    -- 会发现删除失败,因为员工表中dept_id = 1学工部员工，外键约束你不能删除-- 删除部门表中id = 5的记录,其实就是删除人事部
delete from tb_dept where id = 5;    -- 会发现删除成功,因为员工表中没有dept_id = 5人事部员工，此时就可以删除成功

这就是外键约束

（3）逻辑外键（无外键约束的创建多表）与物理外键（采用外键约束的方式创建多表）

我们下面的继续学习也是采用逻辑外键，也就是不采用外键约束的那种方式来学习。

一对一（多用于表的拆分，一张大表拆成多张小表）

拆分成下面两张表（本质上一对一之间看做一对多的一种特例也就能实现了）：

-- 一对一: 用户 与 身份证
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);

多对多

--  多对多: 学生 与 课程
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 案例：（苍穹外卖）多表关系分析设计

参考视频1
参考视频2
这是一个外卖平台的案例：里面三个功能分类管理、菜品管理、套餐管理模块都对应着一张表

• 分类管理（分类表）：包含菜品的类别、套餐的类别
  

• 菜品管理（菜品表）：菜品表，有哪些菜品
  

• 套餐管理（套餐表）：有哪些套餐
  

简单分析一下就有下面的结构设计图：

• 分析：
  • 每个菜品分类下面有多种菜品，所以分类表和菜品表是一对多关系
  • 每个套餐只能属于一个菜品分类，一个菜品分类下面有多种套餐（需求上面规定是），所以分类表和套餐表间也是一对多关系
  • 每个套餐下面有多种菜品，每个菜品可以属于不同套餐，所以套餐表和菜品表间是属于多对多关心，所以需要一张中间表

采用物理外键的方式，将创建表的SQL语句写了下：

# 1 分类表
create table category
(id   int auto_increment primary key comment '主键ID',name varchar(20) not null unique comment '分类名称',type int not null comment '分类类型, 1.菜品分类 2.套餐分类 ',sort int not null comment '排序字段',status int not null default 0 comment '状态字段, 1.启用 0.禁用',create_time datetime not null comment '创建时间',update_time datetime not null comment '修改时间'
) comment '分类表';# 2 菜品表
create table dish
(id          int auto_increment primary key comment '主键ID',name        varchar(20) not null unique comment '菜品名称',category_id int not null comment '所属分类ID(菜品分类)，外键关联category表',price       decimal(8, 2) not null comment '菜品价格',image       varchar(300) not null comment '菜品图片',description varchar(200) comment '菜品描述',status      int not null default 0 comment '状态字段, 1.启售 0.停售',create_time datetime not null comment '创建时间',update_time datetime not null comment '修改时间'
) comment '菜品表';# 3 套餐表
create table setmeal
(id          int auto_increment primary key comment '主键ID',name        varchar(20) not null unique comment '套餐名称',category_id int not null comment '所属分类ID(套餐分类)，外键关联category表',price       decimal(8, 2) not null comment '套餐价格',image       varchar(300) not null comment '套餐图片',description varchar(200) comment '套餐描述',status      int not null default 0 comment '状态字段, 1.启售 0.停售',create_time datetime not null comment '创建时间',update_time datetime not null comment '修改时间'
) comment '套餐表';# 4 套餐菜品关系中间表
create table setmeal_dish
(id        int auto_increment primary key comment '主键ID',setmeal_id int not null comment '套餐ID, 外键关联setmeal表主键',dish_id   int not null comment '菜品ID, 外键关联dish表主键',copys     int not null comment '表示该套餐中该菜品的数量'
) comment '套餐菜品关系中间表';

4 数据库中表设计的范式原则

参考视频

• 概念: 设计数据库时，需要遵循的一些规范。

  • 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
    也就是说满足第二范次就一定满足第一范次，依次下去
  • 目前关系数据库有六种范式: 第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF) 和 第五范式(5NF又称完美范式)。
  • 一般来说，如果我们数据库的设计能够满足前三个范式就已经设计的很完美了，我们也只学习这前三大范式。

• 分类

  • 第一范式(1NF)：:每一列都是不可分割的原子数据项
  • 第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
  • 第三范式(3NF)：在2NF基础上，任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)

下面用一张表来依次说明这个表的设计有什么问题

第一范式：很显然上面这张表的设计中 系 这个字段 分成了 系名、系主任这两个子字段，这就违反了第一范式。应该修改：

这么一修改，就符合了第一范式，但是还是有一些问题。

这些问题可以通过第二、第三范式来解决。

先来理解一下第二范式：

• 第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
  • 几个概念:
    • 1. 函数依赖: A–>B, 如果通过A属性(或属性组)的值，可以确定唯一B属性的值。则称B依赖于A
         例如: 学号 --> 姓名； (学号，课程名称) --> 分数
    • 2. 完全函数依赖: A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
         例如: (学号，课程名称)–>分数
    • 3. 部分函数依赖: A–>B，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
         例如: (学号，课程名称) --> 姓名
    • 4. 传递函数依赖:A–>B，8 -->C 。如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性(属性组)的值可以确定唯一C
         属性的值，则称C 传递函数依赖于A
         例如: 学号–>系名，系名–>系主任
    • 5. 码: 如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
         就是如果一个属性或属性组可以确定剩下的属性，那这个属性（或属性组）就称为这张表的码。
         例如: 该表中码为: (学号，课程名称) ，只要知道了学号和课程名称就能确定姓名、系名、系主任、分数
         （1）主属性: 码属性组中的所有属性
         （2）非主属性: 除过码属性组的属性
         很显然，分数完全依赖于码(学号，课程名称)，但是姓名、系名、系主任只是部分依赖于码(学号，课程名称)
         这就不符合第二范式了，第二范式要求非主属性必须完全依赖于码。
         解决方案：进行表的拆分（将姓名、系名、系主任这几个不是完全依赖的独立出去成一张表继续分析，学号就可以作为主键当逻辑外键）

第二范式将数据冗余的问题解决了，但是任然存在：

下面就要使用第三范式来解决这个问题

• 第三范式(3NF)：在2NF基础上，任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
  上面的 学号可以确定系名，系名又可以确定系主任，很显然这里构成了传递
  解决方案：继续拆
  
  这样就完全解决了上面的三个弊端。

5 小结

• 一对多
  在多的一方添加外键，关联另外一方的主键，
• 一对一
  任意一方，添加外键，关联另外一方的主键
• 多对多
  通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键

二、多表查询

• 多表查询:指从多张表中查询数据

0 数据准备

先讲我们要进行的查询数据的表创建好，数据也insert进去

还是从我们熟悉的部门表与员工表

-- 多表查询: 数据准备
-- 部门管理
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());

1 多表查询概述

参考视频

（1）引入：多表查询中的笛卡尔现象与解决方案

• 单表查询基本语法: select * from 表名；
  我们自然猜测多表查询基本语法：select * from 表名1，表名2…；

下面就来试一试：

select * from tb_emp, tb_dept;

很显然这个结果将两张表安装横轴拼接，并且按照笛卡尔积产生了很多条无效数据。

• 笛卡尔积:笛卡尔乘积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况。
  
  怎么解决这种现象，很简单，利用我们的where和我们表中的逻辑外键就可以了。
• 在多表查询时，需要消除无效的笛卡尔积
  

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

这样就正确了，这样就知道每一个员工属于哪一个部门了。

（2）多表查询的分类概念：连接查询（内连接、外连接）和子查询

上面的引入是最基础的多表查询，在实际的多表查询中主要分为两大类

2 连接查询

（1）内连接查询：select 字段列表 from 表1 [inner] join 表2 on 连接条件…；

参考博客

• 内连接查询：就是查询两张表交集部分的数据
  
  很显然显示的连接语法更加明显（后面加上了进阶用法十分灵活）
  在【4 案例：（苍穹外卖）多表查询练习】里面就会发现

【注】：这里的交关系不要用我们传统的集合观念来理解，这里要使用我们表中的逻辑外键来理解，看完下面案例就明白了

-- A.查询员工的姓名 ，及所属的部门名称(隐式内连接实现)
select tb_emp.name,tb_dept.name from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;-- select e.name,d.name from tb_emp as e, tb_dept as d where e.dept_id = d.id;    -- 别名时的写法-- B.查询员工的姓名 ，及所属的部门名称(显示内连接实现)
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

用这个例子来解释这里内连接中的交集什么意思。其实员工表中有17个员工，但是上面只查出来了16条，还有一个呢？这是因为有一个员工的dept_id是null，没有部门，这意味着该员工没有与部门表联系上，所以这条数据就应该是在交集外面的。

从上面的例子也可以看出，内连接也是多表间横向连接起来。

（2）外连接查询

左外连接：select 字段列表 from 表1 left [outer] join 表2 on 连接条件…；

右外连接：select 字段列表 from 表1 right [outer] join 表2 on 连接条件…；

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

• 左外连接：完全包含左表的数据和左右表交集部分的数据
• 右外连接：完全包含右表的数据和左右表交集部分的数据
  
  【注】：很显然，左右外连接的语法是可以相互转换的，因此我们开发中多使用左外连接。
  【注】：本质上外连接和内连接实现的效果差不多，外连接多一个想要完全保存其中哪一张表的数据

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

可以看到这个外连接将左表中的这条null数据也可以查询出来，这就是外连接相较于内连接多出来的功能

-- B.查询部门表 所有 部门的名称，和对应的员工名称(右外连接)
select tb_emp.name,tb_dept.name from tb_emp right outer join tb_dept on tb_emp.dept_id = tb_dept.id;select * from tb_emp right outer join tb_dept on tb_emp.dept_id = tb_dept.id;

可以看到这里外连接要求保存完整的是部门表，所以里面就业部和人事部在员工表中没有员工数据，查询出来的结果横向拼接就将其余字段全部用null填充了。

【注】：所以这里的交集也不能用我们传统的那种交集思想去理解，或许将交集改成联系这个词来解释可能会更加恰当。

（3）内外连接语法进阶（重要重要！！！！！！）

---- 在on连接后可以继续加上 where条件查询、group by分组查询等

显示内外连接进阶语法（重要！！！！）
如果要在连接后的表后边继续查询，可以继续在后面加上 where条件查询、group by分组查询这些。

• 语法：类似
  select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id where 条件… group by …;

具体的使用见下面的综合案例
具体案例使用见 【4 案例：（苍穹外卖）多表查询练习】

— 多对多怎么三张表连接（前面都是两张表连接查询）

具体案例使用见 【4 案例：（苍穹外卖）多表查询练习】
【注：中间表放中间要】

• 语法demo：
  select s.name,s.price,d.name,d.price,sd.copies from setmeal s
  inner join setmeal_dish sd on s.id = sd.setmeal_id
  inner join dish d on sd.dish_id = d.id
  where s.name = ‘商务套餐A’;

3 子查询（也称为嵌套查询）

（1）标量子查询: 子查询返回的结果为单个值( 标量用 =，>,<等 )

• 语法：
  select * from t1 where column1 = (select column1 from t2 …);
  
  【技巧】：将问题先拆解，先想里面的子查询语句要写什么

-- 标量子查询
-- A.査询"教研部”的所有员工信息-- a.查询教研部的部门IDselect id from tb_dept where name = '教研部';-- b.再查询教研部的所有员工信息select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

-- B.查询在"方东白”入职之后的员工信息-- a.查询方东白的入职时间select entrydate from tb_emp where name = '方东白';-- b.再查询在方东白入职之后的员工信息select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

（2）列子查询: 子查询返回的结果为一列（列向量用 in,not in等）

• 语法：
  select * from t1 where column1 in (select column1 from t2 …);
  

-- 列子查询
-- A.查询"教研部”和"咨询部”的所有员工信息-- a. 查询"教研部”和"咨询部"的部门 ID- tb_deptselect id from tb_dept where name in ('教研部', '咨询部');-- b.再根据部门ID，查询"教研部”和"咨询部"的所有员工信息select * from tb_emp where tb_emp.dept_id in (select id from tb_dept where name in ('教研部', '咨询部'));

（3）行子查询: 子查询返回的结果为一行（行向量用 =，!=，in, not in等）

-- 行子查询
-- A.査询与 "韦一笑”的 入职日期 及 职位都相同的员工信息 ;-- a.查询"韦一笑”的入职日期 及 职位select entrydate, job from tb_emp where name = '韦一笑';-- b.再查询与"韦一笑”的入职日期 及 职位都相同的员工信息-- select * from tb_emp where entrydate = (select entrydatefrom tb_emp where name = '韦一笑') and job = (select job from tb_emp where name = '韦一笑');-- select * from tb_emp where (entrydate, job) = ("2007-01-01", 2);select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = '韦一笑');

（4）表子查询: 子查询返回的结果为多行多列（矩阵其实就是一张表，一般跟在from后当做一张查询表使用）

参考视频
【注】：前面几种查询都是放在where后面，但是这个表子查询子语句返回的是一个表，因此我们通常放在from后面使用

-- 表子查询
-- A.査询入职日期是 "2006-01-01”之后的员工信息，及其部门名称-- a.查询入职日期是 "2006-01-01”之后的员工信息select * from tb_emp where entrydate > '2006-01-01';-- b.再查询入职日期是 "2006-01-01”之后的员工信息，及其部门名称-- select e.*, d.name from (select * from tb_emp where entrydate > '2006-01-01') as e , tb_dept as d where e.dept_id = d.id;select e.*, d.name from (select * from tb_emp where entrydate > '2006-01-01') as e inner join tb_dept as d on e.dept_id = d.id;-- e.*: 表示查询所有员工表的字段

4 案例：（苍穹外卖）多表查询练习（重要重要！！！多练习）

先导入我们需要的四张表：

-- 数据准备 :
-- 分类表
create table category
(id          int unsigned primary key auto_increment comment '主键ID',name        varchar(20)      not null unique comment '分类名称',type        tinyint unsigned not null comment '类型 1 菜品分类 2 套餐分类',sort        tinyint unsigned not null comment '顺序',status      tinyint unsigned not null default 0 comment '状态 0 禁用，1 启用',create_time datetime         not null comment '创建时间',update_time datetime         not null comment '更新时间'
) comment '分类';-- 菜品表
create table dish
(id          int unsigned primary key auto_increment comment '主键ID',name        varchar(20)      not null unique comment '菜品名称',category_id int unsigned     not null comment '菜品分类ID',price       decimal(8, 2)    not null comment '菜品价格',image       varchar(300)     not null comment '菜品图片',description varchar(200) comment '描述信息',status      tinyint unsigned not null default 0 comment '状态, 0 停售 1 起售',create_time datetime         not null comment '创建时间',update_time datetime         not null comment '更新时间'
) comment '菜品';-- 套餐表
create table setmeal
(id          int unsigned primary key auto_increment comment '主键ID',name        varchar(20)      not null unique comment '套餐名称',category_id int unsigned     not null comment '分类id',price       decimal(8, 2)    not null comment '套餐价格',image       varchar(300)     not null comment '图片',description varchar(200) comment '描述信息',status      tinyint unsigned not null default 0 comment '状态 0:停用 1:启用',create_time datetime         not null comment '创建时间',update_time datetime         not null comment '更新时间'
) comment '套餐';-- 套餐菜品关联表
create table setmeal_dish
(id         int unsigned primary key auto_increment comment '主键ID',setmeal_id int unsigned     not null comment '套餐id ',dish_id    int unsigned     not null comment '菜品id',copies     tinyint unsigned not null comment '份数'
) comment '套餐菜品中间表';-- ================================== 导入测试数据 ====================================
-- category
insert into category (id, type, name, sort, status, create_time, update_time)
values (1, 1, '酒水饮料', 10, 1, '2022-08-09 22:09:18', '2022-08-09 22:09:18');
insert into category (id, type, name, sort, status, create_time, update_time)
values (2, 1, '传统主食', 9, 1, '2022-08-09 22:09:32', '2022-08-09 22:18:53');
insert into category (id, type, name, sort, status, create_time, update_time)
values (3, 2, '人气套餐', 12, 1, '2022-08-09 22:11:38', '2022-08-10 11:04:40');
insert into category (id, type, name, sort, status, create_time, update_time)
values (4, 2, '商务套餐', 13, 1, '2022-08-09 22:14:10', '2022-08-10 11:04:48');
insert into category (id, type, name, sort, status, create_time, update_time)
values (5, 1, '经典川菜', 6, 1, '2022-08-09 22:17:42', '2022-08-09 22:17:42');
insert into category (id, type, name, sort, status, create_time, update_time)
values (6, 1, '新鲜时蔬', 7, 1, '2022-08-09 22:18:12', '2022-08-09 22:18:28');
insert into category (id, type, name, sort, status, create_time, update_time)
values (7, 1, '汤类', 11, 1, '2022-08-10 10:51:47', '2022-08-10 10:51:47');-- dish
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (1, '王老吉', 1, 6.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/41bfcacf-7ad4-4927-8b26-df366553a94c.png', '', 1,'2022-06-09 22:40:47', '2022-06-09 22:40:47');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (2, '北冰洋', 1, 4.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4451d4be-89a2-4939-9c69-3a87151cb979.png','还是小时候的味道', 1, '2022-06-10 09:18:49', '2022-06-10 09:18:49');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (3, '雪花啤酒', 1, 4.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/bf8cbfc1-04d2-40e8-9826-061ee41ab87c.png', '', 1,'2022-06-10 09:22:54', '2022-06-10 09:22:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (4, '米饭', 2, 2.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/76752350-2121-44d2-b477-10791c23a8ec.png', '精选五常大米', 1,'2022-06-10 09:30:17', '2022-06-10 09:30:17');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (5, '馒头', 2, 1.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/475cc599-8661-4899-8f9e-121dd8ef7d02.png', '优质面粉', 1,'2022-06-10 09:34:28', '2022-06-10 09:34:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (6, '老坛酸菜鱼', 5, 56.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png','原料：汤，草鱼，酸菜', 1, '2022-06-10 09:40:51', '2022-06-10 09:40:51');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (7, '经典酸菜鮰鱼', 5, 66.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png','原料：酸菜，江团，鮰鱼', 1, '2022-06-10 09:46:02', '2022-06-10 09:46:02');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (8, '蜀味水煮草鱼', 5, 38.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png', '原料：草鱼，汤', 1,'2022-06-10 09:48:37', '2022-06-10 09:48:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (9, '清炒小油菜', 6, 18.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/3613d38e-5614-41c2-90ed-ff175bf50716.png', '原料：小油菜', 1,'2022-06-10 09:51:46', '2022-06-10 09:51:46');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (10, '蒜蓉娃娃菜', 6, 18.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4879ed66-3860-4b28-ba14-306ac025fdec.png', '原料：蒜，娃娃菜',1, '2022-06-10 09:53:37', '2022-06-10 09:53:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (11, '清炒西兰花', 6, 18.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/e9ec4ba4-4b22-4fc8-9be0-4946e6aeb937.png', '原料：西兰花', 1,'2022-06-10 09:55:44', '2022-06-10 09:55:44');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (12, '炝炒圆白菜', 6, 18.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/22f59feb-0d44-430e-a6cd-6a49f27453ca.png', '原料：圆白菜', 1,'2022-06-10 09:58:35', '2022-06-10 09:58:35');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (13, '清蒸鲈鱼', 5, 98.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png', '原料：鲈鱼', 1,'2022-06-10 10:12:28', '2022-06-10 10:12:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (14, '东坡肘子', 5, 138.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a80a4b8c-c93e-4f43-ac8a-856b0d5cc451.png', '原料：猪肘棒', 1,'2022-06-10 10:24:03', '2022-06-10 10:24:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (15, '梅菜扣肉', 5, 58.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/6080b118-e30a-4577-aab4-45042e3f88be.png', '原料：猪肉，梅菜',1, '2022-06-10 10:26:03', '2022-06-10 10:26:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (16, '剁椒鱼头', 5, 66.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png', '原料：鲢鱼，剁椒',1, '2022-06-10 10:28:54', '2022-06-10 10:28:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (17, '馋嘴牛蛙', 5, 98.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/7a55b845-1f2b-41fa-9486-76d187ee9ee1.png','配料：鲜活牛蛙，丝瓜，黄豆芽', 1, '2022-06-10 10:37:52', '2022-06-10 10:37:52');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (18, '鸡蛋汤', 7, 4.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c09a0ee8-9d19-428d-81b9-746221824113.png', '配料：鸡蛋，紫菜',1, '2022-06-10 10:54:25', '2022-06-10 10:54:25');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (19, '平菇豆腐汤', 7, 6.00,'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/16d0a3d6-2253-4cfc-9b49-bf7bd9eb2ad2.png', '配料：豆腐，平菇',1, '2022-06-10 10:55:02', '2022-06-10 10:55:02');-- setmeal
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (1, 4, '商务套餐A', 20.00, 1, '','https://reggie-itcast.oss-cn-beijing.aliyuncs.com/21a5ed3a-97f6-447a-af9d-53deabfb5661.png','2022-06-10 10:58:09', '2022-06-10 10:58:09');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (2, 4, '商务套餐B', 22.00, 1, '','https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8d0075f8-9008-4390-94ca-2ca631440304.png','2022-06-10 11:00:13', '2022-06-10 11:11:37');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (3, 3, '人气套餐A', 49.00, 1, '','https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8979566b-0e17-462b-81d8-8dbace4138f4.png','2022-06-10 11:11:23', '2022-06-10 11:11:23');-- setmeal_dish
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (1, 1, 1, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (2, 1, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (3, 1, 11, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (4, 2, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (5, 2, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (6, 2, 9, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (7, 3, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (8, 3, 6, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (9, 3, 5, 1);

参考视频1
参考视频2

• （1）查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称

-- （1）查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称-- 表： dish category-- 需求中没有特别强调需要完全保留哪一张表的数据，一般直接使用内连接查询-- 子查询select * from dish where price < 10;select a.name,a.price,c.name from (select * from dish where price < 10) as a inner join category as c on a.category_id = c.id;-- 隐式内连接select d.name,d.price,c.name from dish as d,category as c where d.price < 10 and d.category_id = c.id;-- 显式内连接进阶（这个最好用）select d.name,d.price,c.name from dish as d inner join category as c on d.category_id = c.id where d.price < 10;

• （2）查询所有价格在 10元(含)到50元(含)之间 且 状态为"起售"的菜品名称、价格及其分类名称

-- （2）查询所有价格在 10元(含)到50元(含)之间 且 状态为"起售"的菜品名称、价格及其分类名称(即使菜品没有分类也要查询出来)-- 表 dish category-- 很明显，需求中要求查询所有的菜品，所以使用左连接查询select d.name,d.price,c.name from dish as d left outer join category as c on d.category_id = c.id where d.price >= 10 and d.price <= 50 and d.status = 1;

• （3）查询每个分类下最贵的菜品,展示出分类的名称、最贵的菜品的价格

-- （3）查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格-- 表 dish categoryselect c.name, max(d.price) from dish as d inner join category as c on d.category_id = c.id group by c.name;

• （4）查询各个分类下 菜品状态为"起售",并且 该分类下菜品总数量大于等于3 的 分类名称

-- （4）查询各个分类下菜品状态为"起售",并且 该分类下菜品总数量大于等于 3 的分类名称-- 表 dish categoryselect c.name,count(*) from dish d inner join category c on d.category_id = c.id where d.status = 1 group by c.name having count(*) >= 3;

• （5）查询出"商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格,包含的菜品名称、价格、份数
  这是一个多对多有中间表的三张表内连接查询案例

-- （5）查询出"商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格,包含的菜品名称、价格、份数)-- 表 setmeal dish setmeal_dish   多对多三张表连接，中间表放在中间select s.name,s.price,d.name,d.price,sd.copies from setmeal s inner join setmeal_dish sd on s.id = sd.setmeal_id inner join dish d on sd.dish_id = d.id where s.name = '商务套餐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）查询出低于菜品平均价格的菜品信息(展示出菜品名称、菜品价格)

-- （6）查询出低于菜品平均价格的菜品信息(展示出菜品名称、菜品价格)-- 表 dishselect d.name,d.price from dish d where price < (select avg(price) from dish);

三、事务

• 事务的应用场景：
  
  【注】：很显然这是使用逻辑外键的一个缺陷，但是可以使用事务来解决这个缺陷。

1 事务的概念

• 事务是一组操作的集合，它是一个不可分割的工作单位。
  事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作 要么同时成功功，要么同时失败。
  通俗理解：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

• e.g1. 以下面的SQL语句为例：
  在SQL执行中，默认上面两行SQL语句是两个事务，每一行都是一个事务。所以有可能会出现第一行成功执行，但是第二行失败的特殊情况。
  即：默认MySOL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

我们要做的就是将这两行SQL语句封装成一个事务（而不是自动的看做两个事务提交），要么都成功，要么有一个失败大家都一起失败，这样就能保证完整性。

• e.g2：张三给李四转账的例子
  
  很显然，这个转账的流程（分三步），如果不使用一个事务管理，就有可能出现在第三步出现异常导致：张三账户 -500但是李四账户没有+500，这会造成很严重的后果。

2 事务的操作语法

参考视频

• 语法：

  • 开启事务: start transaction; / begin;
  • 提交事务: commit;
    当我们这组操作中所有操作都成功，我们就可以执行提交事务。
    如果开启了事务，如果没有提交事务只是执行了SQL语句，那么数据库中的数据只是临时性的被修改。
    什么意思？意思是如果你关闭窗口，重新打开，数据会自动回滚。也就是说数据恢复原样
  • 回滚事务: rollback;
    当我们这组操作中只要有一个操作失败，就要将数据库中的数据回滚（恢复）到操作前的样子，此时就必须使用回滚事务。不能使用提交事务

• 变成if…else…理解
  if step1,…stepn 全部执行成功，执行commit; else 执行rollback

还是以上面那个解散部门的SQL为案例进行演示：

• 有一条SQL执行失败后我们回滚事务

start transaction;-- step2：执行删除操作  直接执行下面两个SQL语句
-- 删除部门
delete from tb_dept where id = 2;
-- 删除部门下的员工
delete from tb_emp where dept_id == 2;   -- 很显然，这行有语法错误，必定执行失败，我们可以通过回滚事务来撤销这次操作-- 需要注意的是这里的两行SQL执行后，数据库中的数据已经发生了变化，可以使用select * from tb_dept;来查看，但是在gui中是看不到的，因为这是一个事务，只有commit之后才会真正的提交到数据库中再显示在gui上-- step3：step2中有SQL语句执行失败，我们需要回滚事务来撤销这次操作保证数据的完整性
-- 回滚事务
rollback;-- 回滚后再次执行select * from tb_dept;就会发现数据恢复回来了在这里插入代码片

• 所以SQL语句均能正常执行

-- step1：开启事务 先执行这一行
start transaction;-- step2：执行删除操作  直接执行下面两个SQL语句
-- 删除部门
delete from tb_dept where id = 2;
-- 删除部门下的员工
delete from tb_emp where dept_id = 2;-- 需要注意的是这里的两行SQL执行后，数据库中的数据已经发生了变化，可以使用select * from tb_dept;来查看，但是在gui中是看不到的，因为这是一个事务，只有commit之后才会真正的提交到数据库中再显示在gui上-- step3：提交事务 step2中的SQL语句都能正常执行，因此我们需要提交事务
commit;-- 这时候在gui中就能看到数据发生了变化，部门2和部门2下的员工都被删除了

• 问题：我们怎么知道所有的SQL是不是都能正常执行？无法确定最后要使用的是提交事务还是回滚事务啊！
  • 答：业务层可以捕获异常就可以知道了

3 事务的自动提交与手动提交两种方式

参考视频

• MySQL数据库中事务默认自动提交

  • 一条DML(增删改)语句会自动提交一次事务。
  • 知道了MySQL的这个特性，提交事务是怎么回事就可以完全理解了。
  • 事务提交的两种方式:
    • 自动提交:
      mysql就是自动提交的,一条DML(增删改)语句会自动提交一次事务。
      MySQL默认是自动提交
    • 手动提交:
      需要先开启事务，再提交
      oracle默认是手动提交

• 查看事务的默认提交方式: SELECT @@autocommit; --1 代表自动提交 0 代表手动提交

• 修改默认提交方式:set @@autocommit =0; 手动提交 （另外这个修改默认事务提交方式MySQL如果关闭界面重新打开就又会重新设置成自动提交了）
  如果我们修改成手动提交后再执行任意DML(增删改)语句就只会临时修改数据了，如果关闭界面重新打开，数据就会恢复回来

下面演示一下这个修改事务的默认提交方式就能对事务有一个更加充分的认识了：

• step1：查询mysql默认的事务提交方式

-- 查询默认的事务提交方式
select @@autocommit;  -- 1 代表自动提交 0 代表手动提交

• step2：修改成默认事务手动提交并且将 修改员工表中的name全部为 “张三”
  部手动执行commit;提交事务会发现员工表中数据只会临时被修改，如果关闭界面重新打开，员工表中的数据会恢复到修改前

-- 设置事务提交方式为手动提交
set autocommit = 0;-- 修改员工表中的name全部为 "张三"
update tb_emp set name = '王五';

4 事务的四大特性（ACID）

参考视频

• 原子性: 事务是不可分割的最小单元，要么全部成功，要么全部失败
• 持久性: 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
• 隔离性: 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  但真实情况是事务见是有相互影响的，所以我们下面需要学习事务的隔离级别
• 一致性 : 事务完成时，必须使所有的数据都保持一致状态

5 事务的隔离级别（了解即可，通常也不需要我们去设置事务的隔离级别）

参考视频1
参考视频2
参考视频3

• 概念: 多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

• 存在问题 :

  • 1. 脏读: 一个事务，读取到另一个事务中没有提交的数据。
  • 2. 不可重复读(虚读):在同一个事务中，两次读取到的数据不一样
  • 3. 幻读: 一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

• 隔离级别 :

  • 1.read uncommitted:读未提交
    产生的问题:脏读、不可重复读、幻读
  • 2.read committed:读已提交 (Oracle)
    产生的问题: 不可重复读、幻读
  • 3.repeatable read:可重复读 (MySQL默认)
    产生的问题:幻读
  • 4.serializable:串行化
    可以解决所有的问题
    其实这个原理可以类比多线程的锁，这里一个事务在操作共享的那批数据，另一个事务要进行操作只能在外面等着，等上一个事务提交或者回滚后才能进行

• 注意: 隔离级别从小到大安全性越来越高，但是效率越来越低

• 数据库查询隔离级别:
  select @@tx_isolation;

• 数据库设置隔离级别:
  set global transaction isolation level 级别字符串;

【注】：很显然数据库自己有默认隔离级别，我们没必要自己改，人家这么选择有人家的原因。这里之所以还点出这个知识点是为了防止有面试会问
【注】：其实上面这些都是并发产生的一系列问题而已

具体演示可以参考上面视频，由于并不常用，本笔记这里不讲解。

四、索引（数据库的优化，索引部分）

1 索引的介绍

参考视频

• 概念：索引(index)是帮助数据库 高效获取数据 的 数据结构
  简单理解就是，一旦给表中的某些字段设置了索引，那么查询这些字段的查询速度会提高指数倍，当然牺牲的是空间换时间，并且进行insert、update、delete的效率会降低，因为需要维护更加复杂的数据结构。不过现在空间不值钱，在大数据量的情况下，查询效率才是最重要的。
  
  无索引就是全表扫描，一行一行遍历查找；索引优化就是将这种数组或者链表的遍历数据结构变成二叉搜索树这些更加高效的数据结构进行处理。但注意这里使用二叉搜索树只是一个演示，MySQL里面的索引优化不一定是二叉搜索树。

• 索引的优缺点：

  • 优点
    • 提高数据查询的效率，降低数据库的IO成本
    • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。
  • 缺点
    • 索引会占用存储空间。
    • 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

2 MySQL中索引采用的结构

参考视频

• MySQL数据库支持的索引结构有很多，如: Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。
  
• B+Tree(多路平衡搜索树)
  
  【注】：这里要详细理解就要去看数据结构了

3 索引的语法

• 语法：
  • 创建索引： create [unique] index 索引名 on 表名(字段名, …);
    • [unique] 可选，是否要创建唯一索引
  • 查看索引： show index from 表名;
  • 删除索引： drop index 索引名 on 表名；

-- 创建 :为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);-- 査询 :査询 tb_emp 表的索引信息
show index from tb_emp;

可以看到我们查询出来了三条索引，其中idx_emp_name索引是我们为name这个字段创建的，但是另外两个索引怎么回事呢？

• 注意：
  • 主键约束的字段会自动创建一个索引，上面的id这个主键就自动创建了PRIMARY这个索引
    主键字段，在建表时，会自动创建主键索引。
  • 唯一约束的字段会自动创建一个唯一索引，上面的username就是一个唯一约束的字段，所以自动给我们创建了一个username的索引
    添加唯一约束时，数据库实际上会添加唯一索引。

-- 删除:删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;-- 査询 :査询 tb_emp 表的索引信息
show index from tb_emp;

五、多学两招

1 UNION ALL语法和UNION语法（两表纵向合并在一起）

UNION ALL 是 SQL 中用于合并多个 SELECT 查询结果集的操作符。它将两个或多个查询的结果集简单拼接在一起，并返回一个包含所有行的结果集。

• 基本语法：

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

示例
假设有两个表：employees 和 contractors，你想将这两个表中的所有人员信息合并到一个结果集中：

SELECT employee_id AS id, first_name, last_name, 'Employee' AS type
FROM employees
UNION ALL
SELECT contractor_id AS id, first_name, last_name, 'Contractor' AS type
FROM contractors;

• 注意事项：
  • 不删除重复行：与 UNION 不同，UNION ALL 不会去重，它会保留所有行，包括重复的行。
  • 性能更高：因为不需要去重，UNION ALL 通常比 UNION 性能更好。
  • 结果集顺序：UNION ALL 的结果集顺序是不确定的，除非显式使用 ORDER BY。
• 与 UNION 的区别
  • UNION 会去重，即删除结果集中的重复行。
  • UNION ALL 不会去重，保留所有行。
• 特别注意：由于是纵向拼接，所以
  • 所有 SELECT 语句的列数和数据类型必须一致。

2 SQL中的with查询语法（MySQL8.0版本后才支持）= 极大的简化多层子查询的逻辑

WITH 语法用于定义 公用表表达式（CTE, Common Table Expression），它是一种在查询中定义临时结果集的方式。
CTE 可以提高查询的可读性和重用性，并且可以在同一个查询中多次引用。
WITH语法允许你定义临时结果集，这些结果集可以在后续的查询中被引用。
它通常用于简化复杂查询，尤其是涉及多层嵌套子查询的情况。

• 基本语法：简单来说就是将一张查出来的临时取个名字（临时变量）存起来，后面的查询就可以基于这个临时表进一步操作
  • cte_name 是定义的公用表表达式的名字，通常是一个临时表或视图。
  • CTE 查询部分就是你定义的查询语句，它可以像一个普通的表一样被引用。

WITH cte_name AS (-- CTE 查询SELECT ...
)
SELECT ...
FROM cte_name;

• 特点
  • 可读性：WITH语法可以将复杂的查询拆分为多个逻辑部分，提高代码的可读性。
  • 可重用性：在同一个查询中，可以多次引用同一个CTE。
  • 递归查询：WITH语法还支持递归CTE，用于处理层次结构数据（如树形结构）。

基本的 CTE 使用
假设有一个表employees，你想查询每个部门的平均工资，然后再筛选出高于平均工资的员工：

WITH DeptAvgSalary AS (SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id
)
SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
JOIN DeptAvgSalary d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

递归CTE示例
查询一个组织结构中的所有下属：

WITH RECURSIVE Subordinates AS (-- 非递归部分，选择顶层员工（例如，CEO）SELECT employee_id, first_name, last_name, manager_idFROM employeesWHERE employee_id = 1 -- 假设1是顶级经理UNION ALL-- 递归部分，选择下属SELECT e.employee_id, e.first_name, e.last_name, e.manager_idFROM employees eINNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM Subordinates;

非递归部分：我们选择一个顶级经理的员工，作为递归的起点。
递归部分：通过 JOIN 操作，将上级的 id 和下属的 manager_id 进行匹配，不断递归查询直到找到所有下属。

• 注意事项
  • WITH语法仅在MySQL 8.0及以上版本中可用。
  • CTE的作用范围仅限于当前查询，查询结束后CTE会自动销毁。
    如果你使用的是MySQL 8.0或更高版本，可以充分利用WITH语法来优化和简化查询！