MySQL 分页

1.背景

• 查询返回的记录太多了,我们希望一页一页的查看,如: 百度,淘宝的页面

• 表里有多条数据,但是我们只想查询其中的某几条数据

2.实现规则

• 分页原理

所谓分页,就是将数据库中的结果集,一段一段显示出需要的条件

• MySQL中使用LIMIT实现分页

• 使用格式:

  LIMIT [位置偏移量],行数 

  • 第一个"位置偏移量"参数指明MySQL以哪一行开始显示,是一个可选参数.如果不指定"位置偏移量",就会默认从表中的第一条记录开始(第一条记录的位置偏移量为0,第二条记录的位置偏移量为0,以此类推)显示;第二个参数"行数",指明返回的记录条数.

  • 结构"LIMIT 0,条目数" 等价于"LIMIT 条目数"

• 举例:

  • 需求1: 每页显示20条记录,此时显示第一页

  SELECT employee_id,last_name
  FROM employees
  LIMIT 0,20;

  • 需求2: 每页显示20条记录,此时显示第二页

  SELECT employee_id,last_name
  FROM employees
  LIMIT 20,20;

  • #我们可以看出,每页显示pageSize条记录,此时显示pageNo页.

  公式为: (当前页数-1)*每页条数,每页条数

  LIMIT (pageNO-1)*pageSize,pageSize;

  即比如我们在淘宝中点击跳转到第几页,后台就会将数据发给后台程序,后台程序在经过分析后交给sql执行,提供给用户相应的信息

• WHERE ORDER BY LIMIT 声明顺序

  SELECT employee_id,last_name
  FROM employees
  WHERE salary > 6000
  ORDER BY salary DESC
  LIMIT 0,10;

  即LIMIT子句必须写在整个SELECT语句的最后面!

  • #练习: 有107条数据,只要显示第32,33行

    ```sql

    LIMIT 31,2; ```

  注意: MySQL8.0的新特性可以使用"LIMIT 3 OFFSET 4"语法,意思是获取从第5条记录开始后面3条记录,和"LIMIT 4,3"返回的结果相同,即前面参数为条数,后面参数为从第几个开始

  SELECT employee_id,last_name
  FROM employees
  LIMIT 31 OFFSET 2;

• 使用LIMIT 的好处

  约束返回结果的数量可以减少数据表的网络传输量,也可以提升查询效率,如果我们只需要知道的返回结果只有1条,就可以使用LIMIT 1,告诉SELECT语句只需要返回1条记录即可.这样的好处就是不需要扫描完整的表,只需要检索到一符合条件的记录即可返回 如: 查询最高工资

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

3.拓展

• 分页在不同DBMS中使用的关键字可能不同.在MySQL,PostgreSQL,MariaDB和SQLite中使用LIMIT关键字,且需放在SELECT语句的最后面

• 如果是SQL Server 和 Access中,则需使用TOP关键字 如:

  SELECT TOP 5 name,hp_max
  FROM heros
  ORDER BY hp_max DESC;

• 如果是在DB2 中,则需要使用 FETCH FIRST 5 ROWS ONLY

  如:

  SELECT name,hp_max
  FROM heros
  ORDER BY hp_max DESC
  FETCH FIRST 5 ROWS ONLY;

• Oracle 中,需要使用ROWNUM来统计行数

  SELECT rownum,last_name,salary  #每个表中都隐藏着一个rownum字段
  FROM employees
  WHERE rownum < 5
  ORDER BY salary DESC;

  这条语句是先取出前五条数据行,然后再按照hp_max从高到低顺序进行排序,但产生的结果与前面的方法不同