第5讲 SQ与L数据分组查询 .ppt

* 计算机（软件）学院 Oracle数据库原理与应用 * 计算机（软件）学院 Oracle数据库原理与应用 * 计算机（软件）学院 Oracle数据库原理与应用 * 计算机（软件）学院 Oracle数据库原理与应用 第5讲 数据分组查询 主讲：郭永洪 E-mail：yh_guo@126.com 任务 任务1：使用分组函数 任务2：使用group by和having子句 教学目标 1、学会使用SQL分组函数 2、学会使用group by和having子句 知识目标： 能力目标： 1、SQL分组函数 2、SQL查询group by和having子句 任务1：使用分组函数 任务1-1：使用Max、Min、Avg和Sum函数 任务1-2：使用count函数 任务1：使用分组函数 分组函数用于统计表的数据，作用于多行，并返回一个统计结果。分组函数又称多行函数。 使用范围： （1）分组函数只能出现在选择列、order by子句、having子句中 （2）当使用分组函数时，会忽略null行 （3）如果在选择列表中既包含分组函数，也包含了其它列和表达式，那么这些列和表达式必须出现在group by子句中。 （4）当使用分组函数时，可以在函数中指定all和distinct选项。其中all是默认选项，该选项表示统计所有行数据（包含重复值）；如果指定distinct，则只会统计不同行值。 任务1：使用分组函数 任务1-1：使用Max、Min、Avg和Sum函数 例：查询雇员的最高工资、最低工资、平均工资以及工资总额。 SQL select max(sal),min(sal),avg(sal),sum(sal) from emp; Max函数：取列或表达式最大值； Min函数：取列或表达式最小值； Avg函数：取得列或表达式平均值； Sum函数：取得列或表达式总和。 任务1：使用分组函数 任务1-1：使用Max、Min、Avg和Sum函数 练习1：查询从事销售工作的雇员最高工资、最低工资、平均工资以及工资总额。 练习2：查询部门编号为30的部门雇员最高工资、最低工资、平均工资以及工资总额。 练习3：计算能获得奖金的雇员平均奖。 练习4：计算公司所有雇员的平均奖。（组函数中使用nvl函数） 任务1：使用分组函数 任务1-2：使用count函数 例1：统计公司雇员总人数。 SQL select count(*) as 雇员总人数 from emp; 例2：统计30号部门能挣得奖金的雇员总人数 SQL select count(comm) as 能挣得奖金人数 from emp where deptno=30; Count函数：取得满足条件的记录总行数。 任务1：使用分组函数 任务1-2：使用count函数 练习1：统计在30号部门工作的雇员人数 练习2：统计公司工作为“MANAGER”的雇员人数 练习3：统计公司工资在1100～3000元之间的雇员人数 任务2：使用group by和having子句 任务2-1：使用group by进行单列分组 任务2-2：使用group by进行多列分组 任务2-3：使用having子句限制分组显示结果 任务2-4：分组函数嵌套 任务2：使用group by和having子句 Group by子句用于对查询结果分组统计； having子句用于限制分组显示结果。 任务2：使用group by和having子句 任务2-1：使用group by进行单列分组 例：查询每个部门的平均工资，按部门编号升序排列 SQL select deptno,avg(sal) from emp group by deptno order by deptno; 练习：查询每个岗位的平均工资，按计算结果排序。 基于单列生成分组统计结果，为分组列的每个不同值分别生成统计结果 任务2：使用group by和having子句 任务2-2：使用group by进行多列分组 例：查询每个部门每种岗位的最低工资、最高工资和平均工资，按部门编号排序 SQL select deptno,job,min(sal),max(sal),avg(sal) from emp group by deptno,job order by deptno; 基于两个或两个以上的列生成分组统计结果。 思考？上述group by子句中是否可以漏写job字段。 练习：按所在部门及从事工作分组统计雇员一年内薪水总和，按部门编号排序。 任务2：使用group by和having子句 任务2-3：使用having子句限制分组显示结果 Having子句必须出现在group by子句后面。