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)