子查询

含义：出现在其它语句中的select语句，称为子查询或内查询

外部的语句可以是 insert、update、delete、select
select在外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
SELECT后：仅仅支持标量子查询

FROM后：支持表子查询

WHERE或HAVING后：

　　常用于 标量子查询√
　　列子查询√

　　少用于 行子查询

EXISTS后（可以叫相关子查询）：所有子查询都行

按结果集的行列数不同：

标量 子查询/单行子查询	结果集 一行一列或者称为
列 子查询/多行子查询	结果集 一列多行
行 子查询	结果集 多行多列
表 子查询	一般为多行多列

#WHERE或HAVING后

1 标量子查询 单行子查询（一行一列）

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

3 行子查询 （多列多行）

单行和多行的特点：
1 子查询放在小括号内
2 子查询一般放在条件的右侧
3 标量子查询，一般搭配着单行操作符使用
> < >= <= <>

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

子查询的执行顺序优先于主查询，因为查询的条件用到子查询的结果

#1 标量子查询 单行子查询

#案例：谁的工资比abel高？
#第1步 查询Abel的工资

SELECT salary
FROM employees
WHERE last_name='Abel'

#第2步 查询员工的信息满足salary>1结果的结果

SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);#把第1步称为单行子查询，或标量子查询

#案例：返回job_id与141号员工相同，salary比143号员工多的员工 员工姓名 job_id和工资
#第1步查询141员工的job id

SELECT job_id
FROM employees
WHERE employee_id=141;

#第2步查询143员工的salary

SELECT salary
FROM employees
WHERE employee_id=143;
#第3步查询员工的姓名、job id、工资,要求jobid=1并且salary>2
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND(
SELECT salary
FROM employees
WHERE employee_id=143
)

#在子查询中使用分组函数

#案例：返回公司工资最少的员工的last_name、job_id、salary
#第1步查询公司的 最低工资

SELECT MIN(salary)
FROM employees;

#第2步查询last_name、job_id、salary,要求salary=1

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);

#子查询中的having子句

#案例：查询最低工资大于50部门的最低工资，的部门id和最低工资
#第1步查询50部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id=50;

#第2步查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

#第3步 在2基础上筛选，满足MIN(salary)》1的部门和最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);

#非法使用标量子查询

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id=50
);

/*
单行操作符（操作单个值的）只能搭配，单行子查询（标量子查询）
*/

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id=250
);
/*
子查询的结果不是一行一列
*

不管是什么样的子查询都放在小括号中

#2多行子查询（列子查询）（一列多行）

特点：返回多行

使用：需要搭配多行操作符来使用

把模糊查询小括号里放的常量，替换成了查询语句，查询语句的结果代替了模糊查询用的常量（查询语句的结果是一个列表）

常见的： 
操作符 含义
IN/NOT IN 等于类表中的任意一个
ANY|SOME 和子查询返回的 某一个值比较
ALL 和子查询返回的 所有值比较

IN意义：等于类表中的任意一个
NOT IN意义：这里面的都不是

ANY|SOME意义：满足一个即可。例如a<ANY(10,20,30) a=15，这样就可以被查询出来。
使用的较少因为可以被代替：a>MIN()就行了，省去了麻烦事

ALL意义：满足所有的。例如a<ANY(10,20,30) a=15，这样就不能被查询出来。
也可以被代替，a>MAX()

#案例：返回 location_id是1400或1700的部门中的 所有员工姓名

#1.查询location_id是1400或1700的部门编号

SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id=1400 OR location_id=1700 
//WHERE location_id=1400 || location_id=1700 
//WHERE location_id IN(1400,1700);

#2.查询员工姓名，要求部门号是1列表中的 某一个

SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

#还可以

SELECT last_name
FROM employees
WHERE department_id=ANY(#等于里面任意一个
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

SELECT last_name
FROM employees
WHERE department_id NOT IN(
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

SELECT last_name
FROM employees
WHERE department_id !=ALL(#相当于并且
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

SELECT last_name
FROM employees
WHERE department_id !=ANY(#相当于或者
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

#案例：返回其他部门中比job_id为 it_prog 部门仁义工资低的员工的：工号姓名、job_id、salary

#1.查询job_id为 it_prog 任意一个工资

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';

#2.查询员工号、姓名、job_id、salary salary<1任意一个

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';

#或，只要不超过最大的就可以满足

SELECT last_name,employee_id,job_id,salary 
FROM employees
WHERE salary<(
SELECT DISTINCT MAX(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';

#或，只要不低于最小的就可以满足

SELECT last_name,employee_id,job_id,salary 
FROM employees
WHERE salary<(
SELECT DISTINCT MIN(salary)
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id != 'IT_PROG';

#3.行子查询（结果集一行多列，多行多列）

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

#1：查询最小的员工编号

SELECT MIN(employee_id)
FROM employees;

#2:查询最低工资

SELECT MAX(salary)
FROM employees;

#3：查询员工信息

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后

#案例:查询每个部门的员工个数

SELECT d.*,(
SELECT COUNT(*)
FROM employees 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.department_id
WHERE e.employee_id=102
) 部门名;
/*
仅仅支持标量子查询
*/

三、from后面：

放在from后面一般的就是表，相当于把子查询的结果集充当一个表格来使用，充当数据源
要求：必须要起别名，否则找不到
表子查询

#案例：查询每个部门的平均工资的工资等级
#1 查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

#2 连接1的结果集和工资等级表 筛选条件between

SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、EXISTS后（相关子查询）

1代表true
2代表false

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

SELECT EXISTS(
SELECT employee_id 
FROM employees
WHERE salary =300000    
);

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

SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e 
WHERE d.department_id=e.department_id
);
/*
和其他子查询不一样，前面的子查询先执行，因为主查询用到子查询的结果
而这个先去执行主查询，根据主查询的结果，再去过滤。这就是相关子查询
子查询涉及到了主查询的字段
*/

#使用in的方式代替：能用 EXISTS就一定能用in代替

SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees
);

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

UPDATE beauty SET boyfriend_id = NULL WHERE id=10;

#IN 的方式
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);

#EXISTS
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);