实验6 视图的应用

实验6 视图的应用
实验目的
学会使用MS SQL SERVER 2000企业管理器和SQL语句创建、修改和删除视图，掌握通过视图向基本表插入、删除、更新数据的方法和步骤。

知识点拨：
1.视图是一张虚表
2.视图的作用
3.视图的种类
4.创建视图的SQL语句
5.更新视图的SQL语句
6.删除视图的SQL语句
7.通过视图修改数据库中数据的限制
实验内容
1.行列子视图的创建
2.基于多个基本表的视图的创建
3.基于基本表和视图的视图的创建
4.带表达式的视图的创建
5.分组视图的创建
6.删除视图
7.修改视图
8.通过视图修改数据库数据
实验步骤
1.选择数据库JX；
注意：以下操作均在查询分析器定义，在企业管理器中查看和修改，并请验证结果！
2.创建行列子视图
1）创建视图WY_Student，用于查询外语系学生的基本信息；
CREATE VIEW WY_Student
AS
SELECT *
FROM Student
WHERE Sdept='外国语'
2）创建视图SS_Student，用于查询学生的姓名、学号和总学分信息，要求属性列中文显示；
CREATE VIEW SS_Student
AS
SELECT Sname 姓名,SC.Sno 学号,Total 总学分
FROM Student
3.创建基于多个基本表的视图
1）创建视图XSXK，用于查询每个同学的选课信息，包括学号、姓名、课程号、课程名和成绩；
CREATE VIEW XSXK
AS
SELECT SC.Sno,Sname,o,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND o=o
GROUP BY SC.Sno,Sname,o,Cname,Grade
2）创建视图JSSK，用于查询每个教师的任课情况，包括教师号、教师名、职称、所讲授课程的课程号信息，并用中文列出各属性名；
CREATE VIEW JSSK
AS
SELECT Teacher.Tno 教师号,Tname 教师名,Tprof 职称,o 课程号,Cname 课程名FROM Teacher,SC,Course
WHERE Teacher.Tno=SC.Tno AND o=o
GROUP BY Teacher.Tno ,Tname ,Tprof ,o ,Cname
3）基于视图WY_Student和SC，创建视图SSS，用于查询外语系学生的选课情况；CREATE VIEW SSS
AS
SELECT WY_Student.Sno,Sname,Cno
FROM WY_Student,SC
WHERE SC.Sno=WY_Student.Sno
4.带表达式的视图的创建
1）创建视图XSNL，用于查询学生的学号、姓名和年龄；
CREATE VIEW XSNL(Sno,Sname,Sage)
AS
SELECT Sno,Sname,year(getdate())-year(Sbirth)
FROM Student
2）创建视图XSZCJ，用于查询选修了课程的学生的总评成绩，包括学号、姓名、课程号，每门课程的总评成绩计算公式为：成绩*70%+（成绩-10）*20%+10；CREATE VIEW XSZCJ(Sno,Sname,Cno,总评成绩)
AS
SELECT Student.Sno,Sname,Cno,Grade*0.7+(Grade-10)*0.2+10
FROM Student,SC
WHERE Student.Sno=SC.Sno
3）创建视图JSGZ，用于显示教师的预发工资，包括教工号、姓名、职称和按95%预发的工资额；（含有%的列名字可以用【】）
CREATE VIEW JSGZ(Tno,Tname,Tprof,[预发95%工资])
AS
SELECT Tno,Tname,Tprof,Tpay*0.95
FROM Teacher
5.分组视图的创建
1）创建视图XSPJCJ，用于查询选修了课程的学生的成绩，包括学号、姓名和所选课程的平均成绩；
CREATE VIEW XSPJCJ(Sno,Sname,平均成绩)
AS
SELECT SC.Sno,Sname,A VG(Grade)
FROM SC,Student
WHERE SC.Sno=Student.Sno
GROUP BY SC.Sno,Sname
2）创建视图JSRKMS，用于查询教师的任课门数，包括教工号、教师名和任课门数；CREATE VIEW JSRKMS(Tno,Tname,任课门数)
AS
SELECT Teacher.Tno,Tname,COUNT(DISTINCT(Cno))
FROM Teacher,SC
WHERE Teacher.Tno=SC.Tno
GROUP BY Teacher.Tno,Tname
3）创建视图KCPJCJ，用于查询各门课程的平均成绩，包括课程号、课程名、选课人数、平均成绩；
CREATE VIEW KCPJCJ(Cno,Cname,选课人数,平均成绩)
AS
SELECT o,Cname,COUNT(*),A VG(Grade)
FROM Course,SC
WHERE o=o
GROUP BY o,Cname
4）创建视图YXXS，用于查询各门成绩和平均成绩都在85分以上的学生的学号、姓名及平均成绩；
CREATE VIEW YXXS
AS
SELECT DISTINCT SC.Sno,Sname,A VG(Grade) 平均成绩
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Sno NOT IN
(SELECT Sno
FROM SC
WHERE Grade<85)
GROUP BY SC.Sno,Sname
6.删除视图
1）删除视图XSNL；
DROP VIEW XSNL
7.通过视图修改数据库数据
1）查询视图WY_Student，然后使用INSERT…INTO…语句更新WY_Student视图，再次查询该视图以及相关基表，检查执行结果；
SELECT *
FROM WY_Student
INSERT
INTO WY_Student(Sno,Sname)
V ALUES ('06122','王五')
SELECT *
FROM WY_Student
SELECT *
FROM Student
再次查询的结果为，在基本表Student中插入了该记录，但是在视图WY_Student中并没有插入该记录，因为插入的记录不满足视图WY_Student子查询的条件。

若是改为INSERT
INTO WY_Student(Sno,Sname,Sdept)
V ALUES ('06122','王五','外国语')
则视图WY_Student和基本表Student都插入了该记录。

2）使用UPDATE 更新视图WY_Student中的某个或某些属性值，然后再次查询该视图以并查询相关基表检查更新结果。

UPDATE WY_Student
SET Sname='王士武'
WHERE Sno='06003'
SELECT *
FROM WY_Student
SELECT *
FROM Student
再次查询的结果为，视图WY_Student和基本表Student中的记录都被修改了，即如果修改视图中的记录，那么基本表中的记录相应的也被修改。

同样的，若是修改了基本表中的记录，那么由基本表导出的视图中的记录相应也会被修改。

3）使用DELETE删除视图WY_Student中刚插入的记录，然后查询该视图和相关基本表数据；
DELETE
FROM WY_Student
WHERE Sname='王五'
SELECT *
FROM WY_Student
SELECT *
FROM Student
基本表和视图中的记录都被删掉
4）修改视图JSGZ，把职称为副教授的预发工资改为该视图预发工资的90%，查看执行结果；
UPDATE JSGZ
SET [预发95%工资]=[预发95%工资]*0.9
WHERE Tprof='副教授'
执行结果：对视图或函数'JSGZ' 的更新或插入失败，因其包含有派生域或常量域。

5）修改视图YXXS，把每个优秀学生的平均成绩都增加3分，查看执行结果。

UPDATE YXXS
SET [平均成绩]=[平均成绩]+3
执行结果：视图或函数'YXXS' 不可更新，因为它包含聚合。