《实验3吴树森sql.doc》由会员分享，可在线阅读，更多相关《实验3吴树森sql.doc（15页珍藏版）》请在三一办公上搜索。
1、集美大学数据库系统原理课程实验报告课程名称：数据库系统原理班级：软件1214实验成绩：指导教师：吴晓晖姓名：吴树森实验项目名称：实验三 : 视图、存储过程、触发器及完整性学号：201221122104上机实践日期：14.9.实验三 视图、存储过程、触发器及完整性实验目的：掌握视图的创建方法，掌握T-SQL流程控制语句的使用；掌握存储过程及触发器 实验内容：掌握视图的创建方法，掌握T-SQL流程控制语编写，掌握T-SQL 存储过程格式与业务表达上的应用,掌握T-SQL 触发器的语法及业务表达上的应用； 实验要求：单独完成并编写实验报告实验准备：XSCJ数据库（注：在COU表中添加一字段Tname
2、 CHAR（8），并为每个记录添加相应的值。因本章要对数据库进行更新，请先备份XSCJ数据库的2个相关文件。实验步骤1. 视图的创建与应用为表 sc创建一个视图，包括有各个学生学号、选修课程的门数及平均分,如：create view s_grade(sno,c_num,avg_grade) asselect sno,count(cno),avg(grade) from sc group by sno实践下列查询和更新操作的功能，是否出错，若可运行，写出转换至基本表SC上的相应操作。(1) select * from s_grade;(2) select sno,c_num from s_gra
3、de where avg_grade80(3) select sno,avg_grade from s_grade where c_num(select c_num from s_grade where sno=S004)(4) update s_grade set c_num=c_num+2(5)delete from s_grade where c_num32 对SC表创建视图COMPUTER_SC，该视图包括01专业的学生的学号、姓名与性别设计实验完成该视图数据的查询、添加、修改与删除。进一步理解行列子集视图与非行列子集视图应用上的区别这题对SC表？3据SQL SERVER提供的完整性功
4、能自行设计实验完成完整性实践SQL SERVER提供的数据库完整性包括域完整性(CHECK,DEFAULT,UNQUE,RULE，用户自定义类型、基本数据类型)、表间完整性（复习实体完整性、参照完整性的定义与验证）、复杂完整性表达（触发器）1） 主键定义例子一个直接用界面对列右键为主键操作2） 外键定义例子一个alter table sc add constraint fk_sno foreign key (cno) references cou(cno)添加foreign key3）CHECK约束a)定义学生的年龄在16-30之间。DATEDIFF() 函数返回两个日期之间的天数b)定义成绩
5、在0-150之间例：SELECT DATEDIFF(day,2008-12-29,2008-12-30) AS DiffDateb)定义成绩在0-150之间alter table Sc add constraint ck_grade check( grade between 0 and 150 ) c)定义学号由S开头，其余字符为数字字符，长度为4的4） 分别创建一个用户自定义类型，用于定义STU及COU表的学号与课号(CREATE TYPE。FROM、CREATE RULE。AS，SP_BINDRULE,SP_UNBINDRULE等) 学号由S开头，其余字符为数字字符，长度为4的.课号以C开
6、始，其余字符为数字字符，长度为4的sqlP1835） 使用Unique修改MAJOR表，定义专业名具有唯一性。5） Default:性别默认为TRUE（代表男性）6）not null：分别修改major、STU表、COU表，专业名、姓名、课名不可为空5、存储过程创建与使用1）创建存储过程P1，查询学生表所有信息；运行之。create procedure P1 asselect *from STU2）创建存储过程P2，查询某学号学生的所有信息，运行之create procedure P2 sno char(4) asselect *from STU where SNO=sno3）创建存储过程P3
7、，查询某学号学生的姓名及所就读专业，运行之create procedure P3 sno char(4) asselect sname,mname from STU,major where STU.MNO=major.MNO and SNO=sno4）创建存储过程P4，查询C002课程平均分最高的学生学号与姓名，运行之CREATE PROCEDURE P4 ASSELECT STU.SNO,SNAME FROM STU,SC WHERE STU.SNO=SC.SNOAND GRADE=(SELECT MAX(GRADE)FROM SC WHERE CNO=C002)5）创建存储过程P5，查询某
8、课名课程的平均分，并输出之。运行之CREATE PROCEDURE P5 CNAME VARCHAR(30) ASSELECT AVG(GRADE) FROM SC WHERE CNO=(SELECT CNO FROM COU WHERE CNAME=CNAME) 6）创建存储过程P6，完成该生的退学业务处理（即删除某学号学生的所有信息），运行之。create procedure P6 sno char(4) asbegin transactiondelete from SC where SNO=snodelete from STU where SNO=snocommit transactio
9、n7）创建存储过程P7，插入学生S002当前选修的二门课程，并显示已修课程数CREATE PROCEDURE P7 CNO1 CHAR(4),GRADE1 NUMERIC(6,1),CNO2 CHAR(4),GRADE2 NUMERIC(6,1) ASBEGIN TRANSACTION INSERT INTO SC VALUES(S002,CNO1,GRADE1)INSERT INTO SC VALUES(S002,CNO2,GRADE2)SELECT COUNT(*) FROM SC WHERE SNO=S002COMMIT TRANSACTION8)创建存储过程P8，返回每个学生的平均分。
10、create procedure P8 asselect sno,AVG(grade) from SC group by SNO6)。创建存储过程，完成学生状态处理，若学生不合格课程数超过其合格课程数，则留级，否则标注为升学提示：存储过程创建可参考CREATE PROCEDURE的帮助。create procedure P9 AS DECLARE X1 int,X2 int,count_ int,n intset count_=(select count(sno) from stu )set n=1while(n=count_)beginset X1=(SELECT count(*) from
11、 sc where grade=60 and sno=(select top(1) sno from stu where sno not in(select top(n-1) sno from stu ) )IF(X1X2)BEGINUPDATE STU SET MEMO=留级 where sno=(select top(1) sno from stu where sno not in(select top(n-1) sno from stu )ENDIF(X11)1) begin rollback transaction print (插入记录无效) end end2） 每门课最多60个学生
12、。use xscjgocreate trigger t2 on sc after insert asbeginif(select count(sno)from sc group by cno having count(sno)60)60) begin rollback transaction print (每门课最多人选修) end end3） 每个学生最多选修6门课create trigger t3 on sc after insertasbeginif(select count(sno)from sc group by sno having count(sno)6)6)beginrollb
13、ack transactionprint (每个学生最多选修门课)endend4） 删除STU表某学生的记录create trigger t4 on stuafter deleteasbegindelete from sc where sno in (select sno from deleted)end5）“男子汉的培养”课程限男生选修。（注：在课程表中添加一条记录“男子汉的培养”）create trigger t5 on sc after insert asbeginif(select sex from stu where sex=false and sno in(select sno f
14、rom sc where cno in (select cno from cou where cname=男子汉的培养)=0) begin rollback transaction print (“男子汉的培养”课程限男生选修) end end思考题：为学生表设计录入界面，并编程实现之。要求：附界面及运行结果。实验小结：这次实验的内容涉及SQL太多方面，可以说相对实验一的程度是一个飞跃，对实验二来说实验三等同于好几个实验二的量。对表，对视图，对T-SQL，对实验一那时候还不懂要求以后陆续学习再加入的各种约束，存储过程等等都得全部掌握，可以说要做实验3一本SQL的书大半本都得学会，而实验一其实就是简单的界面操作一下表，实验二就是一些选择的SQL语句大概都是一章到两章的内容。通过此次实验感觉SQL大半本书的内容都掌握了，此次实验感觉可以划分开成好几个实验内容实在是太多啦希望老师以后能略作调整。
