# 进阶3:排序查询
/*
引入:
select * from employees;
语法:
select 查询列表 from 表 【where 筛选条件】 order by 排序的列表 asc/desc
#asc升序,desc降序
【where 筛选条件】可选条件
特点:
1.#asc升序,desc降序,如果不写,默认升序
2. order by子句支持单个字段、多个字段、表达式、函数、别名
3. order by子句一般放在查询语句的最后面,limit子句除外。
*/
# 案例1:查询员工的工资,工资从高到低排序
select * from employees order by salary desc;
# 从低到高,默认升序
select * from employees order by salary asc;
#案例2:查询部门编号>=90的员工信息,按照入职时间的先后进行排序。
select * from employees where employee_id>=90 order by hiredate;
#案例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,last_name,salary from employees order by length(first_name) desc;
#案例6:查询员工信息,要求先按工资升序,再按照员工编号降序【按多个字段排序】
select * from employees order by salary asc,employee_id desc;
# 题目1:查询员工的姓名和部门号和年薪,按照年薪降序,按照姓名升序
select last_name,department_id,salary*12*(1+ifnull(commission_pct,0)) as "年薪" from employees order by 年薪 desc,last_name asc;
# 题目2:选择工资不在8000到17000的员工姓名和工资,按照工资降序
select last_name,salary from employees where not(8000<=salary and salary<=17000) order by salary asc;
# 题目3:查询邮箱中包含e的员工信息,并按照邮箱的字节数量降序,再按照部门号升序
select * from employees where email like "%e%" order by length(email) desc,department_id asc;