使用查詢語句的時候，經(jīng)常要返回前幾條或者中間某幾行數(shù)據(jù)，這個時候怎么辦呢？不用擔心，mysql已 經(jīng)為我們提供了這樣一個功能。

SELECT?*?FROM?table?? LIMIT?[offset,]?rows?|?rows OFFSET offset

????? LIMIT 子句可以被用于強制 SELECT 語句返回指定的記錄數(shù)。LIMIT 接受一個或兩個數(shù)字參數(shù)。參數(shù)必須是一個整數(shù)常量。如果給定兩個參數(shù)，第一個參數(shù)指定第一個返回記錄行的偏移量，第二個參數(shù)指定返回記錄行的最大數(shù)目。初 始記錄行的偏移量是 0(而不是 1)： 為了與 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql>?SELECT?*?FROM?table?LIMIT?5,10;??//?檢索記錄行?6-15

//為了檢索從某一個偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個參數(shù)為?-1：?
mysql>?SELECT?*?FROM?table?LIMIT?95,-1;?//?檢索記錄行?96-last.

//如果只給定一個參數(shù)，它表示返回最大的記錄行數(shù)目：?
mysql>?SELECT?*?FROM?table?LIMIT?5;?????//檢索前?5?個記錄行

//換句話說，LIMIT n 等價于 LIMIT?0,n。

實例：

mysql> use sppeivan;
Database changed
mysql> select * from employee;?// 返回所有結(jié)果
+------------+------------+--------+-------------+
| username?? | password?? | gender | cellphone?? |
+------------+------------+--------+-------------+
| ewrew????? | erewr????? | MAN??? | 13618889076 |
| ewrwerw32r | 324324???? | MAN??? | 13618889076 |
| grrrrr???? | r44343???? | WOMAN | 13618889076 |
| pansong??? | erewrwe??? | MAN??? | 13618889076 |
| ret43t43t | 43t43????? | MAN??? | 13618889076 |
| ret44t4??? | r4tgr????? | WOMAN | 13618889076 |
| tttt?????? | 4et??????? | WOMAN | 13618889076 |
| wr34r????? | retre????? | MAN??? | 13618889076 |
| xxx???? | safe?????? | MAN??? | 13618889076 |
| hhh?????? | ewrwer???? | MAN??? | 13618889076 |
| hg?????? | 6574326543 | MAN??? | 13618889076 |
| ps?????? | ewrwe????? | MAN??? | 13618889076 |
| zhu?????? | werew????? | MAN??? | 13618889076 |
+------------+------------+--------+-------------+
13 rows in set (0.01 sec)

mysql> select * from employee limit 3 7;
ERROR 1064 (42000): You have an error in your SQL syntax
corresponds to your MySQL server version for the right s
line 1
mysql> select * from employee limit 3, 7;?// 返回4-11行
+-----------+----------+--------+-------------+
| username | password | gender | cellphone?? |
+-----------+----------+--------+-------------+
| pansong?? | erewrwe | MAN??? | 13618889076 |
| ret43t43t | 43t43??? | MAN??? | 13618889076 |
| ret44t4?? | r4tgr??? | WOMAN | 13618889076 |
| tttt????? | 4et????? | WOMAN | 13618889076 |
| wr34r???? | retre??? | MAN??? | 13618889076 |
| xxx???? | safe???? | MAN??? | 13618889076 |
| hhh????? | ewrwer?? | MAN??? | 13618889076 |
+-----------+----------+--------+-------------+
7 rows in set (0.00 sec)

mysql> select * from employee limit 3,-1;?// 錯誤語法
ERROR 1064 (42000): You have an error in your SQL syntax
corresponds to your MySQL server version for the right s
t line 1

mysql> select * from employee limit 3,1;?// 返回第4行
+----------+----------+--------+-------------+
| username | password | gender | cellphone?? |
+----------+----------+--------+-------------+
| pansong | erewrwe | MAN??? | 13618889076|
+----------+----------+--------+-------------+
1 row in set (0.00 sec)

mysql> select * from employee limit 3;?// 返回前3行

+------------+----------+--------+-------------+
| username?? | password | gender | cellphone?? |
+------------+----------+--------+-------------+
| ewrew????? | erewr??? | MAN??? | 13618889076 |
| ewrwerw32r | 324324?? | MAN??? | 13618889076 |
| grrrrr???? | r44343?? | WOMAN | 13618889076 |
+------------+----------+--------+-------------+
3 rows in set (0.00 sec)

mysql> select * from employee limit 10;?// 返回前10行

+------------+----------+--------+-------------+
| username?? | password | gender | cellphone?? |
+------------+----------+--------+-------------+
| ewrew????? | erewr??? | MAN??? | 13618889076 |
| ewrwerw32r | 324324?? | MAN??? | 13618889076 |
| grrrrr???? | r44343?? | WOMAN | 13618889076 |
| pansong??? | erewrwe | MAN??? | 13618889076 |
| ret43t43t | 43t43??? | MAN??? | 13618889076 |
| ret44t4??? | r4tgr??? | WOMAN | 13618889076 |
| tttt?????? | 4et????? | WOMAN | 13618889076 |
| wr34r????? | retre??? | MAN??? | 13618889076 |
| xxx???? | safe???? | MAN??? | 13811588469 |
| hhh?????? | ewrwer?? | MAN??? | 13618889076 |
+------------+----------+--------+-------------+
10 rows in set (0.00 sec)

mysql> select * from employee limit 9,1;?// 返回第10行

+----------+----------+--------+-------------+
| username | password | gender | cellphone?? |
+----------+----------+--------+-------------+
| hhh???? | ewrwer?? | MAN??? | 13816668468 |
+----------+----------+--------+-------------+
1 row in set (0.00 sec)

總結(jié)

以上是生活随笔為你收集整理的mysql中limit用法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。