本文为博主辛苦总结,希望自己以后返回来看的时候理解更深刻,也希望可以起到帮助初学者的作用.
**转载请注明 出自 : 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会自动在约束列上创建唯一索引)
- 非唯一索引 : 列值可以重复的索引
创建索引的原则:
- 字段取值分布范围很广
- 字段中包含大量空值
- 字段经常出现在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中的索引所在的列