三、询问和排序.sql.sql

# 排序查询
/*
* 语法: SELECT 查询列表 FROM 表 WHERE 筛选条件  ORDER BY 排序列表 ASC|DESC;
* 特点
	* ASC表示升序，DESC表示的是降序。如果不写，默认是升序。
	* ORDER BY 子句中可以支持单个字段、多个字段、表达式、函数、别名
	* ORDER BY一般都是放到查询语句的最后面,LIMIT子句除外。
*/

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

# 案列二: 查询部门编号大于等于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) AS 字节长度,last_name,salary FROM employees ORDER BY LENGTH(last_name) DESC;

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

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