SQL Limit & Offset
1. 定义
慕课解释:
Limit
用于对查询结果集数量进行限制;Offset
则常与 Limit 搭配,意为偏移,表示跳过一定数量的记录。
2. 前言
本小节,我们将一起学习 SQL 中的Limit
和 Offset
。
在实际的开发中,我们可能常常会遇到以下场景:
- 获得分数的前 5 名。
- 分页获取数据。
这些场景需要细粒度地控制数据库数据,因此 SQL 引入了 Limit 和 Offset 来帮助开发者。
本小节测试数据如下,请先在数据库中执行:
DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
id int PRIMARY KEY,
username varchar(20),
age int,
score int
);
INSERT INTO imooc_user(id,username,age,score)
VALUES (1,'peter', 18, 100),(2,'pedro', 24, 500),
(3,'jerry', 24, 500),(4,'mike', 18, 100),(5,'tom', 20, 1000);
提示: 理论上 Limit 一个指令就能实现 Limit + Offset 的全部功能,但一些数据库为了保持彼此的兼容性,仍然提供了Offset。
3. Limit
Limit 后面可接受一个参数或者两个参数;当接受一个参数count
时,表示返回的结果集最多有 count 条。
语法如下:
SELECT [col] FROM [table_name] LIMIT [count];
其中col
表示字段名,table_name
表示数据表名称,count
代表限制数量。
3.1 例1 Limit 单参数使用
请书写 SQL 语句,返回imooc_user
表中年龄最大的3
位用户。
分析:
题干中指出只需返回 3 位用户,因此需用到 Limit 限制数量;且年龄应该为最大的 3 位,因此结果集应按照年龄降序排序,即 ORDER BY age DESC。
语句:
整理可得语句如下:
SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 3;
结果如下:
+----------+-----+
| username | age |
+----------+-----+
| pedro | 24 |
| jerry | 24 |
| tom | 20 |
+----------+-----+
3.2 例2 Limit 双参数使用
Limit 后面使用双参数时,第一个参数表示偏移量,即 offset,表示从几个记录后开始返回,第二个参数表示最大数量,即 count,表示最多返回几个记录。我们合起来可以理解为,跳过一定数量(offset)的记录,返回最多 count 个记录。
语法如下:
SELECT [col] FROM [table_name] LIMIT [offset],[count];
其中col
表示字段名,table_name
表示数据表名称,offset
是偏移量,count
是最大数量。
请书写SQL语句,返回imooc_user
表中年龄由大到小的第 2 位到第 4 位用户。
分析:
题干中指出只需返回年龄的第 2 位到第 4 位用户,因此需用到 Limit 限制数量和偏移;且年龄由大到小,因此结果集应按照年龄降序排序,即 ORDER BY age DESC。
语句:
整理可得语句如下:
SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 1,3;
结果如下:
+----------+-----+
| username | age |
+----------+-----+
| jerry | 24 |
| tom | 20 |
| peter | 18 |
+----------+-----+
第 2 位到第 4 位共 3 位用户,因此 LIMIT 的第一个参数为 1,跳过第 1 个用户,第二个参数为 3。
4. Offset
Offset 必须与 Limit 搭配使用,且二者都必须是单参数形式。Limit 和 Offset 搭配的作用等同于 Limit 使用双参数。
语法如下:
SELECT [col] FROM [table_name] LIMIT [count] OFFSET [offset];
其中col
表示字段名,table_name
表示数据表名称,offset
是偏移量,count
是最大数量。
4.1 例3 Limit 搭配 Offset 使用
请书写 SQL 语句,返回imooc_user
表中年龄由大到小的第 2 位到第 4 位用户。
分析:
题干中指出只需返回年龄的第 2 位到第 4 位用户,因此需用到 Limit 限制数量,Offset 来表示偏移;且年龄由大到小,因此结果集应按照年龄降序排序,即 ORDER BY age DESC。
语句:
整理可得语句如下:
SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 3 OFFSET 1;
结果如下:
+----------+-----+
| username | age |
+----------+-----+
| jerry | 24 |
| tom | 20 |
| peter | 18 |
+----------+-----+
第 2 位到第 4 位共 3 位用户,因此 offset 应是 1,跳过第 1 位用户,count 应是 3。
5. 小结
- Limit 和 Offset 常搭配用于数据分页,是很多分页工具的核心操作。
- Limit 其实完全可以替代 Limit + Offset,但考虑到SQL语句的可读性,Limit + Offset的写法更好。
- 还没有人评论,欢迎说说您的想法!