数据库实验---视图

一、实验目的：

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视图是不可更新的。