网站建设方案 文库,动画设计师培训,网站后台有安全狗,中国十大培训机构影视后期学自B站黑马程序员
1.视图 (物化视图序列同义词oracle特有#xff09; 1.对一个SQL语句的封装#xff0c;一个虚拟的表。-简化开发 视图是一种数据库对象#xff0c;是从一个或者多个数据表或视图中导出的虚表#xff0c;视图所对应的数据并不真正地存储在视图中#xff…学自B站黑马程序员
1.视图 (物化视图序列同义词oracle特有 1.对一个SQL语句的封装一个虚拟的表。-简化开发 视图是一种数据库对象是从一个或者多个数据表或视图中导出的虚表视图所对应的数据并不真正地存储在视图中而是存储在所引用的数据表中视图的结构和数据是对数据表进行查询的结果。视图和表的使用是相同的可以select
使用视图的优点 简化数据操作视图可以简化用户处理数据的方式。 着重于特定数据不必要的数据或敏感数据可以不出现在视图中。 视图提供了一个简单有效的安全机制可以定制不同用户对数据的访问权限。 提供向后兼容性视图使用户能够在表的架构更改时为表创建向后兼容接口。 2.创建或修改视图语法 //基本subquery是查询语句
CREATE VIEW view_name AS subquery
//[]可选
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_name3.简单视图 //视图可以在views文件夹中找到,视图是虚拟表修改视图数据相当于修改表数据改表的数据则视图也跟着变视图在数据库中只占一条sql语句的大小视图的表也叫做基表
//创建视图业主类型为1 的业主信息
create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid1
//通过视图修改数据
update view_owners1 set name周也 where id4 4.带检查约束的视图with check option //根据地址表T_ADDRESS创建视图VIEW_ADDRESS2,内容为区域ID为2的记录
create or replace view view_address2 as
select * from T_ADDRESS where areaid2
with check option
//执行更新语句会报错ORA-01402 视图的条件是areaid2 这个条件不能改
update view_address2 set areaid1 where id45.只读视图的创建与使用只能查不能改WITH READ ONLY //将上边的视图修改为只读视图
create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid1
with read only
//执行更新语句会报错ORA-01733只读视图不允许修改
update view_owners1 set name邢道荣 where id4 6.创建带错误的视图-工具的视图上会有个小红叉 //创建视图但视图的SQL语句所设计的表并不存在ORA-00942
create or replace view view_TEMP as
select * from T_TEMP (错误语句)
//有时我们创建视图时的表可能并不存在但是以后可能会存在 FORCE
create or replace FORCE view view_TEMP as
select * from T_TEMP7.复杂视图-视图的 SQL 语句中有聚合函数或多表关联查询 多表关联复杂视图-视图的每一列都是要有名的 //创建视图查询显示业主编号业主名称业主类型名称
create or replace view view_owners as
select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeidot.id
//查询
select * from view_owners
//修改数据-基表数据也会修改
update view_owners set 业主名称范小冰 where 业主编号1;
//所需改的列不属于键保留表的列ORA-01779
update view_owners set 业主类型普通居民 where 业主编号1;
//多表关联的事务中存在键保留表多表关联的视图中存在某一个或多个基表是键保留表键保留表的数据是可以修改的(若字段属于键保留表就可以修改不是则不能修改)键保留表就是把主键保留下来的那个表(视图的主键来自于哪一张表该表就是键保留表)只读视图没有键保留表的概念(因为不管是否为键保留表都不能修改)
该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说表的键值在一个连接视图中也是键值那么就称这个表为键保留表。在我们这个例子中视图中存在两个表业主表T_OWNERS和业主类型T_OWNERTYPE, 其中 T_OWNERS 表就是键保留表因为 T_OWNERS 的主键也是作为视图的主键。键保留表的字段是可以更新的而非键保留表是不能更新的。分组聚合统计复杂视图-没有键保留表(不能修改数据) //创建视图按年月统计水费金额 sum(money)不算列名需要起别名ORA-00998
create view view_accountsum as
select year,month,sum(money) moneysum
from T_ACCOUNT
group by year,month
order by year,month
//聚合函数没有键保留表所以无法执行 update ORA-01732
update view_accountsum set money100 where year2012 and month032.物化视图-真实存在的表建立副本-提高性能 视图是一个虚拟表也可以认为是一条语句基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询可以将这个查询结果集存储到一个物化视图也叫实体化视图可以理解为一张表。 物化视图与普通的视图相比的区别是物化视图是建立的副本它类似于一张表需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的物化视图相当于查单表(比需要多表查询的视图速度快)提高查询性能但占用空间。基表就是物化视图所涉及的表 要求数据实时性强设置自动刷新若只是想每天抓两个快照不需要最准确的数据可以使用手动刷新(日报表每晚12点刷一下)
//物化视图在Materialized views文件夹中(定义) 物化视图一般用MV_开头
//建立物化视图后会在tables中形成一张对应的表(定义对应的表)两处都可通过工具查询数据,删除物化视图对应的表也会被删除删除物化视图语句如下
drop materialized view MV_address1
//创建语法 METERIALIZED-实体化 IMMEDIATE-立即 DEFERRED-延迟
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 DEFERRED 则在创建时不生成数据以后根据需要再生成数据。
默认为 BUILD IMMEDIATE。
刷新REFRESH指当基表发生了 DML 操作后物化视图何时采用哪种
方式和基表进行同步。
REFRESH 后跟着指定的刷新方法有三种FAST、COMPLETE、FORCE。
FAST(快速刷新)刷新采用增量刷新只刷新自上次刷新以后进行的修改。
数据表新增数据会产生物化视图日志记录下增删改的行为通过日志更新物化视图COMPLETE(完整刷新 完全刷新)刷新对整个物化视图进行完全的刷新。
数据表新增数据会将原来的视图干掉再重新执行查询语句形成物化视图 FORCE方式(自动选择)则Oracle 在刷新时会去判断是否可以进行快速刷新如果可以则采用 FAST 方式否则采用COMPLETE的方式。FORCE 是默认的方式。刷新的模式有两种ON DEMAND 和 ON COMMIT。
ON DEMAND 指需要手动触发命令刷新物化视图默认。
ON COMMIT 指在基表发生COMMIT 操作(提交操作)时自动刷新物化视图。1.创建手动刷新的物化视图-提交基表的时候不会自动更新数据
//查询地址 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.areaidar.id
//查询
select * from mv_address;
//向基表地址表T_ADDRESS中插入一条新记录
insert into t_address values(8,宏福苑小区,1,1);
//再次查询,基表中有新插入的数据,物化视图中没有。需通过语句PL/SQL,手动刷新物化视图,因为默认ON DEMAND故需要手动刷新(执行下列语句进行刷新)
select * from mv_address;
//引号中是物化视图名DBMS_MVIEW.refresh是oracle内置的存储过程C是完全刷新F是快速刷新
begin
DBMS_MVIEW.refresh(MV_ADDRESS,C);
end;
//或者通过下面的命令手动刷新物化视图 注意此语句需要在命令窗口中执行。工具中的Command Window窗口
EXEC DBMS_MVIEW.refresh(MV_ADDRESS,C);
//DBMS_MVIEW.refresh 实际上是系统内置的存储过程2.创建自动刷新的物化视图-每次基表刷新自动更新物化视图
//查地址ID地址名称和所属区域名称 多出refresh on commit字段
//基表发生commit操作自动刷新物化视图
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.areaidar.id
//插入基表
insert into t_address values(8,宏福苑小区,1,1);
//查询物化视图自动更新
select * from mv_address;3.创建时不生成数据的物化视图-build deferred
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.areaidar.id;
//查询视图 此时没有数据
select * from mv_address3
//对基表插数据视图中仍没有数据这种视图第一次必须通过PLSQL生成数据之后可以自动刷新
insert into t_address values(8,宏福苑小区,1,1);
//生成数据PLSQL
begin
DBMS_MVIEW.refresh(MV_ADDRESS3,C);
end;
//查询视图由于我们创建时指定的 on commit ,所以在修改数据后能立刻看到最新数据无须再次执行 refresh
select * from mv_address34.创建增量刷新的物化视图-refresh fast
//创建增量刷新的物化视图必须首先创建物化视图日志
//物化视图日志相当于一张表记录基表发生了那些变化(增删改)用这些记录去更新物化视图 on后面是需要创建物化视图日志的表名 with后面是根据什么进行刷新可以是主键rowid或其他
create materialized view log on t_address with rowid
create materialized view log on t_area with rowid
//创建的物化视图日志名称为 MLOG$_表名称(开头)在tables中可以看到当基表发生增删改时表中出现数据基于两个表创建的物化视图两张表都要创建日志
//创建物化视图的语句中必须有基表的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.areaidar.id;
//此时可以查询出物化视图数据
select * from mv_address4
//插入一条数据
insert into t_address values(9,男士小区,1,1);
commit;
//此时物化视图中没有数据因为设置了手动刷新这样可以看到物化视图中的内容若设置自动刷新物化视图是看不到内容的(增加日志内容后马上就刷新掉看不到过程)
//此时可以看到物化视图日志
M_ROWSS:表示rowid显示修改了那一条数据根据他修改物化视图
SNAPTIME$$用于表示刷新时间。
DMLTYPE$$数据操作语言用于表示 DML 操作类型I 表示 INSERTD 表示 DELETEU表示 UPDATE。
OLD_NEW$$用于表示这个值是新值还是旧值。NEW表示新值OLD表示旧值U 表示 UPDATE 操作。插入为新删除为旧修改是一个新一个旧(两个)
CHANGE_VECTOR$$二进制类型表示修改矢量用来表示被修改的是哪个或哪几个字段。此列是 RAW 类型(小的二进制类型)其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。插入操作显示为FE, 删除显示为OO 更新操作则根据更新字段的位置而显示不同的值。
//当我们手动刷新物化视图后物化视图日志被清空物化视图更新。
begin
DBMS_MVIEW.refresh(MV_ADDRESS4,C);
end;
注意创建增量刷新的物化视图必须
1. 创建物化视图中涉及表的物化视图日志。
2. 在查询语句中必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )3.序列 序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象(产生连续数字的数据库对象)oracle表的字段没有自动增长的特性对应的oracle提供了序列独立于表的一个数据库对象序列产生数字表可以使用这个数字
1.简单序列
//简单序列 sequence-序列 名字最好用seq_开头 在Sequences文件夹中找
create sequence 序列名称
//通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
注意在刚建立序列后无法提取当前值只有先提取下一个值时才能再次提取当前值。
//查询序列的下一个值每执行一次序列走一步取一次就走一次
select 序列名称.nextval from dual
//查询序列的当前值,序列数字不会继续往下走
select 序列名称.currval from dual2.复杂序列-按特有规则增长的数字
//语法
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}];//分配并存入到内存中(序列实际存在磁盘上)开启缓存后可在内存中获取序列减少访问磁盘次数cache 10一次拿10个值内存用完再去磁盘拿NOCACHE表示不走内存每次都从磁盘拿(默认值cache 20)若拿20后序列号走到10时服务器重启此时再抓序列应为21此时缓存中的空间浪费了内存会重新抓20个此刻会出现断号(重启服务器和oracle都可能出现断号)3.案例
//语句需要加分号不然打点调用不出来
//有最大值20的非循环序列
create sequence seq_test1
maxvalue 20
//执行查询时序列号不断自增,当超过最大值时会报错ORA-08004
select seq_test1.nextval from dual;//增长值为10,从10开始最大值100
create sequence seq_test2
increment by 10
start with 10
maxvalue 100;//最小值5
create sequence seq_test3
increment by 10
start with 10
minvalue 5
maxvalue 100;//循环序列,ORA-04013(缓存必须小于一次循环)该限制只在循环下有
//缓存默认20次在该序列中会取200个(10*20)增长值是10起始10最大为210
create sequence seq_test4
increment by 10
start with 10
minvalue 10
maxvalue 210 //多一个数也能通过201但少于等于都不行
cycle; //一次缓存数为缓存值*增长值//非循环序列可以缓存数超出oracle会自动适配
create sequence seq_test5
increment by 10
start with 10
minvalue 10
maxvalue 100 //多一个数也能通过201但少于等于都不行
cache 20; //一次缓存数为缓存值*增长值4.修改和删除序列
//使用 ALTER SEQUENCE 语句修改序列不能更改序列的 START WITH 参数
//修改序列最大值为5000循环序列(非循环用no cycle)起始参数不能改
ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;
//删除序列
DROP SEQUENCE 序列名称;4.同义词-别名-可做兼容性
1.创建同义词的具体语法 SYNONYM-同义词在Synonyms文件夹中看 create [public] SYNONYM synooym for object; 其中 synonym 表示要创建的同义词的名称object 表示表视图序列等我们要对哪个对象起别名。加public是公有同义词(大家都可以用)不加是私有同义词(只有当前用户能使用该同义词system不能直接使用(dba权限)需要通过用户打点调用)
2.案例-varchar是varchar2的同义词(内置类型)类型也是对象的一种
//为表T_OWNERS 创建(私有)同义词 名称为OWNERS(相当于起别名)
create synonym OWNERS for T_OWNERS;
//与表的使用方式相同
select * from OWNERS
//为表 T_OWNERS 创建( 公有 )同义词 名称为 OWNERS2
create public synonym OWNERS2 for T_OWNERS;
//以另外的用户登陆也可以使用公有同义词
select * from OWNERS25.索引-为查询服务-b树 索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。(索引查询有0秒的时候)索引实际上也是通过寻找物理地址查询数据(但是他找的快) 索引是需要占据存储空间的也可以理解为是一种特殊的数据。形式类似于下图的一棵“树”而树的节点存储的就是每条记录的物理地址也就是我们提到的伪列ROWID根据rowid查比主键更快(跟索引差不多)oracle内部有缓存机制所以有时查询时长不同(可多次查询取时长)
1.普通索引-建表指定主键索引自动生成(基于主键)
//语法 在Indexes文件夹中可看到索引基于何表何列创建
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 万条记录
BEGINFOR i in 1..1000000loopINSERT 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 ID765432;
SELECT * from T_INDEXTEST where NAMEAA765432;
//根据 name 查询所用的时间会比根据 id 查询所用的时间要短2.唯一索引-unique某一列不能重复时可创建同时创建唯一约束 在某个表某个列创建索引而这列的值是不会重复的。这是我们可以创建唯一索引。
//语法 性能高于普通索引 唯一约束-无法插入相同的值 列有重复记录则无法创建
create unique index 索引名称 on 表名(列名);
//在业主表的水表编号一列创建唯一索引
create unique index index_owners_watermeter on
T_OWNERS(watermeter);3.复合索引-索引的字段顺序与查询的字段顺序要一致 要对多列进行查询例对这两列建立两个索引要查两棵树查询性能不一定高故建立复合索引也就是基于两个以上的列建立一个索引
//语法
create index 索引名称 on 表名(列名,列名.....);
//根据地址和门牌号对学员表创建索引
create index owners_index_ah
on T_OWNERS(addressid,housenumber);4.反向键索引-reverse 应用场景当某个字段的值为连续增长的值如果构建标准索引会形成歪脖子树。这样会增加查询的层数性能会下降。建立反向键索引可以使索引的值变得不规则从而使索引树能够均匀分布将原来10进制数据转二进制后进行反向键运算(0100变0010)再转回10进制数此时数值就会变得随机一些方便分布降低层数
//语法 建立索引后加reverse
create index 索引名称 on 表名(列名) reverse;5.位图索引-非b树bitmap将对应位图上的点取出进行查找(比树快) 使用场景位图索引适合创建在低基数列上 (可能性少例:男女每个可能性都要建立一张位图存入对应id会占用很大空间) 位图索引不直接存储 ROWID而是存储字节位到 ROWID 的映射 优点减少响应时间节省空间占用
//语法 只能用等号(等于某张位图)不能范围查询不能走索引
create bitmap index 索引名称 on 表名(列名);
//我们在 T_owners 表的 ownertypeid 列上建立位图索引
create bitmap index index_owners_typeid
on T_OWNERS(ownertypeid)
