第九周数据库实验报告图教案

2．使用 HAVING 筛选结果 (1) 若 要 输 出 满 足 一 定 条 件 的 分 组 ， 则 需 要 使 用 having 关键字。即当完成数据结果的查询和统计后， 可以使用 having 关键字来对查询和统计的结果进行 进一步的筛选。 【例】查询选修了 3 门及三门以上课程的学生学号
select 学号 from 成绩表 group by 学号 having count(*)>=3
(2) where 与 having 的主要区别是各自的作用对象不 同。where 是从基表或视图中检索满足条件的记录。 Having 短语作用于组，从中选择满足条件的组。 【导例】 查询 200501 班均分高于 70 分的学生的班级、 学号、姓名、均分，结果按均分降序排列。
use 教学成绩管理数据库 select 班级号, a.学号, b.姓名, avg(分数) 均分 from 成绩表 as a join 学生表 as b on a.学号 = b.学号 group by 班级号, a.学号, b.姓名
use 教学成绩管理数据库 select 性别, max(年龄) 最大年龄, min(年龄) 最小年龄, avg(年龄) 平均年龄, sum(年龄) 年龄总和, count(*) as 人数 from 学生表 group by 性别
板书或课件版 面设计
(2)group by 子句中不支持对列设置别名，也不支持 任何使用了统计函数的集合列。 3)对 select 子句后面每一列数据除了出现在统计函 数中的列以外，都必须在 group by 子句中应用。例 如:以下查询是错误的。select 后面的列“学号”无
2、带有比较运算符的子查询 当内查询语句返回的结果集是单个值时。返回的单个 值被外部查询的比较操作(如，= 、!=、<、<=、>、>=) 使用。该值可以是子查询中使用集合函数得到的值。
例如 查询与’牛玉龙’在同一个班学习的学生
use 教学成绩管理数据库 select * from 学生表 where where 班级号 = 姓名='牛玉龙') (select 班级号 from 学生表
use 教学成绩管理数据库 select * from 学生表 where 学号 in (select 学号 from 成绩表 On Where ) join 课程表 成绩表.课程号=课程表.课程号 课程名称='计算机基础'
select 学号,姓名 from 学生表 where 班级号='200504' and 学号 not in (select 学号 from 成绩表 where 课程号='c3')
select * from 学生表 where 年 龄 < any (select 年 龄 from 学 生 表 where 班级号='200501')
select * from
学ቤተ መጻሕፍቲ ባይዱ表
where 年 龄 < all (select 年 龄 from 学 生 表 where 班级号='200501')
use 教学成绩管理数据库 select * from 学生表 where where 班级号 in 姓名='牛玉龙') (select 班级号 from 学生表
该查询即为不相关查询，子查询不依赖于父查询。执
行时，先查询牛玉龙所在班级号，查询结果是 200501，然后查询所有在‘200501’班学习的学生 本例也可以通过自连接来完成
1．简单分组 (1)分组是按某一列数据的值或某个列组合的值将查 询出的行分成若干组，每组在指定列或列组合上具有 相同的值。分组可通过使用 group by 子句来实现。 GROUP BY 分组表达式 [,...n ] [HAVING 搜索表达式] 【导例】从“学生信息表”中分别统计出男生和女生 的最大年龄、最小年龄、平均年龄、年龄总和及人数。
【例】找出每个学生超过他选修课程平均成绩的课程 号
select 学号,课程号 from 成绩表 cj1 where 分数> (select AVG( 分 数 ) from 成 绩 表 cj2 where cj1.学号=cj2.学号)
本例为相关子查询，子查询是求一个学生的平均成绩 的，至于是哪个学生的平均成绩要看参数 cj1.学号 的值，该值是与父查询相关的。 3、带有 any（some）或 all 谓词的子查询 any、some 和 all 用于一个值与一组值的比较，使用 any、some 和 all 谓词时必须同时使用比较运算符。 以“>”为例，any、some 表示大于一组值中的任意一 个，all 表示大于一组值中的每一个。比如: >any(1,2,3) 表示大于 1； >all(1,2,3) 表示大于 3。 。 【导例】 （1）查询其他班级中比班号为‘200501’班 级某一学生年龄小的学生姓名和年龄。 （2）查询其他 班级中比班号为‘200501’班级所有学生年龄都小的 学生姓名和年龄
Any，All 与集函数的等价关系： <any 等价于<max >any 等价于>min <all 等价于<min >all 等价于>max =any 等价于 in !=all 等价于 not in
教学后记
having avg(分数)>70 and 班级号='200501 ' order by avg(分数) desc
5.5 子查询 在 SQL 语言中，一个 select-from-where 语句 称为一个查询块。将一个查询块嵌套在另一查 询块中的 where 子句或 having 短语的条件中的 查询称为嵌套查询 上层查询块称为外层查询或父查询，下层查询 块称为内层查询或子查询。子查询语句必须使用
use 教学成绩管理数据库 select * from 学生表 join where 学生表 xs2 on xs1 xs1.班级号=xs2.班级号
xs2.姓名='牛玉龙'
in 表示属于， 即外部查询中用于判断的表达式的值与 子查询返回的值列表中的一个值相等；not in 表示不 属于。 【导例】(1)查询选修了计算机课的学生信息；(2)查 询 200504 班没有学习 c3 课程的学生的学号和姓名。
效， 因为该列既不包含在 聚合函数中， 也不包含在 group by 子句中
(4)也可以根据多列进行分组 【导例】从“学生信息表”中分别分班统计出男生和 女生的最大年龄、最小年龄、平均年龄、年龄总和及 人数，结果按班级编号、性别排列。
use 教学成绩管理数据库 select 班级号, 性别, max(年龄) 最大年龄, min(年龄) 最小年龄, avg(年龄) 平均年龄, sum(年龄) 年龄总和, count(*) as 人数 from 学生表 group by 班级号,性别 order by 班级号,性别
上课时间 课 题
5.7
上课节次
3-4
课型
多媒体
使用分组及嵌套查询
能按照语法格式编写分组语句，嵌套查询语句
教学目的 教学方法
讲授
重点、 难点 嵌套查询 时间分配 教学内容
一、回顾上堂课内容 1、查询结果保存（新表、临时表） 2、聚合函数 3、时间日期函数、数学函数、字符串函数 二、新课内容 5.3.2 使用分组
效，因为该列既不包含在聚合函数中，也不包含在 group by 子句中。
use 教学成绩管理数据库 select 学号, 性别, max(年龄) 最大年龄, min(年龄) 最小年龄, avg(年龄) 平均年龄, sum(年龄) 年龄总和, count(*) as 人数 from 学生表 group by 性别 ---- 错误 列“学号”无
括号括起来。 子查询分两种：不相关子查询和相关子查询。 （1） ．不相关子查询 不相关子查询的执行不依赖于父查询。不相关子查询 的执行过程为：首先执行子查询，子查询得到的结果 集不被显示出来，而是传给外部查询，作为外部查询 的条件使用，然后执行外部查询，并显示查询结果。 子查询可以多层嵌套。 （2） ．相关子查询 子查询的查询条件依赖于父查询。因为子查询的执行 依赖于父查询，所以必须反复求值。 1、带有 in 谓词的子查询 嵌套子查询一般也分为两种：返回单个值和返回值列 表。当子查询产生一系列值时，适合用带 in 的嵌套 查询 语法格式如下 Where 查询表达式 in （子查询） 【导例】 查询与’牛玉龙’在同一个班学习的学生 （1） 确定‘牛玉龙’所在班级
use 教学成绩管理数据库 select 班级号 from 学生表 where 姓名='牛玉龙'
（2） 查找所有在‘200501’班学习的学生
use 教学成绩管理数据库 select * from 学生表 where 班级号='200501'
（3） 将第一步嵌入到第二步查询的条件中， 构成嵌 套查询如下