mysql——排序查询

#进阶3：排序查询
/*
语法：
	select 查询列表
	from 表
	where 筛选条件
	order by 排序列表 asc/desc(不写默认升序)
	
特点：
	1.order by可支持单个字段，多个字段，表达式，函数，别名
	2.order by子句一般放在查询语句最后，但limit子句除外
*/


#1.案例：查询员工信息，要求工资从高到低排序
SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC ;



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


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


#4.按别名排序
SELECT *,salary*12*(1+IFNULL(`commission_pct`,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;


#5.按函数排序：按姓名的长度显示员工的姓名和工资
SELECT LENGTH(last_name) 字节长度 ,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;


#6.按多个字段排序:查询员工信息，要求先按工资升序，再按员工编号降序
SELECT *
FROM employees
ORDER BY `salary` ASC, `employee_id` DESC;


#练习
SELECT `last_name`,`department_id`,salary*12*(1+IFNULL(`commission_pct`,0)) 年薪
FROM employees;
ORDER BY 年薪 DESC, `last_name` ASC;

SELECT `last_name`,`salary`
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(`email`) DESC, `department_id` ASC;