资源预览内容
第1页 / 共11页
第2页 / 共11页
第3页 / 共11页
第4页 / 共11页
第5页 / 共11页
第6页 / 共11页
第7页 / 共11页
第8页 / 共11页
第9页 / 共11页
第10页 / 共11页
亲,该文档总共11页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
实验三 视图、存储过程、触发器及完整性课程名称:数据库系统原理班级:软件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 inserted)if s=false and cid=(select cno from cou where cname=男子汉培养)begin print 男生才能选rollback tran end else begin print 选课成功 commit tran end实验小结:本次实验依语法操作了视图,存储过程和触发器的用法,对语法更熟练了。对限制条件的设置更加熟练了。
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号