1.3.4.4 排序

在之前的很多例子中，我们注意到检索的结果是没有排序的。通常，当结果中按照某种方式排序时，更容易、更直观的看到检索结果。对结果排序，我们需要使用 ORDER BY 语法。

这里对宠物的出生日期 ，进行日期排序：

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------------------+------------+
| name                 | birth      |
+----------------------+------------+
| Buffy                | 1989-05-13 |
| Bowser               | 1989-08-30 |
| Fang                 | 1990-08-27 |
| Fluffy               | 1993-02-04 |
| Claws                | 1994-03-17 |
| Slim                 | 1996-04-29 |
| Whistler             | 1997-12-09 |
| Chirpy               | 1998-09-11 |
| Puffball             | 1999-03-30 |
+----------------------+------------+
9 rows in set (0.00 sec)

Note

在字符类型的数据列上，排序通常和字符比较一样，不区分大小写。如果需要大小写敏感排序，同样可以使用 binary ，例如：ORDER BY BINARY col_name

mysql> SELECT name, birth FROM pet
    -> ORDER BY binary name;
+----------------------+------------+
| name                 | birth      |
+----------------------+------------+
| Bowser               | 1989-08-30 |
| Buffy                | 1989-05-13 |
| Chirpy               | 1998-09-11 |
| Claws                | 1994-03-17 |
| Fang                 | 1990-08-27 |
| Fluffy               | 1993-02-04 |
| Puffball             | 1999-03-30 |
| Slim                 | 1996-04-29 |
| Whistler             | 1997-12-09 |
+----------------------+------------+
9 rows in set (0.00 sec)

排序默认是按照升序排序，最小值在第一行。如果要使用降序排序，那么必需增加关键字 DESC ;

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------------------+------------+
| name                 | birth      |
+----------------------+------------+
| Puffball             | 1999-03-30 |
| Chirpy               | 1998-09-11 |
| Whistler             | 1997-12-09 |
| Slim                 | 1996-04-29 |
| Claws                | 1994-03-17 |
| Fluffy               | 1993-02-04 |
| Fang                 | 1990-08-27 |
| Bowser               | 1989-08-30 |
| Buffy                | 1989-05-13 |
+----------------------+------------+
9 rows in set (0.00 sec)

对多个数据列进行排序：

mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;
+----------------------+---------+------------+
| name                 | species | birth      |
+----------------------+---------+------------+
| Chirpy               | bird    | 1998-09-11 |
| Whistler             | bird    | 1997-12-09 |
| Claws                | cat     | 1994-03-17 |
| Fluffy               | cat     | 1993-02-04 |
| Fang                 | dog     | 1990-08-27 |
| Bowser               | dog     | 1989-08-30 |
| Buffy                | dog     | 1989-05-13 |
| Puffball             | hamster | 1999-03-30 |
| Slim                 | snake   | 1996-04-29 |
+----------------------+---------+------------+
9 rows in set (0.00 sec)