视图
数据库对象之一
视图在SQL语句中体现的角色与表相同,但它并非一张真实存在的表,它对应的是一个查询语句的结果集。
创建一个查看10号部门员工信息的视图:
CREATE VIEW v_emp_dept10
AS
SELECT ename,sal,job,deptno
FROM emp
WHERE deptno=10
DESC v_emp_dept10
SELECT *
FROM v_emp_dept10
修改视图就是替换该视图对应的子查询。
使用CREATE OR REPLACE即可。
若视图不存在就创建,存在则替换。
视图对应的子查询的字段若有别名,则该视图对应的字段名就是这个别名。若子查询的字段含有函数或表达式,那么该字段必须给别名。
CREATE OR REPLACE VIEW v_emp_dept10
AS
SELECT empno id,ename name,
sal salary,job,deptno
FROM emp
WHERE deptno=10
对视图进行DML操作就是对视图数据来源的基础表的操作。
INSERT INTO v_emp_dept10
(id,name,salary,job,deptno)
VALUES
(1001,'JACK',3000,'CLERK',10)
SELECT * FROM v_emp_dept10
SELECT * FROM emp
插入数据时,视图看不到的字段都插入基础表对应字段的默认值,所以不能违反基础表相应字段的约束条件,尤其是看不到的字段的非空约束,否则可能导致插入失败。
UPDATE v_emp_dept10
SET name='JACKSON'
WHERE id=1001
SELECT * FROM v_emp_dept10
SELECT * FROM emp
DELETE FROM v_emp_dept10
WHERE id=1001
对视图进行DML操作可能导致对基表数据”污染”
即:对视图进行DML操作的数据视图对其不可见,却修改了基表该数据。
INSERT INTO v_emp_dept10
(id,name,salary,job,deptno)
VALUES
(1001,'JACK',3000,'CLERK',20)
SELECT * FROM v_emp_dept10
SELECT * FROM emp
UPDATE v_emp_dept10
SET deptno=20
为视图添加检查选项可以避免对视图进行DML操作时污染基表。
CREATE OR REPLACE VIEW v_emp_dept10
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_dept10
AS
SELECT empno id,ename name,
sal salary,job,deptno
FROM emp
WHERE deptno=10
WITH READ ONLY
SELECT table_name FROM user_tables
视图根据对应的子查询不同,分为简单视图和复杂视图。当子查询中含有函数,表达式,分组去重,关联查询时,该视图就是一个复杂视图。复杂视图不能进行DML操作。
创建一个包含每个部门薪资情况的视图
CREATE VIEW v_emp_sal
AS
SELECT AVG(e.sal) avg_sal,
SUM(e.sal) sum_sal,
MIN(e.sal) min_sal,
MAX(e.sal) max_sal,
d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname
SELECT * FROM v_emp_sal
查看哪个员工的工资高于其部门平均工资?
SELECT e.ename,e.sal,e.deptno
FROM emp e,v_emp_sal v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal
删除视图
DROP VIEW v_emp_dept10
删除视图中的数据会将基表中对应数据删除,但是删除视图本身不会对基表数据产生影响。
序列
序列也是数据库对象之一
作用是生成一系列数字。通常使用它为某表的主键字段提供值使用。
CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1
序列支持两个伪列:
NEXTVAL:获取序列下一个数字
CURRVAL:获取序列当前数字
NEXTVAL会导致序列发生步进,且序列是不能回退的。CURRVAL是获取序列最后一次生成的数字,新创建的序列至少在调用以此NEXTVAL后才可以使用CURRVAL。
SELECT seq_emp_id.CURRVAL
FROM dual
SELECT * FROM emp
INSERT INTO emp
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_id.NEXTVAL,'JACK',3000,
'CLERK',10)
UUID的生成方式:
SELECT SYS_GUID() FROM dual
删除序列
DROP SEQUENCE seq_emp_id
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
(1,'JACK')