实验五 SQL的数据视图(2学时)
1、实验目的
(1) 掌握DBMS的数据视图机制 (2) 掌握SQL语言的视图功能
2、实验内容
(1) 定义或创建视图(在已有的单个或多个基本表或视图上创建视图) (2) 查询视图(通过视图查询数据)
(3) 更新视图(通过视图来插入、删除或修改数据) (4) 删除视图(从数据字典中删除视图的定义)
3、实验要求
(1) 理解视图的概念和作用
(2) 熟练掌握视图的定义语句(CREATE VIEW)和删除语句(DROP VIEW) (3) 熟练掌握视图的查询语句(SELECT)
(4) 熟练掌握视图的更新语句(插入INSERT、删除DELETE、修改UPDATE) (5) 理解行列子集视图、带表达式的视图和分组视图等概念; (6) 理解视图消解(View Resolution)的过程及限制; (7) 认真记录实验结果,写出实验报告。
4、实验步骤
4.1 执行S_T.sql,建立示例数据库S_T。
4.2 对学生关系Student、课程关系Course和选修关系SC进行视图的定义(创建)、查询、更新、删除等操作。
4.2.1 基本练习 (1)创建视图
练习1:创建信息系学生的视图。 CREATE VIEW IS_Student AS
SELECT Sno,Sname,Ssex,Sage FROM Student WHERE Sdept='IS'
WITH CHECK OPTION;
思考:其中,WITH CHECK OPTION子句的作用是什么?
练习2:建立信息系选修了1号课程且成绩在88分以上的学生的视图。 解法1:
CREATE VIEW IS_S1_88(Sno,Sname,Grade) AS
SELECT Student.Sno, Sname, Grade FROM Student,SC
WHERE Sdept='IS' AND
Student.Sno=SC.Sno AND Cno='1' AND Grade>=88; 解法2:
CREATE VIEW IS_S1_88(Sno,Sname,Grade) AS
SELECT IS_Student.Sno, Sname, Grade FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND Cno='1' AND Grade>=88;
练习3:将学生的学号及平均成绩定义为一个视图。 CREATE VIEW S_G(Sno,Gavg) AS
SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;
(2)查询视图
练习4:在信息系学生的视图中找出年龄小于19的女生。 SELECT Sno,Sname,Ssex,Sage FROM IS_Student
WHERE Sage<19 and Ssex='女';
思考:试写出通过视图消解法转换后的查询语句。
练习5:利用IS_S1_88视图,查询信息系选修了1号课程且成绩在92分以上的学生的学号、姓名和成绩。 SELECT *
FROM IS_S1_88 WHERE Grade>=92;
思考:如何查询信息系选修了1号课程且成绩在85分以上的学生的学号、姓名、性别、年龄和成绩?
练习6:在S_G视图(参见练习3)中查询平均成绩在88分以上的学生学号和平均成
绩。
SELECT * FROM S_G
WHERE Gavg>=88;
思考:对于此视图查询语句,SQL Server2005能否正确完成视图消解?试根据实际执行结果的正确与否加以判断,并写出视图消解转换后的SQL语句。(参考教材p122-123)
(3)更新视图
练习7:向信息系学生视图IS_Student中插入一个新的学生元组,其中学号为200215185,姓名为周捷轮,性别为男,年龄为23岁。 INSERT
INTO IS_Student
VALUES('200215185','周捷轮','男',23);
思考:执行此语句会出现什么问题?如何修改? 试试:
① 首先,使用如下语句新建一个信息系学生视图IS_Student2:
CREATE VIEW IS_Student2 AS
SELECT Sno,Sname,Ssex,Sage FROM Student
WHERE isnull(Sdept,'IS')= 'IS ' /*判断Sdept是否为空,若为空则用'IS'代替 */ WITH CHECK OPTION;
思考:与练习1中建立的IS_Student视图有何区别?
② 然后,执行如下SQL语句,对视图进行插入操作,观察视图和基本表中的结果是否正确? INSERT
INTO IS_Student2
VALUES('200215185','周捷轮','男',23);
练习8:将信息系学生视图IS_Student中学号为200215132的学生姓名修改为樊虹宇。 UPDATE IS_Student SET Sname='樊虹宇' WHERE Sno='200215132';
观察:视图IS_Student、IS_Student2以及基本表Student中的数据的变化情况。
练习9:删除信息系学生视图IS_Student中学号为200215135的记录。 DELETE
FROM IS_Student
WHERE Sno='200215135';
观察:视图IS_Student、IS_Student2以及基本表Student中的数据的变化情况。