资源描述
,项目五、数据查询的实施(简单查询),数据库应用技术,1,任务5：使用聚合函数实现汇总查询 任务6：使用Group by子句实现分类汇总。,项目五、数据查询的实施(简单查询),复习:,数据库的基本运算: 投影运算 选择运算 连接运算,复习:,Select语句实现的命令格式： Select 列名表 From 表名 Where 条件,投影运算,选择运算,引:,在基本查询中，我们只涉及到了： 对表的原始数据进行查询。 对一张表的查询。 请思考： 能否通过查询得到我们所需要的信息？ 例如：平均成绩、最高成绩、最低成绩、男女生人数、各专业人数等 能否同时对多张表中的数据进行查询？ 例如：同时查询学生的姓名、专业名、课程名称、成绩等,数据汇总查询,连接查询,认知目标： 了解数据汇总的基本概念 能力目标: 掌握数据库的汇总查询(重点、难点),学习目标,任务5：使用聚合函数实现汇总查询,数据汇总查询,含义 对查询结果集进行求总和、求平均、求最大值、求最小值的计算称为汇总统计。 使用的方法有三种： 使用聚合函数(重点) Sum()、avg()、count () 、max () 、min () 使用group by子句 (重点),数据汇总查询,聚合函数的作用 用来在查询结果集中产生累加和、平均值、记录个数、最大值、最小值等汇总性的数据，并返回一个单值。 聚合函数的作用范围 既可以是一个表中的全部记录，也可以是由where子句指定的该表的一个子集。 语句格式： select 聚合函数(列名) from 表名 where 条件 说明：当select子句中有聚合函数且没有group by子句时，就不能再指定其它的列名。,最高分 ?,最低分 ?,平均分 ?,成绩表,人数 ?,select from 成绩表 where 课号=1,max(成绩) as 最高分, min(成绩) as 最低分, avg(成绩) as 平均分, count(学号) as 人数,演示,学生表,全部15条记录,where操作 后的11条记录,聚合函数的作用范围,全部记录或由where子句指定表的一个子集。,select 学号, 性别from 学生表 where 班号=1 and 性别=男,数据汇总查询,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：计算该列值非空且不同的记录的个数(不计算重复行)。 * ：计算所有记录的个数。,范例演示:,SELECT count(all name) from sss,结果 5,SELECT count(Distinct name) from sss,结果 4,SELECT count(*) from sss,结果 7,计算该列值非空的记录的个数,默认为all,计算该列值非空且不同的记录的个数(不计算重复行),计算所有记录的个数,数据表,SELECT count(name) from sss,数据汇总查询,实例1：使用聚合函数进行汇总查询。 select avg (成绩) as 平均成绩, max(成绩) as 最高分, min (成绩) as 最低分 from 成绩表,select count (学号) as 女生人数 from 学生表 where 性别=女,数据汇总查询,实例1：使用聚合函数进行汇总查询。 select count (课号) as 课程总数 from 课程表 select count (distinct 课号) as 课程总数 from 成绩表,统计不重复的记录行,数据汇总查询,实例1：使用聚合函数进行汇总查询。 select count (学号) as 女生人数 from 学生表 where 性别=女 select 性别, count (学号) as 女生人数 from 学生表 where 性别=女,注意：当select子句中有聚合函数且没有group by子句时，就不能再指定列名。,数据汇总查询,课堂练习1：完成实现下列功能的汇总语句。 统计成绩表中，成绩不及格的课程门数。 统计成绩表中，成绩不及格的人数和课程门数。,select count (distinct 课程号) as 不及格课程门数 from 成绩表 where 成绩60,select count(distinct 学号) as 不及格人数, count (distinct 课号) as 有不及格学生的课程数 from 成绩表 where 成绩60,什么是分组统计,一家水果店的一天销售情况，要统计各类水果的销售额 。,示例,按水果的种类分组,然后分别汇总,Select 男, count(*) From 学生表 Where 性别=男 Select 女, count(*) From 学生表 Where 性别=女,功能？,统计男生、女生的学生数量。,Select 男, count(*) From 学生表 Where 性别=男 Select 女, count(*) From 学生表 Where 性别=女,功能？,语法格式： select 列名表 from 表名 group by 列名 having 逻辑表达式 ,用于对生成的组进行条件筛选。,按指定的列名进行分组(即列值相同的记录组成一组)，对每一组进行汇总计算。 每一组生成一条记录，且汇总结果按升序排列。,任务6：使用Group by子句实现分类汇总。,数据汇总查询(分类汇总),实例2：分析下列查询语句执行的功能。 select 生源, count (学号) as 生源地人数 from 学生表 group by 生源,统计各生源地的人数.,实例2：在学生表中，统计各班的男女生的人数。,数据汇总查询(分类汇总),Select 班号, 性别, count (性别) as 人数 from 学生表 group by 班号, 性别,注意：select 中的列名必须是group by子句中出现了的列名。,课堂练习2：写出实现下列功能的SQL语句。 统计专业表中，每个系的专业数量。,数据汇总查询(分类汇总),Select 系编号,count(系编号) as 专业数量 from 专业表 group by 系编号,课堂练习2：写出实现下列功能的SQL语句 统计每个学生选修的课程门数。,select 学号， count(*) as 每个学生选修的课程门数 from 成绩表 group by 学号,数据汇总查询(分类汇总),成绩表,实例3：分析下列查询语句执行的功能。 Select 班号,性别, count (性别) as 人数 from 学生表 group by 班号, 性别 having count(性别)11,功能: 统计各班级男女生的人数超过11人的信息。,数据汇总查询(分类汇总),课堂练习3：统计平均成绩超过75分的学生学号和平均成绩。,select 学号， avg(成绩) as 平均成绩 from 成绩表 group by 学号,数据汇总查询(分类汇总),成绩表,课堂练习3：统计平均成绩超过75分的学生学号和平均成绩。,select 学号， avg(成绩) as 平均成绩 from 学生表 group by 学号 having avg(成绩) 75,数据汇总查询(分类汇总),成绩表,Select 班号,count(*) as 男生人数 From 学生表 Where 性别=男,Select 班号,count(*) as 男生人数 From 学生表 Where 性别=男 group by 班号,Select 班号,count(*) as 男生人数 From 学生表 Where 性别=男 group by 班号 having count(*)11,where子句与having子句的区别：,where子句作用于表(在分组之前对表中的记录先筛选)。,having子句作用于组(在分组之后对生成的组进行筛选),Group by 子句实现分组,当where与having同时出现时,先where 后group by 再having,select 学号,课号,成绩 from 成绩表 where 课号=1,select avg(成绩)as平均分 from 成绩表 where 课号=1,例:以下命令执行的结果是：,如果要将这两个查询的结果一起显示 出来，可以用compute by 子句实现。,select 学号,课号,成绩 from 成绩表 where 课号=1 compute avg(成绩),例：查询学习1号课程的全体学生的成绩，并计算平均成绩,如果需将所有课程的成绩均按此格式显示，如何写命令？,6.3.3 数据汇总查询（明细汇总）(计算汇总),格式： select 列名表 from 表名 order by 列名 compute 聚合函数(列名) by 列名 说明：,用computeby进行汇总的列必须按该列排序，所以compute by 必须与order by (排序)连用。,按给定的列进行明细汇总,select 学号,课号, 成绩 from 成绩表 order by 课号 compute avg(成绩) by 课号,用computeby进行汇总的列必须按该列排序，所以compute by 必须与order by (排序)连用。,用课程汇总的列必须按课程排序。,实质是电子表格中的分类汇总。,实例4：分析下列查询语句执行的功能和结果。 select 学号,课程号,成绩 from 成绩表 compute avg(成绩),注意：compute by 必须与order by (排序)连用,select 学号,课号, 成绩from 成绩表 order by 课号 desc -课号降序排列 compute avg(成绩) by 课号 -按课号分组汇总,数据汇总查询（明细汇总）,课堂练习4：写出实现下列功能的SQL语句。 统计课程表中的总学分，并显示明细信息。 按开课学期统计kc表中各期的学分，并显示明细信息。,select 课程名称,开课学期,学分 from kc order by 2 compute sum(学分) by 开课学期,select 课程名称,学分 from 课程表 compute sum(学分),数据汇总查询（明细汇总）,本课小结,数据汇总方法： 用聚合函数、用group by子句、用computeby子句 语法格式： select 列名表 from 表名，n where 条件 group by 列名 having 逻辑表达式 order by 列名 compute 聚集函数 by 列名 ,投影运算,选择运算,连接运算,排序,
展开阅读全文