1.排序数据

使用SELECT 的order by子句可以对查询结果进行排序。

确保ORDER BY子句在SELECT语句的最后，否则会出现错误。

示例：

查询产品名称并以字母顺序排序：

mysql> SELECT prod_name from Products ORDER BY prod_name;  +---------------------+| prod_name           |+---------------------+| 12 inch teddy bear  || 18 inch teddy bear  || 8 inch teddy bear   || Bird bean bag toy   || Fish bean bag toy   || King doll           || Queen doll          || Rabbit bean bag toy || Raggedy Ann         |+---------------------+9 rows in set (0.00 sec)

以价格升序排序：

mysql> SELECT prod_name,prod_price from Products ORDER BY prod_price;  +---------------------+------------+| prod_name           | prod_price |+---------------------+------------+| Fish bean bag toy   |       3.49 || Bird bean bag toy   |       3.49 || Rabbit bean bag toy |       3.49 || Raggedy Ann         |       4.99 || 8 inch teddy bear   |       5.99 || 12 inch teddy bear  |       8.99 || King doll           |       9.49 || Queen doll          |       9.49 || 18 inch teddy bear  |      11.99 |+---------------------+------------+9 rows in set (0.00 sec)

价格降序排序：

mysql> SELECT prod_name,prod_price FROM  Products ORDER BY prod_price DESC;  +---------------------+------------+| prod_name           | prod_price |+---------------------+------------+| 18 inch teddy bear  |      11.99 || King doll           |       9.49 || Queen doll          |       9.49 || 12 inch teddy bear  |       8.99 || 8 inch teddy bear   |       5.99 || Raggedy Ann         |       4.99 || Fish bean bag toy   |       3.49 || Bird bean bag toy   |       3.49 || Rabbit bean bag toy |       3.49 |+---------------------+------------+9 rows in set (0.00 sec)

2.按多个列排序

按多个列排序的时候，先按第一列排序，第一列中有相同值，相同值的记录再按第二列排序；如果第一列都是唯一的，则不再按第二列排序。如下示例：

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY prod_price,prod_name;  +---------+------------+---------------------+| prod_id | prod_price | prod_name           |+---------+------------+---------------------+| BNBG02  |       3.49 | Bird bean bag toy   || BNBG01  |       3.49 | Fish bean bag toy   || BNBG03  |       3.49 | Rabbit bean bag toy || RGAN01  |       4.99 | Raggedy Ann         || BR01    |       5.99 | 8 inch teddy bear   || BR02    |       8.99 | 12 inch teddy bear  || RYL01   |       9.49 | King doll           || RYL02   |       9.49 | Queen doll          || BR03    |      11.99 | 18 inch teddy bear  |+---------+------------+---------------------+9 rows in set (0.00 sec)

上面查询先依据价格排序，价格相同的按prod_name字典顺序排序。

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY 2,3;

使用位置代表列名，优点是不用重新输入列名，缺点是列名更改时会造成错误。

升序和降序

ORDER BY查询默认是升序排序，降序可以加上DESC,比如按价格由高到低排序：

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY prod_price DESC,prod_name;  +---------+------------+---------------------+| prod_id | prod_price | prod_name           |+---------+------------+---------------------+| BR03    |      11.99 | 18 inch teddy bear  || RYL01   |       9.49 | King doll           || RYL02   |       9.49 | Queen doll          || BR02    |       8.99 | 12 inch teddy bear  || BR01    |       5.99 | 8 inch teddy bear   || RGAN01  |       4.99 | Raggedy Ann         || BNBG02  |       3.49 | Bird bean bag toy   || BNBG01  |       3.49 | Fish bean bag toy   || BNBG03  |       3.49 | Rabbit bean bag toy |+---------+------------+---------------------+9 rows in set (0.00 sec)

DESC只对在它前面的一个列名有作用，mysql> SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name DESC;对prod_price无效：

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY prod_price,prod_name DESC;  +---------+------------+---------------------+| prod_id | prod_price | prod_name           |+---------+------------+---------------------+| BNBG03  |       3.49 | Rabbit bean bag toy || BNBG01  |       3.49 | Fish bean bag toy   || BNBG02  |       3.49 | Bird bean bag toy   || RGAN01  |       4.99 | Raggedy Ann         || BR01    |       5.99 | 8 inch teddy bear   || BR02    |       8.99 | 12 inch teddy bear  || RYL02   |       9.49 | Queen doll          || RYL01   |       9.49 | King doll           || BR03    |      11.99 | 18 inch teddy bear  |+---------+------------+---------------------+9 rows in set (0.00 sec)