MySQL-09.子查询

2024-01-02 23:03 由长名06 发表于 #后端开发

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从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中都对自连接进行了优化。

只是为了记录自己的学习历程，且本人水平有限，不对之处，请指正。

热门相关：凤逆天下：战神杀手妃兼职/暑假工我寄人间魔葫我有一个进化点