进阶3:排序查询
/*
语法:
select
查询列表③
from
表①
where
筛选条件②
order by
排序列表 asc|desc④ -- asc用于升序排列,desc用于降序排列
如果后面不加任何关键字,则默认表示升序排列
特点:
1、asc代表的是升序,desc代表的是降序,如果不写,默认是升序
2、order by子句支持单个字段、多个字段、函数、表达式、别名。
3、order by子句放在查询语句的最后面。limit字句除外。
4、执行顺序:①②③④
*/
#案例1:查询员工信息,要求员工工资从高到低
USE myemployees;
SELECT
*
FROM
employees
ORDER BY
salary ASC;
#案例2:查询部门编号》=90的员工信息,按照入职时间的先后顺序进行排序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;
#案例3:按表达式排序,按照员工年薪的高低显示员工信息
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例4:按年薪高低显示员工的信息和年薪,按别名排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
#案例5:按照姓名的长度显示员工的姓名和工资,按姓名长度进行排序
SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM 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)) AS 年薪
FROM employees
ORDER BY 年薪 DESC, last_name ASC;
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资,按工资降序
SELECT last_name,salary
FROM employees
WHERE 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;