MySQL入门命令之排序

降序排序

 select * from employees order by desc;

升序排序

 select * from employees order by asc;
 #或
mysql> select *
    -> from employees
    -> where department_id>=90
    -> order by hiredate asc;

查询符合条件的int形，可以使用下划线作为通配符

mysql> select *
    -> from employees
    -> where department_id like '1__';

支持使用别名进行排序

select *,salary*12*(1+ifnull(commission_pct,0)) as 年薪 from employees order by 年薪 asc;
#两种方式等效
select *,salary*12*(1+ifnull(commission_pct,0)) as 年薪 from employees order by salary*12*(1+ifnull(commission_pct,0)) asc;

按姓名的长度显示员工的信息

mysql> select length('john');
+----------------+
| length('john') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)
#实例，未全部展示
mysql> select length(last_name) as 字节长度,last_name,salary from employees order by 字节长度 desc;
+----------+-------------+----------+
| 字节长度 | last_name   | salary   |
+----------+-------------+----------+
|       11 | Mikkilineni |  2700.00 |
|       10 | Colmenares  |  2500.00 |
|       10 | Philtanker  |  2200.00 |
|       10 | Livingston  |  8400.00 |
|        9 | Pataballa   |  4800.00 |
|        9 | Greenberg   | 12000.00 |

多个排序条件进行排序

mysql> select last_name,salary,employee_id from employees order by salary asc,employee_id desc;
+-------------+----------+-------------+
| last_name   | salary   | employee_id |
+-------------+----------+-------------+
| Olson       |  2100.00 |         132 |
| Philtanker  |  2200.00 |         136 |
| Markle      |  2200.00 |         128 |
| Gee         |  2400.00 |         135 |
| Landry      |  2400.00 |         127 |
| Perkins     |  2500.00 |         191 |
| Sullivan    |  2500.00 |         182 |
| Vargas      |  2500.00 |         144 |
| Patel       |  2500.00 |         140 |
| Marlow      |  2500.00 |         131 |
| Colmenares  |  2500.00 |         119 |
| Grant       |  2600.00 |         199 |
| OConnell    |  2600.00 |         198 |
| Matos       |  2600.00 |         143 |
| Himuro      |  2600.00 |         118 |
| Seo         |  2700.00 |         139 |
| Mikkilineni |  2700.00 |         126 |
| Jones       |  2800.00 |         195 |
| Geoni       |  2800.00 |         183 |
| Atkinson    |  2800.00 |         130 |
| Tobias      |  2800.00 |         117 |
| Gates       |  2900.00 |         190 |
| Rogers      |  2900.00 |         134 |
| Baida       |  2900.00 |         116 |
| Feeney      |  3000.00 |         197 |
| Cabrio      |  3000.00 |         187 |
| Walsh       |  3100.00 |         196 |
| Fleaur      |  3100.00 |         181 |
| Davies      |  3100.00 |         142 |
| Khoo        |  3100.00 |         115 |
| McCain      |  3200.00 |         194 |
| Taylor      |  3200.00 |         180 |
| Stiles      |  3200.00 |         138 |
| Nayer       |  3200.00 |         125 |
| Mallin      |  3300.00 |         133 |
| Bissot      |  3300.00 |         129 |
| Dellinger   |  3400.00 |         186 |
| Rajs        |  3500.00 |         141 |
| Dilly       |  3600.00 |         189 |
| Ladwig      |  3600.00 |         137 |
| Chung       |  3800.00 |         188 |
| Everett     |  3900.00 |         193 |
| Bell        |  4000.00 |         192 |
| Bull        |  4100.00 |         185 |
| Sarchand    |  4200.00 |         184 |
| Lorentz     |  4200.00 |         107 |

更多MySQL命令