排序查询
USE `myemployees`;
#排序查询
/*
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
asc是升序,desc是降序
不写默认是升序
order by 子句一般是放在查询语句的最后面,limit子句除外
*/
#查询员工信息,工资从高到低排序
SELECT * FROM `employees` ORDER BY `salary` DESC;
#查询员工信息,工资从低到高排序
SELECT * FROM `employees` ORDER BY `salary` ASC;
#部门编号>=90的员工信息,按照入职先后顺序
SELECT *
FROM `employees`
WHERE `department_id`>=90
ORDER BY `hiredate` ASC;
#按年薪的高低显示员工信息和年薪【按表达式查询】
SELECT *,`salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM `employees`
ORDER BY `salary`*12*(1+IFNULL(`commission_pct`,0)) DESC;
#按年薪的高低显示员工信息和年薪【按别名查询】
SELECT *,`salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM `employees`
ORDER BY 年薪 DESC;
#按名字长度显示员工的姓名和工资【函数排序】
SELECT LENGTH(`last_name`) AS 字节长度,`last_name`,`salary`
FROM `employees`
ORDER BY LENGTH(`last_name`) DESC;
#查询员工信息,先按工资降序,再按入职时间先后
SELECT *
FROM `employees`
ORDER BY `salary` DESC,`hiredate` ASC;
#查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT *,LENGTH(`email`) AS 邮箱长度
FROM `employees`
WHERE email LIKE '%e%'
ORDER BY LENGTH(`email`) DESC,`department_id` ASC;