数据库实验报告 4.docx
《数据库实验报告 4.docx》由会员分享,可在线阅读,更多相关《数据库实验报告 4.docx(15页珍藏版)》请在冰点文库上搜索。
数据库实验报告4
数据库实验报告(4)
一实验题目
1.索引的建立和删除操作
2.视图的创建、修改、更新和查询操作
二实验目的
1.掌握数据库索引建立与删除操作,掌握数据库索引的分类,并了解建立数据库索引的意义、作用。
2.掌握视图的创建和查询操作,理解视图的使用,理解实图在数据库安全性中的作用。
三实验内容
1.索引的建立和删除操作
(1)在S表中,建立按照sno升序的惟一性索引snoIDX。
(2)在SC表中,建立按照学号升序和课程号降序的唯一性索引scIDX。
(3)在S表中,按照生日建立一个非聚簇索引birthdayIDX。
(4)在C表中,建立一个按照课程名升序的聚簇索引cnameIDX。
(5)删除索引cnameIDX。
2.视图的创建、修改、更新和查询操作
(1)建立一个关于所有女生信息的视图S_GIRL。
(2)将各系学生人数,平均年龄定义为视图
(3)
(4)
(5)
(6)
图表2在SC表建立索引scIDX。
1.在S表中,按照生日建立一个非聚簇索引birthdayIDX。
代码:
createNONCLUSTEREDindexbirthdayIDXonS(Sbirthdayasc);
运行结果:
命令已成功完成。
显示结果如图3所示
图表3建立索引birthdayIDX
2.在C表中,建立一个按照课程名升序的聚簇索引cnameIDX。
代码:
createCLUSTEREDindexcnameIDXonC(Cnoasc);
运行结果:
命令已成功完成。
显示结果如图4:
图表4按照课程名升序的聚簇索引cnameIDX。
3.删除索引cnameIDX。
代码:
dropindexC.cnameIDX
运行结果:
命令已成功完成。
显示结果如图5,无索引,被删除:
图表5
4.视图的创建、修改、更新和查询操作
5.建立一个关于所有女生信息的视图S_GIRL。
createviewS_GIRL
as
select*fromSwhereS.Ssex='女';
运行结果:
服务器:
消息170,级别15,状态1,过程S_GIRL,行3
第3行:
';'附近有语法错误。
删除“;”
代码:
createviewS_GIRL
as
select*fromSwhereSsex='女'
运行结果:
命令已成功完成。
查询显示结果:
select*fromS_GIRL
符合条件的结果如下图6所示:
图表6
6.将各系学生人数,平均年龄定义为视图V_NUM_AVG
代码:
createviewV_NUM_AVGasselectcount(Sno),avg(Sage),Sdept
fromS
groupbySdept
运行结果:
服务器:
消息4511,级别16,状态1,过程V_NUM_AVG,行1
创建视图或函数失败,因为没有为第1列指定列名。
正确代码:
createviewV_NUM_AVGasselectcount(Sno)DeptNum,avg(Sage)Dept,Sdept
fromS
groupbySdept
运行结果:
命令已成功完成。
显示结果
select*fromV_NUM_AVG
如图7所示DeptNum为各系学生人数,Dep为平均年龄
图表7
7.建立一个视图反映学生所选课程的总学分情况TOTAL_CREDIT。
代码:
createviewTOTAL_CREDITasselectSno,sum(Ccredit)astotal_credit
fromSCjoinConC.Cno=SC.Cno
groupbySno
运行结果:
命令已成功完成。
显示结果如图8所示TOTAL_CREDIT为学生所选课程的总学分情况:
图表8
8.建立一个所有学生课程成绩的视图S_GRADE,包括基本学生信息,课程信息和成绩。
createviewS_GRADEasselectS.Sno,S.Sname,C.Cname,C.Ccredit,SC.Grade
fromSC,C,S
whereS.Sno=SC.SnoandSC.Cno=C.Cno
运行结果:
命令已成功完成。
显示结果:
select*fromS_GRADE
结果如图9所示:
基本信息,课程信息和成绩
图表9
9.在视图S_GRADE基础之上,建立一个两门课以上成绩不及格的学生情况视图FAIL_GRADE。
代码:
createviewFAIL_GRADE
as
selectSno,count(*)SnoNumfromS_GRADE
whereGrade<60
groupbySnohavingcount(*)>=2
运行结果:
命令已成功完成。
查询视图符合条件的结果:
select*fromFAIL_GRADE
如下图所示,没有符合选修两门且不及格的学生信息
图表10
10.建立一个至少选修了2门课及2门课以上的学生信息的视图SC_FOUR。
代码:
createviewSC_FOUR
as
selectSno,count(*)CnoNumfromSC
groupbySnohavingcount(*)>=2
运行结果:
命令已成功完成。
查询视图符合条件的结果:
select*fromSC_FOUR
显示结果如下图11:
学号为0001得学生选修3门,0002学生和0003学生选修了3门;
图表11
查询四门以上,数据库中没有符合条件的学生
11.修改视图S_GIRL,要求只显示1997年以前出生的女生信息。
代码:
alterviewS_GIRL
select*fromSwhereSsex='女'andSbirthday<='1997-1-1'
运行结果:
服务器:
消息156,级别15,状态1,过程S_GIRL,行2
在关键字'select'附近有语法错误。
修改后代码:
少了AS,修改视图同ALTER
alterviewS_GIRL
as
select*fromSwhereSsex='女'andSbirthday<='1997-1-1'
运行结果:
命令已成功完成。
显示结果学生都满足1997年之前:
图表12
12.在视图FAIL_GRADE查询不及格超过2门课的学生信息。
代码:
select*fromFAIL_GRADEwhereSnoNum>2
运行结果:
命令已成功完成。
显示结果:
在视图FAIL_GRADE不存在不及格超过2门课;
图表13
13.删除视图S_GRADE。
修改前数据库中存在的视图14如下图所示:
图表14
代码:
dropviewS_GRADE
删除视图后,数据库中存在的视图如下图15所示:
不存在S_GRADE视图
图表15
14.通过视图S_GIRL,将“王思”的名字修改为“汪思”,并查询结果。
修改前:
视图S_GIRL基本情况如下图所示:
图表16
代码:
alterviewS_GIRL
as
setSname='汪思'whereSname='王思'
运行结果:
服务器:
消息156,级别15,状态1,过程S_GIRL,行4
在关键字'set'附近有语法错误。
语句错误:
应用update,update后不用加viewas
正确代码:
updateS_GIRL
setSname='汪思'whereSname='王思'
运行结果:
(所影响的行数为1行)
显示结果如图17红色标记所示:
图表17
15.通过视图S_GIRL,新增一个学生信息(“刘兰兰”,“女”,“计算机学院”,1996-8-8),并查询结果。
代码:
insertintoS_GIRL(Sno,Sname,Ssex,Sbirthday,Sdept)values('0020','刘兰兰','女','1996-8-8','计算机')
运行结果:
(所影响的行数为1行)
结果显示如图:
16.通过视图S_GIRL,删除1996年出生的女生信息,并查询结果。
运行前信息如上图所示:
代码:
deleteS_GIRLwhereSbirthday<='1996-12-31'andSbirthday>='1996-1-1'
运行结果:
(所影响的行数为1行)
显示结果如下图:
标记所示信息被删除
17.通过视图S_GRADE,将“汪思”的名字修改为“王思”,是否可以实现,请说明原因
代码:
updateS_GRADE
setSname='王思'whereSname='汪思'
运行结果:
(所影响的行数为1行)
显示结果:
若是基于多个表的视图、涉及关键字段则不能修改表信息
18.通过视图COMPUTE_AVG_GRADE,将“0001”学生的平均分改为90分,是否可以实现,请说明原因。
创建视图:
代码:
createviewCOMPUTE_AVG_GRADE
as
selectSC.Sno,avg(SC.Grade)AS平均成绩
fromSC,C,S
whereS.Sno=SC.SnoandSC.Cno=C.Cno
groupbySC.Sno
显示视图信息;
修改视图:
代码:
updateCOMPUTE_AVG_GRADE
set平均成绩='91'whereSno='0001'
运行结果:
服务器:
消息4403,级别16,状态1,行1
视图或函数'COMPUTE_AVG_GRADE'不可更新,因为它包含聚合。
视图不能修改数据。
关于可更新视图有以下三条规则:
(1)若视图是基于多个表使用联接操作而导出的,那么对这个视图执行更新操作时,每次只能影响其中的一个表。
(2)若视图导出时包含有分组和聚合操作,则不允许对这个视图执行更新操作。
(3)若视图是从一个表经选择、投影而导出的,并在视图中包含了表的主键字或某个候选键,这类视图称为‘行列子集视图’。
对这类视图可执行更新操作。
上题不能修改,因为视图导出时包含有分组和聚合操作,不允许对这个视图执行更新操作