MySQL进阶7：子查询

# 进阶7：子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

示例：
select frist_name from employees where
 department_id in(
 select department_id from departments
 where location+id=17000
 )
 
分类
按子查询出现的位置：
		select 后面（只能放标量子查询）
		from 后面（只支持表子查询）
		where或having 后面（标量子查询、列子查询、行子查询）
		exists 后面（又叫相关子查询）（表子查询）
按结果集的行列数不同：
	标量子查询（结果类只有一列或一行）
	列子查询（结果集只有一列多行）
	行子查询（结果集可以有一行多列）
	表子查询（结果集，一般为多行多列）

*/

# 一、where或having后面
# 1.标量子查询（单行子查询）
# 2.列子查询（多行子查询）
# 3.行子查询（多行多列）
/*
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符使用
IN、ANY/SOME、ALL

④子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果
*/

# 1.标量子查询

# 案例1： 睡得工资比Abel高？
SELECT *
FROM employees
WHERE salary>
(
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);

# 案例2：返回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
);

# 案例3：返回公司工资最少的员工的last_name,job_id\salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

# 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT employee_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
)

# 2.列子查询
/*
返回多行
使用多行比较操作符

操作符		含义
IN/NOT IN	等于列表中的任意一个
ANY/SOME	和子查询返回的某一个值比较
ALL		和子查询返回的所有值比较


*/

# 案例1： 返回location_id是1400或1700的部门中的所有员工姓名


SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

# 案例2： 返回其他工种中比job_id为'IT_PROG'工种任一员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <>'IT_PROG';

# 或者

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
	SELECT DISTINCT MAX(salary)
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <>'IT_PROG';

# 案例3： 返回其他工种中比job_id为'IT_PROG'工种所有员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <>'IT_PROG';

# 或者

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
	SELECT DISTINCT MIN(salary)
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <>'IT_PROG';

# 3.行子查询（结果集一行多列或多行多列）

# 案例：查询员工编号最小并且工资最高的员工信息
# 以前的方法
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
) AND salary=(
	SELECT MAX(salary)
	FROM employees
);

# 行子查询(两个或多个条件的规则相同)
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

# 二、select后面
# 案例：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees AS e
	WHERE e.department_id=d.`department_id`
)
FROM departments AS d;

# 案例2： 查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments AS d
	INNER JOIN employees AS e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) AS 部门;

# 三、from后面
/*
将子查询结果做成一张表，要求该表必须要取别名
*/
# 案例：查询每个部门的平均工资的工资等级

SELECT ag_dep.*,g.`grade_level`
FROM(
	SELECT AVG(salary) AS ag,department_id
	FROM employees
	GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades AS g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

# 四、exists后面（相关子查询）
/*
语法：
exists（完整的查询语句）
结果：
1或0
*/

# 案例1：查询有员工的部门名
SELECT department_name
FROM departments AS d
WHERE EXISTS(
	SELECT *
	FROM employees AS e
	WHERE d.`department_id`=e.`department_id`
);

# 案例2：查询没有女朋友的男生信息

SELECT bo.*
FROM boys AS bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)

SELECT bo.*
FROM boys AS bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty AS b
	WHERE bo.id=b.boyfriend_id
)