Mysql进阶三：排序查询

进阶三：排序查询

USE myemployees;
SELECT * FROM employees;

语法：

SELECT 查询列表
FROM 表
【WHERE 筛选条件】
ORDER BY 排序列表 【ASC|DESC】
如果不写，默认是升序

案例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(first_name) 字节长度,first_name,salary
FROM employees
ORDER BY LENGTH(first_name) DESC;

案例6:查询员工信息，先按员工工资升序排序，再按员工编号降序排序

SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;  /*即：工资从低到高，工资相同时，再按照编号排序*/

测试题目：

1.查询员工的姓名、部门号、年薪，按照年薪降序，按照姓名升序

2.选择工资不在8000-17000的员工的姓名和工资，按工资降序，并显示是为“姓名”,“工资”两列

3.查询邮箱中包含e的员工信息，并先按照邮箱的字节数降序，再按照部门号升序

测试题答案：

1.

SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,first_name ASC;

2.

SELECT first_name AS 姓名,salary AS 工资
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

3.

SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;