MySQL 3. 排序查询

MySQL 3. 排序查询

#1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
-- 1不加加奖金
select concat (first_name , last_name ) , department_id , salary*12 yearSalary  
from employees
order by yearSalary desc , concat (first_name , last_name ) ASC;

-- 2加奖金 IFNULL(expr1,expr2) expr1表达式一为null时，则取用第二个表达式expr2的值；否则用第一个表达式的值
select concat (first_name , last_name ) as 姓名, department_id ,salary*12*(1+IFNULL(commission_pct,0)) as 年薪
from employees
order by 年薪 DESC , 姓名 ASC;


#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
select CONCAT(first_name,last_name) ,salary
from employees
where salary not between 8000 and 17000
order by salary desc;

#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
select *
from employees
where email like '%e%'
order by LENGTH(email) desc,department_id asc ;

mysql文件：https://download.youkuaiyun.com/download/qq_52102663/85172984