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