进阶三：排序查询
USE myemployees;
SELECT * FROM employees;
语法：
SELECT 查询列表
FROM 表
【WHERE 筛选条件】
ORDER BY 排序列表 【ASC|DESC】
如果不写，默认是升序
案例1：查询员工的信息，要求按照工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary;
案例2：查询部门编号》=90的员工信息，按入职时间进行排序【添加筛选条件】
SELECT *
FROM employees
WHERE department_id >=90
ORDER BY hiredate ASC;
案例3：按年薪高低显示员工的信息和年薪【按照表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
案例4：按年薪高低显示员工信息和年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
/* ‘年薪’ 就是起的别名*/
案例5：按姓名的长度显示员工的姓名和工资【使用函数排序】
SELECT LENGTH(first_name) 字节长度,first_name,salary
FROM employees
ORDER BY LENGTH(first_name) DESC;
案例6:查询员工信息，先按员工工资升序排序，再按员工编号降序排序
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC; /*即：工资从低到高，工资相同时，再按照编号排序*/
测试题目：
1.查询员工的姓名、部门号、年薪，按照年薪降序，按照姓名升序
2.选择工资不在8000-17000的员工的姓名和工资，按工资降序，并显示是为“姓名”,“工资”两列
3.查询邮箱中包含e的员工信息，并先按照邮箱的字节数降序，再按照部门号升序
测试题答案：
1.
SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,first_name ASC;
2.
SELECT first_name AS 姓名,salary AS 工资
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
3.
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;