视图的创建
as 后的子查询可以是任意的SELECT语句,是否可以含有ORDER BY 子句和DISTINCT短语则取决于具体的实现
--建立信息系学生的视图(学号,姓名,年龄),视图名为is_student
create view is_student as
(select sname 姓名, sno 学号, sage 年龄 ,sdept 系名 from student where sdept = 'IS');
WITH CHECK OPTION表示对视图进行UPDATE、INSERT、 DELETE操作时要保证更新、插入或删除的行满足视图定义中 的谓词条件(即子查询中的where条件表达式)
--建立信息系学生的视图(学号,姓名,年龄,系名),并要求进行修改和插入操作时仍保证该视图只有信息系学生
create view is_student2 as
select sname , sno , sage ,sdept from student
where sdept = 'IS'
with check option;
INSERT INTO is_student2( Sno, sname, sage, sdept ) VALUES( '200212132', '李一', 19, 'CS'); --插入失败
--创建信息系选修了1号课程的学生视图(包括姓名,学号,成绩),视图名为is_IS
create view is_IS as
select sname,student.sno,grade from student,sc
where sdept = 'IS' and cno = '1' and student.sno = sc.sno;
create view is_IS as
select sname,is_student2.sno,grade from is_student2,sc
where is_student2.sno = sc.sno;
--创建一个创建信息系选修了1号课程且成绩在90分以上的学生视图,视图名为is_IS2
create view is_IS2 as
select * from is_IS
where grade >= 90;
select * from is_IS2;
--创建一个反应学生出生年份的的视图BT_S
create view BT_S as
select sno,sname, 2019- sage "出生年份" from student;
--将学生学号及他的平均成绩定义为一个视图S_G
create view s_g(sno,avggrade) as
select sno,avg(grade) from sc group by sno;
create view s_g(sno,avggrade) as
select sno,avg(grade)avggrade from sc group by sno;
--将学生表中所有女生记录定义为一个视图F_student;
create view f_student as
select * from student where ssex = '女';
select * from f_student;
删除视图
--删除视图f_student
drop view f_student;
查询视图
--在信息系学生的视图中找出年龄小于20岁的学生
select * from is_student2 where sage < 20;
--查询选修课1号课程的信息系的学号,姓名
select sno,sname from is_student2
where sno in
(select sno from sc where cno = '1');
--在S_G视图中查询平均成绩在88分以上的学生学号和平均成绩
select sno,avggrade from s_g where avggrade >= 88;
更新视图
–将信息系学生视图is_student2中学号为200212122的学生姓名改为’张三’
update is_student2 set sname = ‘张三’ where sno = ‘200212122’;
–向视图is_student2中插入一个新的学生记录,学号为201215130,姓名为赵新,年龄为20
insert into is_student2 (sname,sno,sage,sdept) values (‘赵新’,‘201215130’,20,‘IS’);
–删除信息系学生视图is_student中学号为201215130的记录
delete from is_student2 where sno = ‘201215130’;