Oracle学习(五) — 视图、序列、同义词、索引「建议收藏」

1、视图

1.1、什么是视图

• 视图就是一个虚拟表，实体表的映射。

• 什么时候使用视图
  • 在开发中，有一些表结构是不希望过多的人去接触，就把实体表映射为一个视图。(表结构简化)
  • 在项目过程中，程序员主要关注编码的性能、业务分析这方面。对于一些复杂的SQL语句，设计人员会提前把这些语句封装到一个视图中，供程序员去调用

1.2、视图的基本操作

1.2.1、简化版创建

-- 语法
create view 视图名称 as 查询语句;

代码100分

1.2.2、删除

代码100分-- 语法
drop view 视图名称;

1.2.3、具体操作：CURD

-- 0.准备数据，查询业主表
select * from t_owners;
-- 1 创建视图
create view view_owners 
as select * from t_owners;

-- 2 删除视图
drop view view_owners;

-- 3 查询 -- 与查询表语法相同
select * from view_owners;

-- 4 增删改
insert into view_owners values(11,"美廉美超市666",5,"4-2","30423",to_date("2016-10-12","yyyy-MM-dd"),3 );
commit;

update view_owners set name = "大润发" where id = 11;
commit;

delete from view_owners where id = 11;
commit;

1.3、含有条件(检测)的视图

• 给视图，添加约束检查
  • replace ，如果视图已经存在，将覆盖(替换)
  • with check option ，进行有效数据检查（添加/更新数据,必须能查询出来）

代码100分-- 含条件的视图
-- 0 准备工作
select * from t_owners where ownertypeid = 1

-- 1 创建视图
create or replace view view_owner1 
as select * from t_owners where ownertypeid = 1
with check option

-- 2 查询
select * from view_owner1;

-- 3 添加数据
-- 执行不成功，提供违反约束
insert into view_owner1 values(11,"美廉美超市666",5,"4-2","30423",to_date("2016-10-12","yyyy-MM-dd"),3 );
commit;
-- 执行成功
insert into view_owner1 values(11,"美廉美超市666",5,"4-2","30423",to_date("2016-10-12","yyyy-MM-dd"),1 );
commit;
delete from t_owners where id = 11;
commit;

1.4、强制创建(了解)

• force 强制创建视图，无论查询语句是否有误

--- 强制创建
-- 0 准备工作，查询表数据，表不存在
select * from temp;

-- 1 创建视图
create force view view_owners2
as select * from temp;

1.5、只读视图

• with read only ，要求视图必须只读。

-- 只读视图
-- 0.准备数据，查询业主表
select * from t_owners;
-- 1 创建只读视图
create view view_owners3
as select * from t_owners
with read only

-- 2 查询
select * from view_owners3

-- 3 添加 --不允许
insert into view_owners3 values(11,"美廉美超市666",5,"4-2","30423",to_date("2016-10-12","yyyy-MM-dd"),1 );
commit;

1.6、关联查询

1.6.1、名词解释：键保留表、非键保留表

• 特点：在关联查询生成的视图中，只能添加/修改“键保留表”中的数据。

1.6.2、SQL编写

-- 关联查询（复杂视图）
-- 0 准备sql 
select ow.id 业主编号,ow.name 业主名称,ot.name 类型名称 from t_owners ow
inner join t_ownertype ot on ow.ownertypeid = ot.id
-- 1 创建视图
create or replace view view_owners4
as select ow.id 业主编号,ow.name 业主名称,ot.name 类型名称 from t_owners ow
inner join t_ownertype ot on ow.ownertypeid = ot.id
-- 2 查询数据
select * from view_owners4
-- 3 更新数据
-- 3.1 键保留表
update view_owners4 set 业主名称 = "王小强" where 业主编号 = 2;
commit;
-- 3.2 非键保留表 （非键值保存表）--执行不成功
update view_owners4 set 类型名称 = "行政事业单位" where 业主编号 = 2;
commit;

1.7、总结

create [or replace] [force] view 视图名称
as 查询语句
[with check option]
[with read only]

-- or replace 覆盖
-- force 强制
-- with check option 有效数据检查
-- with read only 只读视图

2、序列

2.1、什么是序列

• 序列：用于生产唯一数字的数据库对象
  • 相当于MySQL自动增长列(auto_increment)
• 序列用途：在表中维护主键的自动增长。

2.2、语法

• 简化版：

create sequence 序列号;

• 完整版：

create sequence 序列号
start with 起始值
increment by 步长
maxvalue 最大值
minvalue 最小值

2.3、创建

-- 简化版
create sequence seq_stuno;

-- 完整版
create sequence seq_stuno2
start with 2
increment by 3
maxvalue 20
minvalue 2

2.4、使用

• 查询获得序列值

-- 查询 , nextval 获得下一个序列
select seq_stuno2.nextval from dual;

• 添加自动维护主键

-- 表主键自动维护
create table t_demo(
  id int primary key,
  name varchar2(50)
);
insert into t_demo(id,name) values(seq_stuno.nextval , "张三");
insert into t_demo(id,name) values(seq_stuno.nextval , "李四");
insert into t_demo(id,name) values(seq_stuno.nextval , "王五");
commit;

3、同义词

3.1、什么是同义词

• 同义词就是别名，可以给表、视图等其别名。
• 同义词意义：
  • 同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

3.2、同义词分类

• 私有同义词：只能用户自己访问
• 公有同义词：数据库的所有用户都可以访问

3.3、创建和删除

• 创建

  • 私有同义词

    create synonym 同义词名称 for 表名|视图名
    

  • 公有同义词

    create public synonym 同义词名称 for 表名|视图名
    

• 删除

  • 私有同义词

    drop synonym 同义词名称
    

  • 公有同义词

    drop public synonym 同义词名称
    

3.4、演示

• 使用同义词查询时，需要使用2个用户进行测试，公共同义词可以在两个用户之间共享表。

--- 同义词
-- 创建私有同义词
create synonym owners for t_owners;
-- 创建完成后，可以查询到数据
select * from owners;

-- 创建共有的同义词
create public synonym owners2 for t_owners;
select * from owners2;

--- 删除
drop synonym owners;
drop public synonym owners2

4、索引

4.1、什么是索引

• 索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。
• 索引目的：提高数据访问性能。
• 索引需要物理储存。使用空间换时间。

4.2、索引分类

• 普通索引：使用关键字 index 声明 一个列
• 唯一索引：使用关键字 unique 声明的列
• 复合索引：使用关键字 index 声明 多个列

4.3、普通索引

4.3.1、语法

create index 索引名称 on 表名(列名);

4.3.2、准备数据

-- 1 准备数据
-- 1.1 创建表
drop table t_indextest;
create table t_indextest(
  id number,
  name varchar2(30)
);
-- 1.2 批量导入100万条数据
begin
  for i in 1..1000000
  loop
    insert into t_indextest(id,name) values(i, "AA" || i);
  end loop;
  commit;
end;

4.3.3、测试性能

-- 2 测试
-- 2.1 查询(id,name)  -- 两个结果基本一样
select * from t_indextest where id = 765432;
select * from t_indextest where name = "AA765432";
-- 2.2 添加索引
create index t_indextest_name on t_indextest(name);
-- 2.3 再次查询   -- 有索引快
select * from t_indextest where id = 765432;
select * from t_indextest where name = "AA765432";

• 测试id查询性能

--- 执行计划
explain plan for select * from t_indextest where id = 765432;
select * from table(dbms_xplan.display());

• 测试name查询性能(已经添加索引)

explain plan for select * from t_indextest where name = "AA765432";
select * from table(dbms_xplan.display());

4.4、唯一索引(了解)

• 如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这时我们可以创建唯一索引。

create unique index 索引名称 on 表名(列名);

4.5、复合索引(了解)

• 我们经常要对某几列进行查询，可以建立复合索引，也就是基于两个以上的列建立一个索引

create index 索引名称 on 表名(列名,列名2,.....);