MySQL之DQL子查询

进阶7：子查询

含义：
嵌套在其他语句内部的select语句称为子查询或内查询，
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
外面如果为select语句，则此语句称为外查询或主查询

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

where或having后面

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

3、行子查询（多列多行或一行多列）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

> < >= <= = <>

列子查询，一般搭配着多行操作符使用

in、any/some、all
in 等价于 any
not in 等价于 <>all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

# 返回location_id不是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)

);

# 返回location_id是1400或1700的部门中的所有员工姓名
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)
);

# 返回location_id是1400或1700的部门中的所有员工姓名
# 用内连接实现
select last_name,location_id,department_name
from employees e
inner join departments d
on e.department_id = d.department_id
where location_id in (1400,1700)

#返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

select job_id , last_name, employee_id, salary
from employees
WHERE salary < (
	SELECT max(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<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';
=================================================================================
#返回其它工种中比job_id为‘IT_PROG’工种所有工资低的员工的员工号、姓名、job_id 以及salary
select job_id , last_name, employee_id, salary
from employees
WHERE salary < (
	SELECT min(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<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

行子查询(结果集一行多列或多行多列)

#案例：查询员工编号最小并且工资最高的员工信息
#用量子查询实现
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后面

仅仅支持标量子查询，一行一列

#案例：查询每个部门的员工个数，要求没有员工的部门显示0
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

from后面

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

#查询每个部门的平均工资的工资等级
SELECT AVG(salary)
FROM employees
GROUP BY department_id

SELECT grade_level,av.avs
FROM (
	SELECT AVG(salary) avs
	FROM employees
	GROUP BY department_id
) av
INNER JOIN job_grades j ON av.avs BETWEEN lowest_sal AND highest_sal

# 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT employee_id, last_name,salary,department_id
FROM (
	SELECT AVG(salary) avs ,department_id de
	FROM employees e
	GROUP BY department_id
) av
INNER JOIN employees e ON e.`salary` > av.avs
WHERE av.de = e.`department_id`;

exists后面（相关子查询）

语法：
exists(完整的查询语句)
结果：
1或0
exists的执行顺序是先执行主查询，然后再执行exists后面的子查询，根据子查询过滤主查询
能用exists的一般也可以用in

#案例1：查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);


#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)

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