Oracle之索引，视图，序列

1.索引

索引：类似于书的目录，提高查询效率

创建索引语法：

create index index_name on table_name(col_name[,col_name[,col_name,]]);

create index:index索引关键字

index_name:索引名称

table_name:表名

col_name:索引列名

create index ind_emp_name on emp(ename);

索引它并不是在所有情况下都会优化查询，它是在大量数据查询时，每次查询的数据量在表的总数据量30%以下时，会提高效率，

索引会提高查询效率，但是会降低添加和修改的执行效率

索引一般是给增删改操作比较少表上建

单列索引：给某个列建一个索引，叫单行索引

联合索引：给多个列建一个索引，叫联全索引，如果联合索引，在查询时，使用了第一个索引列作为条件，那么它会使用索引提高查询效率，如果使用了非第一个索引列作为条件，它不会使用索引

注意：使用索引列作为查询条件时，不要在索引列上进行数学运算

索引在逻辑上和物理上都与相关的表和数据无关，创建和删除索引，都不会影响基本的表，都不会影响sql语句的语法

原理：

如果我们没有索引，搜索name=’scott’ ，需要检索所有的记录

给name字段加上索引，oracle就会对全表进行一次搜索，将每条记录的name值进行升序排列，然后构建索引条目，存储在索引段中，搜索name=’scott’ ，直接找索引排序后的结果

create index ind_emp_sal on emp(sal);

select * from emp where sal*12>10000;   --这样写条件不会使用索引

select * from emp where sal>10000/12;    --这样它就会使用索引

删除索引：

drop index 索引名;

drop index ind_emp_sal;

drop index ind_emp_name;

2.视图

视图以经过定制的方式显示来自一个或多个表的数据

视图可以视为“虚拟表”或“存储的查询”

创建视图所依据的表称为“基表”

视图的优点有：

提供了另外一种级别的表安全性

隐藏的数据的复杂性

简化的用户的SQL命令

隔离基表结构的改变

通过重命名列，从另一个角度提供数据

创建视图的语法：

 CREATE [OR REPLACE] [FORCE] VIEW

    view_name [(alias[, alias]...)]

  AS select_statement

  [WITH CHECK OPTION]

  [WITH READ ONLY];

grant create view to scott;

create view:view视图关键字

OR REPLACE：如果视图已经存在，则替换旧视图。

FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。

view_name:视图名称，符合标识符命名规范

别名：别名的数量要和后面的查询语句的列相同

WITH CHECK OPTION 子句可以保证让你只能在视图的条件之内（where后的查询条件）对视图进行DML

WITH READ ONLY:表示是一个只读视图

create view view_dept_emp as select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

select * from emp;

注：

创建视图中可以使用单行函数、分组函数和表达式必须为使用函数或者表达式的字段指定名字

视图的使用：

select * from view_dept_emp;

select empno,ename,job,sal,deptno,dname from view_dept_emp;

视图上的DML语句有如下限制：

只能修改一个底层的基表（也是键保留表）

如果修改违反了基表的约束条件，则无法更新视图

如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图

如果视图包含伪列或表达式，则将无法更新视图

复杂视图中，可以更新键保留表的列（单表和视图都可以作为主键的列所在的表），不可以更新非键保留表的列。

使用DROP VIEW语句删除视图

3.序列

序列：是oracle中自动生成一个序列的对象，一般用来生成表的主键的值

创建序列语法：

create sequence sequence_name

    start with startValue

    increment by inrValue

    minvalue minValue

    maxvalue maxValue

    cache|nocache

    cycle|no cycle

create seqcurruence:sequence序列关键字

sequence_name:序列名称，符合标识符命名规范

start with satrtValue:指定序列的初始值,startValue是一个自然数

increment by inrValue:指定序列的值每次增加多少，inrValue 是一个自然数 一般用1

minvalue minValue:指定序列的最小值 minValue是一个自然数据

maxvalue maxValue:指定序列的最大值 maxValue是一个自然数据

cache|no cache:指定序列缓存，默认缓存20个,oracle会预先在内存中放置一组指定大小的序列，当使用完这些序列后再生成下一组，这样会存取得快些，但当数据库关闭等情况时，下一次再生成序列时可能会使序列间断，不是一串连续的号，当不是特别需要连续的序列时最好指定；

cycle|no cycle:指定序列是否循环生成

创建一个序列

create sequence my_seq start with 1 increment by 1;

序列的两个属性

nextval:取序列的值，并且序列的值增加

currval:取序列的当前值，序列值不会变

序列名.属性

select my_seq.nextval from dual;

select my_seq.currval from dual;  --不能直接取当前值

insert into 表名 values(my_seq.nextval,'值','值');

使用ALTER SEQUENCE语句修改序列，

不能更改序列的START WITH参数

 ALTER SEQUENCE stu_seq MAXVALUE 5000 CYCLE;

使用DROP SEQUENCE语句删除序列

DROP SEQUENCE stu_seq;