默认排序:
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
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