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

Mysql数据库09——分组聚合函数

类似pandas里面的groupby函数,SQL里面的GROUP BY子句也是可以达到分组聚合的效果。

常用的聚合函数有COUNT(),SUM(),AVG(),MAX(),MIN(),其用法看名字都看的出来,下面一一介绍


聚合函数

COUNT()计数

统计student表中计科系学生的人数。

SELECT  COUNT(*) AS 计科系学生人数
FROM    student  WHERE   institute='计算机学院';

COUNT函数的参数都是星号(*),除了星号可以当COUNT函数的参数以外,字段名也可以当函数参数。当字段名作为函数参数时,如果该字段中没有NULL值,则与星号作函数参数的效果相同。而如果字段中含有NULL值,加上DISTINCT函数,则统计个数时会排除含有NULL值的记录,请看下面的例子。

从foreign_teacher表中,统计外籍教师的所有人数、拥有电话的人数和拥有email的教师人数。

SELECT  COUNT(*) AS 外籍教师总人数, COUNT(DISTINCT tel)  AS 有电话的人数,
COUNT(DISTINCT email)  AS 有email的人数  FROM    foreign_teacher

统计外籍教师中,没有email的教师人数。

分析:统计没有email的教师人数,其实就是在统计该字段上有几个NULL值。下面是具体的解决办法。

SELECT COUNT(*) AS 没有email的人数
FROM foreign_teacher 
WHERE email IS NULL;

 

 

使用SUM函数求某字段的和

在course表中,求所有课程的总学分。

求所有课程的总学分,就是把学分字段的所有数值累加起来,下面的语句完成了这一任务。

SELECT SUM(credit) AS 总学分 FROM course;

 

SUM函数不仅可以累加所有记录值以外,同COUNT函数一样,也可以只将满足条件的记录值累加起来,请看下面的例子。

在course表中,求课程类型为“必修”的课程的学分总和。

SELECT  SUM(credit)  AS  必修课的学分总和
FROM    course  WHERE   type='必修'  ;

 

 

从score表中,求“计算机基础”课的考试成绩总和。

分析:因为score表中只有课号,而并没有课名,因此,首先,需要从course表中,查找“计算机基础”课的课号,其次,才能从score表中,通过“计算机基础”课的课号,查找满足条件的记录,最后,将考试成绩通过SUM函数加起来得到总和。所以,下面使用了两条SELECT语句。

SELECT ID AS 计算机基础课号
FROM  course   WHERE course='计算机基础';

 

 

运行上面的查询语句后得到结果中显示了“计算机基础”课的课号为“004”,根据这一结果,从Score表中,求“计算机基础”课的考试成绩总和。

SELECT SUM(result1) AS 计算机基础总成绩 FROM score WHERE c_id='004';

 


使用AVG函数求某字段的平均值

数据库操作中,除了求字段和以外,还经常需要求字段的平均值。AVG函数用于求字段的平均值,其用法和SUM函数的用法基本相同。AVG函数的参数也必须是数值类型的字段名或者结果为数值的表达式。

从score表中,求“计算机基础”课的考试成绩的平均分。

SELECT AVG(result1) AS 计算机基础平均成绩  FROM  score
WHERE c_id IN (SELECT ID FROM course WHERE course="计算机基础");

 


使用MAXMIN函数求最大、最小值

MAX和MIN函数用于求指定字段中的最大值和最小值,例如,想要知道student表中,最早(最晚)的出生日期是多少时便可以用到MAX(MIN)函数。MAX和MIN两个函数可以用于文本类型、数值类型和日期时间类型的字段上。这两个函数都忽略含有NULL值的记录。下面通过例题学习这两个函数的用法。

从score表中,求“计算机基础”课的考试成绩的最高分和最低分。

SELECT MAX(result1) AS 最高分数,  MIN(result1) AS 最低分数 FROM score
WHERE c_id IN (SELECT ID FROM course WHERE course='计算机基础');

 

求年龄最小(出生日期最大)的学生的姓名,出生日期和所属院系。

SELECT name AS 姓名, birthday AS 出生日期, institute AS 所属院系
FROM student WHERE birthday IN (SELECT MAX(birthday) FROM student);

注意:WHERE子句后的条件表达式不能写成如下形式。

birthday= MAX(birthday),原因是,聚合函数不能出现在WHERE子句中。


统计汇总相异值(不同值)记录

数据库操作中,有时需要统计相异值记录,例如,统计Student表中的学生来自几个地区等。这时可以使用DISTINCT关键字完成统计任务。

统计student表中的学生来自几个地区。

分析:本例只要统计出不同来源地的个数即可。由于student表中的来源地字段中有重复值出现,因此,必须将重复值去掉,然后才能使用COUNT函数统计个数。

SELECT COUNT(DISTINCT(origin)) AS 地区个数 FROM student;

除COUNT函数可以使用DISTINCT以外,上面介绍的其它4个聚合函数中也能使用DISTINCT关键字。

SUM(),AVG(),MAX(),MIN()都是会忽略NULL值的。


数据分组

开始介绍groupby语句。

groupby用来查看一个分类变量有多少类会很快捷。

将student表中的数据,按所属院系字段分组。

SELECT   institute FROM     student GROUP BY institute;

这里需要说明的一点是,如果将上面的SELECT子句字段列表中的“institute”改为星号(*),则会产生一系列的错误 。

通过错误提示可以得到如下启示,如果查询语句带有GROUP BY子句,则SELECT子句中通常不单独使用星号通配符。如果非要单独使用星号通配符,则应当在GROUP BY子句中列出表的所有字段名,字段名之间用逗号分隔。不过这样会使GROUP BY子句失去它的作用。因为,此时并不是按单个字段分组,而是使用GROUP BY后列出的所有字段的组合分组。

如果SELECT子句后是字段名列表,而这些字段名又不在聚合函数中,则应当在GROUP BY子句中列出所有这些字段名。此时,需要注意的还是,分组是按GROUP BY后的所有字段的组合分组,而并非是按单个字段分组。例如“GROUP BY institute, name”表示只有某几个记录中的所属院系和姓名都相同时才把这些记录分为一组。


聚合函数与分组配合使用

将数据分成小组的的很大原因是用于统计汇总,而统计汇总通常都要使用聚合函数,因此,聚合函数和分组经常被人们放在一起使用。

统计student表中,男生的总人数和女生的总人数。

SELECT sex AS 性别, COUNT(*) AS 人数
FROM student GROUP BY sex;

 

统计student表中,每个院系的男生人数。

SELECT institute AS 所属院系, COUNT(*) AS 男生人数
FROM student WHERE sex="男" GROUP BY institute;

 GROUP BY子句中也可以有表达式,就是说可以按照表达式的结果分组数据。

为了方便查看哪一年雇佣了多少名外籍教师,在foreign_teacher表中按雇佣日期的年份统计人数。

SELECT YEAR(hiredate) AS 雇佣年份, COUNT(*) 雇用人数
FROM foreign_teacher GROUP BY YEAR(hiredate);

除COUNT函数以外,GROUP BY子句还可以与其它聚合函数配合使用,下面是SUM函数与GROUP BY子句配合使用的例子。

统计查询course表中必修课的学分总和与选修课的学分总和。

SELECT type AS 类型, SUM(credit) AS 学分总和
FROM course GROUP BY type;

 查询数据的直方图

 

直方图是表示不同实体之间数据相对分布的条状图。在一个查询语句中使用GROUP BY子句,不仅可以查询数据,又可以格式化数据生成图表。请看下面的例题。

从student表中,查询一个表示每个院系学生人数的直方图。

SELECT institute AS 所属院系, RPAD("",COUNT(*)*2,"=") AS 人数对比图
FROM student GROUP BY institute;

排序分组结果

如果想排序分组结果,则应当用使用ORDER BY子句。ORDER BY子句要放在GROUP BY子句的后面。实际上,ORDER BY子句要永远放在其它子句的后面。

在student表中,统计每个院系的学生人数,并按学生人数降序排序。

SELECT institute AS 所属院系, COUNT(*) AS 人数
FROM student GROUP BY institute  ORDER BY COUNT(*) DESC;

 

 


翻转查询结果

从student表中,查询每个院系的男生人数和女生人数。

SELECT institute AS 所属院系, sex AS 性别, COUNT(*) AS 人数
FROM student GROUP BY institute , sex ORDER BY institute;

 执行查询语句后得到的数据虽然正确无误,但是,当人们查看时会很不方便

在MySQL或SQL Server环境中,CASE表达式和GROUP BY子句联合使用会得到很多有用的数据表示,其中就包括反转查询结果的数据表示,请看下面的语句。

SELECT institute AS 所属院系, COUNT(CASE WHEN sex='男' THEN 1 ELSE NULL END) AS 男生人数,
COUNT(CASE WHEN sex='女' THEN 1 ELSE NULL END) AS 女生人数
FROM student GROUP BY institute;

 (其实就是类似pandas里面的.unstack()的用法,反解堆就行。)


使用HAVING子句设置分组查询条件

HAVING子句用于设置分组查询条件,即过滤不需要的分组。该子句通常和GROUP BY子句一起使用。单独使用HAVING子句没有太大的意义。

在student表中,统计计算机系和外语系的学生人数,并按学生人数降序排序。

SELECT institute AS 所属院系, COUNT(*) AS 人数
FROM student GROUP BY institute
HAVING institute IN('计算机学院','外语系')
ORDER BY COUNT(*);

 

另一种实现方式:用WHERE子句代替HAVING子句,其语句如下所示。

SELECT institute AS 所属院系, COUNT(*) AS 人数
FROM student WHERE institute IN('计算机学院','外语系')
GROUP BY institute ORDER BY COUNT(*);

 


HAVING子句与WHERE子句的区别

HAVING子句与WHERE子句之后都写条件表达式,而且都会根据条件表达式的结果筛选数据。但它们是有区别的,主要区别汇总如下。

1.HAVING子句用于筛选组,而WHERE子句用于筛选记录。
2.HAVING子句中可以使用聚合函数,而WHERE子句中不能使用聚合函数。
3.HAVING子句中不能出现既不被GROUP BY 子句包含,又不被聚合函数包含的字段。而WHERE子句中可以出现任意的字段。

通常,HAVING子句总是和GROUP BY 子句配合使用,而WHERE子句可以不用任何子句的配合。下面来看一个非常典型的例子,该例题只能用HAVING子句筛选条件。

统计score表中,考试总成绩大于450分的学生的信息。

SELECT s_id AS 学号, SUM(result1) AS 考试总成绩
FROM score GROUP BY s_id HAVING SUM(result1)>=450
ORDER BY 考试总成绩 DESC;

 

 本例,必须用HAVING子句指定筛选条件,因为只有HAVING子句中才能使用聚合函数,而WHERE子句中不能使用聚合函数。下面使用前面介绍过的一个例题,演示WHERE子句不能用HAVING子句代替的情况。

统计student表中,每个院系的男生人数。

SELECT institute AS 所属院系,COUNT(*) AS 男生人数
FROM student  WHERE sex='男' GROUP BY institute;


 

 

相关文章:

Mysql数据库09——分组聚合函数

类似pandas里面的groupby函数,SQL里面的GROUP BY子句也是可以达到分组聚合的效果。 常用的聚合函数有COUNT(),SUM(),AVG(),MAX(),MIN(),其用法看名字都看的出来,下面一一介绍 聚合函数 COUNT()计数 统计student表中计科系学生的人数。 SE…...

第43章 菜单实体及其约束规则的定义实现

1 Core.Domain.Security.Menu namespace Core.Domain.Security { /// <summary> /// 【菜单--类】 /// <remarks> /// 摘要&#xff1a; /// 通过该实体类及其属性成员&#xff0c;用于实现当前程序【Core】.【领域】.【安全】.【菜单】实体与“[ShopDemo].[…...

OpenAI最重要的模型【CLIP】

最近的 AI 突破 DALLE和 Stable Diffusion有什么共同点&#xff1f; 它们都使用 CLIP 架构的组件。 因此&#xff0c;如果你想掌握这些模型是如何工作的&#xff0c;了解 CLIP 是先决条件。 此外&#xff0c;CLIP 已被用于在 Unsplash 上索引照片。 但是 CLIP 做了什么&…...

分享112个JS菜单导航,总有一款适合您

分享112个JS菜单导航&#xff0c;总有一款适合您 112个JS菜单导航下载链接&#xff1a;https://pan.baidu.com/s/1Dm73d2snbu15hZErJjTXxg?pwdfz1c 提取码&#xff1a;fz1c Python采集代码下载链接&#xff1a;https://wwgn.lanzoul.com/iKGwb0kye3wj base_url "h…...

MySQL 3:MySQL数据库基本操作 DQL

数据库管理系统的一个重要功能是数据查询。数据查询不应简单地返回数据库中存储的数据&#xff0c;还应根据需要对数据进行过滤&#xff0c;确定数据的显示格式。MySQL 提供了强大而灵活的语句来实现这些操作。MySQL数据库使用select语句查询数据。 select [all|distinct]<…...

sql语句的优化

sql优化 优化数据访问 查询性能低下最基本的原因是访问的数据太多&#xff0c;大部分性能低下的查询都可以通过减少访问的数据量来优化所以关于低效的查询&#xff0c;需要确认是否检索了大量不需要的数据&#xff0c;以及mysql服务器层是否在分析大量不需要的数据 因为有些查…...

Shell脚本之——自动安装JDK

目录 1.修改主机名 2.创建文件&#xff0c;单独存放Shell脚本 3.编写Shell脚本 4.Shell脚本命令简介 (1)文件头 (2)打印命令 (3)设置全局变量 (4)条件判断 (5)解压 (6)文件重命名 (7)在/etc/profile指定行插入 5.完整脚本内容 6.重启环境变量 7.判断java是否配置…...

大数据---Hadoop安装Hadoop简易版

编写自动安装Hadoop的shell脚本 完整流程: 大数据—Hadoop安装教程&#xff08;二&#xff09; 文章目录编写自动安装Hadoop的shell脚本上传压缩包编写shell脚本vim hadoopautoinstall.sh运行上传压缩包 在opt目录下创建连个目录install和soft 将压缩包上传到install目录下 …...

Spring框架中使用到的设计模式以及对应的类(方法)

模板方法--->postProcessBeanFactory&#xff0c;onFresh、initPropertySource装饰器模式--->BeanWrapper委托者模式--->BeanDefinitionParseDelegate策略模式--->ClassPathXmlApplicationContext、FileSystemApplicationContext、XMLBeanDefinitionReader、Proper…...

类和类的定义

6.2 类和类的定义 面向对象最重要的概念就是类&#xff08;Class&#xff09;和实例&#xff08;Instance&#xff09;&#xff0c;必须牢记类是抽象的模板&#xff0c;比如学生类&#xff0c;而实例是根据类创建出来的一个个具体的对象&#xff0c;每个对象都拥有相同的方法&…...

丝绸之路——NFT 系列来袭!

丝绸之路的经历讲述了汉朝时代的一个重要历史事件。该系列中的 NFT 带有中国这段黄金时代令人愉悦的视觉元素&#xff0c;使其成为值得收藏的物品。 NFT 系列介绍 敦煌女神像01&#xff08;左&#xff09;&#xff1b;汉代士兵&#xff08;中&#xff09;&#xff1b;敦煌女神像…...

配置CMAKE编译环境:VSCODE + MinGW

一. MinGW安装 MinGW(Minimalist GNU For Windows)是个精简的Windows平台C/C、ADA及Fortran编译器&#xff0c;相比Cygwin而言&#xff0c;体积要小很多&#xff0c;使用较为方便。 MinGW最大的特点就是编译出来的可执行文件能够独立在Windows上运行。 MinGW的组成&#xff…...

六、mybatis与spring的整合

Spring整合Mybaits的步骤 引入依赖 在Spring整合Mybaits的时候需要引入一个中间依赖包mybatis-spring <dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.5</version> </dependency&g…...

JavaWeb--JDBC

JDBC1 JDBC概述1.1 JDBC概念1.2 JDBC本质1.3 JDBC好处2 JDBC快速入门2.1 编写代码步骤2.2 具体操作3 JDBC API详解3.1 DriverManager3.2 Connection3.2.1 获取执行对象3.2.2 事务管理3.3 Statement3.3.1 概述3.3.2 代码实现3.4 ResultSet3.4.1 概述3.4.2 代码实现3.5 案例3.6 P…...

大数据框架之Hadoop:入门(四)Hadoop运行模式

Hadoop运行模式包括&#xff1a;本地模式、伪分布式模式以及完全分布式模式。 Hadoop官方网站&#xff1a;http://hadoop.apache.org/ 4.1本地运行模式 4.1.1官方Grep案例 1.创建在hadoop文件夹下面创建一个input文件夹 [roothdp101 hadoop]# mkdir input2.将Hadoop的xml配…...

《爆肝整理》保姆级系列教程python接口自动化(十一)--发送post【data】(详解

简介  前面登录的是传 json 参数&#xff0c;由于其登录机制的改变没办法演示&#xff0c;然而在工作中有些登录不是传 json 的&#xff0c;如 jenkins 的登录&#xff0c;这里小编就以jenkins 登录为案例&#xff0c;传 data 参数&#xff0c;给各位童鞋详细演练一下。 一、…...

【微服务】Nacos注册中心

&#x1f6a9;本文已收录至专栏&#xff1a;微服务探索之旅 &#x1f44d;希望您能有所收获 &#x1f44d;Nacos和Eureka一样也可以充当服务的注册中心&#xff0c;让我们一起看看有何区别&#xff1f; 点击跳转&#x1f449;【微服务】Eureka注册中心 &#x1f44d;Nacos除了可…...

跟开发打了半个月后,我终于get报bug的正确姿势了

在测试人员提需求的时候&#xff0c;大家经常会看到&#xff0c;测试员和开发一言不合就上BUG。然后开发一下就炸了&#xff0c;屡试不爽&#xff0c;招招致命。 曾经看到有个段子这么写道&#xff1a; 不要对程序员说&#xff0c;你的代码有BUG。他的第一反应是&#xff1a;…...

js万能类型检测Object.prototype.toString.call——定制Object.prototype.toString.call的检测结果

javascript的类型检测 1、typeof typeof操作符可以检测js的基础数据类型&#xff0c;包括number、string、boolean、undefined。因为null在二进制存储的值与object相同&#xff0c;所以typeof检测null会返回object。此为特例 2、instanceof instanceof操作符可以检测某个对…...

激光slam学习笔记2--激光点云数据结构特点可视化查看

背景&#xff1a;不同厂商的激光点云结果存在一定差异&#xff0c;比如有些只有xyz&#xff0c;有些包含其他&#xff0c;如反光率、时间戳、ring等。如何快速判断是个值得学习的点 概要&#xff1a;对于rosbag类型的激光点云&#xff0c;介绍使用rviz快速查看点云结构特点 如…...

Chapter03-Authentication vulnerabilities

文章目录 1. 身份验证简介1.1 What is authentication1.2 difference between authentication and authorization1.3 身份验证机制失效的原因1.4 身份验证机制失效的影响 2. 基于登录功能的漏洞2.1 密码爆破2.2 用户名枚举2.3 有缺陷的暴力破解防护2.3.1 如果用户登录尝试失败次…...

超短脉冲激光自聚焦效应

前言与目录 强激光引起自聚焦效应机理 超短脉冲激光在脆性材料内部加工时引起的自聚焦效应&#xff0c;这是一种非线性光学现象&#xff0c;主要涉及光学克尔效应和材料的非线性光学特性。 自聚焦效应可以产生局部的强光场&#xff0c;对材料产生非线性响应&#xff0c;可能…...

突破不可导策略的训练难题:零阶优化与强化学习的深度嵌合

强化学习&#xff08;Reinforcement Learning, RL&#xff09;是工业领域智能控制的重要方法。它的基本原理是将最优控制问题建模为马尔可夫决策过程&#xff0c;然后使用强化学习的Actor-Critic机制&#xff08;中文译作“知行互动”机制&#xff09;&#xff0c;逐步迭代求解…...

镜像里切换为普通用户

如果你登录远程虚拟机默认就是 root 用户&#xff0c;但你不希望用 root 权限运行 ns-3&#xff08;这是对的&#xff0c;ns3 工具会拒绝 root&#xff09;&#xff0c;你可以按以下方法创建一个 非 root 用户账号 并切换到它运行 ns-3。 一次性解决方案&#xff1a;创建非 roo…...

视频字幕质量评估的大规模细粒度基准

大家读完觉得有帮助记得关注和点赞&#xff01;&#xff01;&#xff01; 摘要 视频字幕在文本到视频生成任务中起着至关重要的作用&#xff0c;因为它们的质量直接影响所生成视频的语义连贯性和视觉保真度。尽管大型视觉-语言模型&#xff08;VLMs&#xff09;在字幕生成方面…...

linux 下常用变更-8

1、删除普通用户 查询用户初始UID和GIDls -l /home/ ###家目录中查看UID cat /etc/group ###此文件查看GID删除用户1.编辑文件 /etc/passwd 找到对应的行&#xff0c;YW343:x:0:0::/home/YW343:/bin/bash 2.将标红的位置修改为用户对应初始UID和GID&#xff1a; YW3…...

Android 之 kotlin 语言学习笔记三(Kotlin-Java 互操作)

参考官方文档&#xff1a;https://developer.android.google.cn/kotlin/interop?hlzh-cn 一、Java&#xff08;供 Kotlin 使用&#xff09; 1、不得使用硬关键字 不要使用 Kotlin 的任何硬关键字作为方法的名称 或字段。允许使用 Kotlin 的软关键字、修饰符关键字和特殊标识…...

JAVA后端开发——多租户

数据隔离是多租户系统中的核心概念&#xff0c;确保一个租户&#xff08;在这个系统中可能是一个公司或一个独立的客户&#xff09;的数据对其他租户是不可见的。在 RuoYi 框架&#xff08;您当前项目所使用的基础框架&#xff09;中&#xff0c;这通常是通过在数据表中增加一个…...

基于Java+MySQL实现(GUI)客户管理系统

客户资料管理系统的设计与实现 第一章 需求分析 1.1 需求总体介绍 本项目为了方便维护客户信息为了方便维护客户信息&#xff0c;对客户进行统一管理&#xff0c;可以把所有客户信息录入系统&#xff0c;进行维护和统计功能。可通过文件的方式保存相关录入数据&#xff0c;对…...

腾讯云V3签名

想要接入腾讯云的Api&#xff0c;必然先按其文档计算出所要求的签名。 之前也调用过腾讯云的接口&#xff0c;但总是卡在签名这一步&#xff0c;最后放弃选择SDK&#xff0c;这次终于自己代码实现。 可能腾讯云翻新了接口文档&#xff0c;现在阅读起来&#xff0c;清晰了很多&…...