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 | +------------+
