数据库_03_排序查询

#进阶3:排序查询

语法:
select 查询列表
from 表
[where 筛选条件]
order by
排序列表 [asc|desc]
注:
1.默认升序
2.一般放在最后面,limit字句除外

#-------------------
#案例1:查询员工信息,要求工资从高到低排序

SELECT * FROM employees ORDER BY salary DESC;

#案例2:查询部门编号大于等于90的员工信息,按入职时间的先后进行排序【添加筛选条件】

SELECT * FROM employees WHERE employee_id>=90 ORDER BY hiredate;

#案例3:按年薪的高低显示员工的信息和年薪【按表达式/别名排序】

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 
AS 年薪 
FROM employees 
ORDER BY 年薪 /* salary*12*(1+IFNULL(commission_pct,0)) 也可*/
DESC;

#案例4:按姓名的长度显示员工的姓名和工资【按函数排序】

SELECT LENGTH(last_name) AS 字节长度,last_name,salary 
FROM employees 
ORDER BY LENGTH(last_name);

#案例5:查询员工信息,要求先按工资排序,再按员工编号排序【按多个字段排序】

SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

#测试

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;

SELECT last_name,salary 
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000;
ORDER BY last_name;

SELECT * ,LENGTH(email) 邮箱
FROM employees 
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id;
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页