数据库（四）

视图的定义、使用实验

以students，courses，sc表为基础完成以下视图定义：

1.定义“IS”系学生基本情况视图V_IS 并查询结果；

create view V_IS  as select * from  S where Sdept ='IS';
select sno from V_IS ;

2.将students，courses，sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;

create view V_S_C_G(sno,sname,cno,cname,grade)as select S.Sno ,S.Sname,c.cno,c.cname,sc.grade from S ,C,SC where S.Sno=sc.sno and c.cno=sc.cno;

select sname from V_S_C_G ;

3.将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;

create view V_NUM_ANG(sdept,num,Gang) as select sdept,COUNT(*) num,AVG(Sage) Gang from S group by Sdept ;
select num from V_NUM_ANG ;

4.定义一个反映学生出生年份的视图V_YEAR并查询结果;

create view V_YERY(sno,sname,Sbirth) as select sno,sname,2017-sage from S ;
select sbirth from V_YERY ;

5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;

create view V_AVG_S_G(sno,num,Gavg) as select sno ,COUNT (sno),AVG (grade) from SC group by sno;
select num from V_AVG_S_G ;

6.建立视图V_AVG平均成绩为90分以上的学生学号；

create view V_AVG as select sno from SC group by sno having AVG(grade)>90;
 select sno from V_AVG ;

7.通过视图V_IS，分别将学号为“95001”和“95002”的学生姓名更改为“王明”,”王强”并查询结果;

update V_IS  set Sname ='王明'where Sno ='95001';
update V_IS  set Sname ='王强'where Sno='95002';
select sname from V_IS ;

8.通过视图V_IS，新增加一个学生记录 (’95005’,’宋明’,19,’IS’)，并查询结果；

insert into V_IS(Sno,Sname,Sage,Sdept) values('95005','宋明',19,'IS');

9.通过视图V_IS，新增加一个学生记录 (’95006’,’王芳’,19,’MA’)，并查询结果；

insert into V_IS(Sno,Sname,Sage,Sdept) values('95006','王芳',19,'MA');

10.通过视图V_IS，删除学号为“95005”和“95006”的学生信息，并查询结果；

delete from V_IS where Sno='95005' and sno='90006';
 select sname from V_IS where Sno='95005' and Sno='95006';