第15讲数据库的数据汇总查询

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,34,第,5,章 数据查询,数据汇总查询,单击此处编辑母版标题样式,第,15,讲,第,5,章 数据查询,数据汇总查询,主讲人：贾晓飞,第,15,讲,复习,:,数据库的基本运算,:,投影运算,选择运算,连接运算,19,20,年龄,女,吴玉,1002,男,王成,1001,性别,姓名,学号,投影运算：,在列上进行,选择运算：,在行上进行,复习,:,Select,语句实现的命令格式：,Select,列名表,From,表名,Where,条件,投影运算,选择运算,关系条件,-,运算符：, = != ! !,逻辑条件,-,运算符：,and or not,范围条件,-,运算符：,not betweenand,模式匹配,-,运算符：,not like,列表条件,-,运算符：,not in,空值判断,-,运算符：,is not null,引,:,在基本查询中，我们只涉及到了：,对表的,原始数据,进行查询。,对,一张表,的查询。,请思考：,能否通过查询得到我们所需要的信息？,例如：平均成绩、最高成绩、最低成绩、男女生人数、各专业人数等,能否同时对多张表中的数据进行查询？,例如：同时查询学生的姓名、专业名、课程名称、成绩等,数据汇总查询,连接查询,第,5,章 数据查询,数据汇总查询,学习目标,认知目标：,了解,数据汇总的基本概念,能力目标,:,掌握数据库的汇总查询,(,重点、难点,),数据汇总查询,含义,对查询结果集进行求总和、求平均、求最大值、求最小值的计算称为汇总统计。,使用的方法有三种：,使用聚合函数,(,重点,),sum,、,avg,、,count,、,max,、,min,使用,group by,子句,(,重点,),使用,compute,子句,(,理解,),数据汇总查询,1.,使用聚合函数,聚合函数的作用,用来,在,查询结果集中,产生累加和、平均值、记录个数、最大值、最小值等汇总性的数据，并返回,一个单值,。,聚合函数的作用范围,既可以是一个表中的全部记录，也可以是由,where,子句指定的该表的一个子集。,语句格式：,select,聚合函数,(,列名,),from,表名,where,条件,说明：当,select,子句中,有,聚合函数且没有,group by,子句,时，就,不能再指定其它的列名。,数据汇总查询,1.,使用聚合函数,SQL Server,提供的常用聚合函数有：,AVG,函数：,age,( ,all,|,distinct,列名,),SUM,函数：,sum,( ,all,|,distinct,列名,),MAX,函数：,max,( ,all,|,distinct,列名,),MIN,函数：,min,( ,all,|,distinct,列名,),Count,函数：,count,(,*,| ,all,|,distinct,列名,),All,：,计算该列值非空的记录的个数，默认为,all,。,Distinct,：,计算该列值非空且不同的记录的个数,(,不计算重复行,),。,* ：计算所有记录的个数。,只能用于,数值型数据,数据汇总查询,1.,使用聚合函数,实例,1,：使用聚合函数进行汇总查询。,select,avg,(,成绩,),as,平均成绩,max,(,成绩,),as,最高分,min,(,成绩,),as,最低分,from,xs_kc,select,count,(,学号,),as,女生人数,from,xsqk,where,性别,=0,select,count,(,课程号,),as,课程总数,from,xs_kc,select,count,(,distinct,课程号,),as,课程总数,from,xs_kc,统计不重复的记录行,注意：当,select,子句中,有,聚合函数且没有,group by,子句,时，就,不能再指定列名。,数据汇总查询,1.,使用聚合函数,课堂练习,1,：完成实现下列功能的汇总语句。,统计,xs_kc,表中，成绩不及格的课程门数。,统计,xs_kc,表中，成绩不及格的人数和课程门数。,select,count,(,distinct,课程号,),as,不及格课程门数,from,xs_kc,where,成绩,60,select,count,(,distinct,学号,),as,不及格人数,count,(,distinct,课程号,),as,不及格课程门数,from,xs_kc,where,成绩,2,功能,:,统计各专业男女生的人数超过,2,人的信息。,数据汇总查询,2.,使用,group by,子句,(,分类汇总,),课堂练习,3,：统计平均成绩超过,75,分的学生学号和平均成绩。,select,学号，,avg,(,成绩,),as,超过,75,的平均成绩,from,xs_kc,group by,学号,having,avg,(,成绩,)75,数据汇总查询,2.,使用,group by,子句,(,分类汇总,),where,子句与,having,子句的区别：,where,子句作用于表,(,在分组之前对表中的记录先筛选,),。,having,子句作用于组,(,在分组之后对生成的组进行筛选,),。,having,子句中可以有聚合函数,where,子句中不能有聚合函数。,当二者同时出现时,(P104):,先,where,后,group by,再,having,数据汇总查询,3.,使用,compute by,子句,(,明细汇总,),格式：,select,列名表,from,表名,order by,列名,compute,聚合函数,(,列名,),by,列名 ,说明：,用,computeby,进行汇总的列必须按该列排序，所以,compute by,必须与,order by,(,排序,),连用。,作用,:,对查询结果集的记录进行,明细汇总,。,查询返回的,结果集有两个,:,一个是数据的详细记录。另一个是汇总记录。而,group by,仅显示汇总结果,不显示数据的详细记录。,按给定的列进行明细汇总,数据汇总查询,3.,使用,compute by,子句,(,明细汇总,),实例,4,：分析下列查询语句执行的功能和结果。,select,学号,课程号,成绩,from,xs_kc,compute,avg,(,成绩,),select,学号,课程号,成绩,from,xs_kc,order by,2,desc,compute,avg,(,成绩,),by,课程号,显示数据的详细记录,显示一条汇总记录,降序排列,按课程号分组汇总,注意：,compute by,必须与,order by,(,排序,),连用,注意：,compute by,必须与,order by,(,排序,),连用,数据汇总查询,3.,使用,compute by,子句,(,明细汇总,),课堂练习,4,：写出实现下列功能的,SQL,语句。,统计,kc,表中的总学分，并显示明细信息。,按开课学期统计,kc,表中各期的学分，并显示明细信息。,select,课程名称,开课学期,学分,from,kc,order by 2,compute,sum,(,学分,),by,开课学期,select,课程名称,开课学期,学分,from,kc,compute,sum,(,学分,),本课小结,数据汇总方法：,用聚合函数、用,group by,子句、用,computeby,子句,语法格式：,select,列名表,from,表名,，,n,where,条件,group by,列名,having,逻辑表达式,order by,列名,compute,聚集函数,by,列名, ,投影运算,选择运算,分类汇总,连接运算,排序,连接查询,连接查询就是把多个表中的行按给定的条件进行连接生成新表，从中查询数据。,其语法形式有两种：一种是,ANSI,语法形式；另一种是,SQL,语法形式,Select,列名表,From,表名,1,连接类型,join,表名,2join,表名,n,On,连接条件,Where,查询条件,Select,列名表,From,表名,1 ,，,n,Where,查询条件,AND|OR,连接条件, n,Select *,From,xsqk,inner join,xs_kc,On,xsqk,.,学号,=,xs_kc,.,学号,内连接,Select,xsqk,.,学号,姓名,课程号,成绩,From,xsqk,inner join,xs_kc,On,xsqk,.,学号,=,xs_kc,.,学号,内连接,Select,xsqk,.,学号,姓名,课程号,成绩,From,xsqk,inner join,xs_kc,On,xsqk,.,学号,=,xs_kc,.,学号,Where,成绩,60,内连接,Select,xsqk,.,学号,姓名,课程号,成绩,From,xsqk,left outer join,xs_kc,On,xsqk,.,学号,=,xs_kc,.,学号,左连接,Select,xsqk,.,学号,姓名,课程号,成绩,From,xsqk,right outer join,xs_kc,On,xsqk,.,学号,=,xs_kc,.,学号,右连接,Select a.,学号, b.,学号, a,课程号, b.,课程号, a.,成绩, b.,成绩,From,xs_kc,a join,xs_kc,b,On a.,课程号,=b.,课程号,自连接,Select a.,学号, b.,学号, a,课程号, b.,课程号, a.,成绩, b.,成绩,From,xs_kc,a join,xs_kc,b,On a.,课程号,b.,课程号,子查询,子查询是指在一个,select,语句中再包含另一个,select,语句，外层的,select,语句称为外部查询，内层的,select,语句称为内部查询或子查询。,根据内外查询的依赖关系，子查询分为：相关子查询和嵌套子查询,相关子查询,:,内部查询的执行依赖于外部查询,嵌套子查询,:,内部查询的执行不依赖于外部查询,查询平均分低于,60,分的学生学号和姓名,Select,学号，姓名,From,xsqk,a,Where (select,avg,(,成绩）,from,xs_kc,b where b.,学号,=a.,学号,)60,查询有不及格学生的课程的授课教师,Select distinct,授课教师,From,kc,Where,课程号,=,any(select,课程号,from,xs_kc,b where,成绩,60,查询有不及格学生的课程的授课教师,Select distinct,授课教师,From,kc,Where,课程号,in(select,课程号,from,xs_kc,b where,成绩,60,查询每门课程的最低分,Select *,From,xs_kc,a,Where,成绩,=,all(select,成绩,from,xs_kc,b where b.,课程号,=a.,课程号,查询每门课程的最低分,Select *,From,xs_kc,a,Where,成绩,=(select min,（成绩）,from,xs_kc,b where b.,课程号,=a.,课程号,查询至少有一门课程不及格的学生信息,Select distinct,学号，姓名,From,xsqk,a,Where,exists(select,* from,xs_kc,b where b.,成绩,60,And b.,学号,=a.,学号,课余作业布置,课外练习：,教材,P137,：二、,1120,上机准备（课堂考核）,根据题目的已知条件，在作业本上写出相应的,SQL,语句。,下次上机调试后，交作业。,