排序查询
#进阶3:排序查询 /* 引入: SELECT * FROM employees; 语法: 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; SELECT * FROM employees ORDER BY salary; #案例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 LENGTH(last_name) 字节长度, last_name, salary FROM employees ORDER BY LENGTH(last_name) DESC; #案例6:查询员工信息,要求先按工资 升序,再按员工编号降序【按多个】 SELECT * FROM employees ORDER BY employee_id DESC, salary ASC; #查询员工的姓名和部门号和年薪,按年薪降序排序,按姓名升序 SELECT last_name, department_id, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪 FROM employees ORDER BY 年薪 DESC, last_name ASC; #选择工资不在8000到17000的员工的姓名和工资,按工资降序 SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC; #查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序 SELECT *, LENGTH(email) AS 邮箱长度 FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC, department_id ASC;
本文来自博客园,作者:自律即自由-,转载请注明原文链接:https://www.cnblogs.com/deyo/p/13234138.html