实验3(86)： 视图、存储过程与触发器.doc

实验三 视图、存储过程、触发器及完整性课程名称：数据库系统原理班级：软件1213实验成绩：指导教师：吴晓晖姓名：俞斌实验项目名称：实验3 学号：201221122086上机实践日期：实验目的：掌握视图的创建方法，掌握T-SQL流程控制语句的使用；掌握存储过程及触发器 实验内容：掌握视图的创建方法，掌握T-SQL流程控制语编写，掌握T-SQL 存储过程格式与业务表达上的应用,掌握T-SQL 触发器的语法及业务表达上的应用； 实验要求：单独完成并编写实验报告实验准备：XSCJ数据库（注：在COU表中添加一字段Tname CHAR（8），并为每个记录添加相应的值。因本章要对数据库进行更新，请先备份XSCJ数据库的2个相关文件。实验步骤1. 视图的创建与应用为表 sc创建一个视图，包括有各个学生学号、选修课程的门数及平均分：实践下列查询和更新操作的功能，是否出错，若可运行，写出转换至基本表SC上的相应操作。(1)select * from s_grade;可以转换后的查询语句为：select sno,count(cno)as c_num,avg(grade) as avg_grade from sc group by sno(2)select sno,c_num from s_grade where avg_grade80可以转换后的查询语句为：select sno,count(cno)as c_num from sc group by sno having avg(grade)80(3)select sno,avg_grade from s_grade where c_num(select c_num from s_grade where sno=S004)可以转换后的查询语句为：select sno,avg(grade)as avg_grade from sc group by sno having count(cno) (select count(cno) from sc group by sno having sno=S004)(4)update s_grade set c_num=c_num+2(5)delete from s_grade where c_num32对SC表创建视图COMPUTER_SC，该视图包括01专业的学生的学号、姓名与性别设计实验完成该视图数据的查询、添加、修改与删除。进一步理解行列子集视图与非行列子集视图应用上的区别3据SQL SERVER提供的完整性功能自行设计实验完成完整性实践SQL SERVER提供的数据库完整性包括域完整性(CHECK,DEFAULT,UNQUE,RULE，用户自定义类型、基本数据类型)、表间完整性（复习实体完整性、参照完整性的定义与验证）、复杂完整性表达（触发器）1）主键定义例子一个2）外键定义例子一个3）CHECK约束a)定义学生的年龄在16-30之间。age between 16 and 30b)定义成绩在0-150之间grade between 0 and 150c)定义学号由S开头，其余字符为数字字符，长度为4的sno like S0-90-90-94）分别创建一个用户自定义类型，用于定义STU及COU表的学号与课号(CREATE TYPE。FROM、CREATE RULE。AS，SP_BINDRULE,SP_UNBINDRULE等) 学号由S开头，其余字符为数字字符，长度为4的.课号以C开始，其余字符为数字字符，长度为4的CREATE TYPE snotype FROM varchar(4) NOT NULLCREATE rule r1 as sno like S0-90-90-9sp_bindrule r1,snotype5）使用Unique修改MAJOR表，定义专业名具有唯一性。ALTER TABLE MAJOR ADD CONSTRAINT IX_MAJOR UNIQUE NONCLUSTERED (MNO ASC)5）Default:性别默认为TRUE（代表男性）ALTER TABLE stu add default true for sex6）not null：分别修改major、STU表、COU表，专业名、姓名、课名不可为空ALTER TABLE stu alter column sname char(8) not nullALTER TABLE major alter column mname char(20) not nullALTER TABLE cou alter column cno char(4) not null4T-SQL1）使用CASE WHEN查询学生的所有信息，若性别为1，则性别显示男，否则显示女2）使用WHILE及游标等对SC表添加一个等级列，若学生成绩80以上等级A，60-79分为B，其余为C5、存储过程创建与使用1）创建存储过程P1，查询学生表所有信息；运行之。2）创建存储过程P2，查询某学号学生的所有信息，运行之3）创建存储过程P3，查询某学号学生的姓名及所就读专业，运行之4）创建存储过程P4，查询C002课程平均分最高的学生学号与姓名，运行之5）创建存储过程P5，查询某课名课程的平均分，并输出之。运行之6）创建存储过程P6，完成该生的退学业务处理（即删除某学号学生的所有信息），运行之。7）创建存储过程P7，插入学生S002当前选修（？）的二门课程，并显示已修课程数8)创建存储过程P8，返回每个学生的平均分。9)。创建存储过程，完成学生状态处理，若学生不合格课程数超过其合格课程数，则留级，否则标注为升学提示：存储过程创建可参考CREATE PROCEDURE的帮助。6触发器应用了解SQL SERVER触发器语法触发器（create trigger）1）对学生表插入一条记录，若该学号存在则提示插入错，否则插入该记录create trigger cr_stu on stu after insert as begin tran tran_stu declare id varchar(10) declare s int set id=(select sno from inserted) set s=(select count(sno) from stu where sno=id) if s=2 begin print 输入错误，已存在该学号rollback tran end else begin print 插入成功 commit tran end2）每门课最多60个学生。create trigger it_sc on sc after insertasbegin tran p_num declare id varchar(10) declare s intset id=(select cno from inserted) set s=(select count(sno) from sc where cno=id) if s60 begin print 选课已满rollback tran end else begin print 选课成功 commit tran end3）每个学生最多选修6门课create trigger pt_sc on sc after insertasbegin tran p_num declare id varchar(10) declare s intset id=(select sno from inserted) set s=(select count(cno) from sc where sno=id) if s6 begin print 您的选课已达上限rollback tran end else begin print 选课成功 commit tran end4）删除STU表某学生的记录create trigger sc_stu on stuafter deleteasbegindeclare sno char(4)select sno=sno from deletedprint 你所删除的学生学号为:+xhend5）“男子汉的培养”课程限男生选修。（注：在课程表中添加一条记录“男子汉的培养”）create trigger sl_sc on sc after insertasbegin tran p_num declare id varchar(4) declare s bitdeclare cid varchar(4)set id=(select sno from inserted) set s=(select sex from stu where sno=id)set cid=(select cno from