实验3 数据更新及视图.doc

实验三 数据更新及视图一、实验目的：1. 熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、更新和删除操作；2. 熟悉SQL支持的视图操作，能够熟练使用SQL语句创建需要的视图进行查询和取消视图。二、实验内容1.向students中插入编号为200616121且姓名为王小兰的记录2.向teachers中插入（106，张敏，，2400）3.将所有教师工资小于2500元的增加200元4.查询没有被选修的课程信息（提示：可以用外连接实现）5.删除没有被学生选修的课程信息6.定义行列子集视图v1：给出选课成绩合格的学生学号，所选课程号和该课程的成绩7.定义带有集函数的视图v2：将学生的学号及他的平均成绩定义为一个视图8.定义基于多表的视图v3：定义学生姓名、选修课程的名称及讲授课程的教师姓名构成的视图，必要时进行视图列的改名9.定义基于视图的视图v4：在建立视图v1的基础上，定义一个包括学号、学生所选课程数目和平均成绩的视图。10.利用视图查询v1：查询所有选修课程“C程序设计”的学生姓名11.再建立一个视图v5，条件与第6题创建视图v1相同，只是增加“WITH CHECK OPTION”子句，然后执行：（提示：执行前后分别查看操作的视图和相应基本表中的记录）(1)先用视图V5插入（200515123，B003，59）记录，分析执行结果。(2)再用视图V1插入（200515123，B003，59）记录，分析执行结果。12.先查看基本表STC和视图v5和v1中学号为200515123的学生有几条选课记录。分别用V5和V1视图删除学号为200515123的记录，试根据两次Delete语句的执行结果，比较With check option语句的作用。提示：执行前后分别查看操作的视图和相应基本表中的记录的变化13.在v5和v1视图中分别修改学号为200515121选修B002课程的成绩为50分，试根据两次Update语句的执行结果，比较With check option语句的作用。提示：执行前后分别查看操作的视图和相应基本表中的记录14.删除V1视图，然后利用V4视图，查询学号为200415121学生的选修课程数，分析原因。*15.创建v6视图，现实每个学生的学号、姓名和已经修得的总学分。提示：每16个学时为一个学分，即每门课程的学分=学时/16答案：/*1.向students中插入编号为200616121且姓名为王小兰的记录*/INSERTINTO Students(Sno,Sname)VALUES(200616121,王小兰);/*2.向teachers中插入（106，张敏，，2400）*/INSERTINTO Teachers(Tno,Tname,Email,Salary)VALUES(106,张敏,,2400);/*3.将所有教师工资小于2500元的增加200元*/UPDATE TeachersSET Salary=Salary+200WHERE Salary=60 AND STC.Sno=Stu1.SnoGROUP BY STC.Sno,Sname,Cno,ScoreCREATE VIEW v1_1ASSELECT TOP 90 PERCENT STC.Sno,Sname,Cno,ScoreFROM STC,(SELECT Sno,Sname FROM Students)Stu1WHERE Score=60 AND STC.Sno=Stu1.SnoORDER BY STC.Sno,Score DESCWITH CHECK OPTION;CREATE VIEW v1_2ASSELECT STC.Sno,Cno,ScoreFROM STCWHERE Score=60/*7.定义带有集函数的视图v2：将学生的学号及他的平均成绩定义为一个视图*/CREATE VIEW v2ASSELECT x.Sno,Sname,AVG(Score) AVG_ScoreFROM STC x,(SELECT Sno,Sname FROM Students)Stu1WHERE x.Sno=Stu1.Sno GROUP BY x.Sno,Sname/*8.定义基于多表的视图v3：定义学生姓名、选修课程的名称及 讲授课程的教师姓名构成的视图，必要时进行视图列的改名*/CREATE VIEW v3ASSELECT DISTINCT Sname,Cname,TnameFROM STC JOIN (SELECT Sno,Sname FROM Students)Stu1 ON(STC.Sno=Stu1.Sno) JOIN (SELECT Cno,Cname FROM Courses)C1 ON(STC.Cno=C1.Cno) JOIN (SELECT Tno,Tname FROM Teachers)T1 ON(STC.Tno=T1.Tno)CREATE VIEW v3_3SELECT DISTINCT Sname,Cname,TnameFROM STC,(SELECT Sno,Sname FROM Students)Stu1,(SELECT Cno,Cname FROM Courses)C1,(SELECT Tno,Tname FROM Teachers)T1WHERE STC.Tno=T1.Tno AND STC.Cno=C1.Cno AND STC.Sno=Stu1.Sno /*9.定义基于视图的视图v4：在建立视图v1的基础上，定义一个包括学号、学生所选课程数目和平均成绩的视图*/ CREATE VIEW v4AS SELECT Sno,COUNT(Cno) 课程数目,AVG(Score) 平均成绩FROM v1GROUP BY Sno/*10.利用视图查询v1：查询所有选修课程“C程序设计”的学生姓名*/ SELECT Sname,C1.CnameFROM v1,(SELECT Cno,Cname FROM Courses)C1WHERE v1.Cno=C1.Cno AND C1.Cname=C程序设计/*11.再建立一个视图v5，条件与第6题创建视图v1相同，只是增加“WITH CHECK OPTION”子句，然后执行：（提示：执行前后分别查看操作的视图和相应基本表中的记录）*/CREATE VIEW v5ASSELECT Sno,Cno,ScoreFROM STCWHERE Score=60WITH CHECK OPTION;/*(1)先用视图V5插入（200515123，B003，59）记录，分析执行结果*/INSERTINTO v5(Sno,Cno,Score)VALUES(200515123,B003,59);/*(2)再用视图V1插入（200515123，B003，59）记录，分析执行结果*/ INSERTINTO v1_2(Sno,Cno,Score)VALUES(200515123,B003,59);/*总结：在对所创建的视图进行更新时，需注意以下几点：对该视图的更新不能影响多个基本表，即该视图的定义不可包含多个基本表（如多个表连接）该视图不能包含聚合、DISTINCT或GROUP BY 或 UNPIVOT运算符如视图的定义包含 WITH CHECK OPTION ，对视图所进行的更新需满足视图定义的谓词条件（即子查询中的条件表达式）*/ /*12.先查看基本表STC和视图v5和v1中学号为200515123的学生有几条选课记录。 分别用V5和V1视图删除学号为200515123的记录， 试根据两次Delete语句的执行结果，比较With check option语句的作用。 提示：执行前后分别查看操作的视图和相应基本表中的记录的变化*/*(1)用V5删除学号为200515123的记录*/DELETE FROM v5WHERE Sno=200515123;-先执行(1)后执行(2),执行结果:(2 行受影响)-先执行(2)后执行(1),执行结果:(0 行受影响)/*(2)用V1_2删除学号为200515123的记录*/DELETE FROM v1_2WHERE Sno=200515123;-先执行(1)后执行(2),执行结果:(0 行受影响)-先执行(2)后执行(1),执行结果:(2 行受影响)/*分析:WITH CHECK OPTION 对于DELETE 没有影响，DELETE只删除符合视图定义且可以被查询到的数据*/*13.在v5和v1视图中分别修改学号为200515121选修B002课程的成绩为50分， 试根据两次Update语句的执行结果，比较With check option语句的作用。 提示：执行前后分别查看操作的视图和相应基本表中的记录*/ /*(1)用视图V5修改学号为200515121选修B002课程的成绩为50分*/UPDATE v5SET Score=50WHERE Sno=200515121 AND Cno=B002/*执行结果:消息 550，级别 16，状态 1，第 1 行 试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。 语句已终止。 视图更新的记录 所包含列Score=50 不满足约束条件Score=60*/*(2)用视图V5修改学号为200515121选修B002课程的成绩为50分*/UPDATE v1_2SET Score=50WHERE Sno=200515121 AND Cno=B002/*执行结果:(1 行受影响) STC表中学号为200515121 选修B002课程的成绩修改为50分*/ /*14.删除V1视图，然后利用V4视图，查询学号为200415121学生的选修课程数，分析原因。*/*(1)删除v1*/ DROP VIEW v1/*(2)利用V4视图，查询学号为200415121学生的选修课程数*/SELECT Sno,课程数目FROM v4WHERE Sno=200415121/*未删除v1之前，语句可成功执行，删除之后出现如下错误：消息 208，级别 16，状态 1，过程 v4，第 4 行对象名 v1 无效。消息 4413，级别 16，状态 1，第 1 行由于绑定错误，无法使用视图或函数 v4。 说明在执行以语句(2)时，由于在视图v4上建立的查询，故先执行v4，而v4是建立在视图v1之上，v1被删除 导致出现：“对象名 v1 无效”； 由于v4并没有成功执行，所以语句(2)的第一行无法执行：“由于绑定错误，无法使用视图或函数 v4” */*15.创建v6视图，实现每个学生的学号、姓名和已经修得的总学分。 提示：每16个学时为一个学分，即每门课程的学分=学时/16*/CREATE VI