oracle数据库基础：注释、序列、视图、索引

1、序列

概述

序列（sequence）是一种数据库对象，作用是帮助表去创建自动增长的主键。序列是oracle数据库中所持有的的对象，其他数据库中是没有的。

创建

创建序列的默认语法

create sequence 序列名;

和创建表很相像

其完整额创建方式如下：

create sequence 序列名
[INCREAMENT BY n]
[START WITH n] 
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE| NOCYCLE }]
[{CACHE n | NOCACHE}]

说明：
（1）INCREMENT BY，用于定义序列的步长，如果省略，则默认为1
（2）START WITH，定义序列的初始值（即产生的第一个值），默认为1
（3）MAXVALUE，定义序列能产生的最大值。NOMAXVALUE是默认值，代表没有最大值定义
（4）MINVALUE，定义序列能产生的最小值。NOMINVALUE是默认值，代表没有最小值定义
（5）CYCLE和NOCYCLE表示当序列的值达到限制值后是否循环
（6）CACHE定义存放序列值的缓冲区的大小，默认为20，NOCACHE表示不对序列进行内存缓冲

序列操作

查看序列的当前值

select 序列名.currval from dual;

查看序列下一个值，

select 序列名.nextval from dual;

注意，在创建好一个序列之后，不能先使用currval，因为当前还没有只，需要先使用nextval之后才可以使用currval

删除序列

drop sequence 序列名;

创建序列并设置属性

create sequence test_seq
increment by 5
start with 10
maxvalue 100
minvalue 10
cycle
nocache;

---

假设有表如下：

create table t_user(
	id number constraint user_id_primary key,
	name varchar2(100),
	salary number
);

接着创建序列如下：

create sequence t_user_seq;

通过序列自动生成id值

insert into t_user(id,name,salary) value (t_user_seq.nextval,'tom',2000);

---

查询当前用户的序列

select sequence-name
from user_sequences;

2、视图

概述

视图（view）是一种数据库对象，简单来说就是提取一张表或者多张表的数据生成的一个映射。操作视图从而达到操作原表的效果，方便数据管理和安全操作，视图的主要作用的隐藏表中的重要数据，代替比较长的sql语句。

分类

1、简单视图
视图所代表的的sql中如果没有group by语句，没有组函数，查询的只有一张表，那么这样的视图就是简单视图

2、复杂视图
视图所代表的sql中如果有group by语句，或者有组函数，或者查询的是多张表，那么这样的视图就是复杂视图

区别：
通过简单视图可以修改原表中的数据，但不能使用复杂视图修改原表中的数据。

创建

创建视图，需要用户有创建视图的权限，如果之前没有，可以专门给用户赋权

grant create view to tom;

语法如下

create or replace view 视图名字
as
sql语句

---

简单视图

例如：假设有如下一个表

create table t_user
as
select id,last_name,salary
from s_emp;

通过这张表创建一个简单视图

create or replace view v_test
as
select id,last_name,salary
from t_user
where id<10;

---

可以通过简单视图来对原来的表进行数据的删除、更新和插入

delete from v_test where id = 6;

update v_test set last_name = 'zhangsan' wehre id = 0;

insert into v_test(id,last_name,salary) values (6,'tom1',3000);

select* from v_test;

---

查看视图内容

select * from v_test

---

with read only语句
在创建视图的时候，可以加上with read only语句，表示只能通过该视图进行查询数据，不能修改

create or replace view v_test
as 
select *
from t_user
where id<10
with read only;

这个视图v_test将来只能查询,不能进行修改，此时再使用上面的DML进行操作数据就会报错

---

with check option语句
在创建视图的时候，可以加上with check option语句，表示通过视图进行的修改，那么也必须可以通过这个视图能够显示出来，否则就操作失败。

例如：

create table t_user(
	id number constraint user_id_pk primary key,
	name varchar2(100),
	salary number
);

insert into t_user values(1,'tom1',1000);
insert into t_user values(2,'tom2',2000);

创建视图（id=2的数据的视图），加上with check option语句，

create or replace view v_test
as
select id,name,salary
from t_user
where id = 2
with check option;

查看视图中数据

select * from v_test

可以查看

插入数据

insert into t_test values(3,'tom3',3000);

失败，这个操作通过视图无法显示

更新数据

update v_test
set name = 'lily'
where id = 1;

失败，这个操作视图显示不出来

更新数据

update v_test
set name = 'lily'
where id=2;

更新成功

---

复杂视图

create or replace view v_test
as 
select avg(salary) avgSal
from t_user;

复杂视图只用来查询，不可修改

select * from v_test;

视图删除

drop view 视图名

3、索引

概述

1. 类似书的目录结构
2. oracle的索引1是一种对象，是与表关联的可选对象，能提高SQL查询语句的速度
3. 索引直接指向包含所查询值的行的位置，减少磁盘I/O
4. 索引和表是相互独立的物理结构
5. Oracle自动使用并维护索引，插入、删除、更新表后都会自动更新索引

原理：假设一个表中有name字段，假设要查找name='tom’的数据，但是数据可能会有多条

1. 如果没有索引，查找这个记录时，需要搜索表中所有的记录，因为不能保证只有一个tom，那么就必须将表中数据全部搜索一遍
2. 如果在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值在什么位置按照一定的规则进行排列，然后构建索引条目，并存储起来，在查询name为tom时，可以直接查找该数据所在的对应地方。
   创建了索引并不一定就会使用，因为oracle在自动统计表的信息后，会决定是否使用索引，表中数据很少时，使用全表扫描速度已经很快了，那么就没有必要使用索引了。

索引的创建

创建索引有俩种情况：

1. 自动创建索引：当在表中指定了primary Key或者unique约束时，会自动创建唯一值索引。
2. 用户创建索引：用户可以创建非唯一值索引以提高在访问数据时的效率。

语法：

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

查看当前用户创建的索引

select index_name from user_indexes;

注意，在某一个列上加入上了索引，那么也只有在数据量很大的时候，才能有所体现出这个查询的效率，索引一旦建立成功，那么之后这个索引就由数据库来管理，我们自己是控制不了的

给某列创建索引的原则：

1. 列经常作为where子句的限定条件或者作为连接条件
2. 列包含的数据量很大，并且很多非空的值。
3. 在经常需要order by,group by,distinct 列上创建索引，可以利用索引加快排序查询时间；
4. 索引不是越多越好，不是索引越多越能加速查找。
5. 要建立索引的表不经常进行修改操作

索引的删除

drop index 索引名;

索引的种类

1. 唯一值索引 unique index
   创建索引时候 列的约束时唯一约束或者时主键约束
2. 非唯一值索引 nounique index
   创建索引时候，列的约束时不是唯一约束或者不是主键约束
3. 单行索引 single index
4. 多行索引 concatenated index

索引的结构

常见结构有
1、B-tree
默认索引结构就是这个,特点如下：

1. 适合大量的增、删、改
2. 不能用包含OR操作符的查询
3. 适合唯一值较多的列
4. 典型的树状结构

2、位图索引
数据基数比较少的时候，较适合建位图索引，特点如下：

1. 非常适合OR操作符的查询
2. 做UPDATE代价非常高，因为oracle要根据表的每次修改来更新索引
   

需要查看当前数据库是否支持这种索引结构，如下：查询是否具备了bitmap索引的功能

如果value值为false表示，Oracle该版本不具有bitmap索引功能

3、反序索引
反向索引是B-tree索引的一个分支，主要是在创建索引时，针对索引列的索引键值进行字节反转。它可以将索引键值分散到不用的节点中。

4、函数索引
经常对某个字段做查询的时候，并且是带函数操作的，那么此时函数索引就可以发挥作用，提高检索速度。

4、注释

在表创建完成之后，可以使用comment关键字，给表或者列添加注释，将来这个注释还可以查询出来。
例如：给表添加注释

comment on table t_user is '很好';

查看表中注释

select * from user_tab_comments where table_name = upper('t_user');

例如：给列添加注释

comment on column t_user.name is 'good';

查看列中注释

select * from user_col_comments
where comments is not null
and table_name = upper('t_user');