ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL-09.子查询 [打印本页]

作者: 道家人 时间: 2024-3-17 14:01
标题: 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`
);

复制代码

问题

自连接和子查询那种好？
答案

自连接更好！如果需求可以使用自连接，也可以使用子查询的情况下，建议使用自连接。原因，因为在许多DBMS的处理过程中，对于自连接的处理速度要比子查询快的多。
可以这样理解，子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此大部分的DBMS中都对自连接进行了优化。
只是为了记录自己的学习历程，且本人水平有限，不对之处，请指正。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)