这是 MySQL 系列第 8 篇.
环境: mysql5.7.25,cmd 命令中进行演示.
代码中被 [] 包含的表示可选,| 符号分开的表示可选其一.
本章内容
详解排序查询
详解 limit
limit 存在的坑
分页查询中的坑
排序查询(order by)
电商中: 我们想查看今天所有成交的订单, 按照交易额从高到低排序, 此时我们可以使用数据库中的排序功能来完成.
排序语法:
select 字段名 from 表名 order by 字段 1 [asc|desc], 字段 2 [asc|desc];
需要排序的字段跟在 order by 之后;
asc|desc 表示排序的规则, asc: 升序, desc: 降序, 默认为 asc;
支持多个字段进行排序, 多字段排序之间用逗号隔开.
单字段排序
- MySQL> create table test2(a int,b varchar(10));
- Query OK, 0 rows affected (0.01 sec)
- MySQL> insert into test2 values (10,'jack'),(8,'tom'),(5,'ready'),(100,'javacode');
- Query OK, 4 rows affected (0.00 sec)
- Records: 4 Duplicates: 0 Warnings: 0
- MySQL> select * from test2;
- +------+----------+
- | a | b |
- +------+----------+
- | 10 | jack |
- | 8 | tom |
- | 5 | ready |
- | 100 | javacode |
- +------+----------+
- 4 rows in set (0.00 sec)
- MySQL> select * from test2 order by a asc;
- +------+----------+
- | a | b |
- +------+----------+
- | 5 | ready |
- | 8 | tom |
- | 10 | jack |
- | 100 | javacode |
- +------+----------+
- 4 rows in set (0.00 sec)
- MySQL> select * from test2 order by a desc;
- +------+----------+
- | a | b |
- +------+----------+
- | 100 | javacode |
- | 10 | jack |
- | 8 | tom |
- | 5 | ready |
- +------+----------+
- 4 rows in set (0.00 sec)
- MySQL> select * from test2 order by a;
- +------+----------+
- | a | b |
- +------+----------+
- | 5 | ready |
- | 8 | tom |
- | 10 | jack |
- | 100 | javacode |
- +------+----------+
- 4 rows in set (0.00 sec)
多字段排序
比如学生表, 先按学生年龄降序, 年龄相同时, 再按学号升序, 如下:
- MySQL> create table stu(id int not null comment '学号' primary key,age tinyint not null comment '年龄',name varchar(16) comment '姓名');
- Query OK, 0 rows affected (0.01 sec)
- MySQL> insert into stu (id,age,name) values (1001,18,'路人甲 Java'),(1005,20,'刘德华'),(1003,18,'张学友'),(1004,20,'张国荣'),(1010,19,'梁朝伟');
- Query OK, 5 rows affected (0.00 sec)
- Records: 5 Duplicates: 0 Warnings: 0
- MySQL> select * from stu;
- +------+-----+---------------+
- | id | age | name |
- +------+-----+---------------+
| 1001 | 18 | 路人甲 Java |
| 1003 | 18 | 张学友 |
| 1004 | 20 | 张国荣 |
| 1005 | 20 | 刘德华 |
| 1010 | 19 | 梁朝伟 |
- +------+-----+---------------+
- 5 rows in set (0.00 sec)
- MySQL> select * from stu order by age desc,id asc;
- +------+-----+---------------+
- | id | age | name |
- +------+-----+---------------+
| 1004 | 20 | 张国荣 |
| 1005 | 20 | 刘德华 |
| 1010 | 19 | 梁朝伟 |
| 1001 | 18 | 路人甲 Java |
| 1003 | 18 | 张学友 |
- +------+-----+---------------+
- 5 rows in set (0.00 sec)
按别名排序
- MySQL> select * from stu;
- +------+-----+---------------+
- | id | age | name |
- +------+-----+---------------+
| 1001 | 18 | 路人甲 Java |
| 1003 | 18 | 张学友 |
| 1004 | 20 | 张国荣 |
| 1005 | 20 | 刘德华 |
| 1010 | 19 | 梁朝伟 |
- +------+-----+---------------+
- 5 rows in set (0.00 sec)
MySQL> select age '年龄',id as '学号' from stu order by 年龄 asc, 学号 desc;
+--------+--------+
| 年龄 | 学号 |
- +--------+--------+
- | 18 | 1003 |
- | 18 | 1001 |
- | 19 | 1010 |
- | 20 | 1005 |
- | 20 | 1004 |
- +--------+--------+
按函数排序
有学生表(id: 编号, birth: 出生日期, name: 姓名), 如下:
- MySQL> drop table if exists student;
- Query OK, 0 rows affected (0.01 sec)
- MySQL> CREATE TABLE student (
- -> id int(11) NOT NULL COMMENT '学号',
- -> birth date NOT NULL COMMENT '出生日期',
- -> name varchar(16) DEFAULT NULL COMMENT '姓名',
- -> PRIMARY KEY (id)
- -> );
- Query OK, 0 rows affected (0.01 sec)
- MySQL> insert into student (id,birth,name) values (1001,'1990-10-10','路人甲 Java'),(1005,'1960-03-01','刘德华'),(1003,'1960-08-16','张学友'),(1004,'1968-07-01','张国荣'),(1010,'1962-05-16','梁朝伟');
- Query OK, 5 rows affected (0.00 sec)
- Records: 5 Duplicates: 0 Warnings: 0
- MySQL>
- MySQL> SELECT * FROM student;
- +------+------------+---------------+
- | id | birth | name |
- +------+------------+---------------+
| 1001 | 1990-10-10 | 路人甲 Java |
| 1003 | 1960-08-16 | 张学友 |
| 1004 | 1968-07-01 | 张国荣 |
| 1005 | 1960-03-01 | 刘德华 |
| 1010 | 1962-05-16 | 梁朝伟 |
- +------+------------+---------------+
- 5 rows in set (0.00 sec)
需求: 按照出生年份升序, 编号升序, 查询出编号, 出生日期, 出生年份, 姓名, 2 种写法如下:
MySQL> SELECT id 编号, birth 出生日期, year(birth) 出生年份, name 姓名 from student ORDER BY year(birth) asc,id asc;
+--------+--------------+--------------+---------------+
| 编号 | 出生日期 | 出生年份 | 姓名 |
+--------+--------------+--------------+---------------+
| 1003 | 1960-08-16 | 1960 | 张学友 |
| 1005 | 1960-03-01 | 1960 | 刘德华 |
| 1010 | 1962-05-16 | 1962 | 梁朝伟 |
| 1004 | 1968-07-01 | 1968 | 张国荣 |
| 1001 | 1990-10-10 | 1990 | 路人甲 Java |
- +--------+--------------+--------------+---------------+
- 5 rows in set (0.00 sec)
MySQL> SELECT id 编号, birth 出生日期, year(birth) 出生年份, name 姓名 from student ORDER BY 出生年份 asc,id asc;
+--------+--------------+--------------+---------------+
| 编号 | 出生日期 | 出生年份 | 姓名 |
+--------+--------------+--------------+---------------+
| 1003 | 1960-08-16 | 1960 | 张学友 |
| 1005 | 1960-03-01 | 1960 | 刘德华 |
| 1010 | 1962-05-16 | 1962 | 梁朝伟 |
| 1004 | 1968-07-01 | 1968 | 张国荣 |
| 1001 | 1990-10-10 | 1990 | 路人甲 Java |
- +--------+--------------+--------------+---------------+
- 5 rows in set (0.00 sec)
说明:
year 函数: 属于日期函数, 可以获取对应日期中的年份.
上面使用了 2 种方式排序, 第一种是在 order by 中使用了函数, 第二种是使用了别名排序.
where 之后进行排序
有订单数据如下:
- MySQL> drop table if exists t_order;
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- MySQL> create table t_order(
- -> id int not null auto_increment comment '订单编号',
- -> price decimal(10,2) not null default 0 comment '订单金额',
- -> primary key(id)
- -> )comment '订单表';
- Query OK, 0 rows affected (0.01 sec)
- MySQL> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);
- Query OK, 6 rows affected (0.00 sec)
- Records: 6 Duplicates: 0 Warnings: 0
- MySQL> select * from t_order;
- +----+--------+
- | id | price |
- +----+--------+
- | 1 | 88.95 |
- | 2 | 100.68 |
- | 3 | 500.00 |
- | 4 | 300.00 |
- | 5 | 20.88 |
- | 6 | 200.50 |
- +----+--------+
- 6 rows in set (0.00 sec)
需求: 查询订单金额>=100 的, 按照订单金额降序排序, 显示 2 列数据, 列头: 订单编号, 订单金额, 如下:
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a where a.price>=100 order by a.price desc;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 3 | 500.00 |
- | 4 | 300.00 |
- | 6 | 200.50 |
- | 2 | 100.68 |
- +--------------+--------------+
- 4 rows in set (0.00 sec)
limit 介绍
limit 用来限制 select 查询返回的行数, 常用于分页等操作.
语法:
select 列 from 表 limit [offset,] count;
说明:
offset: 表示偏移量, 通俗点讲就是跳过多少行, offset 可以省略, 默认为 0, 表示跳过 0 行; 范围:[0,+∞).
count: 跳过 offset 行之后开始取数据, 取 count 行记录; 范围:[0,+∞).
limit 中 offset 和 count 的值不能用表达式.
下面我们列一些常用的示例来加深理解.
获取前 n 行记录
select 列 from 表 limit 0,n;
或者
select 列 from 表 limit n;
示例, 获取订单的前 2 条记录, 如下:
- MySQL> create table t_order(
- -> id int not null auto_increment comment '订单编号',
- -> price decimal(10,2) not null default 0 comment '订单金额',
- -> primary key(id)
- -> )comment '订单表';
- Query OK, 0 rows affected (0.01 sec)
- MySQL> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);
- Query OK, 6 rows affected (0.01 sec)
- Records: 6 Duplicates: 0 Warnings: 0
- MySQL> select * from t_order;
- +----+--------+
- | id | price |
- +----+--------+
- | 1 | 88.95 |
- | 2 | 100.68 |
- | 3 | 500.00 |
- | 4 | 300.00 |
- | 5 | 20.88 |
- | 6 | 200.50 |
- +----+--------+
- 6 rows in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a limit 2;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 1 | 88.95 |
- | 2 | 100.68 |
- +--------------+--------------+
- 2 rows in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a limit 0,2;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 1 | 88.95 |
- | 2 | 100.68 |
- +--------------+--------------+
- 2 rows in set (0.00 sec)
获取最大的一条记录
我们需要获取订单金额最大的一条记录, 可以这么做: 先按照金额降序, 然后取第一条记录, 如下:
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 3 | 500.00 |
- | 4 | 300.00 |
- | 6 | 200.50 |
- | 2 | 100.68 |
- | 1 | 88.95 |
- | 5 | 20.88 |
- +--------------+--------------+
- 6 rows in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc limit 1;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 3 | 500.00 |
- +--------------+--------------+
- 1 row in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc limit 0,1;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 3 | 500.00 |
- +--------------+--------------+
- 1 row in set (0.00 sec)
获取排名第 n 到 m 的记录
我们需要先跳过 n-1 条记录, 然后取 m-n+1 条记录, 如下:
select 列 from 表 limit n-1,m-n+1;
如: 我们想获取订单金额最高的 3 到 5 名的记录, 我们需要跳过 2 条, 然后获取 3 条记录, 如下:
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 3 | 500.00 |
- | 4 | 300.00 |
- | 6 | 200.50 |
- | 2 | 100.68 |
- | 1 | 88.95 |
- | 5 | 20.88 |
- +--------------+--------------+
- 6 rows in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc limit 2,3;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 6 | 200.50 |
- | 2 | 100.68 |
- | 1 | 88.95 |
- +--------------+--------------+
- 3 rows in set (0.00 sec)
分页查询
开发过程中, 分页我们经常使用, 分页一般有 2 个参数:
page: 表示第几页, 从 1 开始, 范围[1,+∞)
pageSize: 每页显示多少条记录, 范围[1,+∞)
如: page = 2,pageSize = 10, 表示获取第 2 页 10 条数据.
我们使用 limit 实现分页, 语法如下:
select 列 from 表名 limit (page - 1) * pageSize,pageSize;
需求: 我们按照订单金额降序, 每页显示 2 条, 依次获取所有订单数据, 第 1 页, 第 2 页, 第 3 页数据, 如下:
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 3 | 500.00 |
- | 4 | 300.00 |
- | 6 | 200.50 |
- | 2 | 100.68 |
- | 1 | 88.95 |
- | 5 | 20.88 |
- +--------------+--------------+
- 6 rows in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc limit 0,2;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 3 | 500.00 |
- | 4 | 300.00 |
- +--------------+--------------+
- 2 rows in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc limit 2,2;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 6 | 200.50 |
- | 2 | 100.68 |
- +--------------+--------------+
- 2 rows in set (0.00 sec)
MySQL> select a.id 订单编号, a.price 订单金额 from t_order a order by a.price desc limit 4,2;
+--------------+--------------+
| 订单编号 | 订单金额 |
- +--------------+--------------+
- | 1 | 88.95 |
- | 5 | 20.88 |
- +--------------+--------------+
- 2 rows in set (0.00 sec)
避免踩坑
limit 中不能使用表达式
- MySQL> select * from t_order where limit 1,4+1;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1,4+1' at line 1
- MySQL> select * from t_order where limit 1+0;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1+0' at line 1
- MySQL>
结论: limit 后面只能够跟明确的数字.
limit 后面的 2 个数字不能为负数
- MySQL> select * from t_order where limit -1;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit -1' at line 1
- MySQL> select * from t_order where limit 0,-1;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 0,-1' at line 1
- MySQL> select * from t_order where limit -1,-1;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit -1,-1' at line 1
排序分页存在的坑
准备数据:
- MySQL> insert into test1 (b) values (1),(2),(3),(4),(2),(2),(2),(2);
- Query OK, 8 rows affected (0.01 sec)
- Records: 8 Duplicates: 0 Warnings: 0
- MySQL> select * from test1;
- +---+---+
- | a | b |
- +---+---+
- | 1 | 1 |
- | 2 | 2 |
- | 3 | 3 |
- | 4 | 4 |
- | 5 | 2 |
- | 6 | 2 |
- | 7 | 2 |
- | 8 | 2 |
- +---+---+
- 8 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc;
- +---+---+
- | a | b |
- +---+---+
- | 1 | 1 |
- | 2 | 2 |
- | 5 | 2 |
- | 6 | 2 |
- | 7 | 2 |
- | 8 | 2 |
- | 3 | 3 |
- | 4 | 4 |
- +---+---+
- 8 rows in set (0.00 sec)
下面我们按照 b 升序, 每页 2 条数据, 来获取数据.
下面的 sql 依次为第 1 页, 第 2 页, 第 3 页, 第 4 页, 第 5 页的数据, 如下:
- MySQL> select * from test1 order by b asc limit 0,2;
- +---+---+
- | a | b |
- +---+---+
- | 1 | 1 |
- | 2 | 2 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc limit 2,2;
- +---+---+
- | a | b |
- +---+---+
- | 8 | 2 |
- | 6 | 2 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc limit 4,2;
- +---+---+
- | a | b |
- +---+---+
- | 6 | 2 |
- | 7 | 2 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc limit 6,2;
- +---+---+
- | a | b |
- +---+---+
- | 3 | 3 |
- | 4 | 4 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc limit 7,2;
- +---+---+
- | a | b |
- +---+---+
- | 4 | 4 |
- +---+---+
- 1 row in set (0.00 sec)
上面有 2 个问题:
问题 1: 看一下第 2 个 sql 和第 3 个 sql, 分别是第 2 页和第 3 页的数据, 结果出现了相同的数据, 是不是懵逼了.
问题 2: 整个表只有 8 条记录, 怎么会出现第 5 页的数据呢, 又懵逼了.
我们来分析一下上面的原因: 主要是 b 字段存在相同的值, 当排序过程中存在相同的值时, 没有其他排序规则时, MySQL 懵逼了, 不知道怎么排序了.
就像我们上学站队一样, 按照身高排序, 那身高一样的时候如何排序呢? 身高一样的就乱排了.
建议: 排序中存在相同的值时, 需要再指定一个排序规则, 通过这种排序规则不存在二义性, 比如上面可以再加上 a 降序, 如下:
- MySQL> select * from test1 order by b asc,a desc;
- +---+---+
- | a | b |
- +---+---+
- | 1 | 1 |
- | 8 | 2 |
- | 7 | 2 |
- | 6 | 2 |
- | 5 | 2 |
- | 2 | 2 |
- | 3 | 3 |
- | 4 | 4 |
- +---+---+
- 8 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc,a desc limit 0,2;
- +---+---+
- | a | b |
- +---+---+
- | 1 | 1 |
- | 8 | 2 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc,a desc limit 2,2;
- +---+---+
- | a | b |
- +---+---+
- | 7 | 2 |
- | 6 | 2 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc,a desc limit 4,2;
- +---+---+
- | a | b |
- +---+---+
- | 5 | 2 |
- | 2 | 2 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc,a desc limit 6,2;
- +---+---+
- | a | b |
- +---+---+
- | 3 | 3 |
- | 4 | 4 |
- +---+---+
- 2 rows in set (0.00 sec)
- MySQL> select * from test1 order by b asc,a desc limit 8,2;
- Empty set (0.00 sec)
看上面的结果, 分页数据都正常了, 第 5 页也没有数据了.
总结
order by ... [asc|desc]用于对查询结果排序, asc: 升序, desc: 降序, asc|desc 可以省略, 默认为 asc
limit 用来限制查询结果返回的行数, 有 2 个参数(offset,count),offset: 表示跳过多少行, count: 表示跳过 offset 行之后取 count 行
limit 中 offset 可以省略, 默认值为 0
limit 中 offset 和 count 都必须大于等于 0
limit 中 offset 和 count 的值不能用表达式
分页排序时, 排序不要有二义性, 二义性情况下可能会导致分页结果乱序, 可以在后面追加一个主键排序
MySQL 系列目录
第 1 篇: MySQL 基础知识
第 2 篇: 详解 MySQL 数据类型(重点)
第 3 篇: 管理员必备技能(必须掌握)
第 4 篇: DDL 常见操作
第 5 篇: DML 操作汇总(insert,update,delete)
第 6 篇: select 查询基础篇
第 7 篇: 玩转 select 条件查询, 避免采坑
MySQL 系列大概有 20 多篇, 喜欢的请关注一下! 提前祝大家中秋快乐!
MySQL 高手系列 - 第 8 篇: 详解排序和分页(order by & limit), 及存在的坑
来源: http://www.bubuko.com/infodetail-3194729.html