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

页面导航:首页 > 数据库 > Mysql > MySQL学习足迹记录03--ORDER BY,DESC

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

来源: 作者: 时间:2016-02-18 10:28 【

MySQL学习足迹记录03--ORDER BY,DESC1.ORDER BY 为了形成对比,这里先列出不用ORDER BY排序的结果 mysql SELECT prod_name FROM products; #受MySQL重回收存储空间的影响, #每次查询排序的结果...
MySQL学习足迹记录03--ORDER BY,DESC
 
1.ORDER BY
  为了形成对比,这里先列出不用ORDER BY排序的结果
  > 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 |
+------------+

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<