MySQL必知必会 -- 排序检索数据 ORDER BY

我们使用SELECT SQL语句返回某个数据库表的单个列没有特定的顺序，如果我们想对获得的数据进行一个排序，就可以加上ORDER BY 这个参数。

子句（clause） SQL语句由子句构成，有些子句是必需的，而有的是可选的。
为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

MariaDB [test]> select  * from linux;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| user1 | 111    | boy  |
| user2 | 222    | girl |
| user3 | 333    | boy  |
| user4 | 444    | boy  |
| user5 | 555    | boy  |
| a     | passa  | boy  |
| 1     | pass1  | girl |
+-------+--------+------+
7 rows in set (0.00 sec)

MariaDB [test]> select  * from linux order by user;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| 1     | pass1  | girl |
| a     | passa  | boy  |
| user1 | 111    | boy  |
| user2 | 222    | girl |		/按照user排序，数字在前，字母在后
| user3 | 333    | boy  |
| user4 | 444    | boy  |
| user5 | 555    | boy  |
+-------+--------+------+
7 rows in set (0.00 sec)

MariaDB [test]> select  * from linux order by user,sex;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| 1     | pass1  | girl |
| a     | passa  | boy  |
| user1 | 111    | boy  |		/按多个列排序
| user2 | 222    | girl |
| user3 | 333    | boy  |
| user4 | 444    | boy  |
| user5 | 555    | boy  |
+-------+--------+------+
7 rows in set (0.00 sec)

MariaDB [test]> insert into linux value ('user1','pass1','girl');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into linux value ('user1','pass1','girl');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select  * from linux order by user,sex;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| 1     | pass1  | girl |
| a     | passa  | boy  |
| user1 | 111    | boy  |
| user1 | pass1  | girl |
| user2 | 222    | girl |	/按多个列排序时，首先按user排序，然后在每个user中再按sex排序，适用于有相同的情况
| user3 | 333    | boy  |
| user4 | 444    | boy  |
| user5 | 555    | boy  |
+-------+--------+------+
8 rows in set (0.01 sec)

排序方向
数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

MariaDB [test]> select  * from linux order by user DESC,sex;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| user5 | 555    | boy  |
| user4 | 444    | boy  |
| user3 | 333    | boy  |
| user2 | 222    | girl |
| user1 | 111    | boy  |
| user1 | pass1  | girl |
| a     | passa  | boy  |
| 1     | pass1  | girl |
+-------+--------+------+
8 rows in set (0.00 sec)
MariaDB [test]> select  * from linux order by user,sex DESC;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| 1     | pass1  | girl |
| a     | passa  | boy  |
| user1 | pass1  | girl |
| user1 | 111    | boy  |
| user2 | 222    | girl |
| user3 | 333    | boy  |
| user4 | 444    | boy  |
| user5 | 555    | boy  |
+-------+--------+------+
8 rows in set (0.00 sec)

MariaDB [test]> select  * from linux order by user DESC,sex DESC;
+-------+--------+------+
| user  | passwd | sex  |
+-------+--------+------+
| user5 | 555    | boy  |
| user4 | 444    | boy  |
| user3 | 333    | boy  |
| user2 | 222    | girl |
| user1 | pass1  | girl |
| user1 | 111    | boy  |
| a     | passa  | boy  |
| 1     | pass1  | girl |
+-------+--------+------+
8 rows in set (0.00 sec)

可见 DESC 关键字在那个条件上，就会对那一列倒叙排列，其他的列仍按照默认排列。

如果想在多个列上都进行降序排序，必须对每个列指定DESC关键字。

与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。但实际上升序是默认的

在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。

ORDER BY和LIMIT的组合

MariaDB [test]> select  * from linux;
+-------+--------+------+------+
| user  | passwd | sex  | age  |
+-------+--------+------+------+
| user1 | 111    | boy  | 18   |
| user2 | 222    | girl | 23   |
| user3 | 333    | boy  | 12   |
| user4 | 444    | boy  | 22   |
| user5 | 555    | boy  | 30   |
| a     | passa  | boy  | 22   |
| 1     | pass1  | girl | 30   |
| user1 | pass1  | girl | 18   |
+-------+--------+------+------+
8 rows in set (0.00 sec)
MariaDB [test]> select  * from linux order by sex,age DESC;
+-------+--------+------+------+
| user  | passwd | sex  | age  |
+-------+--------+------+------+
| user5 | 555    | boy  | 30   |
| user4 | 444    | boy  | 22   |
| a     | passa  | boy  | 22   |
| user1 | 111    | boy  | 18   |
| user3 | 333    | boy  | 12   |
| 1     | pass1  | girl | 30   |
| user2 | 222    | girl | 23   |
| user1 | pass1  | girl | 18   |
+-------+--------+------+------+
8 rows in set (0.00 sec)

MariaDB [test]> select  * from linux order by sex,age DESC limit 1;
+-------+--------+-----+------+
| user  | passwd | sex | age  |
+-------+--------+-----+------+
| user5 | 555    | boy | 30   |
+-------+--------+-----+------+
1 row in set (0.00 sec)

就可以通过这种方式找出男生中年龄最大的了。

TIP:
order by 位于 from 关键字之后；
limit 位于 order by 关键字之后。