数据库系统概论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 ,sno ,total
from student
3.创建基于多个基本表的视图
1）创建视图XSXK，用于查询每个同学的选课信息，包括学号、姓名、课程号、课程名和成绩；
create view XSXK
as
select student.sno,sname,o,cname,grade
from student,sc,course
where student.sno=sc.sno and o=o
2）创建视图JSSK，用于查询每个教师的任课情况，包括教师号、教师名、职称、所讲授课程的课程号信息，并用中文列出各属性名；
create view JSSK (教师号,教师名,职称,所讲授课程的课程号)
as
select teacher.tno,teacher.tname,teacher.tprof,o
from teacher,sc
where teacher.tno=sc.tno
3）基于视图WY_Student和SC，创建视图SSS，用于查询外语系学生的选课情况；
create view SSS
as
select WY_Student.Sno,Sname,Ssex,Cno,Sbirth,Sdept,Total,grade
from WY_Student,SC
where WY_Student.sno=sc.sno
4.带表达式的视图的创建
1）创建视图XSNL，用于查询学生的学号、姓名和年龄；
create view XSNL(学号,姓名,年龄)
as
select sno,sname,2011-year(sbirth)
from student
2）创建视图XSZCJ，用于查询选修了课程的学生的总评成绩，包括学号、姓名、课程号，每门课程的总评成绩计算公式为：成绩*70%+（成绩-10）*20%+10；
create view XSZCJ(学号,姓名,课程号,总评成绩)
AS
select
student.sno,student.sname,o,grade*0.7+(grade-10)*0.2+10
from student,sc,course
where student.sno=sc.sno AND o=o
3）创建视图JSGZ，用于显示教师的预发工资，包括教工号、姓名、职称和按95%预发的工资额；
create view JSGZ (教工号,姓名,职称,预发工资)
as
select tno,tname,tprof,tpay*0.95
from teacher
5.分组视图的创建
1）创建视图XSPJCJ，用于查询选修了课程的学生的成绩，包括学号、姓名和所选课程的平均成绩；
create view XSPJCJ(学号,姓名,平均成绩)
as
select student.sno,sname,avg(grade)
from student,sc
where student.sno=sc.sno
group by student.sno,sname
2）创建视图JSRKMS，用于查询教师的任课门数，包括教工号、教师名和任课门数；
create view JSRKMS (教工号,教师名,任课门数)
as
select tno,tname,count(tno)
from teacher
group by tno,tname
3）创建视图KCPJCJ，用于查询各门课程的平均成绩，包括课程号、课程名、选课人数、平均成绩；
create view KCPJCJ (课程号,课程名,选课人数,平均成绩)
as
select o,ame,count(o),avg(sc.grade)
from sc,course
where o=o
group by o,ame
4）创建视图YXXS，用于查询各门成绩和平均成绩都在85分以上的学生的学号、姓名及平均成绩；
create view YXXS
as
select sc.sno,sname,A VG(GRADE) 平均成绩
from student,sc
where student.sno=sc.sno
and not exists
(select DISTINCT sno from sc where grade<85)
group by sname,sc.sno
6.删除视图
1）删除视图XSNL；
drop view xsnl
7.通过视图修改数据库数据
1）查询视图WY_Student，然后使用INSERT…INTO…语句更新WY_Student视图，再次查询该视图以及相关基表，检查执行结果；
select *
from WY_student
insert
into WY_Student
values('06011','陈东','男','1989-01-01','外国语',null)
select *
from WY_Student
select *
from Student
2）使用UPDATE 更新视图WY_Student中的某个或某些属性值，然后再次查询该视图以并查询相关基表检查更新结果。

update WY_Student
set sname='西门吹雪'
where sname='陈东'
select *
from WY_Student
select *
from Student
3）使用DELETE删除视图WY_Student中刚插入的记录，然后查询该视图和相关基本表数据；
delete
from WY_Student
where sno='06011'
select *
from WY_Student
select *
from Student
4）修改视图JSGZ，把职称为副教授的预发工资改为该视图预发工资的90%，查看执行结果；
update JSGZ
set 预发工资=预发工资*0.9
where 职称='副教授'
对视图或函数 'JSGZ' 的更新或插入失败，因其包含有派生域或常量域。

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

update YXXS
set 平均成绩=平均成绩+3
派生表'YXXS' 不可更新，因为它包含聚合。