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