根据尚硅谷的视频教程学习MySQL,学习记录-03 - 排序查询。
所有操作开始之前,建议先打开对应的库:
USE 库名;
USE employees;
=> 语法:
SELECT 查询列表
FROM 表名
WHERE 筛选条件
ORDER BY 排序列表 (ASC | DESC)
=> 特点:
1 ASC: 升序, DESC: 降序; 不写则默认为ASC,升序排列
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, LENGTH(last_name) 字节长度,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
案例6:查询员工信息,要求先按照工资升序,再按照员工编号降序排列【多字段排序】
SELECT * FROM employees
ORDER BY salary ASC, employee_id DESC;
案例7:选择工资不在8000-17000的员工的姓名、工资,按照工资降序【排序+筛选】
SELECT last_name, salary
FROM employees
WHERE salary not between 8000 and 17000 -- not (salary between 8000 and 17000)
ORDER BY salary DESC;
案例8:查询邮箱中包含e的员工信息,并先按照邮箱字节数降序,再按部门号升序
SELECT * ,LENGTH(email) FROM employees
WHERE emial LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id;