Oracle基础4

1 视图
1.1 视图的基本创建
查询 t_owners 和 查询 view_test_1 实际是一样的
数据源表发生变化 那么视图也会发生变化
drop view VIEW_TEST_1;
select *
from T_OWNERS;
create view view_test_1 as
(
select *
from T_OWNERS
    );
select *
from view_test_1;
1.2 对复杂sql简化
显示 业主id 业主name 地址 区域
create view view_test_2 as
(
select T_OWNERS.ID,
       T_OWNERS.NAME  user_name,
       T_ADDRESS.NAME addr_name,
       T_AREA.NAME    area_name
from T_OWNERS
         join T_ADDRESS on T_OWNERS.ADDRESSID = T_ADDRESS.ID
         join T_AREA on T_ADDRESS.AREAID = T_AREA.ID
    );
select *
from VIEW_TEST_2;
视图简化/显示想要显示的字段

查询视图

todo 1.3 向后兼容
创建表 v_1 具有 编号 姓名 年龄字段
drop view V_1;
create table v_1
(
    编号 number,
    姓名 varchar2(30),
    年龄 number
);
drop table v_1;
插入数据
insert into v_1
values (1, '老王', 18);
commit;
select *
from v_1;
查询 所有 姓名

创建表 v_2 具有 id name age sex 字段
drop table v_2;
create table v_2
(
    id   number,
    name varchar2(30),
    age  number,
    sex  varchar2(30)
);
通过 v_1 同步数据到 v_2
insert into v_2(id, name, age) (select * from v_1);
commit;
select *
from v_2;
删除表 v_1
drop table v_1;
查看数据

创建视图 使得 v_2 可以向后兼容
create view v_1 as
(
select id 编号, name 姓名, age 年龄
from v_2
    );
select *
from v_1;
2 视图的简单使用
-- 简单视图: 数据来源只有一个表 没有聚合操作
-- 注意: 可以进行数据的事务性操作
2.1 创建视图  获取t_address中所有areaid为3的视图
create view view_test_3
as
select *
from T_ADDRESS
where AREAID = 3;
-- 查看视图
select *
from view_test_3;
select *
from T_ADDRESS;
insert into T_ADDRESS
values (8, '翻斗花园', 1, 1);
-- 修改非视图产生的约束字段 把视图中 name=西三旗 改成 name=西二旗
update view_test_3
set NAME = '西三旗'
where name = '西二旗';
-- 查看视图

2.2 修改视图外的数据(无法修改 视图外的数据)

-- 注意:
-- 视图中显示的数据 我们可以进行任意操作 并且可以影响 我们的原始表
-- 但是 视图以外的数据 不可以通过视图进行任何操作(操作权限仅限视图中的数据)
2.3 修改视图产生的约束字段
-- 把视图中的 areaid 改成 1
update view_test_3
set NAME = '召唤师峡谷'
where id = 8;
update view_test_3
set NAME = '召唤师峡谷'
where id = 7;
update view_test_3
set AREAID = 1
where ID = 7;
update T_ADDRESS
set AREAID = 3
where ID = 7;
-- 查看视图

-- 查看原始数据表

-- 如果修改 数据产生的字段 就会造成我们视图数据不完整(不希望这样)
2.4 带约束 with check option 的视图
-- 创建视图 获取t_address中所有areaid为3的数据 并设置为 with check option
drop view view_test_4;
create view view_test_4 as
select *
from T_ADDRESS
where AREAID = 3
with check option;
-- 查看视图
select *
from view_test_4;
-- 修改非视图产生约束字段

-- 查看视图

-- 修改视图产生约束字段的数据
update view_test_4
set AREAID = 1
where ID = 7;
delete
from view_test_4
where AREAID = 3;
rollback;
-- 删除视图产生约束字段的数据

-- 插入视图产生约束字段的数据

-- 查看视图

3 只读视图 视图替换 无数据源视图
3.1 只读视图(数据不可变化) with read only
-- 创建只读视图

-- 修改数据

-- 插入数据

-- 删除数据

3.2 视图替换 or replace
-- 创建视图 替换 上一个视图 (视图内容不一样)

-- 查看

3.3 没有数据源的视图 force
-- 创建一个没有数据源的视图

-- 查看视图

-- 创建数据源表

3.4 删除视图
-- 删除视图

-- 删除数据表

4 复杂视图
-- 复杂视图:
-- 注意:
4.1 需求：创建视图，查询显示业主编号，业主名称，业主类型名称
-- 创建视图，查询显示业主编号，业主名称，业主类型名称 t_owners t_ownertype
create view view_test_6 as
select t1.ID, t1.NAME, t2.NAME as type_name
from T_OWNERS t1
         join T_OWNERTYPE t2 on t1.OWNERTYPEID = t2.ID;
-- 查看视图
select *
from view_test_6;
-- 修改 视图中数据 name='范冰冰'  id=1
-- (注意: )
update view_test_6
set NAME = '范冰冰'
where ID = 1;
-- 查看视图

-- 查看t_owners表数据
select *
from T_OWNERS;
-- 修改 视图中数据 type_name='商业' id=1
-- (注意: )

-- 查看视图数据

-- 查看t_ownertype表数据
select *
from T_OWNERTYPE;

4.2 需求：创建视图，按年月统计水费金额t_account
-- 创建视图，按年year 月month 统计水费金额money t_account
create view view_test_7 as
select YEAR, MONTH, sum(MONEY) as money
from T_ACCOUNT
group by YEAR, MONTH;
-- 查看视图
select *
from view_test_7;
--修改数据 把视图中month为01 的money值进行修改
update view_test_7
set money = 1000
where MONTH = '01';

5 物化视图的使用
5.1 需求：查询地址 ID,地址名称和所属区域名称 t_address t_area
-- 创建手动更新的物化视图(默认)
-- create materialized view 视图名
-- build immediate
-- refresh force on demand
create materialized view view_test_8
            build immediate
    refresh force on demand
as
select T_ADDRESS.ID, T_ADDRESS.NAME, T_AREA.NAME area
from T_ADDRESS
         join T_AREA on T_ADDRESS.AREAID = T_AREA.ID;
-- 查询视图
select *
from VIEW_TEST_8;
-- 向 t_address添加数据 (8,'宏福苑小区',1,1) 查看是否同步数据
insert into T_ADDRESS
values (9, '宏福苑小区', 1, 1);
commit;
-- 这里需要手动刷新 begin dbms_mview.refresh('view_test_8') end;
begin
    DBMS_MVIEW.REFRESH('view_test_8');
end;

5.2 创建自动更新的物化视图
-- 需求：查询地址 ID,地址名称和所属区域名称 t_address t_area
-- create materialized view 视图名
-- build immediate
-- refresh force on commit
drop materialized view VIEW_TEST_9;
create materialized view VIEW_TEST_9
            build immediate
    refresh force on COMMIT
as
select T_ADDRESS.ID, T_ADDRESS.NAME, ta.NAME area
from T_ADDRESS
         join T_AREA TA on T_ADDRESS.AREAID = TA.ID;
-- 查看视图
select *
from VIEW_TEST_9;
-- 向 t_address添加数据 (9,'龙旗2区',1,1) 查看是否同步数据
insert into T_ADDRESS
values (10, '龙旗2区', 1, 1);
commit;
-- 查看视图
select *
from VIEW_TEST_9;

5.3 创建不生成数据的物化视图 bulid deferred(延时生成数据)
-- 需求：查询地址 ID,地址名称和所属区域名称 t_address t_area
-- create materialized view 视图名
-- build deferred
-- refresh force on commit
create materialized view view_test_10
            build deferred
    refresh force on COMMIT
as
select T_ADDRESS.ID, T_ADDRESS.NAME, T_AREA.NAME area
from T_ADDRESS
         join T_AREA on T_ADDRESS.AREAID = T_AREA.ID;
-- 查看数据
select *
from VIEW_TEST_10;
-- 刷新后生成数据 begin DBMS_MVIEW.REFRESH('view_test_10'); end;
begin
    DBMS_MVIEW.REFRESH('view_test_10');
end;

6 物化视图 增量/全量 更新
6.1 创建增量更新物化视图
-- 需求：查询地址 ID,地址名称和所属区域名称 t_address t_area
-- 注意:
-- 1. 创建增量更新物化视图 所有的源数据表 必须有物化视图日志
-- 2. 创建的增量更新物化视图 中 必须包含 源数据表中的rowid
create materialized view log on T_ADDRESS with rowid;
create materialized view log on T_AREA with rowid;
-- 创建物化视图日志
-- create materialized view log on T_ADDRESS with rowid;
-- create materialized view log on T_AREA with rowid;
select *
from MLOG$_T_AREA;
select *
from MLOG$_T_ADDRESS;
-- 查看t_address的日志 MLOG$_T_ADDRESS
select *
from T_ADDRESS;
-- 向t_address表插入数据 (10,'白各庄社区',1,1)
insert into T_ADDRESS
values (11, '白各庄社区', 1, 1);
-- 查看t_address的日志 MLOG$_T_ADDRESS
create materialized view view_test_11
            build immediate
    refresh fast on DEMAND
as
select  T_ADDRESS.ROWID as addr_rowid,
        T_AREA.ROWID as area_rowid,
        T_ADDRESS.ID, T_ADDRESS.NAME, T_AREA.NAME area
from T_ADDRESS
         join T_AREA on T_ADDRESS.AREAID = T_AREA.ID;
select * from C##WATERUSER.VIEW_TEST_11;
insert into T_ADDRESS values (12, '顺义', 1, 1);
commit ;
begin
    DBMS_MVIEW.REFRESH('view_test_11',method =>'f');
end;
select * from C##WATERUSER.VIEW_TEST_11;

-- 创建增量更新手动刷新表
-- create materialized view view_test_11
-- build immediate
-- refresh fast on demand
-- as
-- select
--         T_ADDRESS.ROWID as addr_rowid,
--         T_AREA.ROWID as area_rowid,
--         T_ADDRESS.ID, T_ADDRESS.NAME, T_AREA.NAME area
-- from T_ADDRESS
-- inner join T_AREA on T_ADDRESS.AREAID = T_AREA.ID;

-- 向t_address表中增加数据(11, '顺义', 1, 1)

-- 查看T_ADDRESS表数据

-- 查看视图数据(非select方式查看)

-- 手动刷新 begin DBMS_MVIEW.REFRESH('view_test_', METHOD =>'f'); end;

7 创建全量物化视图
7.1 需求: 查询地址 ID,地址名称和所属区域名称
-- create materialized 视图名
-- build immediate
-- refresh complete on commit

-- 向t_address表添加数据(12, '顺义校区', 1, 1)

-- 查询 t_address表 数据

-- 查询视图

7.2 删除视图
-- drop materialized view 视图名;

7.3 删除视图日志
-- drop materialized view log on 数据表名;

8 序列
8.1基本使用
-- 创建序列  seq_a 默认为从1开始 差值为1 的等差数列

-- 获取序列值
-- 序列名.currval 当前值(需要先获取next值 才可以获取当前值)
-- select seq_a.currval from dual;

-- 序列名.nextval 下一个值

8.2 最 大/小 值
-- 需求1: 创建序列名为 seq_b，它以5递增，从10开始，最大值为30，最小值为2。
-- create sequence 序列名
-- increment by 递增值
-- start with 起始值
-- maxvalue 最大值
-- minvalue 最小值;

-- 起始值不能小于最小值
-- 查看序列值(超过最大值报错)

8.3 循环cycle
-- 需求2: 创建序列名为 seq_c，它以5递增，从10开始，最大值为30，最小值为2, 带循环
-- 默认没有循环
-- 默认cache值为20

-- 查看序列值

-- 删除序列

8.4 给数据表增加序列值
-- 1 创建序列 seq_student, 每次递增2, 从1000开始

-- 2 创建学生表 tb_student(sid, sname)
create table tb_student
(
    sid   number,
    sname varchar2(30)
);
-- 3 插入数据, sid 使用序列生成的值

-- 4 查询数据

-- 5 重置序列起始值
-- truncate table 不能重置序列值

-- 重置序列值 直接删除序列 重新创建即可
-- drop sequence

-- 6 查询数据

9 同义词
-- create [public] synonym 名称 for obj;

-- 1 需求：为表 T_AREA  创建( 私有 )同义词 名称为 sym_test1

-- 2 需求：为表 T_AREA  创建( 共有 )同义词 名称为 sym_test2

-- 3 查看私有同义词

-- 4 验证 其他用户 是否能查看公共同义词

10 索引
10.1 普通索引
-- 创建表 t_index_test
create table t_index_test
(
    id   number,
    name varchar2(30)
);
-- 插入数据
begin
    for i in 1 .. 10000000
        loop
            insert into t_index_test values (i, 'dev' || i);
        end loop;
    commit;
end;
-- 验证性能 在虚拟机中set timing on可以开启sql执行时间检测
-- 没有索引的情况下 查询
select *
from t_index_test
where name = 'dev5555555';
-- 创建普通索引
-- create index 索引名 on 表名(索引字段);
create index index_test on t_index_test (name);
-- 验证性能 在虚拟机中set timing on可以开启sql执行时间检测
select *
from t_index_test
where name = 'dev5555555';

10.2 唯一索引
-- 唯一索引
-- create unique index 索引名 on 表名(索引字段);
create unique index index_test2 on t_index_test (id);
-- 验证性能
select *
from t_index_test
where id = 5555555;

10.3 复合索引
-- 复合索引
create index index_test3 on t_index_test (id, name);
-- 验证性能
select *
from t_index_test
where name = 'dev5555555'
  and id = 5555555;