MySQL数据库：第九章：子查询

回退至Mysql数据库理论与实战
#子查询

概念：出现在其他语句内部的select语句，称为子查询或内查询
其他语法：可以是select语句，也可以是create、insert、update等。只是select语句出现的较多
外面如果是select语句，称为主查询或外查询
位置：
子查询可以放在select语句中的select后面、from后面、where或having后面、exists后面
分类：（放在where或having后面）
单行子查询：子查询的结果只有一行
多行子查询：子查询的结果可以有多行
特点：
①子查询一般需要使用小括号括起来，为了提高阅读性
②子查询一般放在条件的右侧
③子查询优先于主查询执行，主查询使用到了子查询的结果
④单行子查询一般搭配单行操作符使用
单行操作符：> < = <> >= <=
多行子查询一般搭配多行操作符使用
多行操作符：in/not in、any、all

#一、单行子查询

注意：
单行子查询的结果肯定是一行一列，不能是多行，也不能是空值

#案例1：谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'
#②查询哪个员工的工资>①
SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);
#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
#③查询job_id = ① 并且salary>②的员工姓名，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 MIN(salary)
FROM employees
#②查询last_name,job_id,salary，要求salary= ①
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees

);
SELECT * FROM employees;
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
#②查询每个部门的最低工资，筛选条件：哪个部门最低工资>①
SELECT MIN(salary) 最低工资,department_id
FROM employees
GROUP BY department_id
HAVING 最低工资>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);

#二、多行子查询

in/not in:判断某个字段是否属于子查询结果的某个值
any/some:一般搭配条件运算符使用 【用的较少】
where sal>any(1,2,3,4) 0.5
all: 一般搭配条件运算符使用 【用的较少】
where sal > all(1,2,3,4) 4.5

#题目：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700)
#②查询员工姓名，要求：department_id 属于①列表
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
#①查询job_id为‘IT_PROG’部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②查询其它部门中salary<any(①)
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';
#题目：返回其它部门中比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 MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#1. 查询和 Zlotkey 相同部门的员工姓名和工资
#①查询Zlotkey的部门
SELECT  department_id
FROM employees
WHERE last_name = 'Zlotkey'
#②查询姓名和工资，要求部门号 = ①
SELECT last_name,salary
FROM employees
WHERE department_id = (
    SELECT  department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);
#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
#①公司平均工资
SELECT AVG(salary)
FROM employees
#②查询salary>①的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees
);
#3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②将①结果集和employees表进行连接查询，关联条件为老个表的department_id一致
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE e.salary > ag_dep.ag;
#6. 查询管理者是 King 的员工姓名和工资
#①查询king的员工号
SELECT employee_id
FROM employees
WHERE last_name = 'k_ing'
#②查询哪个员工的领导号是①
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
    SELECT employee_id
    FROM employees
    WHERE last_name = 'k_ing'
);