#进阶三:排序查询
/*
语法:
select 查询列表
from 表明
【where 筛选条件】
order by 排序列表
执行顺序
1、from子句
2、where子句
3、select子句
4、order by 子句
举例:
select last_name,salary
from employees
where salary>10000
order by salary;
特点:
1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2、升序,通过asc,默认行为
降序,通过desc
*/
#一、按单个字段排序
SELECT *
FROM employees
WHERE employee_id>120
ORDER BY salary DESC;
#二按表达式排序
#案例一:对有将近的员工,按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;
#三、按别名排序
#案例一
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;
#四、按函数的结果排序
#案例一:根据姓名的字符个数升序排序
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name) DESC;
SELECT LENGTH(last_name),last_name
FROM employees;
#五、按多个字段排序
#案例一:查询员工的姓名、工资、部门编号。先按工资升序,再按部门编号降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;
#六、按列数排序
SELECT * FROM employees
ORDER BY last_name DESC;
MySQL排序查询
最新推荐文章于 2024-05-18 21:53:29 发布