当前位置: 首页 > 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快速查看点云结构特点 如…...

【JavaEE】-- HTTP

1. HTTP是什么&#xff1f; HTTP&#xff08;全称为"超文本传输协议"&#xff09;是一种应用非常广泛的应用层协议&#xff0c;HTTP是基于TCP协议的一种应用层协议。 应用层协议&#xff1a;是计算机网络协议栈中最高层的协议&#xff0c;它定义了运行在不同主机上…...

Appium+python自动化(十六)- ADB命令

简介 Android 调试桥(adb)是多种用途的工具&#xff0c;该工具可以帮助你你管理设备或模拟器 的状态。 adb ( Android Debug Bridge)是一个通用命令行工具&#xff0c;其允许您与模拟器实例或连接的 Android 设备进行通信。它可为各种设备操作提供便利&#xff0c;如安装和调试…...

Leetcode 3577. Count the Number of Computer Unlocking Permutations

Leetcode 3577. Count the Number of Computer Unlocking Permutations 1. 解题思路2. 代码实现 题目链接&#xff1a;3577. Count the Number of Computer Unlocking Permutations 1. 解题思路 这一题其实就是一个脑筋急转弯&#xff0c;要想要能够将所有的电脑解锁&#x…...

转转集团旗下首家二手多品类循环仓店“超级转转”开业

6月9日&#xff0c;国内领先的循环经济企业转转集团旗下首家二手多品类循环仓店“超级转转”正式开业。 转转集团创始人兼CEO黄炜、转转循环时尚发起人朱珠、转转集团COO兼红布林CEO胡伟琨、王府井集团副总裁祝捷等出席了开业剪彩仪式。 据「TMT星球」了解&#xff0c;“超级…...

Qwen3-Embedding-0.6B深度解析:多语言语义检索的轻量级利器

第一章 引言&#xff1a;语义表示的新时代挑战与Qwen3的破局之路 1.1 文本嵌入的核心价值与技术演进 在人工智能领域&#xff0c;文本嵌入技术如同连接自然语言与机器理解的“神经突触”——它将人类语言转化为计算机可计算的语义向量&#xff0c;支撑着搜索引擎、推荐系统、…...

关键领域软件测试的突围之路:如何破解安全与效率的平衡难题

在数字化浪潮席卷全球的今天&#xff0c;软件系统已成为国家关键领域的核心战斗力。不同于普通商业软件&#xff0c;这些承载着国家安全使命的软件系统面临着前所未有的质量挑战——如何在确保绝对安全的前提下&#xff0c;实现高效测试与快速迭代&#xff1f;这一命题正考验着…...

云原生玩法三问:构建自定义开发环境

云原生玩法三问&#xff1a;构建自定义开发环境 引言 临时运维一个古董项目&#xff0c;无文档&#xff0c;无环境&#xff0c;无交接人&#xff0c;俗称三无。 运行设备的环境老&#xff0c;本地环境版本高&#xff0c;ssh不过去。正好最近对 腾讯出品的云原生 cnb 感兴趣&…...

使用Spring AI和MCP协议构建图片搜索服务

目录 使用Spring AI和MCP协议构建图片搜索服务 引言 技术栈概览 项目架构设计 架构图 服务端开发 1. 创建Spring Boot项目 2. 实现图片搜索工具 3. 配置传输模式 Stdio模式&#xff08;本地调用&#xff09; SSE模式&#xff08;远程调用&#xff09; 4. 注册工具提…...

Scrapy-Redis分布式爬虫架构的可扩展性与容错性增强:基于微服务与容器化的解决方案

在大数据时代&#xff0c;海量数据的采集与处理成为企业和研究机构获取信息的关键环节。Scrapy-Redis作为一种经典的分布式爬虫架构&#xff0c;在处理大规模数据抓取任务时展现出强大的能力。然而&#xff0c;随着业务规模的不断扩大和数据抓取需求的日益复杂&#xff0c;传统…...

在 Spring Boot 中使用 JSP

jsp&#xff1f; 好多年没用了。重新整一下 还费了点时间&#xff0c;记录一下。 项目结构&#xff1a; pom: <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.0.0" xmlns:xsi"http://ww…...