MySQL的DQL语言：3、排序查询

排序查询

语法：
select
	查询列表
from
	表
where
	筛选条件
order by 
	排序列表 asc | desc

说明：
	1、asc 升序、desc 降序 ，若不写，则默认为升序
	2、order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
	3、order by 子句一般时放在查询语句的组后面，limit子句除外

案例1：查询员工信息，要求工资从高到低排序

select last_name,salary from employees order by salary desc;

案例2：查询部门编号 >= 90 的员工信息，要求按入职时间先后排序【增加了筛选条件】

select
	last_name,department_id,hiredate
from
	employees
where
	department_id>=90
order by
	hiredate asc;

案例3：按年薪的高低显示员工的姓名和年薪【按表达式排序】

select
	last_name,salary*12*(1+ifnull(commission_pct,0)) as 年薪
from
	employees
order by salary*12*(1+ifnull(commission_pct,0)) desc;

案例3：按年薪的高低显示员工的姓名和年薪【按别名排序】

select
	last_name,salary*12*(1+ifnull(commission_pct,0)) as 年薪
from
	employees
order by 年薪 desc;

按姓名的长度显示员工的姓名和工资【按函数排序】

select 
	length(last_name) 字节长度 ,last_name,salary
from
	employees
order by length(last_name) desc;

案例6：查询员工信息，要求先按工资升序排序，再按员工编号降序排序【按多个字段排序】

select
	last_name,salary,employee_id
from
	employees
order by
	salary asc,employee_id desc;

测 试

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

select 
	last_name,department_id,salary*12*ifnull(commission_pct,0) 年薪
from
	employees
order by
	salary*12*ifnull(commission_pct,0) desc,last_name asc;

2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

select
	last_name,salary
from
	employees
where
	not (salary between 8000 and 17000)
order by
	salary desc;

3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

select
	*
from
	employees
where
	email like '%e%'
order by
	length(email) desc,department_id asc;