4-10：order by语句对查询结果排序

对查询结果进行排序的语法结构：

[ORDER BY {col_name | expr | position}[ASC | DESC],...]

ASC：升序（默认），DESC：降序，比如我们按照id降序排序：

mysql> SELECT * FROM users ORDER BY id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  9 | 111      | 222                              |  33 | NULL |
|  8 | BEn      | 456                              |  17 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  5 | John     | 123                              |  10 |    0 |
|  4 | John     | 123                              |  35 |    0 |
|  3 | Tom      | 123                              |  27 |    0 |
|  2 | John     | 123                              |  38 |    0 |
|  1 | Tom      | 123                              |  27 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

如果对多个字段进行排序时，如果第一个字段可以排出想要的结果，则忽略其他字段，如果不能，则遵守第二个字段，依次类推。

首先按照年龄默认排序：

mysql> SELECT * FROM users ORDER BY age;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  5 | John     | 123                              |  10 |    0 |
|  8 | BEn      | 456                              |  17 |    0 |
|  1 | Tom      | 123                              |  27 |    0 |
|  3 | Tom      | 123                              |  27 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
|  4 | John     | 123                              |  35 |    0 |
|  2 | John     | 123                              |  38 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

可以看到有同龄的用户，我们可以设置如果同龄，则按照id降序排列：

mysql> SELECT * FROM users ORDER BY age,id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  5 | John     | 123                              |  10 |    0 |
|  8 | BEn      | 456                              |  17 |    0 |
|  3 | Tom      | 123                              |  27 |    0 |
|  1 | Tom      | 123                              |  27 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
|  4 | John     | 123                              |  35 |    0 |
|  2 | John     | 123                              |  38 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)