MySql（12）子查询

Mysql专栏入口

链接：https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w
提取码：ttst
提取码：ttst
专栏中有mysql的导入导出，里面有教如何导入

一、子查询基础

1.1 例子

谁的工资比Abel的高

第一种方法：分开两次查

SELECT
	salary 
FROM
	employees 
WHERE
	last_name = 'Abel' 
	
SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary > 11000;

第二种方法：自连接

SELECT
	e1.salary,
	e1.employee_id 
FROM
	employees e1
	JOIN employees e2 ON e1.salary > e2.salary 
WHERE
	e2.last_name = 'Abel'

第三种就是我们今天学习的子查询

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

1.2 子查询分类

1.2.1 单行子查询 vs 多行子查询

返回结果条目只有一行的为单行子查询，返回多行的为多行子查询。

1.2.2 相关子查询 vs 不相关子查询

子查询和主查询相关联的则为相关子查询，否则为不相关子查询。
相关子查询:查询工资大于本部门平均工资的员工信息
不相关子查询:查询工资大于本公司平均工资的员工信息

二、单行子查询

查询工资大于149号员工工资的信息

SELECT
	salary,
	employee_id 
FROM
	employees 
WHERE
	salary > (
	SELECT
		salary 
	FROM
		employees 
	WHERE
	employee_id = 149)

返回job_id与141号员工相同,salary 比143号员工多的员工姓名，job_id和工资

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 
ORDER BY
	salary 
	LIMIT 1

# 子查询
SELECT
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary = (
	SELECT
		MIN( salary ) 
	FROM
	employees)

三、多行子查询

操作符	含义
IN	列表中任意一个
ANY	列表中某一个
ALL	列表中所有

返回其他job_id中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id、以及salary

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

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,
	AVG( salary ) 
FROM
	employees 
GROUP BY
	department_id 
ORDER BY
	AVG( salary ) 
	LIMIT 1

# 新
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG( salary ) = (
	SELECT MIN( avg_salary ) 
	FROM (
		SELECT AVG( salary ) avg_salary, department_id
		FROM employees 
		GROUP BY department_id
	) dept_avg_sal
)
	
# 新二
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG( salary ) <= ALL (
	SELECT AVG( salary ) 
	FROM employees 
	GROUP BY department_id 
)

四、相关子查询

查询员工中工资大于本部门平均工资的员工的信息

# 一
SELECT last_name,salary,department_id 
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE e2.department_id = e1.department_id
)

# 二
SELECT 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
ON e.department_id = t.department_id
WHERE e.salary > t.avg_sal

若 employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id

# 一、 join
SELECT
	e.employee_id,
	e.last_name,
	e.job_id
FROM
	employees e
	JOIN job_history j ON e.employee_id = j.employee_id 
GROUP BY
	e.employee_id 
HAVING
	COUNT(e.employee_id) >= 2

# 二、子查询
SELECT e.employee_id,e.last_name,e.job_id
FROM employees e
WHERE 2 <= (
	SELECT COUNT(employee_id)
	FROM job_history j
	WHERE e.employee_id = j.employee_id
)

EXISTS 与 NOT EXISTS 关键字

查询公司管理者的employee_id,last_name,job_id,department_id信息

# 一、join
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id

# 二、子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN(
	SELECT DISTINCT manager_id
	FROM employees
)

# 三、使用EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
	SELECT DISTINCT manager_id
	FROM employees e2
	WHERE e1.employee_id = e2.manager_id
)

查询departments表中，不存在于employess表中的部门的department_id和department_name

# join
SELECT d.department_id,d.department_name ,e.employee_id
FROM departments d 
LEFT JOIN employees e 
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL

# 子查询
SELECT department_id,department_name
FROM departments d 
WHERE NOT EXISTS(
	SELECT department_id
	FROM employees e
	WHERE d.department_id = e.department_id
)

五、小练习

#1．查询和 Zlotkey 相同部门的员工姓名和工资
#2．查询工资比公司平均工资高的员工的员工号，姓名和工资。
#3．选择工资大于所有 JOB _ ID =' SA _ MAN '的员工的工资的员工的 last _ name , job _ id , salary 
#4．查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
#5.査询在部门的 location _ id 为1700的部门工作的员工的员工号
#6．查询管理者是 King 的员工姓名和工资
#7．查询工资最低的员工信息： last _ name , salary 
#8．查询平均工资最低的部门信息
#9．查询平均工资最低的部门信息和该部门的平均工资（相关子査询）
#10．查询平均工资最高的 job 信息
#11．查询平均工资高于公司平均工资的部门有哪些？
#12．查询出公司中所有 manager 的详细信息
#13．各个部门中最高工资中最低的那个部门的最低工资是多少？
#14．查询平均工资最高的部门的 manager 的详细信息： last _ name , department _ id , email , salary 
#15．查询部门的部门号，其中不包括 job _ id 是＂ ST _ CLERK ＂的部门号