MySQL-09.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL4.1开始引入。
SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算出一个数据结果，然后与这个数据结果(可能是单行，也可能是多行)进行比较。
1.需求分析与问题解决
1.1 实际问题
查询出employees表中，比Abel工资高的员工信息。
-- 方式1多表查询 自连接
SELECT
e2.`last_name`,
e2.`salary`
FROM
employees e1,
employees e2
WHERE e2.`salary` > e1.`salary`
AND e1.`last_name` = 'Abel';-- 相当于自连接e1表(一张只有一行数据的employees表)
-- 方式2 子查询
SELECT
last_name,
salary
FROM
employees
WHERE salary >
(SELECT
salary
FROM
employees
WHERE last_name = 'Abel');
1.2 子查询的基本使用
- 子查询的基本语法结构。
SELECT select_list
FROM table_name
WHERE expr operator
(SELECT select_list
FROM table_name
);
-
子查询(内查询)在主查询之前一次执行完成。
-
子查询的结果被主查询(外查询)使用。
-
注意事项
- 子查询要包含在括号内。
- 将子查询放在比较条件的右侧。
- 单行操作符对于单行子查询，多行操作符对于多行子查询。
1.3 子查询的分类
分类方式1:
按照内查询的结果返回一条还是多条记录，将子查询分为单行子查询，多行子查询。
分类方式2:
我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。
如果，子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的方式，就称为相关子查询。
2.单行子查询
2.1 单行比较操作符
2.2 代码示例
题目：查询工资大于149号员工工资的员工的信息
mysql> SELECT last_name,employee_id,salary
-> FROM employees
-> WHERE salary > (
-> SELECT salary FROM employees WHERE employee_id = 149
-> );
+-----------+-------------+----------+
| last_name | employee_id | salary |
+-----------+-------------+----------+
| King | 100 | 24000.00 |
| Kochhar | 101 | 17000.00 |
| De Haan | 102 | 17000.00 |
...
| Hartstein | 201 | 13000.00 |
| Higgins | 205 | 12000.00 |
+-----------+-------------+----------+
13 rows in set (0.00 sec)
题目: 返回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 = 149)
-> AND salary > (SELECT salary FROM employees WHERE employee_id = 143);
+-----------+--------+----------+
| last_name | job_id | salary |
+-----------+--------+----------+
| Russell | SA_MAN | 14000.00 |
| Partners | SA_MAN | 13500.00 |
| Errazuriz | SA_MAN | 12000.00 |
| Cambrault | SA_MAN | 11000.00 |
| Zlotkey | SA_MAN | 10500.00 |
+-----------+--------+----------+
5 rows in set (0.00 sec)
题目 :返回公司工资最少的员工的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)
扩展,如何求出工资倒数第二的工资的员工的last_name,job_id和salary
mysql> SELECT last_name,job_id,salary
-> FROM employees
-> WHERE salary = (SELECT MIN(salary) FROM employees WHERE salary != (SELECT MIN(salary) FROM employees));
+------------+----------+---------+
| last_name | job_id | salary |
+------------+----------+---------+
| Markle | ST_CLERK | 2200.00 |
| Philtanker | ST_CLERK | 2200.00 |
+------------+----------+---------+
2 rows in set (0.00 sec)
来源，这是我再leetcode刷到过的sql的中等题目
思路解析，WHERE salary != (SELECT MIN(salary) FROM employees) 就是，过滤掉倒数第一的工资，排除倒数第一的工资的倒数第一的工资就是倒数第二，当时觉得这个思路，特别好，记忆比较深刻。
题目，查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
题目 其实是多行子查询(返回结果是多行的)
不成对比较
mysql> SELECT employee_id,manager_id,department_id
-> FROM employees
-> WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (141,174))
-> AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (141,174))
-> AND employee_id NOT IN(141,174);
+-------------+------------+---------------+
| 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 |
| 175 | 149 | 80 |
| 176 | 149 | 80 |
| 177 | 149 | 80 |
| 179 | 149 | 80 |
+-------------+------------+---------------+
11 rows in set (0.00 sec)
成对比较(使用的条件很苛刻，用的很少)
mysql> SELECT employee_id,manager_id,department_id
-> FROM employees
-> WHERE (manager_id,department_id) IN (SELECT manager_id,department_id FROM employees WHERE employee_id IN (141,174))
-> AND employee_id NOT IN(141,174);
+-------------+------------+---------------+
| 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 |
| 175 | 149 | 80 |
| 176 | 149 | 80 |
| 177 | 149 | 80 |
| 179 | 149 | 80 |
+-------------+------------+---------------+
11 rows in set (0.00 sec)
2.3 HAVING中的子查询
- 首先执行子查询
- 向主查询中的HAVING子句返回结果
题目 查询最低工资大于50号部门最低工资的部门id和其最低工资
mysql> SELECT department_id,MIN(salary)
-> FROM employees
-> GROUP BY department_id
-> HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);
+---------------+-------------+
| department_id | MIN(salary) |
+---------------+-------------+
| NULL | 7000.00 |
| 10 | 4400.00 |
| 20 | 6000.00 |
| 30 | 2500.00 |
| 40 | 6500.00 |
| 60 | 4200.00 |
| 70 | 10000.00 |
| 80 | 6100.00 |
| 90 | 17000.00 |
| 100 | 6900.00 |
| 110 | 8300.00 |
+---------------+-------------+
11 rows in set (0.00 sec)
2.4 CASE中的子查询
题目：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT
employee_id,
last_name,
(
CASE
department_id
WHEN
(SELECT
department_id
FROM
departments
WHERE location_id = 1800)
THEN 'Canadad'
ELSE 'USA'
END
) AS 'location'
FROM
employees
2.5 子查询中的空值现象
出现原因是，子查询中为查到数据，也可能是子查询中查到的数据中包含null(多行子查询中出现空值现象的原因)
mysql> SELECT last_name,salary,job_id
-> FROM employees
-> WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'wind');
Empty set (0.00 sec)
2.6 非法使用子查询
mysql> SELECT last_name,salary,job_id
-> FROM employees
-> WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);
ERROR 1242 (21000): Subquery returns more than 1 row
多行子查询使用单行比较符
3.多行子查询
- 也称为集合比较子查询
- 子查询返回多行
- 使用多行比较操作符
3.1 多行比较操作符
3.2 代码示例
题目 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
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';
+-------------+-------------+------------+---------+
| employee_id | last_name | job_id | salary |
+-------------+-------------+------------+---------+
...
| 202 | Fay | MK_REP | 6000.00 |
| 203 | Mavris | HR_REP | 6500.00 |
| 206 | Gietz | AC_ACCOUNT | 8300.00 |
+-------------+-------------+------------+---------+
76 rows in set (0.00 sec)
-- 上述sql语句也可以转化为下面这条sql语句，是因为比任一工资低的条件，可以转化为比最大值低即可
-- 这是因为比除最大值外的值低的条件，会隐形的包含在比最大值低的情况下
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
题目：返回其它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';
+-------------+-------------+----------+---------+
| 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 |
...
| 197 | Feeney | SH_CLERK | 3000.00 |
| 198 | OConnell | SH_CLERK | 2600.00 |
| 199 | Grant | SH_CLERK | 2600.00 |
+-------------+-------------+----------+---------+
44 rows in set (0.00 sec)
-- 上述sql语句也可以转化成下面这条SQL语句，是因为比所有的工资都低的员工，可以转成比最小值低即可
-- 这是因为比除最小值的值低的条件，会隐形的包含在比最小值低的情况下
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (SELECT MIN(salary) FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
题目：查询平均工资最低的部门id
思路，按照部门id分类，查出各部门的平均工资，然后找出小于等于所有部门平均工资的平均工资对于的部门id。
mysql> SELECT
-> department_id
-> FROM
-> employees
-> GROUP BY department_id
-> HAVING AVG(salary) <= ALL
-> (SELECT
-> AVG(salary)
-> FROM
-> employees
-> GROUP BY department_id);
+---------------+
| department_id |
+---------------+
| 50 |
+---------------+
1 row in set (0.00 sec)
-- 上述sql可以转化为下面SQL语句
-- 思路，找出最小的平均工资对应的部门
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_salary)
FROM (
SELECT AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) t
);
注意，在MYSQL中聚合函数不能嵌套使用。
mysql> SELECT
-> MIN(AVG(salary))
-> FROM
-> employees
-> GROUP BY department_id;
ERROR 1111 (HY000): Invalid use of group function
3.3 空值问题
mysql> SELECT employee_id
-> FROM employees
-> WHERE employee_id NOT IN(
-> SELECT manager_id
-> FROM employees
-> );
Empty set (0.00 sec)
-- sql分析，原本是想获取不在管理者中的员工信息，但是因为子查询中存在null值，导致整个查询为空
SELECT employee_id,last_name
FROM employees
WHERE employee_id NOT IN(
SELECT manager_id
FROM employees
);
-- 获取不在管理者中的员工信息 正确的写法
SELECT employee_id,last_name
FROM employees
WHERE employee_id NOT IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
4.相关子查询
4.1 相关子查询执行流程
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就被称为关联子查询。
相关子查询按照一行一行的顺序执行，主查询的每一行都执行一次子查询。
-- 相关子查询语法 子查询中使用主查询中的列
SELECT column1,column2,...
FROM table1 outer
WHERE column1 operator
(SELECT column1,column2
FROM table2
WHERE expr1 = outer.expr2
);
4.2 代码示例
题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
方式一 相关子查询
mysql> SELECT last_name,salary,department_id
-> FROM employees e
-> WHERE salary > (
-> SELECT AVG(salary)
-> FROM employees
-> WHERE department_id = e.`department_id`
-> GROUP BY department_id
-> );
+-----------+----------+---------------+
| last_name | salary | department_id |
+-----------+----------+---------------+
| King | 24000.00 | 90 |
| Hunold | 9000.00 | 60 |
| Ernst | 6000.00 | 60 |
| Greenberg | 12000.00 | 100 |
...
| Everett | 3900.00 | 50 |
| Hartstein | 13000.00 | 20 |
| Higgins | 12000.00 | 110 |
+-----------+----------+---------------+
38 rows in set (0.00 sec)
方式二 FROM子句中使用子查询
mysql> SELECT last_name,salary,e1.department_id
-> FROM employees e1,(SELECT AVG(salary) AS avg_sal,department_id FROM employees GROUP BY department_id) e2
-> WHERE e1.`salary` > e2.avg_sal AND e1.`department_id` = e2.department_id;
+-----------+----------+---------------+
| last_name | salary | department_id |
+-----------+----------+---------------+
| Hartstein | 13000.00 | 20 |
| Raphaely | 11000.00 | 30 |
...
| Greenberg | 12000.00 | 100 |
| Faviet | 9000.00 | 100 |
| Higgins | 12000.00 | 110 |
+-----------+----------+---------------+
38 rows in set (0.00 sec)
FROM子句中使用子查询，子查询是作为FROM的一部分，子查询要用()引起来，并且要给这个子查询起别名，把它当作一张临时表使用。
在ORDER BY中使用子查询
题目：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments
WHERE department_id = e.`department_id`
);
题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.`employee_id`
);
4.3 EXISTS与NOT EXISTS关键字
- 关联子查询通常也会和EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行
- 条件返回false
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行
- 不在子查询中继续查找
- 条件返回true
- NOT EXISTS关键字表示如果不存在返回true，存在返回false
题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
-- 方式1 相关子查询 + EXISTS关键字
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (SELECT manager_id FROM employees e2 WHERE e2.manager_id = e1.`employee_id`);
-- 方式2 自连接
-- 加DISTINCT关键字的原因，因为满足e1.employee_id = e2.manager_id的条件有很多条，而显示的又是e1表的列数据，就会导致重复的数据
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;
-- 或者这样理解，因为显示的是管理者的列信息，而一个管理者下面会有很多员工，所以满足条件的管理者就会重复出现，所以需要加DISTINCT关键字
SELECT DISTINCT manager.employee_id,manager.last_name,manager.job_id,manager.department_id
FROM employees manager JOIN employees worker
WHERE manager.employee_id = worker.manager_id;
-- 方式3
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT manager_id FROM employees);
题目：查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X' FROM employees e WHERE e.`department_id` = d.`department_id`)
4.4 相关更新
语法
UPDATE table1 alias1
SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);
使用相关子查询依据一个表中的数据更新另一个表的数据。
案例
题目：在employees中增加一个department_name字段，数据为员工对应的部门名称。
ALTER TABLE employees
MODIFY department_name VARCHAR(30)
UPDATE employees e
SET e.department_name = (SELECT department_name FROM departments d WHERE d.`department_id` = e.`department_id`);
4.5 相关删除
语法
DELETE FROM table1 alias1
WHERE column operator (
SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column
);
案例
题目：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE e.employee_id IN (
SELECT employee_id
FROM emp_history e1
WHERE e1.employee_id = e.employee_id
);
5.思考题
谁的工资比Abel的高？
-- 方式1多表查询 自连接
SELECT
e2.`last_name`,
e2.`salary`
FROM
employees e1,
employees e2
WHERE e2.`salary` > e1.`salary`
AND e1.`last_name` = 'Abel';-- 相当于自连接e1表(一张只有一行数据的employees表)
-- 方式2 子查询
SELECT
last_name,
salary
FROM
employees
WHERE salary >
(SELECT
salary
FROM
employees
WHERE last_name = 'Abel');
问题
自连接和子查询那种好？
答案
自连接更好！如果需求可以使用自连接，也可以使用子查询的情况下，建议使用自连接。原因，因为在许多DBMS的处理过程中，对于自连接的处理速度要比子查询快的多。
可以这样理解，子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此大部分的DBMS中都对自连接进行了优化。
只是为了记录自己的学习历程，且本人水平有限，不对之处，请指正。
热门相关：凤逆天下：战神杀手妃 兼职/暑假工 我寄人间 魔葫 我有一个进化点