>>> 多年前的 SQL 联系笔记，现在回头看很多问题，也懒得整理了，直接搬运到 博客，仅仅是一些练习题，并非知识库 <<<
子查询是嵌套在 SQL 语句中的另一个SELECT 语句
子查询可以用在很多地方，如 where 后面 from 后面 group by 后面 order by后面、select后面等等
子查询 (内查询) 在主查询执行之前执行
主查询(外查询)使用子查询的结果：
1、问题：查询工资大于149号员工工资的员工的信息
SELECT *
FROM employees e
WHERE salary > (
SELECT salary
FROM employees
WHERE employee_id = 149
多列子查询
主查询与子查询返回的 多个列 进行比较
列比较
多列子查询中的比较分为两种:
成对比较
不成对比较
2、问题：查询与141号或174号员工的manager_id和department_id相同的 其他员工的 employee_id, manager_id, department_id
不成对查询：
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)
)
-- 上段查询的到的是所有的结果（包括了141和174号员工），其他员工则需要排除141和174号员工
AND employee_id NOT IN (141, 174);
成对查询：
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)
)
-- 上段查询的到的是所有的结果（包括了141和174号员工），其他员工则需要排除141和174号员工
AND employee_id NOT IN (141, 174);
在FROM 子句中使用子查询：
-- 查询各个部门的平均工资 SELECT AVG(salary) FROM employees GROUP BY department_id;
-- 查询各个部门的平均工资，显示部门ID和名称
SELECT d.department_id, department_name,avg(salary)
FROM employees e,
departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id;
结果如图所示，生成新的临时表：
-- 单独查询指定部门ID的部门平均工资 SELECT AVG(salary) FROM employees WHERE employees.department_id = 80; -- 或者 SELECT AVG(salary) FROM employees WHERE department_id = 80; -- 或者 SELECT AVG(salary) FROM employees e WHERE e.department_id = 80;
-- 获取各个部门中平均工资的最大值,Oracle中支持此种方式，主函数嵌套，但是MySQL中不支持 SELECT max(AVG(salary)) FROM employees GROUP BY department_id;
MySQL中不能使用主函数嵌套，解决方案，将上图查询结果的临时表当做一张表再次查询，
即将 临时表的查询语句嵌套如 下一次查询的 FROM （table）语句中，临时表也是一张表
示例一：
SELECT temp_id,temp_name,max(avg_sal)
FROM (
SELECT d.department_id temp_id, department_name temp_name, avg(salary) avg_sal
FROM employees e,
departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id
) t_avg;
示例二：
SELECT max(avg_sal)
FROM (
SELECT AVG(salary) avg_sal -- 临时表中的列必须有自己的名字
FROM employees
GROUP BY department_id
) t_avg; -- 临时表也必须有自己的名字
注意，临时表中，每个列都必须有自己的名字，临时表也必须有自己的名字（推荐使用示例二，只显示关键的列，不容易出错）
相关子查询
主查询与子查询中有相关性，子查询无法单独的完成查询，子查询中条件也需要主查询中提供
问题：返回比 本 部门平均工资高的员工的last_name, department_id, salary及平均工资
-- 方式一：（在 select 和 where 语句中使用子查询）
-- -- 难度分解，先查询比本部门平均工资高的员工的last_name,department_id, salary
SELECT last_name, department_id, salary
FROM employees e1
WHERE e1.salary >(
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
GROUP BY department_id
);
-- 子查询需要主查询提供数据，这就是相关性，相关子查询
SELECT 语句中加入子查询 均工资
(SELECT AVG(salary) FROM employees e3 WHERE e3.department_id = e1.department_id GROUP BY e3.department_id)
SELECT last_name,
department_id,
salary,
(SELECT AVG(salary)
FROM employees
WHERE department_id = e1.department_id
GROUP BY department_id) avg_Salary --可以给这个临时表价格别名，显示的时候好看一些（也可以不加）
FROM employees e1
WHERE e1.salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
GROUP BY department_id
);
-- 同样 SELECT 语句中的子查询关联条件也用到了主查询中的数据
结果如下：
方式一：（在 from 语句中使用子查询），将平均工资查询结果临时表作为一张表，这样查询更简单，普通子查询就可解决
子查询可独立完成查询
SELECT last_name,
e1.department_id,
e2.avg_salary
FROM employees e1,
(
SELECT avg(salary) avg_salary, department_id
FROM employees
GROUP BY department_id
) e2 -- 将查询平均工资的临时表作为一张表，作为 FROM 的表，注意：一定要给临时表 别名
WHERE e1.department_id = e2.department_id
AND e1.salary > e2.avg_salary;
或者：
SELECT e1.last_name,
e1.department_id,
e1.salary,
e2.avg_salary
FROM employees e1,
(SELECT avg(salary) avg_salary,
department_id
FROM employees
WHERE department_id
GROUP BY department_id
) e2 -- 将查询平均工资的临时表作为一张表，作为 FROM 的表，注意：一定要给临时表 别名
WHERE e1.department_id = e2.department_id
AND e1.salary > e2.avg_salary;
-- 显示员工的employee_id,last_name和location。其中，
-- 若员工department_id与 location_id为1800的department_id相同，则location为’Canada’,其余为’USA’
即：{department_id = （查询 department_id where条件 location_id=1800）} 为Canada，剩下的为 USA
SELECT employee_id,
last_name,
(CASE
WHEN department_id = (
SELECT department_id
FROM departments
WHERE location_id = 1800
) THEN 'CANADA'
ELSE 'USA' END) location
FROM employees;
-- 在 ORDER BY 子句中使用单列子查询
-- 问题：查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments
WHERE e.department_id = departments.department_id);
相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
子查询中使用主查询中的列
-- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE e.department_id = department_id
);
EXISTS 操作符应用举例
-- 常规方式查询,manager也是员工，也有员工id，员工的manager_id就是管理者的员工id，所以，员工id在manager_id
-- 队列里的就是管理者
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
);
方式二 ：EXISTS 关键字
EXISTS 判断返回 false 或 true，
SELECT employee_id, last_name, job_id, department_id
FROM employees e
WHERE exists( -- 子查询只负责判断true / false （底层返回true或false），单独查询子查询无结果
SELECT 'x' -- 没有具体的明确的查询设么，所以，任意字符即可，常规以 X 代替
FROM employees
WHERE manager_id = e.employee_id
);
NOT EXISTS 操作符应用举例：
-- 查询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
)
删除应用举例
如：删除数据表中重复的数据，重复的数据即：数据条数 > 1 ，count （*） > 1 ;
首先查询出重复的数据
-- 删除表employees中重复的数据,先查询出数量大于 1 的记录，按照 employee_id 删除，所以还要查询出employee_id SELECT employee_id,last_name FROM employees GROUP BY last_name HAVING count(*) > 1;
或者
-- 出现在 select 子句后的非分组函数，一定出现在 group by 子句后（MySQL中不会报错，Oracle中如下句子直接报错）
DELETE
FROM employees
WHERE employee_id IN (
SELECT employee_id
FROM ( -- 这一步重复的书写，因为MySQL只能将结果临时表当做一张表，并要求有自己的表名 e
SELECT employee_id
FROM employees
GROUP BY last_name
HAVING count(*) > 1
) e
);