数据库系统实验三

实验题目        实验三 创建和使用视图、索引、存储过程             

一、实验目的

1、理解视图、索引、存储过程的定义、索引、存储过程的优点与、索引、存储过程的工作原理；

2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引、存储过程；

3、掌握创建视图、索引、存储过程的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。

4、掌握使用视图来查询数据。

二、实验主要仪器设备和材料

1．计算机及操作系统：PC机，Windows；

2．数据库管理系统：SQL  Server  2005；

三、实验方法、步骤及结果测试

（一）、视图

1、建立“计算机学院”的学生基本情况视图ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。

2、执行ies_student_view视图并观察结果。

3、建立课程1（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。

4、建立统计不及格情况的视图，列出不及格学生的学号、姓名和不及格的课程代码；

5、执行视图并观察结果。

6、修改视图ies_student_view，使该视图包括所有学生的学号、姓名、性别、出身年月、学院名称、专业名称。

7、执行ies_student_view视图并观察结果。

8、删除视图ies_student_view。并查询结果。

记录上述实验过程并截图说明。

查询分析器执行情况：

创建视图的SQL语句及执行结果显示

CREATE VIEW ies_student_view

AS

SELECT s_no,sname,sex,sbirthday,spname

FROM student_3113006393,speciality_3113006393

WHERE student_3113006393.spno=speciality_3113006393.spno

查询视图SQL语句及执行结果截图

查询分析器执行情况：

创建视图的SQL语句及执行结果显示

CREATE VIEW tcid1_student_view

AS

SELECT student_3113006393.s_no,sname,spname,score

FROM student_3113006393,speciality_3113006393,student_course_3113006393

WHERE student_3113006393.spno=speciality_3113006393.spno

  AND student_3113006393.s_no=student_course_3113006393.s_no

AND tcid='1'

查询视图SQL语句及执行结果截图

查询分析器执行情况：

创建视图的SQL语句及执行结果显示

CREATE VIEW fail_student_view

AS

SELECT student_3113006393.s_no,sname,Course_3113006393.cno

FROM student_3113006393,student_course_3113006393,

Course_3113006393,teacher_course_3113006393

WHERE student_3113006393.s_no=student_course_3113006393.s_no

  AND student_course_3113006393.tcid=teacher_course_3113006393.tcid

  AND Course_3113006393.cno=teacher_course_3113006393.cno

AND score<60

查询视图SQL语句及执行结果截图

查询分析器执行情况：

修改视图的SQL语句及执行结果显示

ALTER view ies_student_view

as

select dept_name,s_no,sname,sex,sbirthday,spname

from student_3113006393,department_3113006393,speciality_3113006393

where student_3113006393.dno=department_3113006393.dno

and   student_3113006393.spno=speciality_3113006393.spno

查询视图SQL语句及执行结果截图

查询分析器执行情况：

删除视图的SQL语句及执行结果显示

DROP VIEW ies_student_view

查询视图SQL语句及执行结果截图

（二）、索引

在student_course表（学生选课表）的学生学号（sno）列上创建索引sc_sno_index

 

查询分析器执行情况：

创建索引的SQL语句及执行结果显示

CREATE INDEX sc_sno_index

on student_course_3113006393(s_no desc)

创建索引的SQL语句及执行结果截图

（三）、存储过程

题目（1）创建存储过程list_student_department,该存储过程接收学院代码作为输入参数，列出数据库sc中某个院系学生的全部信息。

查询分析器执行情况：

SQL语句

CREATE Procedure list_student_department

@dno char(6)

AS

SELECT s_no,sname,sex,sbirthday,spno,class_no,student_3113006393.dno

FROM student_3113006393,department_3113006393 

WHERE department_3113006393.dno=@dno AND student_3113006393.dno=department_3113006393.dno

执行结果截图显示

题目（2）使用SQL语句执行存储过程list_student_department

查询分析器执行情况：

SQL语句

EXEC list_student_department '510000'

执行结果截图显示

题目（3）编写存储过程将某班选修某门课程的所有学生的学号和上课编号插入到student_course表中

查询分析器执行情况：

SQL语句

CREATE PROCEDURE intoclass

(@dno char(6), @spno char(8),@class_no char(4),@tcid integer)

AS

INSERT INTO student_course_3113006393(tcid,s_no)

SELECT @tcid,s_no

FROM student_3113006393

WHERE dno=@dno AND spno=@spno AND class_no=@class_no 

AND s_no not IN(SELECT s_no FROM student_course_3113006393 WHERE tcid=@tcid)

执行结果截图显示

题目（4）使用SQL语句执行（3）创建的存储过程

查询分析器执行情况：

SQL语句

EXEC intoclass '510000','080605','0307',2

执行结果截图显示

题目（5）使用SQL语句查看（3）创建的存储过程代码

查询分析器执行情况：

SQL语句

EXEC sp_helptext intoclass

执行结果截图显示

题目（6）使用SQL语句删除一个前面建立的存储过程

查询分析器执行情况：

SQL语句

DROP PROC intoclass

执行结果截图显示

四、实验中出现的问题及解决方案

无

就是没涉及到如何使用索引，希望实验以后能添加多一些内容

五、思考题    

1、视图更新的优点

答：1.可以访问表中列的子集。在表中有些列是比较敏感的数据不想用户看到的，如用户密码，员工工资等，用视图可以隐藏这些列。

2.可以访问表中行的子集。有时不想让用户看到与TA无关的数据时，可以在where条件中过滤。

3.可以重命名列名。在表中有些列名定义没有代表意义，如列“abc”，为了让用户看到列名就可以知该列是什么数据，可以在组建视图时重命名列名。

4.可以快速访问两表或多表连接所组成的数据。有时要访问表间连接所组成的数据集，可以把查询出来的数据集定义成视图，可以帮助快速访问所需的数据。 

5.可以快速读取用聚合函数运算所返回的数据集。有时用户要读取经过用聚合函数复杂运算后的结果集，每次读取都是很耗时，这时可以把这运算后的数据集组成视图，就可以避免每次读取都要运算的消耗。

总的来讲，使用视图在提高安全性的同时也加快查询的速度。

2、哪些视图是可以更新的？哪些视图是不可以更新的？针对实验举例说明。

答：(1) 若视图是基于多个表使用联接操作而导出的，那么对这个视图执行更新操作时，每次只能影响其中的一个表。

(2) 若视图导出时包含有分组和聚合操作，则不允许对这个视图执行更新操作。

(3) 若视图是从一个表经选择、投影而导出的，并在视图中包含了表的主键字或某个候选键，这类视图称为‘行列子集视图’。对这类视图可执行更新操作。

本实验的ise_student_view视图的字段是来自字段表达式的，所以不能更新。

3、什么是存储过程，什么是触发器，两者有什么区别？

答：触发器是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效:UPDATE、INSERT 或 DELETE。触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

触发器与存储过程可以说是非常相似，可以说是一种变种的存储过程，触发器和存储过程一样都是SQL语句集，通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

4、teacher_course表中的class_no是否是外码？请说明判断的理由。

答：不是，不依赖外表主键，也不含外码，所以不是。

5、teacher_course表中的class_no应该与class表中的class_no建立对应关系，即

第一、当向teacher_course表中插入一条元组时，必须保证class_no是class表中的class_no；

第二、当删除或修改class表中的class_no时，teacher_course表中的class_no也应该随之变化或不允许变化。

请用触发器完成上述要求。