mysql基础 | 10.分页查询、联合查询

 一、分页查询

#进阶8：分页查询 ★
/*

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit offset,size;
	
	offer：要显示条目的起始索引（起始索引从0开始）
	size：要显示的条目个数

特点:
	①limit语句放在查询语句的最后
	②公式
	要显示的页数 page，每页的条目数 size
	
	select 查询列表
	from 表
	limit (page-1)*size，size;
	
	size=10
	page
	1	0
	2	10
	3	20

LIMIT：用于限制查询结果返回的行数。例如，LIMIT 5 将只返回查询结果的前5行。
OFFSET：用于指定查询结果的起始位置。例如，LIMIT 5 OFFSET 2 将从第3行开始返回查询结果，并且最多返回5行

*/
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

#案例2：查询第22条-第25条
SELECT * FROM employees LIMIT 10,15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;


#测试题
/*
已知表 stuinfo
id 学号
name 姓名
email 邮箱  john@126.com
gradeId 年级编号
sex 性别 男 女
age 年龄

已知表 grade
id 年级编号
gradeName 年级名称
*/

#1、查询所有学员的邮箱的用户名（注：邮箱中@前面的字符）
SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用户名
FROM stuinfo;

#2、查询男生和女生的个数
SELECT sex,COUNT(*)
FROM stuinfo 
GROUP BY sex;

#3、查询年龄>18的所有学生的姓名和年级名称
SELECT NAME,gradeName
FROM stuinfo s
INNER JOIN grade g
ON s.gradeId=g.id
WHERE age>18;

#4、查询哪个年级的学生最小年龄>20岁
SELECT gradeId,MIN(age)
FROM stuinfo
GROUP BY gradeId
HAVING MIN(age)>20;

#5、试说出查询语句中涉及到的所有的关键字，以及执行先后顺序
SELECT 查询列表
FROM 表1		①
连接类型 JOIN 表2	②
ON 连接条件		③
WHERE 筛选条件		④
GROUP BY 分组列表	⑤
HAVING 分组后的筛选	⑥
ORDER by排序列表	⑦
LIMIT 偏移,条目数; 	⑧


#子查询景点案例讲解
#1.查询工资最低的员工信息：last_name,salary
#①查询最低的工资
SELECT MIN(salary)
FROM employees;

#②查询last_name,salary，要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

#2.查询平均工资最低的部门信息
#方式一：
#①各部门的平均工资
SELECT department_id,AVG(salary) ag
FROM employees
GROUP BY `department_id`;

#②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT department_id,AVG(salary) ag
	FROM employees
	GROUP BY `department_id`
) ag_dep;

#③查询哪个部门的平均工资=②
SELECT department_id
FROM employees
GROUP BY `department_id`
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT department_id,AVG(salary) ag
		FROM employees
		GROUP BY `department_id`
	) ag_dep
);

#④查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY `department_id`
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT department_id,AVG(salary) ag
			FROM employees
			GROUP BY `department_id`
		) ag_dep
	)
);

#方式二：
#①求平均工资最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

#②查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

#3.查询平均工资最低的部门信息和该部门的平均工资
#①求平均工资最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

#②查询部门信息和最低工资
SELECT d.*,ag
FROM departments d
	INNER JOIN (
	SELECT department_id,AVG(salary) ag
	FROM employees
	GROUP BY department_id
	ORDER BY ag
	LIMIT 1
)ag_dep
ON d.department_id=ag_dep.department_id;

#4.查询平均工资最高的job信息
#①查询每个job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;

#②查询job信息
SELECT *
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

#5.查询平均工资高于公司平均工资的部门有哪些
#①查询公司的平均工资
SELECT AVG(salary)
FROM employees;

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

#③筛选②结果集，满足平均工资>①
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees
);

#6.查询出公司中所有manager的详细信息
#①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees;

#②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id IN(
	SELECT DISTINCT manager_id
	FROM employees
);

#7.各个部门中最高工资中最低的那个部门的最低工资是多少
#①查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1;

#②查询①结果的那个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary) ASC
	LIMIT 1
);

#8.查询平均工资最高的部门的manager的相信信息：last_name,department_id,email,salary
#①查询平均工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;

#②将employees和departments连接查询，筛选条件是①
SELECT e.manager_id,last_name,e.department_id,email,salary
FROM departments d
INNER JOIN employees e
ON d.`manager_id`=e.`employee_id`
WHERE e.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

二、联合查询

#进阶9：联合查询
/*
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;


#案例：查询中国用户中男性的信息以及外国用户中男性的用户信息

SELECT id,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';