USE myemployees;
DESC employees;
#进阶3:排序查询
/*
语法:
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;
特点:
1、asc代表的是升序,可以省略
desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面,除了limit子句
*/
#1 按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;
#2. 添加筛选条件再排序
#案例: 查询部门编号 >= 90 的员工信息,并按员工编号降序
SELECT *
FROM employees
WHERE `department_id` >= 90
ORDER BY employee_id DESC;
#3 按表达式排序
# 案例: 查询员工信息,按年薪降序
SELECT *,salary * 12 * (1 + IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) ASC;
#4、按别名排序
#案例:查询员工信息 按年薪升序
SELECT *,salary * 12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 ASC;
#5、按函数排序
#案例:查询员工名,并且按名字的长度降序
SELECT LENGTH(last_name),last_name
FROM employees
ORDER BY LENGTH(last_name) DESC;
#6、按多个字段排序
#案例:查询员工信息,要求先按工资降序,再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;
#----测试
SELECT
`last_name`,salary * 12 * (1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC , `last_name` ASC;
SELECT
`salary`,`last_name`
FROM
employees
ORDER BY salary DESC;
SELECT
*,LENGTH(`email`)
FROM
employees
WHERE
`email` LIKE '%e%'
ORDER BY LENGTH(`email`) DESC,`department_id` ASC;
MySQL day02 01)排序查询
最新推荐文章于 2022-05-12 20:37:41 发布