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

bitsCN.com

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

bitsCN.com