NO.47 视图、序列、索引 、 约束

为方便测试，创建表emp，包含以下信息：

emp测试用表

视图VIEW

视图是数据库对象之一，在SQL语句中体现的角色与表一致。但是视图并非一张真实存在的表，它只是一个查询语句对应的结果集。

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

SELECT * FROM v_emp_10

视图对应的子查询中的字段可以指定别名，这样该视图对应的字段名就是这个别名。

当一个字段是函数或者表达式，那么该字段必须指定别名

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

视图根据对应的子查询不同，分为简单视图和复杂视图

简单视图：对应的子查询不含有函数，表达式，分组，去重，关联查询。

除了简单视图就是复杂视图

简单视图可以进行DML操作，对该视图的操作就是对该视图数据来源的基础表进行的操作。

复杂视图不允许进行DML操作。

对简单视图进行DML操作也不能违反基础表的约束条件。

对视图进行DML操作，视图对基础表操作时，只能对视图可见的字段进行。

INSERT INTO v_emp_10(id,name,salary,deptno) VALUES (1001,'JACK',3000,10)

SELECT * FROM v_emp_10

SELECT * FROM emp

UPDATE v_emp_10 SET salary=4000 WHERE id=1001 DELETE FROM v_emp_10 WHERE id=1001

对视图的不当DML操作会污染基表数据

即：对视图进行DML操作后，视图对基础表对应数据进行该DML操作，但是操作后视图却对该记录不可见。

INSERT INTO v_emp_10(id,name,salary,deptno) VALUES(1001,'JACK',3000,20)

UPDATE v_emp_10 SET deptno=20

DELETE不会产生污染现象。

DELETE FROM v_emp_10 WHERE deptno=20

为视图添加检查选项，可以避免对视图操作而导致的对基表的数据污染。

WITH CHECK OPTION

该选项要求对视图进行DML操作后，该记录必须对视图可见。

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

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 WITH READ ONLY

常用的数据库的数据字典

USER_OBJECTS：记录用户创建过的所有数据库对象

SELECT object_name,object_type FROM user_objects WHERE object_type='VIEW' 

USER_VIEWS：专门记录曾经创建过的视图信息

SELECT view_name,text FROM user_views WHERE view_name LIKE '%EMP%'

USER_TABLES：专门记录曾经创建过的表的信息

SELECT table_name FROM user_tables

删除视图

DROP VIEW v_emp_10

创建复杂视图

创建一张视图，包含员工工资及相关部门信息

包含：每个部门的平均工资，最大，最小，工资总和，以及对应的部门名称，部门编号。

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

SELECT * FROM v_emp_salinfo

查看哪些员工的工资高于其所在部门平均工资?

SELECT e.ename,e.sal,e.deptno FROM emp e,v_emp_salinfo v WHERE e.deptno=v.deptno

AND e.sal>v.avg_sal

序列SEQUENCE

序列是数据库对象之一，作用是根据指定的规则生成一系列数字。通常使用序列生成的

数字是为表中的主键字段提供值使用。

CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1

序列支持两个伪列：

NEXTVAL：获取序列的下一个数字，如果是新创建的序列，那么会从START WITH开始返回。

之后则是用上次生成的数字加上步长来得到本次生成的数字返回。

需要注意，序列是不能后退的。并且不受事务控制。

CURRVAL：获取序列最后生成的数字，新创建的序列至少调用NEXTVAL生成一个数字后才可以使用。CURRVAL不会导致序列步进。

SELECT seq_emp_id.NEXTVAL FROM dual

SELECT seq_emp_id.CURRVAL FROM dual

使用序列为EMP表主键字段提供值：

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

SELECT * FROM emp

删除一个序列

DROP SEQUENCE seq_emp_id

序列的数据字典

SELECT * FROM USER_SEQUENCES

索引INDEX

索引是数据库对象之一，作用是提高查询效率

索引的创建时是数据库自行完成的，并且数据库会在适当的时候自动使用索引。

CREATE INDEX idx_emp_ename ON emp(ename)

经常出现在WHERE中和ORDER BY中的字段要添加索引。经常出现在DISTINCT后面的字段也可以添加索引。

需要注意，对于字符串类型字段，若在WHERE中使用LIKE进行过滤时，是不会用到索引的。

约束

非空约束

CREATETABLE employees(

eidNUMBER(6),

nameVARCHAR2(30)NOTNULL,

salaryNUMBER(7,2),

hiredate DATE

CONSTRAINTemployees_hiredate_nn NOT NULL

)

添加非空约束

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

取消非空约束

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

唯一性约束

CREATE TABLE employees2 (

eid NUMBER(6) UNIQUE,

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE,

CONSTRAINT employees_email_uk UNIQUE(email)

)

增加唯一性约束

ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE(name)

测试

INSERT INTO employees2

(eid,name,email)

VALUES

(NULL,'jack',NULL)

SELECT * FROM employees2

主键约束

一张表只能有一个字段定义主键约束，主键约束要求该字段非空且唯一

CREATE TABLE employees3 (

eid NUMBER(6) PRIMARY KEY,

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE

)

增加主键约束

ALTER TABLE employees3 ADD CONSTRAINT employees3_eid_pk PRIMARY KEY(eid);

测试

INSERT INTO employees3

(eid,name,email)

VALUES

(2,'jack','jack@123.com')

外键约束

外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。比如emp表的deptno列参照dept表的deptno列，则dept称作主表或父表，emp表称作从表或子表。

CREATE TABLE employees4(

eidNUMBER(6),

nameVARCHAR2(30),

salaryNUMBER(7,2),

deptnoNUMBER(4)

);

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

外键约束条件包括两个方面的数据约束：

从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL；

当主表参照列的值被从表参照时，主表的该行记录不允许被删除。

检查约束

ALTER TABLE employees4 ADD CONSTRAINT employees4_salary_check CHECK(salary>2000);