MySQL 数据库 day-04

七、子查询

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

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

(一)、where或having后面 ☆

1、标量子查询(单行子查询)√
2、列子查询(多行子查询)√
3、行子查询(多行多列)

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
　　> < >= <= = <>
　列子查询，一般搭配着多行操作符使用
　　　in any/some all
④子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果

1、行子查询

*案例1：谁的工资比Abel高？*
#①查询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和工资，要求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
);

案例3：返回工资最少的员工的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
);

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

#①查询50号部门的最低工资
SELECT MIN(salary)
FROM	employees
WHERE	department_id=50

#②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM	employees
GROUP BY  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
);

#非法使用标量子查询
SELECT MIN(salary),department_id
FROM	employees
GROUP BY  department_id
HAVING	MIN(salary)>(
	SELECT salary
	FROM	employees
	WHERE	department_id=50
);
#单行操作符只能搭配单行子查询

2、列子查询(多行子查询)

返回多行
使用多行比较操作符

操作符 　　　　　　含义
IN/NOT IN 　　　返回列表中的任意一个
ANY|some 　　　和子查询返回的某一个值比较 <可以用MIN替换>
ALL 　　　　　　和子查询返回的所以值比较 <可以用MAX替换>

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

#①查询location_id是1400或1700的部门编号
SELECT department_id
FROM	departments
WHERE	location_id IN(1400,1700);

#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM	employees
WHERE	department_id IN(
	SELECT department_id
	FROM	departments
	WHERE	location_id IN(1400,1700)
);

案例2：返回其他工种中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary

#①查询job_i为'IT_PROG'部门任意工资
SELECT salary
FROM	employees
WHERE	job_id='IT_PROG'

#②查询员工号、姓名、job_id以及salary,salary<①的任意一个
#法1
SELECT last_name,employee_id,job_id,salary
FROM	employees
WHERE	salary<ANY(
	SELECT salary
	FROM	employees
	WHERE	job_id='IT_PROG'
)AND job_id<>'IT_PROG'; 
#法2
SELECT last_name,employee_id,job_id,salary
FROM	employees
WHERE	salary<(
	SELECT MAX(salary)
	FROM	employees
	WHERE	job_id='IT_PROG'
)AND job_id<>'IT_PROG'; 

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

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

#①查询最小的员工编号

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

#法2
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后面

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

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

#①查询每个部门的平均工资
SELECT   AVG(salary),department_id
FROM	 departments
GROUP BY	department_id

#②连接①的结果集和job_grades表，筛选条件平均工资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
);

案例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
);

(五)、子查询总结：

子查询格式：
　　　　　　　　　　　　　执行顺序:
select　　　查询列表　　　　　 ⑦
from 　　　　表1　　　　　　　①
连接类型 　　join　　表2　　　　②
on　　　　　连接条件　　　　　③
where　　　筛选条件　　　　　④
group by　　分组列表 　　　　　⑤
having　　　分组后的筛选　　　⑥
order by　　排序列表　　　　　⑧
limit　　　　偏移,条目数；　　　⑨

八、分页查询 ★

应用场景：当要显示的数据,一页显示不全,需要分页提交sql请求

语法:
select 　　　查询列表
from　　　　　表
[＜join type＞ join 　表2
on　　　　　连接条件
where　　　筛选条件
group by 　分组条件
having 　　分组后的筛选
order by 　　排序后的字段]
limit 　　　　offset,size;

#offset:要显示条目的起始索引(从0开始)
size:要显示的条目个数

特点：
①limit语句放在查询语句的最后
②提交分页请求公式
要显示的页数 page,每页的条目数 size

select 查询列表
from	表
limit (page-1)*size,size;

案例1:查询前五条员工信息

SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;  #索引默认从0开始

案例2:有奖金的员工信息，并且工资较高的前10名显示出来

SELECT * 
FROM	employees
WHERE	commission_pct IS NOT NULL
ORDER BY	salary DESC
LIMIT	10;

九、联合查询

union:将多条查询语句的结果合并成一个结果

语法：
　　查询语句1
　　union
　　查询语句2
　　union
　　　 ……

应用场景：
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时

特点：
1、要求多条查询语句的查询列表是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、使用union关键字默认会去重,使用union all可以包含重复项

引入的案例：查询部门编号>90或邮箱中包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;