当前位置: 首页 > news >正文

【数据库】MySQL聚合统计

目录

1.聚合函数

案例1: 统计班级共有多少同学

 案例2:统计本次考试的数学成绩分数个数

 案例3:统计数学成绩总分

案例4:统计平均总分

案例5:返回英语最高分

案例6:返回 > 70 分以上的数学最低分

2.分组聚合统计(group by)

准备工作:

案例1:如何显示每个部门的平均工资和最高工资

 案例2:显示每个部门的每种岗位的平均工资和最低工资

案例3:显示平均工资低于2000的部门和它的平均工资

那怎么样理解having和where的区别


1.聚合函数

使用聚合函数是为了帮助我们在查询结果之后对结果进行统计,这种统计的方式一般都是一个值为结果。

 在使用这些函数之前,我们需要创建一张测试表

CREATE TABLE exam_result ( 
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20) NOT NULL COMMENT '同学姓名', 
chinese float DEFAULT 0.0 COMMENT '语文成绩', 
math float DEFAULT 0.0 COMMENT '数学成绩', 
english float DEFAULT 0.0 COMMENT '英语成绩' 
);

在插入一些数据

INSERT INTO exam_result (name, chinese, math, english) VALUES 
('唐三藏', 67, 98, 56), 
('孙悟空', 87, 78, 77), 
('猪悟能', 88, 98, 90), 
('曹孟德', 82, 84, 67), 
('刘玄德', 55, 85, 45), 
('孙权', 70, 73, 78), 
('宋公明', 75, 65, 30);

 

案例1: 统计班级共有多少同学

 select count(*) from exam_result;

 可以看到它可以直接告诉我们一个值。

也可以对列名进行重命名。

select count(*) 总数 from exam_result;

 案例2:统计本次考试的数学成绩分数个数

先筛选出数学成绩观察

使用函数统计

select count(math) 总数 from exam_result;

可以看到和统计全班人数用法相同。

 我们也可以对重复的数学成绩进行去重之后再统计

select count(distinct math) from exam_result;

 案例3:统计数学成绩总分

使用上面表中的sum函数

select sum(math) from exam_result;

也可以在select之后构建表达式,统计出数学的平均分

select sum(math)/count(*) from exam_result;

案例4:统计平均总分

除了在select之后使用表达式之外,我们还可以使用avg来直接求平均值

 select avg(english+math+chinese) from exam_result;

案例5:返回英语最高分

先查看所有英语成绩

使用max筛选

select max(english) from exam_result;

结果如上

案例6:返回 > 70 分以上的数学最低分

 select min(math) from exam_result where math > 70;

  结果也符合要求。

以上案例我们需要知道的是要被聚合函数统计,被统计的对象一定要是可聚合的,比如上表中的name列和math等成绩列是放不到一起去的 。

2.分组聚合统计(group by)

准备工作:

首先我们需要的目的是分组之后方便进行聚合统计,所以分组的最终目标还是为了聚合统计。

在学习之前我们需要做一些准备工作。

创建一个雇员信息表(来自oracle 9i的经典测试表)

EMP员工表

DEPT部门表

SALGRADE工资等级表。

这里我直接将sql文件的内容放出来

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `scott`;DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (`grade` int(11) DEFAULT NULL COMMENT '等级',`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

这个sql文件的内容都是一些基本的操作,不难看懂。

我们show tables;


可以看到上文创建的表都在其中,接下来我们完成案例上的需求。

案例1:如何显示每个部门的平均工资和最高工资

首先全列查询所有员工的信息

首先我们观察到depton分为三种,也就是说公司分为三个部门10,20 ,30;

所以我们按照需求

select deptno ,max(sal) 最高,avg(sal) 平均 from emp group by deptno;

 我们需要知道的是group by是进行分组聚合统计,所以我们要按照自己觉得需求进行使用。

而且可以看到的是使用group by指定列名,实际分组是通过该列不同行的行数据进行分组的。

其实分组简单地按逻辑来看,就是将一张大表拆成了多个子表,然后对多个子表进行聚合统计

 案例2:显示每个部门的每种岗位的平均工资和最低工资

select deptno,job,avg(sal) 平均工资,min(sal) 最低工资  from emp group by deptno,job;

案例3:显示平均工资低于2000的部门和它的平均工资

我们将问题拆开

1.统计出来每一个部门的平均工资(现将结果聚合出来)

select deptno,avg(sal) 平均工资 from emp group by deptno;

 

2.再进行判断(对聚合出来的结果再进行判断)

这里就要使用having和group by配合使用,对group by结果进行过滤

select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;

having的作用是对聚合后的数据再进行统计,也是条件筛选 。

having经常和group by搭配使用,作用是对分组进行筛选,作用有些像where。

那怎么样理解having和where的区别

where和having都可以做条件筛选,但是两个条件筛选完全不同。

如果我们将上文的having换成where

就会报错

 再举一个小例子

可以观察上图中命令的语序 

 所以where是对及具体的任意列进行条件筛选;而having是对分组聚合之后的结果进行条件筛选。

也就是说两个语句的应用场景不同。

where可以对原始的表进行条件过滤,之后在对结果使用having进行分组。

所以我们可以得出结论上图sql语句的执行语句如下

 通过以上的案例,未来我们只要能处理好单表的CURD,之后所有的SQL场景,我们都可以用统一的方式进行。

相关文章:

  • 北京网站建设多少钱?
  • 辽宁网页制作哪家好_网站建设
  • 高端品牌网站建设_汉中网站制作
  • 【vuetify】v-select 无法正常显示,踩坑记录!
  • Vue3生命周期钩子函数(Vue3生命周期)
  • vue3 一次二次封装element-plus组件引发的思考
  • 解决ubuntu 24.04 ibus出现卡死、高延迟问题
  • 解决uniapp视频video组件进入全屏再退出全屏后,cover-view失效的问题
  • Brave编译指南2024 Windows篇:拉取Brave源码(六)
  • SpringSecurity剖析
  • yjs04——matplotlib的使用(多个坐标图)
  • 探索源代码防泄漏与模块化沙箱的秘密
  • 高级实时通信:基于 Python 的 WebSocket 实现与异步推送解决方案
  • 房产销售系统开发:SpringBoot技术要点
  • 关于node安装的全局js库,提示‘not found’
  • 【北京迅为】《STM32MP157开发板使用手册》- 第三十章Cortex-M4通用定时器实验
  • IntelliJ IDE 插件开发 | (十二)自定义项目脚手架(上)
  • RabbitMQ 基础入门
  • 【翻译】Mashape是如何管理15000个API和微服务的(三)
  • C++类中的特殊成员函数
  • js作用域和this的理解
  • mysql中InnoDB引擎中页的概念
  • Nacos系列:Nacos的Java SDK使用
  • 诡异!React stopPropagation失灵
  • 简单基于spring的redis配置(单机和集群模式)
  • 少走弯路,给Java 1~5 年程序员的建议
  • 它承受着该等级不该有的简单, leetcode 564 寻找最近的回文数
  • 一个普通的 5 年iOS开发者的自我总结,以及5年开发经历和感想!
  • 用Node EJS写一个爬虫脚本每天定时给心爱的她发一封暖心邮件
  • 原生Ajax
  • 找一份好的前端工作,起点很重要
  • postgresql行列转换函数
  • ​ssh免密码登录设置及问题总结
  • # linux 中使用 visudo 命令,怎么保存退出?
  • # 再次尝试 连接失败_无线WiFi无法连接到网络怎么办【解决方法】
  • #NOIP 2014# day.1 T2 联合权值
  • (1)Hilt的基本概念和使用
  • (2)Java 简介
  • (9)目标检测_SSD的原理
  • (ZT)薛涌:谈贫说富
  • (二)fiber的基本认识
  • (附源码)ssm经济信息门户网站 毕业设计 141634
  • (回溯) LeetCode 40. 组合总和II
  • (力扣)循环队列的实现与详解(C语言)
  • (三)模仿学习-Action数据的模仿
  • (三分钟)速览传统边缘检测算子
  • (原創) 如何優化ThinkPad X61開機速度? (NB) (ThinkPad) (X61) (OS) (Windows)
  • (转)shell调试方法
  • (转载)深入super,看Python如何解决钻石继承难题
  • .NET Project Open Day(2011.11.13)
  • .net中生成excel后调整宽度
  • ?php echo $logosrc[0];?,如何在一行中显示logo和标题?
  • @media screen 针对不同移动设备
  • [ Python ]使用Charles对Python程序发出的Get与Post请求抓包-解决Python程序报错问题
  • [Android]创建TabBar
  • [Angularjs]asp.net mvc+angularjs+web api单页应用
  • [AS3]URLLoader+URLRequest+JPGEncoder实现BitmapData图片数据保存
  • [bug总结]: Feign调用GET请求找不到请求体实体类