【MySQL基础】简单的分组条件查询练习

文章目录

前言

MySQL的分组查询操作对在数据查询时起到了相当大的作用，能让我们在处理数据时更高效便捷的对数据的处理。

📕往期知识点

📕往期内容回顾
💡 python教程】保姆版教使用pymysql模块连接MySQL实现增删改查
💡 python+requests+BeautifulSoup实现对数据保存到mysql数据库
💡selenium自动化测试实战案例哔哩哔哩信息至Excel
💡 舍友打一把游戏的时间，我实现了一个selenium自动化测试并把数据保存到MySQL

📅分组查询语法

Select 字段列表 from 表名 where(条件) group by 分组字段名 having 分组后过滤条件

where与having区别

• 执行时机不同：where是分子之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

本章节实例使用到了以下表结构及数据，使用前我们可以先将以下数据导入数据库中,再进行查询操作

📙练习题（1）

📌描述

⏰
题目：将数据表按名字进行分组，并统计每个人有多少条记录

用户信息表：class

• 姓名（name）
• 时间（data）
• 大学（登录次数）

如： 第一行表示id等于1，姓名小明，时间2016-04-22 15:25:33 登录次数1

📝 根据示例，你的查询应返回以下结果：

表数据代码

DROPTABLEIFEXISTS`class`;# 如果存在就删除# 创建表CREATETABLE`class`(`id`int(11)NOTNULL,`name`char(10)NOTNULLDEFAULT'',`date`datetimeNOTNULL,`signin`tinyint(4)NOTNULLDEFAULT'0'COMMENT'登录次数',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;# 向表中添加数据INSERTINTO class VALUES('1','小明','2016-04-22 15:25:33','1');INSERTINTO class VALUES('2','小王','2016-04-20 15:25:47','3');INSERTINTO class VALUES('3','小丽','2016-04-19 15:26:02','2');INSERTINTO class VALUES('4','小王','2016-04-07 15:26:14','4');INSERTINTO class VALUES('5','小明','2016-04-11 15:26:40','4');INSERTINTO class VALUES('6','小明','2016-04-04 15:26:54','2');

📙练习题（2）

📌描述

⏰
题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

用户信息表：student

• 设备id（device-id）
• 性别（gender）
• 年龄（age）
• 大学（university）
• 30天内活跃天数字段（active_days_within_30）
• 发帖数量字段（question_cnt）
• 回答数量字段（answer_cnt）

如：
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12。。
.
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

📝
根据示例，你的查询应返回以下结果：

表数据代码

droptableifexists student;# 如果存在# 创建表CREATETABLE`student`(`id`intNOTNULL,`device_id`intNOTNULL,`gender`varchar(14)NOTNULL,`age`int,`university`varchar(32)NOTNULL,`gpa`float,`active_days_within_30`float,`question_cnt`float,`answer_cnt`float);# 向表中增添数据INSERTINTO student VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERTINTO student VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);INSERTINTO student VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);INSERTINTO student VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);INSERTINTO student VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);INSERTINTO student VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);INSERTINTO student VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

📙练习题（3）

📌描述

⏰
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

示例：user_profile表

• 设备id（device-id）
• 性别（gender）
• 年龄（age）
• 大学（university）
• 30天内活跃天数字段（active_days_within_30）
• 发帖数量字段（question_cnt）
• 回答数量字段（answer_cnt）

如：
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12 。。。
.
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

📝
根据示例，你的查询应返回以下结果：

数据表代码

droptableifexists user_profile;# 创建表CREATETABLE`user_profile`(`id`intNOTNULL,`device_id`intNOTNULL,`gender`varchar(14)NOTNULL,`age`int,`university`varchar(32)NOTNULL,`gpa`float,`active_days_within_30`int,`question_cnt`float,`answer_cnt`float);# 向表中添加数据INSERTINTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERTINTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);INSERTINTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);INSERTINTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);INSERTINTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);INSERTINTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);INSERTINTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

📙练习题（4）

📌描述

⏰
题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

示例：user

• 设备id（device-id）
• 性别（gender）
• 年龄（age）
• 大学（university）
• 30天内活跃天数字段（active_days_within_30）
• 发帖数量字段（question_cnt）
• 回答数量字段（answer_cnt）如： 第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12 。。。 最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52 📝根据示例，你的查询应返回以下结果：

表数据代码

droptableifexistsuser;# 创建表CREATETABLE`user`(`id`intNOTNULL,`device_id`intNOTNULL,`gender`varchar(14)NOTNULL,`age`int,`university`varchar(32)NOTNULL,`gpa`float,`active_days_within_30`int,`question_cnt`int,`answer_cnt`int);# 创建表后添加数据INSERTINTOuserVALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);INSERTINTOuserVALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);INSERTINTOuserVALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);INSERTINTOuserVALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);INSERTINTOuserVALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);INSERTINTOuserVALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);INSERTINTOuserVALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

📑题目答案

习题一答案：

select name,count(*)from class groupby name;

习题二答案：

select
gender,university,count(gender),avg(active_days_within_30),avg(question_cnt)from student
groupby university,gender;

习题三答案：

select
university,avg(question_cnt)as avg_question_cnt,avg(answer_cnt)as avg_answer_cnt
from user_profile
groupby university having avg_question_cnt <5or avg_answer_cnt <20;

习题四答案：

select
university,avg(question_cnt)as avg_question_cnt
fromusergroupby university orderby avg_question_cnt;