分页查询

• 应用场景：当要显示的数据，一页显示不全，需要分页提交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：查询前5条员工信息

SELECT * FROM employees LIMIT 0, 5;
或者
SELECT * FROM employees LIMIT 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 ;

• 经典案例1：
  • 查询工资最低的员工信息：last_name, salary

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

• • 查询平均工资最低的部门信息

SELECT
*
FROM
departments
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1) ;

• • 查询平均工资最低的部门信息和该部门的平均工资

SELECT
d.*,
dd.ag
FROM
departments d
INNER JOIN
(SELECT
AVG(salary) ag,
department_id
FROM
employees
GROUP BY department_id
ORDER BY ag
LIMIT 1) dd
ON d.`department_id` = dd.department_id ;

• • 查询平均工资最高的job信息

SELECT
*
FROM
jobs j
WHERE j.`job_id` =
(SELECT
job_id
FROM
employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1) ;

• • 查询平均工资高于公司平均工资的部门有哪些

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

• • 查询出公司中所有manager的详细信息

SELECT
*
FROM
employees
WHERE employee_id IN
(SELECT DISTINCT
manager_id
FROM
employees
WHERE manager_id IS NOT NULL) ;

• • 各个部门中，最高工资中，最低的那个部门的最低工资是多少

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

• • 查询平均工资最高的部门的manager的详细信息

SELECT
last_name,
department_id,
email,
salary
FROM
employees
WHERE employee_id =
(SELECT DISTINCT
manager_id
FROM
employees
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1)
AND manager_id IS NOT NULL) ;

 

原文链接：https://blog.csdn.net/qq_21579045/article/details/98111827