Oracle数据中的序列、索引、视图、事务操作详解以及rowid 和 rownum的简单介绍

1. 序列(sequence)

序列是 Oracle 中特有的对象, 用于生成一个自动递增的数列. 通常被用来作为主键的值.

1.1 创建序列

a) 语法

create sequence seq_name  
[increment by n  
start with n  
maxvalue n|nomaxvalue // 10^27 or -1  
minvalue n|no minvalue  
cycle|nocycle  
cache n|nocache] 

解释： increment by, 代表每次增长的步长, 默认是 1, 可以是负数, 表示每次递减; start with, 从哪个值开始, 默认是 1; maxvalue, 序 列 能 到 达 的 最 大 值 , 默 认 值 是 nomaxvalue, 此时正数最大值是 10^27, 负数最大值是-1; minvalue, 序 列 能 到 达 的 最 小 值 , 默 认 值 是nominvalue, 此时正数的最小值是 1, 负数的最小值是-10^26; cycle|nocycle, 表示是否循环. 如果是 cycle, 达到最大值时会重新从头开始, 如果是 nocycle, 最大值后会报错. cache n|nocache, 表示高速缓存, 可以优化序列, 缓存的默认值是 20. nocache 表示没有缓存.

b) 创建学生序列

create sequence seq_student; 

1.2 序列的使用

1.2.1 nextval(序列的下一个值)

查看序列的下一个值

select seq_student.nextval from dual; 

1.2.2 currval(序列的当前值)

查看序列的当前值

select seq_student.currval from dual; 

1.2.3 在插入数据时使用序列

insert into student values (seq_student.nextval, '小红', '女', 19, sysdate, 'hong@sxt.com', 102); 

1.3 删除序列

drop sequence seq_student;

ps： – 清空表格(保留表结构，注意与drop区别)

truncate table student;

2. 索引(index)

为了提高查询效率, 可以建立类似目录的数据库对象, 实现数据快速查询, 这就是索引(Index)

2.1 索引的创建

2.1.1 自动创建

Oracle 对 primary key 和 unique 约束的列, 会自动创建索引.

2.1.2 手动创建

对于不是 primary key 和 unique 约束的列, 如果经常会被查询或用于排序, 可以手动给其创建索引, 例如:

create index idx_sname on student (sname desc); 

2.2 索引的使用

索引被创建后, 查询时会自动生效, 提高查询效率.

2.3 索引的删除

drop index idx_sname; 

2.4 索引的优缺点

2.4.1 优点

当数据量比较庞大时, 索引可以大大提高查询的效率

2.4.2 缺点

a) 索引会单独存放, 索引过多会占用大量的存储空间; b) 索引会降低 DML 的效率, 因为数据发生变化时, 还需要重新维护索引; c) 对于唯一性不好的数据, 不适合创建索引.

3. 视图(view)

视图是从若干基本表和（或）其他视图构造出来的表. 视图中并不会存放数据, 只会存放视图的定义语句. 在用户使用视图时, 才去动态检索数据.

3.1 创建视图

a) 语法

create [or replace] view 视图名 as (查询) [with read only]

b) 创建简单视图

create or replace view v_student as (select * from student); 

c) 可以对视图进行 DQL 和 DML 操作

3.2 查询视图

 `select * from v_student;` 

3.3新增视图

insert into v_student values (seq_student.nextval, '小刚', '男', 20,
 sysdate, 'gang@sxt.com', 102); 

3.4 修改视图

update v_student set age=21 where sno=2;

3.5 删除视图

 delete from v_student where sno=2; 

3.6 只读视图

只读视图, 只能对视图进行查询(DQL)操作, 不能执行增删 改(DML)操作 （with read only）

create or replace view v_student as (select * from student) with read only; 

3.7 视图的作用

4. 事务(Transaction)

事务（Transaction）是一个操作序列。 这些操作要么都做, 要么都不做, 是一个不可分割的工作单元, 是数据库环境中的最小工作单元。

4.1 事务的特性(ACID)

4.1.1 Atomicity(原子性)

原子性是指事务包含的所有操作要么全部成功, 要么全部失 败回滚, 因此事务的操作如果成功就必须要完全应用到数据 库, 如果操作失败则不能对数据库有任何影响.

4.1.2 Consistency(一致性)

一致性是指事务必须使数据库从一个一致性状态变换到另 一个一致性状态, 也就是说一个事务执行之前和执行之后都 必须处于一致性状态.

4.1.3 Isolation(隔离性)

隔离性是当多个用户并发访问数据库时, 比如操作同一张表 时, 数据库为每一个用户开启的事务, 不能被其他事务的操 作所干扰, 多个并发事务之间要相互隔离.

4.1.4 Durability(持久性)

持久性是指一个事务一旦被提交了, 那么对数据库中的数据 的改变就是永久性的, 即便是在数据库系统遇到故障的情况 下也不会丢失提交事务的操作.

4.2 事务的提交和回滚

a) 提交, 在确保事务执行成功时, 应该将事务进行提交. 提 交后, 数据被永久保存, 不能进行回滚. commit; b) 回滚, 当事务执行出现故障时, 应该进行事务的回滚操作, 本次事务的所有操作将被还原, 保证数据库的一致性. Rollback

5. rowid 和 rownum

5.1 rowid

a) rowid 在记录创建时生成，而且是不变，直接指向硬件上 的存储位置 b) 通过 rowid 查询是效率最高的, 但是 rowid 是由 Oracle 维护的, 人力无法做到

5.2 rownum

rownum 是一个伪列, 查询的时候除非特别指定，否则不会显 示. 表示行号, 常用于控制查询返回的行数.

5.2.1 通过 rownum 进行 TOP-N 查询

当 rownum 和 order by 一起使用时，会首先选出符合 rownum 条件的记录，然后再进行排序. 因此, 需要用子查询来完成.

例如: 查询工资排名前 5 的员工信息

select * from (      
select e.* from emp e order by sal desc
) where rownum<=5;

5.2.2 通过 rownum 进行分页查询

由于先要进行 where 条件判断, 满足条件后才能生成rownum, 所以导致 rownum 无法进行大于(>)和大于等于(>=)的判断. 此时, 需要使用嵌套子查询来实现.

例如: 分页查询员工信息, 按工资降序排序. 用 page 表示当前页数, size 表示每页显示的记录数, 则分页查询语句为:

select * from (       
select rownum rn, t.*       
from (            
select * from emp order by sal desc       
) t       
where rownum<=page*size ) tt 
where tt.rn>(page-1)*size;