MySQL进阶三

#进阶3 排序查询

/*
引入:

SELECT *
FROM employees

语法:
SELECT 查询列表
FROM 表名
[WHERE 筛选条件]
order by 排序列表 [asc|desc]

执行顺序:先是FROM,到WHERE,再到SELECT,最后ORDER BY。

特点:
@1:asc代表的是升序,desc代表的是降序
如果不写,默认是升序。
@2:order by 子句中可以支持单个字段,多个字段,表达式,函数,别名
@3:order by 子句一般是放在查询语句的最后面,但limit子句除外
*/

#案例:查询员工信息,要求工资从高到低排序
SELECT *
FROM employees
ORDER BY salary DESC;

#案例:查询员工信息,要求工资从低到高排序 ASC可以去掉
SELECT *
FROM employees
ORDER BY salary ASC;

#案例二:查询部门编号>=90的员工信息,按入职时间先后进行排序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;

#案例三:按年薪的高低显示员工的信息和年薪[按表达式排序]
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#案例四:按年薪的高低显示员工的信息和年薪[按别名排序]
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;

#案例五:按姓名的长度显示员工的姓名和工资[按函数排序]
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

#案例六:查询员工信息,要求先按工资升序,再按员工编号降序[按多个字段排序]
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;


#测试题
/*
1.查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序。
2.选择工资不在8000到17000的员工的姓名和工资,按工资降序。
3.查询邮箱种包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序。
*/
#1.查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序。
SELECT CONCAT(last_name,first_name) AS 姓名,department_id AS 部门号,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,姓名 ASC;

#2.选择工资不在8000到17000的员工的姓名和工资,按工资降序。
SELECT CONCAT(last_name,first_name) AS 姓名,salary AS 工资
FROM employees
WHERE salary<8000 OR salary>17000
ORDER BY salary DESC;

#3.查询邮箱种包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序。
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

posted @ 2020-08-31 09:49  雅典娜Athena  阅读(116)  评论(0编辑  收藏  举报