ORDER BY 关键词用于对记录集中的数据进行排序。
语法
SELECT column_name(s)
FROM table_name
ORDER BY column_name
注释:SQL 对大小写不敏感。ORDER BY 与 order by 等效。
常见的三种用法:
降序:[desc]
升序:[asc]
多列排序:排序列与列之间逗号隔开.
升序或降序的排序
如果您使用 ORDER BY 关键词,记录集的排序顺序默认是升序(1 在 9 之前,"a" 在 "p" 之前)。
请使用 DESC 关键词来设定降序排序(9 在 1 之前,"p" 在 "a" 之前):
可以根据多个列进行排序。当按照多个列进行排序时,只有第一列相同时才使用第二列:
SELECT column_name(s)
FROM table_name
ORDER BY column_name1, column_name2
列子
mysql中排序默认为升序[asc]:
mysql> select * from sc;
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
| 1 | K1 | 83 |
| 2 | K1 | 85 |
| 2 | K5 | 90 |
| 5 | K1 | 92 |
| 5 | K5 | 84 |
| 5 | K8 | 80 |
+-----+-----+-------+
6 rows in set (0.00 sec)
选取sc表中的数据,根据“score”排序:
mysql> select * from sc order by score;
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
| 5 | K8 | 80 |
| 1 | K1 | 83 |
| 5 | K5 | 84 |
| 2 | K1 | 85 |
| 2 | K5 | 90 |
| 5 | K1 | 92 |
+-----+-----+-------+
6 rows in set (0.00 sec)
降序排列[desc],在关键列后加desc:
mysql> select * from sc order by score desc;
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
| 5 | K1 | 92 |
| 2 | K5 | 90 |
| 2 | K1 | 85 |
| 5 | K5 | 84 |
| 1 | K1 | 83 |
| 5 | K8 | 80 |
+-----+-----+-------+
6 rows in set (0.00 sec)
多列排序:
以年龄排序遇到名字拼音相同,单行排不出来时,采用多行排序:
mysql> select * from student order by age;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 4 | NULL | 10 | NULL |
| 2 | 刘丽 | 22 | 女 |
| 5 | 张友 | 22 | 男 |
| 6 | 刘力 | 22 | 男 |
| 1 | 换换 | 23 | 男 |
+-----+--------+------+------+
5 rows in set (0.00 sec)
以年龄升序排列完,再以sno倒叙排列.
mysql> select * from student order by age,sno desc;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 4 | NULL | 10 | NULL |
| 6 | 刘力 | 22 | 男 |
| 5 | 张友 | 22 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 1 | 换换 | 23 | 男 |
+-----+--------+------+------+
5 rows in set (0.00 sec)
转载于:https://blog.51cto.com/215687833/2339172