进阶3:排序查询
/*
语法:
select
查询列表
from
表
【where 筛选条件】
order by
排序列表
【asc|desc】;
特点:
1、asc代表升序,desc代表降序
如果不写,默认升序
2、order by支持单个字段多个字段,表达式,函数
3、order by中不能使用concat()
4、order by语句一般放在查询语句最后,limt子句除外
*/
#案例1:查询员工信息,要求工资从高到低排序
#由低到高
SELECT
*
FROM
employees
ORDER BY
salary
ASC;
#由低到低
SELECT
*
FROM
employees
ORDER BY
salary
;
#由高到低
SELECT
*
FROM
employees
ORDER BY
salary
DESC;
#案例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))
ASC;
#案例4:按年薪的高低显示员工的信息和年薪【按别名升序】
SELECT
*,
salary * 12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY
年薪
ASC;
#案例5、按姓名的长度显示员工姓名和工资【按函数排序】
SELECT
LENGTH(CONCAT(last_name,first_name)) AS 姓名长度,
CONCAT(last_name,first_name) AS 姓名,
salary
FROM
employees
ORDER BY
# length(concat(last_name,first_name)
#不能在此使用concat()
姓名长度
ASC;
#案例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
NOT(salary 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;