五、mysql子查询
参考url:https://www.bilibili.com/video/BV12b411K7Zu?p=132
含义：出现在其他语句内部的select语句，称为子查询或内查询
内部嵌套其他select语句的查询，称为主查询或外查询
示例：
SELECT first_name FROM employees
WHERE department_id in (
SELECT department_id FROM departments
WHERE location_id=1700
)
分类：
1) 按子查询出现的位置
SELECT后面：仅仅支持标量子查询
FROM 后面：支持表子查询
WHERE或HAVING后面：标量子查询、列子查询、行子查询
EXISTS后面（相关子查询）：表子查询
2) 按结果集的行列数不同
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
特点：
1) 子查询放在小括号内
2) 子查询一般放在条件的右侧
3) 标量子查询，一般搭配着单行操作符使用 < > = <= >= ,.
4) 列子查询，一般搭配着多行操作符使用 in 、all、 any/some
5) 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
1、标量子查询
案例１：谁的工资比Abel高？
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name=’Abel’
);
案例２:返回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
);
2、列子查询（多行子查询）
案例１：返回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)
);
案例２：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号，姓名，job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id=’IT_PROG’
) AND job_id <>’IT_PROG’;
案例３：返回其它工种中比job_id为‘IT_PROG’工种所有的工资低的员工的员工号，姓名，job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id=’IT_PROG’
) AND job_id <>’IT_PROG’;
3、行子查询（结果集一行多列或多行多列）
案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
或
SELECT *
FROM employees
WHERE employee_Id=(
SELECT MIN(employee)
FROM employees
) AND salary=(
SELECT MAX(salary)
FROM employees
);
4、Select后面(仅仅支持标量子查询)
案例1：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees AS e
WHERE e.department_id=d.department_id
) AS 个数
FROM departments d;
案例２：查询员工号＝102的部门名
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.departmenta_id
WHERE e.employee_id=102
) AS 部门名;
5、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;
6、Exists后面（相关子查询）
语法：exists(完整的查询语句)
结果：１或0
案例１：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id
)