14.视图的创建与删除

14.视图的创建与删除

–建立信息系学生视图包括学号姓名年龄院系
create view is_student
as
select sno,sname,sage,sdept
from student
where sdept = ‘IS’;

select *
from is_student;

create view is_student9
as
select sno,sname,sage,sdept
from student
where sdept = ‘IS’
with check option;

insert into is_student
values(‘241216118’,‘wo000w’,22,‘CS’); --成功

insert into is_student9
values(‘241216118’,‘wo000w’,22,‘CS’); --不成功 视图有约束 只能插入IS

insert into is_student9
values(‘241215118’,‘wo000w’,22,‘IS’); --成功

–视图年龄改为出生日期
create view wow
as
select sno,sname,2020-sage ‘出生日期’
from student;

select*
from wow;

–建立信息系选修一号课程学生视图
create view is_1
as
select *
from student
where sdept = ‘IS’ and sno in (select sno
from SC
where cno = 1);

select *
from is_1;

–建立信息系选修一号课程学生视图+成绩
create view is_11
as
select Student.*,SC.grade
from student,SC
where Student.sno = SC.sno and student.sdept = ‘IS’ and sc.cno = 1;

select *
from is_11;

–建立信息系选修了一号课程且成绩90以上视图
create view is_12
as
select *
from is_11
where grade>=90;

select *
from is_12;

–将学生学号和平均成绩定义为一个视图 --此时视图后名字不能省略
create view s_g(sno,avg)
as
select sno,AVG(grade)
from SC
group by sno;

–删除视图 删除 s11是可以的 但是S22不能再用了
drop view s_g;