#三.排序查询
/*
1.语法:
select 查询列表 from 表 where 筛选条件 order by 排序列表;
注意:
1).升序:默认的asc 降序:desc
2).排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2.执行顺序:
from -> where -> select -> order by
*/
#3.案例
-- 一、按单个字段排序
#案例1:将员工编号>120的员工信息进行工资的升序
select *
from employees
where employee_id>120
order by salary;
#案例2:将员工编号>120的员工信息进行工资的降序
select *
from employees
where employee_id>120
order by salary desc;
-- 二、按表达式排序
#案例1:对有奖金的员工,按年薪降序
select first_name , salary , commission_pct , salary*12 '年薪'
from employees
where commission_pct is not null
order by salary*12 desc;
#案例2:对有奖金的员工,按总收入降序
select first_name , salary , commission_pct , salary*(1+IFNULL(commission_pct,0))*12 '总收入'
from employees
where commission_pct is not null
order by salary*(1+IFNULL(commission_pct,0))*12 desc;
-- 三、按别名排序
#案例:对有奖金的员工,按年薪降序
select first_name , salary , commission_pct , salary*12 '年薪'
from employees
where commission_pct is not null
order by '年薪' desc;
-- 四、按函数的结果排序
#案例:按姓名的字符数长度进行升序
select first_name , char_length(first_name)
from employees
order by char_length(first_name) ;
-- 五、按多个字段排序
#案例:查询员工的姓名、工资、部门编号,先按工资升序,再按部门编号降序
select first_name , salary , department_id
from employees
order by salary , department_id desc;
-- 六、按列数排序(不做要求)
#案例:按第二列排序
select * from employees order by 2;
#4.作业
-- 1. 查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
select first_name , salary*12 '年薪'
from employees
ORDER BY salary*12 desc,first_name;
-- 2. 选择工资不在 8000 到 17000 的员工的姓名和工资,按工资降序
select first_name , salary
from employees
where salary<8000 or salary>17000
ORDER BY salary desc;
-- 3. 查询邮箱中包含 e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
select *
from employees
where email like '%e%'
ORDER BY LENGTH(email) desc , department_id asc;
MySQL(3.排序查询)
最新推荐文章于 2024-10-02 06:14:43 发布