十一、子查询详解

前置知识:

一、数据库开发与实战专栏导学及数据库基础概念入门
二、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,继续在子查询中查找。如果在子查询中存在满足条件的行:不在子查询中继续查找,条件返回 TRUENOT 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 中都对自连接处理进行了优化。

五、课后练习

  1. 查询和 Zlotkey 相同部门的员工姓名和工资。
  2. 查询工资比公司平均工资高的员工的员工号,姓名和工资。
  3. 选择工资大于所有 JOB_ID = 'SA_MAN' 的员工的工资的员工的 last_name, job_id, salary
  4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名。
  5. 查询在部门的 location_id1700 的部门工作的员工的员工号。
  6. 查询管理者是 King 的员工姓名和工资。
  7. 查询工资最低的员工信息: last_name, salary
  8. 查询平均工资最低的部门信息。
  9. 查询平均工资最低的部门信息和该部门的平均工资。
  10. 查询平均工资最高的 job 信息 。
  11. 查询平均工资高于公司平均工资的部门有哪些?
  12. 查询出公司中所有 manager 的详细信息。
  13. 各个部门中 最高工资中最低的那个部门的 最低工资是多少?
  14. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
  15. 查询部门的部门号,其中不包括 job_idST_CLERK 的部门号。
  16. 选择所有没有管理者的员工的 last_name
  17. 查询员工号、姓名、雇用时间、工资,其中员工的管理者为 De Haan
  18. 查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资。
  19. 查询每个部门下的部门人数大于 5 的部门名称。
  20. 查询每个国家下的部门个数大于 2 的国家编号。

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


在这里插入图片描述

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

  • 4
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Amo Xiang

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值