数据库学习之排序&分组查询

时间：2025-02-07 08:45:28

一、 排序查询

1、语法   

            select       要查询的东西    

            from       表    

            where       条件    

            order by 排序的字段|表达式|函数|别名 【asc|desc】

（order by：放在最后除了limit字句，asc|desc ：不写默认为升序）

2、排序方式

  •  ASC：升序，默认的
  • DESC：降序
  • 排序分类：

           按单个字段进行排序

           按多个字段排序

           按表达式排序

           按别名排序

           按函数排序

     注意： 如果有多个排序条件，则当之前的条件值一样时，才会判断第二条件

2、 常见函数

1、单行函数
（1）字符函数
  •  length：获取数值字节个数
  • concat：拼接字符串
  • substr/substring：截取字符串
  • upper：大写转换
  • lower：小写转换
  • instr：返回子串第一次出现的索引
  • trim:去除空格
  • ltrim: 去左边空格
  • rtrim: 去右边空格
  • replace: 替换
  • lpad: 左填充
  • rpad: 右填充
（2）数学函数
  • round: 四舍五入
  • rand: 随机数
  • floor: 向下取整
  • ceil: 向上取整
  • mod: 取余
  • truncate: 截断
（3）日期函数
  • now: 当前系统日期+时间
  • curdate: 当前系统日期
  • curtime: 当前系统时间
  • str_to_date: 将字符转换成日期
  • date_format: 将日期转换成字符
（4）流程控制函数
  • if 处理双分支
  •  case语句 处理多分支   

                情况1：处理等值判断

                                case    要判断的字段或表达式

                                when   常量1  then   要显示的值1或者语句1

                                when   常量2  then   要显示的值2或者语句2

                                ....................................

                                else    要显示的值n或语句n

                                end

                 情况2：处理条件判断

                                case    

                                when   条件1  then   要显示的值1或者语句1

                                when   条件2  then   要显示的值2或者语句2

                                ....................................

                               else    要显示的值n或语句n

                               end

（5）其他函数
  • version: 版本
  • database: 当前库
  • user: 当前连接用户

  • 课堂练习

    1.显示系统时间(注：日期+时间)

SELECT NOW();

2、查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT `employee_id`,CONCAT(`last_name`,`first_name`)AS 姓名,`salary`,`salary`*1.2 AS 新工资
FROM `employee`;

3.将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT `employee_id`,CONCAT(`last_name`,`first_name`) AS 姓名,LENGTH(CONCAT(`last_name`,`first_name`))
FROM `employee`
ORDER BY SUBSTR(`last_name`,1,1) ASC;
2、分组函数
  • count：计算个数

              一般选择非空的列：主键

             count(*)：统计非null的行数
             count(1)：统计行数

  • max：计算最大值
  • min：计算最小值
  • sum：计算和
  • avg：计算平均值

    特点：

  • 1、以上五个分组函数都忽略null值，除了 count(*)
  • 2、sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
  • 3、都可以搭配distinct使用，用于统计去重后的结果
  • 4、count的参数可以支持：        * 字段、*、常量值，一般放1

注意：聚合函数的计算，排除null值。

解决方案： 选择不包含非空的列进行计算

                  IFNULL函数 ​ ​

课堂练习

  1. 查询公司员工工资的最大值，最小值，平均值，总和

SELECT `job_id`,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM `employee`;

    2、查询部门编号为90的员工个数

SELECT COUNT(*)
FROM `employee`
WHERE `department_id`= 90;

二、分组查询

  • 语法

               select 查询的字段，分组函数    

               from 表    

              group by 分组的字段

  • 特点：    

         1、可以按单个字段分组    

         2、和分组函数一同查询的字段最好是分组后的字段    

         3、可以按多个字段分组，字段之间用逗号隔开    

         4、可以支持排序    

         5、having后可以支持别名

  •  where 和 having 的区别？

        1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不 满足结果，则不会被查询出来

        2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

课堂练习

1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

SELECT `job_id`,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM `employee`
GROUP BY  `job_id`
ORDER BY `job_id`;

2.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT `manager_id`,MIN(`salary`) 
FROM `employee`
WHERE `manager_id` IS NOT NULL
GROUP BY `manager_id`
HAVING MIN(`salary`)>=6000;

3.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序

SELECT `department_id`,COUNT(1),AVG(`salary`)
FROM `employee`
GROUP BY `department_id`
ORDER BY AVG(`salary`) DESC;

相关文章