工信版（中职）计算机组装与维护A0506教学课件.pptx
YCF（中职）计算机组装与维护A0506教学课件SQL Server数据库项目教程教学课件项目5：操作数据我们毕业啦其实是答辩的标题地方任务6 简单查询之Group by子句1.知识准备2.聚合函数应用3.Group-By分组4.WHERE和HAVING条件CONTANTS思维导图1.知识准备1.知识准备1.1聚合函数聚合函数对一组值执行计算，并返回单个值。除了COUNT外，聚合函数都会忽略 Null 值。聚合函数经常与 SELECT 语句的GROUP BY子句一起使用。Sql Server 2019 的聚合函数在AVG、COUNT、MAX、MIN、SUM、COUNT_BIG、GROUPING、BINARY_CHECKSUM、CHECKSUM_AGG、CHECKSUM、STDEV、STDEVP、VAR、VARP等。其中AVG、COUNT、MAX、MIN和SUM是常用的聚合函数1.知识准备1.1聚合函数名称描述AVG返回指定组中的平均值，空值被忽略。例：select pID,avg(qPrice)from shops group by pIDCOUNT返回指定组中项目的数量。例：select count(pID)from shopsMAX返回指定数据的最大值。例：select pID,max(qPrice)from shops group by pIDMIN返回指定数据的最小值。例：select pID,min(qPrice)from shops group by pIDSUM 返回指定数据的和，只能用于数字列，空值被忽略。例：select pID,sum(qPrice)from shops group by pID2.聚合函数应用1.2 GROUP BY 分组将查询结果划分为多个行组的 SELECT 语句子句，通常用于在每个组上执行一个或多个聚合。SELECT 语句每组返回一行。对于GROUP BY后面的列表有严格的要求：列必须出现在 SELECT 语句的 FROM 子句中，但不要求出现在 SELECT 列表中。但是 列表中任何非聚合表达式中的每个表列或视图列都必须包括在 GROUP BY 列表中。SELECT 列表中定义的列不能使用别名。2.聚合函数应用1.3 HAVING条件和WHERE条件WHERE 子句的作用是在对查询结果进行分组前，将不符合WHERE条件的行去掉，即在分组之前过滤数据，条件中不能包含聚合函数，使用WHERE条件显示特定的行。HAVING 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用HAVING 条件显示特定的组，也可以使用多个分组标准进行分组。WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。如果有WHERE条件、GROUP BY分组和HAVING等参数，请注意它们的顺序：先是WHERE,GROUP BY,HAVING，最后才是ORDER BY。2.聚合函数应用2.聚合函数应用在数据库dbStudentScore中，完成下面的查询操作。（1）在班级表tbClass中计算新校区共有多少个班级。（2）在班级表tbClass中计算新校区各班级的班级总人数。（3）在成绩表tbScore中计算课程代码为“kc90018”课程的总评成绩进行计算，计算最高分，最低分，平均分。本案例配备微课视频2.1 任务描述2.聚合函数应用综合应用聚合函数可以完成子任务1-3。聚合函数对一组值执行计算，并返回单个值。除了COUNT外，聚合函数都会忽略 Null 值。聚合函数经常与 SELECT 语句的GROUP BY子句一起使用。其中AVG、COUNT、MAX、MIN和SUM是常用的聚合函数2.2 任务分析2.聚合函数应用-1.1在班级表tbClass中计算新校区共有多少个班级。select count(*)as total from tbClass where Campus=新校区go-通常使用*或者主键列进行统计select count(ClassID)as total from tbClass where Campus=新校区go-1.2在班级表tbClass中计算新校区各班级的班级总人数。select sum(StudentTotals)as total from tbClass where Campus=新校区Go-1.3在成绩表tbScore中计算课程代码为“kc90018”课程的总评成绩进行计算，计算最高分，最低分，平均分。select max(ScoreAll)as maxScore,min(ScoreAll)as minScore,avg(ScoreAll)as avgScore from tbScore where CourseID=kc90018 go参考答案3.Group-By分组3.Group-By分组在数据库dbStudentScore中，完成下面的查询操作。（4）在学生表tbStudent中，按性别统计男生和女生的学生总人数。（5）在班级表tbClass中，按校区统计各班级的班级总人数。（6）在成绩表tbScore中，按课程代码统计各个课程的总评成绩大于或者等于70分以上的学生人数。本案例配备微课视频3.1 任务描述3.Group-By分组应用聚合函数和分组可以完成子任务4-6。将查询结果划分为多个行组的 SELECT 语句子句，通常用于在每个组上执行一个或多个聚合。SELECT 语句每组返回一行。对于GROUP BY后面的列表有严格的要求：列必须出现在 SELECT 语句的 FROM 子句中，但不要求出现在 SELECT 列表中。但是 列表中任何非聚合表达式中的每个表列或视图列都必须包括在 GROUP BY 列表中。SELECT 列表中定义的列不能使用别名。3.2任务分析3.Group-By分组-2.1在学生表tbStudent中，按性别统计男生和女生的学生总人数。select sex,count(StudentID)as total from tbStudent group by sexgo-2.2在班级表tbClass中，按校区统计各班级的班级总人数。select Campus,sum(StudentTotals)as total from tbClass group by Campusgo-2.3在成绩表tbScore中，按课程代码统计各个课程的总评成绩大于或者等于70分以上的学生人数。select CourseID,count(*)as total from tbScore where ScoreAll=70 group by CourseIDgo参考答案4.WHERE和HAVING条件4.WHERE和HAVING条件在数据库dbStudentScore中，完成下面的查询操作。（7）在成绩表tbScore中对每门课程的总评成绩计算最高分，最低分，平均分，并把平均分大于等于65 分的数据显示出来。（8）在学生表tbStudent中，计算各个班级团员人数，并把班级团员人数大于等于20人的班级显示出来。（9）按学校的规定，超过50个学生的课程为大班教学课程，学校会对老师的课酬进行加权计算。在课程教学任务表tbTeachingTasks中，统计第1学期大班教学课程情况，显示课程代码，教师工号，选修学生人数。（10）身份证号码规定前1、2位数字表示所在省份的代码。在学生表tbStudent中统计各省份的男生超过10人的人数情况，按2位省份代码升序排序，显示2位省份代码和学生人数。4.1 任务描述4.WHERE和HAVING条件应用WHERE条件和HAVING条件可以完成子任务7-11。WHERE 子句的作用是在对查询结果进行分组前，将不符合WHERE条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用WHERE条件显示特定的行。HAVING 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用HAVING 条件显示特定的组，也可以使用多个分组标准进行分组。本案例配备微课视频4.2 任务分析4.WHERE和HAVING条件WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。HAVING 通常与 GROUP BY 或者聚合函数一起使用，WHERE通常不与聚合函数一起使用。如果有WHERE条件、GROUP BY分组和HAVING等参数，请注意它们的顺序：先是WHERE,GROUP BY,HAVING，最后才是ORDER BY。4.2 任务分析4.WHERE和HAVING条件-3.1在成绩表tbScore中对每门课程的总评成绩计算最高分，最低分，平均分，并把平均分大于等于65 分的数据显示出来。select CourseID,max(ScoreAll)as maxScore,min(ScoreAll)as minScore,avg(ScoreAll)as avgScorefrom tbScoregroup by CourseID having avg(ScoreAll)65go-3.2在学生表tbStudent中，计算各个班级团员人数，并把班级团员人数大于等于20人的班级显示出来。select ClassID,count(StudentID)as total from tbStudent where LeagueMember=是 group by ClassID having count(StudentID)=20go参考答案4.WHERE和HAVING条件-3.3按学校的规定，超过50个学生的课程为大班教学课程，学校会对老师的课酬进行加权计算。在课程教学任务表tbTeachingTasks中，统计第1学期大班教学课程情况，显示课程代码，教师工号，选修学生人数。select CourseID,TeacherID,count(TaskID)as total from tbTeachingTaskswhere Semester=1group by CourseID,TeacherIDhaving count(TaskID)50Go-3.4身份证号码规定前1、2位数字表示所在省份的代码。在学生表tbStudent中统计各省份的男生超过10人的人数情况，按2位省份代码升序排序，显示2位省份代码和学生人数。select left(IDNumber,2)as 2位省份代码,count(StudentID)as total from tbStudent where sex=男group by left(IDNumber,2)having count(StudentID)10order by left(IDNumber,2)ASC -请注意order by 写在后面go参考答案THANKS
