Oracle 数据库学习笔记04

1. 视图

视图是数据库对象之一,数据库对象有: 表 , 视图 , 索引 , 序列
视图在SLQ中的体现的角色与表相同，但是并非真实的表，它不包含任何数据。它只是对应一个查询语句的结果集
所以查询一张视图时，先会执行该视图对应的查询语句得到结果集，然后再根据这个结果集进行实际的查询工作。

• 简单视图

  视图数据来自单一的一张表，且不含有函数或表达式

• 复杂视图

  查询语句含有函数，表达式，分组等

• 连接视图

  视图数据来自多张表，算是复杂视图的一种

创建一个简单视图 , 显示部门 10 的员工信息

CREATE OR REPLACE VIEW v_emp_dept10ASSELECT empno id, ename name, sal salary,deptnoFROM empWHERE deptno = 10

创建一个复杂视图， 显示 不同部门的工资信息

 CREATE OR REPLACE VIEW v_salinfo AS SELECT AVG(sal) avg_sal,         SUM(sal) sum_sal,         MAX(sal) max_sal,         MIN(sal) min_sal ,        e.deptno, d.dname FROM emp e, dept d  WHERE e.deptno = d.deptno GROUP BY e.deptno, d.dname

注意: 当视图对应的查询语中字段含有函数和或者表达式时,该字段必须给别名

基于上述视图实现查询

 -- 查询工资低于部门平均工资的员工信息 SELECT e.ename, e.sal, e.deptno FROM emp e, v_salinfo v WHERE e.deptno = v.deptno AND e.sal< v.avg_sal;

删除视图

-- 删除视图并不会删除基表中的数据DROP VIEW v_emp_dept10

---

2. 视图的DML操作

对视图进行DML操作
仅能对简单视图进行 DML 操作，复杂视图不允许进行 DML 操作。

简单视图的DML操作要求

注意不能违反基表的约束条件
基表： 视图数据来源的表
对视图进行 DML 操作就是对基表进行 DML 操作

• 向视图对应的基表插入一条

INSERT INTO v_emp_dept10(id,name,salary,deptno) VALUES (1001，'JACK',5000，10)

• 更新记录

UPDATE v_emp_dept10SET salary = 9000WHERE id = 1001;

---

视图对基表的污染

对视图进行某些 DML 操作时，可能导致视图自身对该记录不可见，但是依然能影响到基表记录，这种操作是对基表的“污染”。（DELETE 操作不会造成该影响）
举例 1：

--因为视图本身只能查询到 部门10 的数据，所以一下插入的数据对基表修改了，但是视图不可见INSERT INTO v_emp_dept10(id, name, salary, deptno) VALUES ('1002','ROSE',5000,20)

举例 2 ：

-- 一下操作将视图所能查询到的 部门 10 的部门号都改为20，导致基表不存在部门10，再次查询该视图则会没有记录UPDATE  v_emp_dept10SET deptno = 20；

如何防止上述情况发生？对视图添加检查选项

添加了检查选项的视图要求对视图进行 DML 操作后，视图必须对其可控：
INSERT 后视图必须可见，UPDATE 后视图也必须对其可见，DELETE 不影响

创建视图 指定检查选项 ( WITH CHECK OPTION)

CREATE OR REPLACE VIEW v_emp_dept10 ASSELECT empno id, ename name, sal salary,deptnoFROM empWHERE deptno = 10WITH CHECK OPTION

创建视图 指定只读选项 ( WITH READ ONLY)

-- 该视图只能进行查询，不能进行 DML 操作CREATE OR REPLACE VIEW v_emp_dept10 ASSELECT empno id, ename name, sal salary,deptnoFROM empWHERE deptno = 10WITH READ ONLY

---

3 . 序列

数据库对象之一，用来生成一系列数字，通常序列生成的数字是为某张表的主键字段（id）提供值的

创建 从1000 开始，每次自增 10 的序列

CREATE SEQUENCE seq_emp_empnoSTART WITH 1000INCREMENT BY 10

序列支持两个伪列：

• NEXTVAL : 使序列生成下一个数字，新创建的序列第一次生成是 START WITH 指定的数，以后就是上次生成的数字加上步长得到的。序列不能后退，所以生成新数字后就不能得到以前的数字了
• CURRVAL ： 返回序列最后生成的数字， 新创建的序列至少调用一次 NEXTVAL 以后才可以使用CURRVAL

 SELECT seq_emp_empno.NEXTVAL FROM dual; -- 第一次执行结果为1000， 以后每次加10 SELECT seq_emp_empno.CURRVAL FROM dual; -- 执行后为当前数字

使用序列插入数据（主键自增）

INSERT INTO emp (empno, ename, job ,sal, deptno)VALUES(seq_emp_empno.NEXTVAL,'jack','CLERK' ,1000,10)

删除序列

DROP SEQENCE sqq_emp_empno

---

4 . 索引

索引是一种允许直接访问数据表中某一数据行的属性结构，为了提高查询效率而引入的，是独立于表的对象

创建索引

CREATE INDEX idx_emp_ename ON emp(ename)

创建复合索引（多字段）

CREATE index idx_emp_job_sal ON emp(job,sal)

创建基于函数的索引

CREATE INDEX idx_emp_ename_upper ON emp(UPPER(ename))

在查询 索引 所绑定的字段时，数据库管理软件会自动调用索引以提高效率。

注意：索引不宜创建过多，表的数据量不大时不推荐创建索引。