3、排序查询
1.语法
SELECT
查询列表
FROM
表
WHERE
筛选条件
ORDER BY
排序列表[ASC|DESC][升序|降序]
2.特点:ASC表示的是升序,DESC表示的是降序
如果不写,默认是升序
案例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))
FROM
employees
ORDER BY
salary * 12 * (1 + IFNULL(commission_pct, 0)) ASC;
案例4:按年薪的高低显示员工的信息和年薪(按别名排序)
SELECT
*, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY
年薪 ASC;
案例5:按姓名的长度显示员工的姓名和工资(按函数排序)
SELECT
LENGTH(last_name) 字节长度,last_name,salary
FROM
employees
ORDER BY
LENGTH(last_name) DESC;
案例6:查询员工信息,要求先按工资排序,再按员工编号排序(按多个字段排序)
SELECT
*
FROM
employees
ORDER BY
salary ASC,employee_id DESC;
Note:先按工资升序排序,当工资相等时,再按员工编号排序