Mysql基础部分(4)---排序查询

喜欢ヅ旅行 2023-05-21 08:56 33阅读 0赞

Mysql基础部分内容：  
[Mysql基础部分(1)—基础操作指令与语法规范][Mysql_1]  
[Mysql基础部分(2)—基础查询][Mysql_2]  
[Mysql基础部分(3)—条件查询][Mysql_3]  
[Mysql基础部分(4)—排序查询][Mysql_4]  
[Mysql基础部分(5)—常见函数][Mysql_5]  
[Mysql基础部分(6)—分组函数][Mysql_6]  
[Mysql基础部分(7)—分组查询][Mysql_7]  
[Mysql基础部分(8)—sql99语法][Mysql_8_sql99]  
[Mysql基础部分(9)—子查询][Mysql_9]  
[Mysql基础部分(10)—分页查询][Mysql_10]  
[Mysql基础部分(11)—联合查询][Mysql_11]  
[Mysql基础部分(12)—数据的增删改][Mysql_12]  
[Mysql基础部分(13)—库和表的管理][Mysql_13]  
[Mysql基础部分(14)—常见的数据类型][Mysql_14]  
[Mysql基础部分(15)—常见约束][Mysql_15]  
[Mysql基础部分(16)—标识列][Mysql_16]  
[Mysql基础部分(17)—事务][Mysql_17]  
[Mysql基础部分(18)—视图][Mysql_18]  
[Mysql基础部分(19)—变量][Mysql_19]  
[Mysql基础部分(20)—存储过程][Mysql_20]  
[Mysql基础部分(21)—函数][Mysql_21]  
[Mysql基础部分(22)—流程控制结构][Mysql_22]

#进阶3：排序查询
    
    /* 引入： select * from employees; 语法： select 查询列表 from 表 [where 筛选条件] order by 排序列表 [asc|desc] 特点： 1.asc代表的是升序，desc代表降序 如果不写，默认升序 2.order by字句中可以支持单个字段，多个字段，表达式，函数，别名 3.order by字句一般是放在查询语句的最后面，limit子句除外 */	
    #案例：查询员工信息，要求工资从高到低实现排序
    SELECT * FROM employees ORDER BY salary DESC;
    SELECT * FROM employees ORDER BY salary ASC;
    
    #案例2：查询部门编号>=90的员工信息，按照入职时间的先后进行排序
    #添加筛选条件
    SELECT	* 
    FROM employees
    WHERE	department_id>=90
    ORDER BY hiredate ASC;
    
    #案例3：按年薪的高低显示员工的信息和年薪[按表达式排序]
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    FROM employees
    ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
    
    #案例4：按年薪的高低显示员工的信息和年薪[按别名排序]
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    FROM employees
    ORDER BY 年薪 DESC;
    
    #案例5：按姓名的长度显示员工的姓名和工资[按函数排序]
    SELECT LENGTH(last_name) 字节长度,last_name,salary
    FROM employees
    ORDER BY LENGTH(last_name) DESC;
    
    #案例6：查询员工信息，要求先按工资升序，再按员工编号降序[按多个字段排序]
    SELECT * 
    FROM employees
    ORDER BY salary ASC,employee_id DESC;

[Mysql_1]: https://blog.csdn.net/weixin_43900321/article/details/105664839
[Mysql_2]: https://blog.csdn.net/weixin_43900321/article/details/105729424
[Mysql_3]: https://blog.csdn.net/weixin_43900321/article/details/105729465
[Mysql_4]: https://blog.csdn.net/weixin_43900321/article/details/105729506
[Mysql_5]: https://blog.csdn.net/weixin_43900321/article/details/105729526
[Mysql_6]: https://blog.csdn.net/weixin_43900321/article/details/105729554
[Mysql_7]: https://blog.csdn.net/weixin_43900321/article/details/105729593
[Mysql_8_sql99]: https://blog.csdn.net/weixin_43900321/article/details/105810240
[Mysql_9]: https://blog.csdn.net/weixin_43900321/article/details/105849897
[Mysql_10]: https://blog.csdn.net/weixin_43900321/article/details/105869419
[Mysql_11]: https://blog.csdn.net/weixin_43900321/article/details/105978422
[Mysql_12]: https://blog.csdn.net/weixin_43900321/article/details/105978492
[Mysql_13]: https://blog.csdn.net/weixin_43900321/article/details/106000721
[Mysql_14]: https://blog.csdn.net/weixin_43900321/article/details/106000793
[Mysql_15]: https://blog.csdn.net/weixin_43900321/article/details/106057742
[Mysql_16]: https://blog.csdn.net/weixin_43900321/article/details/106057793
[Mysql_17]: https://blog.csdn.net/weixin_43900321/article/details/106156900
[Mysql_18]: https://blog.csdn.net/weixin_43900321/article/details/106156942
[Mysql_19]: https://blog.csdn.net/weixin_43900321/article/details/106233375
[Mysql_20]: https://blog.csdn.net/weixin_43900321/article/details/106233530
[Mysql_21]: https://blog.csdn.net/weixin_43900321/article/details/106233577
[Mysql_22]: https://blog.csdn.net/weixin_43900321/article/details/106253492