实测：查员工的 ID、姓名和所属部门名称#

课程的第一个内连接练习是”查询所有员工的 ID、姓名，及所属的部门名称”，隐式和显式各写一遍：

1
-- 隐式内连接
2
select emp.id, emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
3

4
-- 显式内连接（inner 可省）
5
select emp.id, emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;
6
select emp.id, emp.name, dept.name from emp join dept on emp.dept_id = dept.id;

1
+----+--------+--------+
2
| id | name   | name   |
3
|    |        | (部门) |
4
+----+--------+--------+
5
|  1 | 施耐庵 | 教研部 |
6
|  2 | 宋江   | 教研部 |
7
|  3 | 卢俊义 | 教研部 |
8
|  4 | 吴用   | 教研部 |
9
|  5 | 公孙胜 | 教研部 |
10
...

1
select e.id, e.name, d.name from emp e join dept d on e.dept_id = d.id;

第二个练习再加两个筛选条件——“性别为男，且工资高于 8000”：

1
-- 隐式
2
select e.id, e.name, d.name
3
from emp e, dept d
4
where e.dept_id = d.id and e.gender = 1 and e.salary > 8000;
5

6
-- 显式
7
select e.id, e.name, d.name
8
from emp e join dept d on e.dept_id = d.id
9
where e.gender = 1 and e.salary > 8000;

实测：三种外连接的结果#

1
select e.name, d.name from emp e left outer join dept d on e.dept_id = d.id;

1
select d.name, e.name from emp e right outer join dept d on e.dept_id = d.id;

1
select e.name, d.name from dept d left join emp e on e.dept_id = d.id;

1
select e.name, d.name from emp e left join dept d on e.dept_id = d.id where e.salary > 8000;

实测一：标量子查询（返回一个值）#

1
-- A. 查询最早入职的员工信息
2
select min(entry_date) from emp;                  -- 第一步：最早入职时间 = 2000-01-01
3
select * from emp where entry_date = '2000-01-01';-- 第二步：拿着这个日期去查人
4

5
-- 合并成一条（子查询套在括号里）
6
select * from emp where entry_date = (select min(entry_date) from emp);

1
-- B. 查询在"阮小五"入职之后入职的员工信息
2
select entry_date from emp where name = '阮小五';  -- 第一步：阮小五的入职时间 = 2015-01-01
3
select * from emp where entry_date > '2015-01-01'; -- 第二步
4

5
-- 合并
6
select * from emp where entry_date > (select entry_date from emp where name = '阮小五');

实测二：列子查询（返回一列）#

1
-- A. 查询"教研部"和"咨询部"的所有员工信息
2
select id from dept where name = '教研部' or name = '咨询部';  -- 第一步：部门 id 是 2 和 3
3
select * from emp where dept_id in (2, 3);                      -- 第二步
4

5
-- 合并：子查询返回一列（2 和 3），所以用 in 来接
6
select * from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');

实测三：行子查询（返回一行）#

1
-- A. 查询与"李忠"的薪资及职位都相同的员工信息
2
select salary, job from emp where name = '李忠';   -- 第一步：薪资 5000、职位 5
3
select * from emp where salary = 5000 and job = 5;  -- 第二步
4

5
-- 合并（两个标量子查询拼起来，各查各的）
6
select * from emp where salary = (select salary from emp where name = '李忠')
7
                    and job = (select job from emp where name = '李忠');
8

9
-- 优化：把"一行"当成整体去比，一条子查询搞定
10
select * from emp where (salary, job) = (select salary, job from emp where name = '李忠');

实测四：表子查询（返回一张小表）#

1
-- A. 获取每个部门中薪资最高的员工信息
2
-- 第一步：先查出每个部门的最高薪资（结果是一张"多行多列"的小表）
3
select dept_id, max(salary) from emp group by dept_id;

1
+---------+-------------+
2
| dept_id | max(salary) |
3
+---------+-------------+
4
|       1 |        6500 |   ← 学工部
5
|       2 |       15000 |   ← 教研部
6
|       3 |        5800 |   ← 咨询部
7
|    NULL |        NULL |   ← 没有部门的那位
8
+---------+-------------+

1
-- 第二步 + 合并：把这张小表当成一张"临时表"再和 emp 连接
2
select * from emp e, (select dept_id, max(salary) max_sal from emp group by dept_id) a
3
where e.dept_id = a.dept_id and e.salary = a.max_sal;

需求 1：教研部 + 男性 + 2011-05-01 之后入职#

多个条件同时成立，连接条件 + 筛选条件都用 and 串在 where 里：

1
select e.* from emp e, dept d
2
where e.dept_id = d.id          -- 连接条件
3
  and d.name = '教研部'          -- 部门条件（在 dept 表里）
4
  and e.gender = 1               -- 性别条件
5
  and e.entry_date > '2011-05-01';   -- 入职日期条件

需求 2：工资低于公司平均工资的男性#

“公司平均工资”是一个事先不知道的值——这正是子查询的用武之地，先拆两步：

1
-- 第一步：先算出公司平均工资
2
select avg(salary) from emp;

1
-- 第二步（分步写）：拿着 7606.8966 去比较
2
select * from emp where salary < 7606.8966 and gender = 1;
3

4
-- 合并成一条（标量子查询）
5
select * from emp where salary < (select avg(salary) from emp) and gender = 1;

需求 3：部门人数超过 10 人的部门名称#

“部门人数”需要分组统计，“人数超过 10”是对统计结果的过滤（having），最后只要部门名称：

1
select d.name, count(*) from emp e, dept d
2
where e.dept_id = d.id
3
group by d.name
4
having count(*) > 10;

1
select name from dept
2
where id in (select dept_id from emp group by dept_id having count(*) > 10);

需求 4：2010-05-01 后入职 + 薪资高于 10000 + 教研部 + 薪资倒序#

条件比需求 1 更多，还多了一个排序：

1
select e.* from emp e, dept d
2
where e.dept_id = d.id
3
  and e.entry_date > '2010-05-01'
4
  and e.salary > 10000
5
  and d.name = '教研部'
6
order by e.salary desc;        -- 薪资倒序

需求 5：工资低于本部门平均工资#

这道题的”平均工资”不是全公司的平均值，而是每个部门各算一个——所以不能简单套一个标量子查询，得先把”每个部门的平均薪资”做成一张小表（表子查询），再和员工表连接起来比：

1
-- 第一步：先算出每个部门的平均薪资（三行数据：学工部、教研部、咨询部各一行）
2
select dept_id, avg(salary) avg_sal from emp group by dept_id;
3

4
-- 第二步 + 合并：把这张小表当临时表和 emp 连接，逐行比较
5
select e.* from emp e, (select dept_id, avg(salary) avg_sal from emp group by dept_id) a
6
where e.dept_id = a.dept_id and e.salary < a.avg_sal;

一、知识回顾（读完直接做下面的实践题）#

1. 多表查询指从多张表中查询数据；笛卡尔积指两个集合的所有组合情况——本机实测 select count(*) from emp e, dept d = 180（30 个员工 × 6 个部门），所以多表查询必须加连接条件消除无效的笛卡尔积
2. 多表查询的分类：连接查询（内连接 / 外连接：左外、右外）+ 子查询；内连接取 A、B 的交集，左外连接取左表全部（含交集），右外连接取右表全部
3. 内连接的两种写法：隐式 select 字段列表 from 表1, 表2 where 连接条件 ...;、显式 select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;——两种结果一样；一旦起了别名，就必须用别名，不能再写表名
4. 外连接的两种写法：表1 left [outer] join 表2 on 连接条件、表1 right [outer] join 表2 on 连接条件；常用左外连接，因为右外连接可以通过调换两张表的顺序改写成左外
5. 子查询是”SQL 语句中嵌套 select 语句”，写法形如 select * from t1 where column1 = (select column1 from t2 …);，外层的语句可以是 insert / update / delete / select 中的任何一个
6. 子查询的四种分类：标量子查询（返回一个值）、列子查询（返回一列，外层用 in 接）、行子查询（返回一行，用 (字段1,字段2) = (子查询) 接）、表子查询（返回多行多列，当成一张临时表放在 from 后面连接）
7. 子查询的要点（PPT 第 32 页）：先对需求做拆分，明确具体的步骤，然后再逐步编写 SQL 语句——课程里每道题都是”a. 查子查询 → b. 写外层 → 合并成一条”
8. 本机实测的关键数字（30 名员工、6 个部门）：内连接查”员工 + 部门名” 29 行；左外连接 30 行（没有部门的员工部门名为 NULL）；右外连接查”部门 + 员工名” 32 行（就业部、人事部、行政部三行为 NULL）；公司平均工资 7606.8966；学工部 / 教研部 / 咨询部的平均薪资 5285.71 / 9793.33 / 5242.86
9. 子查询四类的实测例子：最早入职的员工是施耐庵（entry_date = (select min(entry_date) from emp)）；在阮小五入职之后入职的 5 人（李应、阮小七、阮小二、李云、令狐冲）；教研部 + 咨询部的员工 22 人（列子查询，用 in 接）；与李忠薪资职位都相同的是童威和李忠（行子查询，2 人）；每个部门薪资最高的员工是施耐庵、扈三娘、阮籍（表子查询，3 人）
10. PPT 第 34 页五个需求的结果：①5 人（公孙胜、宋江、时迁、阮小二、令狐冲）②15 人（低于 7606.8966 的男性）③只有教研部（15 人）④3 人（孙二娘 10900、阮小二 10800、时迁 10200）⑤16 人（学工部 4 + 教研部 8 + 咨询部 4）

二、裸写题#

• 2-1 用内连接查出员工的姓名和所属部门名称 在 tlias 库上查询性别为男、且工资高于 8000 的员工，列出员工 ID、姓名、所属部门名称。 （练习文件 test_65_多表查询.sql 里给了题目注释和写作区。）

  提示（先自己想，实在想不出再点开）

  一级 · 思路：涉及两张表（emp 拿姓名和工资、dept 拿部门名称），所以是”连接条件 + 筛选条件”的组合；连接条件是”员工的部门 = 部门的 id”，筛选条件是性别和工资 二级 · 方法：隐式内连接 from emp e, dept d where e.dept_id = d.id and ...；显式内连接 from emp e join dept d on e.dept_id = d.id where ...；多个条件用 and 串起来（隐性连接的连接条件也放在 where 里） 三级 · 骨架：select e.id, e.name, ____.name from emp e, dept d where e.dept_id = d.id and e.gender = ____ and e.salary ____ 8000;

  参考答案（做完再点开）

  1
  -- 隐式内连接
  2
  select e.id, e.name, d.name
  3
  from emp e, dept d
  4
  where e.dept_id = d.id and e.gender = 1 and e.salary > 8000;
  5
  
  6
  -- 显式内连接（结果一样）
  7
  select e.id, e.name, d.name
  8
  from emp e join dept d on e.dept_id = d.id
  9
  where e.gender = 1 and e.salary > 8000;

  本机实测结果：11 行——施耐庵 15000、宋江 8600、卢俊义 8900、吴用 9200、公孙胜 9500、呼延灼 9700、小李广 10000、史进 10600、鲁智深 9600、时迁 10200、阮小二 10800，全部属于教研部（学工部、咨询部的薪资都够不到 8000 这条线）。 别忘了 gender 的取值含义是 1 男、2 女（写反了会查出 0 行）；另外那位没有部门的员工 dept_id 是 NULL，即使工资条件满足也配不上任何部门，所以不会出现在内连接的结果里。

• 2-2 用外连接查出”每个部门有多少人”和”没有员工的部门”

  1. 查出每个部门的名称和它下面的员工人数，没有员工的部门也要列出来（显示 0 人）；
  2. 查出一个员工都没有的部门名称。
  提示（先自己想，实在想不出再点开）

  一级 · 思路：第 1 问要保住”没有员工的部门”，所以要用外连接把部门放在保留的那一侧；第 2 问就是第 1 问里”人数为 0”的那几行 二级 · 方法：部门放左边用 left join；统计人数时不能用 count(*)——外连接补出来的那一行也有一个 *，会被算成 1 人，要用 count(员工表的某个字段)（NULL 不计入统计）；第 2 问可以再加条件 where 员工表.id is null 三级 · 骨架：第 1 问 select d.name, count(e.id) from dept d ____ join emp e on d.id = e.dept_id ____ ____ d.name;；第 2 问把上面结果用 having count(____) = 0 或 where e.id is ____ 筛一下

  参考答案（做完再点开）

  1
  -- 1. 每个部门的员工人数（含 0 人）
  2
  select d.name, count(e.id) as 人数
  3
  from dept d left join emp e on d.id = e.dept_id
  4
  group by d.name;
  5
  
  6
  -- 2. 一个员工都没有的部门
  7
  select d.name
  8
  from dept d left join emp e on d.id = e.dept_id
  9
  where e.id is null;
  10
  -- 换个写法：先按部门分组统计人数，再筛出人数为 0 的组
  11
  select d.name, count(e.id) as cnt
  12
  from dept d left join emp e on d.id = e.dept_id
  13
  group by d.name having cnt = 0;

  本机实测结果：

  1
  +--------+------+
  2
  | name   | 人数 |
  3
  +--------+------+
  4
  | 学工部 |    7 |
  5
  | 教研部 |   15 |
  6
  | 咨询部 |    7 |
  7
  | 就业部 |    0 |
  8
  | 人事部 |    0 |
  9
  | 行政部 |    0 |
  10
  +--------+------+

  第 2 问查出的是就业部、人事部、行政部这 3 个部门。 这里最容易踩的坑是 count(*)：外连接给”没有员工的部门”补出来的那一行也是实打实的一行，count(*) 会把它数成 1；改成 count(e.id)（或团队里任何一个员工表字段）之后，补出来的 NULL 被跳过，才正确显示 0。

• 2-3 用标量子查询查两类员工

  1. 查出最早入职的员工信息；
  2. 查出在”阮小五”入职之后入职的员工信息。
  提示（先自己想，实在想不出再点开）

  一级 · 思路：两问的”比较标准”（最早入职日期、阮小五的入职日期）都不能先写死在 SQL 里，所以先单独把它查出来，再塞回外层语句 二级 · 方法：子查询写在括号里，位置就是外层条件的右边——where 字段 = (子查询)、where 字段 > (子查询)；求最早日期用 min(entry_date)，“之后入职”用 >（不是 >=） 三级 · 骨架：第 1 问 select * from emp where entry_date = (select ____(entry_date) from ____);；第 2 问 select * from emp where entry_date ____ (select entry_date from emp where name = '____');

  参考答案（做完再点开）

  1
  -- 1. 最早入职的员工
  2
  select min(entry_date) from emp;                       -- 先看看最早是哪天：2000-01-01
  3
  select * from emp where entry_date = (select min(entry_date) from emp);
  4
  
  5
  -- 2. 在"阮小五"入职之后入职的员工
  6
  select entry_date from emp where name = '阮小五';        -- 阮小五的入职日期：2015-01-01
  7
  select * from emp where entry_date > (select entry_date from emp where name = '阮小五');

  本机实测结果：

  1. 最早入职的是施耐庵（入职日期 2000-01-01）；
  2. 在阮小五之后入职的一共 5 人：李应（2015-03-21）、阮小七（2016-01-01）、阮小二（2018-01-01）、李云（2020-03-01）、令狐冲（2023-10-19）。

  第 2 问有个细节值得盯一下：宋江和时迁的入职日期正好是 2015-01-01（和阮小五同一天），条件写的是 > 严格大于，所以他俩不算”之后入职”；如果写成 >=，结果会从 5 人变成 8 人——多出来的是宋江、时迁，还有阮小五自己（他的入职日期正好等于自己那个日期）。

• 2-4 用表子查询找出每个部门薪资最高的员工 查出每个部门里薪资最高的那名员工的信息（部门名称 + 员工姓名 + 薪资）。

  提示（先自己想，实在想不出再点开）

  一级 · 思路：不能直接一条 group by 查出来——因为分组之后”谁拿了最高薪”这个人的其他字段是查不出来的。拆两步：先算出每个部门的最高薪资（一张按部门分组的小表），再拿这张小表去和员工表连接，把”薪资 = 本部门最高薪资”的人找出来 二级 · 方法：第 1 步 select dept_id, max(salary) from emp group by dept_id;；第 2 步把第 1 步的语句整段用括号括起来放到 from 后面当临时表（起个别名），连接条件是”部门相同 且 薪资等于该部门的最高薪资” 三级 · 骨架：select ... from emp e, dept d, (select dept_id, max(salary) max_sal from emp ____ ____ dept_id) a where e.dept_id = d.id and e.dept_id = a.____ and e.salary = a.____;

  参考答案（做完再点开）

  1
  -- 第一步：每个部门的最高薪资（一张小表）
  2
  select dept_id, max(salary) from emp group by dept_id;
  3
  
  4
  -- 第二步：把小表当临时表，和 emp、dept 连接起来
  5
  select d.name as 部门, e.name as 姓名, e.salary as 薪资
  6
  from emp e, dept d, (select dept_id, max(salary) max_sal from emp group by dept_id) a
  7
  where e.dept_id = d.id
  8
    and e.dept_id = a.dept_id
  9
    and e.salary = a.max_sal;

  本机实测结果：

  1
  +--------+--------+-------+
  2
  | 部门   | 姓名   | 薪资  |
  3
  +--------+--------+-------+
  4
  | 教研部 | 施耐庵 | 15000 |
  5
  | 学工部 | 扈三娘 |  6500 |
  6
  | 咨询部 | 阮籍   |  5800 |
  7
  +--------+--------+-------+

  3 行——部门数量是 6，但只有 3 个部门下面有员工；剩下三个部门（就业部、人事部、行政部）没有员工，自然没有”最高薪员工”。 另外注意子查询那张小表里还有一行 dept_id 为 NULL（没有部门的那位，max(salary) 也是 NULL），连接条件 e.dept_id = a.dept_id 会把它排除掉——NULL 和谁都不相等。

三、综合题#

• 3-1 把 PPT 第 34 页的五个需求在 tlias 库上做完 这是本节的综合案例，五道题要用到内连接、子查询、分组统计和排序。要求每题都写出完整 SQL，并先回答”这题该用哪种写法”（连接查询还是子查询？子查询是哪一类？）：

  1. 查询”教研部”的”男性”员工，且在”2011-05-01”之后入职的员工信息；
  2. 查询工资低于公司平均工资且性别为男的员工信息（先单独执行一次算平均工资的语句，把结果记下来）；
  3. 查询部门人数超过 10 人的部门名称；
  4. 查询在”2010-05-01”后入职、且薪资高于 10000 的”教研部”员工信息，并按薪资倒序排序；
  5. 查询工资低于本部门平均工资的员工信息（先单独查出每个部门的平均工资，把三个数字记下来）。

  涉及知识点

  知识点	在这里的应用
  内连接 + 多条件筛选	第 1、4 题——连接条件与筛选条件都用 and 串起来
  标量子查询	第 2 题——公司平均工资是一个值，查出来再比
  分组 + having / 子查询	第 3 题——按部门分组统计人数，再筛出超过 10 人的
  排序	第 4 题——order by ... desc 写在最后
  表子查询	第 5 题——“每个部门的平均工资”是一张多行多列的小表，当临时表连接

  提示（先自己想，实在想不出再点开）

  一级 · 思路：第 1、4 题只需要”连接 + 筛选 + 排序”；第 2 题的关键是”平均值事先不知道”；第 3 题的关键是”人数是分组统计出来的”；第 5 题是第 2 题的升级版——平均值不是全局一个，而是每个部门一个 二级 · 方法：连接 from emp e, dept d where e.dept_id = d.id；子查询放在括号里（标量子查询用 =/< 接，表子查询放进 from 当临时表）；分组统计 group by + having 筛组；排序 order by 字段 desc 三级 · 骨架：第 3 题 select d.name from emp e, dept d where e.dept_id = d.id group by d.____ having ____(*) > 10;；第 5 题 select e.* from emp e, (select dept_id, ____(salary) avg_sal from emp group by dept_id) a where e.dept_id = a.____ and e.salary < a.____;

  参考答案（做完再点开）

  1
  -- 1. 教研部 + 男性 + 2011-05-01 之后入职（内连接 + 多个筛选条件）
  2
  select e.* from emp e, dept d
  3
  where e.dept_id = d.id and d.name = '教研部' and e.gender = 1 and e.entry_date > '2011-05-01';
  4
  
  5
  -- 2. 工资低于公司平均工资的男性（标量子查询）
  6
  select avg(salary) from emp;                       -- 先看一眼：7606.8966
  7
  select * from emp where salary < (select avg(salary) from emp) and gender = 1;
  8
  
  9
  -- 3. 部门人数超过 10 人的部门名称（内连接 + 分组 + having）
  10
  select d.name, count(*) from emp e, dept d
  11
  where e.dept_id = d.id group by d.name having count(*) > 10;
  12
  -- 子查询写法（结果一样）
  13
  select name from dept where id in (select dept_id from emp group by dept_id having count(*) > 10);
  14
  
  15
  -- 4. 2010-05-01 后入职 + 薪资高于 10000 的教研部员工，薪资倒序（内连接 + 排序）
  16
  select e.* from emp e, dept d
  17
  where e.dept_id = d.id and e.entry_date > '2010-05-01' and e.salary > 10000 and d.name = '教研部'
  18
  order by e.salary desc;
  19
  
  20
  -- 5. 工资低于本部门平均工资（表子查询）
  21
  select dept_id, avg(salary) avg_sal from emp group by dept_id;
  22
  select e.* from emp e, (select dept_id, avg(salary) avg_sal from emp group by dept_id) a
  23
  where e.dept_id = a.dept_id and e.salary < a.avg_sal;

  显示更多显示更少

  本机实测结果（MySQL 9.0.1，库 tlias；30 名员工、6 个部门）：

  1. 5 人——公孙胜（2012-12-05）、宋江（2015-01-01）、时迁（2015-01-01）、阮小二（2018-01-01）、令狐冲（2023-10-19）；
  2. 公司平均工资 7606.8966（= 薪资总额 220600 ÷ 29，分母不含那位没有薪资的员工）；低于它的男性 15 人：柴进 4700、李逵 4800、童猛 4800、武松 4900、林冲 5000、童威 5000、李忠 5000、阮小五 5200、杨志 5300、燕顺 5400、阮小七 5500、李应 5800、阮籍 5800、李俊 6600、令狐冲 6800；
  3. 只有教研部（15 人）——学工部、咨询部各 7 人，都没过 10 人这条线；
  4. 3 人——孙二娘 10900、阮小二 10800、时迁 10200（降序）；
  5. 16 人——学工部 4 人（平均 5285.71）、教研部 8 人（平均 9793.33）、咨询部 4 人（平均 5242.86）。

  复盘一下五道题各自的门道：第 1、4 题是”连接 + 筛选”，条件越多越要一行一个条件地摆清楚；第 2、3、5 题的共同点是”有个数事先不知道”——第 2 题不知道的就是一个平均值（标量子查询），第 3 题不知道的是分组后的统计结果（having 或子查询），第 5 题不知道的是”每一组的平均值”（表子查询）。先把不知道的那部分单独查出来，再塞进外层 SQL——这就是 PPT 说的”先拆分需求、明确步骤”。