实验四数据库更新,视图和数据控制

嘉应学院计算机学院一、实验目的通过实验掌握数据库更新的方法和完整性约束的概念以及视图建立和操作方法。掌握SQLSERVER的数据控制和安全性设计的基本方法,存储过程的建立和使用方法。二、实验原理1、插入数据INSERTINTO表名〉[属性列1〉[,属性列2〉...)]VALUES(常量1[,常量2]...)2、修改数据UPDATE表名SET列名〉=表达式〉[，列名〉=表达式〉]...[WHERE条件〉]3.删除数据DELETEFROM表名[WHERE条件4.建立视图CREATEVIEW视图名〉[（列名〉[，（列名〉]...AS子查询〉[WITHCHECKOPTION]-1-5.授权GRANT权限[，权限]...ON对象类型〉对象名]TO用户[，用户]...[WITHGRANTOPTION]三、实验环境操作系统:WindowsServer2003、Windows7编译环境:SQLServerEnterpriseManager数据库管理系统:MicrosoftSQLServer2005四、实验步骤及内容（一）数据库更新1.在school数据库中，确保表之间已经建立关系，用企业管理器建立数据库的Diagrams-2-2.在course表中删除cno=1的记录，有什么结果？未删除前course表的内容如下:执行删除操作后，结果如下:-3-3.在Diagrams中编辑course表和sc表之间的关系，选择cascadedeleterelatedrecords执行级联删除操作前，三个表之间的关系如图：-4-在SC表中的属性关系里选择级联删除相关记录，如图：再查看三表之间的关系：-5-重复2的操作，观察course表和sc表的变化，有什么结果，为什么？-6-可观察到：Course表与sc表中的cno=‘1’的记录都删除了。-7-因为选择在sc表属性中设置了级联删除这个选项。并且删除了course表和sc表之间的联系4.将course表和sc表的课程号为2的都改为22，如何做？（提示：在Diagrams中编辑course表和sc表之间的关系，选择cascadeupdaterelatedfields)观察并记录结果。建立course表和sc表的关系，并选择级联更新相关的字段执行update语句，并查看：-8-5.在SC表中，（1）将95002同学的成绩都减少10%执行操作前，sc表的内容如下：-9-执行update操作后，如图：（2）课程号为3的改为33，都有什么结果？为什么？执行update操作后，结果如下：-10-分析：UPDATE语句与FOREIGNKEY约束fk_cno冲突。该冲突发生于数据库school，表dbo.Course,column'Cno'。语句已终止。由于产生冲突，所以不允许执行该更新操作6.（1）在STUDENT表中插入一条新纪录：95006李三男21IS执行insert操作：查看student表是否正确插入了相应的数据：-11-（2）在SC表中插入一条新纪录：95008380都有什么结果？为什么？如何完成上述操作？执行insert操作，结果如下：分析原因：原因在于修改sc表中的一个元组，修改后该元组的cno属性的值在course-12-表中找不到一个元组，其cno属性的值与之相等，故拒绝执行此操作。修改要完成上面的操作，先修改student表与sc表之间的关系：然后再执行插入，并查看sc表是否正确插入相应的信息：分析：在Diagrams中编辑student表和sc表之间的关系，强制外键约束选择否（二）建立新用户和数据控制管理1.企业管理器--security--logins-13-建立两个新用户:用户名stu1,密码stu1和用户名stu2,密码stu2defaultdatabase为schooldatabaseaccess选择school-14--15--16-2.企业管理器--database--school--property观察结果-17-3.SQLQueryAnalyzer--用超级用户登录建立存储过程sp1建立存储过程sp2-18-4.企业管理器--database--school--storedprocedure观察结果SQLQueryAnalyzer--用超级用户登录，执行存储过程sp1,sp2execsp1execsp2-19-查看sp1sp2存储过程：-20-企业管理器--database--school--property--permission观察结果-21-企业管理器--database--school--student,course和SC--property--permission观察结果-22--23-5.检验一:(1)在命令行用stu1用户登录（2）在SQLQueryAnalyzer--file--connect以stu2用户登录用stu2登陆：点击确定，结果如下：-24-可见无法登陆。现在查看SQLserver的属性：把仅window改为SQLServer和window（S）：-25-再次登陆。结果如下：-26-A.用SC表建立视图B.更新course表将CNO=1的CPNO改变为22执行更新操作，并查看course表：-27-C.更新course表将CNO=1的Credit改变为22执行更新操作，并查看course表：-28-分析原因：刚开始stu2无法登陆，是因为SQLServer的属性默认为仅window进行身份验证，修改后，便可以登陆。检验二:SQLQueryAnalyzer--file--connect以sa用户登录执行存储过程sp3(1)在命令行用stu1用户登录（2）SQLQueryAnalyzer--file--connect以stu2用户登录用select语句查看student,course和sc三表有什么结果,为什么?-29--30-分析原因：因为各个表的所有查询权限都授权给了stu2,故当用stu2查询各个表时，都能获得查询结果(三)视图建立和操作1.分别用student,course和sc三表建立vstudent,vcourse和vsc三张视图-31-A.在student,course和sc三表中进行update,delete和insert操作,观察vstudent,vcourse和vsc三张视图的变化。在进行update操作之前，vstudent的内容如下：执行updatestudentsetsname='黄思'wheresno='95002'后的结果如下：执行deletefromstudentwheresno='95001'结果如下：-32-执行insertintostudentvalues('95008','李立','男',21,'CS')后，Vstudent的内容如下：对course和sc的操作与对student表的操作同理，即：当数据在更新规则范围内，表变化视图也变化。B.在vstudent,vcourse和vsc三张视图中进行update,delete和insert操作,观察student,course和sc三张基表的变化。执行updatedeleteinsert操作前，student表的内容如下：执行updatevstudentsetsname='张翼'wheresno='95002'后student表的内容如下：执行deletefromstudentwheresno='95008'后student表的内容如下：-33-执行insertintovstudentvalues('95005','李进','男',21,'CS')后，student表的内容如下：2.将成绩高于60的姓名、系名和成绩的结果建立视图V1然后对视图V1进行update,delete和insert操作,观察student,course和sc三张基表的变化。执行updatev1setgrade=95,sname='何宇'wheresname='张翼'，结果如下：执行deletefromv1wheresname='张翼'，结果如下：-34-执行insertintov1values('程生','CS''70')，结果如下：分析原因：当视图由两个表及两个以上的表导出的，则视图不允许更新，当视图在允许更新的前提下，视图的改变也将引起表的改变。五、结论根据实验目的和实验内容，本次实验旨在掌握数据库更新的方法和完整性约束的概念以及视图建立和操作方法并掌握SQLSERVER的数据控制和安全性设计的基本方法,存储过程的建立和使用方法。每个操作都要考虑到很多条件约束，在我们对数据库进行修改前，应该要注意怎样运用这些操作。避免引起错误的执行。