实验4 数据库索引视图与触发器.docx

实验4 数据库索引视图与触发器.docx

《实验4 数据库索引视图与触发器.docx》由会员分享，可在线阅读，更多相关《实验4 数据库索引视图与触发器.docx（23页珍藏版）》请在冰豆网上搜索。

实验4数据库索引视图与触发器

石家庄经济学院

实验报告

学院:

信息工程学院

专业:

计算机应用

信息工程学院计算机实验中心制

学号

513109030101

姓名

张雪洁

实验日期

2014-4-28

实验室

260

指导教师

张有华

设备编号

JF260-11

实验内容

实验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）将各系学生人数，平均年龄定义为视图V_NUM_AVG

（3）建立一个视图反映学生所选课程的总学分情况TOTAL_CREDIT。

（4）建立一个所有学生课程成绩的视图S_GRADE，包括基本学生信息，课程信息和成绩。

（5）在视图S_GRADE基础之上，建立一个两门课以上成绩不及格的学生情况视图FAIL_GRADE。

（6）建立一个至少选修了4门课及4门课以上的学生信息的视图SC_FOUR。

（7）修改视图S_GIRL，要求只显示1997年以前出生的女生信息。

（8）在视图FAIL_GRADE查询不及格超过2门课的学生信息。

（9）删除视图S_GRADE。

（10）通过视图S_GIRL，将“王丹”的名字修改为“汪丹”，并查询结果。

（11）通过视图S_GIRL，新增一个学生信息（“刘兰兰”，“女”，“计算机学院”，1996-8-8），并查询结果。

（12）通过视图S_GIRL，删除1995年出生的女生信息，并查询结果。

（13）通过视图S_GRADE，将“汪丹”的名字修改为“王丹”，是否可以实现，请说明原因。

（14）通过视图COMPUTE_AVG_GRADE，将“4121090301”学生的平均分改为90分，是否可以实现，请说明原因。

四实验要求

1．要求掌握索引的类型，以及创建索引时的注意事项，例如每个表只能创建一个聚集索引，可以创建非聚集索引最多为249个，等等。

2．理解创建视图的目的和意义。

掌握创建视图时需要考虑的原则：

只能在当前数据库中创建视图、视图名不得与该用户的表名相同、可在视图上建立视图、定义视图不能包括ORDERBY等关键字、不能建立临时视图，等等。

3．报告中由同学写明具体的操作意图（文字描述）、操作命令（SQL语句）、和执行结果（文字描述+适当截图）。

4．对于重要的运行界面和结果窗口，可以用Alt+PrintScreen来截取当前窗口，并粘贴到实验报告中。

五实验步骤

1．索引的建立和删除操作

1）CREATEINDEX—创建索引

为给定表或视图创建索引。

只有表或视图的所有者才能为表创建索引。

表或视图的所有者可以随时创建索引，无论表中是否有数据。

可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。

语法

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name

    ON{table|view}（column[ASC|DESC][,...n]）

[WITH[,...n]]

[ONfilegroup]

:

:

=

    {PAD_INDEX|

        FILLFACTOR=fillfactor|

        IGNORE_DUP_KEY|

        DROP_EXISTING|

    STATISTICS_NORECOMPUTE|

    SORT_IN_TEMPDB 

}

2）DROPINDEX—删除索引

从当前数据库中删除一个或多个索引。

DROPINDEX语句不适用于通过定义PRIMARYKEY或UNIQUE约束创建的索引（通过分别使用CREATETABLE或ALTERTABLE语句的PRIMARYKEY或UNIQUE选项创建）。

有关PRIMARY或UNIQUEKEY约束的更多信息，请参见本卷中的"CREATETABLE"或"ALTERTABLE"。

语法

DROPINDEX'table.index|view.index'[,...n]

参数

table|view

是索引列所在的表或索引视图。

若要查看在表或视图上存在的索引列表，请使用sp_helpindex并指定表名或视图名称。

表名和视图名称必须符合标识符规则。

有关更多信息，请参见使用标识符。

可以选择是否指定表或视图所有者名称。

Index是要除去的索引名称。

索引名必须符合标识符的规则。

N是表示可以指定多个索引的占位符。

（1）在S表中，建立按照sno升序的惟一性索引snoIDX。

SQL语句：

CREATEUNIQUEINDEXsnoIDXONS（sno）;

运行结果如图1-1所示：

图1-1索引snoIDX

（2）在SC表中，建立按照学号升序和课程号降序的唯一性索引scIDX。

SQL语句：

CREATEUNIQUEINDEXscIDXONSC（snoASC,cnoDESC）;

运行结果如图1-2所示：

图1-2索引scIDX

（3）在S表中，按照生日建立一个非聚簇索引birthdayIDX。

SQL语句：

CREATENONCLUSTEREDINDEXbirthdayIDXONS（Sbirthday）;

运行结果如图1-3所示：

图1-3索引birthdayIDX

（4）在C表中，建立一个按照课程名升序的聚簇索引cnameIDX。

SQL语句：

CREATECLUSTEREDINDEXcnameIDXonC（cname）;

结果框显示为：

服务器:

消息1902，级别16，状态3，行1

不能在表'C'上创建多个聚集索引。

请在创建新聚集索引前除去现有的聚集索引'PK__C__22AA2996'。

查询后，发现，一个表创建时，如果不定义索引类型。

会把主键默认设置为聚集索引，如所示，而在一个表中只能存在一个聚集索引，所以本题，建立聚集索引时会提示以上错误，此时要将表中默认的聚集索引删除，语句如下：

DROPINDEXC.PK__C__22AA2996

结果框显示为：

服务器:

消息3723，级别16，状态4，行1

不允许对索引'C.PK__C__22AA2996'显式地使用DROPINDEX。

该索引正用于PRIMARYKEY约束的强制执行。

解决方法如图1-4所示：

图1-4编辑索引PK_C_22AA2996

将聚集索引前的对勾去掉后，提示错误，如图1-5所示：

图1-5错误

打开C表设计表，将主键去除，如图1-6所示：

图1-6去除主键

此时在创建聚集索引，运行结果如图1-7所示：

图1-7索引CnameIDX

（5）删除索引cnameIDX

SQL语句：

DROPINDEXC.cnameIDX

结果框显示为：

命令已成功完成。

运行结果如图1-8所示：

图1-8索引被删除

2．视图的创建、修改、更新和查询操作

1）CREATEVIEW—创建视图

创建一个虚拟表，该表以另一种方式表示一个或多个表中的数据。

CREATEVIEW必须是查询批处理中的第一条语句。

语法

CREATEVIEW[.][.]view_name[（column[,...n]）]

[WITH[,...n]]

AS

select_statement

[WITHCHECKOPTION]

:

:

=

    {ENCRYPTION|SCHEMABINDING|VIEW_METADATA}

参数

view_name是视图的名称。

视图名称必须符合标识符规则。

可以选择是否指定视图所有者名称。

Column是视图中的列名。

只有在下列情况下，才必须命名CREATEVIEW中的列：

当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。

还可以在SELECT语句中指派列名。

如果未指定column，则视图列将获得与SELECT语句中的列相同的名称。

WITHCHECKOPTION强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则。

通过视图修改行时，WITHCHECKOPTION可确保提交修改后，仍可通过视图看到修改的数据。

WITHENCRYPTION表示SQLServer加密包含CREATEVIEW语句文本的系统表列。

使用WITHENCRYPTION可防止将视图作为SQLServer复制的一部分发布。

2）DROPVIEW—删除视图

从当前数据库中删除一个或多个视图。

可对索引视图执行DROPVIEW。

语法

DROPVIEW{view}[,...n]

参数

View是要删除的视图名称。

视图名称必须符合标识符规则。

有关更多信息，请参见使用标识符。

可以选择是否指定视图所有者名称。

若要查看当前创建的视图列表，请使用sp_help。

N是表示可以指定多个视图的占位符。

3）ALTERVIEW—修改视图

更改一个先前创建的视图（用CREATEVIEW创建），包括索引视图，但不影响相关的存储过程或触发器，也不更改权限。

有关ALTERVIEW语句中所用参数的更多信息，请参见CREATEVIEW。

语法

ALTERVIEW[.][.]view_name[（column[,...n]）]

[WITH[,...n]]

AS

    select_statement

[WITHCHECKOPTION]

:

:

=

    {ENCRYPTION|SCHEMABINDING|VIEW_METADATA}

参数

view_name是要更改的视图。

Column是一列或多列的名称，用逗号分开，将成为给定视图的一部分。

只有在ALTERVIEW执行前后列名称不变的情况下，列上的权限才会保持不变。

（1）建立一个关于所有女生信息的视图S_GIRL。

SQL语句：

createviewS_GIRL

AS

select*

fromS

wheressex='女'

原S表中的数据为：

select*

fromS

如图2-1所示：

图2-1原S表中的数据

查询视图S_GIRL中的数据：

select*

FROMS_GIRL

如图2-2所示：

图2-2视图S_GIRL中的数据

（2）将各系学生人数，平均年龄定义为视图V_NUM_AVG

SQL语句：

createviewV_NUM_AVG

AS

selectcount（sdept）asnum,avg（2014-year（sbirthday））asage

fromS

groupbysdept

原S表中数据为：

Selectsname,sdept,2014-year（sbirthday）asage

FROMS

如图2-3所示：

图2-3原S表中数据

查询视图V_NUM_AVG中的数据为：

Select*

FROMV_NUM_AVG

如图2-4所示：

图2-4视图V_NUM_AVG中的数据

（3）建立一个视图反映学生所选课程的总学分情况TOTAL_CREDIT。

SQL语句：

CreateviewTOTAL_CREDIT

AS

SelectSC.sno,sum（ccredit）assum

FromSC,C

WhereC.cno=SC.cno

Groupbysno

原表SC和C连接后的数据：

SelectSC.sno,C.cno,cname,ccredit

FromSC,C

WhereC.cno=SC.cno

OrderbySC.snoasc

如图2-5所示：

图2-5表SC和C连接后的数据

查询视图TOTAL_CREDIT中的数据为：

Select*

FROMTOTAL_CREDIT

如图2-6所示：

图2-6视图TOTAL_CREDIT中的数据

（4）建立一个所有学生课程成绩的视图S_GRADE，包括基本学生信息，课程信息和成绩。

SQL语句：

CreateviewS_GRADE

AS

SelectdistinctS.*,c.*,sc.grade

FromSC,C,S

WhereC.cno=SC.cnoandSC.sno=S.sno

查询视图S_GRADE中的数据：

Select*

FROMS_GRADE

如图2-7所示：

图2-7视图S_GRADE中的数据

（5）在视图S_GRADE基础之上，建立一个两门课以上成绩不及格的学生情况视图FAIL_GRADE。

SQL语句：

CreateviewFAIL_GRADE

AS

Selectsno,count（sno）asnum

FromS_GRADE

Wheregrade<60

Groupbysno

Havingcount（sno）>=2

原视图S_GRADE中的数据：

Selectsno,cno,grade

FROMS_GRADE

如图2-8所示：

图2-8视图S_GRADE中的数据

原视图S_GRADE中成绩小于60的数据：

Selectsno,cno,grade

FROMS_GRADE

Wheregrade<60

如图2-9所示：

图2-9视图S_GRADE中成绩小于60的数据

查询视图FAIL_GRADE中的数据：

Select*

FROMFAIL_GRADE

如图2-10所示：

图2-10视图FAIL_GRADE中的数据

（6）建立一个至少选修了4门课及4门课以上的学生信息的视图SC_FOUR。

SQL语句：

createviewSC_FOUR

AS

selectS.*

fromS

whereS.snoin（

selectdistinctS.sno--,count（S.sno）asnum

fromS,SC,C

whereC.cno=SC.cnoandSC.sno=S.sno

groupbyS.sno

havingcount（S.sno）>=4

）

子查询中学生选修大于4门的学生学号：

selectdistinctS.sno,count（S.sno）asnum

fromS,SC,C

whereC.cno=SC.cnoandSC.sno=S.sno

groupbyS.sno

havingcount（S.sno）>=4

如图2-11所示：

图2-11子查询中学生选修大于4门的学生学号

查询视图SC_FOUR中的数据：

select*

FROMSC_FOUR

如图2-12所示：

图2-12视图SC_FOUR中的数据

（7）修改视图S_GIRL，要求只显示1997年以前出生的女生信息。

SQL语句：

alterviewS_GIRL

AS

select*

fromS

wheressex='女'andyear（sbirthday）<1997

查询视图S_GIRL中的数据：

select*

FROMS_GIRL

如图2-13所示：

图2-13视图S_GIRL中的数据

（8）在视图FAIL_GRADE查询不及格超过2门课的学生信息。

SQL语句：

selectS.*

fromFAIL_GRADE,S

whereS.sno=FAIL_GRADE.sno

查询结果如图2-14所示：

图2-14视图FAIL_GRADE查询

（9）删除视图S_GRADE。

SQL语句：

dropviewS_GRADE

删除前如图2-15所示：

图2-15删除前

删除后如图2-16所示：

图2-16删除后

（10）通过视图S_GIRL，将“王丹”的名字修改为“汪丹”，并查询结果。

SQL语句：

updateS_GIRL

setsname='王丹'

wheresname='汪丹'

查询视图S_GIRL中的数据：

select*

fromS_GIRL

如图2-17所示：

图2-17视图S_GIRL中的数据

（11）通过视图S_GIRL，新增一个学生信息（“刘兰兰”，“女”，“计算机学院”，1996-8-8），并查询结果。

SQL语句：

insertintoS_GIRL（sno,sid,sname,ssex,sdept,sbirthday）

values（12,513109030112,'刘兰兰','女','计算机学院',1996-8-8）

查询视图S_GIRL中的数据：

select*

fromS_GIRL

如图2-18所示：

图2-18视图S_GIRL中的数据

（12）通过视图S_GIRL，删除1995年出生的女生信息，并查询结果。

SQL语句：

delete

fromS_GIRL

whereyear（sbirthday）='1995'

原视图S_GIRL中的数据：

select*

fromS_GIRL

如图2-19所示：

图2-19删除前视图S_GIRL中的数据

语句执行后，结果框提示错误：

服务器:

消息547，级别16，状态1，行1

DELETE语句与COLUMNREFERENCE约束'FK__SC__Sno__25869641'冲突。

该冲突发生于数据库'DEX'，表'SC',column'Sno'。

语句已终止。

删除约束FK__SC__Sno__25869641语句：

altertablesc

dropconstraintFK__SC__Sno__25869641

再执行删除语句：

delete

fromS_GIRL

whereyear（sbirthday）='1995'

查询视图S_GRADE中的数据：

select*

fromS_GIRL

如图2-20所示：

图2-20删除后视图S_GIRL中的数据

（13）通过视图S_GRADE，将“汪丹”的名字修改为“王丹”，是否可以实现，请说明原因。

SQL语句：

updateS_GRADE

setsname='王丹'

wheresname='汪丹'

运行结果框显示：

服务器:

消息4404，级别16，状态1，行1

视图或函数'S_GRADE'不可更新，因为其定义中包含DISTINCT子句。

查询视图S_GRADE中的数据：

select*

fromS_GRADE

如图2-21所示：

图2-21视图S_GRADE中的数据

数据不能更新的原因是：

S_GRADE不是行列子集视图，行列子集视图是一个从单个基本表中导出的，并且仅仅是去掉了基本表中的某些行和列。

S_GRADE是有S,C,SC表三个表导出的。

（14）通过视图COMPUTE_AVG_GRADE，将“4121090301”学生的平均分改为90分，是否可以实现，请说明原因。

不能实现，原因是：

在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一的有已得地转换成对相应基本表的更新。