SQL基础（一一）--排序

默认排序：

mysql> select * from t_employee;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| DEV001   | Tom   |   25 | 8300    |
| DEV002   | Jerry |   28 | 2300.8  |
| HR001    | Jane  |   23 | 2200.88 |
| HR002    | Tina  |   25 | 5200.36 |
| IT001    | Smith |   28 | 3900    |
| IT002    | NULL  |   27 | 2800    |
| SALES001 | John  |   23 | 5000    |
| SALES002 | Kerry |   28 | 6200    |
| SALES003 | Stone |   22 | 1200    |
+----------+-------+------+---------+
9 rows in set

按照年龄升序排序1：

mysql> select * from t_employee order by fage asc;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| SALES003 | Stone |   22 | 1200    |
| HR001    | Jane  |   23 | 2200.88 |
| SALES001 | John  |   23 | 5000    |
| DEV001   | Tom   |   25 | 8300    |
| HR002    | Tina  |   25 | 5200.36 |
| IT002    | NULL  |   27 | 2800    |
| DEV002   | Jerry |   28 | 2300.8  |
| IT001    | Smith |   28 | 3900    |
| SALES002 | Kerry |   28 | 6200    |
+----------+-------+------+---------+
9 rows in set

按照年龄升序排序2：

mysql> select * from t_employee order by fage;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| SALES003 | Stone |   22 | 1200    |
| HR001    | Jane  |   23 | 2200.88 |
| SALES001 | John  |   23 | 5000    |
| DEV001   | Tom   |   25 | 8300    |
| HR002    | Tina  |   25 | 5200.36 |
| IT002    | NULL  |   27 | 2800    |
| DEV002   | Jerry |   28 | 2300.8  |
| IT001    | Smith |   28 | 3900    |
| SALES002 | Kerry |   28 | 6200    |
+----------+-------+------+---------+
9 rows in set

年龄按照降序排序：

mysql> select * from t_employee order by fage desc;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| DEV002   | Jerry |   28 | 2300.8  |
| IT001    | Smith |   28 | 3900    |
| SALES002 | Kerry |   28 | 6200    |
| IT002    | NULL  |   27 | 2800    |
| DEV001   | Tom   |   25 | 8300    |
| HR002    | Tina  |   25 | 5200.36 |
| HR001    | Jane  |   23 | 2200.88 |
| SALES001 | John  |   23 | 5000    |
| SALES003 | Stone |   22 | 1200    |
+----------+-------+------+---------+
9 rows in set

年龄和工资都按照降序排序：

mysql> select * from t_employee order by fage desc, fsalary desc;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| SALES002 | Kerry |   28 | 6200    |
| IT001    | Smith |   28 | 3900    |
| DEV002   | Jerry |   28 | 2300.8  |
| IT002    | NULL  |   27 | 2800    |
| DEV001   | Tom   |   25 | 8300    |
| HR002    | Tina  |   25 | 5200.36 |
| SALES001 | John  |   23 | 5000    |
| HR001    | Jane  |   23 | 2200.88 |
| SALES003 | Stone |   22 | 1200    |
+----------+-------+------+---------+
9 rows in set

将年龄大于23的员工按照年龄和工资降序排序：

mysql> select * from t_employee
    -> where fage>23
    -> order by fage desc, fsalary desc;
+----------+-------+------+---------+
| fnumber  | fname | fage | fsalary |
+----------+-------+------+---------+
| SALES002 | Kerry |   28 | 6200    |
| IT001    | Smith |   28 | 3900    |
| DEV002   | Jerry |   28 | 2300.8  |
| IT002    | NULL  |   27 | 2800    |
| DEV001   | Tom   |   25 | 8300    |
| HR002    | Tina  |   25 | 5200.36 |
+----------+-------+------+---------+
6 rows in set