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中都对自连接进行了优化。

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

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