mysql排序查询

mysql排序查询

排序查询

语法：
select 查询列表
from 表名
【where  筛选条件】
order by 排序的字段或表达式 【asc | desc】;

1、asc代表的是升序,可以省略,因为默认就是asc升序
2、desc代表的是降序
3、order by子句可以支持 单个字段、多个字段、别名、表达式、函数
4、order by子句一般是放在查询语句的最后面，limit子句除外

/*

排序查询

语法：
select 查询列表
from 表名
【where  筛选条件】
order by 排序的字段或表达式 【asc | desc】;

1、asc代表的是升序,可以省略,因为默认就是asc升序
2、desc代表的是降序
3、order by子句可以支持 单个字段、多个字段、别名、表达式、函数
4、order by子句一般是放在查询语句的最后面，limit子句除外

*/
#ASC代表的是升序
SELECT salary FROM employees ORDER BY salary ASC ;
#ASC可以省略，因为默认就是asc升序
SELECT salary FROM employees ORDER BY salary ;
#desc代表的是降序
SELECT salary FROM employees ORDER BY salary DESC;

#案例：查询员工信息，要求薪水从高到低
SELECT * FROM employees ORDER BY salary DESC;

#案例：查询员工信息，要求薪水从低到高
SELECT * FROM employees ORDER BY salary ASC;
#ASC可以省略，因为默认就是asc升序
SELECT * FROM employees ORDER BY salary;

#------------------------------------------------------------

#语法正确，不报错
SELECT *, department_name FROM departments;
#语法错误，会报错
SELECT department_name, * FROM departments;
#语法正确，不报错
SELECT department_name, departments.* FROM departments;
#语法正确，不报错
SELECT department_name, dep.* FROM departments dep;
#语法正确，不报错
SELECT dep.department_name, dep.* FROM departments dep;
#语法正确，不报错
SELECT dep.`department_name`, dep.* FROM departments dep;

#------------------------------------------------------------

#添加筛选条件再排序
#案例：查询部门编号>=90的员工信息，按入职时间的先后进行排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;

#案例，按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *, salary*12*(1+IFNULL(commission_pct, 0)) AS 年薪 FROM 
employees ORDER BY salary*12*(1+IFNULL(commission_pct, 0)) ASC;

#案例，按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *, salary*12*(1+IFNULL(commission_pct, 0)) AS 年薪 FROM 
employees ORDER BY 年薪 ASC;
#按别名排序
SELECT *, salary*12*(1+IFNULL(commission_pct, 0)) AS annual FROM 
employees ORDER BY annual ASC;

#LENGTH()函数，获取字符的长度
SELECT LENGTH("jack");

#按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name), last_name, salary 
FROM employees ORDER BY LENGTH(last_name) DESC;
#给函数取别名
SELECT LENGTH(last_name) AS nameLength, last_name, salary 
FROM employees ORDER BY nameLength DESC;

#案例查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;

#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct, 0)) AS annual
FROM employees ORDER BY annual DESC, last_name ASC;


#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name, salary FROM 
employees WHERE (salary NOT BETWEEN 8000 AND 17000) ORDER BY salary DESC;
#或者
SELECT last_name, salary FROM 
employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;
#或者
SELECT last_name, salary FROM 
employees WHERE NOT (salary BETWEEN 8000 AND 17000) ORDER BY salary DESC;
#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT LENGTH(email) 邮箱长度, email, department_id, employees.* FROM 
employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC, department_id ASC;