#排序查询
/*
order by 排序列表【asc | desc 】
order by 子句中支持单个字段,多个字段,表达式,函数,别名;
放在查询语句的最后面,limit除外
*/
#案例1:查询员工信息,要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例2:查询部门编号>=90 的员工信息,按入职时间的先后进行排序
SELECT * FROM employees WHERE employee_id >90 ORDER BY hiredate ASC;
#案例3:按年薪的高低显示员工的信息和年薪(按表达式排序)
SELECT * ,salary*12*(1+commission_pct) AS 年薪 FROM employees ORDER BY salary*12*(1+commission_pct) DESC;
#案例4:年薪的高低显示员工的信息和年薪(按别名排序)
SELECT * ,salary*12*(1+commission_pct) AS 年薪 FROM employees ORDER BY 年薪 DESC;
#案例5:按姓名的长度显示员工的姓名和工资(按函数排序)
SELECT last_name , salary FROM employees ORDER BY LENGTH(last_name) DESC;
#案例6:查询员工信息,要求先按工资排序,在按员工编号排序(按多个字段排序)
SELECT * FROM employees ORDER BY salary DESC,employee_id ASC;
#测试题
#(1)查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序;
SELECT last_name,department_id,salary*12*(1+commission_pct) AS 年薪 FROM employees ORDER BY 年薪 DESC, last_name ASC;
#(2)选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT last_name , salary FROM employees WHERE NOT(salary >8000 AND salary<17000) ORDER BY salary DESC;
#(3)查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,在按部门号升序;
SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC , department_id ASC;
MySql-------排序查询
最新推荐文章于 2022-05-27 11:01:53 发布