SQL中视图整理

一、视图：

从表中抽出来的逻辑上相关的数据集合
视图其实就是一条查询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 查询

二、在sql中进行增删改

1. 创建修改视图的例子
SQL> CREATE OR REPLACE VIEW salvu41 AS SELECT id, first_name FIRST,
   last_name LAST, salary MONTHLY_SALARY
    FROM s_emp WHERE dept_id = 41;
SQL> CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.name, MIN(e.salary), MAX(e.salary),

AVG(e.salary) FROM s_emp e, s_dept d WHERE e.dept_id = d.id GROUP BY d.name;
注意: 如果用select * from table_name创建的视图
   table_name的结构改变后 view要重建或compile后才能显示新的字段内容

2. 查询视图的数据字典
SQL> set   long   20000 
set linesize 800
set pagesize 100
SQL> select view_name,text from user_views;
说明: 可以根据视图text_length来设置set long 数字;
User_updatable_columns视图能查询视图里能被修改的字段

　　Lesktop开源IM发布以后，有一些网友问及如何在嵌入IM后与自己网站的用户系统整合(即如何让嵌入的IM直接使用网站原有的用户数据库，而不需要将已有的用户数据导入到IM的数据库中)。Lesktop对Users表（存储用户登录名，昵称，密码等信息的表）都是在存储过程中进行增删改的，显然，如果直接去改Users表相关的存储过程是比较麻烦的，本文将介绍一种较为简单的方法，在不需要修改存储过程和源代码的情况下整合用户系统。

　　为实现这个目的，先介绍一下在SQL SERVER中，如何对视图进行增删改。假使用户有Name,Remark两项信息，但是没有存放在同一张表中，而是分开存储在两个表UserBase(ID, Name)，UserExtent(ID, Remark)中。

　　为使用方便，建立一个视图Users，用于表示用户的完整信息，其定义如下：

    
    

     
     CREATE VIEW [dbo].[Users]
as
SELECT b.ID as ID, b.Name as Name, e.Remark as Remark 
FROM UserBase b, UserExtent e 
WHERE b.ID = e.ID;
    
    

　　现在，我们希望通过Users视图进行增删改实现对UserBase，UserExtent表进行修改。显然，如果对Users直接执行insert，update，delete是不可能的，执行时会发生以下错误：

　　在SQL Server中，对视图增删改可以通过触发器来实现，例如我们可以创建一个INSERT触发器，当在视图Users上执行INSERT时，在触发器中实现对UserBase，UserExtent的INSERT操作。在触发器中，可以通过名称为inserted的表，获取到新插入的行，具体代码如下：

    
    

     
     CREATE TRIGGER [dbo].[Users_Insert] ON [dbo].[Users] INSTEAD OF INSERT
as
declare @name nvarchar(32), @remark nvarchar(32)
declare ins_cursor cursor
for
select Name, Remark from inserted
open ins_cursor
fetch next from ins_cursor into @name, @remark;
while(@@fetch_status = 0)
begin
    --读取所有行，并插入
    insert into UserBase (Name) values (@name);
    insert into UserExtent(ID, Remark) values (@@identity, @remark);
    fetch next from ins_cursor into @name, @remark;
end
close ins_cursor
    
    

　　下面我们通过插入两行数据测试触发器：

    
    

     
     --清空所有数据
delete from UserExtent;
delete from UserBase;

create table #temp(
    name nvarchar(32),
    remark nvarchar(32)
)
insert #temp (name,remark) values (N'user1', N'1');
insert #temp (name,remark) values (N'user2', N'2');

--插入两行数据
insert Users(name, remark)
select name,remark from #temp

drop table #temp

select * from Users;
select * from UserBase;
select * from UserExtent;
    
    

　　执行结果如下：

　　创建更新触发器，与INSERT触发器类似，受影响的行会保存在inserted中，可以从inserted表中获取受影响的行，并更新UserBase，UserExtent，具体代码如下：

    
    

     
     CREATE TRIGGER [dbo].[Users_Update] ON [dbo].[Users] INSTEAD OF UPDATE
as
update UserExtent
set UserExtent.Remark=ins.Remark
from inserted ins
where UserExtent.ID = ins.ID;

update UserBase
set UserBase.Name=ins.Name
from inserted ins
where UserBase.ID = ins.ID;
    
    

　　测试代码：

    
    

     
     --清空所有数据
delete from UserExtent;
delete from UserBase;
--插入两行数据
insert Users (name,remark) values (N'user1', N'1');
insert Users (name,remark) values (N'user2', N'2');
insert Users (name,remark) values (N'user3', N'2');
--修改后两行数据
UPDATE Users set Remark = N'3' where Remark = N'2'
--输出数据
select * from Users;
select * from UserBase;
select * from UserExtent;
    
    

　　测试结果：

　　创建删除触发器，在删除的触发器中，可以通过deleted表，获取被删除的行，具体代码如下：

    
    

     
     CREATE TRIGGER [dbo].[Users_Delete] ON [dbo].[Users] INSTEAD OF DELETE
as
delete from UserExtent where ID in (select ID from deleted)
delete from UserBase where ID in (select ID from deleted)
    
    

　　测试代码：

    
    

     
     --清空所有数据
delete from UserExtent;
delete from UserBase;
--插入两行数据
insert Users (name,remark) values (N'user1', N'1');
insert Users (name,remark) values (N'user2', N'2');
insert Users (name,remark) values (N'user3', N'2');
--删除后两行数据
delete from Users where Remark = N'2'
--输出数据
select * from Users;
select * from UserBase;
select * from UserExtent;
    
    

　　运行结果：

　　上文已介绍了如何对视图进行增删改，接下来将介绍如何通过建立视图并添加增删改触发器实现Lesktop开源IM用户系统的整合。首先介绍一下Lesktop开源IM数据库中Users表的结构：

　　假使您的网站的用户表(假使名称为MyUserTable)只有Name,Nickname：

　　那么，您可以建立一张扩展表(假使名称为UserExtentIM)，用于存储其他信息：

　　接下来，您只需要把Users表删掉，重新建立一个名称为Users的视图，然后用上文处理Users,UserBase,UserExtent的方法，在Users视图上建好触发器，在触发器中对MyUserTable，UserExtentIM表进行增删改即可，Lesktop的存储过程对User进行读取和增删改时，将通过触发器自动转换成对MyUserTable，UserExtentIM的操作，因此不需要修改任何存储过程和源代码，当然也不会对你原有的数据库造成影响

三、SQL Server视图管理的四大限制条件
SQL Server视图其优点是非常明显的，但是视图并不是万能的，在使用视图的时候，需要遵守一些限制，下面就让我们一起来了解一下。
SQL Server中通过SQL Server视图来访问数据，其优点是非常明显的，如可以起到数据保密、保证数据的逻辑独立性、简化查询操作等等。
但是，话说回来，SQL Server数据库中的视图并不是万能的，他跟表这个基本对象还是有重大的区别。在使用视图的时候，需要遵守四大限制。

限制条件一：SQL Server视图数据的更改

当用户更新视图中的数据时，其实更改的是其对应的数据表的数据。无论是对视图中的数据进行更改，还是在视图中插入或者删除数据，都是类似的道理。但是，不是所有视图都可以进行更改。如下面的这些视图，在SQL Server数据库中就不能够直接对其内容进行更新，否则，系统会拒绝这种非法的操作。

如在一个视图中，若采用Group By子句，对视图中的内容进行了汇总。则用户就不能够对这张视图进行更新。这主要是因为采用Group By子句对查询结果进行汇总在后，视图中就会丢失这条纪录的物理存储位置。如此，系统就无法找到需要更新的纪录。若用户想要在视图中更改数据，则数据库管理员就不能够在视图中添加这个Group BY分组语句。

如不能够使用Distinct关键字。这个关键字的用途就是去除重复的纪录。如没有添加这个关键字的时候，视图查询出来的纪录有250条。添加了这个关键字后，数据库就会剔除重复的纪录，只显示不重复的50条纪录。此时，若用户要改变其中一个数据，则数据库就不知道其到底需要更改哪条纪录。因为视图中看起来只有一条纪录，而在基础表中可能对有的纪录有几十条。为此，若在视图中采用了Distinct关键字的话，就无法对视图中的内容进行更改。


如果在SQL Server视图中有AVG、MAX等函数，则也不能够对其进行更新。如在一张视图中，其采用了SUN函数来汇总员工的工资时，此时，就不能够对这张表进行更新。这是数据库为了保障数据一致性所添加的限制条件。


可见，试图虽然方便、安全，但是，其仍然不能够代替表的地位。当需要对一些表中的数据进行更新时，我们往往更多的通过对表的操作来完成。因为对视图内容进行直接更改的话，需要遵守一些限制条件。在实际工作中，更多的处理规则是通过前台程序直接更改后台基础表。至于这些表中数据的安全性，则要依靠前台应用程序来保护。确保更改的准确性、合法性。

限制条件二：定义视图的查询语句中不能够使用某些关键字

我们都知道，视图其实就是一组查询语句组成。或者说，视图是封装查询语句的一个工具。在查询语句中，我们可以通过一些关键字来格式化显示的结果。如我们在平时工作中，经常会需要把某张表中的数据跟另外一张表进行合并。此时，数据库管理员就可以利用Select Into语句来完成。先把数据从某个表中查询出来，然后再添加到某个表中。

当经常需要类似的操作时，我们是否可以把它制作成一张视图。每次有需要的时候，只需要运行这个视图即可，而不用每次都进行重新书写SQL代码。不过可惜的是，结果是否定的。在SQL Server数据库的视图中，是不能够带有Into关键字。如果要实现类似的功能，只有通过函数或者过程来实现。

另外，跟Oracle数据库不同的是，在微软的SQLServer数据库中创建视图的时候，还有一个额外的限制。就是不能够在创建视图的查询语句中，使用order by排序语句。这是一个很特殊的规定。一些Oracle的数据库管理员，在使用SQL Server数据库创建视图的时候，经常会犯类似的错误。他们就搞不明白，为什么Oracle数据库中可行，但是在微软的数据库中则行不通呢?这恐怕只有微软数据库产品的设计者才能够回答的问题。总之我们要记住的就是，在SQLServer数据库中，建立视图时，查询语句中不能够包含Order By语句。

限制条件三：要对某些列取别名，并保证列名的唯一

在表关联查询的时候，当不同表的列名相同时，只需要加上表的前缀即可。不需要对列另外进行命名。但是，在创建视图时就会出现问题，数据库会提示 “duplicate column name”的错误提示，警告用户有重复的列名。有时候，用户利用Select语句连接多个来自不同表的列，若拥有相同的名字，则这个语句仍然可以执行。但是，若把它复制到创建视图的窗口，创建视图时，就会不成功。

查询语句跟创建SQL Server视图的查询语句还有很多类似的差异。如有时候，我们在查询语句中，可能会比较频繁的采用一些算术表达式;或者在查询语句中使用函数等等。在查询的时候，我们可以不给这个列“取名”。数据库在查询的时候，会自动给其命名。但是，在创建视图时，数据库系统就会给你出难题。系统会提醒你为列取别名。

从以上两个例子中，我们可以看出，虽然视图是对SQL语句的封装，但是，两者仍然有差异。创建视图的查询语句必须要遵守一定的限制。如要保证视图的各个列名的唯一;如果自阿视图中某一列是一个算术表达式、函数或者常数的时候，要给其取名字，等等。

限制条件四：权限上的双重限制

为了保障基础表数据的安全性，在视图创建的时候，其权限控制比较严格。

一方面，若用户需要创建SQL Server视图，则必须要有数据库视图创建的权限。这是视图建立时必须遵循的一个基本条件。如有些数据库管理员虽然具有表的创建、修改权限;但是，这并不表示这个数据库管理员就有建立视图的权限。恰恰相反，在大型数据库设计中，往往会对数据库管理员进行分工。建立基础表的就只管建立基础表;负责创建视图的就只有创建视图的权限。

其次，在具有创建视图权限的同时，用户还必须具有访问对应表的权限。如某个数据库管理员，已经有了创建视图的权限。此时，若其需要创建一张员工工资信息的视图，还不一定会成功。这还要这个数据库管理员有美誉跟工资信息相关的基础表的访问权限。如建立员工工资信息这张视图一共涉及到五张表，则这个数据库管理员就需要拥有者每张表的查询权限。若没有的话，则建立这张视图就会以失败告终。

第三，就是视图权限的继承问题。如上面的例子中，这个数据库管理员不是基础表的所有者。但是经过所有者的授权，他就可以对这个基础表进行访问，就可以以此为基础建立视图。但是，这个数据库管理员有没有把对这个基础表的访问权限再授权给其他人呢?如他能否授权给A用户访问员工考勤信息表呢?答案是不一定。默认情况下，数据库管理员不能够再对其他用户进行授权。但是，若基础表的所有者，把这个权利给了数据库管理员之后，则他就可以对用户进行重新授权。让数据库管理员可以给A用户进行授权，让其可以进行相关的操作。

可见，视图虽然灵活，安全，方便，但是其仍然有比较多的限制条件。根据笔者的经验，一般在报表、表单等等工作上，采用视图会更加的合理。因为其 SQL语句可以重复使用。而在基础表更新上，包括纪录的更改、删除或者插入上，往往是直接对基础表进行更新。对于一些表的约束，可以通过触发器、规则等等来实现;甚至可以通过前台SQL语句直接实现约束。作为数据库管理员，要有这个能力，能够判断在什么时候使用视图，什么时候直接调用基础表。