SQL存储过程触发器实验复习

1.3检索至少有两个学生选修的课程名称
1.3、检索至少有两个学生选修的课程名称
选修课程名称（） 人数（SC.Sno统计）
先组合两个来源表

1.3.1学生的选修课程详细情况（课程名称、学号） select cn,sno from c,sc where o=o
统计人数

1.3.2学生的选修课程情况（课程名称、人数） select cn,count(sno) from c,sc where o=o group by cn having count(sno)>1

举例五：

5、求年龄大于男同学平均年龄的女学生姓名和年龄 男同学平均年龄/姓名和年龄（女学生）
根据相关数据判断 1、SQL嵌套？2、可能用到的SQL？


5.1求男同学平均年龄 select avg(age) from s where sex=‘男’ 5.2求年龄大于（步骤1年龄）的女学生姓名和年龄 select sn,age from s where age>(select avg(age) from s where sex=‘男’)
3、需要分组吗？ 4、需要排序吗？
举例一：题2/4参考

p80
1.1学生的选修课程情况（课程号、人数） 选修课程号（o） 人数（SC.Sno统计） select cno,count(sno) from sc group by cno 统计值有多种情况时，必须分组；分组列为非统计列

1.2检索至少有两个学生选修的课程号 select cno,count(sno) from sc group by cno having count(sno)>1 “至少有两个学生”这条件是在分组前还是分组后？
实验复习

触发器
触发器举例
1临时表 2插入触发器 3删除触发器 4修改触发器

1 临时表

触发器生成的两个临时表： Inserted 表和deleted表
作用： 可以在SQL语句中引用，用于触发器条 件测试，表内容不能修改。
临时表和SQL更新语句的关系：
1）INSERT语句：Inserted表。 2）UPDATE语句：deleted表，Insert 表。 3）DELETE语句：deleted表。
过滤条件
举例二：题3参考

2、检索没有女同学选修的课程号 女同学（S.Sex） 选修的课程号 （o）
先组合两个来源表

2.1学生选课详细信息（课程号、学号、性别） select s.sex,s.sno,o from s,sc where s.sno=sc.sno 2.2有女同学选课的课程号 select distinct o from s,sc where s.sno=sc.sno and s.sex=‘女’
举例四：题8/9参考

4、统计学分为2的每门课程学生平均年龄 学分（C.CT=2）/每门课/年龄（S.Age）
根据相关数据判断可能用到的SQL 多表组合连接+条件+分组统计
4.1学分为2的课程所选学生年龄（组合表数据） Select age,o,sc.sno from s,sc,c where s.sno=sc.sno and o=o and ct=2; 4.2求平均年龄 Select avg(age),o from s,sc,c where s.sno=sc.sno and o=o and ct=2 group by o

举例二（方法2 ）：3
注：1、与Inserted表无关。 2、只是在带有触发器时才出现Deleted表。
2插入触发器

往学生表S插入数据时，如果年龄为空时，设定为19
确定目标表；什么时候动作；相关临时表；动作内容



1、取出插入记录的年龄值（取值） 取值 select age from inserted; 赋值 2、把取出值赋给局部变量（赋值） 判断 DECLARE @cAge int; 动作 select @cAge=age from inserted; 3、修改年龄为19（动作） update s set age=19 where sno=? 4、取出插入记录的主键值 DECLARE @cSno char(2);--类型和数据库定义一致 select @cSno=sno from inserted;
存储过程举例
定义输入输出变量，写出存储过程


2、写出存储过程，假设值用输入变量定义 Create proc proc_avgScore @Cname varchar(20) --和实际数据库定义一致 As select s.sno,s.sn,sc.score from s,sc,c where s.sno=sc.sno and o=o and = @Cname 3、运行测试 EXEC proc_avgScore 'C语言'
定义 赋值om S; delete from S where sno='S8'; select * from S; delete from S where sno='S1'; select * from S;
4修改触发器
确定目标表；什么时候动作；相关临时表；动作内容
2插入触发器

运行测试(删除预先设定的CHECK约束) select * from S; insert into S(SNO,SN,SEX,AGE,DEPT) values('S7','test7','男',17,'计算机'); insert into S(SNO,SN,SEX,DEPT) values('S8','test8','男','计算机'); select * from S;
Insert 语句
①
目标表
Inserted表
注：1、与Deleted表无关。 2、只是在带有触发器时才出现Inserted表。
Update 语句
②
目标表 ①
Inserted表 Deleted表
注：只是在带有触发器时才出现Inserted表 和Deleted表。
Delete 语句
① 目标表 Deleted表

对学生成绩进行修改，如果修改为负值时， 提示‘不能为负值’并取消修改 Create Trigger U_S on SC for update As --取出被修改记录修改后的成绩值 DECLARE @cScore smallint; select @cScore=SCORE from inserted; --判断修改后成绩是否为负值 If @cScore<0 Begin Rollback Transaction;--撤销修改 Print '不能为负值'; end
定义 赋值 判断 动作
4修改触发器
测试(删除预先设定的CHECK约束) Select * from SC where SNO='S5' and CNO='C2' Update SC set SCORE=-89 where SNO='S5' and CNO='C2' Select * from SC where SNO='S5' and CNO='C2'
实验复习

存储过程
存储过程举例

p254
建立proc_avgScore 存储过程。根据指定课程名称 查询课程的学生成绩信息(学号、姓名、成绩)。
确定输入输出变量，写出SQL语句

1、假设课程名为‘x’，写出相关SQL语句 学号(s.sno)/姓名(s.sn)/成绩(sc.score)/课程名称 () select s.sno,s.sn,sc.score from s,sc,c where s.sno=sc.sno and o=o and =‘x’

2.3没有女学生选课的课程号 select distinct cno from sc where cno not in(select distinct o from s,sc where s.sno=sc.sno and s.sex=‘女’) select cno from c where cno not in(select distinct o from s,sc where s.sno=sc.sno and s.sex=‘女’)
举例六：

6、求年龄小于所有男同学年龄的女学生姓名和年龄 所有男同学年龄/女学生姓名和年龄
所有/任何一个(All/ Any)


6.1所有男同学年龄 Select distinct age from s where sex=‘男’ 6.2女学生姓名和年龄 Select sn,age from s where sex=‘女’ And age>all(Select distinct age from s where sex=‘男’)
2插入触发器
确定目标表；什么时候动作；相关临时表；动作内容

定义 往学生表S插入数据时，如果年龄为空时，设定为19 赋值 Create trigger I_S on S 判断 For insert 动作 as -- 取出插入记录的年龄值,主键值 DECLARE @cAge int; DECLARE @cSno char(2); select @cAge=age, @cSno=sno from inserted; --判断是否为空值 If @cAge is null Begin update s set age=19 where sno=@cSno --修改年龄 end

定义 赋值 判断 动作