（MySQL学习笔记）排序查询

排序查询

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

特点：
1、ASC为升序，DESC为降序；如果不写，则默认升序
2、order by字句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by子句一般是放在查询语句的最后面（limit子句除外）
*/

#例：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#从低到高
SELECT * FROM employees ORDER BY salary ASC;
#asc可以省略
SELECT * FROM employees ORDER BY salary;

#例：查询部门编号>=90的员工信息，按入职时间的先后进行排序
SELECT
	*
FROM
	employees
WHERE 
	department_id>=90
ORDER BY
	hiredate
ASC;

#例：【按表达式排序】按年薪高低显示员工的信息和年薪
SELECT 
	*,salary*12*(1+IFNULL(commission_pct,0)) AS '年薪'
FROM
	employees
ORDER BY 
	salary*12*(1+IFNULL(commission_pct,0))
DESC;

#例：【按别名排序】按年薪高低显示员工的信息和年薪
SELECT 
	*,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;

#例：【先按工资排序，再按员工编号排序】查询员工信息
SELECT
	*
FROM 
	employees
ORDER BY
	salary DESC,employee_id ASC;

#例：查询员工的姓名和部门号和年薪，按年薪降序，姓名升序
SELECT
	last_name,
	department_id,
	salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM 
	employees
ORDER BY 
	年薪 DESC,department_id ASC;

#例：选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT 
	last_name,
	salary
FROM 
	employees
WHERE
	NOT(salary BETWEEN 8000 AND 17000)
ORDER BY 
	salary DESC;

#例：查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT
	*
FROM 
	employees
WHERE
	email LIKE '%e%'
ORDER BY 
	LENGTH(email) DESC,
	department_id ASC;