视图和索引(数据库实验4)

1、 数据库基础与实践实验报告实验四视图和索引班级：惠普测试142学号：1408090213姓名：闫伟明日期：2016-11-13实验目的：1）掌握SQL进行视图创建的方法；2）掌握SQL进行视图更新的方法，理解视图更新受限的原因；3）掌握SQL进行索引创建及删除的方法。实验平台：操作系统：Windowsxp。实验环境：SQLServer2000以上版本。实验内容与步骤利用实验一创建的sch_id数据库完成下列实验内容。1.定义视图V_TCS（定义时不加withcheckoption），存放全部计算机系老师的信息。视图定义代码：CREATEVIEWV_TCSASSELECTtno,tn,sex,a

2、ge,prof,sal,comm,T.dnoFROMT,DWHERET.dno=D.dnoANDD.dn=计算机视图查询语句与查询结果截图SELECT*FROMV_TCS5EHECT*FROMVICSJ结果消息tnctnsexagepnofsalcommdno1T1庄彗蝶女讲师4290D12T3单雨童埶授44003D1定义视图V_sal,存放全体教师的教师号，教师姓名，教师酬金（工资+岗位津贴），职称信息。视图定义代码：CREATEVIEWV_salASSELECTtno教师号，tn教师姓名,sal+comm教师酬金,prof支撑信息FROMT视图查询语句与查询结果截图SELECT*FROMV

3、_salSELECT*FROM视图查询语句与查询结果截图SELECT*FROMV_salSELECT*FROMVsal二结果消息埶师号敎师姓茗埶师酬金支搓信息1T1庄彗蝶7S90讲师2T2庄晓蝶画讲师3T3单雨童SODD斗T4单雨真44皿助教5T5王健林71DD畐燉攪GTG吴哥窟讲师7T7张三4662STS刘伟353.7.6讲师5T&3G&2.S副教授向V_TCS中插入一条计算机专业教师的新记录，并查询V_TCS视图的全部记录。视图更新代码：INSERTINTOV_TCSSELECTT10,良瑗,女,20,讲师,6000,8000,D.dnoFROMDWHERED.dn=WHERED.dn=计

4、算机SELECTXFRCMvTC5消息ClCl行受13响）视图查询语句与查询结果截图SELECT*FROMV_TCSSELECT*FROMVTC5tnotnsexageprofsalcommdno1T1庄彗擦女S9讲師血D30012T1D良谖玄20讲师GflOD枫D13T2庄晓蝶女36讲師46BD1斗T3.单雨童2G敎授44W31向V_TCS中插入一条信息专业教师的新记录，并查询V_TCS视图的全部记录。视图更新代码：INSERTINTOV_TCSSELECTT11,百里登风，男,21,讲师，6000,8000,D.dnoFROMDWHERED.dn=信息$凤男山工J师1,$凤男山工J师1,0

5、00,E000,D.dnoSELECTIll百里FROMDHWHERED.dn=,消息4行受戢响）视图查询语句与查询结果截图：SELECT*FROMV_TCSSELECT*FROMTWHEREtno=T11SELE匚T*FROMVTC5SELECT*FROMTWHEREtncTilJ结果J消息tnotnsex.ageprafsalcommdno1T1庄梦蝶39讲师42903fiOD12T1C良援玄20讲师SDDD13T2庄曉蝶36讲师3WQ14T3单雨童2GWC-3D1tnotnsexageprofsalaommdno1T11百里登凤卑21讲师6M8DDDD7修改V_TCS中的记录，使得编号为

6、t2的教师的职称为副教授。视图更新代码：UPDATEV_TCSSETprof=畐U教授WHEREtno=T2视图查询语句与查询结果截图：SELECT*FROMV_TCSWHEREtno=T26.定义视图V_TCS_ck（定义时加withcheckoption），存放全部计算机系老师的信息，对比该视图与V_TCS在进行数据更新时的差别。视图定义代码：CREATEVIEWV_TCS_ckASSELECTtno,tn,sex,age,prof,sal,comm,T.dnoFROMT,DWHERET.dno=D.dnoANDD.dn=计算机WITHCHECKOPTIONREATEVIEWV_T匚匚址A

7、SSELECTtno,tsex,agefprof,sal,conini,T.dnofromTrdHHmmHHBHHHBHHH订丰SZZ.士二=D上二AXDD迁二i计算机，万二二匚三乂三：ce二工沖.y、：.：；;处讥沖.-.5-V；、：勺:行号2吸*消息命令已成功完成,视图更新对比实验代码及运行结果截图-1.能插入INSERTINTOV_TCS_ckSELECTT12,良莠,女,20,讲师,4000,4000,D.dnoFROMDWHERED.dn=计算机-2.不能插入（视图CHECKOPTION约束）INSERTINTOV_TCS_ckSELECTT13,天明,男,25,讲师,4000,40

8、00,D.dnoFROMDWHERED.dn=信息由消息-行受戢响消息细别!状态S第E行试團进行的抽入或更新已失敗原因是目标视團或者目标视朗癒的某一视圃錠了CtHCKDPTICN,而该撵作的一个或务语句已终止。7.在课程C表的课程名属性上按降序创建唯一索引I_cn。索引定义代码：CREATEINDEXI_cnONC(cnDESC)运行结果截图：命令已成功完成。命令已成功完成。8.删除C表上的索引I_cn。索引删除代码：DROPINDEXC.I_cn运行结果截图：命令已成功完成。命令已成功完成。深入思考与讨论1)请先自己定义一个视图，使定义该视图的查询中使用连接。再更新该视图，观察视图更新受限情

9、况-1.定义：CREATEVIEWV_SCASSELECTsn,S.sno,cn,scoreFROMS,C,SCWHERES.sno=SC.snoANDC.cno=SC.cno-2.查询：SELECT*FROMV_SCS1小三小红12&4-5结杲消息涉童询已成血执行local(10.0艮sn呂仃cnscore笑笑引大学英语昭张明S1QC+NULL4张明SIDJAVANULLG张明S10PythonNULL7舐明SID大学数学NULL3小红56JAVA769小翠S7C：i5S1D张明SID犬学英语NULL11张明SID马克思NULL13张明SID埶据库NULL14舐明SID线代NULL15张明S

10、ID语文NULL16小芳S2C：iNULLSELECT*FROMVE-3.均无法插入（插入影响到多张表）：INSERTINTOV_SCVALUESINSERTINTOV_SCVALUESINSERTINTOV_SCVALUESINSERTINTOV_SCVALUES(笑笑,S1,JAVA,99)(笑笑,S1,RUBY,99)(良垣,S15,JAVA,99)(良莠,S1,JAVA,99)SCscsc5匸SCscsc5匸SI1K1JAVA1f99wHUBY1f95515,JAVA,99SIf1JAVAf99INSERTINSERTINSERTINSERTINTOVINTOVINTOVINTOVVA

11、LUESVALUESVALUESVALUES消息消息440B,级别记状态1,第2L行视厠或函数冗耳不可更新，因为修改会影响梦个基表。SELECT*FROMV_SCWHEREsno=S14.可以更新（修改分数只影响到SC张表）：UPDATEV_SCSETV_SC.score=99WHEREV_SC.cn=大学英语ANDV_SC.sno=S1SELECT*FROMV_SCWHEREsno=S1SELECT=*FROMVSCWHEREsno=511tKKtKttKKKKKt/KKtKKtKtKttKKUUPDATEVSCSETVSC.scQre=99WHESELECT*FROMVSCWHEREsno

12、=l5111结果一打消息snsnocnscone1.-yS1匚卄952S1尢学英语昶snsnocnscaiB1S1C-n-952七”骑mS1大学英语勢母葩已成财行n（local）（10.0RTMDESKTOP-0IJ1SSK-5.无法更新年龄（视图V_SC不包含age列）：UPDATEV_SCSETage=18WHEREV_SC.sno=S1UPDATEVSCSETage=18WHEREVSC.sno=SI1耐象名S匚无知消息吋级别吗状态-第1行列名，旳詁无效。-6.无法更新，修改会影响到S和SC两张表：UPDATEVSCSETsn=韩笑,score=98WHEREVSC.sno=S1UPDA

13、TEVS匚SETsn=11fscore=9EWHEREV9匚.吕口二耳工Lj消息消息4405,级别状态1第1行视图或函数中_日不可更新，因为修改会影响多个基表。-7.将成绩为NULL的课程名设为NULL，更新失败（遵守基表C的cn列的NOTNULL约束）：Lj消息UPDATEVSCSETc.n.=NU匚匚WHEREscore15NULLUPDATELj消息UPDATEVSCSETc.n.=NU匚匚WHEREscore15NULL消息叫级别皿状态S第！行不能将值，皿匸插入列5表achid-diM.c列不允许有Mnii值。update失败*语句已终止。2）请先自己定义一个视图，使定义该视图的查询带

14、分组和集函数。再更新该视图，观察视图更新受限情况。-1.定义：CREATEVIEWV_SC_gpASSELECTsn,S.sno,AVG（score）avg_scoreFROMS,C,SCWHERES.sno=SC.snoANDC.cno=SC.cnoGROUPBYsn,S.snopRE直TEVIEWV_SC_gpASSELECTsnfS.snafJLVG(scareavgbc-qrefromsfcfscHHHHHlHHIWHERE5.sno=SC.snoANDC.c-ilq=SC.c-noGROUPBYsn,5siwWl_J消息命令已成功完成。-2.查询：SELECT*FROMV_SC_gpSELECT*FFLOMVSGgpJ结果-J1消息ansnovg_scona1笑笑iS1972张明S10關3小芳S2734小三S39S5孙珊S477.2G小明57小红S696小翠S7卑5李华S37U-3.无法插入：INSERTINTOV_SC_gpVALUES（刘静,S8,88工凶吕ERT工凶TO占匚_qpVALUES（更1静SEEE_J消息消息44叫级别乐状态一第1行对视團或13数的更新或插入失败，因其包含派生域或常量域。-4.无法更新（avg_score集函数列）：UPDATEV_SC_gpSETavg_score=88WHEREsn=小明JPD