用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,已 经为我们提供了这样一个功能。
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初 始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。
实例:
mysql> use sppeivan;
Database changedmysql> select * from employee; // 返回所有结果+------------+------------+--------+-------------+| 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 syntaxcorresponds to your MySQL server version for the right sline 1mysql> 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 syntaxcorresponds to your MySQL server version for the right st 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)