SQL基础语句
来源于尚硅谷SQL基础语句笔记
三 排序查询
引入
SELECT *FROM employees;
语法:
Select 查询列表
From biao
Where 条件
Order by 排序列表 【asc| desc】 asc代表升序 desc代表降序 不写默认升序
案例:查询员工信息 要求工资从高到低排序
SELECT FROM employees
ORDER BY salary DESC;
从低到高
SELECTFROM employees
ORDER BY salary ASC;
案例2 : 查询部门编号>=90的员工信息 按入职先后的顺序进行排序
SELECT *
FROM employees
WHERE department_id >=90
ORDER BY hiredate ASC;
案例3 按年薪的高地显示员工的信息和年薪
SELECT ,salary12*(1+IFNULL(commission_pct)) AS 年薪
FROM employees
OEDER BY salary12(1+IFNULL(commission_pct)) desc;
OEDER BY年薪 desc; //效果一样
案例5 按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name)AS字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name)DESC ;
案例6:查询员工信息,要求先按工资升序排序 ,在按员工编号降序排序
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
ORDER BY不限制于几个字段 ;
- 查询员工的姓名和部门和年薪,按年薪降序 按姓名升序;
SELECT last_name,department_id,salary*12-(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC; - 选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT last_name,salary
FROMemployees
WHERE salary NOT BETWEEN8000 AND 17000
ORDER BY salary DESC; - 查询邮箱中包含e的员工信息,先按邮箱的字节数降序,在按部门号升序。
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE ‘%e%’
Order by length(email) DESC ,department_id ASC;