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

分类：按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：表子查询
※where或having后面：
标量子查询（单行）
列子查询（多行）
行子查询
exists后面（相关子查询）：表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（一列多行）
行子查询（一行多列）
表子查询（一般为多行多列）
*/

一、where或having后面

/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、列子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
< > >= <= <>
列子查询：一般搭配着多行操作符使用
in any/some all
*/

1、标量子查询

案例一：谁的工资比abel高？

①查询abel的工资

show databases;
use myemployees;
select salary from employees where last_name ='Abel';

②查询员工的信息，满足salary大于①中的结果

select * from employees where salary > (
    select salary from employees where last_name ='Abel'
);

案例二：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和工资（要求job_id与①相同，salary比②多）

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_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
);

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

①查询50号部门的最低工资

select min(salary) from employees where department_id = 50;

②查询每个部门的最低工资

select min(salary),department_id from employees group by department_id;

③在②基础上筛选salary大于①的department_id和min（salary）

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 department_id from departments where location_id in(1400,1700);

②查询员工姓名，要求部门编号是①列表中的某一个(in关键字可以换成=any（）)

select last_name from employees where department_id in(
    select department_id from departments where location_id in(1400,1700)
);
#或
select last_name from employees where department_id =any(
    select department_id from departments where location_id in(1400,1700)
);
#若换成not in
select last_name from employees where department_id not in(
    select department_id from departments where location_id in(1400,1700)
);
#或
select last_name from employees where department_id <>all(
    select department_id from departments where location_id in(1400,1700)
);

案例二：返回其他工种中比 job_id为‘IT_PROG’工种任一工资 低 的

员工的员工号、姓名、job_id以及salary

#①查询job_id为‘IT_PROG’工种的所有工资
select salary from employees where job_id = 'IT_PROG';
#②查询比①中工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary from employees 
where salary<any(
    select 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';

案例三：返回其他工种中比 job_id为‘IT_PROG’工种所有工资 低 的

员工的员工号、姓名、job_id以及salary

select employee_id,last_name,job_id,salary from employees 
where salary<all(
    select 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';

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

案例:查询员工编号最小并且工资最高的员工信息

#①查询最小的员工编号
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 department_name from departments d inner join employees e 
on e.department_id = d.department_id where e.employee_id = 102;
#或
select (
    select department_name from departments d inner join employees e 
on e.department_id = d.department_id where e.employee_id = 102
) 部门名;

三：from后面

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

查询每个部门的平均工资的工资等级

#①每个部门的平均工资
select avg(salary),department_id from employees group by department_id;
select * from job_grades;
#②连接上面的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
select ag_dep.*,g.grade_level
from (
    select avg(salary) ag_sal,department_id from employees group by department_id
) ag_dep 
inner join job_grades g 
on ag_dep.ag_sal between lowest_sal and highest_sal; 

四、exists后面（相关子查询）

/*
语法：exists(完整的查询语句)
结果：0或1
*/

select exists (select employee_id from employees where salary = 30000) 员工编号;

案例1：查询有员工的部门名

select department_name from departments d where exists(
    select * from employees e where e.department_id = d.department_id
);
#in
select department_name from departments d where d.department_id in (
    select department_id from employees 
);

案例2：查询没女朋友的男神信息

#in
use girls;
select bo.* from boys bo where bo.id not in (
    select boyfriend_id from beauty
); 
select * from boys;
select * from beauty;
#exists
select bo.* from boys bo where not exists(
        select boyfriend_id from beauty b 
        where b.boyfriend_id = bo.id
);