MySQL学习足迹记录03--ORDER BY，DESC

1.ORDER BY

为了形成对比，这里先列出不用ORDER BY排序的结果

  mysql> SELECT prod_name FROM products;  #受MySQL重回收存储空间的影响，
 #每次查询排序的结果可能不同
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

 *用ORDER BY排序
  eg:
 mysql> SELECT prod_name FROM products ORDER BY prod_price;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| Carrots        |
| Fuses          |
| Sling          |
| .5 ton anvil   |
| Oil can        |
| 1 ton anvil    |
| TNT (5 sticks) |
| Bird seed      |
| Detonator      |
| 2 ton anvil    |
| JetPack 1000   |
| Safe           |
| JetPack 2000   |
+----------------+

 *按多个列排序（先排完A，再从结果中排B)
  eg:
     mysql> SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price,prod_name;
                                                       #先按prod_price排序，再从排序结果中价格相同的部分再按prod_name排序
+----------------+---------+------------+
| prod_name      | prod_id | prod_price |
+----------------+---------+------------+
| Carrots        | FC      |       2.50 |
| TNT (1 stick)  | TNT1    |       2.50 |
| Fuses          | FU1     |       3.42 |
| Sling          | SLING   |       4.49 |
| .5 ton anvil   | ANV01   |       5.99 |
| Oil can        | OL1     |       8.99 |
| 1 ton anvil    | ANV02   |       9.99 |
| Bird seed      | FB      |      10.00 |
| TNT (5 sticks) | TNT2    |      10.00 |
| Detonator      | DTNTR   |      13.00 |
| 2 ton anvil    | ANV03   |      14.99 |
| JetPack 1000   | JP1000  |      35.00 |
| Safe           | SAFE    |      50.00 |
| JetPack 2000   | JP2000  |      55.00 |
+----------------+---------+------------+

2.指定排序方向

*默认的排序方向为升序（ASC），为了进行降序，必须用DESC关键字

  eg:
   mysql> SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price DESC;
+----------------+---------+------------+
| prod_name      | prod_id | prod_price |
+----------------+---------+------------+
| JetPack 2000   | JP2000  |      55.00 |
| Safe           | SAFE    |      50.00 |
| JetPack 1000   | JP1000  |      35.00 |
| 2 ton anvil    | ANV03   |      14.99 |
| Detonator      | DTNTR   |      13.00 |
| TNT (5 sticks) | TNT2    |      10.00 |
| Bird seed      | FB      |      10.00 |
| 1 ton anvil    | ANV02   |       9.99 |
| Oil can        | OL1     |       8.99 |
| .5 ton anvil   | ANV01   |       5.99 |
| Sling          | SLING   |       4.49 |
| Fuses          | FU1     |       3.42 |
| Carrots        | FC      |       2.50 |
| TNT (1 stick)  | TNT1    |       2.50 |
+----------------+---------+------------+
   
 *先降序，再按多个列排序
   mysql> SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price DESC,prod_name;
+----------------+---------+------------+
| prod_name      | prod_id | prod_price |
+----------------+---------+------------+
| JetPack 2000   | JP2000  |      55.00 |
| Safe           | SAFE    |      50.00 |
| JetPack 1000   | JP1000  |      35.00 |
| 2 ton anvil    | ANV03   |      14.99 |
| Detonator      | DTNTR   |      13.00 |
| Bird seed      | FB      |      10.00 |
| TNT (5 sticks) | TNT2    |      10.00 |
| 1 ton anvil    | ANV02   |       9.99 |
| Oil can        | OL1     |       8.99 |
| .5 ton anvil   | ANV01   |       5.99 |
| Sling          | SLING   |       4.49 |
| Fuses          | FU1     |       3.42 |
| Carrots        | FC      |       2.50 |
| TNT (1 stick)  | TNT1    |       2.50 |
+----------------+---------+------------+

3.ORDER BY和LIMIT的组合

*SQL语句是由子句组合成的，有些子句是必须的，而有的是可选的。

  mysql> SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
+------------+
| prod_price |
+------------+
|      55.00 |
+------------+