排序查询
排序查询导图
格式
- order by 排序列表 [asc/desc](升序、降序)
select [查询列表] from [表[] where [筛选条件] order by 排序列表 [asc/desc](升序、降序) - 特点:
- 升序:小的在前,降序:大的再前
- asc:升序,desc:降序,如果不写默认是升序
- order by 子句支持单个字段、多的字段、表达式、函数、别名
- order by 子句一般放在查询语句的最后面
例题
-
查询员工信息要求工资从高到低排序
select * from employess order by salary desc ;
-
查询部门编号>=90的员工信息按入职时间的先后进行排序。
select * from employees where department_id>=90 order by hiredate asc;
-
按年薪高低显示员工的信息和年薪,按表达式排序。
select * ,salary * 12 *(1+ifnull(commission_pct,0))
from emplouees
order by salary * 12 *(1+ifnull(commission_pct,0)) desc;
-
按姓名的长度显示员工的姓名和工资,按函数排序。
select length(last_name) as 字节长度 , last_name , salary
form employees
order by lenath(last_name) desc;
-
按年薪的高低显示员工的信息和年薪,按别名排序。
select * , salary * 12 *(1+ifnull(commission_pct,0)) as 年薪
form employees
order by length(last_name) desc
-
查询员工信息要求先按工资排序,再按员工编号排序,(按多个字段排序)
select * from employees
order by salary asc , employees_id desc;
-
查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
select * , salary * 12 *(1+ifnull(commission_pct,0)) as 年薪
from employees
order by 年薪 desc, last_name asc ;
-
选择工资不在 8000 到 17000 的员工的姓名和工资,按工资降序
select last_name,salary
form employess
where salary nor between 8000 ans 17000
order by salary desc;
-
查询邮箱中包含 e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
select * from employees
where email like '%e%'
order by length(email desc, department_id asc);