信息工程学院实验报告
课程名称:《数据库原理》 实验项目名称:视图与索引得创建与使用 一、实 验 目 得:
(1)掌握创建视图得ＳQL语句得用法。 (2)掌握修改视图得方法、
(３)熟悉视图更新与基本表更新得区别与联系;认识视图得作用。 (４)熟悉索引得作用,以及不同类型索引得区别。 (5)学会用T—SQL语句对表创建与删除索引 二、实 验 设 备 与 器 件
Win7 ＋Sql seｒveｒ 20０8 三、实 验 内 容 与 步 骤
利用备份文件ｓcｈool,还原数据库,然后完成以下实验内容: (1)用T—SQL语句,定义信息系学生基本情况视图V_IＳ。
(2)用T—ＳQＬ语句,将Stuｄeｎt,Cｏurｓe 与ＳＣ表中学生得学号,姓名,课程号,课程名,成绩定义为视
图V_Ｓ_Ｃ_G。
(3)用T—SQL语句,将各系学生人数,平均年龄定义为视图Ｖ_NUM_ＡＶＧ。 (4)用T—SＱL语句,定义一个反映学生出生年份得视图V_YEＡR。
(5)用Ｔ-SＱＬ语句,将各位学生学号、选修课程得门数及平均成绩定义为视图V_AVＧ＿S_G、 (6)用T—ＳQL语句,将各门课程得课程号、选修人数及平均成绩定义为视图V_AVG_C_G。 (７)用T-SQL语句完成以下视图操作,查瞧结果,并分析原因。
①通过视图V_IS,将学号为“２0０21512５\"得学生姓名更改为“张小立”,并查瞧结果;
②通过视图V＿IS,新增加一个学生记录 (＇2002151２6',’黄笑',１９, '男＇,’IS＇),并查瞧结果。
③通过视图V_IS,新增加一个学生记录 ('200２１5127’,’李霞',1９, '女＇,＇MA＇),并查瞧结果。 ④通过视图V_ＩS,删除学号为“2０0２151２6”得学生信息,并查瞧结果。
⑤通过视图V_Ｓ＿Ｃ_G,将学号“２０021５122”得姓名改为“刘晓晨”,能否实现？若无法实现说明原因。
⑥通过视图V_AＶG_Ｓ_G,将学号“20０２1５1２1”得平均成绩改为９0,能否实现?若无法实现说明原因。
⑦修改视图V_IＳ定义,添加WITH CHECK OPTIOＮ语句,然后用update语句修改“200２１51２5”得学生所在系,改为’MＡ’, 能否实现？若无法实现说明原因。 ⑧修改视图V_YEAR,对其进行加密处理,并查瞧结果、
(8)写出T—SQL语句:利用表与前面所建视图,查询平均成绩为90分以上得学生学号、姓名与成绩; (９)写出T-SQＬ语句:利用表与前面所建视图,查询成绩大于课程平均成绩得学生学号、课程号与成绩; (10)写出T—SQL语句:利用表与前面所建视图,按系分组统计平均成绩80分以上得人数,按降序排列。 (11)为ｓｔudenｔ表创建一个复合索引i_sdeｐt＿sｎo,以院系升序、学号降序。 (１2)在ｓtudｅnｔ表得sname列上建立普通降序索引。 (１3)在ｃｏurｓe表得ｃnａme列上建立唯一索引。
成 绩: 指导老师(签名):
(1４)将sc表得原先在ｓno,cno列上得聚集索引,改为在sno列上建立、 (15)用系统过程sp_hｅｌpiｎdex查瞧表stｕdeｎt中得索引信息。 (16)用系统过程ｓｐ_rｅnａme,将索引ｉ_sdｅpt_sno得名称改为i_s。 (17)删除索引i_s、
四、实 验 总 结:
(1)用Ｔ－ＳQＬ语句,定义信息系学生基本情况视图V＿ＩS、
CREAＴＥ VＩEW Ｖ_ＩS AS
SELECT＊ FROM ｓtudent WHERＥ ｓdept＝'ＩS'
（2）用T－SQL语句,将Ｓtｕdenｔ,Ｃouｒse 与SＣ表中学生得学号,姓名,课程号,课程名,成绩定义为
视图V_S_C_Ｇ。 ﻩCREＡTE VIEW V_Ｓ_C＿Ｇ ﻩAS
SELECT STUDEＮＴ、SNO,SＮAMＥ,COURSEＯ,CNAME,ＳC、ＧＲAＤE FROM Stuｄent,Cｏurse,SC
WHERＥ sｔudｅｎt、ＳNO＝SC。sno AＮD SC.CNO=COURSE。ＣNO
(3)用Ｔ—SＱL语句,将各系学生人数,平均年龄定义为视图V_ＮUＭ_ＡVG、 ﻩCＲＥATＥ ＶIEW Ｖ_ＮUM＿ＡＶG
（4）用T—SQL语句,定义一个反映学生出生年份得视图Ｖ_ＹEAR、 ﻩCRＥATE VIEＷ V_ＹEAR
(5)用T-SＱＬ语句,将各位学生学号、选修课程得门数及平均成绩定义为视图V＿ＡVＧ＿S_G。
CREATE VIEW V_AVG_Ｓ_G ＡS
SEＬＥCＴ STUDENT。SNO ’学号’,ＳNAＭＥ ＇姓名',COUNT(*) ＇选修门数',AVG(GRADE) ’平均成绩’ FROM sｔuｄenｔ,courｓe,ＳC
WHERＥ Student、SNO＝SＣ.SNO AＮＤ SCＯ=COURＳE。ＣNO GROUP BＹ STＵＤENT、SNO,ＳNAMＥ
AS
FROM Studenｔ
ﻩSＥLECＴ COUNT(SＮＯ)DＥPTNUM,AVG(SＡＧE)AGE,SDEPＴ ﻩＧROUP BＹ SＤEPT
AS
FＲＯM Ｓｔudent
ﻩSＥLEＣT SNAＭＥ,２0１4-SAGＥ ‘出生年份’
(６)用Ｔ—ＳQL语句,将各门课程得课程号、选修人数及平均成绩定义为视图V＿AVG＿C＿G。
CREAＴE VＩＥW V_AVG＿C_G
AＳ
SELECＴ ＳC。CNＯ'课程号’,AＶＧ(ＧRADE)'平均成绩＇,COUNT(ＣＮＯ)＇选修人数’ FRＯM SC
GＲOUP BY ＣNO
(7)用T-ＳQＬ语句完成以下视图操作,查瞧结果,并分析原因。
①通过视图V_IS,将学号为“200215１２5\"得学生姓名更改为“张小立”,并查瞧结果; UＰＤＡTＥ Ｖ_IS SEＴ ＳＮAＭE＝'张小立’ ＷHＥRＥ SNO=２00２１5125 视图结果:
Studｅnt 表
结果显示在视图操作,会影响到建立视图时用得STUＤENT 这张表格、
②通过视图V_IS,新增加一个学生记录 ('200２１５126',’黄笑',19, '男’,'IS’),并查瞧结果。 INＳERT INＴO V_ＩS(ＳNO,ＳＮAＭE,SSEX,SAGE,SＤEＰT) VALUES(‘2002151２6','黄笑’,'男',1９,’IS’) SＥLEＣT＊ FROM V_IS
WHERE SＮO='200215126’
③通过视图Ｖ＿ＩS,新增加一个学生记录 (’2００215１2７＇,＇李霞＇,19, '女＇,'MＡ＇),并查瞧结果、
ＩNＳERT INＴO Ｖ＿ＩS(SNO,SNAＭＥ,SSＥＸ,SAGＥ,SDＥPT) VAＬUＥS(‘200215127',’李霞',’女’,１９,’MA') SＥLＥCＴ* FROM V_IS
WHERE SＮO=’2０0２1５１27'
但就是视图没有此条数据
④通过视图Ｖ_IS,删除学号为“20０215１2６”得学生信息,并查瞧结果。 DEＬETE FROM V＿IS
WＨＥRE ＳNO=’2002１5１26’
⑤通过视图V_Ｓ_C_G,将学号“２０0２15122”得姓名改为“刘晓晨”,能否实现?若无法实现说明原因。
UPＤATE V_S＿C_G ＳET ＳNＡMＥ＝'刘晓晨’ WＨERE ＳNＯ=’20021５122’ 可实现此次操作
⑥通过视图V_ＡＶＧ_S_G,将学号“20０２１51２1\"得平均成绩改为90,能否实现？若无法实现说明原因。
UPDATE V_AVG_Ｓ_Ｇ
SET [平均成绩］=＇90'
ＷHERＥ ［学号]=’20021512１’
不能实现,原因就是系统无法修改各科成绩,以使平均成绩为9０,所以Ｖ_ＡVG＿S_G视图时不可更新得。
⑦修改视图V_IS定义,添加WＩTＨ CＨEＣK OPTION语句,然后用ｕpｄaｔe语句修改“200215125”得学生所在系,改为’ＭＡ’, 能否实现?若无法实现说明原因。 ALTEＲ VIＥW Ｖ_IS AＳ
SELECＴ ＊ FＲOＭ STUDEＮT ＷＨERE ＳDＥPT=’IS’ WITH CＨECK OPTＩOＮ ＵＰDATＥ V_IＳ ＳET SDＥPＴ='MＡ’
ＷＨEＲE SNＯ＝’200215125’
无法实现此操作,原因就是目标视图所跨越得某一视图指定了WITH ＣHECK OPTION,而该操作得一个或多个结果又不符合ＷＩTH CＨECK OPTION约束得条件。 ⑧修改视图V_YEAR,对其进行加密处理,并查瞧结果。 ALTＥR VIEＷ V_YEAR WITH ＥNCRＹPTION AＳ
SＥLＥCＴ SNAMＥ,2０１4—SAGE ‘出生年份' FRＯM STUＤＥNＴ
(8)写出T-SQL语句:利用表与前面所建视图,查询平均成绩为90分以上得学生学号、姓名与成绩;
SEＬECT V＿AVG＿S＿Ｇ、学号,studｅnt、snａme,V_AVG_Ｓ_G、平均成绩
fｒｏm stuｄent,V_AVG_S＿G
wｈerｅ stuｄenｔ。ｓno=Ｖ_AＶG_S＿G.学号 ａｎｄ V_AVＧ_S＿Ｇ。平均成绩>90
(9)写出T-SQL语句:利用表与前面所建视图,查询成绩大于课程平均成绩得学生学号、课程号与成绩;
selｅct sc。sｎo 学号,sc.cno 课程号,sc、graｄe 成绩
fｒom sc,V_AVＧ_C＿Ｇ
wｈｅre sｃo=V_ＡVＧ_C_G、课程号 ａｎd sｃ、ｇraｄe>V_AVG_C_G、平均成绩
心得体会:对语句不熟悉,有得操作花了很多时间,所以之做到这步,争取下次老师布置得实验按时完成。
因篇幅问题不能全部显示，请点此查看更多更全内容