JavaWeb开发知识总结(三)-(oracle_对象)

JavaWeb开发知识总结(Oracle-对象)

1. 视图：Oracle和mysql通用

1.1 视图概述

​ 视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。根据创建视图时给定的条件，视图可以是一个数据表的一部分(简单视图)，也可以是多个基表的联合(复杂视图)，视图的本质就是对sql语句的封装，是对真实表中字段的映射，操作视图中的数据时，都会先执行视图所封装的sql语句。

使用视图的优点：
1. 简化数据操作：视图可以简化用户处理数据的方式，将复杂的查询语句封装到视图中，从视图中处理数据。
2. 着重于特定数据：不必要的数据或敏感数据可以不出现在视图中，将较为敏感的字段不封装到视图中，则通过视图是无法直接访问敏感字段的。
3. 视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限，通过对不同的视图封装不同的字段及创建视图的约束条件来进行权限的控制。
4. 提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

1.2 视图创建

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

简单视图：

​ 简单视图：视图封装的SQL语句是单表，并且SQL语句中没有聚合函数。对视图的操作本质就是对构成视图SQL语句中的基表的操作，可以对视图中的数据进行增删改查操作，数据的查询，删除和数据可以依据主键进行操作；添加和修改时，如果基表中的字段存在外键关联，则要修改或增加的字段的值必须满足外键约束。

--语法：
CREATE [OR REPLACE] VIEW view_name
AS subquery
--案例：
--简单视图，单表数据，不能有聚合函数
--创建简单视图
create view view_owners as
select * from t_owners where ownertypeid = 1
--查询简单视图
select * from view_owners;--可以简单查询
select count(*) from view_owners;--可以使用聚合函数
--修改简单视图
--视图是对sql语句的逻辑封装，其本质就是一个SQL语句，视图中的字段都是对真实表字段的映射
--修改视图中的数据实际上修改的是真实表的数据
--视图一般用在查询语句中，在增删改时要谨慎使用
update view_owners set name='范冰' where id=1;commit;
--修改视图中条件字段值，可以修改
update view_owners set ownertypeid = 2 where id = 1;commit;
--增加视图中的数据
insert into view_owners values(18,'java',1,'123','456',sysdate,1);--可以添加数据
--删除视图中的数据
delete from view_owners where id = 18; -- 可以删除数据

带检查约束的视图：

​ 带检查约束的视图：检查约束是对视图封装SQL语句中条件的约束，SQL语句中的约束条件就是创建视图的条件，视图约束的是封装的SQL中的条件中的字段值不能修改，其他的字段值可以修改。并且该约束只针对insert和update语句有效，对delete语句无效。

--语法：
CREATE [OR REPLACE] VIEW view_name
AS subquery
WITH CHECK OPTION
--案例：
--带检查约束的视图：只针对insert和update语句有效，对delete无效
create view view_address4 as
select * from t_address where areaid = 4
with check option
--查询视图数据
select * from view_address4;
--修改创建视图的条件,无法修改，因为创建视图的条件是areaid=4
--下面的语句会报错,带检查的视图不允许修改创建视图的条件
update view_address4 set areaid = 3 where id = 1;

只读视图：

​ 只读视图：只允许查询数据，不允许修改数据，有修改语句时会报错。

--语法：
CREATE [OR REPLACE] VIEW view_name
AS subquery
WITH READ ONLY
--案例：
--只读视图，只能查询，不能增删改
create view view_owners2 as
select * from t_owners where ownertypeid=1
with read only;
--修改只读视图,不能修改，只能查询数据
update view_owners2 set name ='somnus' where id = 1; -- 语句会报错

带错误的视图：了解

​ 带错误的视图：就是封装的SQL语句中的基表是不存在的，这个表可能在程序动态生成，则此时需要创建带错误的视图，一般的视图创建时SQL中的基表必须存在。

--语法：
CREATE [OR REPLACE] FORCE VIEW view_name
AS subquery
--案例：
--带错误的视图,基表可以不用存在，以后可能存在的表，或在程序中动态生成的表
create force view view_somnus as
select * from somnus;--somnus表时不存在的

复杂视图：

​ 复杂视图：视图封装的SQL语句中包含多表关联或聚合函数；SQL语句出现的基表中，如果基表中的主键作为视图中的字段该基表称为键保留表(主键保留到视图中)，视图中只有是键保留表中字段的的字段值才能修改，其他非键保留表中的数据不能修改(原因是：修改一张表中的数据需要由主键唯一确定一条数据，而非键保留表在视图中没有出现该基表的主键，则无法确定要修改的字段在非键保留表中的位置，则不能修改)，如果视图中都是非键保留表，则视图相当于是只读视图，任何字段的数据均不能修改。

--1.多表关联创建视图
--注意：如果视图封装的SQL语句的结果中出现相同的字段名时，需要为字段名取不同的别名(视图和表类似，不能出现相同的字段名)
create view view_owners3 as
--下面语句无法创建成功，创建的视图表中不能出现名称相同的字段名
--select tn.*,tw.* from t_owners tn, t_ownertype tw where tn.ownertypeid = tw.id
select tn.id,tn.name oname,tw.name tname from t_owners tn, t_ownertype tw where tn.ownertypeid = tw.id
--查询多表关联视图，和基本表的查询相同
select * from view_owners3;
select * from view_owners3 where id = 2;
--修改多表关联的数据
--键保留表：将主键保留到视图中的基表，只有键保留表中的字段才能修改
--修改键保留表中的字段，可以修改
update view_owners3 set oname = 'somnus' where id = 2;commit;
--修改非键保留表中的字段，不可以修改
update view_owners3 set tname = 'somnus' where id = 2;commit; --报错

--2.聚合函数的视图
--注意：聚合函数的结果必须有别名，聚合函数的视图不能修改数据，相当于是只读视图(数据是统计出来的，修改值无法对应到具体的基表中的数据)
create view view_accountsum as
--select year,month,sum(money) 聚合函数结果不能作为视图中的列名，必须有别名
select year,month,sum(money) money
from t_account
group by year,month -- 分组可以有多个字段
order by year,month;-- 排序可以是多个的字段的组合
--查询聚合函数视图中的数据
select * from view_accountsum;
select * from view_accountsum where id = 1; --有条件的查询
--修改视图中数据
--聚合函数视图中数据不能修改，只能查询，没有键保留表
update view_accountsum set money = 1000 where year='2016' and month='01'; -- 报错

1.3 删除视图

DROP VIEW 视图名称;

2. 物化视图：Oracle特有

​ 视图是一个虚拟表(封装的是SQL语句)，基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。
​ 物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。

视图和物化视图的区别：
​ 视图：是虚拟表，本质是对SQL语句的封装，占用的只是一条SQL语句的存储空间，其映射的是真实的表；视图的作用是简化开发；
​ 物化视图：是真实存在的表，需要占用存储空间；物化视图的作用的提高数据库的性能。

创建物化视图：

--语法：
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
--参数说明：
BUILD IMMEDIATE：是在创建物化视图的时候就生成数据，创建物化视图的默认值
BUILD DEFERRED：则在创建时不生成数据，以后根据需要再生成数据。
REFRESH(刷新)：指当视图封装的SQL语句中的基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。
REFRESH后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。
REFRESH FAST：刷新采用增量刷新，只刷新自上次刷新以后进行的修改。
REFRESH COMPLETE：刷新对整个物化视图进行完全的刷新。
REFRESH FORCE：如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。FORCE是默认的方式。
刷新的模式有两种：ON DEMAND和ON COMMIT。
ON DEMAND 指需要手动刷新物化视图（默认）。
ON COMMIT 指在基表发生COMMIT操作时自动刷新。
START WITH (start_time) NEXT (next_time)：是指定刷新的时间。

创建手动刷新的物化视图：

--手动刷新的物化视图：
--创建手动刷新的物化视图,创建物化视图会自动为该视图创建一张表
create materialized view mv_address1 as
select ad.id,ad.name aname,at.name rname from t_address ad,t_area at
where ad.areaid = at.id;
--查询物化视图
select * from mv_address1;
--向物化视图的基表中添加数据
insert into t_address values(14,'双流2',4,4);commit;
--手动刷新视图数据,需要手动执行PL/SQL语句进行刷新
--方式1
begin --DBMS_MVIEW.refresh 实际上是系统内置的存储过程
  DBMS_MVIEW.refresh('MV_ADDRESS1','c');--第一个参数：刷新视图名称，第二个参数：刷新模式(C代表是完全刷新)
end;
--方式2
EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C'); --注意：此语句需要在命令窗口中执行。

创建自动刷新的物化视图：

--创建自动刷新的物化视图：对基表数据的修改会立即刷新到物化视图中
create materialized view mv_address2 
refresh --默认刷新模式是force,由于没有设置快速刷新,则会使用完全刷新
on commit --设定为自动刷新数据
as
select ad.id,ad.name aname,at.name rname from t_address ad,t_area at
where ad.areaid = at.id;
--查询物化视图
select * from mv_address2;
--向物化视图的基表中添加数据
insert into t_address values(13,'双流3',4,4);commit;
--案例：
--创建物化视图（手动刷新），查询列出各部门的部门名和部门经理名字。并编写手动刷新命令。
--手动刷新可以使用通用的左外连接
create materialized view view_emp_dept3
refresh
on demand
as 
select d.deptno,e.job from dept d
left join emp e on d.deptno=e.deptno
group by d.deptno,e.job
having e.job='MANAGER' or e.job is null
--自动刷新只能使用Oracle特有的左外连接的方式
create materialized view view_emp_dept7
refresh
on commit
as 
select d.deptno,e.job from dept d,emp e where d.deptno=e.deptno(+)
and (e.job='MANAGER' or e.job is null)
--group by d.deptno,e.job --加上此句报错
--having e.job='MANAGER' or e.job is null --此代码报错

创建创建时不生产数据的物化视图：

--创建不生产数据的物化视图
create materialized view mv_address3
build deferred --创建时不产生数据
refresh --默认的刷新模式
on commit --刷新模式是有执行commit语句时刷新
as
select ad.id,ad.name aname,at.name rname from t_address ad,t_area at
where ad.areaid = at.id;
--查询物化视图
select * from mv_address3;
--向物化视图的基表中添加数据
insert into t_address values(15,'双流4',4,4); commit;
--第一次必须先使用语句进行,后续的就不需要进行手动刷新，数据会自动刷新
begin
  dbms_mview.refresh('mv_address3','c');
end;
--删除基表中数据
delete from t_address where id = 14;
delete from mv_address3 where id = 15; -- 不能删除

创建增量刷新的物化视图：

--创建增量刷新物化视图：
--前提是必须创建物化视图日志：日志作用是记录基表发生的变化，用来更新物化视图数据
--视图日志：当基表中的数据发生变化时，会将变化的数据保存在日志中，创建日志名称为MLOG$_表名称
--然后刷新视图数据时，保存在日志中的数据就被更新到视图中，日志中的数据就消失
--如果有多表时：物化视图中有几张基表就需要建立几个日志，并且建立时使用必须是各个基表中rowid
--如果有单表时：物化视图中需要建立1个日志，并且建立的日志中使用必须是基表中的主键(primary key)
--1.创建单表的物化视图,with后的必须是primary key的字符串形式
--创建物化视图日志
create materialized view log on t_owners with primary key;
--创建物化视图
create materialized view mv_test
refresh fast --设置增量刷新模式
on commit -- 设置执行commit语句时自动刷新数据
as select * from t_owners;
--2.创建多表的物化视图,只能在rowid上创建视图日志
--创建物化视图日志
create materialized view log on t_address with rowid;
create materialized view log on t_area with rowid;
--删除物化视图的日志
drop materialized view log on t_owners; -- 删除指定基表上的视图日志
--创建增量更新的物化视图，手动刷新视图
create materialized view mv_address4
refresh fast -- 增量刷新
as --创建的语句中必须将基表的rowid查询出来作为物化视图的字段
select ad.rowid adrowid,at.rowid arrowid,ad.id,ad.name aname,at.name rname from t_address ad,t_area at
where ad.areaid = at.id;
--删除物化视图
drop materialized view mv_test;
--查询物化视图
select * from mv_address4
--向物化视图的基表中添加数据
insert into t_address values(14,'双流4',4,4);commit;
--手动刷新视图
begin
  dbms_mview.refresh('mv_address4','c');
end;

3. 序列：Oracle特有

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

创建简单序列：一般作为表中主键的自动增长

--语法：创建的序列中有两个伪列NEXTVAL和CURRVAL用来获取当前的值和下一个值
create sequence 序列名称;
通过序列的伪列来访问序列的值
NEXTVAL：返回序列的下一个值，没执行一次，序列中值就自动增加1
CURRVA：返回序列的当前值
注意：我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。
--创建简单序列:
create sequence seq_somnus
--查询序列的下个值
select seq_somnus.nextval from dual
--查询序列的当前值
select seq_somnus.currval from dual;

创建复杂序列：了解

--语法：
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_somnus2
maxvalue = 10;
select seq_somnus2.nextval from dual;

删除和修改序列：

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

4. 同义词：Oracle特有

​ 同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。你可以创建公共同义词和私有同义词。其中，公共同义词属于 PUBLIC 特殊用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，只允许特定用户或者有基对象访问权限的用户进行访问。同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给同义词的基对象赋予权限，同义词只是基对象的一个别名。其实：Oracle中数据类型的varchar就是varchar2的同义词。

创建同义词：

--语法：同义词相当于是为表或视图或序列创建的别名，可以通过别名代替表名或视图名或序列名
create [public] SYNONYM synooym for object;
--参数说明：
public：公有的同义词，缺省的是私有的同义词
synonym：表示要创建的同义词的名称
object：表示表，视图，序列等我们要创建同义词的对象的名称。

5. 索引：Oracle和mysql通用

​ 索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ROWID）。数据库会自动为每张表的的主键创建索引，索引一般应用在数据量较大的表中，为查询提高效率，当数据量较小时，使用索引会降低效率。

普通索引：

--语法：为某列创建索引
create index 索引名称 on 表名(列名);
--案例：
--创建测试表
create table test_somnus(
id number,
name varchar2(20));
--向表中插入1000000条数据
begin 
  for x in 1.. 1000000
    loop 
      insert into test_somnus values(x,'somnus'||x);
    end loop;
end;
--创建普通索引,为name列创建索引,
--当表中存储的数据量达到百万级别时，通过name字段查询某条数据的时间较短
create index index_name on test_somnus(name);
--通过id字段查询指定的数据
select * from test_somnus where id = 123456; --用时0.062秒
select * from test_somnus where name = 'somnus123456'; --用时0.031

唯一索引：

--语法：如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可以创建唯一索引。
create unique index 索引名称 on 表名(列名);
--案例：在test_somnus表的name字段上创建唯一性索引
create unique index index_unique_name on test_somnus(name);

复合索引：

​ 如果经常要对某几列进行查询，比如，我们经常要根据学历和性别对学员进行搜索，如果我们对这两列建立两个索引，因为要查两棵树，查询性能不一定高。建立复合索引，也就是基于两个以上的列建立一个索引，可以提高查询性能。

--语法：
create index 索引名称 on 表名(列名,列名.....);
--案例：在test_somnus的两个字段上创建复合索引
create index index_id_name on test_somnus(id,name);

反向键索引：

​ 应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。

--语法：创建反向键索引
create index 索引名称 on 表名(列名) reverse;

位图索引：

​ 使用场景：位图索引适合创建在低基数列上位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射。优点：减少响应时间，节省空间占用。一般创建位图索引的字段是低基数(有限个值)列，如：性别列等。

--语法：
create bitmap index 索引名称 on 表名(列名);