手册   游戏   WEB   字典   单词   在线工具  

MySQL 使用技巧 分页 limit

MySQL 分页使用 limit 关键字, limit x,y (x 代表从哪条数据开始, y 代表页面大小. MySQL 第一条数据在 limit 计算时索引为 0)

limit 10

前 10 条

limit 0,10

从第 1 条开始的 10 条

limit 10,10

从第 11 条开始的 10 条

limit 100,10

从第 101 条开始的 10 条

数据量大时 (> 千万), 效率低

oracal 分页, 使用 oracle 的特殊列 rownum

  1. select * from
  2. (select *,rownum R from
  3. (select * from a)

实例: 查找入职员工时间排名倒数第三的员工所有信息. limit 2,1 代表选择从第 3 条数据开始的 1 条数据, 即第 3 页数据, 页面大小为 1

select * from employees order by hire_date desc limit 2,1

前 n 条数据, 即从索引 0 开始计算: limit n 或者 limit 0,n

  1. MySQL> select * from employees order by hire_date desc limit 5;
  2. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  3. | employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
  4. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  5. |         173 | Sundita    | Kumar     | SKUMAR  | 011.44.1343.329268 | 2000-04-21 | SA_REP   | 6100.00 |           0.10 |        148 |            80 |
  6. |         167 | Amit       | Banda     | ABANDA  | 011.44.1346.729268 | 2000-04-21 | SA_REP   | 6200.00 |           0.10 |        147 |            80 |
  7. |         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
  8. |         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
  9. |         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
  10. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  11. 5 rows in set (0.00 sec)
  12. MySQL> select * from employees order by hire_date desc limit 0,5;
  13. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  14. | employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
  15. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  16. |         173 | Sundita    | Kumar     | SKUMAR  | 011.44.1343.329268 | 2000-04-21 | SA_REP   | 6100.00 |           0.10 |        148 |            80 |
  17. |         167 | Amit       | Banda     | ABANDA  | 011.44.1346.729268 | 2000-04-21 | SA_REP   | 6200.00 |           0.10 |        147 |            80 |
  18. |         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
  19. |         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
  20. |         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
  21. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  22. 5 rows in set (0.00 sec)

更多例子

limit 5 前 5 条数据

limit 2,1 第 3 页数据, 页面大小为 1

limit 2,2 第 2 页数据, 页面大小为 2

limit 2,3 第 3 条数据起, 共 3 条数据, 此数据不符合分页数据显示格式

分页显示格式: limit startIndex pageSize

  1. startIndex = (需要查询的页码数 - 1) * pageSize
  2. MySQL> select * from employees order by hire_date desc limit 5;
  3. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  4. | employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
  5. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  6. |         173 | Sundita    | Kumar     | SKUMAR  | 011.44.1343.329268 | 2000-04-21 | SA_REP   | 6100.00 |           0.10 |        148 |            80 |
  7. |         167 | Amit       | Banda     | ABANDA  | 011.44.1346.729268 | 2000-04-21 | SA_REP   | 6200.00 |           0.10 |        147 |            80 |
  8. |         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
  9. |         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
  10. |         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
  11. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  12. 5 rows in set (0.00 sec)
  13. MySQL> select * from employees order by hire_date desc limit 2,1;
  14. +-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
  15. | employee_id | first_name | last_name | email | phone_number       | hire_date  | job_id | salary  | commission_pct | manager_id | department_id |
  16. +-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
  17. |         166 | Sundar     | Ande      | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 |           0.10 |        147 |            80 |
  18. +-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
  19. 1 row in set (0.00 sec)
  20. MySQL> select * from employees order by hire_date desc limit 2,2;
  21. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  22. | employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
  23. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  24. |         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
  25. |         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
  26. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  27. 2 rows in set (0.00 sec)
  28. MySQL> select * from employees order by hire_date desc limit 2,3;
  29. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  30. | employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
  31. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  32. |         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
  33. |         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
  34. |         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
  35. +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
  36. 3 rows in set (0.00 sec)

来源: https://www.cnblogs.com/InternetJava/p/12725963.html


与本文相关文章

  1. MySQL 开发规范与使用技巧总结
  2. MySQL oracle 分页
  3. mysql 的 OFFSET 实现分页
  4. MySQL 实现分页 - 基于 limit 方式
  5. 解决 MySQL 分页数据错乱重复
  6. mysql 在 orderby 和 limit 混合使用时重复数据问题
  7. 自定义分页查询 (MySQL)
  8. MySQL   主从复制
暂无,快来抢沙发吧！
更多





黑ICP备17005318号-1