定义视图

定义视图

（1）定义计算机系学生基本情况视图V_Computer；

create view v_computer(学号,姓名,性别,专业)

as

select学生信息表.sno , sname , sex , cname

from学生信息表,专业信息表,课程信息表

where学生信息表.spno=专业信息表.spno

and专业信息表.spno =课程信息表.spno

and spname ='计算机系'

（2）将Student、Course 和Student_course表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

create view V_S_C_G

as

select学生信息表.sno'学号', sname'姓名',课程信息表.cno'课程号

',score'成绩', cname'课程名'

from学生信息表,课程信息表,学生选课成绩表

where学生信息表.sno =学生选课成绩表.sno

and学生选课成绩表.cno =课程信息表.cno

（3）将各系学生人数，平均年龄定义为视图V_NUM_AVG

create view v_unmber_avg(系别号,系别名称,学生人数,平均年龄)

as

select专业信息表.spno,spname ,count(*),avg(datediff

(year,birthday,entime))

from专业信息表,学生信息表

where专业信息表.spno=学生信息表.spno

group by专业信息表.spno,spname

（4）定义一个反映学生出生年份的视图V_YEAR

create view v_year(学号,姓名,出生年月)

as

select学生信息表.sno,sname,birthday

from学生信息表

（5）将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G create view v_avg_s_g(学号,姓名,选修课门数,平均成绩)

as

select学生信息表.sno, sname,count(*),avg(score)

from学生信息表,学生选课成绩表

where学生信息表.sno =学生选课成绩表.sno

group by学生信息表.sno,sname

（6）将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G

create view v_avg_c_g(课程号,课程名称,平均成绩)

as

select课程信息表.cno,cname,avg(score)

from课程信息表,学生选课成绩表

where课程信息表.cno=学生选课成绩表.cno

group by课程信息表.cno,cname

（7）定义学生信息视图V_IS(学号、姓名、年龄、院系)

create view v_is(学号,姓名,年龄,院系)

as

select学生信息表.sno,sname,datediff (year,birthday,entime),spname

from学生信息表,专业信息表

where学生信息表.spno =专业信息表.spno

group by学生信息表.sno,sname, birthday,entime,专业信息表.spname

使用视图

（1）查询以上所建的视图结果。

select学号,姓名

from v_is

where院系='计算机系'

（2）查询平均成绩为90分以上的学生学号、姓名和成绩；

select学号,姓名,平均成绩

from v_avg_s_g

where平均成绩> 80

（3）查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；select v_s_c_g.学号,v_avg_s_g.姓名, v_s_c_g.课程名,v_s_c_g.成绩

from v_s_c_g ,v_avg_s_g

where v_s_c_g.学号=v_avg_s_g.学号and

v_s_c_g.成绩> v_avg_s_g.平均成绩

（4）按系统计各系平均成绩在80分以上的人数，结果按降序排列；create view系_均(系别号,系名称,系平均成绩)

as

select专业信息表.spno,spname,avg(score)

from专业信息表,学生选课成绩表,课程信息表

where课程信息表.spno =专业信息表.spno

and课程信息表.cno =学生选课成绩表.cno

group by专业信息表.spno,spname

修改视图

（1）通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM”并查询结果;

create trigger bb on v_is

instead of update

AS

UPDATE V_IS

set姓名='S1_MM'

where学号='JSJ001'

修改的语句如下：

UPDATE学生信息表

SET sname ='S1_MMM'

where sno ='JSJ001'

UPDATE学生信息表

set sname ='s2_MMM'

WHERE SNO ='JSJ002'

（2）通过视图V_IS，新增加一个学生记录('S12','YAN XI',19,'IS')，并查询结果

创建触发器的语句：

create trigger bb on v_is

instead of insert

as

insert into v_is values('S13','YAN XI','19','MA')

插入的语句：

insert into dbo.学生信息表(sno,sname,birthday,entime,spno )values

('s13','yan xi','1989','2008','111')

insert into dbo.专业信息表(spno,spname)values('111','ma')

（3）通过视图V_IS，新增加一个学生记录('S13','YAN XI',19,'MA')，并查询结果

代码：

insert into dbo.学生信息表(sno,sname,birthday,entime,spno )values ('s12','yan xi','1989','2008','111')

insert into dbo.专业信息表(spno,spname)values('111','ma')

（4）通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果