首页 > 基础资料博客日记

MySQL-09.子查询

2024-01-02 23:07:06基础资料围观216次

Java资料网推荐MySQL-09.子查询这篇文章给大家，欢迎收藏Java资料网享受知识的乐趣

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

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

文章来源:https://www.cnblogs.com/changming06/p/17941541
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！

标签：

上一篇：国企两年，我懂得了这些职场真理！
下一篇：Spring Boot学习随笔- 集成MyBatis-Plus（三）自定义SQL、分页实现（PaginationInterceptor ）、更新和删除详细方法

首页 > 基础资料博客日记

MySQL-09.子查询

1.需求分析与问题解决

1.1 实际问题

1.2 子查询的基本使用

1.3 子查询的分类

分类方式1:

分类方式2:

2.单行子查询

2.1 单行比较操作符

2.2 代码示例

2.3 HAVING中的子查询

2.4 CASE中的子查询

2.5 子查询中的空值现象

2.6 非法使用子查询

3.多行子查询

3.1 多行比较操作符

3.2 代码示例

3.3 空值问题

4.相关子查询

4.1 相关子查询执行流程

4.2 代码示例

4.3 EXISTS与NOT EXISTS关键字

4.4 相关更新

语法

案例

4.5 相关删除

语法

案例

5.思考题

问题

答案

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料 博客日记

MySQL-09.子查询

1.需求分析与问题解决

1.1 实际问题

1.2 子查询的基本使用

1.3 子查询的分类

分类方式1:

分类方式2:

2.单行子查询

2.1 单行比较操作符

2.2 代码示例

2.3 HAVING中的子查询

2.4 CASE中的子查询

2.5 子查询中的空值现象

2.6 非法使用子查询

3.多行子查询

3.1 多行比较操作符

3.2 代码示例

3.3 空值问题

4.相关子查询

4.1 相关子查询执行流程

4.2 代码示例

4.3 EXISTS与NOT EXISTS关键字

4.4 相关更新

语法

案例

4.5 相关删除

语法

案例

5.思考题

问题

答案

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料博客日记