视图的基本用法

视图

什么是视图？

1.视图是一个虚拟表
2.视图并不真实存储数据
3.视图中的数据只是对基表的引用
4.视图是由查询语句产生的

视图的作用

1.简化数据操作
2.着重为特定的数据提供一定的安全性
3.向后提供兼容性

视图的基本语法

create [or replace] [force] view view_name
as
subquery
[with check option]
[with read only]
-- 解释
-- or replace 
-- 替换已经存在的视图
-- force
-- 强制创建视图
-- with check option
-- 不能修改视图形成的字段数据
-- with read only
-- 只读不能进行修改

数据准备

--地址表
create table t_address
(
    id         number primary key,
    name       varchar2(100),
    areaid     number,
    operatorid number
);
--地址信息--
insert into t_address
values (1, '明兴花园', 1, 1);
insert into t_address
values (2, '鑫源秋墅', 1, 1);
insert into t_address
values (3, '华龙苑南里小区', 2, 2);
insert into t_address
values (4, '河畔花园', 2, 2);
insert into t_address
values (5, '霍营', 2, 2);
insert into t_address
values (6, '回龙观东大街', 3, 2);
insert into t_address
values (7, '西二旗', 3, 2);

or replace

-- 创建视图 替换 上一个视图 (视图内容不一样)
create or replace view view_test_1 as
    select * from T_ADDRESS where AREAID=2;
-- 查看
select * from view_test_1;

force

-- 创建一个没有数据源的视图
create force view view_test_2 as
select * from xx;
-- 查看视图
select * from view_test_2;
-- 创建数据源表
create table xx(id int,name varchar2(30));
insert into xx values (1,'22');
select * from xx;

with check option

create view view_test_3 as
select * from T_ADDRESS where AREAID=3
with check option;

with read only

create view view_test_0 as
    select * from T_ADDRESS where AREAID=3
with read only;

数据准备

在这里插入代码片

复杂视图

-- 创建视图，查询显示业主编号，业主名称，业主类型名称 t_owners t_ownertype
create view view_test_7 as
select t_owners.id,
       t_owners.name,
       t_ownertype.name typename
from T_OWNERS
         inner join T_OWNERTYPE on T_OWNERS.ownertypeid = T_OWNERTYPE.id;

-- 创建视图，按年year 月month 统计水费金额money t_account
select year,
       month,
       sum(money) as money
from t_account
group by year, month;
-- 查看视图
create view view_test_8 as
select year,
       month,
       sum(money) as money
from t_account
group by year, month;

物化视图

基本语法

create materialized view view_name
[build immediate  |  build deferred ]
refresh [fast|complete|force]
[
on  [commit  |  demand ]  |  start  with  (start_time)  next
(next_time)
]
as
subquery

• materialized view
  • 物化视图
• build immediate(默认)
  • 创建视图后 立马生成数据
• build deferred
  • 创建视图后 等待刷新生成数据
• refresh : 数据刷新
  • fast : 增量更新
  • complete : 全量更新
  • force (默认方式)
    • 尝试使用fast更新
    • 再去使用全量更新
  • on (确定更新时机)
    • commit : 基表只要commit就会开始更新
    • demand : 手动方式(刷新) (默认的)

手动更新

-- 创建手动更新的物化视图(默认)
-- create materialized view 视图名
-- build immediate
-- refresh force on demand
create materialized view view_test_9
build immediate
refresh force on DEMAND
as
select
t_address.id,
t_address.name addr,
t_area.name area
from T_ADDRESS
         inner join T_AREA on T_ADDRESS.areaid = T_AREA.id;

自动更新

-- 需求：查询地址 ID,地址名称和所属区域名称 t_address t_area
-- create materialized view 视图名
-- build immediate
-- refresh force on commit
create materialized view view_test_10
build immediate
refresh force on commit
as
select
t_address.id,
t_address.name addr,
t_area.name area
from T_ADDRESS
         inner join T_AREA on T_ADDRESS.areaid = T_AREA.id;

创建不生成数据的物化视图

-- todo 5.3 创建不生成数据的物化视图 bulid deferred(延时生成数据)
-- 需求：查询地址 ID,地址名称和所属区域名称 t_address t_area
-- create materialized view 视图名
-- build deferred
-- refresh force on commit
create materialized view view_test_11
build deferred
refresh force on commit
as
select
t_address.id,
t_address.name addr,
t_area.name area
from T_ADDRESS
         inner join T_AREA on T_ADDRESS.areaid = T_AREA.id;