专栏首页乐沙弥的世界SQL 基础--> 视图(CREATE VIEW)

SQL 基础--> 视图(CREATE VIEW)

--================================

-- SQL 基础--> 视图(CREATE VIEW)

--================================

视图:

从表中抽出来的逻辑上相关的数据集合

视图其实就是一条查询SQL语句,用于显示一个或多个表或其它视图中相关数据。

视图将查询的结果作为一个表来使用,因此视图可以被看作是存储的查询或一个虚拟表

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

在基表上的触发器。(Oracle支持在视图上显式的定义触发器和定义一些逻辑约束)

使用视图的好处:

可把复杂的SQL语句简单化

可保证数据的安全性,限制对数据的访问,因为它对表中的一些字段是隐藏的

可使相同的数据以不同形式出现在不同的视图中

视图分类:

简单视图

只从一个表中获取数据

不包含函数

不包含分组数据

可通过该视图进行DML操作

复杂视图

从多个表中获取数据

包含函数

包含分组数据

不一定能通过视图进行DML操作

创建视图:

CREATE [OR REPLACE] [FORCE |NOFORCE ]VIEW view_name [alias [,alias]...]

AS subquery

[WITH CHECK OPTION]

[WITH READ ONLY]

OR REPLACE 如果视图存在,重建、修改这个视图

FORCE 不管引用的表是否存在,都创建这个视图

NOFORCE 只有当表存在的时候,才能创建这个视图(默认方式)

WITH CHECK OPTION 只有子查询能够检索出的行才能够被插入,修改,或删除。默认情况下对此不作检查

没有指定约束名,系统会自动为约束命名,形式为SYS_Cn。

WITH READ ONLY 只读,不可对视图做DML操作

需要注意的是,在子查询中不能包含ORDER BY ,子查询可以是复杂的SELECT语句

修改视图

使用CREATE OR REPLACE VIEW 子句修改视图

删除视图:

DROP VIEW view_name

视图中使用DML的规定:

当视图定义中含有以下元素之一不能使用INSERT

组函数

GROUP BY、ORDER BY

DISTINCT

ROWNUM

列的定义为表达式

表中非空的列,在视图定义中未包括

视图定义含有以下元素不能使用UPDATE

组函数

GROUP BY、ORDER BY

DISTINCT

ROWNUM

列的定义为表达式

视图包含以下元素不能DELETE

组函数

GROUP BY、ORDER BY

DISTINCT

ROWNUM

与视图有关的数据字典:

DBA_VIEWS

USER_VIEWS

--演示创建视图

SQL> create or replace view vw_emp as

2 select empno,ename,sal from emp where sal >2500;

View created.

SQL> select * from vw_emp;

EMPNO ENAME SAL

---------- ---------- ----------

7566 JONES 2975

7698 BLAKE 2850

7788 SCOTT 3000

7839 KING 5000

7902 FORD 3000

SQL> update vw_emp set sal = 3500 where ename = 'FORD'; --可以更新并且直接修改了基表

1 row updated.

SQL> select * from emp where ename = 'FORD';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7902 FORD ANALYST 7566 03-DEC-81 3500 20

SQL> insert into vw_emp select 9999,'Robinson',6000 from dual; --可以插入并且直接修改了基表

1 row created.

SQL> select * from emp where empno = 9999;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

9999 Robinson 6000

1 rows selected.

SQL> delete from emp where ename = 'Robinson'; --可以删除并且直接修改了基表

1 row deleted.

SQL> select * from vw_emp;

EMPNO ENAME SAL

---------- ---------- ----------

7566 JONES 2975

7698 BLAKE 2850

7788 SCOTT 3000

7839 KING 5000

7902 FORD 3500

SQL> rollback;

Rollback complete.

--使用with check option

--使用WITH CHECK OPTION 子句确保DML只能在特定的范围内执行,任何违反

--WITH CHECK OPTION 约束的请求都会失败

SQL> create or replace view vw_emp

2 as

3 select empno,ename,sal from emp where sal > 2500

4 with check option;

View created.

SQL> insert into vw_emp select 9999,'Robinson',2000 from dual; --不满足条件sal > 2500 不可插入

insert into vw_emp select 9999,'Robinson',2000 from dual

*

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> select * from vw_emp;

EMPNO ENAME SAL

---------- ---------- ----------

7566 JONES 2975

7698 BLAKE 2850

7788 SCOTT 3000

7839 KING 5000

7902 FORD 3000

SQL> delete from vw_emp where empno = 7902; -- 满足条件sal > 2500 可以删除,并且修改了基表

1 row deleted.

SQL> select * from vw_emp;

EMPNO ENAME SAL

---------- ---------- ----------

7566 JONES 2975

7698 BLAKE 2850

7788 SCOTT 3000

7839 KING 5000

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7900 JAMES CLERK 7698 03-DEC-81 950 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

13 rows selected.

SQL> rollback;

Rollback complete.

SQL> select * from vw_emp;

EMPNO ENAME SAL

---------- ---------- ----------

7566 JONES 2975

7698 BLAKE 2850

7788 SCOTT 3000

7839 KING 5000

7902 FORD 3000

SQL> update vw_emp set sal = 2000 where empno = 7499; --不满足条件,不能更新

0 rows updated.

--WITH READ ONLY 屏蔽DML 操作

--可以使用WITH READ ONLY 选项屏蔽对视图的DML操作

--任何DML 操作都会返回一个Oracle server 错误

SQL> create or replace view vw_emp

2 as

3 select empno,ename,sal from emp where sal > 2500

4 with read only;

View created.

SQL> insert into vw_emp select 9901,'Robinson',3000 from dual;

insert into vw_emp select 9901,'Robinson',3000 from dual

*

ERROR at line 1:

ORA-01733: virtual column not allowed here

SQL> select * from vw_emp;

EMPNO ENAME SAL

---------- ---------- ----------

7566 JONES 2975

7698 BLAKE 2850

7788 SCOTT 3000

7839 KING 5000

7902 FORD 3000

SQL> update vw_emp set sal = 8000 where empno = 7902;

update vw_emp set sal = 8000 where empno = 7902

*

ERROR at line 1:

ORA-01733: virtual column not allowed here

SQL> delete from vw_emp where empno = 7566;

delete from vw_emp where empno = 7566

*

ERROR at line 1:

ORA-01752: cannot delete from view without exactly one key-preserved table

--创建复杂视图

SQL> create view vw_sum_emp(name,minsal,maxsal,avgsal)

2 as

3 select dname,min(e.sal),max(e.sal),avg(e.sal)

4 from emp e

5 join dept d

6 on e.deptno = d.deptno

7 group by dname;

View created.

SQL> select * from vw_sum_emp;

NAME MINSAL MAXSAL AVGSAL

-------------- ---------- ---------- ----------

ACCOUNTING 1300 5000 2916.66667

RESEARCH 800 3000 2175

SALES 950 2850 1566.66667

--查询与视图有关的数据字典

SQL> select view_name,text from user_views;

VIEW_NAME TEXT

------------------------------ --------------------------------------------------------------------------------

VW_SUM_EMP select dname,min(e.sal),max(e.sal),avg(e.sal)

from emp e

join dept d

on e.deptno = d.deptno

group by dname

VW_EMP select empno,ename,sal from emp where sal > 2500

with read only

更详细的创建视图的语法:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#SQLRF01504

更多参考:

Oracle 数据库实例启动关闭过程

Oracle 10g SGA 的自动化管理

使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例

Oracle实例和Oracle数据库(Oracle体系结构)

SQL 基础-->常用函数

SQL基础-->过滤和排序

SQL 基础-->SELECT 查询

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 使用RMAN迁移文件系统数据库到ASM

    在实际的工作过程中,由于ASM磁盘管理的便利性,因此很多时候需要将文件系统的数据库迁移到ASM,本文演示了如何将文件系统数据库迁移到ASM实例。

    Leshami
  • PL/SQL --> 游标

    映射在结果集中某一行数据的具体位置,类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行,然后根据业务需求

    Leshami
  • MySQL视图

    视图是关系型数据库重要的组成部分之一,它可以限制数据访问,简化复杂查询,保持数据的独立性,以及基于相同的数据提供不同的视图等等。本文介绍MySQL数据库视图的一...

    Leshami
  • Nature:摩尔定律时代落幕(深度解析)

    编者按:芯片业喊 “狼来了” 已经有将近 20年 了,但摩尔定律似乎每一次都能化险为夷,维持存在。但是这次不一样了,随着产业在技术、经济层面摸到的天花板越来越多...

    新智元
  • 7 Papers & Radios | 超图表示学习新框架HNHN;GNN的泛化与表示极限

    论文 1:Implicit Neural Representations with Periodic Activation Functions

    机器之心
  • 存储过程和触发器的应用

    L宝宝聊IT
  • 来自一线企业的使用公共云服务的10大技巧

    本文是通用电气、联邦快递、美国银行、摩根大通和摩根士丹利等一线企业使用云计算的方式一览。 通用电气、花旗集团、联邦快递、美国银行、Intuit公司、Gap、Ka...

    静一
  • 7 Papers & Radios | 迪士尼百万像素级换脸;语义分割损失函数综述

    论文1:Discovering Symbolic Models from Deep Learning with Inductive Biases

    机器之心
  • 7 Papers & Radios | 微软亚研麻将AI「Suphx」技术细节;港中文、商汤动作识别时序金字塔网络

    论文 1:Background Matting: The World is Your Green Screen

    机器之心
  • 自学Python十一 Python爬虫总结

      通过几天的学习与尝试逐渐对python爬虫有了一些小小的心得,我们渐渐发现他们有很多共性,总是要去获取一系列的链接,读取网页代码,获取所需内容然后重复上面的...

    叁金

扫码关注云+社区

领取腾讯云代金券