数据库学习之旅——实验3(2)

本次大实验分为两部分实验其中（2）为视图

本次实验目的是为了熟悉SQL语言支持的有关视图的操作，能够熟悉使用SQL语句来创建需要的视图，对视图进行查询和取消视图。

视图实验原理解析：

视图是虚表，是 从一个或几个基本表（或视图）导出的表，在数据库中只存放视图的定义，不会出现数据冗余。当基表中的数据发生变化，从视图中查询出的数据也随之改变。视图只是基本表数据的一个窗。

以下是本次实验的练习与习题答案：(school表在此并未发出，请参考之前的练习)

USE SCHOOL
--1.4.4实验练习
--(1)创建一个行列子集视图，给出选课成绩合格的学生的编号，所选课程号和该课程成绩。
CREATE VIEW CS
AS SELECT NO,SID,CID,SCORE
FROM CHOICES
WHERE SCORE>=60


--(2)创建基于多个基表的视图，这个视图由学生和其所选修的课程及讲授该课程的教师姓名构成。
CREATE VIEW SCT
(SNAME,CNAME,TNAME)
AS SELECT STUDENTS.SNAME,COURSES.CNAME,TEACHERS.TNAME
FROM CHOICES,STUDENTS,COURSES,TEACHERS
WHERE CHOICES.TID=TEACHERS.TID AND CHOICES.CID=COURSES.CID AND CHOICES.SID=STUDENTS.SID



--(3)创建带有表达式的视图，由学生姓名、所选课程名和所有课程成绩都比原来多5分这几个属性构成。
CREATE VIEW SCC
(SNAME,CNAME,SCORE)
AS SELECT STUDENTS.SNAME,COURSES.CNAME,CHOICES.SCORE+5
FROM CHOICES,STUDENTS,COURSES
WHERE CHOICES.CID=COURSES.CID AND CHOICES.SID=STUDENTS.SID


--(4)创建分组视图，将学生的学号及其平均成绩定义为一个视图。
CREATE VIEW S_G
(SID,SAVG)
AS SELECT SID,AVG(SCORE)
FROM CHOICES
GROUP BY SID


--(5)创建一个基于视图的视图，基于(1)中建立的视图，定义一个包括学生编号，学生所选课程树木和平均成绩的视图。
CREATE VIEW S_C_S(SID,CCOUNT,SAVG)
AS SELECT SID,COUNT(CS.CID),AVG(SCORE)
FROM CS
GROUP BY CS.SID


--(6)查询所有选秀课程Software Engineering 的学生姓名。
SELECT SNAME FROM SCT WHERE CNAME='Software Engineering';


--(7)插入元组(600000000,823069829,10010,59)到视图CS1中。若是在视图的定义中存在WITH CHECK OPTION子句对插入操作有什么影响？
INSERT INTO CS VALUES ('600000000','823069829','10010','59');



--(8)将视图CS(包含定义WITH CHECK OPTION)中，所有课程编号为10010的课程的成绩都减去5分。这个操作数据库是否会正确执行？为什么？如果加上5分(原来95分以上的不变)呢？
UPDATE CS
SET SCORE=SCORE-5
WHERE CID='10010'

UPDATE CS
SET SCORE=SCORE-5
WHERE CID='10010' AND SCORE<95


--(9)在视图CS(包含定义WITH CHECK OPTION)删除编号为804529880学生的记录，会差生什么结果？
DELETE CS
WHERE SID='804529880'


--(10)取消视图SCT和视图CS
DROP VIEW CS
DROP VIEW SCT

针对以上练习，以下是自我练习题并附以答案：

USE SCHOOL
--1.4.4实验练习
--(1)创建一个行列子集视图，给出选课成绩合格的学生的编号，所选课程号和该课程成绩。
CREATE VIEW CS
AS SELECT NO,SID,CID,SCORE
FROM CHOICES
WHERE SCORE>=60


--(2)创建基于多个基表的视图，这个视图由学生和其所选修的课程及讲授该课程的教师姓名构成。
CREATE VIEW SCT
(SNAME,CNAME,TNAME)
AS SELECT STUDENTS.SNAME,COURSES.CNAME,TEACHERS.TNAME
FROM CHOICES,STUDENTS,COURSES,TEACHERS
WHERE CHOICES.TID=TEACHERS.TID AND CHOICES.CID=COURSES.CID AND CHOICES.SID=STUDENTS.SID



--(3)创建带有表达式的视图，由学生姓名、所选课程名和所有课程成绩都比原来多5分这几个属性构成。
CREATE VIEW SCC
(SNAME,CNAME,SCORE)
AS SELECT STUDENTS.SNAME,COURSES.CNAME,CHOICES.SCORE+5
FROM CHOICES,STUDENTS,COURSES
WHERE CHOICES.CID=COURSES.CID AND CHOICES.SID=STUDENTS.SID


--(4)创建分组视图，将学生的学号及其平均成绩定义为一个视图。
CREATE VIEW S_G
(SID,SAVG)
AS SELECT SID,AVG(SCORE)
FROM CHOICES
GROUP BY SID


--(5)创建一个基于视图的视图，基于(1)中建立的视图，定义一个包括学生编号，学生所选课程树木和平均成绩的视图。
CREATE VIEW S_C_S(SID,CCOUNT,SAVG)
AS SELECT SID,COUNT(CS.CID),AVG(SCORE)
FROM CS
GROUP BY CS.SID


--(6)查询所有选秀课程Software Engineering 的学生姓名。
SELECT SNAME FROM SCT WHERE CNAME='Software Engineering';


--(7)插入元组(600000000,823069829,10010,59)到视图CS1中。若是在视图的定义中存在WITH CHECK OPTION子句对插入操作有什么影响？
INSERT INTO CS VALUES ('600000000','823069829','10010','59');



--(8)将视图CS(包含定义WITH CHECK OPTION)中，所有课程编号为10010的课程的成绩都减去5分。这个操作数据库是否会正确执行？为什么？如果加上5分(原来95分以上的不变)呢？
UPDATE CS
SET SCORE=SCORE-5
WHERE CID='10010'

UPDATE CS
SET SCORE=SCORE-5
WHERE CID='10010' AND SCORE<95


--(9)在视图CS(包含定义WITH CHECK OPTION)删除编号为804529880学生的记录，会差生什么结果？
DELETE CS
WHERE SID='804529880'


--(10)取消视图SCT和视图CS
DROP VIEW CS
DROP VIEW SCT


--1.4.5自我实践
--(1)定义选课信息和课程名称的视图VIEWC
CREATE VIEW VIEWC
AS
SELECT CHOICES.NO,CHOICES.SID,CHOICES.TID,CHOICES.SCORE,COURSES.CNAME
FROM CHOICES,COURSES
WHERE CHOICES.CID=COURSES.CID


--(2)定义学生姓名与选课信息的视图VIEWS
CREATE VIEW VIEWS
AS
SELECT CHOICES.NO,CHOICES.CID,CHOICES.TID,CHOICES.SCORE,STUDENTS.SNAME
FROM CHOICES,STUDENTS
WHERE STUDENTS.SID=CHOICES.SID


--(3)定义年级低于1998的学生的视图S1(SID,SNAME,GRADE)
CREATE VIEW S1(SID,SNAME,GRADE)
AS
SELECT STUDENTS.SID,STUDENTS.SNAME,STUDENTS.GRADE
FROM STUDENTS
WHERE GRADE>1998


--(4)查询学生为“uxjof”的学生的选课信息
SELECT * FROM VIEWS WHERE SNAME='uxjof';


--(5)查询选修课程“UML”的学生的编号和成绩
SELECT SID,SCORE FROM VIEWC
WHERE CNAME='UML'


--(6)向视图S1插入记录('60000001','LILT','2001')
INSERT INTO S1 VALUES('60000001','LILT','2001');



--(7)定义包括更新和插入约束的视图S2,尝试向视图插入记录('60000001','LILT','1997')，删除所有年级为1999的学生记录，讨论更新和插入约束带来的影响
CREATE VIEW S2
(SID,SNAME,GRADE)
AS
SELECT SID,SNAME,GRADE FROM STUDENTS
WHERE GRADE>1998
WITH CHECK OPTION
INSERT INTO S1 VALUES('60000001','LILT','1997');
DELETE FROM S1 WHERE GRADE=1999;


--(8)在视图VIEWS中将姓名为“uxjof”的学生的选课成绩都加上5分
UPDATE VIEWS SET SCORE=SCORE+5 WHERE SNAME='uxjof'


--(9)取消以上建立的所有视图
DROP VIEW VIEWS;
DROP VIEW VIEWC;
DROP VIEW S1;

运行结果请读者自试。