大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说mysql 使用技巧 分页limit,希望您对编程的造诣更进一步.
mysql 分页使用 limit关键字，limit x,y （x代表从哪条数据开始，y代表页面大小。mysql第一条数据在limit计算时索引为0）
代码100分
代码100分limit 10 前10条 limit 0,10 从第1条开始的10条 limit 10,10 从第 11 条开始的 10 条 limit 100,10 从第101条开始的10条 数据量大时(>千万)，效率低
oracal 分页，使用 oracle的特殊列 rownum select * from (select *,rownum R from (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
代码100分mysql> select * from employees order by hire_date desc limit 5; +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 2000-04-21 | SA_REP | 6100.00 | 0.10 | 148 | 80 | | 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2000-04-21 | SA_REP | 6200.00 | 0.10 | 147 | 80 | | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2000-03-08 | ST_CLERK | 2200.00 | NULL | 120 | 50 | | 165 | David | Lee | DLEE | 011.44.1346.529268 | 2000-02-23 | SA_REP | 6800.00 | 0.10 | 147 | 80 | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ 5 rows in set (0.00 sec) mysql> select * from employees order by hire_date desc limit 0,5; +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 2000-04-21 | SA_REP | 6100.00 | 0.10 | 148 | 80 | | 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2000-04-21 | SA_REP | 6200.00 | 0.10 | 147 | 80 | | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2000-03-08 | ST_CLERK | 2200.00 | NULL | 120 | 50 | | 165 | David | Lee | DLEE | 011.44.1346.529268 | 2000-02-23 | SA_REP | 6800.00 | 0.10 | 147 | 80 | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ 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
startIndex = (需要查询的页码数 - 1) * pageSize
mysql> select * from employees order by hire_date desc limit 5; +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 2000-04-21 | SA_REP | 6100.00 | 0.10 | 148 | 80 | | 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2000-04-21 | SA_REP | 6200.00 | 0.10 | 147 | 80 | | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2000-03-08 | ST_CLERK | 2200.00 | NULL | 120 | 50 | | 165 | David | Lee | DLEE | 011.44.1346.529268 | 2000-02-23 | SA_REP | 6800.00 | 0.10 | 147 | 80 | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ 5 rows in set (0.00 sec) mysql> select * from employees order by hire_date desc limit 2,1; +-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+ | employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | +-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+ | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | +-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+ 1 row in set (0.00 sec) mysql> select * from employees order by hire_date desc limit 2,2; +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2000-03-08 | ST_CLERK | 2200.00 | NULL | 120 | 50 | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ 2 rows in set (0.00 sec) mysql> select * from employees order by hire_date desc limit 2,3; +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2000-03-08 | ST_CLERK | 2200.00 | NULL | 120 | 50 | | 165 | David | Lee | DLEE | 011.44.1346.529268 | 2000-02-23 | SA_REP | 6800.00 | 0.10 | 147 | 80 | +-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+ 3 rows in set (0.00 sec)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8944.html
