排序数据
使用ORDER BY
子句,根据其后面跟的列名,对输出结果进行排序
下述命令的含义是,输出PROD_NAME列数据的同时,以字母升序的排序方法对输出进行排序
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 |
+---------------------+
按多个列排序
检索3个列,并根据其中2个列进行输出排序
ORDER BY
子句会优先根据第一个列名进行输出排序,只有当多行出现同一个值时,才会根据第二个列名进行输出排序
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 |
+---------+------------+---------------------+
按列位置排序
ORDER BY
子句除了支持按照列名排序,还支持按照列相对位置排序
ORDER BY 2,3
表示的是,根据SELECT清单中的第二列和第三列排序,先第二列,再第三列
当要按照不在SELECT清单中的列名排序时,不能使用这种排序方法
mysql> SELECT PROD_ID,PROD_PRICE,PROD_NAME FROM PRODUCTS
-> ORDER BY 2,3;
+---------+------------+---------------------+
| 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 |
+---------+------------+---------------------+
指定排序方向
数据排序默认是ASC升序(A-Z)
也可以降序DESC(Z-A)
mysql> SELECT PROD_ID,PROD_PRICE,PROD_NAME FROM PRODUCTS
-> ORDER BY PROD_PRICE DESC;
+---------+------------+---------------------+
| 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 |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
注意:
如果想要多个列都进行降序排序,必须要每个列后指定DESC关键字