数据库创建视图 索引会生效吗

一、视图

1.1 什么是视图？

• 视图（View）也被称作虚表，即虚拟的表，是一组数据的逻辑表示
• 视图对应于一条SELECT语句，结果集被赋予一个名字，即视图名字
• 视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化
• 视图创建后，可以像操作表一样操作视图，主要是查询

❤️ 简单理解：

• 视图是数据库对象之一
• 所有数据库对象名字不能重复，所以视图名字一般是以“v_”开头
• 视图在SQL语句中体现的角色与表相同但是视图并不是一张真实存在的表，而只是对应一个SELECT语句的查询结果集，并将其当做表看待而已。
• 使用视图的目的是简化SQL语句的复杂度，重用子查询，限制数据访问。

根据视图所对应的子查询种类分为以下几种类型：

• （1）SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视图，此时视图是基表的子集
• （2）SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY 子句，叫做复杂视图
• （3）SELECT语句是基于多个表的，叫做连接视图

1.2 视图的作用？

• 如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询
• 视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其他列起到安全和保密的作用，限制数据访问

1.3 授权创建视图

• 创建视图的语句是CREATE VIEW
• 用户必须有CREATE VIEW 系统权限，才能创建视图
• 如果没有权限，会提示：权限不足
• 管理员可以通过DCL语句授予用户创建视图的权限

1.4 创建简单视图（单表）

• 创建一个简单视图v_emp_10来显示部门10中的员工的编码，姓名和薪水

CREATE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10;

• 可以用OR REPLACE短语修改视图对应的SQL查询语句

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal*12 salary ,deptno
FROM emp
WHERE deptno=10;

1.5 查询视图

• 视图对应的子查询中的字段若含有函数或者表达式，那么该字段必须指定别名。当视图对应的子查询中的字段使用了别名，那么视图中该字段就用别名
  来命名。
• 修改视图
  由于视图仅对应一个SELECT语句，所以修改视图就是替换该SELECT语句而已。

查看视图结构：
SELECT * FROM v_emp_10;

1.6 对视图进行DML操作

• 视图本身并不包含数据，只是基表数据的逻辑映射
• 当对视图执行DML操作时，实际上是对基表的DML操作
• 对视图执行DML操作的基本原则：
  （1）简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列对视图不可见，这时无法对视图执行INSERT操作
  （2）如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作
  （3）DML操作不能违反基表的约束条件
  eg:
  
  ❤️ 注意：
• 对视图的DML操作就是对基表操作，那么操作不当可能对基表进行数据污染
• 更新同样存在更新后对数据不可控的情况
• 删除不会对基表数据进行污染
  eg:

1.7 创建具有CHECK OPTION约束的视图

为视图添加检查选项，可以保证对视图的DML操作后视图对其可见，否则不允许进行该DML操作，这样就避免了对基表进行数据污染
eg:

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal salary ,deptno
FROM emp
WHERE deptno=10
WITH CHECK OPTION;

添加记录1：

INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES
(1001,'jack',2000,10);

SELECT * FROM v_emp_10;

添加记录2：

INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES
(1002,'rose',3000,20);

❤️ 小结：

同插入一条记录为是什么jack就不报错，而rose会报错呢？

原因：

因为 WITH CHECK OPTION为视图添加检查选项，可以保证对视图的DML操作后视图对其可见，否则不允许进行该DML操作，由于rose是20号部门的，创建的视图是10号部门的，20号部门数据对10号部门数据不可见所以rose会报错。

1.8 创建具有READ ONLY约束的视图

• 对简单视图进行DML操作是合法的，但是不安全的
• 如果没有在视图上执行DML操作的必要，在建立视图时声明为只读来避免这种情况，保证视图对应的基表数据不会被非法修改

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal salary ,deptno
FROM emp
WHERE deptno=10
WITH READ ONLY;

当对含有WITH READ ONLY的视图进行修改时会报错

1.9 通过查询USER_VIEW获取相关信息

• 和视图相关的数据字典
  （1）USER_OBJECTS
  （2）USER_VIEWS
  （3）USER_UPDATE_COLUMNS
  eg 1 :
  查询创建过的所有视图

SELECT TEXT
FROM user_views;

eg 2：
查询创建过的所有表名

SELECT TABLE_NAME
FROM USER_TABLES;

1.10 创建复杂视图（多表关联）

• 复杂视图指在子查询中包含了表达式，单行函数或分组函数的视图
• 必须为子查询中的表达式或函数定义别名

eg 1：
创建一个含有公司部门工资情况的视图，内容为：部门编号，部门名称，部门的最高，最低，以及工资总和信息

CREATE OR REPLACE VIEW v_salary
AS
SELECT d.dname,d.deptno,
MAX(e.sal) max ,
MIN(e.sal) min,
SUM(e.sal) sum,
AVG(e.sal) avg
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno, d.dname;

eg 2：

查看谁比自己所在部门平均工资高

SELECT e.ename,e.sal,e.deptno
FROM emp e ,v_salary v
WHERE e.deptno=v.deptno
AND e.sal>v.avg;

1.11 删除视图

• 当不再需要视图的定义，可以使用DROP VIEW语句删除视图
• 视图虽然是存放在数据字典中的独立对象，但视图仅仅是基于表的一个查询定义，所以对视图的删除不会导致基表数据的丢失，不会影响基表数据。

DROP VIEW  v_salary;

二、序列

2.1 什么是序列

• 序列（SEQUENCE）是一种用来生成唯一数字值的数据库对象
• 序列的值由Oracle程序按递增或递减顺序自动生成，通过用来自动产生表的主键值，是一种高效率获得唯一键值的途径
• 序列是独立的数据库对象，和表是独立的对象，序列并不依附于表
• 通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值

2.2 创建序列

❤️ 注意事项：

序列支持两个伪列

• NEXTVAL:获取序列下一个值
  若是新创建的序列，那么第一次调用返回的是START WITH指定的值，以后每次调用都会得到当前序列值加上步长后的数字。
  NEXTVAL会导致序列发生步进，且序列不能回退。
• CURRVAL:获取序列当前值，即：最后一次调用NEXTVAL后得到的值，CURRVAL不会导致步进。但是新创建的序列至少调用一次NEXTVAL后才可以
  使用CURRVAL.

2.3 使用序列

2.4 删除序列

/*
删除序列
*/
DROP SEQUENCE seq_emp_id;

三、索引

3.1 索引的原理

• 索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中
• 索引记录中存有索引关键字和指向表中数据的指针（地址）
• 对索引进行的I/O操作比对表进行操作要少很多
• 索引一旦被建立就将Oracle系统自动维护，查询语句中不用指定使用哪个索引
• 索引是一种提高查询效率的机制
  ❤️ 注意：
  索引是数据库对象之一，索引是为了提高查询效率。索引的统计与应用是数据库自动完成的，只要数据库认为可以使用某个已创建的索引时就会自动应用。

3.2.1 创建索引

eg 1 ：

/*
在EMP表的NAME列上建立索引
*/
CREATE INDEX idx_emp_ename ON emp(ename);

3.2.2 修改和删除索引

3.3 创建基于函数的索引

3.4 合理使用索引提升查询效率