【MySQL】第十二部分 子查询
12. 子查询
子查询 说白了就是嵌套查询,在一个查询语句中嵌套另外一个查询语句进行内部查询.
子查询的使用大大增强了 SELECT 查询的能力,很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算得出一个数据结果,然后与这个数据结果(可能是某个标量,也可能是某个集合)进行比较。
子查询分为 : 单行子查询、多行子查询、非相关子查询和相关子查询
12.1 单行子查询
顾名思义,子查询的结果只返回一行数据.
单行子查询比较操作符
操作符 | 含义 |
---|---|
= | 等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
<> | 不等于 |
通过例子来学习单行子查询:
例题一 : 返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
# 第一次写可以将所有的条件先进行拆分最后进行组合即可
# 1. 查询员工姓名,job_id和工资
SELECT last_name,job_id,salary
FROM employees
# 2. 查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
# 3. 143号员工的薪资
SELECT salary
FROM employees
WHERE employee_id = 143
# 最后组合
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
)
AND
salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);
例题二 : 返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
例题三 : 查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
SELECT manager_id
FROM employees
WHERE employee_id = 141
)
AND department_id = (
SELECT department_id
FROM employees
WHERE employee_id = 141
)
例题四 : 查询大于50号部门最低工资的其他部门id和其最低工资
# 在HAVING中也可以使用子查询
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
例题五 : 显示员工的employee_id,last_name和location(别名)。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’
# 子查询也可以在CASE中使用
SELECT employee_id,last_name,(
CASE department_id WHEN (
SELECT department_id
FROM departments
WHERE location_id = 1800
) THEN 'Canada'
ELSE 'USA' END
) "location"
FROM employees
12.2 多行子查询
顾名思义,子查询的结果返回多行数据
单行子查询比较操作符
操作符 | 含义 |
---|---|
IN | 等于列表中的任意一个 |
ANY | 需要和单行比较操作符一起使用,和子查询返回的某一个值比较 |
ALL | 需要和单行比较操作符一起使用,和子查询返回的所有值比较 |
还是使用举例子来学习:
例题一 :返回其它job_id中比job_id为‘IT_PROG’任一工资低的员工的员工号、姓名、job_id 以及salary
# 分析: 比job_id为‘IT_PROG’任一工资低,那等同于比该工种的最高工资低就行
# 方法一:
SELECT employee_id,last_name,job_id,salary
from employees
WHERE job_id != 'IT_PROG'
AND salary < (
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
);
# 方法二:
SELECT employee_id,last_name,job_id,salary
from employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
例题二 :返回其它job_id中比job_id为‘IT_PROG’所有工资都低的员工的员工号、姓名、job_id以及salary
# 分析:job_id为‘IT_PROG’所有工资都低的员工,那等同于比该工种的最低工资低就行
# 方法一:
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < (
SELECT MIN(salary)
FROM employees
WHERE job_id = 'IT_PROG'
);
# 方法二:
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
例题三 :查询各部门之间平均工资最低的部门id
# 分析:先算出各部门的平均工资,再算出最低的平均工资,最后判断他们是不是相等
# 方法一:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_salary)
FROM ( # 这里的操作是将算出来的各部门的平均工资当成一张表,因为聚合函数不能进行嵌套
SELECT AVG(salary) avg_salary
FROM employees
GROUP BY department_id
) t_avg_salary
)
# 方法二:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
例题四 :查询与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 ( # 返回的数据是多行,所以使用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)
);
12.3 非相关子查询和相关子查询
上述所举的例子都是非相关子查询,那么什么是非相关子查询?
非相关子查询: 子查询执行的时候不依赖外部查询
相关子查询:查询的执行依赖于外部查询,一般就是子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次。
还是通过例子说明:
例题一:查询员工中工资大于本部门平均工资的员工的last_name,salary和department_id
# 分析 :本部门是动态的,根据当前员工进行变化的,当执行一次外查询,子查询都要重新计算一次
# 方法一:
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
);
# 方法二:
# 在from中使用子查询
# 将算出来的各个部门的平均工资当成一张表
SELECT e1.last_name,salary,e1.department_id
FROM employees e1, (
SELECT department_id,AVG(salary) "avg_salary"
FROM employees
GROUP BY department_id
) t_avg
WHERE e1.department_id = t_avg.department_id
AND e1.salary > t_avg.avg_salary;
例题二: 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.department_id = d.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 j
WHERE e.employee_id = j.employee_id
)
12.4 EXISTS 与 NOT EXISTS关键字
相关子查询通常也会和 EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行:条件返回 FALSE,继续在子查询中查找,反之如果在子查询中存在满足条件的行:条件返回 TRUE,不在子查询中继续查找
NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE
例题一 : 查询公司管理者的employee_id,last_name,job_id,department_id信息
# 方法一:
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e1.employee_id = e2.manager_id
)
# 方法二:
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL
)
# 方法三:
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1, employees e2
WHERE e1.employee_id = e2.manager_id;
例题二: 查询departments表中,不存在于employees表中的部门的department_id和department_name
# 方法一
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
# 方法二
SELECT d.department_id ,d.department_name
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;
总结
以上就是今天要讲的内容,希望对大家有所帮助!!!