Oracle_view视图
视图view |
|
--视图：view
--查询班级信息并统计各班的人数
select * from stu;
select * from clazz;
select c.*,count(s.id)
from stu s
right join clazz c
on s.cid = c.id
group by c.id,c.cname;
--创建视图
create or replace view view_cls_info as (
select c.*,count(s.id) snum
from stu s
right join clazz c
on s.cid = c.id
group by c.id,c.cname
);
--删除视图
drop view view_cls_info; select * from view_cls_info;
select * from stu; |
|
--视图的作用：1.简化查询，方便操作 2.只对外显示表中的部分数据，隐藏不需要显示的数据
create or replace view view_cls_info as(
select id, sname, gender from stu
);
--可以执行DQL,DML
select * from view_cls_info;
insert into view_cls_info values(seq_stu_id.nextval,'虎子','男'); |
|
--创建只读视图,只允许执行DQL语句，不允许执行DML语句
create or replace view view_cls_info as(
select id, sname, gender from stu
) with read only; |
|
--可以基于视图再创建视图
create or replace view view_cls_info_simp as(
select id, snum from view_cls_info
);
select * from view_cls_info_simp;
select * from view_cls_info; |