当前位置：首页 > article >正文

【 MySQL 学习4】排序

article 2025/1/23 12:11:04

1.1 升序

默认是升序，如果要制定，增加asc关键字

mysql> select ename,sal from emp  order by sal asc
    -> ;
+--------+
| ename  |
+--------+
| SMITH  |
| JAMES  |
| ADAMS  |
| WARD   |
| MARTIN |
| MILLER |
| TURNER |
| ALLEN  |
| CLARK  |
| BLAKE  |
| JONES  |
| SCOTT  |
| FORD   |
| KING   |
+--------+
14 rows in set (0.00 sec)

1.2 降序

mysql> select ename, sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

1.3 按照多个字段排序

mysql> select ename, sal
    -> from emp order by sal asc, ename asc; // sal在前主导，相等时才会启动ename
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

1.4 根据字段的位置进行排序

按照查询结果的第2列进行排序，
不建议这样写，因为列的顺序很容易发生改变。

mysql> select ename, sal from emp order by 2; // 2表示第2列，第二列是sal
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

1.5 找出在1250-3000之间，按照降序排列

关键字的顺序不能变

select
	...
from 
	...
where
	...
order by (排序总是在最后执行)
	...

mysql> select ename, sal from emp
    -> where sal between 1250 and 3000
    -> order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+
10 rows in set (0.00 sec)