***#进阶三:查询排列
引入:
select * from employees;
语法:
select 查询列表
from 库
[where 筛选条件]
order by 排序列表 【asc/desc】;
特点:
1.asc代表的是升序,desc代表降序
2.如果不写,则默认为升序
#案例一:查询员工信息,要求员工工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例二:查询员工信息,要求员工工资从低到高排序
SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary ;
#案例三:查询部门编号>=90的员工信息,按入职时间的先后顺序进行排列[添加筛选条件]
SELECT
*
FROM
employees
WHERE
department_id>=90
ORDER BY
hiredate;
#案例四:按年薪高低显示员工信息和年薪[按表达式排序]
SELECT
*,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM
employees
ORDER BY
salary*12*(1+IFNULL(commission_pct,0));
#案例五:按年薪高低显示员工信息和年薪[按别名排序]
SELECT
*,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM
employees
ORDER BY 年薪;
#案例六:按姓名的长度显示员工姓名的工资[按函数排序]
SELECT
LENGTH(last_name) 字节长度,last_name,salary
FROM
employees
ORDER BY LENGTH(last_name) DESC;
#案例七:查询员工的信息,要求先按工资升序,再按员工编号降序[多个字段排序]
SELECT
*
FROM
employees
ORDER BY salary,employee_id DESC;
#优先排序前面的
如图,可以发现salary优先排序,后排序employee_id
#按多个列排序,例如检索三个列,只对其中的两个列排序
SELECT department_id,salary,job_id
FROM employees
ORDER BY salary,job_id;
如图,可发现department_id并没有排序,而salary,job_id排序,且优先排序salary
#按列位置排序
#按多个列排序,例如检索三个列,只对其中的两个列排序
SELECT department_id,salary,job_id
FROM employees
ORDER BY 2,3;
#结果和上面的图一样