MySQL基础——DQL语句(排序查询)
排序查询
语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点:
1.asc代表的是升序,desc代表的是降序
如果不写,默认是升序
2.order by子句中可以支持单个字段,多个字段,表达式,函数,别名
3.order by子句一般放在查询语句的最后面,limit子句除外
案例1:查询员工信息,要求工资从高到低排序
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)) DESC;
案例4:按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT
*,
salary * 12 * (1+ IFNULL (commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC;
案例5:按姓名的长度来显示员工的姓名和工资【按函数排序】
SELECT
last_name,
salary
FROM
employees
ORDER BY LENGTH (last_name) 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 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;
2021-02-11
祝大家新年快乐!!!