写在前面

在实际工作中经常会有一些特殊需求，如在开发一个电商系统时，如果我们想查看今天所有成交的订单，并按照交易额由高到低进行排序展示，那么就需要用到本篇学习的排序查询了。

本篇是DQL的排序查询篇，主要学习的内容如下所示：（1）排序查询基本语法格式；（2）单字段排序；（3）多字段排序；（4）按别名排序；（5）按函数排序；（6）where之后排序；（7）截取查询基本语法格式；（8）获取前n行记录；（9）获取最大的一条记录；（10）获取排名第n到m的记录；（11）分页查询；（12）limit中不能使用表达式；（13）limit后面2个参数不能为负数；（14）排序分页存在的坑；（15）文章小结等。

需要说明的是，本篇文章代码中被[]包含的内容表示可选的，而被|分割的内容表示可选其一。

排序查询

所谓的排序查询就是将查询得到的结果进行排序后进行展示。

基本语法格式

排序查询的基本语法格式如下所示：

1	select 字段名 from 表名 order by 字段1 [asc\|desc],字段2 [asc\|desc],...;

在上述语句中，需要排序的字段必须跟在order by后面，[asc|desc]表示排序的规则，其中asc是升序，desc是降序，默认是asc，也就是升序。同时支持多个字段的排序，多个字段之间使用逗号进行隔开。

单字段排序

例子1：下面的例子展示了，如何进行单字段的排序：

mysql> drop table if exists book2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table book2(
    -> a int,
    -> b varchar(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into book2 values(10,'a'),(8,'b'),(2,'c'),(16,'d');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from book2;
+------+------+
| a    | b    |
+------+------+
|   10 | a    |
|    8 | b    |
|    2 | c    |
|   16 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from book2 order by a desc;
+------+------+
| a    | b    |
+------+------+
|   16 | d    |
|   10 | a    |
|    8 | b    |
|    2 | c    |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from book2 order by a asc;
+------+------+
| a    | b    |
+------+------+
|    2 | c    |
|    8 | b    |
|   10 | a    |
|   16 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from book2 order by a;
+------+------+
| a    | b    |
+------+------+
|    2 | c    |
|    8 | b    |
|   10 | a    |
|   16 | d    |
+------+------+
4 rows in set (0.00 sec)

多字段排序

例子2：下面的例子展示了，如何进行多字段的排序。这里有一个学生表，需要先按照学生年龄降序排序，当年龄相同时，再按照学号升序排序，如下所示：

mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table student(
    -> id int not null comment '学号' primary key,
    -> age tinyint not null comment '年龄',
    -> name varchar(20) comment '姓名');
Query OK, 0 rows affected (0.02 sec)


mysql> insert into student(id,age,name) values(1001,18,'aa'),
mysql> (1002,18,'bb'),(1003,20,'cc'),(1005,20,'dd'),(1006,26,'ee');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+-----+------+
| id   | age | name |
+------+-----+------+
| 1001 |  18 | aa   |
| 1002 |  18 | bb   |
| 1003 |  20 | cc   |
| 1005 |  20 | dd   |
| 1006 |  26 | ee   |
+------+-----+------+
5 rows in set (0.00 sec)

mysql> select * from student order by age desc,id asc;
+------+-----+------+
| id   | age | name |
+------+-----+------+
| 1006 |  26 | ee   |
| 1003 |  20 | cc   |
| 1005 |  20 | dd   |
| 1001 |  18 | aa   |
| 1002 |  18 | bb   |
+------+-----+------+
5 rows in set (0.00 sec)

按别名排序

除了可以按照既有的字段排序，还可以按照字段的别名进行排序。

例子3：下面的例子展示了，如何按别名进行排序：

mysql> select * from student;
+------+-----+------+
| id   | age | name |
+------+-----+------+
| 1001 |  18 | aa   |
| 1002 |  18 | bb   |
| 1003 |  20 | cc   |
| 1005 |  20 | dd   |
| 1006 |  26 | ee   |
+------+-----+------+
5 rows in set (0.00 sec)

mysql> select age as '年龄',id as '学号' from student order by 年龄 asc,学号 desc;
+--------+--------+
| 年龄   | 学号   |
+--------+--------+
|     18 |   1002 |
|     18 |   1001 |
|     20 |   1005 |
|     20 |   1003 |
|     26 |   1006 |
+--------+--------+
5 rows in set (0.00 sec)

按函数排序

其实还可以按照函数来进行排序，尤其是涉及到对时间进行排序的时候。

例子4：下面的例子展示了，如何按函数进行排序。

第一步，新建一个学生表，其中有编号(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.02 sec)

mysql> insert into student (id,birth,name) values (1001,'1990-10-10','a'),(1005,'1960-03-01','b'),
mysql> (1003,'1960-08-16','c'),(1004,'1968-07-01','d'),(1010,'1962-05-16','e');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+------------+------+
| id   | birth      | name |
+------+------------+------+
| 1001 | 1990-10-10 | a    |
| 1003 | 1960-08-16 | c    |
| 1004 | 1968-07-01 | d    |
| 1005 | 1960-03-01 | b    |
| 1010 | 1962-05-16 | e    |
+------+------------+------+
5 rows in set (0.00 sec)

第二步，需求是按照出生年份升序，学号升序，并查询出学号，出生日期，出生年份和姓名等信息。这里提供两种SQL语句，如下所示：

mysql> select id as 学号,birth as 出生日期,year(birth) as 出生年份,name as 姓名 from student order by year(birth) asc,id asc;
+--------+--------------+--------------+--------+
| 学号   | 出生日期     | 出生年份     | 姓名   |
+--------+--------------+--------------+--------+
|   1003 | 1960-08-16   |         1960 | c      |
|   1005 | 1960-03-01   |         1960 | b      |
|   1010 | 1962-05-16   |         1962 | e      |
|   1004 | 1968-07-01   |         1968 | d      |
|   1001 | 1990-10-10   |         1990 | a      |
+--------+--------------+--------------+--------+
5 rows in set (0.01 sec)

mysql> select id as 学号,birth as 出生日期,year(birth) as 出生年份,name as 姓名 from student order by 出生年份 asc,id asc;
+--------+--------------+--------------+--------+
| 学号   | 出生日期     | 出生年份     | 姓名   |
+--------+--------------+--------------+--------+
|   1003 | 1960-08-16   |         1960 | c      |
|   1005 | 1960-03-01   |         1960 | b      |
|   1010 | 1962-05-16   |         1962 | e      |
|   1004 | 1968-07-01   |         1968 | d      |
|   1001 | 1990-10-10   |         1990 | a      |
+--------+--------------+--------------+--------+
5 rows in set (0.00 sec)

在上面的SQL语句中，year函数属于日期函数，可以获取对应日期中的年份。这里使用了两种不同方式来实现排序需求，第一种是在order by中使用了函数，第二种是使用别名进行排序，本质上这两种方式都是没有区别的。

where之后排序

前面介绍的都是没有条件查询的排序，这种在实际工作中很少见，大部分都是存在条件查询的情况。

例子5：下面的例子展示了，如何where之后排序。

第一步，新建一个订单表，其中有订单编号(id)，和价格(price)：

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.02 sec)

mysql> insert into t_order(price) values(88.95),(100.68),(500.00),(300.00),(20.88),(200.50);
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的订单，并按照订单金额降序排序显示订单编号和订单金额，如下所示：

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t where t.price >=100 order by 订单金额 desc;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            3 |       500.00 |
|            4 |       300.00 |
|            6 |       200.50 |
|            2 |       100.68 |
+--------------+--------------+
4 rows in set (0.00 sec)

截取查询

当需要从查询结果中截取部分数据进行展示，此时就需要使用到截取查询。

基本语法格式

截取查询的基本语法格式如下所示：

1	select 列名 from 表名 limit [offset,] count;

请注意以下几点内容：
（1）上面的offset是偏移量，也就是跳过多少行，offset可以省略，默认是0，表示跳过0行，其取值范围为[0,+∞)；
（2）count表示跳过offset行之后才开始取数据，取count行记录，其取值范围为[0,+∞)；
（3）limit中的offset和count的值不能使用表达式。

获取前n行记录

开发者如果想获取前n行记录，可以使用如下命令：

1
2
3

select 列名 from 表名 limit 0,n;
或者
select 列名 from 表名 limit n;

例子6：下面的例子展示了，如何获取订单表中的前2行记录：

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t limit 0,2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            1 |        88.95 |
|            2 |       100.68 |
+--------------+--------------+
2 rows in set (0.00 sec)

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t limit 2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            1 |        88.95 |
|            2 |       100.68 |
+--------------+--------------+
2 rows in set (0.00 sec)

获取最大的一条记录

现在又有一个需求，需要获取订单金额中最大的一条记录，此时应该如何操作呢？

这里提供一个思路，先按照订单金额进行降序排列，然后取第一条记录即可。

例子7：下面的例子展示了，如何获取订单金额中最大的一条记录：

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 desc limit 0,1;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            3 |       500.00 |
+--------------+--------------+
1 row in set (0.00 sec)

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 desc limit 1;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            3 |       500.00 |
+--------------+--------------+
1 row in set (0.00 sec)

获取排名第n到m的记录

为了获取排名第n到m的记录，首先需要跳过第n-1条记录，然后取出m-n+1条记录，此时语法格式如下：

1	select 列名 from 表名 limit n-1,m-n+1;

例子8：下面的例子展示了，如何获取订单表中金额由高到底排序的第2到4名的信息：

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 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 t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 desc limit 1,3;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            4 |       300.00 |
|            6 |       200.50 |
|            2 |       100.68 |
+--------------+--------------+
3 rows in set (0.00 sec)

分页查询

在实际开发过程中，当返回结果较多时，我们就需要使用到分页查询了。分页一般有两个参数page和pageSize，其中page表示第几页，注意是从1开始，其取值范围为[1,+∞)；pageSize表示每页显示多少条记录，其取值范围为[1,+∞)。例如：page=2，pageSize=10，表示获取第二页10条数据。

我们可以使用limit关键字来实现分页，相应的语法格式如下所示：

1	select 列名 from 表名 limit (page-1)*pageSize,pageSize;

例子9：下面的例子展示了，如何使用分页查询来获取订单表中金额由高到底排序，每页显示2条，依次获取所有订单数据：

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 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 t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 desc limit 0,2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            3 |       500.00 |
|            4 |       300.00 |
+--------------+--------------+
2 rows in set (0.00 sec)

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 desc limit 2,2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            6 |       200.50 |
|            2 |       100.68 |
+--------------+--------------+
2 rows in set (0.00 sec)

mysql> select t.id as 订单编号,t.price as 订单金额 from t_order as t order by 订单金额 desc limit 4,2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            1 |        88.95 |
|            5 |        20.88 |
+--------------+--------------+
2 rows in set (0.00 sec)

limit使用中的一些坑

limit中不能使用表达式

参看如下代码：

mysql> select * from t_order limit 1,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 '+1' at line 1
mysql> select * from t_order limit 1+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 '+1,1' at line 1

也就是说limit字句中不允许使用表达式，后面必须是明确的数字。

limit后面2个参数不能为负数

参看如下代码：

mysql> select * from t_order 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 '-1' at line 1
mysql> select * from t_order 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 '-1' at line 1
mysql> select * from t_order 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 '-1,-1' at line 1

也就是说limit后面2个参数不能为负数，必须是非负数。

排序分页存在的坑

第一步，准备数据，如下所示：

mysql> drop table if exists book3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table book3(
    -> a int not null primary key auto_increment,
    -> b int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into book3(b) values(1),(2),(3),(4),(2),(2),(2),(2);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from book3;
+---+---+
| 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)

第二步，我们按照字段b进行升序，然后每页2条来分页查询数据。那么第一页，第二页，第三页，第四页的数据依次为：

mysql> select * from book3 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)

mysql> select * from book3 order by b asc limit 0,2;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)

mysql> select * from book3 order by b asc limit 2,2;
+---+---+
| a | b |
+---+---+
| 8 | 2 |
| 6 | 2 |
+---+---+
2 rows in set (0.00 sec)

mysql> select * from book3 order by b asc limit 4,2;
+---+---+
| a | b |
+---+---+
| 6 | 2 |
| 7 | 2 |
+---+---+
2 rows in set (0.00 sec)

mysql> select * from book3 order by b asc limit 6,2;
+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
2 rows in set (0.00 sec)

mysql> select * from book3 order by b asc limit 7,2;
+---+---+
| a | b |
+---+---+
| 4 | 4 |
+---+---+
1 rows in set (0.00 sec)

仔细观察上述代码的执行过程，可以发现存在两个问题：
（1）第3个和第4个SQL语句，分别是查询第2页和第3页的数据，但是结果却出现了相同的数据；
（2）第一个SQL语句查询可以知道整个表只有8条数据，每页查询2条，那么正好就只有4页，怎么还会出现5页，且第5页还有数据呢？

将下来就需要好好分析造成上面问题的原因，其实原因就是b字段存在相同的值，当排序过程中存在相同的值时，且没有其他排序规则时，MySQL不知道应该按照什么规则来进行排序。

这就像足球比赛中的排名一样，两个队比分战平时，如何排名？不指定的话就随意排，当然这肯定是不行的，一般会算主客场进球，净胜球，红黄牌数量等规则加以辅助。

如果排序字段中存在重复值时，建议再指定一个排序规则，以此来规避排序的二义性。

那么可以在之前的例子中，在其后面添加对字段a进行降序排序规则：

mysql> select * from book3 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 book3 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 book3 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 book3 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 book3 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 book3 order by b asc ,a desc limit 8,2;
Empty set (0.00 sec)

这样观察才发现分页数据都正常显示了，第5页就没有数据。

文章小结

接下来将对本文的一些知识进行小结：
（1）可以使用order by[asc|desc]来对查询结果排序，其中asc是升序，desc是降序，[asc|desc]可以省略，默认是asc，也就是升序；
（2）limit可以用来限制查询结果的返回行数，它有两个参数(offset、count)，其中offset是偏移量，也就是跳过多少行；count是跳过多少行之后取count行。注意offset可以省略，默认为0。同时offset和count的值都必须不为负数，且两个值都不能使用表达式；
（3）在进行分页排序时，一定要规避二义性，因此建议在后面添加主键来保证排序有确切意义。

那么这样关于DQL的排序查询篇学习就到此为止，后续开始学习其他知识。