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;