十一、子查询详解

最新推荐文章于 2024-06-25 07:55:25 发布

Amo Xiang

最新推荐文章于 2024-06-25 07:55:25 发布

阅读量1.6k

点赞数 4

分类专栏： MySQL8.0从菜鸟到大牛文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/xw1680/article/details/128748621

版权

MySQL8.0从菜鸟到大牛专栏收录该内容

25 篇文章 87 订阅

订阅专栏

文章目录

一、需求分析与问题解决
二、单行子查询
三、多行子查询
四、相关子查询
五、课后练习

前置知识：

一、数据库开发与实战专栏导学及数据库基础概念入门
 二、MySQL 介绍及 MySQL 安装与配置
 三、MySQL 数据库的基本操作
 四、MySQL 存储引擎及数据类型
 五、数据导入与基本的 SELECT 语句
 六、MySQL 数据库练习题1(包含前5章练习题目及答案)
七、MySQL 多表查询详解(附练习题及答案----超详细)
八、MySQL 常用函数汇总(1)
九、MySQL 常用函数汇总(2)
十、MySQL 聚合函数、分组查询及过滤分组

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操作符有 ANY(SOME)、ALL、IN、EXISTS。 子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如 <、<=、>、>=、!= 等。本文将介绍如何在 SELECT 语句中嵌套子查询。

一、需求分析与问题解决

1.1 实际问题

问题引入: 查询 employees 表中谁的工资比 Abel 高？

解决方案：

# 方案1:
mysql> SELECT salary FROM employees WHERE last_name='Abel';
+----------+
| salary   |
+----------+
| 11000.00 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT last_name,job_id,salary FROM employees WHERE salary>11000;
+-----------+---------+----------+
| last_name | job_id  | salary   |
+-----------+---------+----------+
| King      | AD_PRES | 24000.00 |
| Kochhar   | AD_VP   | 17000.00 |
| De Haan   | AD_VP   | 17000.00 |
| Greenberg | FI_MGR  | 12000.00 |
| Russell   | SA_MAN  | 14000.00 |
| Partners  | SA_MAN  | 13500.00 |
| Errazuriz | SA_MAN  | 12000.00 |
| Ozer      | SA_REP  | 11500.00 |
| Hartstein | MK_MAN  | 13000.00 |
| Higgins   | AC_MGR  | 12000.00 |
+-----------+---------+----------+
10 rows in set (0.00 sec)
#方案2
mysql> SELECT e1.last_name,e1.job_id,e1.salary FROM employees e1 INNER JOIN employees e2 ON e2.last_name='Abel' 
AND e1.salary>e2.salary;
+-----------+---------+----------+
| last_name | job_id  | salary   |
+-----------+---------+----------+
| King      | AD_PRES | 24000.00 |
| Kochhar   | AD_VP   | 17000.00 |
| De Haan   | AD_VP   | 17000.00 |
| Greenberg | FI_MGR  | 12000.00 |
| Russell   | SA_MAN  | 14000.00 |
| Partners  | SA_MAN  | 13500.00 |
| Errazuriz | SA_MAN  | 12000.00 |
| Ozer      | SA_REP  | 11500.00 |
| Hartstein | MK_MAN  | 13000.00 |
| Higgins   | AC_MGR  | 12000.00 |
+-----------+---------+----------+
10 rows in set (0.01 sec)
#方案3
mysql> SELECT last_name,job_id,salary FROM employees
    -> WHERE salary > (SELECT salary FROM employees WHERE last_name='Abel');
+-----------+---------+----------+
| last_name | job_id  | salary   |
+-----------+---------+----------+
| King      | AD_PRES | 24000.00 |
| Kochhar   | AD_VP   | 17000.00 |
| De Haan   | AD_VP   | 17000.00 |
| Greenberg | FI_MGR  | 12000.00 |
| Russell   | SA_MAN  | 14000.00 |
| Partners  | SA_MAN  | 13500.00 |
| Errazuriz | SA_MAN  | 12000.00 |
| Ozer      | SA_REP  | 11500.00 |
| Hartstein | MK_MAN  | 13000.00 |
| Higgins   | AC_MGR  | 12000.00 |
+-----------+---------+----------+
10 rows in set (0.00 sec)

1.2 子查询的基本使用

子查询的基本语法结构：
在这里插入图片描述
子查询(内查询) 在主查询之前一次执行完成。子查询的结果被 主查询(外查询) 使用。注意事项：

子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询

1.3 子查询的分类

分类方式1： 我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询、多行子查询。 单行子查询如下图所示：
在这里插入图片描述
多行子查询如下图所示：

我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做 不相关子查询。 同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为 相关子查询。

二、单行子查询

单行比较操作符： =,>,>=,<,<=,<>

【练习1】查询工资大于149号员工工资的员工的信息。

#查询工资大于149号员工工资的员工的信息
mysql> SELECT last_name,salary,job_id FROM employees
    -> WHERE salary > (
    -> SELECT salary FROM employees WHERE employee_id=149);
+-----------+----------+---------+
| last_name | salary   | job_id  |
+-----------+----------+---------+
| King      | 24000.00 | AD_PRES |
| Kochhar   | 17000.00 | AD_VP   |
| De Haan   | 17000.00 | AD_VP   |
| Greenberg | 12000.00 | FI_MGR  |
| Raphaely  | 11000.00 | PU_MAN  |
| Russell   | 14000.00 | SA_MAN  |
| Partners  | 13500.00 | SA_MAN  |
| Errazuriz | 12000.00 | SA_MAN  |
| Cambrault | 11000.00 | SA_MAN  |
| Ozer      | 11500.00 | SA_REP  |
| Abel      | 11000.00 | SA_REP  |
| Hartstein | 13000.00 | MK_MAN  |
| Higgins   | 12000.00 | AC_MGR  |
+-----------+----------+---------+
13 rows in set (0.00 sec)

分析如下图所示：
在这里插入图片描述
【练习2】返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

mysql> #返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
mysql> SELECT last_name,job_id,salary FROM employees
    -> WHERE job_id = (
    -> SELECT job_id FROM employees WHERE employee_id=141
    -> ) AND
    -> salary > (SELECT salary FROM employees WHERE employee_id=143);
+-------------+----------+---------+
| last_name   | job_id   | salary  |
+-------------+----------+---------+
| Nayer       | ST_CLERK | 3200.00 |
| Mikkilineni | ST_CLERK | 2700.00 |
| Bissot      | ST_CLERK | 3300.00 |
| Atkinson    | ST_CLERK | 2800.00 |
| Mallin      | ST_CLERK | 3300.00 |
| Rogers      | ST_CLERK | 2900.00 |
| Ladwig      | ST_CLERK | 3600.00 |
| Stiles      | ST_CLERK | 3200.00 |
| Seo         | ST_CLERK | 2700.00 |
| Rajs        | ST_CLERK | 3500.00 |
| Davies      | ST_CLERK | 3100.00 |
+-------------+----------+---------+
11 rows in set (0.00 sec)

【练习3】返回公司工资最少的员工的last_name,job_id和salary

mysql> #返回公司工资最少的员工的last_name,job_id和salary
mysql> SELECT last_name,job_id,salary
    -> FROM employees WHERE salary = (
    -> SELECT MIN(salary) FROM employees);
+-----------+----------+---------+
| last_name | job_id   | salary  |
+-----------+----------+---------+
| Olson     | ST_CLERK | 2100.00 |
+-----------+----------+---------+
1 row in set (0.00 sec)

【练习4】查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id。

mysql> #查询与141号员工的manager_id和department_id相同的其他员工的employee_id，
mysql> #manager_id，department_id
mysql> #方案1
mysql> SELECT employee_id,manager_id,department_id FROM employees
    -> WHERE manager_id = (
    -> SELECT manager_id FROM employees WHERE employee_id=141
    -> )
    -> AND department_id = (
    -> SELECT department_id FROM employees WHERE employee_id=141)
    -> AND employee_id != 141;
+-------------+------------+---------------+
| employee_id | manager_id | department_id |
+-------------+------------+---------------+
|         142 |        124 |            50 |
|         143 |        124 |            50 |
|         144 |        124 |            50 |
|         196 |        124 |            50 |
|         197 |        124 |            50 |
|         198 |        124 |            50 |
|         199 |        124 |            50 |
+-------------+------------+---------------+
7 rows in set (0.00 sec)

mysql> #方案2
mysql> SELECT employee_id,manager_id,department_id FROM employees
    -> WHERE (manager_id,department_id) = (
    ->   SELECT manager_id,department_id FROM employees WHERE employee_id=141)
    -> AND employee_id != 141;
+-------------+------------+---------------+
| employee_id | manager_id | department_id |
+-------------+------------+---------------+
|         142 |        124 |            50 |
|         143 |        124 |            50 |
|         144 |        124 |            50 |
|         196 |        124 |            50 |
|         197 |        124 |            50 |
|         198 |        124 |            50 |
|         199 |        124 |            50 |
+-------------+------------+---------------+
7 rows in set (0.00 sec)

【练习5】查询最低工资大于40号部门最低工资的部门id和其最低工资。

mysql> #查询最低工资大于40号部门最低工资的部门id和其最低工资。
mysql> SELECT department_id,MIN(salary)
    -> FROM employees
    -> -- WHERE department_id IS NOT NULL
    -> GROUP BY department_id
    -> HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id=40);
+---------------+-------------+
| department_id | MIN(salary) |
+---------------+-------------+
|          NULL |     7000.00 |
|            70 |    10000.00 |
|            90 |    17000.00 |
|           100 |     6900.00 |
|           110 |     8300.00 |
+---------------+-------------+
5 rows in set (0.00 sec)

【练习6】显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。

mysql> SELECT employee_id, last_name, (CASE department_id
    -> WHEN
    -> (SELECT department_id FROM departments WHERE location_id = 1800)
    -> THEN 'Canada' ELSE 'USA' END) location FROM employees LIMIT 5;
+-------------+-----------+----------+
| employee_id | last_name | location |
+-------------+-----------+----------+
|         100 | King      | USA      |
|         101 | Kochhar   | USA      |
|         102 | De Haan   | USA      |
|         103 | Hunold    | USA      |
|         104 | Ernst     | USA      |
+-------------+-----------+----------+
5 rows in set (0.00 sec)

【练习7】子查询的空值问题。

SELECT last_name, job_id FROM	employees
WHERE job_id =
#没有姓名为Haas的员工 为NULL 则外查询也为空
(SELECT job_id FROM	employees
WHERE last_name = 'Haas');

【练习8】非法使用子查询。

SELECT employee_id,last_name FROM employees
WHERE salary =
(SELECT	MIN(salary) FROM	employees GROUP BY department_id);
-- SELECT employee_id,last_name FROM employees
-- WHERE salary =
-- (SELECT	MIN(salary) FROM	employees GROUP BY department_id)
-- > Subquery returns more than 1 row
-- > 时间: 0.001s

三、多行子查询

也称为集合比较子查询，内查询返回多行，使用多行比较操作符。

in 等于列表中的任意一个
ANY  需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME 实际上是ANY的别名，作用相同，一般常使用ANY

【练习9】返回其它job_id中比job_id为"IT_PROG"部门任一工资低的员工的员工号、姓名、job_id 以及salary。

#为了显示效果 使用LIMIT关键字限制条目数
mysql> SELECT employee_id,last_name,job_id,salary
    -> FROM employees WHERE salary < ANY(SELECT salary FROM employees WHERE job_id='IT_PROG')
    -> AND job_id <> 'IT_PROG' LIMIT 5;
+-------------+-----------+------------+---------+
| employee_id | last_name | job_id     | salary  |
+-------------+-----------+------------+---------+
|         206 | Gietz     | AC_ACCOUNT | 8300.00 |
|         200 | Whalen    | AD_ASST    | 4400.00 |
|         110 | Chen      | FI_ACCOUNT | 8200.00 |
|         111 | Sciarra   | FI_ACCOUNT | 7700.00 |
|         112 | Urman     | FI_ACCOUNT | 7800.00 |
+-------------+-----------+------------+---------+
5 rows in set (0.00 sec)

如下图所示：
在这里插入图片描述
【练习10】返回其它job_id中比job_id为"IT_PROG"部门所有工资低的员工的员工号、姓名、job_id 以及salary。

mysql> SELECT employee_id,last_name,job_id,salary
    -> FROM employees WHERE salary < ALL(SELECT salary FROM employees WHERE job_id='IT_PROG')
    -> AND job_id <> 'IT_PROG' LIMIT 5;
+-------------+------------+----------+---------+
| employee_id | last_name  | job_id   | salary  |
+-------------+------------+----------+---------+
|         115 | Khoo       | PU_CLERK | 3100.00 |
|         116 | Baida      | PU_CLERK | 2900.00 |
|         117 | Tobias     | PU_CLERK | 2800.00 |
|         118 | Himuro     | PU_CLERK | 2600.00 |
|         119 | Colmenares | PU_CLERK | 2500.00 |
+-------------+------------+----------+---------+
5 rows in set (0.00 sec)

【练习11】查询平均工资最低的部门id。

mysql> #方式1
mysql> SELECT department_id FROM employees GROUP BY department_id
    -> HAVING AVG(salary) = (
    -> SELECT MIN(avg_sal) FROM
    -> (SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id) AS temp_emp);
+---------------+
| department_id |
+---------------+
|            50 |
+---------------+
1 row in set (0.00 sec)

mysql> #方式2
mysql> SELECT department_id FROM employees
    -> GROUP BY department_id HAVING AVG(salary) <= ALL (
    -> SELECT AVG(salary) avg_sal FROM employees
    -> GROUP BY department_id);
+---------------+
| department_id |
+---------------+
|            50 |
+---------------+
1 row in set (0.00 sec)

【练习12】空值问题。

mysql> SELECT last_name FROM employees
    -> WHERE employee_id NOT IN (
    -> SELECT manager_id FROM employees
    -> );
Empty set (0.00 sec)

#字句中有NULL
mysql> SELECT manager_id FROM employees;
+------------+
| manager_id |
+------------+
|       NULL |  
|        100 |

四、相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了 条件关联， 因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询。 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

子查询中使用主查询中的列，说明如下：
在这里插入图片描述
【练习13】查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id。

#方式1
mysql> SELECT last_name,salary,department_id
    -> FROM employees `outer`
    -> WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id=`outer`.department_id) LIMIT 5;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| King      | 24000.00 |            90 |
| Hunold    |  9000.00 |            60 |
| Ernst     |  6000.00 |            60 |
| Greenberg | 12000.00 |           100 |
| Faviet    |  9000.00 |           100 |
+-----------+----------+---------------+
5 rows in set (0.00 sec)
#方式2
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id AND e2.dept_avg_sal < e1.`salary`;

【练习14】查询员工的id,salary,按照department_name 排序。

mysql> SELECT employee_id,salary FROM employees e
    -> ORDER BY (
    -> SELECT department_name FROM departments d
    -> WHERE e.`department_id` = d.`department_id`
    -> ) LIMIT 5;
+-------------+----------+
| employee_id | salary   |
+-------------+----------+
|         178 |  7000.00 |
|         206 |  8300.00 |
|         205 | 12000.00 |
|         200 |  4400.00 |
|         101 | 17000.00 |
+-------------+----------+
5 rows in set (0.00 sec)

【练习15】若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id。

mysql> SELECT e.employee_id, last_name,e.job_id FROM employees e
    -> WHERE 2 <= (SELECT COUNT(*) FROM
    -> job_history WHERE employee_id = e.employee_id);
+-------------+-----------+---------+
| employee_id | last_name | job_id  |
+-------------+-----------+---------+
|         101 | Kochhar   | AD_VP   |
|         176 | Taylor    | SA_REP  |
|         200 | Whalen    | AD_ASST |
+-------------+-----------+---------+
3 rows in set (0.00 sec)

关联子查询通常也会和 EXISTS 操作符一起来使用，用来检查在子查询中是否存在满足条件的行。如果在子查询中不存在满足条件的行：条件返回 FALSE，继续在子查询中查找。如果在子查询中存在满足条件的行：不在子查询中继续查找，条件返回 TRUE。NOT EXISTS 关键字表示如果不存在某种条件，则返回 TRUE， 否则返回 FALSE。
【练习16】查询公司管理者的employee_id，last_name，job_id，department_id信息。

#方式1
SELECT employee_id, last_name, job_id, department_id FROM	employees e1
WHERE EXISTS ( SELECT *
FROM	employees e2 WHERE e2.manager_id =
e1.employee_id);
#方式2
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id 
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
#方式3
-- WHERE employee_id IN (
-- SELECT DISTINCT manager_id FROM employees);

【练习17】查询departments表中，不存在于employees表中的部门的department_id和department_name。

SELECT department_id, department_name FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM	employees
WHERE department_id = d.department_id) LIMIT 5;

备注：相关更新和相关删除在在讲解表数据更新及删除之后再进行演示。

问题？谁的工资比Abel的高？

#方式1：自连接
SELECT e2.last_name,e2.salary 
FROM employees e1,employees e2 
WHERE e1.last_name = 'Abel' AND e1.`salary` < e2.`salary`
#方式2：子查询
SELECT last_name,salary FROM employees
	WHERE salary > (
	SELECT salary FROM employees
	WHERE last_name = 'Abel'
);

问题：以上两种方式有好坏之分吗？

解答：自连接方式好！题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。可以这样理解：子查询实际上是通过 未知表 进行查询后的条件判断，而自连接是通过已知的 自身数据表 进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

五、课后练习

查询和 Zlotkey 相同部门的员工姓名和工资。
查询工资比公司平均工资高的员工的员工号，姓名和工资。
选择工资大于所有 JOB_ID = 'SA_MAN' 的员工的工资的员工的 last_name, job_id, salary。
查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名。
查询在部门的 location_id 为 1700 的部门工作的员工的员工号。
查询管理者是 King 的员工姓名和工资。
查询工资最低的员工信息: last_name, salary。
查询平均工资最低的部门信息。
查询平均工资最低的部门信息和该部门的平均工资。
查询平均工资最高的 job 信息。
查询平均工资高于公司平均工资的部门有哪些?
查询出公司中所有 manager 的详细信息。
各个部门中最高工资中最低的那个部门的最低工资是多少?
查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary。
查询部门的部门号，其中不包括 job_id 是 ST_CLERK 的部门号。
选择所有没有管理者的员工的 last_name。
查询员工号、姓名、雇用时间、工资，其中员工的管理者为 De Haan。
查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资。
查询每个部门下的部门人数大于 5 的部门名称。
查询每个国家下的部门个数大于 2 的国家编号。

至此今天的学习就到此结束了，笔者在这里声明，笔者写文章只是为了学习交流，以及让更多学习数据库的读者少走一些弯路，节省时间，并不用做其他用途，如有侵权，联系博主删除即可。感谢您阅读本篇博文，希望本文能成为您编程路上的领航者。祝您阅读愉快！

在这里插入图片描述

好书不厌读百回，熟读课思子自知。而我想要成为全场最靓的仔，就必须坚持通过学习来获取更多知识，用知识改变命运，用博客见证成长，用行动证明我在努力。
如果我的博客对你有帮助、如果你喜欢我的博客内容，请 点赞、评论、收藏 一键三连哦！听说点赞的人运气不会太差，每一天都会元气满满呦！如果实在要白嫖的话，那祝你开心每一天，欢迎常来我博客看看。
编码不易，大家的支持就是我坚持下去的动力。点赞后不要忘了 关注 我哦！

Amo Xiang

关注

4
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
0
评论
十一、子查询详解

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操作符有 ANY(SOME)、ALL、IN、EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如 =、!= 等。本文将介绍如何在 SELECT 语句中嵌套子查询。
复制链接

扫一扫