数据库应用技术数据查询的实施汇总查询总结ppt课件.ppt

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