Oracle(三)

哪里有你，我去哪里。

代鑫-<妄越>

---

一、视图

视图是一种数据库对象,是从一个或多个数据表或视图中导出的虚表,是对数据表进行查询的结果.

优点: 1.简化数据操作.

​ 2.着重于特定的数据.

​ 3.提供简单有效的安全机制,可以定制不同用户对数据的访问权限.

​ 4.提供向后兼容性.

--创建视图的语法:
CREATE [OR REPLACE] [FORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION ]
[WITH READ ONLY]
--格式解释:
--OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；
--FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
--subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
--WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
--WITH READ ONLY ：只读,该视图上不能进行任何 DML 操作。

--删除视图的语法:
Drop view view_name;

简单视图:(单表查询)

--创建视图 ：业主类型为 1 的业主信息
create or replace view view_owners1 
as select * from t_owners where ownertype=1;
--查询视图
select * from view_owners1 where addressid=1;
--修改视图
update view_owners1 set name='王刚' where id=2;

--带检查约束的视图: 根据地址表（T_ADDRESS）创建视图 VIEW_ADDRESS2 ,内容为区域 ID为 2 的记录。
create or replace view view_address2 as
select * from T_ADDRESS where areaid=2
with check option --带id约束,需根据约束修改,不能修改id.

-- 只读视图的创建与使用:(不能修改)
create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1
with read only

--创建带错误的视图:(不存在的表创建视图)
create or replace FORCE view view_TEMP as
select * from T_TEMP

复杂视图:(有聚合函数或多表关联查询)

--多表关联查询: 创建视图，查询显示业主编号，业主名称，业主类型名称
create or replace view view_owners as
select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id
--查询视图
select * from view_owners;
--修改视图
update view_owners set 业主名称='范小冰' where 业主编号=1;--修改成功

update view_owners set 业主类型='普通居民' where 业主编号=1;--修改失败
--原因:所需改的列不属于键保留表的列。

--分组聚合统计查询: 创建视图，按年月统计水费金额
create view view_accountsum as
select year,month,sum(money) moneysum
from T_ACCOUNT
group by year,month
order by year,month
--用到聚合函数，没有键保留表，所以只能查询,无法执行 update.

二.物化视图

物化视图与普通视图的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的.

优缺点： – 用空间换时间，查询效率快

​ – 占有一定的空间，数据不能及时更新

--创建物化视图语法
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE]
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery
--BUILD IMMEDIATE 是在创建物化视图的时候就生成数据,默认为 BUILD IMMEDIATE。
--BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。
--刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。
--REFRESH 后跟着指定的刷新方法有三种：FAST、 COMPLETE、 FORCE。 
--FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。如果创建REFRESH FAST增量刷新的物化视图，必须首先创建物化视图日志.
--COMPLETE 刷新对整个物化视图进行完全的刷新。
--如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。 FORCE 是默认的方式。
--刷新的模式有两种：ON DEMAND 和 ON COMMIT。 ON DEMAND 指需要手动刷新物化视图（默认）。 ON COMMIT 指在基表发生 COMMIT 操作时自动刷新。

--案例:
--创建手动刷新的物化视图,查询地址 ID,地址名称和所属区域名称.
create materialized view mv_address
as
select ad.id,ad.name adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id
--删除物化视图
drop materialized view mv_address
--执行上边的语句后查询
select * from mv_address;
--向地址表（T_ADDRESS）中插入一条新记录
insert into t_address values(8,'宏福苑小区',1,1);
--再次执行上边的语句进行查询，会发现新插入的语句并没有出现在物化视图中。
--需要通过下面的语句（PL/SQL），手动刷新物化视图：
begin
DBMS_MVIEW.refresh('MV_ADDRESS','C');--C代表完全刷新
end;
--或者通过下面的命令手动刷新物化视图：
EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');
--注意：此语句需要在命令窗口中执行。
--再次查询物化视图，就可以查询到最新的数据.

--创建自动刷新的物化视图
create materialized view mv_address2
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id
--创建时不生成数据的物化视图
--第一次生成数据必须手动执行刷新
create materialized view mv_address3
build deferred
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id;

--创建增量刷新的物化视图(了解,笔记不全)
--创建增量刷新的物化视图，必须首先创建物化视图日志:
--记录基表发生了拿些变化,用记录去更新物化视图
create materialized view log on t_address with rowid；
create materialized view log on t_area with rowid;
--创建的物化视图日志名称为 MLOG$_表名称
--创建物化视图,必须有基表的rowid
create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid, ad.id,ad.name
adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id;

三.序列

序列是ORACLE提供的用于产生一系列唯一数字的数据库对象。

--创建序列的语法
create sequence 序列名称;
--通过序列的伪列来访问序列的值
--NEXTVAL 返回序列的下一个值
select 序列名称.nextval from dual
--CURRVAL 返回序列的当前值
select 序列名称.currval from dual

--案例
create sequence myemp3_seq;
--使用序列：
insert into myemp3 values(myemp3_seq.nextval,'张三','男',10,'101001@qq.com');
--获取当前值使用myemp3_seq.currval
-- 注意：myemp3_seq.nextval 每调用一次nextval指针向后移动一位，也就是该序列增加一次；

--创建复杂序列
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中(默认缓存20)

--案例
--有最大值的非循环序列
create sequence seq_test1
maxvalue 20; --超过20报错

--修改和删除序列
--修改序列：使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START WITH 参数
ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;
--删除序列
DROP SEQUENCE 序列名称;

四.同义词:实质是别名

提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。

--创建同义词
create [public] SYNONYM synooym for object;
--其中 synonym 表示要创建的同义词的名称，object 表示表，视图，序列等我们要创建同义词的对象的名称。

--私有同义词
create synonym OWNERS for T_OWNERS;
--公有同义词:(以其他用户登录,也可使用)
create public synonym OWNERS2 for T_OWNERS;
--查询同义词
select * from 同义词名称

varchar是varchar2的同义词(兼容性的作用)

五.索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列(ROWID).

索引建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据.

--普通索引:
create index 索引名称 on 表名(列名);
--如我们经常要根据业主名称搜索业主信息，所以我们基于业主表的 name 字段来建立索引.
create index index_owners_name on T_OWNERS(name)

--索引性能测试
--创建一个两个字段的表
create table T_INDEXTEST (
ID NUMBER,
NAME VARCHAR2(30)
);
--编写 PL/SQL 插入 100 万条记录
BEGIN
FOR i in 1..1000000
loop
INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
end loop;
commit;
END;
--创建完数据后，根据 name 列创建索引
CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name)
--执行下面两句 SQL 执行
SELECT * from T_INDEXTEST where ID=765432; --用时0.031
SELECT * from T_INDEXTEST where NAME='AA765432'; --用时0.016

--唯一索引:
--需要在某个表某个列创建索引，而这列的值是不会重复的。这是可以创建唯一索引.
create unique index 索引名称 on 表名(列名);
--在业主表的水表编号一列创建唯一索引
create unique index index_owners_watermeter on T_OWNERS(watermeter);

--复合(组合)索引:
--要对某几列进行查询，比如，我们经常要根据学历和性别对学员进行搜索,可以建立复合索引，也就是基于两个以上的列建立一个索引.
create index 索引名称 on 表名(列名,列名.....);
--根据地址和门牌号对学员表创建索引
create index owners_index_ah on T_OWNERS(addressid,housenumber);
--面试题：经常查询的两个字段,对其建立索引,是建立复合索引快还是对每个字段单独建索引快？
 复合索引,一个索引对应一棵树,单独建索引就是两棵树,查询速度低于一个索引.

--反向键索引:
--当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。
--这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。
create index 索引名称 on 表名(列名) reverse;

--位图索引:
--位图索引适合创建在低基数列(有限的值,比如性别：男和女)上
--位图索引不直接存储 ROWID，而是存储字节位到ROWID的映射
--优点：减少响应时间，节省空间占用
create bitmap index 索引名称 on 表名(列名);
--在 T_owners 表的 ownertypeid 列上建立位图索引
create bitmap index index_owners_typeid on T_OWNERS(ownertypeid);

--scott用户解锁与赋权:
scott/tiger
    alter user scott identified by "tiger" account unlock;
	grant dba to scott