SQL基础篇（进阶八）-分页查询

#进阶八： 分页查询 ※
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交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

size=10
page
1	0
2	10
3	20

*/

#案例1： 查询钱五条的员工信息

SELECT *
FROM `employees`
LIMIT 0,5;

#案例2：查询第11条到第25条

SELECT *
FROM `employees`
LIMIT 10,15;

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

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

P96
#一、查询所有学员的邮箱的用户名 （注：邮箱中@前面的字符）

SELECT SUBSTR( email,1,INSTR(email,'@'-1)) 用户名
FROM stuinfo;

#二、查询一下男生和女生的个数

SELECT COUNT(*)
FROM stuinfo 
GROUP BY sex;

#三、查询年龄>18的所有学生的姓名和年级名称

SELECT NAME, gradeName
FROM stuinfo AS s
LEFT JOIN grade AS g
ON s.gradeId = g.id
WHERE s.age>18;

#查询哪个年级的学生最小年龄>20岁

SELECT MIN(age) 最小年龄,gradeID
FROM stuinfo 
GROUP BY gradeID
HAVING 最小年龄>20;

#试说出查询语句中设计到所有的关键词，以及执行先后顺序

SELECT 查询列表		7	
FROM 表 		1 
连接类型 JOIN 表2	2
ON 连接条件		3
WHERE 筛选条件		4
GROUP BY 分组列表	5
HAVING 分组后的筛选	6
ORDER BY 排序列表	8
LIMIT 偏移，条目数 	9

#1.查询工资最低的员工信息：last_name，salary

SELECT MIN(salary)
FROM `employees`

SELECT `last_name`,`salary`
FROM `employees`
WHERE salary =(
	SELECT MIN(salary)
	FROM `employees`

);

#2.查询平均工资最低的部门信息

SELECT AVG(salary) AS 平均值,`department_id`
FROM `employees`
GROUP BY `department_id`

SELECT MIN(平均值)
FROM (
	SELECT AVG(salary) AS 平均值,`department_id`
	FROM `employees`
	GROUP BY `department_id`) AS ag_e;

SELECT *
FROM `departments` d
WHERE d.`department_id` =(
SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
HAVING AVG(salary) =(
		SELECT MIN(平均值)
	FROM (
		SELECT AVG(salary) AS 平均值,`department_id`
		FROM `employees`
		GROUP BY `department_id`) AS ag_e)
)

#简单：方式二：

SELECT AVG(salary),`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 d.*, ag
FROM `departments` d
INNER JOIN (
	SELECT AVG(salary) ag,`department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(salary)
	LIMIT 1
) AS ag_dep
ON d.`department_id`=ag_dep.`department_id`

#4.查询平均工资最高的job信息

SELECT AVG(salary) ag,`job_id`
FROM `employees`
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;

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 `department_id`,AVG(`salary`)
FROM `employees`
GROUP BY `department_id`

SELECT `department_id`
FROM (
	SELECT `department_id`,AVG(`salary`) ag
	FROM `employees`
	GROUP BY `department_id`) AS ag_dep
WHERE ag_dep.ag> (
	
SELECT AVG(`salary`)
FROM `employees`
)

#老师：

SELECT `department_id`,AVG(`salary`)
FROM `employees`
GROUP BY `department_id`
HAVING AVG(`salary`)>(

	SELECT AVG(`salary`)
	FROM `employees`

)

#6.查询出公司中所有manager的详细信息

SELECT DISTINCT `manager_id`
FROM `employees`

SELECT *
FROM (
	SELECT DISTINCT`manager_id`
	FROM `employees`) AS m
LEFT JOIN `employees` AS e
ON m.`manager_id`=e.`employee_id`

#老师：

SELECT * 
FROM `employees`
WHERE `employee_id`= ANY(
	SELECT DISTINCT `manager_id`
	FROM `employees`
)

#7.各个部门中 最高工资中最低的那个部门的 最低工资是多少
#各个部门的最高工资

SELECT MAX(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`

#各个部门中 最高工资中最低的那个部门

SELECT MAX(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY MAX(`salary`) 
LIMIT 1

#这个部门的最低工资是

SELECT MIN(`salary`),`department_id`
FROM `employees`
WHERE `department_id`=(
	SELECT`department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY MAX(`salary`) 
	LIMIT 1
)
GROUP BY `department_id`

#8.查询平均工资最高的部门的manager 的详细信息：last_name，department_id，email，salary
#平均工资最高的部门

SELECT MAX(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY MAX(`salary`) DESC
LIMIT 1

SELECT DISTINCT manager_id
FROM `employees`
WHERE `department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY MAX(`salary`) DESC
	LIMIT 1
)
SELECT `last_name`,`department_id`,`email`,`salary`
FROM `employees`
WHERE `employee_id`= ANY(
	SELECT DISTINCT manager_id
	FROM `employees`
	WHERE `department_id`=(
		SELECT `department_id`
		FROM `employees`
		GROUP BY `department_id`
		ORDER BY MAX(`salary`) DESC
		LIMIT 1)
)