视图、序列、索引、约束--Oracle（六）

视图

视图是数据库对象之一，所有数据库对象不能重复，所以视图名字一般是以“v_”开头。
视图在SQL语句中体现的角色与表相同，但是视图并不是一张真实存在的表，而只是对应一个查询语句的结果集，并将其当做表看而已。
使用视图的目的是简化SQL语句的复杂度，重用了查询，限制了数据访问。
创建视图该视图包含的数据时10号部门的员工信息

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

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

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

视图分为简单视图与复杂视图
简单视图：对应子查询中不含有关联查询，查询的字段不包含函数，表达式，没有分组，没有去重，反之是复杂查询。
对视图进行DML操作
仅能对简单视图进行DML操作。
对视图进行DML操作就是对视图数据来源的基础表进行的操作。

INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES(1001,'JACK',2000,10);
SELECT * FROM v_emp_10;
SELECT * FROM emp;

对视图的DML操作就是对基表的操作，那么操作不当可能对基表进行数据污染

INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES(1001,'ROSE',2000,20);
SELECT * FROM v_emp_10;
SELECT * FROM emp;

视图对ROSE不可见
更新同样存在更新后对数据不可控的情况：

UPDATE v_emp_10 
SET deptno=20;

删除不会对基表产生数据污染：

DELETE FROM v_emp_10
WHERE deptno=20;

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

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

插入1001,’JACK’,2000,10操作正常：

 INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES(1001,'JACK',2000,10);
SELECT * FROM v_emp_10;
SELECT * FROM emp;

插入1001,’ROSE’,2000,20操作将报错：

INSERT INTO v_emp_10
(id,name,salary,deptno)
VALUES(1001,'ROSE',2000,20);
SELECT * FROM v_emp_10;
SELECT * FROM emp;

WITH READ ONLY
为视图添加只读选项，那么该视图不允许进行DML 操作。

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

数据字典
user_objects

SELECT object_name
FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%_emp';

user_views
TEXT 查询SQL语句

SELECT  TEXT,view_name
FROM user_views;
user_tables
SELECT table_name
FROM user_tables;

复杂视图

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

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

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

SELECT e.ename,e.sal,e.deptno
FROM emp e,v_dept_sal v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal;

删除视图
删除视图本身并不会影响基表数据，但是删除视图数据会对应将基表数据删除。

DROP VIEW v-emp_10;

序列

Id主键
序列是独立的数据库对象，是一种高效获取唯一键值的途径。

CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT 1;

MAXVALUE
MINVALUE
CYCLE 表示在递增至最大值或者递减至最小值之后是否重用序列。若是递减并有最大值从最大值开始。若是递减并有最小值，从最小值开始。若没有从START WITH指定的值开始。默认是NOCYCLE。
CACHE(默认20）
序列支持两个伪列：
NEXTVAL:获取序列下一个值，若是新创建的序列那么第一次调用返回的是START WITH指定的值，以后每次调用都会得到当前序列值加上步长后的数字。
NEXTVAL会导致序列发生步进，且序列不能回退。

CURRVAL:获取序列当前值，并且不会导致步进。但是新创建的序列应至少调用一次NEXTVAL后才可以使用CURRVAL.

SELECT seq_emp_id.NEXTVAL
FROM dual;
SELECT seq_emp_id.CURRVAL
FROM dual;

使用序列为EMP表中新插入的数据提供主键字段的值：

INSERT INTO emp
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_id.NEXTVAL,'JACK',3000,'CLERK',10);
SELECT * FROM emp ;

删除序列

DROP SEQUENCE seq_emp_id;

索引

索引是数据库对象，目的是为了提高查询效率。
索引的统计与应用是自动完成的，只要数据库认为可以使用某个已创建的索引时就会自动应用。
索引是加在字段名上的。
在emp表的ename列上建立索引：

CREATE INDEX idx_emp_ename 
ON emp(ename)；

复合索引
如果经常在ORDER BY 子句中使用job和sal作为排序依据，可以建立复合索引：

CREATE INDEX idx_emp_job_sal 
ON emp(job,sal);

当做下面查询时，会自动应用索引idx_emp_job_sal

SELECT empno,ename,sal,job 
FROM emp
ORDER BY job，sal;(注意顺序）

创建基于函数的索引
如果需要在emp表的ename列上执行大小写无关的搜索，可以在此列上建立一个基于upper函数的索引：

CREATE INDEX idx_emp_ename_upper
ON emp(UPPER(ename));

当做下面查询时，会自动应用刚刚建立的索引：

SELECT * FROM emp
WHERE UPPER(ename)='KING';

修改和删除索引
如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率：
重建索引idx_emp_ename：

ALTER INDEX idx_emp_ename REBUILD;

当一个表上有不合理的索引，会导致操作性能下降，需删除索引：

DROP INDEX idx_emp_ename;

合理使用索引提高查询效率

• 为经常出现在WHERE子句中的列创建索引
• 为经常出现在ORDER BY DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
• 为经常作为表的连接条件的列上创建索引
• 不要在经常做DML操作的表上建立索引
• 不要在小表上建立索引
• 限制索引数目，索引并不是越多越好
• 删除很少被使用的、不合理的索引

约束

非空约束 NOT NULL

 CREATE TABLE employees(
 id NUMBER(6),
 name VARCHAR2(30) NOT NULL,
 salary NUMBER(7,2),
 hiredate DATE CONSTRAINT employees_hiredate_nn NOT NULL
 );

 ALTER TABLE employees
 MODIFY(eid NUMBER(6) NOT NULL);

如果业务要求取消某列的非控约束，可以采用重建表或者修改表的方式：

ALTER TABLE employees
 MODIFY(eid NUMBER(6）NULL);

唯一性约束
唯一性约束可以保证表中该字段的值任何一条记录都不可以重复，NULL除外。

CREATE TABLE employees1(
 id NUMBER(6) UNIQUE,
 name VARCHAR2(30),
 email VARCHAR(50),
 salary NUMBER(7,2),
 hiredate DATE,
 CONSTRAINT employees_email_uk  UNIQUE(email)
 );

ALTER TABLE employees
  ADD CONSTRAINT employees_name_uk UNIQUE(name);

主键约束 （primary key）
主键约束从功能上相当于非空 (not null)且唯一(unique)的组合。
主键应是对系统无意义的数据，永远也不要改变。
一张表上只有一个主键。

 CREATE TABLE employees2(
 id NUMBER(6) PRIMARY KEY,
 name VARCHAR2(30),
 email VARCHAR(50),
 salary NUMBER(7,2),
 hiredate DATE
 );

ALTER TABLE employees3
  ADD CONSTRAINT employees3_id_pk PRIMARY KEY (id);

外键约束
通常来讲，外键约束会对对性能有降低，
关联不一定需要外键约束。

 CREATE TABLE employees4(
 id NUMBER(6) ,
 name VARCHAR2(30),
 email VARCHAR(50),
 salary NUMBER(7,2),
 deptno NUMBER(4)
 ); 

ALTER TABLE employees4
ADD CONSTRAINT employees4_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno);

检查约束
可以对字段的取值有更详细的约束，
当定义了CHECK约束的列新增或者修改数据时，数据必须符合check约束。

ALTER TABLE employees2
ADD CONSTRAINT employees2_salary_check CHECK(salary>2000);