排序查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108

#进阶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;