MySQL讲义第 30 讲——select 查询之子查询

在一个 select 语句中嵌入另外一个 select 语句，被嵌入的 select 语句称之为子查询。子查询要么充当条件，要么充当主查询的数据源。根据子查询返回的结果可以把子查询分为以下几类： （1）标量子查询：子查询结果是一个数据（单行单列）。 （2）列子查询：子查询结果是一列（多行单列）。 （3）表子查询：子查询结果是一张临时表（多行多列）。 （4）exists 子查询：根据子查询是否有返回值决定主查询的返回结果。

一、标量子查询

标量子查询的结果是一个数据（单行单列）。一般和 =、>、<、>=、<=、!= 等操作符一起使用来构造主查询的条件。针对下面的员工（emp）表进行查询。

mysql> select * from emp; +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 1101 | 张美华 | 女 | 5000.00 | 1104 | 销售部 | | 1102 | 王涛 | 男 | 5200.00 | 1104 | 销售部 | | 1103 | 张学有 | 男 | 4700.00 | 1104 | 销售部 | | 1104 | 刘得华 | 男 | 5200.00 | 1104 | 销售部 | | 1105 | 董雯花 | 女 | 5900.00 | 1104 | 销售部 | | 1106 | 宋族营 | 男 | 6500.00 | 1104 | 销售部 | | 2201 | 李霜江 | 男 | 7200.00 | 2202 | 财务部 | | 2202 | 梁美丽 | 女 | 6400.00 | 2202 | 财务部 | | 2203 | 王大强 | 男 | 6100.00 | 2202 | 财务部 | | 3301 | 张美华 | 女 | 7800.00 | 3302 | 技术部 | | 3302 | 赵紫龙 | 男 | 6900.00 | 3302 | 技术部 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | +------+-----------+--------+---------+--------+-----------+ 13 rows in set (0.00 sec)

1、查询高于所有员工平均工资的员工信息

mysql> SELECT -> * -> FROM -> emp -> WHERE -> salary > -> (SELECT avg(salary) FROM emp); +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 2201 | 李霜江 | 男 | 7200.00 | 2202 | 财务部 | | 3301 | 张美华 | 女 | 7800.00 | 3302 | 技术部 | | 3302 | 赵紫龙 | 男 | 6900.00 | 3302 | 技术部 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | +------+-----------+--------+---------+--------+-----------+ 5 rows in set (0.02 sec)

2、查询工资比王涛的工资还低的员工信息

mysql> SELECT -> * -> FROM -> emp -> WHERE -> salary < -> (SELECT salary FROM emp WHERE e_name = '王涛'); +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 1101 | 张美华 | 女 | 5000.00 | 1104 | 销售部 | | 1103 | 张学有 | 男 | 4700.00 | 1104 | 销售部 | +------+-----------+--------+---------+--------+-----------+ 2 rows in set (0.00 sec)

3、查询工资最高的员工信息

mysql> SELECT -> * -> FROM -> emp -> WHERE -> salary = -> (SELECT min(salary) FROM emp); +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 1103 | 张学有 | 男 | 4700.00 | 1104 | 销售部 | +------+-----------+--------+---------+--------+-----------+ 1 row in set (0.02 sec)

二、列子查询

列子查询的结果是一列（多行单列），可以看成一个集合。一般搭配和 IN、ANY | SOME、ALL 等操作符一起使用。例如：

1、查询比财务部所有员工的工资都高的员工的信息

mysql> SELECT -> * -> FROM -> emp -> WHERE -> salary > ALL -> (SELECT salary FROM emp WHERE dept_name = '财务部'); +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 3301 | 张美华 | 女 | 7800.00 | 3302 | 技术部 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | +------+-----------+--------+---------+--------+-----------+ 3 rows in set (0.02 sec) --说明：以上查询等价于： mysql> SELECT -> * -> FROM -> emp -> WHERE -> salary > -> (SELECT max(salary) FROM emp WHERE dept_name = '财务部'); +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 3301 | 张美华 | 女 | 7800.00 | 3302 | 技术部 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | +------+-----------+--------+---------+--------+-----------+ 3 rows in set (0.02 sec)

2、查询比财务部任意一个员工的工资高的员工的信息

mysql> SELECT -> * -> FROM -> emp -> WHERE -> salary > ANY -> (SELECT salary FROM emp WHERE dept_name = '财务部'); +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 1106 | 宋族营 | 男 | 6500.00 | 1104 | 销售部 | | 2201 | 李霜江 | 男 | 7200.00 | 2202 | 财务部 | | 2202 | 梁美丽 | 女 | 6400.00 | 2202 | 财务部 | | 3301 | 张美华 | 女 | 7800.00 | 3302 | 技术部 | | 3302 | 赵紫龙 | 男 | 6900.00 | 3302 | 技术部 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | +------+-----------+--------+---------+--------+-----------+ 7 rows in set (0.00 sec) --说明：以上查询等价于： mysql> SELECT -> * -> FROM -> emp -> WHERE -> salary > -> (SELECT min(salary) FROM emp WHERE dept_name = '财务部'); +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 1106 | 宋族营 | 男 | 6500.00 | 1104 | 销售部 | | 2201 | 李霜江 | 男 | 7200.00 | 2202 | 财务部 | | 2202 | 梁美丽 | 女 | 6400.00 | 2202 | 财务部 | | 3301 | 张美华 | 女 | 7800.00 | 3302 | 技术部 | | 3302 | 赵紫龙 | 男 | 6900.00 | 3302 | 技术部 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | +------+-----------+--------+---------+--------+-----------+ 7 rows in set (0.00 sec)

三、表子查询

表子查询结果是一张临时表（多行多列），一般作为主查询的数据源。例如：查询比本部门平均工资高的员工的信息。

mysql> SELECT -> e.*, -> d.salary_avg -> FROM -> emp e JOIN (SELECT dept_name,avg(salary) salary_avg FROM emp GROUP BY dept_name) d -> ON e.dept_name = d.dept_name -> WHERE -> e.salary > d.salary_avg; +------+-----------+--------+---------+--------+-----------+-------------+ | e_id | e_name | gender | salary | leader | dept_name | salary_avg | +------+-----------+--------+---------+--------+-----------+-------------+ | 1105 | 董雯花 | 女 | 5900.00 | 1104 | 销售部 | 5416.666667 | | 1106 | 宋族营 | 男 | 6500.00 | 1104 | 销售部 | 5416.666667 | | 2201 | 李霜江 | 男 | 7200.00 | 2202 | 财务部 | 6566.666667 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | 8075.000000 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | 8075.000000 | +------+-----------+--------+---------+--------+-----------+-------------+ 5 rows in set (0.00 sec)

四、exists 子查询

根据子查询是否有返回值决定主查询的返回结果。例如有以下两张表：

mysql> select * from dept; +---------+--------------+ | dept_id | dept_name | +---------+--------------+ | D01 | 管理系 | | D02 | 计算机系 | | D03 | 数学系 | | D04 | 法律系 | | D05 | 艺术系 | +---------+--------------+ 5 rows in set (0.00 sec) mysql> select * from stu; +-----------+-----------+--------+---------------------+--------+-------------+---------+ | stu_id | stu_name | gender | birth | height | phone | dept_id | +-----------+-----------+--------+---------------------+--------+-------------+---------+ | 201801101 | 王占峰 | 男 | 1999-12-30 00:00:00 | 177.0 | 15937320987 | D02 | | 201801102 | 刘国强 | 男 | 2000-08-14 00:00:00 | 174.0 | 15937320789 | D02 | | 201801103 | 巩莉 | 女 | 2000-06-18 00:00:00 | 170.0 | 15937320456 | D02 | | 201801104 | 宋丹风 | 女 | 1999-11-20 00:00:00 | 165.0 | 15937320444 | D02 | | 201801201 | 王艳艳 | 女 | 1999-09-30 00:00:00 | 162.0 | NULL | NULL | | 201801202 | 赵牡丹 | 女 | 2001-08-10 00:00:00 | 160.0 | 15937320666 | D03 | | 201801203 | 王鹏飞 | 男 | 2000-10-19 00:00:00 | 174.0 | 15937320555 | D03 | | 201901002 | 王宏伟 | 男 | 2001-02-15 00:00:00 | 180.0 | 15937320255 | D01 | | 201901003 | 张静静 | 女 | 2001-08-17 00:00:00 | 167.0 | 15937320123 | D01 | | 201901004 | 李刚 | 男 | 2000-12-25 00:00:00 | 178.0 | 15937320321 | D01 | | 201901005 | 刘鹏 | 男 | 2001-12-18 00:00:00 | 176.0 | NULL | D01 | +-----------+-----------+--------+---------------------+--------+-------------+---------+ 11 rows in set (0.00 sec)

1、查询有学生的系的信息

mysql> SELECT -> * -> FROM -> dept -> WHERE -> exists (SELECT * FROM stu WHERE dept_id = dept.dept_id); +---------+--------------+ | dept_id | dept_name | +---------+--------------+ | D01 | 管理系 | | D02 | 计算机系 | | D03 | 数学系 | +---------+--------------+ 3 rows in set (0.00 sec) --说明：把每一个系的系编号带入学生表，只要查询到一条记录，就表示该系有学生。

2、查询没有学生的系的信息

mysql> SELECT -> * -> FROM -> dept -> WHERE -> not exists (SELECT * FROM stu WHERE dept_id = dept.dept_id); +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | D04 | 法律系 | | D05 | 艺术系 | +---------+-----------+ 2 rows in set (0.00 sec)