1.1 概述
在 SQL 语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作，首先必须创建一个查询来确定用户不知道但包含在数据库中的值，将一个查询块嵌套在另一个查询块的 WHERE 字句或 HAVING 短语的条件中查询块称为子查询或内层查询。外部的 SELECT 查询语句，称为主查询或外查询。按结果集的行列数不同子查询可以分为 4 种 ♞ 标量子查询(结果集只有一行一列) ♞ 列子查询(结果集只有一列多行) ♞ 行子查询(结果集有一行多列) ♞ 表子查询(结果集一般为多行多列)
1.2 数据准备
代码语言：javascript复制# 员工表
mysql> select * from emp;
------ -------- -------- ------ ------------ ---------- ---------- ---------
| id | ename | job_id | mgr | joindate | salary | bonus | dept_id |
------ -------- -------- ------ ------------ ---------- ---------- ---------
| 1001 | 孙悟空 | 4 | 1004 | 2000-12-17 | 8000.00 | NULL | 20 |
| 1002 | 卢俊义 | 3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 |
| 1003 | 林冲 | 3 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 |
| 1004 | 唐僧 | 2 | 1009 | 2001-04-02 | 29750.00 | NULL | 20 |
| 1005 | 李逵 | 4 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | 30 |
| 1006 | 宋江 | 2 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 |
| 1007 | 刘备 | 2 | 1009 | 2001-09-01 | 24500.00 | NULL | 10 |
| 1008 | 猪八戒 | 4 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 |
| 1009 | 罗贯中 | 1 | NULL | 2001-11-17 | 50000.00 | NULL | 10 |
| 1010 | 吴用 | 3 | 1006 | 2001-09-08 | 15000.00 | 0.00 | 30 |
| 1011 | 沙僧 | 4 | 1004 | 2007-05-23 | 11000.00 | NULL | 20 |
| 1012 | 李逵 | 4 | 1006 | 2001-12-03 | 9500.00 | NULL | 30 |
| 1013 | 小白龙 | 4 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 |
| 1014 | 关羽 | 4 | 1007 | 2002-01-23 | 13000.00 | NULL | 10 |
------ -------- -------- ------ ------------ ---------- ---------- ---------
14 rows in set (0.15 sec)
# 部门信息表
mysql> select * from dept;
---- -------- ------
| id | dname | loc |
---- -------- ------
| 10 | 教研部 | 北京 |
| 20 | 学工部 | 上海 |
| 30 | 销售部 | 广州 |
| 40 | 财务部 | 深圳 |
---- -------- ------
4 rows in set (0.15 sec)
# 工作职责表
mysql> select * from job;
---- -------- --------------------
| id | jname | description |
---- -------- --------------------
| 1 | 董事长 | 管理整个公司，接单 |
| 2 | 经理 | 管理部门员工 |
| 3 | 销售员 | 向客人推销产品 |
| 4 | 文员 | 使用办公软件 |
---- -------- --------------------
4 rows in set (0.16 sec)
# 薪资等级表
mysql> select * from salarygrade;
------- ---------- ----------
| grade | losalary | hisalary |
------- ---------- ----------
| 1 | 7000 | 12000 |
| 2 | 12010 | 14000 |
| 3 | 14010 | 20000 |
| 4 | 20010 | 30000 |
| 5 | 30010 | 99990 |
------- ---------- ----------
5 rows in set (0.14 sec)1.3 select 后的子查询
1.3.1 查询每个部门员工个数
代码语言：javascript复制mysql> select dept.*,
-> ( select count(*) from emp where emp.dept_id = dept.id ) as 员工个数
-> from dept;
---- -------- ------ ----------
| id | dname | loc | 员工个数 |
---- -------- ------ ----------
| 10 | 教研部 | 北京 | 3 |
| 20 | 学工部 | 上海 | 5 |
| 30 | 销售部 | 广州 | 6 |
| 40 | 财务部 | 深圳 | 0 |
---- -------- ------ ----------
4 rows in set (2.02 sec)1.3.2 查询 1002 号员工的姓名和部门名称
代码语言：javascript复制mysql> select ename as 姓名,
-> (select dname from dept where emp.dept_id = dept.id) as 部门名
-> from emp
-> where id = 1002;
-------- --------
| 姓名 | 部门名 |
-------- --------
| 卢俊义 | 销售部 |
-------- --------
1 row in set (0.15 sec)1.4 from 后的子查询
1.4.1 查询每个部门的平均工资
代码语言：javascript复制mysql> select avg(salary) as avg, dept_id from emp group by dept_id;
-------------- ---------
| avg | dept_id |
-------------- ---------
| 29166.666667 | 10 |
| 21750.000000 | 20 |
| 15666.666667 | 30 |
-------------- ---------
3 rows in set (0.11 sec)
mysql> select dept.dname as 部门, t.avg as 平均工资 from
-> ( select avg(salary) as avg, dept_id from emp group by dept_id ) as t, dept
-> where t.dept_id = dept.id;
-------- --------------
| 部门 | 平均工资 |
-------- --------------
| 教研部 | 29166.666667 |
| 学工部 | 21750.000000 |
| 销售部 | 15666.666667 |
-------- --------------
3 rows in set (0.18 sec)1.5 where 后的子查询
1.5.1 查询工资高于 30 号部门平均工资的员工信息
代码语言：javascript复制mysql> select avg(salary) from emp where dept_id = 30;
--------------
| avg(salary) |
--------------
| 15666.666667 |
--------------
1 row in set (0.10 sec)
mysql> select * from emp where ( select avg(salary) from emp where dept_id = 30 ) < salary;
------ -------- -------- ------ ------------ ---------- --------- ---------
| id | ename | job_id | mgr | joindate | salary | bonus | dept_id |
------ -------- -------- ------ ------------ ---------- --------- ---------
| 1002 | 卢俊义 | 3 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 |
| 1004 | 唐僧 | 2 | 1009 | 2001-04-02 | 29750.00 | NULL | 20 |
| 1006 | 宋江 | 2 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 |
| 1007 | 刘备 | 2 | 1009 | 2001-09-01 | 24500.00 | NULL | 10 |
| 1008 | 猪八戒 | 4 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 |
| 1009 | 罗贯中 | 1 | NULL | 2001-11-17 | 50000.00 | NULL | 10 |
| 1013 | 小白龙 | 4 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 |
------ -------- -------- ------ ------------ ---------- --------- ---------
7 rows in set (0.16 sec)1.5.2 查询有工资高于 29000 的部门
代码语言：javascript复制mysql> select dept_id from emp where salary > 29000 group by dept_id;
---------
| dept_id |
---------
| 10 |
| 20 |
---------
2 rows in set (0.11 sec)
mysql> select dname as 部门名
-> from dept
-> where id in (select dept_id from emp where salary > 29000 group by dept_id);
--------
| 部门名 |
--------
| 学工部 |
| 教研部 |
--------
2 rows in set (0.14 sec)1.5.3 查询部门号最小，工资最高的员工信息
代码语言：javascript复制mysql> select min(dept_id), max(salary) from emp;
-------------- -------------
| min(dept_id) | max(salary) |
-------------- -------------
| 10 | 50000.00 |
-------------- -------------
1 row in set (0.11 sec)
mysql> select * from emp where (dept_id, salary) = (select min(dept_id), max(salary) from emp);
------ -------- -------- ------ ------------ ---------- ------- ---------
| id | ename | job_id | mgr | joindate | salary | bonus | dept_id |
------ -------- -------- ------ ------------ ---------- ------- ---------
| 1009 | 罗贯中 | 1 | NULL | 2001-11-17 | 50000.00 | NULL | 10 |
------ -------- -------- ------ ------------ ---------- ------- ---------
1 row in set (19.54 sec)1.6 注意
① 子查询放在小括号内，且一般放在条件的右侧。 ② 标量子查询，一般搭配着单行操作符使用，列子查询，一般搭配着多行操作符使用。 ③ 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。 ④ select 后面的子查询，仅仅支持标量子查询，即结果集只有一行一列。 ⑤ from 后面的子查询是将子查询的结果集充当一张临时表，要求必须起别名，否者这个表找不到。然后将真实的表和子查询结果表进行连接查询。 ⑥ where 或 having 后面可以有：标量子查询、 列子查询(一般搭配着多行操作符使用 in、any、some、all 等)、行子查询。
