MySQL——（7）子查询

含义：出现在其它语句中的select语句，称为子查询或内查询（放在小括号里面的）

          外部的查询语句，称为主查询语句

分类：按照子查询出现的位置：select后面、from后面、where或者having后面、exists后面（也称：相关子查询）

支持类型：select后面：仅支持标量子查询

                  from后面：支持表子查询

                  where或者having后面：标量子查询、列子查询、行子查询

                  exists后面：表子查询

按照结果的行列数不同：标量子查询（结果集只有一行一列）、列子查询（结果集只有一列多行）、行子查询（结果集只有一行多列）、表子查询（结果集一般为多行多列，其它类型也行）

一、where或者having后面

特点：子查询放在小括号内

          一般放在条件的右侧

          标量子查询一般搭配单行操作符使用（>、<、>=、<=、=、<>）

          列子查询，一般搭配着多行操作符使用（in、any/some、all）

          子查询的执行优先于主查询的执行

1、标量子查询（单行子查询）

#案例1：谁的工资比Abel高？
use myemployees;
#步骤一：查询Abel的工资
select salary from employees where last_name = 'Abel';
#步骤二：查询员工信息，满足salary大于步骤一的结果
select * 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和工资，且在步骤一和步骤二的范围内
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，且工资最低
select last_name, job_id, salary from employees where salary = (
    select min(salary) from employees
    );

#案例4：查询最低工资大于50号部门的id和最低工资
#步骤一：查询50号部门的最低工资
select min(salary) from employees where department_id = 50;
#步骤二：查询每个部门的id和最低工资
select min(salary), department_id from employees group by department_id;
#步骤三：筛选步骤二中，满足最低工资大于步骤一的结果
select min(salary), department_id from employees group by department_id having min(salary) > (
    select min(salary) from employees where department_id = 50
    );

2、列子查询（多行子查询）

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

#案例2：返回其他工种中，比job_Id为‘IT_PROG’部门任意工资低的员工号、姓名、job_id、salary
#步骤一：查询job_id为‘’的任意工资
select distinct salary from employees where job_id = 'IT_PROG';
#步骤二：查询员工号、姓名、job_id、salary，且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 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 min(salary) from employees where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';

3、行子查询（一行多列，但也可能多行多列，但用的比较少）

#案例1：查询员工编号最小并且工资最高的员工信息
#步骤一：查询最小的员工编号
select min(employee_id) from employees;
#步骤二：查询最高的工资
select max(salary) from employees;
#步骤三：查询员工信息
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后面

注意：仅支持标量子查询

#案例1：查询每个部门的员工个数
select d.*, (
    select count(*) from employees e where e.department_id = d.department_id
    ) 个数
from departments d;
#案例2：查询员工号=102的部门名
select (
    select department_name from departments d inner join employees e on d.department_id = e.department_id where e.employee_id = 102
) 部门名;

三、from后面

按子查询结果充当一张表，要求必须起别名

#案例1：查询每个部门的平均工资的工资等级
#步骤一：查询每个部门的平均工资
select avg(salary), department_id from employees group by department_id;
#步骤二：连接步骤一的结果集和job_grade表，筛选条件：平均工资between lowest_sal and highest_sal
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后面（相关子查询）

语法：exists(完整的查询语句)

结果：1或0

select exists(select employee_id from employees);
#案例1：查询有员工的部门名
select department_name from departments d where exists(
    select * from employees e where d.department_id = e.department_id
);
#用in做：
select department_name from departments d where d.department_id in (
    select department_id from employees
);

#案例2：查询没有女朋友的男生信息
use girls;
select b.* from boys b where not exists(
    select boyfriend_id from beauty be where b.id = be.boyfriend_id
);
#用in做
select b.* from boys b where b.id not in (
    select boyfriend_id from beauty
);