1. 视图
视图是数据库对象之一,数据库对象有: 表 , 视图 , 索引 , 序列
视图在SLQ中的体现的角色与表相同,但是并非真实的表,它不包含任何数据。它只是对应一个查询语句的结果集
所以查询一张视图时,先会执行该视图对应的查询语句得到结果集,然后再根据这个结果集进行实际的查询工作。
简单视图
视图数据来自单一的一张表,且不含有函数或表达式
复杂视图
查询语句含有函数,表达式,分组等
连接视图
视图数据来自多张表,算是复杂视图的一种
创建一个简单视图 , 显示部门 10 的员工信息
CREATE OR REPLACE VIEW v_emp_dept10
AS
SELECT empno id, ename name, sal salary,deptno
FROM emp
WHERE 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_dept10
SET salary = 9000
WHERE 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_dept10 SET deptno = 20;
如何防止上述情况发生?对视图添加检查选项
添加了检查选项的视图要求对视图进行 DML 操作后,视图必须对其可控:
INSERT 后视图必须可见,UPDATE 后视图也必须对其可见,DELETE 不影响
创建视图 指定检查选项 ( WITH CHECK OPTION)
CREATE OR REPLACE VIEW v_emp_dept10
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_dept10
AS
SELECT empno id, ename name, sal salary,deptno
FROM emp
WHERE deptno = 10
WITH READ ONLY
3 . 序列
数据库对象之一,用来生成一系列数字,通常序列生成的数字是为某张表的主键字段(id)提供值的
创建 从1000 开始,每次自增 10 的序列
CREATE SEQUENCE seq_emp_empno
START WITH 1000
INCREMENT 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))
在查询 索引 所绑定的字段时,数据库管理软件会自动调用索引以提高效率。
注意:索引不宜创建过多,表的数据量不大时不推荐创建索引。