【MySQL】第十二部分 子查询

【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;

---

总结

以上就是今天要讲的内容，希望对大家有所帮助！！！