【超详细】MySQL学习笔记汇总(四)之排序查询
MySQL学习笔记汇总(四)
五、进阶3:排序查询
select * from employees;
select 查询列表 from 表 [where 筛选条件] order by 排序列表 [asc|desc]
支持:
①:表达式
②:别名
③:函数名
④:多个字段排序
执行顺序:
- 一般放在查询语句的最后面,但limit字句除外是在最后面
- from 表-》where 字句-》select 字段-》order by 字句
案例1:查询员工信息,要求工资从高到低排序
select * from employees order by salary asc;#升序 select * from employees order by salary desc;
案例2:查询部门编号>=员工信息,按入职时间的先后进行排序
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 sql employees order by 姓名长度 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)) 年薪 FROM employees order by 年薪 desc,last_name asc;
2、选择工资不在8000 到17000 的员工的姓名和工资,按工资降序
select last_name, salary from employees where not (salary between 8000 and 17000) order by salary desc;
3、查询邮箱中包含e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
select *, LENGTH(email) 邮箱字节长度 from employees where email like '%e%' order by LENGTH(email) desc,department_id asc;