SQL server 实验四视图的定义、使用_修改 student 表结构,新增一列 addr 用于描述地址信息,并设置数据类型以及长度为-优快云博客

本文链接：https://blog.youkuaiyun.com/Essa_S/article/details/80265599

本文介绍了使用SQL创建多种视图的方法，包括基于单一表的简单视图、涉及多表连接的复杂视图以及带有聚合函数的视图，并演示了如何更新、插入和删除视图中的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/* 1 */
create view V_IS
as
select *
from Student
where Sdept='IS';

/* 2 */
create view V_S_C_G
as
select Student.Sno,Student.Sname,Course.Cno,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;

/* 3 */
create view V_NUM_AVG
as
select	Sdept,COUNT(*) NUM,AVG(Sage) S_AVG
from Student
group by Sdept;


/* 4 */
create view V_YEAR
as
select Sno,Sname,Ssex,Sdept,2018-Sage BIRTHDAY
from Student;

/* 5 */
create view V_AVG_S_G
as
select Student.Sno,Sname,COUNT(Cno) CLASSES,AVG(Grade) G_AVG
from Student,SC
where Student.Sno=SC.Sno
group by Student.Sno,Sname;


/* 6 */
create view V_AVG_C_G
as
select Course.Cno,Cname,COUNT(Sno) PEOPLE,AVG(Grade) G_AVG
from Course,SC
where Course.Cno=SC.Cno
group by Course.Cno,Cname;

/* 7 */
create view AVG_G_90
as
select Student.Sno,Sname,Cno,Grade
from Student,SC
where Student.Sno=SC.Sno and Student.Sno=(	select Sno
											from SC
											group by Sno
											having AVG(Grade)>=90);

/* 8 */
update V_IS
set Sname='S1_MMM'
where Sno='20161513';

/* 9 */
insert
into V_IS
values('S12','YAN XI','男',19,'IS');

select *
from V_IS
where Sno='S12';

/* 10 */
insert
into V_IS(Sno,Sdept)
values('S13','IS');

delete 
from V_IS
where Sno='S12'
delete
from V_IS
where Sno='S13';

select *
from V_IS;