如果遇到没有创建的表的解决方案
视图
视图是数据库对象之一,所有数据库对象名字不能重复,所以视图名字一般是以”V”开头的。
视图在SQL语句中体现的角色与表相同,但是视图并不是一张真实存在的表,而只是对应一个SELECT语句的查询结果集,并将其当做表看待而已。
使用视图的目的是简化SQL语句的复杂度,重用子查询,限制数据访问
创建视图
该视图包含的数据为10号部门的员工信息
CREATE VIEW v_emp_10_swm
AS
SELECT empno,ename,sal,deptno
FROM emp_swm
WHERE deptno=10
查看视图数据
SELECT * FROMv_emp_10_swm
等价于
SELECT * FROM
(SELECT empno,ename,sal,deptno
FROM emp_swm WHERE deptno=10)
SELECT * FROMv_emp_10_swm
视图对应的子查询中的字段若含有函数或者表达式,那么该字段必须指定别名。
当视图对应的子查询中的字段使用了别名,那么视图中该字段就用别名来命名。
创建或修改视图
由于视图仅对应一个SELECT语句,所以修改视图就是替换该SELECT语句而已。
CREATE OR REPLACE VIEW v_emp_10_swm
AS
SELECT empno id,ename name,
sal salary,deptno
FROM emp_swm
WHERE deptno=10
DESC v_emp_10
视图的作用
简化复杂查询
限制数据访问
视图的分类
视图分为简单视图和复杂视图
简单视图:
对应的子查询中不含有关联查询,查询的字段不包含函数,表达式等,没有分组,没有去重。
复杂视图
简单视图反之则是复杂视图
对视图进行DML操作
仅能对简单视图进行DML操作。对视图进行DML操作就是对视图数据来源的基础表进行操作
INSERT INTO v_emp_10_swm
(id,name,salary,deptno)
VALUES
(1001,'JACK',2000,10)
SELECT * FROM v_emp_10_swm
SELECT * FROM emp_swm
对视图的DML操作就是对基表操作,那么操作不当就可能对基表进行数据污染。
INSERT INTO v_emp_10_swm
(id,name,salary,deptno)
VALUES
(1002,'ROSE',3000,20)
视图对ROSE不可见
SELECT * FROM v_emp_10_swm
SELECT * FROM emp_swm
更新,同样存在更新后对数据不可控的情况
UPDATE v_emp_10_swm
SET deptno=20
删除不会对基表产生数据污染
DELETE FROM v_emp_10_swm
WHERE deptno=20
视图的约束
WITH CHECK OPTION
为视图添加检查选项,可以保证对视图的DML的操作后视图对其可见,否则不允许进行该DML操作,这样就避免了对基表进行数据污染。
CREATE OR REPLACE VIEW v_emp_10_swm
AS
SELECT empno id,ename name,
sal salary,deptno
FROM emp_swm
WHERE deptno=10
WITH CHECK OPTION
WITH READ ONLY
为视图添加只读选项,那么该视图不允许进行DML操作。
CREATE OR REPLACE VIEW v_emp_10_swm
AS
SELECT empno id,ename name,
sal salary,deptno
FROM emp_swm
WHERE deptno=10
WITH READ ONLY
和视图相关的数据字典:
USER_OBJECTS
在数据字段USER_OBJECTS中查询后缀是 _SWM视图名称
SELECT object_name
FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%_SWM'
USER_VIEWS
在数据字典USER_VIEW中查询指定视图
SELECT TEXT,view_name
FROM user_views
WHERE view_name='V_EMP_10_SWM'
USER_UPDATE_COLUMNS
在数据字典USER_UPDATEBLE_COLUMNS中查询可以更新的列视图
SELECT column_name, insertable, updatable, deletable
FROM user_updatable_columns
WHERE table_name = 'V_EMP_10'
查看表的名称
SELECT table_name
FROM user_tables
复杂视图
创建一个含有公司部门工资情况的视图,内容为:部门编号,部门名称,部门的最高,最低,平均,以及工资总和信息。
CREATE VIEW v_dept_sal_swm
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_swm e JOIN dept_swm d
ON e.deptno=d.deptno
GROUP BY d.deptno,d.dname
查看视图
SELECT * FROM v_dept_sal_swm
查看谁比自己所在部门平均工资高?
SELECT e.ename,e.sal,e.deptno
FROM emp_swm e JOIN v_dept_sal_swm v
ON e.deptno=v.deptno
AND e.sal>v.avg_sal
删除视图
删除视图本身并不会影响基表数据。
但是删除视图数据会将对应基表数据删除
语法:DROP VIEW view_name;
DROP VIEW v_emp_10_swm