第12篇：子查询（本篇非常重要，高手必备）

star2017 1年前 ⋅ 595 阅读

打算提升sql技能的，可以加我微信itsoku，带你成为sql高手。

这是Mysql系列第12篇。

环境：mysql5.7.25，cmd命令中进行演示。

本章节非常重要。

子查询

出现在select语句中的select语句，称为子查询或内查询。

外部的select查询语句，称为主查询或外查询。

子查询分类

按结果集的行列数不同分为4种

  • 标量子查询（结果集只有一行一列）
  • 列子查询（结果集只有一列多行）
  • 行子查询（结果集有一行多列）
  • 表子查询（结果集一般为多行多列）

按子查询出现在主查询中的不同位置分

  • select后面：仅仅支持标量子查询。

  • from后面：支持表子查询。

  • where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）

  • exists后面（即相关子查询）：表子查询（多行、多列）

准备测试数据

测试数据比较多，放在我的个人博客上了。

浏览器中打开链接：http://www.itsoku.com/article/196

mysql中执行里面的javacode2018_employees库部分的脚本。

成功创建javacode2018_employees库及5张表，如下：

表名 描述
departments 部门表
employees 员工信息表
jobs 职位信息表
locations 位置表（部门表中会用到）
job_grades 薪资等级表

select后面的子查询

子查询位于select后面的，仅仅支持标量子查询。

示例1

查询每个部门员工个数

  1. SELECT
  2.   a.*,
  3.   (SELECT count(*)
  4.    FROM employees b
  5.    WHERE b.department_id = a.department_id) AS 员工个数
  6. FROM departments a;

示例2

查询员工号=102的部门名称

  1. SELECT (SELECT a.department_name
  2.         FROM departments a, employees b
  3.         WHERE a.department_id = b.department_id
  4.               AND b.employee_id = 102) AS 部门名;

from后面的子查询

将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到。

然后将真实的表和子查询结果表进行连接查询。

示例1

查询每个部门平均工资的工资等级

  1. -- 查询每个部门平均工资
  2. SELECT
  3.   department_id,
  4.   avg(a.salary)
  5. FROM employees a
  6. GROUP BY a.department_id;
  8. -- 薪资等级表
  9. SELECT *
  10. FROM job_grades;
  12. -- 将上面2个结果连接查询，筛选条件:平均工资 between lowest_sal and highest_sal;
  13. SELECT
  14.   t1.department_id,
  15.   sa AS '平均工资',
  16.   t2.grade_level
  17. FROM (SELECT
  18.         department_id,
  19.         avg(a.salary) sa
  20.       FROM employees a
  21.       GROUP BY a.department_id) t1, job_grades t2
  22. WHERE
  23.   t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;

运行最后一条结果如下：

  1. mysql> SELECT
  2.           t1.department_id,
  3.           sa AS '平均工资',
  4.           t2.grade_level
  5.         FROM (SELECT
  6.                 department_id,
  7.                 avg(a.salary) sa
  8.               FROM employees a
  9.               GROUP BY a.department_id) t1, job_grades t2
  10.         WHERE
  11.           t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
  12. +---------------+--------------+-------------+
  13. | department_id | 平均工资     | grade_level |
  14. +---------------+--------------+-------------+
  15. |          NULL |  7000.000000 | C           |
  16. |            10 |  4400.000000 | B           |
  17. |            20 |  9500.000000 | C           |
  18. |            30 |  4150.000000 | B           |
  19. |            40 |  6500.000000 | C           |
  20. |            50 |  3475.555556 | B           |
  21. |            60 |  5760.000000 | B           |
  22. |            70 | 10000.000000 | D           |
  23. |            80 |  8955.882353 | C           |
  24. |            90 | 19333.333333 | E           |
  25. |           100 |  8600.000000 | C           |
  26. |           110 | 10150.000000 | D           |
  27. +---------------+--------------+-------------+
  28. 12 rows in set (0.00 sec)

where和having后面的子查询

where或having后面，可以使用

  1. 标量子查询（单行单列行子查询）
  2. 列子查询（单列多行子查询）
  3. 行子查询（一行多列）

特点

  1. 子查询放在小括号内。
  2. 子查询一般放在条件的右侧。
  3. 标量子查询，一般搭配着单行单列操作符使用 >、<、>=、<=、=、<>、!=

  4. 列子查询，一般搭配着多行操作符使用

    in(not in)：列表中的“任意一个”

    any或者some：和子查询返回的“某一个值”比较，比如a>some(10,20,30)，a大于子查询中任意一个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。

    all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，a大于子查询中所有值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

  5. 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

mysql中的in、any、some、all

in，any，some，all分别是子查询关键词之一。

in：in常用于where表达式中，其作用是查询某个范围内的数据

any和some一样： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

下文中会经常用到这些关键字。

标量子查询

一般标量子查询，示例

查询谁的工资比Abel的高？

  1. /*①查询abel的工资【改查询是标量子查询】*/
  2. SELECT salary
  3. FROM employees
  4. WHERE last_name = 'Abel';
  6. /*②查询员工信息，满足salary>①的结果*/
  7. SELECT *
  8. FROM employees a
  9. WHERE a.salary > (SELECT salary
  10.                   FROM employees
  11.                   WHERE last_name = 'Abel');

多个标量子查询，示例

返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资

  1. /*返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资*/
  2. /*①查询141号员工的job_id*/
  3. SELECT job_id
  4. FROM employees
  5. WHERE employee_id = 141;
  6. /*②查询143好员工的salary*/
  7. SELECT salary
  8. FROM employees
  9. WHERE employee_id = 143;
  10. /*③查询员工的姓名、job_id、工资，要求job_id=① and salary>②*/
  11. SELECT
  12.   a.last_name 姓名,
  13.   a.job_id,
  14.   a.salary    工资
  15. FROM employees a
  16. WHERE a.job_id = (SELECT job_id
  17.                   FROM employees
  18.                   WHERE employee_id = 141)
  19.       AND
  20.       a.salary > (SELECT salary
  21.                   FROM employees
  22.                   WHERE employee_id = 143);

子查询+分组函数，示例

查询最低工资大于50号部门最低工资的部门id和其最低工资【having】

  1. /*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/
  2. /*①查询50号部门的最低工资*/
  3. SELECT min(salary)
  4. FROM employees
  5. WHERE department_id = 50;
  6. /*②查询每个部门的最低工资*/
  7. SELECT
  8.   min(salary),
  9.   department_id
  10. FROM employees
  11. GROUP BY department_id;
  12. /*③在②的基础上筛选，满足min(salary)>①*/
  13. SELECT
  14.   min(a.salary) minsalary,
  15.   department_id
  16. FROM employees a
  17. GROUP BY a.department_id
  18. HAVING min(a.salary) > (SELECT min(salary)
  19.                         FROM employees
  20.                         WHERE department_id = 50);

错误的标量子查询，示例

将上面的示例③中子查询语句中的min(salary)改为salary，执行效果如下：

  1. mysql> SELECT
  2.           min(a.salary) minsalary,
  3.           department_id
  4.         FROM employees a
  5.         GROUP BY a.department_id
  6.         HAVING min(a.salary) > (SELECT salary
  7.                                 FROM employees
  8.                                 WHERE department_id = 500000);
  9. ERROR 1242 (21000): Subquery returns more than 1 row

错误提示：子查询返回的结果超过了1行记录。

说明：上面的子查询只支持最多一列一行记录。

列子查询(子查询结果集一列多行)

列子查询需要搭配多行操作符使用：in(not in)、any/some、all。

为了提升效率，最好去重一下distinct关键字。

示例1

返回location_id是1400或1700的部门中的所有员工姓名

  1. /*返回location_id是1400或1700的部门中的所有员工姓名*/
  2. /*方式1*/
  3. /*①查询location_id是1400或1700的部门编号*/
  4. SELECT DISTINCT department_id
  5. FROM departments
  6. WHERE location_id IN (1400, 1700);
  8. /*②查询员工姓名，要求部门是①列表中的某一个*/
  9. SELECT a.last_name
  10. FROM employees a
  11. WHERE a.department_id IN (SELECT DISTINCT department_id
  12.                           FROM departments
  13.                           WHERE location_id IN (1400, 1700));
  15. /*方式2：使用any实现*/
  16. SELECT a.last_name
  17. FROM employees a
  18. WHERE a.department_id = ANY (SELECT DISTINCT department_id
  19.                              FROM departments
  20.                              WHERE location_id IN (1400, 1700));
  22. /*拓展，下面与not in等价*/
  23. SELECT a.last_name
  24. FROM employees a
  25. WHERE a.department_id <> ALL (SELECT DISTINCT department_id
  26.                              FROM departments
  27.                              WHERE location_id IN (1400, 1700));

示例2

返回其他工种中比job_id为’IT_PROG’工种任意工资低的员工的员工号、姓名、job_id、salary

  1. /*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*/
  2. /*①查询job_id为'IT_PROG'部门任-工资*/
  3. SELECT DISTINCT salary
  4. FROM employees
  5. WHERE job_id = 'IT_PROG';
  7. /*②查询员工号、姓名、job_id、salary，slary<①的任意一个*/
  8. SELECT
  9.   last_name,
  10.   employee_id,
  11.   job_id,
  12.   salary
  13. FROM employees
  14. WHERE salary < ANY (SELECT DISTINCT salary
  15.                     FROM employees
  16.                     WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
  18. /*或者*/
  19. SELECT
  20.   last_name,
  21.   employee_id,
  22.   job_id,
  23.   salary
  24. FROM employees
  25. WHERE salary < (SELECT max(salary)
  26.                 FROM employees
  27.                 WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

示例3

返回其他工种中比job_id为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id、salary

  1. /*返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary*/
  2. SELECT
  3.   last_name,
  4.   employee_id,
  5.   job_id,
  6.   salary
  7. FROM employees
  8. WHERE salary < ALL (SELECT DISTINCT salary
  9.                     FROM employees
  10.                     WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
  12. /*或者*/
  13. SELECT
  14.   last_name,
  15.   employee_id,
  16.   job_id,
  17.   salary
  18. FROM employees
  19. WHERE salary < (SELECT min(salary)
  20.                 FROM employees
  21.                 WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

行子查询（子查询结果集一行多列）

示例

查询员工编号最小并且工资最高的员工信息，3种方式。

  1. /*查询员工编号最小并且工资最高的员工信息*/
  2. /*①查询最小的员工编号*/
  3. SELECT min(employee_id)
  4. FROM employees;
  5. /*②查询最高工资*/
  6. SELECT max(salary)
  7. FROM employees;
  8. /*③方式1：查询员工信息*/
  9. SELECT *
  10. FROM employees a
  11. WHERE a.employee_id = (SELECT min(employee_id)
  12.                        FROM employees)
  13.       AND salary = (SELECT max(salary)
  14.                     FROM employees);
  16. /*方式2*/
  17. SELECT *
  18. FROM employees a
  19. WHERE (a.employee_id, a.salary) = (SELECT
  20.                                      min(employee_id),
  21.                                      max(salary)
  22.                                    FROM employees);
  23. /*方式3*/
  24. SELECT *
  25. FROM employees a
  26. WHERE (a.employee_id, a.salary) in (SELECT
  27.                                      min(employee_id),
  28.                                      max(salary)
  29.                                    FROM employees);

方式1比较常见，方式2、3更简洁。

exists后面（也叫做相关子查询）

  1. 语法：exists(完整的查询语句)。
  2. exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。
  3. 一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。
  4. 和前面的查询不同，这先执行主查询，然后主查询查询的结果，在根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。

示例1

简单示例

  1. mysql> SELECT exists(SELECT employee_id
  2.               FROM employees
  3.               WHERE salary = 300000) AS 'exists返回1或者0';
  4. +----------------------+
  5. | exists返回1或者0     |
  6. +----------------------+
  7. |                    0 |
  8. +----------------------+
  9. 1 row in set (0.00 sec)

示例2

查询所有员工的部门名称

  1. /*exists入门案例*/
  2. SELECT exists(SELECT employee_id
  3.               FROM employees
  4.               WHERE salary = 300000) AS 'exists返回1或者0';
  6. /*查询所有员工部门名*/
  7. SELECT department_name
  8. FROM departments a
  9. WHERE exists(SELECT 1
  10.              FROM employees b
  11.              WHERE a.department_id = b.department_id);
  13. /*使用in实现*/
  14. SELECT department_name
  15. FROM departments a
  16. WHERE a.department_id IN (SELECT department_id
  17.                           FROM employees);

示例3

查询没有员工的部门

  1. /*查询没有员工的部门*/
  2. /*exists实现*/
  3. SELECT *
  4. FROM departments a
  5. WHERE NOT exists(SELECT 1
  6.                  FROM employees b
  7.                  WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL);
  8. /*in的方式*/
  9. SELECT *
  10. FROM departments a
  11. WHERE a.department_id NOT IN (SELECT department_id
  12.                               FROM employees b
  13.                               WHERE b.department_id IS NOT NULL);

上面脚本中有b.department_id IS NOT NULL，为什么，有大坑，向下看。

NULL的大坑

示例1

使用not in的方式查询没有员工的部门，如下：

  1. SELECT *
  2. FROM departments a
  3. WHERE a.department_id NOT IN (SELECT department_id
  4.                               FROM employees b);

运行结果：

  1. mysql> SELECT *
  2.     -> FROM departments a
  3.     -> WHERE a.department_id NOT IN (SELECT department_id
  4.     ->                               FROM employees b);
  5. Empty set (0.00 sec)

not in的情况下，子查询中列的值为NULL的时候，外查询的结果为空。

建议：建表是，列不允许为空。

总结

  1. 本文中讲解了常见的子查询，请大家务必多练习
  2. 注意in、any、some、any的用法
  3. 字段值为NULL的时候，not in查询有大坑，这个要注意
  4. 建议创建表的时候，列不允许为空

Mysql系列目录

mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!

最新资料

更多内容请访问：IT源点

注意：本文归作者所有，未经作者允许，不得转载

#MySQL教程
阅读全部
相关文章推荐
  • 1
  • 2

全部评论: 0 条

    我有话说: