【MYSQL 基础篇笔记】DQL——分页查询

分页查询

一、应用场景

当要显示的数据，一页显示不全，需要分页提交sql请求

二、语法

SELECT 查询列表
FROM 表
【JOIN type】JOIN 表2
ON 链接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序字段
LIMIT 【OFFSET】,size;

OFFSET :要显示条目的起始索引，起始索引从0开始
size：要显示的条目个数

执行顺序：FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

三、特点

1、LIMIT语句在查询语句的最后
2、公式：要显示的页数是page，每页条目数是size

SELECT 查询列表
FROM 表
LIMIT (page-1)*size,size

(page-1)*size=起始索引，size为显示个数

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

#案例2：查询第11条-25条员工信息
SELECT *FROM employees LIMIT 10,15;
SELECT *FROM employees LIMIT 24 OFFSET 9 #选取前0-24行，排除0-9行
#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
#案例4：查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*
FROM departments d,(
	SELECT avg(salary),department_id
	FROM employees
	GROUP BY department_id
	ORDER BY avg(salary)
	LIMIT 1) avg
WHERE d.department_id=avg.department_id;
#或	
SELECT d.*
FROM departments d
WHERE d.department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY avg(salary)
	LIMIT 1)

#案例5：各部门中，最高工资中最低的那个部门的，最低工资是多少？
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);
	
#案例6：查询平均工资最高的部门的manager的姓名、部门、邮箱、工资
SELECT last_name,e.department_id,email,salary
FROM employees e
JOIN departments d
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);