排序查询
语句
#排序查询
/*
引入:
SELECT * FROM employees;
语法:
SELECT 查询列表
FROM 表名
【where 筛选条件】
ORDER BY 排序列表【ASC|DESC】
特点:
1.asc代表升序,decs代表降序,如果不写,默认的是升序
2.ORDER BY 子句中可以支持单个字段、多个字段、表达式、函数、别名
3.ORDER BY 子句 一般是放在查询语句的最后面,但是limit子句除外。
*/
排序查询案例练习
案例1:查询员工信息,工资由高到低
解答1:
SELECT
*
FROM
employees
ORDER BY
salary DESC;
SELECT
*
FROM
employees
ORDER BY
salary ASC;
案例2:查询部门编号>=90的员工信息,按照入职时间的先后排序
解答2:
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate DESC
案例3:按照(表达式)排序:按照年薪的高低显示员工的信息和年薪
解答3:
SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY
salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC
案例4:按照(别名)排序:按照年薪的高低显示员工的信息和年薪
解答4:
SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY
年薪 DESC
案例5:按姓名的长度显示员工的姓名和工资(按函数排序)
解答5:
SELECT
LENGTH(last_name) 字节长度,
last_name,
salary
FROM
employees
ORDER BY
LENGTH(last_name) DESC
案例6:查询员工信息,按照工资排序,在按照员工编号排序(按字段排序)
解答6:
SELECT
*
FROM
employees
ORDER BY
salary ASC,
employee_id DESC
案例7:
案例8:
案例9: