数据库原理及应用-8分组聚合函数.ppt
数据库原理及应用,项目一学生管理系统分组聚合函数,知识目标：,查询数据时使用Group by子句分组数据、使用Having子句包含或过滤分组的行。常用分组聚合函数：求平均avg()、求和sum()、求最大值max()、求最小值min()、求行数count(),能力目标：,能够独立完成分组数据数据的操作能力能够根据要求写出正确的分组表达式能够正确使用常用的5个分组聚合函数熟悉SQL语句中Select的语法结构，能修改一般的语法错误,学生管理系统分组聚合函数,拓展训练,操作演示,情境描述,知识概述,教学单元设计,情境描述,完成以下查询任务1、求出计算机信息专业学生的总成绩及平均成绩。2、求出学生年龄最大的和最小的。3、求有手机的人数4、统计出成绩表中的70分以上的个数。5、统计出成绩表中的高等数学70分以上的个数。6、统计出成绩表中70分以上的人数。7、统计出各门功课的总分及最高分。8、统计出每个学生的总分及考试门数9、统计出计算机系每个专业的学生人数10、统计出每个系部的学生总数,Select语句语法结构,Select 列名/聚合函数/表达式FROM 表名/视图名【WHERE 条件】【GROUP BY 分组列名【HAVING 条件】【ORDER BY 排序表达式】,上节课学习了Select语句的哪几个子句？,Oracle 分组聚合函数,组函数对行的集合进行操作，对每组给出一个结果。AVG()、sum（）只能对数字型数据使用。除Count()以外的聚合函数都忽略空值。即空值不参与任何数学运算。用于聚合函数的参数可以是字符型、数字型、日期型。,单独使用聚合函数,1、求出计算机信息专业学生的总成绩及平均成绩。,成绩表,学生表,专业表,Select sum(chengji)“总成绩”,avg(chengji)“平均成绩”from chengji c,xuesheng x,zy Where c.xuehao=x.xuehao and x.zybh=zy.zybh And zy.zyname=计算机信息技术,2、求出学生年龄最大的和最小的?,Select max(chusheng)“年龄最？”,min(chusheng)“年龄最？”from xuesheng,生日,小生日,大生日,3、求有手机的人数,Select count(*)“包括空值统计”,count(shouji)“有手机人数”from xuesheng,查询70分以上的学生成绩单,Select c.xuehao,c.chengji,x.xingming,k.kcming from chengji c,xuesheng x,kecheng kWhere c.xuehao=x.xuehao and c.kcbh=k.kcbh and chengji=70,4、统计出成绩表中的70分以上的个数。,Select count(*)“包括空值统计”,count(chengji)“70分以上行数”from chengji Where chengji=70,5、统计出成绩表中的高等数学70分以上的个数。,Select count(*)“包括空值统计”,count(chengji)“70分以上行数”from chengji c,kecheng k Where c.kcbh=k.kcbh and kcming=“高等数学”and chengji=70,6、统计出成绩表中70分以上的人数,Select count(*)“包括空值统计”,count(chengji)“70分以上行数”,count(distinct chengji)“70分以上?”,count(distinct xuehao)“70分以上人数”from chengji Where chengji=70,聚合函数与GROUP BY子句一起使用时的要求,如果在select 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在Group by子句中。如果未能在Group by子句中包含一个字段列表，会收到一个错误信息。组函数所起的作用是分类汇总之前所得的记录集，因此where子句在分组前就已经对记录行进行了条件过滤。在group by子句中必须包含分类规则列。在group by子句中不能用列别名。,7、统计出各门功课的总分及最高分。,Select kcbh,sum(chengji)“总分”,max(chengji)“最高分”from chengjiGroup by kcbh,kcbh,Select k.kcbh,k.kcming“课程名称”,sum(chengji)“总分”,max(chengji)“最高分”from chengji c,kecheng kWhere c.kcbh=k.kcbhGroup by kcbh，k.kcming,k.kcming,如果不用两个分组字段，还可以怎么写？,在group by子句中不能用列别名,Select k.kcming“课程名称”,sum(chengji)“总分”,max(chengji)“最高分”from chengji c,kecheng kWhere c.kcbh=k.kcbhGroup by kcming,k.kcming,8、统计出每个学生的总分及考试门数,Select c.xuehao,x.xingming“姓名”,sum(chengji)“总分”,count(chengji)“考试门数”from chengji c,xuesheng xWhere c.xuehao=x.xuehaoGroup by c.xuehao,x.xingming,8、统计出每个学生的总分及考试门数,并按总分降序排列,Select c.xuehao,x.xingming“姓名”,sum(chengji)“总分”,count(chengji)“考试门数”from chengji c,xuesheng xWhere c.xuehao=x.xuehaoGroup by c.xuehao,x.xingmingOrder by sum(chengji)desc,8、统计出每个学生的总分及考试门数,显示考试门数在3门以上的学生信息，并按总分降序排列,Select c.xuehao,x.xingming“姓名”,sum(chengji)“总分”,count(chengji)“考试门数”from chengji c,xuesheng xWhere c.xuehao=x.xuehaoGroup by c.xuehao,x.xingmingHaving count(chengji)=2Order by sum(chengji)desc,9、统计出计算机系每个专业的学生人数,Select zyname“专业名”,count(*)“学生人数”from xuesheng x,zy,xibu xbWhere x.zybh=zy.zybh and zy.xbbh=xb.xbbh and xbname=计算机系Group by zyname,10、统计出每个系部的学生总数,Select xbname“系部名”,count(*)“学生人数”from xuesheng x,zy,xibu xbWhere x.zybh=zy.zybh and zy.xbbh=xb.xbbhGroup by xbname,不要一看求总数，就是sum.要看懂题意！,以hr用户登陆，做公司人事系统的查询,1、查询显示employees表中job_id含有“REP”的员工的平均工资、工资总额。,Select avg(salary),sum(salary)from employeesWhere job_id like%REP%,2、查询显示employees表中最早和最晚进入公司的员工的雇用时间,Select min(hire_date),max(hire_date)from employees,3、从employees表中查询显示部门编号为50的部门的员工数,Select count(*)from employeesWhere department_id=50,4、从employees表中查询显示部门编号为80的部门的员工中commission_pct为非空的员工数量,Select count(commission_pct)from employeesWhere department_id=80,5、从employees表中查询显示部门编号为80的部门的员工中commission_pct为唯一的、非空的员工数量,Select count(distinct commission_pct)from employeesWhere department_id=80,6、查询显示employees表中所有commission_pct非空的commission_pct字段的平均值,Select avg(commission_pct)from employees,Avg()本身不包括空值运算。,7、从employees表中查询分类显示每个部门的平均工资,Select department_id,avg(salary)from employeesGroup by department_id,8、从employees表中查询每个部门、每个工种的平均工资和工资总和,Select department_id,job_id,avg(salary),sum(salary)from employeesGroup by department_id，job_id,8、从employees表中查询显示每个部门的平均工资的最大值,Select max(avg(salary)from employeesGroup by department_id,9、从employees表中查询显示部门编号小于50的部门的平均工资,Select department_id,avg(salary)from employeesWhere department_id 50Group by department_id,10、从employees表中查询显示平均工资大于8000的部门的平均工资,Select department_id,avg(salary)from employeesGroup by department_idHaving avg(salary)8000,11、从employees表中查询显示平均工资高于8000的那些部门的平均工资、工资总额，并按平均工资降序排列。,Select department_id,avg(salary),sum(salary)from employeesGroup by department_idHaving avg(salary)8000Order by avg(salary),作业,完成P80自测题实验作业完成 P81页操作题实验报告,
