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 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
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)
如对本文有疑问， 点击进行留言回复！！
我要评论
网友评论