Oracle学习总结_day06_视图&序列&索引

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

**转载请注明 出自 : luogg的博客园 ** 谢谢配合!

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会自动在约束列上创建唯一索引)
• 非唯一索引 : 列值可以重复的索引

创建索引的原则：

1. 字段取值分布范围很广
2. 字段中包含大量空值
3. 字段经常出现在where子句或连接条件中
4. 表经常被访问、数据量很大，且通常每次访问的数据量小于记录

不适合建索引的情况：

1. 表很小
2. 字段不经常出现在where子句中
3. 每次访问的数据量大于记录总数的2%~4%
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不相同.

索引信息的查询

1. SELECT * FROM User_Indexes; --查看当前用户所有表的索引
2. SELECT * FROM User_Indexes WHERE table_name = UPPER('copy_emp');
   -- 查看copy_emp这张表有多少索引
3. SELECT * FROM User_Ind_Columns; --查看当前用户所有索引所在的列
4. SELECT * FROM User_Ind_Columns WHERE table_name = UPPER('copy_emp');--查看表copy_emp中的索引所在的列