前置知识:
一、数据库开发与实战专栏导学及数据库基础概念入门
二、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
的国家编号。
至此今天的学习就到此结束了,笔者在这里声明,笔者写文章只是为了学习交流,以及让更多学习数据库的读者少走一些弯路,节省时间,并不用做其他用途,如有侵权,联系博主删除即可。感谢您阅读本篇博文,希望本文能成为您编程路上的领航者。祝您阅读愉快!
好书不厌读百回,熟读课思子自知。而我想要成为全场最靓的仔,就必须坚持通过学习来获取更多知识,用知识改变命运,用博客见证成长,用行动证明我在努力。
如果我的博客对你有帮助、如果你喜欢我的博客内容,请点赞
、评论
、收藏
一键三连哦!听说点赞的人运气不会太差,每一天都会元气满满呦!如果实在要白嫖的话,那祝你开心每一天,欢迎常来我博客看看。
编码不易,大家的支持就是我坚持下去的动力。点赞后不要忘了关注
我哦!