MySQL学习足迹记录03--ORDER BY,DESC
来源:互联网 发布:qq音乐for mac 10.7.5 编辑:程序博客网 时间:2024/05/17 07:55
排序所用到的表格数据,详见:MySQL学习足迹记录02
1.ORDER BY
为了形成对比,这里先列出不用ORDER BY排序的结果
| 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 |
+------------+
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 |
+------------+
- MySQL学习足迹记录03--ORDER BY,DESC
- mysql:sql order by */* desc (查询)
- MySQL学习足迹记录11--分组数据--GROUP BY,HAVING
- mysql实现ROW_NUMBER() over (PARTITION BY xx ORDER BY ** DESC)
- sql order by,desc和limit使用(mysql)
- Mysql| order by 排序检索数据(ASC,DESC)
- ORDER BY xxx DESC/ASC
- mysql 实现oracle里面row_number() OVER (PARTITION BY cid,author ORDER BY id DESC) 的方法
- MySQL替换oracle里ROW_NUMBER () OVER ( PARTITION BY '' ORDER BY ' ' DESC )的方法
- MySQL学习足迹记录01--SOURCE,SHOW
- MySQL学习足迹记录02--SELECT
- SQL函数学习 之 DENSE_RANK() OVER (PARTITION BY col2 ORDER BY col3 DESC) AS seq
- row_number() over(order by col2 desc)用法
- sql2008 order by desc 查询速度慢
- 排序用法 order by asc desc
- row_number() over(partition by a order by b desc) rn
- row_number() over (partition by idno order by id desc)
- row_number() over(partition by a order by b desc) 用法
- 代码解决免费空间时出现乱码的问题
- JAVA从零单排0-----前因
- 实例说明FTP无法列表的原因及解决方式
- 1794. Masterpieces of World Architecture
- 阻截木马新方法 IP安全策略出招
- MySQL学习足迹记录03--ORDER BY,DESC
- gcc同时使用动态和静态链接
- wcf 看不到Edit WCF Configuration的操作方法
- Windows下多线程编程指南 .
- 3des加密中- pkcs#5和pkcs#7填充的区别
- Linux 远程连接工具 【vnc viewer】 图形界面
- 有道单词-批量导入[只有单词]
- 纸上得来终觉浅 绝知此事要躬行
- C语言基础学习(1)