Oracle

oracle视图创建索引 Oracle学习总结


本文为博主辛苦总结,希望自己以后返回来看的时候理解更深刻,也希望可以起到帮助初学者的作用.

day 06 视图,索引,序列 视图 什么是视图:

视图是基于一个或多个表或视图的逻辑表,视图本身并不包含数据,但是却像一个窗口,通过它可以查询或修改表中的数据。视图所基于的表称为基表,视图是存储在数据字典中的一条SELECT SQL语句。

视图优点:

限制对数据的访问,因为视图可以有选择性地显示数据库的一部分。

视图可以简化复杂的查询, 但不能提高性能 .

维护数据的独立性,尤其是对用户和应用程序来说,视图可以从多个表简述数据

视图分类

简单视图: 基于单个表创建, 不包含任何函数,表达式以及分组

复杂视图: 包含函数,表达式或者分组的视图, 简化了查询操作

连接视图: 基于多个表建立的视图, 简化了连接查询

只读视图: 只允许Selwct 操作,不允许DML操作

创建一个简单视图

CREATE VIEW v_emp AS SELECT * from copy_emp;

CREATE [OR REPLACE] VIEW 视图名 [(alias[,alias]…)] (alias指定视图别名)
AS subquery(子查询)
[WITH CHECK OPTION[CONSTRAINT constraint]](定义约束)
[WITH READ ONLY](只读);
设置视图的可见字段(OR REPLACE可以在创建视图时候不用删除已存在的视图便更改视图)

CREATE OR REPLACE VIEW v_emp AS SELECT first_name,last_name, salary from copy_emp;

修改视图 , 给Donald 工资涨400

UPDATE V_EMP SET SALARY=SALARY+400 WHERE FIRST_NAME='Donald';

增加一条记录

INSERT INTO v_emp(first_name,last_name,email,hire_date,job_id,salary) VALUES('Yan','Luogg','111',sysdate,'IT_PROG','8000');

给视图字段重命名: 两种方法

方法一 : CREATE OR REPLACE VIEW v_emp AS SELECT first_name || ',' || last_name "姓名", salary "工资" from copy_emp;

方法二 : CREATE OR REPLACE VIEW v_emp("姓名","工资","入职时间") AS SELECT first_name , salary,hire_date from copy_emp;

查看系统视图的详细信息 : select * from user_views where view_name=UPPER('v_emp');

查询视图中是否可以执行DML操作 : select * from user_updatable_columns where table_name=upper('v_emp');

删除视图 : DROP VIEW V_EMP;

只读视图 with read onluy

CREATE OR REPLACE VIEW v_emp AS SELECT * from copy_emp WITH READ ONLY;
select * from user_updatable_columns where table_name=upper('v_emp');--全部为no,不能增删改查

定义约束 with check option 例:
create view view2 as select * from emp where deptno=10 with check option;
只对插入与删除与更新有意义。
例:
insert into view2 values( ….., 10);  正确
insert into view2 values( ….., 20);  失败
这个选项表示:
通过本视图插入更新删除时,要遵循SQL语句中定义的限制,这里只限定了10号部门,所以只能插入10号部门。
序列

序列的产生 :

系统自动生成的、不重复的整数值

序列是一种数据库对象,可以被多个用户共享

典型用途是作为主键值,它对于每一行必须是唯一的

序列可以替代应用程序编号

可以对序列值进行缓冲存储,以提高访问效率

创建序列 : CREATE SEQUENCE 序列名称
[INCREMENT BY n](序列直接间隔,默认为1)
[START WITH n](起始序列值,默认为1)
[{MAXVALUE n | NOMAXVALUE}](指定最大序列值|指定最大序列值为10的27次方)
[{MINVALUE n | NOMINVALUE}](指定最小序列值|指定最小序列值为1)
[{CYCLE | NOCYCLE}](达到最大或最小值之后循环)
[{CACHE n | NOCACHE(内存中可以预分配的序列个数,默认值:20)
)
例如:
--创建一个从100开始的递减序列
create sequence emp_ind3
start with 100
INCREMENT BY -1
MINVALUE 1
MAXVALUE 100;

pl/sqldeveloper 中也可以鼠标点击sequence 直接创建序列

查询序列信息(通过字典表):
select * from user_sequences;

序列的两个伪列:nextval、currval
NEXTVAL用于返回序列下一个可获取的序列值,必须在NEXTVAL前用序列名作前缀。
CURRVAL用于提取当前用户产生的序列值,必须在CURRVAL前加序列名称。
select myseq.nextval from dual;
select myseq.currval from dual;
刚创建的序列,第一次只能访问nextval。
如果先使用curval,就会报错:
select mysql.currval from dual;
ORA-0802:序列 MYSEQ.CURRVAL 尚未在此会话中定义。
正常使用:
select myseq.nextval from dual; --> 1
select myseq.nextval from dual; --> 2
select myseq.currval from dual; --> 2
每次查询nextval都会导致序列的指针向后移支一次。

删除序列
DROP SEQUENCE sequence_name;

索引 索引概念:

一种用于提升查询效率的数据库对象

Oracle数据库自动使用和维护索引

通过快速定位数据的方法,减少磁盘I/O操作

索引信息与表独立存放

单列索引 : 基于一列建立的索引, CREATE INDEX emp_idx1 ON emp(ename,job);

复合索引 : 基于两列或多列的索引, CREATE INDEX emp_idx1 ON emp(ename,job);

唯一索引 : 列值不能重复(当定义主键和唯一约束是,oracle会自动在约束列上创建唯一索引)

非唯一索引 : 列值可以重复的索引

创建索引的原则:

字段取值分布范围很广

字段中包含大量空值

字段经常出现在where子句或连接条件中

表经常被访问、数据量很大,且通常每次访问的数据量小于记录

不适合建索引的情况:

表很小

字段不经常出现在where子句中

每次访问的数据量大于记录总数的2%~4%

表经常更新

创建索引 :

CREATE [UNIQUE] INDEX index_name
on table_name (column|,column|…);

例子: create index myindex on t_emp(ename);

删除索引:drop index index_name;

单列索引的使用 :

CREATE INDEX i_ename ON emp(ename);--建立单列索引

在建立了i_ename之后,如果在WHERE 子句中引用
Ename列,会自动使用该索引。

SELECT * FROM EMP WHERE ename=‘’;
UPDATE EMP SET sal = 3000 WHERE ename=‘’;
DELETE FROM EMP WHERE ename=‘’;

复合索引使用 :

复合索引基于多个列建立索引。如果在WHERE 子句中需要经常引用一个表的多列定位数据,那么考虑在这些列上建立复合索引. 建立复合索引时不能超过32个。
CREATE INDEX i_deptno_job ON emp(deptno,job);

SELECT * FROM EMP WHERE deptno=‘’ AND job=‘’;
SELECT * FROM EMP WHERE deptno=‘’

注意:
如果在WHERE 子句中使用OR引用deptno和job列或单独引用job列就不会引用索引. 要想引用复合索引, 那么where后边的顺序按索引建立的顺序,或者只引用第一个索引

如:
SELECT * FROM EMP WHERE deptno=‘’ OR job=‘’;
SELECT * FROM EMP WHERE job=‘’

注意:
两个索引,字段相同,但顺序不同,那么这两个索引不是同一个索引.
例如:
CREATE INDEX EMP_INDEX2 ON COPY_EMP(EMPLOYEE_ID,JOB_ID);
CREATE INDEX EMP_INDEX3 ON COPY_EMP(JOB_ID,EMPLOYEE_ID);
EMP_INDEX2和EMP_INDEX3不相同.

索引信息的查询

SELECT * FROM User_Indexes; --查看当前用户所有表的索引

SELECT * FROM User_Indexes WHERE table_name = UPPER('copy_emp');
-- 查看copy_emp这张表有多少索引

SELECT * FROM User_Ind_Columns; --查看当前用户所有索引所在的列

SELECT * FROM User_Ind_Columns WHERE table_name = UPPER('copy_emp');--查看表copy_emp中的索引所在的列