实验五 视图的定义、使用实验

-- １．  定义“IS”系学生基本情况视图V_IS 并查询结果；
create view V_IS as
select * from student
where sdept='IS'
with check option ;
/*
drop view v_IS;
create view V_IS as
select * from student
where sdept='IS';
*/
-- select *from v_IS;
-- ２．  将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;
-- drop view V_S_C_G;
create view V_S_C_G as
select sno,sname,cno,cname,grade
from sc join student using(sno)
join course using(cno);
SELECT *from v_s_c_g;
-- ３．  将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;
create VIEW V_NUM_AVG as
select Sdept,count(sno) from student
group by sdept;
SELECT * from v_num_avg;
-- ４．  定义一个反映学生出生年份的视图V_YEAR并查询结果;
create view V_YEAR as
select sno,year(now())-sage as Birthyear 
from student;
select *from v_year;
-- ５．  将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;
-- drop view V_AVG_S_G;
create view V_AVG_S_G as
select sno,count(cno),avg(grade) as avg_grade 
from sc join student using(sno)
group by sno;
SELECT * from v_avg_S_G;
-- ６．  将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G并查询结果;
create view V_AVG_C_G as
select cno,count(*) as cnum,avg(grade) avg_grade
from sc group by cno;
select *from V_AVG_C_G;
-- ７．  查询平均成绩为90分以上的学生学号、姓名和成绩；
SELECT sno,sname,avg_grade from v_avg_S_G
join student using(sno)
where avg_grade>=90 ;
-- ８．  查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；
SELECT sno,sname,cno,grade from sc a
join student using(sno)
where not exists (
	select * from sc b join v_avg_c_g using(cno)
	where a.Grade< v_avg_c_g.avg_grade and a.Cno=b.Cno
);
-- ９．  按系统计各系平均成绩在80分以上的人数，结果按降序排列；
select sdept,count(*) num 
from  student join v_avg_s_g using(sno)
where avg_grade > 80 
group by sdept
order by num desc;
-- １０．通过视图V_IS，分别将学号为“200215121”和“200215124”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果;
update V_IS set sno='S1_MMM' where sno='200215121'; -- 由于该同学为CS系,因此更新失败
update V_IS set sno='S4_MMM' where sno='200215124';
select *from v_is;
select *from student;
select *from sc;
select *from v_s_c_g;
-- １１．通过视图V_IS，新增加一个学生记录 ('S12','YAN XI',19,'IS')，并查询结果；
insert into V_IS(sno,sname,sage,sdept) values('S12','YAN XI',19,'IS');
-- １２．通过视图V_IS，新增加一个学生记录 ('S13','YAN XI',19,'MA')，并查询结果；
-- 注意由于该学生是MA系的,当有check约束时将会产生更新错误.
insert into v_is(sno,sname,sage,sdept) values('S13','YAN XI',19,'MA');
-- １３．通过视图V_IS，删除学号为“S12”和“S13”的学生信息，并查询结果；
-- 由于s13不是is系,因此不能删除
delete from v_is where sno='S12' or sno='S13' ;
-- １４．要通过视图V_S_C_G，将学号为“200215124”的姓名改为“200215124_MMM”，是否可以实现？
update v_s_c_g set sname='200215124_MMM' where sno='200215124';
-- １５．要通过视图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现？
-- 不能实现,因为视图是不真正存在的,对视图的修改是对基本表的修改,
-- 而平均分在基本表中不存在,涉及多个成绩,不能修改