数据库原理实验报告S7-数据更新及创建视图

1、数据库管理系统SQL Server实验报告实验7 数据更新及创建视图实验日期和时间：2014.10.31实验室：软件实验室班级：12计科3学号：20124122姓名：张翔实验环境：1.硬件：内存 4.00GB 处理器2.50Hz2.软件：Windows 7旗舰版Microsoft SQL Server 2005实验原理：实验主要任务：在学生选课数据库中，对数据库中的各个表实施如下更新操作：1.按下表的定义创建“俱乐部”表：“俱乐部”表字段定义字段名称数据类型字段大小主键俱乐部编号文本4是俱乐部名称文本20成立日期日期/时间俱乐部主席文本6俱乐部地址文本20状态文本4人数Integercreat

2、e table 俱乐部(俱乐部编号char(4) primary key, 俱乐部名称char(20), 成立日期datetime, 俱乐部主席char(6), 俱乐部地址char(20), 状态char(4),人数integer)2.按下表的定义创建“俱乐部成员”表：“俱乐部成员”表字段定义字段名称数据类型字段大小主键俱乐部编号文本4是学号文本10是加入日期日期/时间create table 俱乐部成员(俱乐部编号char(4), 学号char(10), 加入日期datetime, primary key (俱乐部编号,学号), foreign key (俱乐部编号) references

3、俱乐部(俱乐部编号) )3.修改表结构1) 在俱乐部成员表中增加大小为6个字符的姓名字段：alter table 俱乐部成员add 姓名char(6)2) 从俱乐部表中删除俱乐部地址字段： 4.插入记录1) 插入单条记录。2) 插入多条记录。1) 增加一条编号为“0811”、名称为“足球终结者”的俱乐部记录：insertinto 俱乐部(俱乐部编号,俱乐部名称)values(0811,足球终结者)2) 增加一条编号为“0906”、名称为“海龙八极拳会”、俱乐部主席为“蔡小刀”的俱乐部记录：insertinto 俱乐部(俱乐部编号,俱乐部名称,俱乐部主席)values(0906,海龙八极拳会,蔡

4、小刀)3) 增加一条今天参加了编号为“0906”的俱乐部、学号为“2005218141”、姓名为“东方鸿”的俱乐部成员记录：insertinto 俱乐部成员(俱乐部编号,学号,加入日期,姓名)values(0906,2005218141,get date(),东方鸿)4) 学生名单中“031国贸3”班全部男生今天都加入了足球终结者俱乐部，请向俱乐部成员表中插入这些记录： insertinto 俱乐部成员(俱乐部编号,学号,加入日期,姓名)select 俱乐部编号,sno,get date(),snamefrom 俱乐部,student where 俱乐部名称=足球终结者 and ssex=男

5、and sclass=031国贸3班5.修改记录1) 单条记录的单项数据更新。2) 单条记录的多项数据更新。3) 多条记录的某些项数据同时更新。4) 带有子查询的更新。1) 将“海龙八极拳会”更名为“恨地无环八极拳会”，并将其成立日期设置为#2009-1-2#：update 俱乐部set 俱乐部名称=恨地无环八极拳会,成立日期=2009-1-2where 俱乐部名称=海龙八极拳会2) 原“足球终结者”俱乐部中姓陈、姓李的成员全部在2009年3月6日转入“恨地无环八极拳会”，修改这些会员的记录：update 俱乐部成员set 加入日期=2009-3-6,俱乐部编号=(select 俱乐部编号 f

6、rom 俱乐部 where 俱乐部名称=恨地无环八极拳会)where 俱乐部编号=(select 俱乐部编号 from 俱乐部 where 俱乐部名称=足球终结者) and 姓名like 李% or 姓名like 陈%3) 如果某俱乐部没有会员将该俱乐部的状态设置为“停止”update 俱乐部set 状态=停止where not exists (select * from 俱乐部成员 where 俱乐部成员.俱乐部编号=俱乐部.俱乐部编号)6.删除记录1) 删除单条记录。对比一下，对于设置了“级联删除”和没有设置 “级联删除”的两种情况下此操作有何不同的结果？2) 删除多条记录。3) 带有子查

7、询的删除1) 从俱乐部中删除“恨地无环八极拳会”的记录：delete from 俱乐部where 俱乐部名称=恨地无环八极拳会2) 删除足球终结者俱乐部全部成员：delete from 俱乐部成员where 俱乐部编号=(select 俱乐部编号 from 俱乐部 where 俱乐部名称=足球终结者)3) 删除没有会员的俱乐部：delete from 俱乐部where not exists (select * from 俱乐部成员 where 俱乐部成员.俱乐部编号=俱乐部.俱乐部编号)报告应说明在数据的插入、更新、删除操作中如何遵循完整性规则（实体完整性、参照完整性、域完整性、用户定义的完整

8、性）。 在设置级联更新、级联删除的前后，你的更新操作对数据库有何影响？你通过什么样的操作验证你的设置是有效的？ l 创建视图1. 创建视图。（1） 利用SQL 管理平台创建一个视图，命名为VIEW_ZHANG，要求显示学生名单表中所有姓张的同学，用SELECT 查询该视图的学生记录。create view VIEW_ZHANGasselect *from studentwhere sname like 张%（2） 使用Transact-SQL语句创建一个新视图，命名为VIEW_SCORE，要求基表的来源为：要求查询的数据为051财政1班学生的考试成绩，要求显示出姓名，课程名，成绩。用SELEC

9、T 查询该视图的学生记录。create view VIEW_SCOREasselect sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and o=o and sclass=051财政1班（3） 使用Transact-SQL语句创建新视图VIEW_SCORE_NotPass。对数据表和对字段的选择同上，要求只显示不及格的学生的学号、姓名、课程名及成绩。并利用with encryption 加密视图的定义。用SELECT 查询该视图的学生记录。并按课程名排序。create view VIEW_SCORE_NotPas

10、s with encryptionasselect student.sno,sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and o=o and grade60（4） 使用Transact-SQL语句创建新视图VIEW_SCORE_SumAndAverage。要求计算各个班级各门课程的总分及平均分，并把平均分省略到小数点后两位。用SELECT 查询该视图的学生记录。create view VIEW_SCORE_SumAndAverageasselect sclass,cname,sum(grade) score_

11、sum,Convert(decimal(18,2),avg(grade) score_avgfrom sc,course,studentwhere o=o and student.sno=sc.sno group by sclass,cname（5） 使用系统存储过程sp_help显示VIEW_SCORE视图的特征信息。sp_help VIEW_SCORE（6） 使用sp_helptext显示VIEW_SCORE视图在系统表中的定义。sp_helptext VIEW_SCORE（7） 使用sp_helptext查看加密视图VIEW_SCORE_NotPass的定义信息。sp_helptext

12、VIEW_SCORE_NotPass（8） 使用sp_depends显示VIEW_SCORE视图所依赖的对象。sp_depends VIEW_SCORE （9） 利用ALTER VIEW 视图名 AS select 子句 修改视图VIEW_SCORE_NotPass，在该视图中增加一个新的条件，要求只显示数据库原理不及格的学生信息。ALTER VIEW VIEW_SCORE_NotPassASselect student.sno,sname,cname,gradefrom student,sc,coursewhere cname=数据库原理（10） 使用系统存储过程sp_rename来完成把视

13、图“VIEW_SCORE_NotPass”重命名为“VIEW_SCORE_NotPass_数据库原理”。sp_rename VIEW_SCORE_NotPass,VIEW_SCORE_NotPass_数据库原理（11） 创建一个基于表学生名单表建立的新视图VIEW_STUDENT_ 031会计3，要求包含031会计3班的学生记录，显示他们的学号，姓名，性别，班级。create view VIEW_STUDENT_031会计asselect sno,sname,ssex,sclassfrom studentwhere sclass=031会计3班（12） 首先基于视图VIEW_STUDENT_

14、031会计3创建一个包含限制条件的视图VIEW_STUDENT_ 031会计3_男，限制条件为显示031会计3班的男同学信息，然后插入了一条不满足限制条件的记录，如插入一个女生记录，学号2003087777,王菲, 031会计3，女，再用SELECT语句检索视图和表,验证。create view VIEW_STUDENT_031会计_男asselect *from VIEW_STUDENT_031会计3where ssex=男insertinto VIEW_STUDENT_031会计_男values(200308777,王菲,女,031会计3班)（13） 在上述题目（12）的基础上添加WITH

15、 CHECK OPTION选项, 创建一个包含限制条件的视图VIEW_STUDENT_ 031会计3_男_Only。然后插入了一条不满足限制条件的记录，如插入一个女生记录，如学号2003088888,那英, 031会计3，,验证能否插入。create view VIEW_STUDENT_031会计_男_Onlyasselect *from VIEW_STUDENT_031会计where ssex=男WITH CHECK OPTIONinsertinto VIEW_STUDENT_031会计_男_Onlyvalues(200308888,那英,女,031会计3班)（14） 更新数据记录，利用视图

16、VIEW_STUDENT_ 031会计3_男将学号为2003081108的学生姓名改为刘德华。再用SELECT语句检索视图和表,验证update VIEW_STUDENT_031会计_男set sname=刘德华where sno=200308110（15） 删除视图VIEW_STUDENT_031会计3drop view VIEW_STUDENT_031会计3将以上任务的实验完成情况、实验结果、实验原理、总结分栏一一填写到下表中，格式参考任务1或者自定。回答问题：1. 视图和表之间的主要区别是什么？1.视图是已经编译好的sql语句。而表不是 2、视图没有实际的物理记录。而表有。 3、表是内容

17、，视图是窗口 4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改 5、表是内模式，视图是外模式 6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。 7、表属于全局模式中的表，是实表;视图属于局部模式的表，是虚表。 8、视图的建立和删除只影响视图本身，不影响对应的基本表。2. 使用视图的优点有哪些？1. 简单性。 2.安全性。3.逻辑数据独立性。 3. 使用哪些存储过程可以查看视图的信息4. cascade用在视图的什么方面？小结：（不少于100字）你在完成查询任务的过程中遇到的问题及解决的方法有：要求：1. 报告格式和内容要求： a. 内容和格式整齐。大标题采用黑体四号字加粗，小标题采用小四号字加粗。正文采用五号宋体，单倍行距。 b. 贴图时请剪裁到适当大小，要保证打印时可以看清，但也不要太大以免“越界”。 c. 不要在报告