MySQL数据库——子查询

 

 

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

分类：
    按子查询出现的位置
        select后面
            仅仅支持标量子查询
        from后面
            支持表子查询
        where、having后面 ※
            支持标量子查询（单行） ※，也支持列子查询（多行） ※，行子查询较少
        exists后面（相关子查询）
            支持表子查询
    按结果集行列数不同
        标量子查询（结果集只有一行一列）
        列子查询（结果集只有一列多行），也叫多行子查询
        行子查询（结果集有一行多列）
        表子查询（结果集随便几行几列）
        
*/
#一、where/having后面的子查询
/*
特点：
    都放在小括号内
    一般放在条件的右侧
    标量子查询：一般搭配单行操作符使用< > <= >= = 
    列子查询：一般搭配多行操作符使用in any or some
    子查询的执行优先于主查询的执行，因为主查询用到了子查询的结果

*/
#1.标量子查询（单行子查询）
#案例：谁的工资比Abel高
#①查询名字叫Abel的工资
SELECT salary
FROM employees
WHERE last_name = '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
);
#案例：返回公司工资最少的员工的last_bame,job_id，salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);
#案例：查询最低工资大于50号部门的最低工资的部门id和最低工资
#查询每个部门的最低工资，筛选最低公司大于50号部门的
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) >  (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);
#非法使用标量子查询
#一行一列！
#子查询必要要能查出来！

#2.列子查询（多行子查询，一列多行）
#案例：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(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,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,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后面
#案例：查询每个部门的员工个数
SELECT d.*,(
    SELECT COUNT(*) 
    FROM employees e
    WHERE e.department_id = d.`department_id`
)
FROM departments d;

#三、放在from后面
#将查询的结果集充当数据源,必须其别名
#案例：查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.`grade_level`
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

#四、exists后面（相关子查询）
#判断子查询的结果有没有值
SELECT EXISTS(SELECT employee_id FROM employees);#1
#案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE e.`department_id` = d.`department_id`
);