mysql学习打卡4排序查询

末蓝、 2023-03-13 13:11 7阅读 0赞

## 进阶3：排序查询 ##

/*
    语法：
    	select 查询列表
    	from 表
    	【where 筛选条件】
    	order by 排序列表 【asc|desc】
    特点	
    	1.默认升序
    	2.可以多个字段，表达式，函数，别名都可
    	3.查询语句的最后面，limit子句前面
    */

#1.工资从高到低
    SELECT * 
    FROM employees 
    ORDER BY salary ASC;
    SELECT * 
    FROM employees 
    ORDER BY salary DESC;
    
    #2.查询部门编号>=90 按照入职时间先后排序
    SELECT * 
    FROM employees
    WHERE department_id>=90
    ORDER BY hiredate ASC; 
    
    #3.按照年薪高低显示员工信息和年薪 没有年薪这个字段 按照表达式排序
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
    FROM employees
    ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
    
    #4.按照别名排序
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
    FROM employees
    ORDER BY 年薪 DESC;
    
    #5.按照姓名长度排序 按照函数排序
    SELECT LENGTH(last_name) AS 名字长度,last_name,salary
    FROM employees
    ORDER BY LENGTH(last_name);
    
    SELECT LENGTH(last_name) AS 名字长度,last_name,salary
    FROM employees
    ORDER BY 名字长度;
    
    #6.先按工资s升序排序，再按编号降序排序，按多个字段排序
    SELECT *
    FROM employees
    ORDER BY salary ASC,employee_id DESC;

# 练习1  按年薪升序姓名降序
    SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
    FROM employees
    ORDER BY 年薪 DESC,last_name ASC;
    
    # 练习2 选择工资不在8000到17000的员工的姓名和工资，按照工资降序
    SELECT last_name,salary
    FROM employees
    WHERE salary NOT BETWEEN 8000 AND 17000
    ORDER BY salary DESC;
    
    # 练习3 邮箱包含e的员工信息，邮箱字节数降序，部门编号升序
    SELECT *
    FROM employees
    WHERE email LIKE '%e%'
    ORDER BY LENGTH(email) DESC,department_id ASC;