9.排序查询（select from where order by（asc|desc））

进阶三:排序查询

引入:
	SELECT * FROM 表名;
/*
	语法:
		select * 
		from table 
		where 筛选条件 
		order by 排序列表 asc|desc(默认升序)
	支持多个字段,单个字段,表达式,函数,别名
	order by一般放在最后, limit子句除外
*/

# 案例: 查询员工信息从薪水高到低显示
#select * from table order by salary desc
SELECT * FROM employees ORDER BY salary ASC;

# 案例2:查询部门编号>=90的员工信息,按入职时间的先后进行排序;
# where与order by共用
SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;

# 案例薪水降序[按表达式排序]
(salary*12*(1+IFNULL(commission_pct,0))解析：ifnull：如果是null的话，用0代替提成：如果没有薪水的话乘以0，有的话乘以1+提成
SELECT * 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 *,(salary*12*(1+IFNULL(commission_pct,0))) AS 年薪 FROM employees ORDER BY  (salary*12*(1+IFNULL(commission_pct,0))) DESC;

# 案例:根据姓名长度排序：使用函数length判断长度，concat来进行字符串连接
SELECT * FROM employees ORDER BY LENGTH(CONCAT(first_name,last_name)) DESC;

# 先按工资排序,再按员工编号排序
SELECT * FROM employees ORDER BY salary DESC, employee_id ASC;

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

# 选择工资不再8000到17000的员工的姓名和工资,按工资降序
SELECT last_name, salary FROM employees WHERE !(salary >= 8000 AND salary <= 17000) ORDER BY salary DESC;
SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;

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