oracle的视图，序列， 索引，约束知识点

数据库对象
数据库对象包含:表,视图,索引,序列

视图VIEW

视图在SQL语句中体现的角色与表一样。
但是视图并非真实存在的表，它只是对应
一条查询语句的结果集。

使用视同通常是为了重用子查询，简化
SQL语句的复杂度和限制某些数据的访问。

创建一个包含10号部门员工信息的视图:
CREATE VIEW v_emp_10
AS
SELECT empno,ename,sal,job,deptno
FROM emp
WHERE deptno=10

视图也可以查看结构:
DESC v_emp_10

SELECT * FROM v_emp_10

SELECT *
FROM(SELECT empno,ename,sal,job,deptno
FROM emp
WHERE deptno=10)

视图中对应的子查询中若含有函数或
表达式，那么该字段必须给别名。并且
字段的别名会成为该视图对应字段的名字

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

SELECT * FROM v_emp_10

针对视图的DML操作

复杂视图不能进行DML操作
对简单视图进行DML操作就是对该视图数据
来源的基础表进行的。

INSERT INTO v_emp_10
(id,name,salary,job,deptno)
VALUES
(1001,’JACK’,2000,’CLERK’,10)

SELECT * FROM v_emp_10
SELECT * FROM emp

UPDATE v_emp_10
SET salary=3000
WHERE id=1001

DELETE FROM v_emp_10
WHERE id=1001

**对视图进行DML操作是可能会污染基础表数据
即:对视图进行DML操作后，视图对该数据不可见**

INSERT INTO v_emp_10
(id,name,salary,job,deptno)
VALUES
(1001,’JACK’,2000,’CLERK’,20)

UPDATE v_emp_10
SET deptno=20

SELECT * FROM v_emp_10
SELECT * FROM emp

删除不会污染
DELETE FROM v_emp_10
WHERE deptno=20

为了避免不当的DML操作会污染基表，
可以为视图添加检查选项:
WITH CHECK OPTION
当视图添加了该选项后，那么对视图
进行DML操作时，视图会检查执行该
操作后视图是否对操作的记录可见，
不可见则不允许该DML操作。
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,
sal salary,job,deptno
FROM emp
WHERE deptno=10
WITH CHECK OPTION

为视图添加只读选项后，该视图不允许进行DML操作
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,
sal salary,job,deptno
FROM emp
WHERE deptno=10
WITH READ ONLY

数据字典可以查看用户创建的数据库对象信息
SELECT object_name,object_type
FROM user_objects
WHERE object_name LIKE ‘%_fcq’

SELECT view_name,text
FROM user_views

SELECT table_name
FROM user_tables

创建复杂视图
当视图对应的SQL语句含有函数，表达式
分组，去重或关联查询时，该视图为复杂
视图。
复杂视图不允许进行DML操作。

创建一个含有每个部门薪资情况的视图
CREATE VIEW v_dept_sal
AS
SELECT
MAX(e.sal) max_sal,
MIN(e.sal) min_sal,
AVG(e.sal) avg_sal,
SUM(e.sal) sum_sal,
d.deptno,d.dname
FROM
emp e,dept d
WHERE
e.deptno=d.deptno
GROUP BY
d.deptno,d.dname

SELECT * FROM v_dept_sal

查看谁的工资高于所在部门的平均工资?
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 BY 1

序列提供了两个伪列用于取数字
NEXTVAL:获取序列下一个数字。
序列会根据序列最后生成的数字加上步长
来得到。NEXTVAL会导致序列步进。
序列是不能回退的，发生步进后之前的数字
就无法再次获取。

CURRVAL:获取序列最后生成的数字。
在使用CURRVAL之前应至少调用过一次
NEXTVAL后才可以使用。

SELECT seq_emp_id.NEXTVAL
FROM dual

使用seq_emp_id为emp表提供主键值：
INSERT INTO emp
(empno,ename,job,sal,deptno)
VALUES
(seq_emp_id.NEXTVAL,’JACK’,’CLERK’,2000,10)

SELECT * FROM emp

删除序列
DROP SEQUENCE seq_emp_id

---

INDEX索引

索引是为了提高查询效率
索引的实现是数据库内部完成。
create index idx_emp_ename_ggf on tb_emp_ggf(empname);

select * from tb_emp_ggf where empname = ‘SCOTT’;

创建复合索引
create index idx_emp_job_sal_ggf on tb_emp_ggf(job, sal);
在进行排序的时候如果使用了复合索引，顺序需要和定义索引的顺序一样
select empno, empname, sal, job from tb_emp_ggf order by job, sal;

为一个函数添加索引
create index emp_upper_ename on tb_emp_ggf(upper(empname));

select * from tb_emp_ggf where upper(empname) = ‘KING’;

重建索引
如果经常再索引列上执行DML操作，就需要重建索引

alter index emp_upper_ename rebuild;

删除索引
drop index emp_upper_ename;

---

约束

非空约束（NOT NULL）
添加非空约束的两种方式

create table tb_employees(
eid number(6),
name varchar2(30) not null,
salary number(7,2),
hiredate date constraint emply_hiredate_nn not null
);

desc tb_employees;

修改表时添加非空约束
alter table tb_employees modify (eid number(6) not null);

取消非空约束
alter table tb_employees modify (eid number(6) null);

drop table tb_employees;

唯一性约束（UNIQUE）
唯一性约束可以保证字段的值在整张表
中每条记录都不一样，NULL除外。
CREATE TABLE employees1 (
eid NUMBER(6) UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE,
CONSTRAINT employees1_email_uk UNIQUE(email)
);

INSERT INTO employees1
(eid,name,email)
VALUES
(NULL,’JACK’,NULL)

SELECT * FROM employees1

主键约束
非空且唯一，且一张表只能有一个字段添加
主键约束。
CREATE TABLE employees2 (
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE
)

INSERT INTO employees2
(eid,name)
VALUES
(NULL,’JACK’)

check约束
约束数据的范围

alter table tb_employees
add constraint emple_sal_check check (salary > 2000);

insert into tb_employees(eid,salary) values (2,3000);
desc tb_employees;
select * from tb_employees;