mysql limit 用法

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)：为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

1 mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15   
2   
3 //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：    
4 mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.   
5   
6 //如果只给定一个参数，它表示返回最大的记录行数目：    
7 mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行   
8   
9 //换句话说，LIMIT n 等价于 LIMIT 0,n。

转载自 https://www.cnblogs.com/cai170221/p/7122289.html

 1 取得薪水最高的前五名员工
 2 select * from emp order by sal desc limit 0,5;
 3 +-------+-------+-----------+------+------------+---------+------+--------+
 4 | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
 5 +-------+-------+-----------+------+------------+---------+------+--------+
 6 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
 7 |  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
 8 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
 9 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
10 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
11 +-------+-------+-----------+------+------------+---------+------+--------+
12 
13 10.取得薪水最高的第六到第十名员工
14 
15 select * from emp order by sal desc limit 5,5;
16 +-------+--------+----------+------+------------+---------+---------+--------+
17 | EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
18 +-------+--------+----------+------+------------+---------+---------+--------+
19 |  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
20 |  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
21 |  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
22 |  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
23 |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
24 +-------+--------+----------+------+------------+---------+---------+--------+
25 
26 11.取得最后入职的5名员工
27 select * from emp order by hiredate desc limit 5;
28 +-------+--------+---------+------+------------+---------+------+--------+
29 | EMPNO | ENAME  | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
30 +-------+--------+---------+------+------------+---------+------+--------+
31 |  7876 | ADAMS  | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
32 |  7788 | SCOTT  | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
33 |  7934 | MILLER | CLERK   | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
34 |  7902 | FORD   | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
35 |  7900 | JAMES  | CLERK   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
36 +-------+--------+---------+------+------------+---------+------+--------+