第5章第2讲数据库的数据汇总查询.ppt

SQL Server 2005SQL Server 2005第5章 数据查询数据汇总查询主讲人:白杨第第1515讲讲2第5章 数据查询数据汇总查询第第1515讲讲复习:数据库的基本运算:uu投影运算投影运算uu选择运算选择运算uu连接运算连接运算1920年龄女吴玉1002男王成1001性别姓名学号投影运算：在列上进行选择运算：在行上进行3第5章 数据查询数据汇总查询第第1515讲讲复习:Select语句实现的命令格式：uSelect 列名表列名表 From 表名表名 Where 条件条件投影运算选择运算 关系条件关系条件-运算符：运算符：=!=!=!=!逻辑条件逻辑条件-运算符：运算符：and or notand or not 范围条件范围条件-运算符：运算符：not betweenand not betweenand 模式匹配模式匹配-运算符：运算符：not likenot like 列表条件列表条件-运算符：运算符：not innot in 空值判断空值判断-运算符：运算符：is not nullis not null4第5章 数据查询数据汇总查询第第1515讲讲引:在基本查询中，我们只涉及到了：uu对表的对表的原始数据原始数据进行查询。进行查询。uu对对一张表一张表的查询。的查询。请思考：uu能否通过查询得到我们所需要的信息？能否通过查询得到我们所需要的信息？vv例如：平均成绩、最高成绩、最低成绩、男女生人数、例如：平均成绩、最高成绩、最低成绩、男女生人数、各专业人数等各专业人数等uu能否同时对多张表中的数据进行查询？能否同时对多张表中的数据进行查询？vv例如：同时查询学生的姓名、专业名、课程名称、成例如：同时查询学生的姓名、专业名、课程名称、成绩等绩等数据汇总查询数据汇总查询连接查询连接查询5第5章 数据查询数据汇总查询第第1515讲讲第5章 数据查询数据汇总查询 学习目标认知目标：uu了解了解数据汇总的基本概念数据汇总的基本概念能力目标:uu掌握数据库的汇总查询掌握数据库的汇总查询(重点、难点重点、难点)6第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询含义uu对查询结果集进行求总和、求平均、求最大值、对查询结果集进行求总和、求平均、求最大值、求最小值的计算称为汇总统计。求最小值的计算称为汇总统计。使用的方法有三种：uu使用聚合函数使用聚合函数(重点重点)uusumsum、avgavg、countcount、maxmax、min min uu使用使用group by子句子句 (重点重点)uu使用使用compute 子句子句 (理解理解)7第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 1.使用聚合函数聚合函数的作用聚合函数的作用uu用来用来在在查询结果集中查询结果集中产生累加和、平均值、记录个数、产生累加和、平均值、记录个数、最大值、最小值等汇总性的数据，并返回最大值、最小值等汇总性的数据，并返回一个单值一个单值。聚合函数的作用范围聚合函数的作用范围uu既可以是一个表中的全部记录，也可以是由既可以是一个表中的全部记录，也可以是由where子句指子句指定的该表的一个子集。定的该表的一个子集。语句格式：语句格式：uu select 聚合函数聚合函数(列名列名)from 表名表名 where 条件 说明：当说明：当select子句中子句中有有聚合函数且没有聚合函数且没有group by子句子句时，就时，就不能再指定其它的列名。不能再指定其它的列名。8第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 1.使用聚合函数SQL Server提供的常用聚合函数有：uuAVGAVG函数：函数：age (all|distinct 列名)uSUM函数：sum (all|distinct 列名)uMAX函数：max (all|distinct 列名)uMIN函数：min (all|distinct 列名)uCount函数：count(*|all|distinct 列名)All All ：计算该列值非空的记录的个数，默认为计算该列值非空的记录的个数，默认为allall。DistinctDistinct：计计算算该该列列值值非非空空且且不不同同的的记记录录的的个个数数(不不计计算算重重复复行行)。*：计算所有记录的个数。：计算所有记录的个数。只能用于只能用于数值型数据数值型数据9第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 1.使用聚合函数实例1：使用聚合函数进行汇总查询。select avg(成绩)as 平均成绩,max(成绩)as 最高分,min(成绩)as 最低分 from 成绩表select count(学号)as 女生人数 from 学生基本信息表 where 性别=女select count(课程编号)as 课程总数 from 成绩表select count(distinct 课程编号)as 课程总数 from 成绩表 统计不重复的记录行注意：当注意：当selectselect子句中子句中有有聚聚合函数且没有合函数且没有group bygroup by子句子句时，就时，就不能再指定列名。不能再指定列名。10第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 1.使用聚合函数课堂练习1：完成实现下列功能的汇总语句。统计成绩表表中，成绩不及格的课程门数。统计成绩表表中，成绩不及格的人数和课程门数。select count(distinct 课程编号)as 不及格课程门数from 成绩表 where 成绩60select count(distinct 学号)as 不及格人数,count(distinct 课程编号)as 不及格课程门数from 成绩表 where 成绩2功能:统计各专业男女生的人数超过2人的信息。15第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 2.使用 group by 子句(分类汇总)课堂练习3：统计平均成绩超过75分的学生学号和平均成绩。uselect 学号，avg(成绩)as 超过75的平均成绩 from 成绩表 group by 学号 having avg(成绩)7516第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 2.使用 group by 子句(分类汇总)where子句与having子句的区别：uwhere子句作用于表(在分组之前对表中的记录先筛选)。having子句作用于组(在分组之后对生成的组进行筛选)。uhaving子句中可以有聚合函数 where子句中不能有聚合函数。当二者同时出现时(P104):uu先先where 后后group by 再再having17第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 3.使用 compute by 子句(明细汇总)格式：uselect 列名表列名表 from 表名表名 order by 列名 compute 聚合函数聚合函数(列名列名)by 列名列名 说明：用用computeby进行汇总的列必须按该列排序，进行汇总的列必须按该列排序，所以所以compute by 必须与必须与order by(排序排序)连用。连用。作用作用:uu对查询结果集的记录对查询结果集的记录进行进行明细汇总明细汇总。uu查询返回的查询返回的结果集有结果集有两个两个:一个是数据的详一个是数据的详细记录。另一个是汇细记录。另一个是汇总记录。而总记录。而group by仅仅显示汇总结果显示汇总结果,不显示不显示数据的详细记录。数据的详细记录。按给定的列进行明细汇总按给定的列进行明细汇总18第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 3.使用 compute by 子句(明细汇总)实例4：分析下列查询语句执行的功能和结果。uselect 学号,课程编号,成绩 from 成绩表 compute avg(成绩)uselect 学号,课程编号,成绩 from 成绩表 order by 2 desc compute avg(成绩)by 课程编号显示数据的详细记录显示数据的详细记录显示一条汇总记录显示一条汇总记录降序排列降序排列按课程编号分组汇按课程编号分组汇总总注意：compute by 必必须与须与order by (排序排序)连用连用注意：compute by 必必须与须与order by (排序排序)连用连用19第5章 数据查询数据汇总查询第第1515讲讲数据汇总查询 3.使用 compute by 子句(明细汇总)课堂练习4：写出实现下列功能的SQL语句。统计课程信息表表中的总学分，并显示明细信息。按开课学期统计课程信息表表中各期的学分，并显示明细信息。uselect 课程名称,开课学期,学分 from 课程信息表 order by 2 compute sum(学分)by 开课学期uselect 课程名称,开课学期,学分 from 课程信息表 compute sum(学分)20第5章 数据查询数据汇总查询第第1515讲讲本课小结数据汇总方法：数据汇总方法：uu用聚合函数、用用聚合函数、用group bygroup by子句、用子句、用computebycomputeby子句子句语法格式：语法格式：select 列名表 from 表名，n where 条件 group by 列名 having 逻辑表达式 order by 列名 compute 聚集函数 by 列名列名 投影运算选择运算分类汇总连接运算排序21第5章 数据查询数据汇总查询第第1515讲讲课余作业布置课外练习：课外练习：uu教材教材P137P137：二、：二、11201120上机准备（课堂考核）上机准备（课堂考核）uu根据题目的已知条件，在作业本上写出相应的根据题目的已知条件，在作业本上写出相应的SQLSQL语语句。句。uu下次上机调试后，交作业。下次上机调试后，交作业。