MySQL中查询子数据 mysql子查询详解

select 查询之子查询

文章目录

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

• 一、标量子查询

• 1、查询高于所有员工平均工资的员工信息
• 2、查询工资比王涛的工资还低的员工信息
• 3、查询工资最高的员工信息

• 二、列子查询

• 1、查询比财务部所有员工的工资都高的员工的信息
• 2、查询比财务部任意一个员工的工资高的员工的信息

• 三、表子查询
• 四、exists 子查询

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

在一个 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)