Oracle基础(十五):视图、数据字典、复杂视图
一、视图
视图 VIEW,是数据库对象之一。
视图也被成为虚表,即虚拟的表,是一组数据逻辑表示。
视图对应于一条SELECT语句,结果集被赋予一个名字,即视图的名字。
视图本身并不包含任何数据,它只包含映射到基表的一个查询语句,当基表数据发生变化,视图数据也随之变化。
使用视图的目的是简化SQL语句的复杂度,重用子查询,限制数据访问。
在SQL语句中体现的角色与表一致,但视图只是对应一个查询语句的结果集。
视图创建后,可以像操作表一样操作视图,主要是查询。
SubQuery是SELECT查询语句,对应的表被称为基表。
根据视图所对应的子查询种类分为几种类型:
1.简单视图:SELECT语句是基于单表建立的,且不包含任何函数运算、表达式或分组函数,叫做"简单视图",此时视图是基表的子集。
(只有简单视图可以实现DML操作)
2.复杂视图:SELECT语句同样是基于单表,但包含了单行函数、表达式、分组函数或者GROUP BY子句,叫做"复杂视图"。
3.连接视图:SELECT语句是基于多个表的,叫做"连接视图"。
(1)创建视图
语法:CREATE VIEW
视图名 AS SELECT
xxx ....;
①创建简单视图
--简单视图,创建一个简单视图,该视图包含的数据为10号部门员工的编号、名字、薪水、部门号 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;--查看视图数据(重用子查询),相当于以下的子查询 --SUBQUERY SELECT * FROM(SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10);
②创建视图时,给列赋予别名。
可以用OR REPLACE
短语修改视图对应的SQL查询语句
视图对应的子查询中的字段若含有函数或者表达式,那么该字段必须指定别。
当视图对应的子查询中的字段使用了别名,那么视图中该字段就用别名来命名。
修改视图
由于视图仅对应一个SELECT语句,所以修改视图就是替换该SELECT语句而已.
(若存在视图则修改,不存在则创建)
语法:CREATE OR REPALCE VIEW 视图名 AS SELECT xxx....;
create or replace view v_emp_10 as select empno id,ename name,sal salary,deptno from emp where deptno=10; select * from v_emp_10; desc v_emp_10;
视图分为简单视图与复杂视图
简单视图:对应的子查询中不含有关联查询,查询的字段不包含函数,
表达式等,没有分组,没有去重.反之则是复杂视图.
③对简单视图进行DML操作
对视图进行DML操作就是对视图数据来源的基础表进行的操作(但不能违反基础表的约束条件)。
只能对简单视图进行DML操作,复杂视图不可以。
--对简单视图v_emp_10执行INSERT语句,插入数据操作 insert into v_emp_10(id,name,salary,deptno) values(1001,'jack',2000,10); select * from v_emp_10; select * from emp; --操作v_emp_10视图,把jack的工资改为3000 update v_emp_10 set salary=3000 where name='jack'; --操作v_emp_10视图,删除name是jack的记录 delete v_emp_10 where name='jack'; --根据上面对视图进行DML操作就是对基表操作,进行DML操作不当可能会污染基础表数据。 --操作v_emp_10视图,该INSERT语句插入了20号部门数据,所以v_emp_10视图对ROSE不可见。 insert into v_emp_10(id,name,salary,deptno) values(1003,'ROSE',3000,20); --UPDATE同样存在更新后对数据不可控的情况 UPDATE v_emp_10 SET deptno=20; rollback; select * from v_emp_10; select * from emp; --从视图中删除20号部门的记录 --删除不会对基表产生数据污染,因为该视图看不到20号部门的数据。 delete from v_emp_10 where deptno=20;--脚本输出:0 行已删除。
④创建具有 CHECK OPTION约束的视图
语法:
CREATE [OR REPLACE] VIEW 视图名 AS subquery [WITH CHECK OPTION];
WITH CHECK OPTION
表示通过视图所作的修改,必须在视图可见的范围内。
假设进行INSERT
操作,新增的记录在视图仍可查看(但可能会违反基表的非空约束)。
假设进行UPDATE
操作,修改后的结果必须能通过视图查看到。
可以为视图添加检查选项,来保证对视图进行DML操作时不会对基表数据污染。
当视图添加了检查选项后,视图要求对视图中数据进行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;
--插入20号部门数据,ORA-01402: 视图 WITH CHECK OPTION where 子句违规 --该记录对视图不可见 insert into v_emp_10(id,name,salary,deptno) values(1003,'ROSE',3000,20); --插入10号部门数据,对视图可见 INSERT INTO v_emp_10(id,name,salary,deptno) VALUES (1001,'JACK',2000,10); --更新为20部门,错误报告 -ORA-01402: 视图 WITH CHECK OPTION where 子句违规, --因为该记录对视图不可见 UPDATE v_emp_10 SET deptno=20;--出错,该记录对视图不可见 select * from v_emp_10; select * from emp; rollback;
⑤创建具有READ ONLY约束的视图
对简单视图进行DML操作时合法的,但这是不安全的。
如果没有在视图上执行DML操作的必要,那么在建立视图时声明为"只读"来避免这种情况,
保证视图对应的基表数据不会被非法修改 。
语法:CREATE [OR REPLACE ] VIEW 视图名 AS subquery [WITH READ ONLY]; create or replace view v_emp_10 as select empno id,ename name,sal salary,deptno from emp where deptno=10 with read only; --为视图添加只读选项,当一个视图添加了只读选项后,该视图不能进行DML操作。 INSERT INTO v_emp_10(id,name,salary,deptno) VALUES(1001,'JACK',2000,10);--SQL 错误: ORA-42399: 无法对只读视图执行 DML 操作
二、数据字典
查看数据字典(特殊的表),有助于了解曾经创建过的数据库对象。
和视图相关的数据字典:USER_OBJECTS
数据库对象表 USER_VIEWS
通过查询user_views获取相关信息USER_UPDATE_COLUMNS
USER_TABLES
--在数据字典USER_OBJECTS中查询所有视图名称 desc USER_OBJECTS; select object_name from user_objects where object_type='VIEW'; select object_name from user_objects where object_type='TABLE'; desc user_views; select view_name,text from user_views;--text字段对应的是视图的子查询 desc user_tables; select table_name from user_tables;
三、复杂视图
复杂视图不能进行DML操作。
SELECT语句同样是基于单表,但包含了单行函数、表达式、分组函数或GROUP BY子句,叫做复杂视图。
--创建一个公司部门工资信息的视图,内容为:部门号,部门名,部门最高、最低、平均以及工资总和信息。 create or replace view v_dept_sal 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 e join dept d on e.deptno=d.deptno group by d.deptno,d.dname; desc v_dept_sal; select * from v_dept_sal; --通过视图v_dept_sal查看谁的工资高于自己所在部门平均工资? select e.ename,e.sal,e.deptno,v.deptno,v.avg_sal from emp e join v_dept_sal v on e.deptno=v.deptno where e.sal>v.avg_sal; --删除视图,当不再需要视图定义,可以使用DROP VIEW viewName语句删除视图。 --删除视图本身不会影响基表数据,但是删除视图数据会对应将基表数据删除! drop view v_dept_sal;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)