1 数据库（下）：多表设计 、多表查询 + SQL中的with查询语法（MySQL8.0以后版本才支持这种新语法）+ 事务 + 数据库优化（索引优化）

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

前言

一、多表设计

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.jpg
     job 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.jpg
     job 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(