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,1 ...
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)
