数据库系统原理与应用教程（049）—— MySQL 查询（十一）：子查询

在一个 SELECT 语句中嵌入另外一个 SELECT 语句，被嵌入的 SELECT 语句称之为子查询，子查询所在的查询称为外部查询。子查询要么充当条件，要么充当主查询的数据源。根据子查询返回的结果可以把子查询分为以下几类：

（1）标量子查询：子查询结果是一个数据（单行单列）。

（2）列子查询：子查询结果是一列（多行单列）。

（3）表子查询：子查询结果是一张临时表（多行多列）。

（4）EXISTS 子查询：根据子查询是否有返回值决定主查询的返回结果。

一、标量子查询

标量子查询的结果是一个单行单列的数据。一般和 =、>、<、>=、<=、!= 等操作符一起使用来构造外部查询的条件。

标量子查询的语法格式如下：

select ...
where 列名 比较运算符 (标量子查询);

说明：标量子查询的查询结果一个单行单列的数据，此时外部查询的运算符可以使用 =、>、<、>=、<=、!= 等比较运算符。如果子查询结果为空，则外部查询的条件也为 NULL。如果查询结果为多行单列，则出现错误。

例如：

（1）查询和【李明博】地址相同的学生信息。

/*
select * from student where addr = (select addr from student where s_name = '李明博'
);
*/
mysql> select * from student where addr = (->     select addr from student where s_name = '李明博'-> );
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
6 rows in set (0.01 sec)

（2）查询【特朗普】同学选修的课程【高等数学】成绩。

/*
select score from score where s_id = (select s_id from student where s_name = '特朗普'
) and c_id = (select c_id from course where c_name = '高等数学'
);
*/
mysql> select score from score where s_id = (->     select s_id from student where s_name = '特朗普'-> ) and c_id = (->     select c_id from course where c_name = '高等数学'-> );
+-------+
| score |
+-------+
|    68 |
+-------+
1 row in set (0.00 sec)

（3）查询【特朗普】同学选修的课程【大学英语】成绩。

/*
select score from score where s_id = (select s_id from student where s_name = '特朗普'
) and c_id = (select c_id from course where c_name = '大学英语'
);
*/
-- 特朗普没有选修大学英语课程，查询结果为空
mysql> select score from score where s_id = (->     select s_id from student where s_name = '特朗普'-> ) and c_id = (->     select c_id from course where c_name = '大学英语'-> );
Empty set (0.00 sec)

（4）修改 student 表中的数据如下

mysql> update student set s_name='刘艳' where s_id='S2015';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from student;
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2015 | 刘艳      | 女     | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2016 | 刘若非    | 女     | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2021 | 董雯花    | 女     | 2000-07-30 00:00:00 | 13533735564 | 开封市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2024 | 奥巴马    | 男     | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
14 rows in set (0.00 sec)

（5）查询和【刘艳】同学年龄相同的学生信息

/*
select * from student where year(now())-year(birth) = (select year(now())-year(birth) from student where s_name = '刘艳'
);
*/-- 查询出现错误，因为子查询结果有两个数据
mysql> select * from student where year(now())-year(birth) = (->     select year(now())-year(birth) from student where s_name = '刘艳'-> );
ERROR 1242 (21000): Subquery returns more than 1 row

二、列子查询

列子查询的结果是一列多行，查询结果相当于一个数据集合。列子查询一般使用 IN、ANY | SOME、 ALL 等比较运算符。

1、运算符 IN

运算符 IN 表示外部查询的某个列的取值和查询结果中的任意一个数据相等即可。

例如：查询和【刘艳】同学年龄相同的学生信息

/*
select * from student where year(now())-year(birth) IN (select year(now())-year(birth) from student where s_name = '刘艳'
);
*/-- 因表中有两个学生姓名为【刘艳】，子查询查到两条记录。
-- 则外部查询的条件为：只要年龄和其中的一个刘艳的年龄相同即可。
mysql> select * from student where year(now())-year(birth) IN (->     select year(now())-year(birth) from student where s_name = '刘艳'-> );
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2015 | 刘艳      | 女     | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
10 rows in set (0.00 sec)

2、运算符 ANY | SOME

ANY | SOME 一般和大于号或小于号一起使用，SOME 和 ANY 的用法和查询结果完全相同。形式为如下：

select ... 
where 列名 > ANY | SOME(子查询)  -- 表示比查询结果中的任意一个大即满足条件（比最小的大即可）
-- 或
select ... 
where 列名 < ANY | SOME(子查询)  -- 表示比查询结果中的任意一个小即满足条件（比最大的小即可）

例如：

student 表中有两个【刘艳】，年龄分别为 23 和 24，结果如下。

mysql> select year(now())-year(birth) from student where s_name = '刘艳';
+-------------------------+
| year(now())-year(birth) |
+-------------------------+
|                      24 |
|                      23 |
+-------------------------+
2 rows in set (0.00 sec)

（1）查询比【刘艳】年龄大的学生信息。

/*
select * from student where year(now())-year(birth) >= some (select year(now())-year(birth) from student where s_name = '刘艳'
);
*/
-- 查询结果为所有大于年龄大于或等于 23 的学生信息，及 birth 为 1999 年 12 月 31 日之前出生的学生信息
mysql> select * from student where year(now())-year(birth) >= some (->     select year(now())-year(birth) from student where s_name = '刘艳'-> );
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2015 | 刘艳      | 女     | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
10 rows in set (0.00 sec)

（2）查询比【刘艳】年龄小的学生信息。

/*
select * from student where year(now())-year(birth) <= some (select year(now())-year(birth) from student where s_name = '刘艳'
);
*/
-- 查询结果为所有大于年龄小于或等于 24 的学生信息，及 birth 为 1998 年 1 月 1 日之后出生的学生信息
mysql> select * from student where year(now())-year(birth) <= some (->     select year(now())-year(birth) from student where s_name = '刘艳'-> );
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2015 | 刘艳      | 女     | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2016 | 刘若非    | 女     | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2021 | 董雯花    | 女     | 2000-07-30 00:00:00 | 13533735564 | 开封市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2024 | 奥巴马    | 男     | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
14 rows in set (0.00 sec)

3、运算符 ALL

ALL 一般和大于号或小于号一起使用。形式为如下：

select ... 
where 列名 > ALL(子查询)  -- 表示比查询结果中所有的数据都大（比最大的还大）
-- 或
select ... 
where 列名 < ALL(子查询)  -- 表示比查询结果中所有的数据都小（比最小的还小）

（1）查询比【刘艳】年龄大的学生信息。

/*
select * from student where year(now())-year(birth) >= all (select year(now())-year(birth) from student where s_name = '刘艳'
);
*/
-- 查询结果为所有大于年龄大于或等于 24 的学生信息，及 birth 为 1998 年 12 月 31 日之前出生的学生信息
mysql> select * from student where year(now())-year(birth) >= all (->     select year(now())-year(birth) from student where s_name = '刘艳'-> );
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2013 | 曹梦德    | 男     | 1998-02-13 00:00:00 | 13853735522 | 郑州市    |
| S2014 | 刘艳      | 女     | 1998-06-24 00:00:00 | 13623735335 | 郑州市    |
| S2026 | 张学有    | 男     | 1998-07-06 00:00:00 | 13743735566 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
3 rows in set (0.01 sec)

（2）查询比【刘艳】年龄小的学生信息。

/*
select * from student where year(now())-year(birth) <= all (select year(now())-year(birth) from student where s_name = '刘艳'
);
*/
-- 查询结果为所有大于年龄小于或等于 23 的学生信息，及 birth 为 1999 年 1 月 1 日之后出生的学生信息
mysql> select * from student where year(now())-year(birth) <= all (->     select year(now())-year(birth) from student where s_name = '刘艳'-> );
+-------+-----------+--------+---------------------+-------------+-----------+
| s_id  | s_name    | gender | birth               | phone       | addr      |
+-------+-----------+--------+---------------------+-------------+-----------+
| S2011 | 张晓刚    | 男     | 1999-12-03 00:00:00 | 13163735775 | 信阳市    |
| S2012 | 刘小青    | 女     | 1999-10-11 00:00:00 | 13603732255 | 新乡市    |
| S2015 | 刘艳      | 女     | 1999-07-06 00:00:00 | 13813735225 | 信阳市    |
| S2016 | 刘若非    | 女     | 2000-08-31 00:00:00 | 13683735533 | 开封市    |
| S2021 | 董雯花    | 女     | 2000-07-30 00:00:00 | 13533735564 | 开封市    |
| S2022 | 周华建    | 男     | 1999-05-25 00:00:00 | 13243735578 | 郑州市    |
| S2023 | 特朗普    | 男     | 1999-06-21 00:00:00 | 13343735588 | 新乡市    |
| S2024 | 奥巴马    | 男     | 2000-10-17 00:00:00 | 13843735885 | 信阳市    |
| S2025 | 周健华    | 男     | 2000-08-22 00:00:00 | 13788736655 | 开封市    |
| S2031 | 李明博    | 女     | 1999-10-26 00:00:00 | 13643732222 | 郑州市    |
| S2032 | 达芬奇    | 男     | 1999-12-31 00:00:00 | 13043731234 | 郑州市    |
+-------+-----------+--------+---------------------+-------------+-----------+
11 rows in set (0.00 sec)

三、表子查询

表子查询结果是一张临时表（多行多列），一般作为主查询的数据源。在 MySQL 数据库中，如果子查询是一张表，并且作为外部查询的数据源放在 from 后面，必须为子查询指定别名。

例如：有以下两张表。

/*
-- 创建父表 t51，d_id 为主键
create table department(d_id int primary key,d_name char(20)
);insert into department values(11,'销售部'),(12,'后勤部'),(13,'生产部'),(14,'研发部');-- 创建子表 t52，d_id 为外键，和父表的 d_id 对应
create table employee(e_id int primary key,e_name char(20),salary int,d_id int,foreign key(d_id) references department(d_id)
);insert into employee values(11001,'张云',4500,11),(11002,'刘涛',4800,11),(11003,'王菲',5200,11);
insert into employee values(12001,'刘静',6200,12),(12002,'张红',5100,12);
insert into employee values(12003,'陈刚',6800,12),(12004,'李中华',4200,12);
insert into employee values(13001,'周涛',7400,13),(13002,'张强',6800,13);
insert into employee values(13003,'周龙',6250,13),(13004,'张中伟',4520,13);
*/
mysql> select * from department;
+------+-----------+
| d_id | d_name    |
+------+-----------+
|   11 | 销售部    |
|   12 | 后勤部    |
|   13 | 生产部    |
|   14 | 研发部    |
+------+-----------+
4 rows in set (0.01 sec)mysql> select * from employee;
+-------+-----------+--------+------+
| e_id  | e_name    | salary | d_id |
+-------+-----------+--------+------+
| 11001 | 张云      |   4500 |   11 |
| 11002 | 刘涛      |   4800 |   11 |
| 11003 | 王菲      |   5200 |   11 |
| 12001 | 刘静      |   6200 |   12 |
| 12002 | 张红      |   5100 |   12 |
| 12003 | 陈刚      |   6800 |   12 |
| 12004 | 李中华    |   4200 |   12 |
| 13001 | 周涛      |   7400 |   13 |
| 13002 | 张强      |   6800 |   13 |
| 13003 | 周龙      |   6250 |   13 |
| 13004 | 张中伟    |   4520 |   13 |
+-------+-----------+--------+------+
11 rows in set (0.00 sec)

查询比本部门平均工资高的员工的信息，SQL 命令如下：

/*
SELECT e.*, d.salary_avg FROM employee e JOIN 
(SELECT d_id,avg(salary) salary_avg FROM employee GROUP BY d_id) d 
ON e.d_id = d.d_id
WHERE e.salary > d.salary_avg;
*/
mysql> SELECT e.*, d.salary_avg FROM employee e JOIN -> (SELECT d_id,avg(salary) salary_avg FROM employee GROUP BY d_id) d -> ON e.d_id = d.d_id-> WHERE e.salary > d.salary_avg;
+-------+--------+--------+------+------------+
| e_id  | e_name | salary | d_id | salary_avg |
+-------+--------+--------+------+------------+
| 11003 | 王菲   |   5200 |   11 |  4833.3333 |
| 12001 | 刘静   |   6200 |   12 |  5575.0000 |
| 12003 | 陈刚   |   6800 |   12 |  5575.0000 |
| 13001 | 周涛   |   7400 |   13 |  6242.5000 |
| 13002 | 张强   |   6800 |   13 |  6242.5000 |
| 13003 | 周龙   |   6250 |   13 |  6242.5000 |
+-------+--------+--------+------+------------+
6 rows in set (0.00 sec)

四、EXISTS 子查询

EXISTS 子查询根据子查询是否有返回值决定主查询的返回结果。

例如：根据上面的 department 和 employee 两张表。

（1）查询有员工的部门信息

/*
select * from department where exists
(select 1 from employee where d_id = department.d_id);
*/
mysql> select * from department where exists-> (select 1 from employee where d_id = department.d_id);
+------+-----------+
| d_id | d_name    |
+------+-----------+
|   11 | 销售部    |
|   12 | 后勤部    |
|   13 | 生产部    |
+------+-----------+
3 rows in set (0.00 sec)

（2）查询没有员工的部门信息

/*
select * from department where not exists
(select 1 from employee where d_id = department.d_id);
*/
mysql> select * from department where not exists-> (select 1 from employee where d_id = department.d_id);
+------+-----------+
| d_id | d_name    |
+------+-----------+
|   14 | 研发部    |
+------+-----------+
1 row in set (0.00 sec)