DBA MySQL多表查询

前戏工作

数据准备

​ 多表查询相关数据：

# 部门表
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    dep_id INT UNSIGNED NOT NULL COMMENT "部门编号",
    name CHAR(12) NOT NULL UNIQUE COMMENT "部门名称(唯一)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 教师表:未做物理层面上的外键关系限制，但是是有逻辑层面的关系存在的
# 这意味着插入数据时必须小心，因为可能会产生非法数据
CREATE TABLE teacher(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    name CHAR(12) NOT NULL COMMENT "教师名称",
    age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "教师年龄",
    gender ENUM("MALE", "FEMALE", "UNKNOW") NOT NULL DEFAULT "MALE" COMMENT "教师性别",
    coaching_age TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT "执教年龄",
    salary INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "教师薪资(月)",
    dep_fk_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "所属部门"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 插入数据
INSERT INTO
    department(dep_id, name)
VALUES
    (100, "管理部"),
    (200, "教学部"),
    (300, "财务部"),
    (500, "教务部");

INSERT INTO
    teacher(name, gender, age, coaching_age, salary, dep_fk_id)
VALUES
    ("TeacherZhang", "male", 32, 8, 9000, 200),
    ("TeacherLi", "male", 34, 10, 12000, 200),
    ("TeacherYun", "male", 26, 4, 21000, 100),
    ("TeacherZhou", "female", 24, 2, 4000, 300),
    ("TeacherZhao", "female", 32, 12, 23000, 100),
    ("TeacherYang", "male", 28, 6, 3000, 300),
    ("TeacherWang", "female", 22, 1, 3200, 400);
    
# 数据说明：
# 老师表中有个部门编号为400的TeacherWang老师，没有对应的部门。
# 部门表中有个编号为500的教务部，其中没有包含老师。

查询语法

​ 查询语法如下：

SELECT
    DISTINCT(字段名1, 字段名2...)
FROM
    左表名
连接类型 JOIN
    右表名
ON 连表条件
WHERE
    过滤条件
GROUP BY
    分组字段名
HAVING
    筛选条件
ORDER BY
    排序字段名 ASC / DESC
LIMIT
    限制条数;

执行顺序

​ 在单表查询的基础上，多表查询多了一些查询的步骤，因此执行顺序也与单表查询有所不同。

1. 通过FROM找到将要查询的多张表，生成虚拟的笛卡尔积表
2. 通过ON来过滤出笛卡尔积虚拟表中需要保留的字段
3. 根据不同连接类型的JOIN来对虚拟表的记录进行外部行的添加
4. WHERE规定查询条件，在虚拟表记录中逐行进行查询并过滤出符合规则的记录
5. 将过滤到的记录信息按照字段进行GROUP BY分组，如果没有进行分组，则默认按照主键分为1组
6. 将分组得到的结果进行HAVING筛选，此时可使用聚合函数。WHERE时不可使用聚合函数
7. 执行SELECT准备打印
8. 执行DISTINCT()函数对打印结果进行去重
9. 执行ORDERY BY对结果进行排序，升序ASC，降序DESC
10. 执行LIMIT对打印结果进行条数限制

笛卡尔积表

​ 将两张表同时进行查询时，会产生一张笛卡尔积表，该表存储于内存中。

​ 笛卡尔积表是连表查询的基础，但是有很多无用的数据。

左表的每一行记录都会与右表中的每一行记录做一次连接

如下左表teacher有7条记录，右表department有4条记录

那么总共就有4*7条记录

​ 示例如下：

M > SELECT * FROM teacher, department;
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+
| id | name         | age | gender | coaching_age | salary | dep_fk_id | id | dep_id | name      |
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |  1 |    100 | 管理部    |
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |  2 |    200 | 教学部    |
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |  3 |    300 | 财务部    |
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |  4 |    500 | 教务部    |
|  2 | TeacherLi    |  34 | MALE   |           10 |  12000 |       200 |  1 |    100 | 管理部    |
|  2 | TeacherLi    |  34 | MALE   |           10 |  12000 |       200 |  2 |    200 | 教学部    |
|  2 | TeacherLi    |  34 | MALE   |           10 |  12000 |       200 |  3 |    300 | 财务部    |
|  2 | TeacherLi    |  34 | MALE   |           10 |  12000 |       200 |  4 |    500 | 教务部    |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |  1 |    100 | 管理部    |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |  2 |    200 | 教学部    |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |  3 |    300 | 财务部    |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |  4 |    500 | 教务部    |
|  4 | TeacherZhou  |  24 | FEMALE |            2 |   4000 |       300 |  1 |    100 | 管理部    |
|  4 | TeacherZhou  |  24 | FEMALE |            2 |   4000 |       300 |  2 |    200 | 教学部    |
|  4 | TeacherZhou  |  24 | FEMALE |            2 |   4000 |       300 |  3 |    300 | 财务部    |
|  4 | TeacherZhou  |  24 | FEMALE |            2 |   4000 |       300 |  4 |    500 | 教务部    |
|  5 | TeacherZhao  |  32 | FEMALE |           12 |  23000 |       100 |  1 |    100 | 管理部    |
|  5 | TeacherZhao  |  32 | FEMALE |           12 |  23000 |       100 |  2 |    200 | 教学部    |
|  5 | TeacherZhao  |  32 | FEMALE |           12 |  23000 |       100 |  3 |    300 | 财务部    |
|  5 | TeacherZhao  |  32 | FEMALE |           12 |  23000 |       100 |  4 |    500 | 教务部    |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |  1 |    100 | 管理部    |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |  2 |    200 | 教学部    |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |  3 |    300 | 财务部    |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |  4 |    500 | 教务部    |
|  7 | TeacherWang  |  22 | FEMALE |            1 |   3200 |       400 |  1 |    100 | 管理部    |
|  7 | TeacherWang  |  22 | FEMALE |            1 |   3200 |       400 |  2 |    200 | 教学部    |
|  7 | TeacherWang  |  22 | FEMALE |            1 |   3200 |       400 |  3 |    300 | 财务部    |
|  7 | TeacherWang  |  22 | FEMALE |            1 |   3200 |       400 |  4 |    500 | 教务部    |
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+

WHERE连表

​ 笛卡尔积表的数据非常全面，我们可以针对笛卡尔积表做一些条件限制使其能够拿到我们想要的数据。

​ 如下所示，经过WHERE条件过滤后，拿到了很精确的一张表：

# AS在多表连接查询中，可以给表也取一个别名

SELECT
    *
FROM
    teacher AS lt,
    department AS rt
WHERE
    lt.dep_fk_id = rt.dep_id;
    
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+
| id | name         | age | gender | coaching_age | salary | dep_fk_id | id | dep_id | name      |
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |  2 |    200 | 教学部    |
|  2 | TeacherLi    |  34 | MALE   |           10 |  12000 |       200 |  2 |    200 | 教学部    |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |  1 |    100 | 管理部    |
|  4 | TeacherZhou  |  24 | FEMALE |            2 |   4000 |       300 |  3 |    300 | 财务部    |
|  5 | TeacherZhao  |  32 | FEMALE |           12 |  23000 |       100 |  1 |    100 | 管理部    |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |  3 |    300 | 财务部    |
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+

​ 使用WHERE的确可以做到连表查询并且能够剔除笛卡尔积表中无用数据，但此种方式并不建议。

​ MySQL提供了为连表操作做过滤条件的语句ON，所以我们不应该使用WHERE过滤来做连表的过滤条件，而是使用WHERE做一些额外的过滤条件。

​ 上述WHERE连表中还存在两个问题，如下所示：

左表teacher中有一个名为TeacherWang的教师拿不出来，这是因为TeacherWang的部门编号400不在右表中

右表department中有一个部门编号为500的部门拿不出来，这是因为该部门下没有任何老师

​ 所以，忘记WHERE连表查询吧。

连接查询

基本概述

​ 连接查询是MySQL中提供的连表操作语法。

​ 在连接查询中，对笛卡尔积表的多余记录剔除应该使用ON来指定条件，而不应该使用WHERE。

​ WHERE应该是对无重复数据记录的笛卡尔积表再次进行二次过滤所使用的。

​ 使用连表查询时，一定要记住一个规则，用记录较少的表来连接记录较多的表。

INNER JOIN

​ INNER JOIN为内连接的查询连接类型，可简写为JOIN。

​ 内连接查询的特点是拿到左表和右表中共有的部分，这与上面的WHERE连表查询拿到的数据是相同的。

# 注意：INNER JOIN可简写为JOIN

SELECT
    *
FROM
    teacher AS ft
    JOIN department AS rt ON (ft.dep_fk_id = rt.dep_id);
    
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+
| id | name         | age | gender | coaching_age | salary | dep_fk_id | id | dep_id | name      |
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |  2 |    200 | 教学部    |
|  2 | TeacherLi    |  34 | MALE   |           10 |  12000 |       200 |  2 |    200 | 教学部    |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |  1 |    100 | 管理部    |
|  4 | TeacherZhou  |  24 | FEMALE |            2 |   4000 |       300 |  3 |    300 | 财务部    |
|  5 | TeacherZhao  |  32 | FEMALE |           12 |  23000 |       100 |  1 |    100 | 管理部    |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |  3 |    300 | 财务部    |
+----+--------------+-----+--------+--------------+--------+-----------+----+--------+-----------+

LEFT JOIN

​ LEFT JOIN为左连接的查询连接类型。

​ 左连接查询的特点是除了可以拿到左表和右表共有的部分之外还可以拿到左表独有的部分。

​ 如下所示，通过左连接查询，可以拿出左表teacher表中的TeacherWang相关数据：

SELECT
    *
FROM
    teacher AS ft
    LEFT JOIN department AS rt ON (ft.dep_fk_id = rt.dep_id);
    
+----+--------------+-----+--------+--------------+--------+-----------+------+--------+-----------+
| id | name         | age | gender | coaching_age | salary | dep_fk_id | id   | dep_id | name      |
+----+--------------+-----+--------+--------------+--------+-----------+------+--------+-----------+
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |    1 |    100 | 管理部    |
|  5 | TeacherZhao  |  32 | FEMALE |           12 |  23000 |       100 |    1 |    100 | 管理部    |
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |    2 |    200 | 教学部    |
|  2 | TeacherLi    |  34 | MALE   |           10 |  12000 |       200 |    2 |    200 | 教学部    |
|  4 | TeacherZhou  |  24 | FEMALE |            2 |   4000 |       300 |    3 |    300 | 财务部    |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |    3 |    300 | 财务部    |
|  7 | TeacherWang  |  22 | FEMALE |            1 |   3200 |       400 | NULL |   NULL | NULL      |
+----+--------------+-----+--------+--------------+--------+-----------+------+--------+-----------+

RIGHT JOIN

​ RIGHT JOIN为右连接的查询连接类型。

​ 右连接查询的特点是除了可以拿到左表和右表共有的部分之外还可以拿到右表独有的部分。

​ 如下所示，通过右连接查询，可以拿出右表department表中的编号为500的教务部相关数据：

SELECT
    *
FROM
    teacher AS ft
    RIGHT JOIN department AS rt ON (ft.dep_fk_id = rt.dep_id);

+------+--------------+------+--------+--------------+--------+-----------+----+--------+-----------+
| id   | name         | age  | gender | coaching_age | salary | dep_fk_id | id | dep_id | name      |
+------+--------------+------+--------+--------------+--------+-----------+----+--------+-----------+
|    1 | TeacherZhang |   32 | MALE   |            8 |   9000 |       200 |  2 |    200 | 教学部    |
|    2 | TeacherLi    |   34 | MALE   |           10 |  12000 |       200 |  2 |    200 | 教学部    |
|    3 | TeacherYun   |   26 | MALE   |            4 |  21000 |       100 |  1 |    100 | 管理部    |
|    4 | TeacherZhou  |   24 | FEMALE |            2 |   4000 |       300 |  3 |    300 | 财务部    |
|    5 | TeacherZhao  |   32 | FEMALE |           12 |  23000 |       100 |  1 |    100 | 管理部    |
|    6 | TeacherYang  |   28 | MALE   |            6 |   3000 |       300 |  3 |    300 | 财务部    |
| NULL | NULL         | NULL | NULL   |         NULL |   NULL |      NULL |  4 |    500 | 教务部    |
+------+--------------+------+--------+--------------+--------+-----------+----+--------+-----------+

FULL OUTER JOIN

​ FULL OUTER JOIN是全外连接的查询连接类型。

​ 全外连接查询的特点是除了可以拿到左表和右表共有的部分之外还可以拿到左表与右表独有的部分。

​ 通过全外连接查询，可以拿出左表teacher表中的TeacherWang相关数据，并且也可以拿出右表department表中的编号为500的教务部相关数据。

​ 遗憾的是MySQL中并不支持这种用法：

M > SELECT
           *
       FROM
           teacher AS ft
           FULL OUTER JOIN department AS rt ON (ft.dep_fk_id = rt.dep_id);
    
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN department AS rt ON (ft.dep_fk_id = rt.dep_id)' at line 5

链接配图

​ 如果你还是搞不懂内连接，左连接，外连接的区别，那么推荐你可以看一下runoob.com提供的这张图。

​ 其中非常详细的举例了各种连接的差别：

​ 点我跳转

拼接查询

UNION&UNION ALL

​ UNION与UNION ALL都是对查询出的两张表结果做拼接使用的。

​ 使用这种拼接查询，一定要确保每个查询返回的列的数量与顺序要一样。

​ 使用语法如下：

# 将两张查询出的位于内存中的表拼起来

SELECT * FROM tb1
UNION
SELECT * FROM tb2
LIMIT 5;

​ 他们的区别如下：

UNION会过滤重复的结果

UNION ALL不会过滤重复的结果

​ 尽管MySQL中不支持全外链接查询，但是我们可以使用LEFT JOIN与RIGHT JOIN配合拼接查询UNION来做出 FULL OUTER JOIN的效果，如下所示：

SELECT * FROM teacher AS ft LEFT JOIN department AS rt ON (ft.dep_fk_id = rt.dep_id)
UNION
SELECT * FROM teacher AS ft RIGHT JOIN department AS rt ON (ft.dep_fk_id = rt.dep_id);

+------+--------------+------+--------+--------------+--------+-----------+------+--------+-----------+
| id   | name         | age  | gender | coaching_age | salary | dep_fk_id | id   | dep_id | name      |
+------+--------------+------+--------+--------------+--------+-----------+------+--------+-----------+
|    3 | TeacherYun   |   26 | MALE   |            4 |  21000 |       100 |    1 |    100 | 管理部    |
|    5 | TeacherZhao  |   32 | FEMALE |           12 |  23000 |       100 |    1 |    100 | 管理部    |
|    1 | TeacherZhang |   32 | MALE   |            8 |   9000 |       200 |    2 |    200 | 教学部    |
|    2 | TeacherLi    |   34 | MALE   |           10 |  12000 |       200 |    2 |    200 | 教学部    |
|    4 | TeacherZhou  |   24 | FEMALE |            2 |   4000 |       300 |    3 |    300 | 财务部    |
|    6 | TeacherYang  |   28 | MALE   |            6 |   3000 |       300 |    3 |    300 | 财务部    |
|    7 | TeacherWang  |   22 | FEMALE |            1 |   3200 |       400 | NULL |   NULL | NULL      |
| NULL | NULL         | NULL | NULL   |         NULL |   NULL |      NULL |    4 |    500 | 教务部    |
+------+--------------+------+--------+--------------+--------+-----------+------+--------+-----------+

子查询

基本概述

​ 子查询是将一个查询语句嵌套在另一个查询语句中。

​ 因为每一次的查询结果都可以当作一个在内存中的临时表来进行看待，所以我们可以在这张临时表的基础上再次进行查询。

​ 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS和 NOT EXISTS等关键字，还可以包含比较运算符：= 、!=、> 、<等。

​ 新手如何正确的使用子查询：

使用子查询先写子查询的内容

简单使用

​ 子查询示例，查询管理部门老师的信息：

SELECT * FROM teacher
    WHERE dep_fk_id IN
    (SELECT dep_id FROM department WHERE name = "管理部");

+----+-------------+-----+--------+--------------+--------+-----------+
| id | name        | age | gender | coaching_age | salary | dep_fk_id |
+----+-------------+-----+--------+--------------+--------+-----------+
|  3 | TeacherYun  |  26 | MALE   |            4 |  21000 |       100 |
|  5 | TeacherZhao |  32 | FEMALE |           12 |  23000 |       100 |
+----+-------------+-----+--------+--------------+--------+-----------+

​ 子查询示例，查询薪资最高的部门：

SELECT * FROM department WHERE dep_id =
    (SELECT dep_fk_id FROM teacher GROUP BY dep_fk_id HAVING MAX(salary) LIMIT 1);

+----+--------+-----------+
| id | dep_id | name      |
+----+--------+-----------+
|  1 |    100 | 管理部    |
+----+--------+-----------+

​ 子查询示例，查询没人的部门：

SELECT * FROM department

WHERE dep_id NOT IN
    (SELECT dep_fk_id FROM teacher);
    
+----+--------+-----------+
| id | dep_id | name      |
+----+--------+-----------+
|  4 |    500 | 教务部    |
+----+--------+-----------+

​ 子查询示例，查询部门被撤销的老师，即部门表中没有该老师所在的部门：

SELECT * FROM teacher
    WHERE dep_fk_id NOT IN
    (SELECT dep_id FROM department);
    
+----+-------------+-----+--------+--------------+--------+-----------+
| id | name        | age | gender | coaching_age | salary | dep_fk_id |
+----+-------------+-----+--------+--------------+--------+-----------+
|  7 | TeacherWang |  22 | FEMALE |            1 |   3200 |       400 |
+----+-------------+-----+--------+--------------+--------+-----------+

​ 写子查询的正确打开方式，如下图所示，先写下面再写上面:

EXISTS

​ EXISTS在子查询中都是跟在外部查询的WHERE之后，用于关注此次子查询结果是否为真。

​ 如果此次子查询结果为真，则执行外部查询，否则将不会执行。

​ 以下有一个例子非常不错，领导视察工作，如果教师平均薪资大于一万，则看一眼工资大于一万教师的教师都有谁，如果不大于一万就不看。

SELECT AVG(salary) FROM teacher;

+-------------+
| AVG(salary) |
+-------------+
|  10742.8571 |
+-------------+

SELECT * FROM teacher
    WHERE EXISTS
    (SELECT GROUP_CONCAT(NAME) FROM teacher HAVING AVG(salary) > 10000)
    AND
    salary > 10000;

+----+-------------+-----+--------+--------------+--------+-----------+
| id | name        | age | gender | coaching_age | salary | dep_fk_id |
+----+-------------+-----+--------+--------------+--------+-----------+
|  2 | TeacherLi   |  34 | MALE   |           10 |  12000 |       200 |
|  3 | TeacherYun  |  26 | MALE   |            4 |  21000 |       100 |
|  5 | TeacherZhao |  32 | FEMALE |           12 |  23000 |       100 |
+----+-------------+-----+--------+--------------+--------+-----------+

自连接

示例展示

​ 自连接SELF JOIN是建立在子查询以及连接查询基础之上，即在上一次查询自己的记录中再连接并查询一次自己。

​ 因为每次的查询都会建立一张虚拟表，所以我们可以用AS为这张虚拟表取一个别名。

​ 如下示例将展示查询每个部门中工资最少的教师信息。

SELECT * FROM teacher AS t1
    JOIN
    (SELECT MIN(salary) AS min_salary FROM teacher GROUP BY dep_fk_id) AS t2
    ON t1.salary = t2.min_salary;

+----+--------------+-----+--------+--------------+--------+-----------+------------+
| id | name         | age | gender | coaching_age | salary | dep_fk_id | min_salary |
+----+--------------+-----+--------+--------------+--------+-----------+------------+
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |       9000 |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |      21000 |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |       3000 |
|  7 | TeacherWang  |  22 | FEMALE |            1 |   3200 |       400 |       3200 |
+----+--------------+-----+--------+--------------+--------+-----------+------------+

流程分析

​ 第一步：写子查询，拿到每组中最少的薪资，并且为这张虚拟表取名为t2：

(SELECT MIN(salary) AS min_salary FROM teacher GROUP BY dep_fk_id);

+------------+
| min_salary |
+------------+
|      21000 |
|       9000 |
|       3000 |
|       3200 |
+------------+

​ 第二步：使用INNER JOIN进行连接查询，将表t1与表t2相连，拿到共有的部分，通过薪资来找到教师：

SELECT * FROM teacher AS t1
    JOIN
    (SELECT MIN(salary) AS min_salary FROM teacher GROUP BY dep_fk_id) AS t2
    ON t1.salary = t2.min_salary;

+----+--------------+-----+--------+--------------+--------+-----------+------------+
| id | name         | age | gender | coaching_age | salary | dep_fk_id | min_salary |
+----+--------------+-----+--------+--------------+--------+-----------+------------+
|  1 | TeacherZhang |  32 | MALE   |            8 |   9000 |       200 |       9000 |
|  3 | TeacherYun   |  26 | MALE   |            4 |  21000 |       100 |      21000 |
|  6 | TeacherYang  |  28 | MALE   |            6 |   3000 |       300 |       3000 |
|  7 | TeacherWang  |  22 | FEMALE |            1 |   3200 |       400 |       3200 |
+----+--------------+-----+--------+--------------+--------+-----------+------------+

三表联查

数据准备

​ 总有一些行业精英可以同时隶属于多个部门，而多个部门下也可能有多个人。

​ 在此基础上建立多对多关系表格。

​ PS：依旧没有做物理外键约束表关系，而是采用逻辑表关系。

# 员工表
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    name CHAR(12) NOT NULL COMMENT "员工名称",
    age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "员工年龄",
    gender ENUM("MALE", "FEMALE", "UNKNOW") NOT NULL DEFAULT "MALE" COMMENT "教师性别",
    salary INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "员工工资(月)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 部门表
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    dep_id INT UNSIGNED NOT NULL COMMENT "部门编号",
    name CHAR(12) NOT NULL UNIQUE COMMENT "部门名称(唯一)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 关系表
CREATE TABLE emp_dep(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    emp_fk_id INT UNSIGNED NOT NULL,
    dep_fk_id INT UNSIGNED NOT NULL,
    UNIQUE KEY emp_dep_unique(emp_fk_id, dep_fk_id) COMMENT "联合唯一(emp_fk_id,dep_fk_id)"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 插入数据
INSERT INTO
	employee(name,gender,age,salary) 
VALUES
    ("Yunya","male",22,16000),
    ("Jack","male",25,18000),
    ("Bella","female",24,12000),
    ("Maria","female",22,8000),
    ("Tom","male",23,6000),
    ("Jason","male",28,32000),
    ("James","male",31,35000),
    ("Lisa","female",36,28000);

INSERT INTO
	department(dep_id,name) 
VALUES
    (1001,"研发部"),
    (1002,"开发部"),
    (1003,"财务部"),
    (1004,"人事部");

INSERT INTO
	emp_dep(emp_fk_id,dep_fk_id) 
VALUES
    (1,1002),
    (2,1002),
    (3,1003),
    (4,1004),
    (5,1004),
    (6,1001),
    (6,1002),
    (7,1002),
    (7,1001),
    (7,1003),
    (8,1003),
    (8,1004);

思路解析

​ 三表查询的思路很简单，先用左表与中间表进行查找，这时候就会得到一张虚拟的表。

SELECT
    *
FROM
    employee
    JOIN emp_dep ON employee.id = emp_dep.emp_fk_id;

+----+-------+-----+--------+--------+----+-----------+-----------+
| id | name  | age | gender | salary | id | emp_fk_id | dep_fk_id |
+----+-------+-----+--------+--------+----+-----------+-----------+
|  1 | Yunya |  22 | MALE   |  16000 |  1 |         1 |      1002 |
|  2 | Jack  |  25 | MALE   |  18000 |  2 |         2 |      1002 |
|  3 | Bella |  24 | FEMALE |  12000 |  3 |         3 |      1003 |
|  4 | Maria |  22 | FEMALE |   8000 |  4 |         4 |      1004 |
|  5 | Tom   |  23 | MALE   |   6000 |  5 |         5 |      1004 |
|  6 | Jason |  28 | MALE   |  32000 |  6 |         6 |      1001 |
|  6 | Jason |  28 | MALE   |  32000 |  7 |         6 |      1002 |
|  7 | James |  31 | MALE   |  35000 |  9 |         7 |      1001 |
|  7 | James |  31 | MALE   |  35000 |  8 |         7 |      1002 |
|  7 | James |  31 | MALE   |  35000 | 10 |         7 |      1003 |
|  8 | Lisa  |  36 | FEMALE |  28000 | 11 |         8 |      1003 |
|  8 | Lisa  |  36 | FEMALE |  28000 | 12 |         8 |      1004 |
+----+-------+-----+--------+--------+----+-----------+-----------+

​ 继续按照上面的思路，再将这将中间表与右表相连，就会得到完整的三表。

SELECT
    *
FROM
    employee
    JOIN emp_dep ON employee.id = emp_dep.emp_fk_id
    JOIN department ON department.dep_id = emp_dep.dep_fk_id;

+----+-------+-----+--------+--------+----+-----------+-----------+----+--------+-----------+
| id | name  | age | gender | salary | id | emp_fk_id | dep_fk_id | id | dep_id | name      |
+----+-------+-----+--------+--------+----+-----------+-----------+----+--------+-----------+
|  1 | Yunya |  22 | MALE   |  16000 |  1 |         1 |      1002 |  2 |   1002 | 开发部    |
|  2 | Jack  |  25 | MALE   |  18000 |  2 |         2 |      1002 |  2 |   1002 | 开发部    |
|  3 | Bella |  24 | FEMALE |  12000 |  3 |         3 |      1003 |  3 |   1003 | 财务部    |
|  4 | Maria |  22 | FEMALE |   8000 |  4 |         4 |      1004 |  4 |   1004 | 人事部    |
|  5 | Tom   |  23 | MALE   |   6000 |  5 |         5 |      1004 |  4 |   1004 | 人事部    |
|  6 | Jason |  28 | MALE   |  32000 |  6 |         6 |      1001 |  1 |   1001 | 研发部    |
|  6 | Jason |  28 | MALE   |  32000 |  7 |         6 |      1002 |  2 |   1002 | 开发部    |
|  7 | James |  31 | MALE   |  35000 |  9 |         7 |      1001 |  1 |   1001 | 研发部    |
|  7 | James |  31 | MALE   |  35000 |  8 |         7 |      1002 |  2 |   1002 | 开发部    |
|  7 | James |  31 | MALE   |  35000 | 10 |         7 |      1003 |  3 |   1003 | 财务部    |
|  8 | Lisa  |  36 | FEMALE |  28000 | 11 |         8 |      1003 |  3 |   1003 | 财务部    |
|  8 | Lisa  |  36 | FEMALE |  28000 | 12 |         8 |      1004 |  4 |   1004 | 人事部    |
+----+-------+-----+--------+--------+----+-----------+-----------+----+--------+-----------+

实例练习

​ 练习一，拿到James所在的部门，打印其部门名称：

SELECT
    name
FROM
    department
WHERE
    dep_id IN (
        SELECT dep_id FROM emp_dep AS mid JOIN employee AS emp ON mid.emp_fk_id = emp.id WHERE emp.name ="JAMES"
    );
    
+-----------+
| name      |
+-----------+
| 研发部    |
| 开发部    |
| 财务部    |
| 人事部    |
+-----------+

​ 练习二， 查询开发部的所有人员工资情况：

SELECT
    name,
    salary
FROM
    employee
WHERE
    id IN(
        SELECT
            emp_fk_id
        FROM
            department AS dep
            JOIN emp_dep AS mid ON mid.dep_fk_id = dep.dep_id
        WHERE
            dep.name = "开发部"
    );
    
+-------+--------+
| name  | salary |
+-------+--------+
| Yunya |  16000 |
| Jack  |  18000 |
| Jason |  32000 |
| James |  35000 |
+-------+--------+

​ 练习三，查询平均工资大于三万的部门名称。

​ 第一步，先用中间表和员工表拿出部门id再说：

SELECT
    dep_fk_id
FROM
    emp_dep AS mid
    JOIN employee AS emp ON emp.id = mid.emp_fk_Id
GROUP BY
    dep_fk_id
HAVING
    AVG(emp.salary) > 30000;

+-----------+
| dep_fk_id |
+-----------+
|      1001 |
+-----------+

​ 第二步，让这张虚拟表和部门表进行关联，查询一下其名称即可：

SELECT
    name
FROM
    department
WHERE
    dep_id IN (
        SELECT
            dep_fk_id
        FROM
            emp_dep AS mid
            JOIN employee AS emp ON emp.id = mid.emp_fk_Id
        GROUP BY
            dep_fk_id
        HAVING
            AVG(emp.salary) > 30000
    );
    
+-----------+
| name      |
+-----------+
| 研发部     |
+-----------+