MySQL---子查询
我只想卷死各位,或者被各位卷死,在此特别感谢康师傅的MySQL教程
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL 4.1开始引入。
SQL 中子查询的使用大大增强了 SELECT 查询的能力,因为很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算得出一个数据结果,然后与这个数据结果(可能是某个标量,也可能是某个集合)进行比较。
需求分析与问题解决
实际问题
- 谁的工资比Abel高?
为了解决这个问题,我们需要先查出Abel的工资是多少
1 | SELECT salary |
我们可以采用自查询的方式1
2
3
4SELECT b.last_name,b.salary
FROM employees a,employees b
WHERE a.`last_name` = 'Abel'
AND b.`salary` > a.`salary`;
子查询,在方式一的基础上,将11000替换成我们查询Abel工资的语句,并用括号包裹1
2
3
4
5
6SELECT last_name,salary
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
子查询的基本使用
- 子查询(内查询)在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
- 注意事项
- 子查询要包含在括号内
- 最好将子查询放在比较条件的右侧
- 单行操作符对应单行子查询,多行操作符对应多行子查询
1
2
3
4
5
6
7
8-- 放在左侧会影响代码可读性
SELECT last_name,salary
FROM employees
WHERE (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
) < salary;
子查询的分类
分类方式1:
- 我们按内查询的结果返回一条还是多条记录,将子查询分为
单行子查询
、多行子查询
。
- 我们按内查询的结果返回一条还是多条记录,将子查询分为
分类方式2:
- 我们按内查询是否被执行多次,将子查询划分为
相关(或关联)子查询
和不相关(或非关联)子查询
。 - 子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,那么这样的子查询叫做不相关子查询。
- 同样,如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询。
- 我们按内查询是否被执行多次,将子查询划分为
单行子查询
单行比较操作符
操作符 | 含义 |
---|---|
= | equal TO |
> | greater THAN |
>= | greater THAN OR equal TO |
< | LESS THAN |
<= | LESS THAN OR equal TO |
<> | NOT equal TO |
代码示例
题目:查询工资大于149号员工工资的员工的信息1 | SELECT last_name,salary |
1 | SELECT last_name,job_id,salary |
1 | SELECT last_name,job_id,salary |
1 | SELECT employee_id,manager_id,department_id |
1 | SELECT employee_id,manager_id,department_id |
HAVING 中的子查询
- 首先执行子查询。
- 向主查询中的HAVING 子句返回结果。
1 | SELECT department_id,MIN(salary) "min_sal" |
CASE 中的子查询
题目:显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为Canada,其余则为USA。1 | SELECT employee_id,last_name, |
子查询中的空值问题
1 | SELECT last_name, job_id |
没有叫Haas的人,子查询不返回任何行
非法使用子查询
1 | SELECT employee_id, last_name |
错误信息:Subquery returns more than 1 row
说明:括号中的子查询语句会返回每个部门的最低薪资,远不止一行
多行子查询
- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符
多行比较操作符
操作符 | 含义 |
---|---|
IN | 等于列表中的任意一个 |
ANY | 需要和单行比较操作符一起使用,和子查询返回的某一个值比较 |
ALL | 需要和单行比较操作符一起使用,和子查询返回的所有值比较 |
SOME | 实际上是ANY的别名,作用相同,一般常使用ANY |
代码示例
题目:返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary1 | SELECT employee_id,last_name,job_id,salary |
1 | SELECT employee_id,last_name,job_id,salary |
在MySQL中,聚合函数不支持嵌套,但是在Oracle可以嵌套1
2
3
4
5
6SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
空值问题
内查询中出现NULL,会影响查询结果1
2
3
4
5
6
7
8
9
10
11
12
13
14SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL -- 加一行判断,排除NULL的情况
);
相关子查询
相关子查询执行流程
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为 关联子查询
。
例如查询比Abel工资高的员工,这就不是一个相关子查询,因为我们只需要查询一次,即查询Abel的工资是多少,然后把它当成一个常量来进行比较
代码示例
题目:查询员工中工资大于本部门平均工资的员工的last_name先来回顾一下之前学的东西,如果改成查询工资大于本公司平均工资的员工,那么代码如下1
2
3
4
5
6SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
这里仍然是不相关子查询,因为内查询只查询了一次,即找到公司平均工资即可
那我们现在再来看看原题该怎么做
1 | SELECT last_name,salary,department_id |
在 FROM 中使用子查询
查询各部门id的平均工资,当做一张表,然后再多表查询1
2
3
4
5
6
7
8SELECT e.last_name,e.salary,e.department_id
FROM employees e
JOIN (SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) "t_dept_avg_sal"
ON e.`department_id` = t_dept_avg_sal.department_id
WHERE e.`salary` > t_dept_avg_sal.avg_sal;
1 | SELECT employee_id,salary |
左外连接也可以做1
2
3
4
5SELECT employee_id,salary
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
ORDER BY d.department_name;
1 | -- 每次读题都得读个半天才知道啥意思 |
EXISTS 与 NOT EXISTS关键字
- 关联子查询通常也会和 EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行:
- 条件返回 FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行:
- 不在子查询中继续查找
- 条件返回 TRUE
- NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
自连接1
2
3
4SELECT DISTINCT mng.employee_id,mng.last_name,mng.job_id,mng.department_id
FROM employees emp
JOIN employees mng
ON emp.`manager_id` = mng.`employee_id`;
子查询1
2
3
4
5
6
7SELECT DISTINCT employee_id,last_name,job_id,department_id
FROM employees e
WHERE e.`employee_id`
IN (
SELECT manager_id
FROM employees
);
1 | SELECT employee_id,last_name,job_id,department_id |
1 | SELECT d.department_id,d.department_name |
NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
不存在e表中的department_id和d表中的的department_id,则返回TRUE,该条记录也正是我们需要的1
2
3
4
5
6SELECT d.department_id,d.department_name
FROM departments d
WHERE NOT EXISTS (SELECT department_id
FROM employees e
WHERE e.`department_id` = d.`department_id`
);
相关更新
题目:在employees中增加一个department_name字段,数据为员工对应的部门名称1 | UPDATE employees e |
相关删除
删除表employees中,其与emp_history表皆有的数据1 | DELETE FROM employees e |
思考题
问题:谁的工资比Abel的高?1 | SELECT e2.last_name,e2.salary |
1 | SELECT last_name,salary |
- 以上两种方式有好坏之分吗?
- 答:自连接方式好!
题目中可以使用子查询,也可以使用自连接。一般情况建议你使用自连接,因为在许多 DBMS 的处理过程中,对于自连接的处理速度要比子查询快得多。可以这样理解:子查询实际上是通过未知表进行查询后的条件判断,而自连接是通过已知的自身数据表进行条件判断,因此在大部分 DBMS 中都对自连接处理进行了优化
课后练习
- 查询和Zlotkey相同部门的员工姓名和工资
1
2
3
4
5
6
7SELECT last_name,salary
FROM employees e
WHERE e.`department_id` = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
); - 查询工资比公司平均工资高的员工的员工号,姓名和工资。
1
2
3
4
5
6SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
); - 选择工资大于所有JOB_ID = ‘SA_MAN’的员工的工资的员工的last_name, job_id, salary
1
2
3
4
5
6
7SELECT last_name,job_id,salary
FROM employees
WHERE salary > (
SELECT MAX(salary)
FROM employees
WHERE job_id = 'SA_MAN'
);1
2
3
4
5
6
7SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE job_id = 'SA_MAN'
); 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
1
2
3
4
5
6SELECT employee_id,last_name
FROM employees a
WHERE department_id IN (SELECT DISTINCT department_id
FROM employees b
WHERE b.`last_name` LIKE "%u%"
);1
2
3
4
5
6SELECT employee_id,last_name
FROM employees a
WHERE department_id = ANY (SELECT DISTINCT department_id
FROM employees b
WHERE b.`last_name` LIKE "%u%"
);查询在部门的location_id为1700的部门工作的员工的员工号
1
2
3
4
5
6SELECT employee_id,last_name
FROM employees e
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700
);- 查询管理者是King的员工姓名和工资
1
2
3
4
5
6SELECT last_name,salary
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE last_name = 'King'
); - 查询工资最低的员工信息: last_name, salary
1
2
3
4
5SELECT last_name,salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees
); - 查询平均工资最低的部门信息
1
2
3
4
5
6
7
8
9SELECT *
FROM departments
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id
));1
2
3
4
5
6
7
8SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1) e
WHERE d.`department_id` = e.department_id; - 查询平均工资最低的部门信息和该部门的平均工资(相关子查询)
1
2
3
4
5
6
7
8SELECT *
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 1) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id; - 查询平均工资最高的 job 信息
1
2
3
4
5
6
7
8
9SELECT j.*
FROM jobs j,(
SELECT AVG(salary) avg_sal,job_id
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 1
) t_job_id_avg_sal
WHERE j.`job_id` = t_job_id_avg_sal.job_id;1
2
3
4
5
6
7
8
9
10
11
12SELECT *
FROM jobs j
WHERE j.`job_id` = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= ALL(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id
)
); - 查询平均工资高于公司平均工资的部门有哪些?
1
2
3
4
5
6
7SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary)
FROM employees
); - 查询出公司中所有 manager 的详细信息
1
2
3SELECT DISTINCT mng.*
FROM employees emp , employees mng
WHERE mng.`employee_id` = emp.`manager_id`;1
2
3
4
5SELECT *
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id
FROM employees
);1
2
3
4
5
6SELECT *
FROM employees m
WHERE EXISTS (SELECT DISTINCT *
FROM employees e
WHERE m.`employee_id` = e.`manager_id`
); - 各个部门中 最高工资中最低的那个部门的 最低工资是多少?
1
2
3
4
5
6
7
8
9SELECT employee_id,MIN(salary)
FROM employees e,(SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
) t_max_sal
WHERE e.`department_id` = t_max_sal.department_id;1
2
3
4
5
6
7
8
9SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
); 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
1
2
3
4
5
6
7
8
9SELECT *
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 1));1
2
3
4
5
6
7
8
9
10
11
12SELECT *
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= ALL (
SELECT AVG(salary)
FROM employees
GROUP BY department_id
))查询部门的部门号,其中不包括job_id是”ST_CLERK”的部门号
1
2
3
4
5
6SELECT department_id
FROM departments
WHERE department_id
NOT IN (SELECT DISTINCT department_id
FROM employees
WHERE job_id = 'ST_CLERK')- 选择所有没有管理者的员工的last_name
1
2
3SELECT last_name
FROM employees
WHERE manager_id IS NULL;1
2
3
4
5
6SELECT last_name
FROM employees e1
WHERE NOT EXISTS (
SELECT *
FROM employees e2
WHERE e1.`manager_id` = e2.`employee_id`); - 查询员工号、姓名、雇用时间、工资,其中员工的管理者为 ‘De Haan’
1
2
3
4
5SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE last_name = 'De Haan'); - 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(相关子查询)
1
2
3
4
5SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.`department_id` = e2.`department_id`); - 查询每个部门下的部门人数大于 5 的部门名称(相关子查询)
1
2
3
4
5SELECT department_id,department_name
FROM departments d
WHERE 5 < (SELECT COUNT(*)
FROM employees e
WHERE e.`department_id` = d.`department_id`); - 查询每个国家下的部门个数大于 2 的国家编号(相关子查询)
1
2
3
4
5
6
7SELECT country_id
FROM locations l
WHERE 2 < (
SELECT COUNT(*)
FROM departments d
WHERE d.`location_id` = l.`location_id`
);