一、实验目的:
1.掌握创建视图的SQL语句的用法。
2.掌握修改视图的方法。
3.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用;
4.熟悉和掌握对数据表中视图的更新操作和SQL命令的使用,并注意视图更新与基本表更新的区别与联系
5.学习灵活熟练的进行视图的操作,认识视图的作用
二、实验环境:
SQL Serve 2017
三、实验内容:
Part 1 定义视图
在数据库中,以Student Course 和sc 表为基础完成以下视图定义:
1. 定义计算机系学生基本情况视图V_Computer;
createview V_Computer
As
select sno,sname,ssex,sage,sdept
from student
where sdept='计算机系';
2.将Student Course 和sc表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_S_C_G
createview V_S_C_G
as
select student.sno,sname,course.cno,cname,grade
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno;
3.将各系学生人数,平均年龄定义为视图V_NUM_AVG
create view V_NUM_AVG(sdept,sums,asage)
as
select sdept,count(*),avg(sage)
from student
group by sdept
4.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G
createviewV_AVG_S_G(sno,sname,sums,agrade)
as
select sc.sno,sname,count(*),avg(grade)
from student,sc
where student.sno=sc.sno
groupby sc.sno,sname
5. 各门课程的选修人数及平均成绩定义为视图V_AVG_C_G
createviewV_AVG_C_G(cname,sums,agrade)
as
select cname,count(*),avg(grade)
from course,sc
where course.cno=sc.cno
groupby cname
Part 2 使用视图
1.查询以上所建的视图结果。
Select *
from V_Computer
Select*
from V_S_C_G
Select*
from V_NUM_AVG
Select*
from V_AVG_S_G
Select*
from V_AVG_C_G
(其它视图V_S_C_G,V_NUM_AVG, V_YEAR, V_AVG_S_G, V_AVG_C_G的查询方法类似)
2. 查询平均成绩为90分以上的学生学号、姓名和成绩;
参考SQL语句:
SelectdistinctV_AVG_S_G .sno,V_S_C_G .sname,agrade
From V_AVG_S_G,V_S_C_G
Where V_S_C_G.sno= V_AVG_S_G .snoand agrade >90;
3. 查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩;
createview V_AVG
as
select SC.Cno,avg(Grade)AvgGrade
from Course,SC
where Course.Cno=SC.Cno
groupby SC.Cno;
select
distinctStudent.Sno,Sname,sc.Cno,Grade
from Student,SC,V_AVG
whereStudent.Sno=SC.Sno and sc.cno=V_AVG.Cno and Grade >V_AVG.AvgGrade;
4. 按系统计各系平均成绩在80分以上的人数,结果按降序排列;
selectdistinct student.sdept,count(*)sum
from student,V_AVG_S_G
where student.sno=V_AVG_S_G.sno and agrade>80
groupby sdept
orderbysumdesc;
Part3 修改视图
1. 通过视图V_Computer,将学号为“201215121”的学生姓名进行修改,并查询结果;
updateV_Computer
setSname='John'
where Sno='200215121';
select*
from V_Computer ;
Select*from student;
2. 通过视图V_Computer,新增加一个学生记录 ('201215201','YAN XI',19,'IS'),并查询结果
insertintoV_Computer(Sno,Sname,Sage,Sdept)
values('201215201','YAN XI',19,'IS');
select*
from V_Computer ;
Select*from student;
3.通过视图V_Computer,新增加一个学生记录('201215301','Yangyang',20,'MA'),并查询结果
insertintoV_Computer(Sno,Sname,Sage,Sdept)
values('201215301','Yangyang',20,'MA');
select*
from V_Computer ;
Select*from student;
4. 要通过视图V_S_C_G,将学号为“201215127”的姓名改为“Weiwen”,是否可以实现?并说明原因
答:可以实现。一般来说,行列子集视图是可更新的。
update V_S_C_G
setsname='Weiwen'
where sno='201215127';
select*
from V_S_C_G ;
Select*from student;
5. 要通过视图V_AVG_S_G,将学号为“201215121”的平均成绩改为90分,是否可以实现?并说明原因
update V_AVG_S_G
set agrade=90
where sno='201215121'
答:不可以实现。这个对视图的更新是无法转换成对基本表sc的更新的,因为系统无法修改各科成绩为90。所以V_AVG_S_G视图是不可更新的。