MySQL-05-子查询

子查询

子查询也叫 嵌套查询，就是在 SQL 语句中，部分值是从查询语句中返回的。子查询的语句需要在 () 中写出来，然后会替换查询返回的结果：

比如：查询工资大于 Abel 的人：

SELECT last_name, salary 
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = "Abel" );

子查询位置

子查询 除了 在 GROUP BY（接收参数是当前表的列名，用来提取数据分组）和 LIMIT（只接收数字）无法使用子查询以外，其他地方都可以用子查询返回的内容作为参数。

SELECT 每一行的子查询返回的单行内容可以作为新的一列的数据
FROM/JOIN 子查询返回内容可以作为一个表（需要起别名）
WHERE 可以
GROUP BY 不可以
HAVING 可以
ORDER BY 接收的是每一行的排序依据，可以从子查询中获取
LIMIT 不可以

SELECT 子查询

SELECT 中，每一行的子查询返回的单行内容可以作为新列的数据。

SELECT e1.employee_id, (
			SELECT e2.last_name
			FROM employees AS e2 
			WHERE e2.employee_id = e1.employee_id) AS e2_name
FROM employees AS e1;

结果：

employee_id	e2_name -- 新的一列数据是从子查询中获取的。
206	Gietz
205	Higgins
204	Baer
203	Mavris

FROM / JOIN 子查询

除了多行操作符，还有一个关键词也是操作多行数据的，就是 FROM JOIN，也就是说，多行子查询的结果可以被当做一张新的表来使用（必须要给这个新的表一个别名）：

比如：查询部门最低的平均工资：

-- 只需要在一个平均工资的表里进行 MIN() 查询即可
SELECT MIN(avg_salary) 
FROM (SELECT AVG(salary) AS avg_salary
      FROM employees
      WHERE department_id IS NOT NULL
      GROUP BY department_id) AS avg_table; -- 必须给一个别名

WHERE/HAVING 子查询

• 两者一模一样。

题目：查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

ORDER BY 子查询

ORDER BY 需要的参数是行数据的排序依据，可以是当前表某一列数据，也可以是子查询获得的数据（因为排序依据是和行数据有一定关系的，所以 ORDER BY 的都是相关子查询）：

比如：查询员工的 id，salary，按照 department_name 排序：

SELECT employee_id, salary
FROM employees emp
ORDER BY ( -- 每一行数据都会子查询出自己部门的名字，作为 ORDER BY 排序这些行的依据
	  SELECT department_name 
	  FROM departments dep 
	  WHERE emp.department_id = dep.department_id);

CASE 子查询

• WHEN 中使用子查询，比较操作和 WHERE 一模一样。也可以判断是否有查询结果，有就是 TRUE。

SELECT CASE WHEN (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas') THEN "有 Haas"
                 ELSE "没有 Hass" END
FROM DUAL;

子查询没有返回数据，则被 WHEN 判定为 FASLE。

子查询分类

依据返回结果行数和子查询和主查询是否有关系进行分类：

返回结果行数

• 按内查询的结果返回一行还是多行记录，将子查询分为单行子查询、多行子查询。

单行子查询：查询返回一行数据。
多行子查询：查询返回多行数据。

主子查询关系

• 按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

相关子查询：子查询和主查询的行数据相关，会随着主查询行数据的变化，子查询的结果也不一样，会进行多次子查询。eg：查询工资大于部门平均工资的，每个人的部门可能不一样。对于子查询来说，部门名就是和主查询的相关点，每一个员工都会执行一次子查询。这就是多次执行的相关子查询。

不相关子查询：子查询和主查询的行数据无关，返回的内容是固定值，只会执行一次。eg：查询工资大于公司平均工资的员工。公司平均工资是固定的，和员工变量无关，只用执行一次即可。

行数子查询

单行子查询

因为单行子查询结果返回一行，所以对结果的操作也应该是 单行操作符：

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

单行单列数据

指的是返回数据只有一个，可以直接进行比较：

比如：查询工资大于 Abel 的人：

SELECT last_name, salary 
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = "Abel" );

单行多列数据

指的是返回了一行多列数据，需要借用 () 将列名代表的数据组成数据对，的和返回的一行多列数据比较。

比如 查询 141 号员工的 manager_id 和 department_id 相同的员工的信息，这两个数据组成的数据对可用 () 为一个整体和返回的单行多列数据进行比较，进行比较：

SELECT last_name, employee_id
FROM employees
WHERE (manager_id, department_id) = (
    SELECT manager_id, department_id 
    FROM employees
    WHERE employee_id = 141);

多行子查询

重要提醒：

1. 多行子查询的结果是多行数据，一定要思考里面掺杂有 NULL 数据之后的运算逻辑，如果不确定就过滤掉 NULL 数据之后再处理。
2. 多行子查询返回的数据可以看做一个新的表，给个别名就可以当做表名来使用。

---

因为要操作子查询返回的多行数据（可以看做一张表），所以需要出现多行操作符：

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较

比如：查询平均工资最低的部门 id，如果是其他数据库，可以用 MIN(AVG(salary)) 进行嵌套聚合函数，进行双重分组查询即可，但是 MySQL 不支持，需要进行子查询：

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) <= ALL( -- 只有部门平均工资比所有其他部门都低，就是最低的。
	SELECT AVG(salary) 
	FROM employees 
	GROUP BY department_id);

有无子查询 EXISTS

如果子查询能否查到数据也可以作为判断条件，那就要有对应的关键词来转化为 TRUE 和 FASLE，它就是 EXISTS 和 NOT EXISTS。

如果 EXISTS 后面的子查询返回了结果，那么表示为 TRUE。

比如，使用 EXISTS 查询公司管理者的 ID 信息。（用子查询看表里面有没有把自己的 ID 作为上司 ID 的）

SELECT e1.employee_id 
FROM employees e1
WHERE EXISTS (SELECT * 
	      FROM employees e2
	      WHERE e1.employee_id = e2.manager_id );

关系查询

非相关子查询

不相关子查询：子查询和主查询的行数据无关，返回的内容是固定值，只会执行一次。eg：查询工资大于公司平均工资的员工。公司平均工资是固定的，和员工变量无关，只用执行一次即可。

其他例子：查询工资大于 Abel 的人（Abel 工资是固定值，不会变化，和主查询的行数据没有任何关联）：

SELECT last_name, salary 
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = "Abel" );

相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

比如，查找工资大于自己部门平均工资的人：

SELECT last_name, department_id 
FROM employees e1 -- 每换一个人都要
WHERE salary > (SELECT AVG(salary)
		FROM employees e2
		WHERE e1.department_id = e2.department_id); 
        -- 用到了外部表 e1，通过 department_id 关联

和增删改结合

创建

CREATE TABLE table_name as 子查询

CREATE TABLE emp1 AS SELECT * FROM employees; -- 复制 employees 表的定义和数据为 emp1

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 只复制表的定义

CREATE TABLE dept80 -- 例子
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;

插入

语法格式（不需要子查询的括号，也不需要 Values 关键字，单查询出来的表要和被插入的表列对应）：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

eg：

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

修改

关联子查询，每次修改一个列的时候，就会进行一次子查询得到修改数据，和普通的关联子查询的用法是一模一样的。

UPDATE employees e
SET department_name =  (SELECT department_name 
	                       FROM   departments d
	                       WHERE  e.department_id = d.department_id);

删除

和修改子查询一样，都是普通的关联子查询而已：

DELETE FROM table1 t1
WHERE column operator (SELECT expression
                       FROM   table2 t2
                       WHERE  t1.column = t2.column);

练习

• 查询平均工资最低的部门信息和该部门的平均工资

子查询获取平均工资最低的的 部门 id 和 平均工资，再作为一个表进行多表查询，绑定上部门信息一起展示。

SELECT dep.*, min_sal_dep.avg_salary
FROM departments AS dep 
INNER JOIN (SELECT department_id, AVG(salary) AS avg_salary
	        FROM employees
	        GROUP BY department_id
	        ORDER BY avg_salary ASC
	        LIMIT 0,1) AS min_sal_dep 
ON dep.department_id = min_sal_dep.department_id;

---

• 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

子查询套子查询，因为需要将平均工资最高的部门 和 个人具体信息 借助着 departments 表连接起来，不得不这样嵌套。

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = (SELECT manager_id 
		             FROM departments
		             WHERE department_id = (SELECT department_id
                                            FROM employees
                                            GROUP BY department_id
                                            ORDER BY AVG(salary) DESC
                                            LIMIT 0,1) );

---

• 查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

locations 和 departments 两个表，因为 country_id 可以对应多个 location_id，所以要查到一个国家下面的所有部门 location，然后在去找这个 location 里有几个部门，累加即可。

SELECT country_id
FROM locations loc 
INNER JOIN (SELECT location_id, COUNT(*) dep_num
	        FROM departments
	        GROUP BY location_id) loc_dep_num -- 获取每个 location 里有多少个 departments
ON loc.location_id = loc_dep_num.location_id
GROUP BY country_id -- 分组聚合 一个 country 下所有 location 的 departments 数量
HAVING SUM(dep_num) > 2;