排序查询介绍
语法:
select 查询列表
from 表
[where 筛选条件]
order by 排序列表 [asc/desc]
特点:
- asc代表的是升序,desc代表的是降序,如果不写,默认是升序
案例:查询员工信息,要求工资从高到低排序
SELECT
*
FROM
employees
ORDER BY
salary ASC;
- order by子句可以支持单个字段、多个字段、表达式、函数、别名
- order by子句一般是放在查询语句的最后面,limit子句除外
#查询员工的姓名、部门号和年薪,按年薪降序、姓名升序
SELECT last_name,`department_id`,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;
#选择工资不在7000到15000的员工姓名和工资,按工资降序
SELECT
last_name,
salary
FROM
employees
WHERE
salary NOT BETWEEN 7000 AND 15000
ORDER BY
salary DESC;
#查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT
*,LENGTH(email)
FROM
employees
WHERE
email LIKE '%e%'
ORDER BY
LENGTH(email) DESC,`manager_id` ASC;