【T-MAC个人学习笔记15之--浅谈视图】

Question

视图一个没有索引的视图其实只是一个虚拟表,没有任何物理的数据,它就是一段代码组成的东西,包含了描述它的元数据,如结构,依赖性等.
1.它可以对基础数据做些聚合之类的操作后提供使用.
2.它可以筛选数据,让客户只能访问视图以达到保护原表的目的.
3.同样,视图不能包含单独的ORDER BY .因为它被认为是一个表,表是逻辑实体,本身里面的数据时随便放的,并米有刻意按照某个顺序存储数据.说几点关于视图的东西1.视图中的ORDER BY
视图中的ORDER BY 绝非它本意--它不起排序的作用。
平时我们用 select top n * from tb order by col1 这里的order by 不仅排序还确定要返回哪几行
视图我们用 select top n * from tb order by col1 这里的ORDER BY 只能用来确定希望返回哪几行.
因为我们的视图最后出来的东西本质上是个表,这个时候你去查询这个视图的时候 出来的结果可不一定是按照COL1排序的 
因为视图它就是个表 表是逻辑实体,本身里面的数据时随便放的.看实例：
create table k (a int , b int)
insert k select 1,2 
insert k select 2,6 
insert k select 3,7 
insert k select 4,2 
insert k select 5,9
gp
create view v_showk
as
select top 100 percent *
from k 
order by b  
go
select * from v_showk
/*
a           b
----------- -----------
1           2
2           6
3           7
4           2
5           9
*/
-------结果并没有按照你期待的B排序 这就说明视图里的order by 是没有起到排序作用的----------------

2.视图刷新的问题 sp_refreshview
有时候你会发现当你修改基础表的结构后,视图并没有跟着变,解决的方法就是修改后立即进行视图的刷新.特别是你试图出现select *的时候
USE tempdb;
GO
IF OBJECT_ID('dbo.V1') IS NOT NULL
  DROP VIEW dbo.V1;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO

CREATE TABLE dbo.T1(col1 INT, col2 INT);
INSERT INTO dbo.T1(col1, col2) VALUES(1, 2);
GO
--建立视图
CREATE VIEW dbo.V1
AS
SELECT * FROM dbo.T1;
GO
-- 查看视图
SELECT * FROM dbo.V1;
GO
/*
col1        col2
----------- -----------
1           2
*/
--修改表架构
ALTER TABLE dbo.T1 ADD col3 INT;
GO
--再次查看视图
SELECT * FROM dbo.V1;
GO
/*
col1        col2
----------- -----------
1           2
*/
--发现修改后的结构没有出现在眼前,刷新视图
EXEC sp_refreshview 'dbo.V1';
GO
--再次查看
SELECT * FROM dbo.V1;
GO
/*
col1        col2        col3
----------- ----------- -----------
1           2           NULL
*/
 ------------这里分享个一次刷新所有视图的代码（书上）-----------
 --刷新所有视图
SELECT N'EXEC sp_refreshview '
     + QUOTENAME(VIEW_NAME, '''') + ';' AS cmd
FROM (SELECT QUOTENAME(TABLE_SCHEMA)
       + N'.' + QUOTENAME(TABLE_NAME) AS VIEW_NAME
     FROM INFORMATION_SCHEMA.VIEWS) AS V
WHERE OBJECTPROPERTY(OBJECT_ID(VIEW_NAME), 'IsSchemaBound') = 0;
GO3.视图的模块化解题
如果一个题目解题思路写成一个语句比较长 比较难读 可以尝试用视图分开它 依次攻破 --(书上的例子,懒了下,盗用数据说明问题)
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Sales') IS NOT NULL
  DROP TABLE dbo.Sales;
GO
CREATE TABLE dbo.Sales
(
  mnth DATETIME NOT NULL PRIMARY KEY,
  qty  INT      NOT NULL
);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20041201', 100);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050101', 110);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050201', 120);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050301', 130);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050401', 140);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050501', 140);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050601', 130);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050701', 120);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050801', 110);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20050901', 100);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20051001', 110);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20051101', 100);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20051201', 120);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060101', 130);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060201', 140);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060301', 100);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060401', 100);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060501', 100);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060601', 110);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060701', 120);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060801', 110);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20060901', 120);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20061001', 130);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20061101', 140);
INSERT INTO dbo.Sales(mnth, qty) VALUES('20061201', 100);
GO
-----目标结果-----根据本月于上个月差来判断销售趋势
/*
start_range end_range trend
----------- --------- -------
200412      200412    unknown
200501      200504    up
200505      200505    same
200506      200509    down
200510      200510    up
200511      200511    down
200512      200602    up
200603      200603    down
200604      200605    same
200606      200607    up
200608      200608    down
200609      200611    up
200612      200612    down
*/
--VSalesRN视图给每个月上编号
IF OBJECT_ID('dbo.VSalesRN') IS NOT NULL
  DROP VIEW dbo.VSalesRN;
GO
CREATE VIEW dbo.VSalesRN
AS
SELECT mnth, qty, ROW_NUMBER() OVER(ORDER BY mnth) AS rn
FROM dbo.Sales;
GO
--VSgn视图利用sign函数和自连接,求出sgn标识趋势
IF OBJECT_ID('dbo.VSgn') IS NOT NULL
  DROP VIEW dbo.VSgn;
GO
CREATE VIEW dbo.VSgn
AS
SELECT Cur.mnth, Cur.qty, isnull(SIGN(Cur.qty - Prv.qty),2) AS sgn
FROM dbo.VSalesRN AS Cur 
  left  JOIN dbo.VSalesRN AS Prv
    ON Cur.rn = Prv.rn + 1;
 --VGrp视图根据对标识的分组,取出最后标定范围的分组因子grp 
IF OBJECT_ID('dbo.VGrp') IS NOT NULL
  DROP VIEW dbo.VGrp;
GO
create  VIEW dbo.VGrp
AS

SELECT mnth, sgn,
  DATEADD(month,
    -ROW_NUMBER() OVER(PARTITION BY sgn ORDER BY mnth),
    mnth) AS grp
FROM dbo.VSgn;
GO
--VTrends视图 根据sgn 和 grp分组 求出自己范围内的最大最小值 并且标识出对应的中文表示
IF OBJECT_ID('dbo.VTrends') IS NOT NULL
   DROP VIEW dbo.VTrends;
GO
CREATE VIEW dbo.VTrends
AS

SELECT 
  CONVERT(VARCHAR(6), MIN(mnth), 112) AS start_range,
  CONVERT(VARCHAR(6), MAX(mnth), 112) AS end_range,
  CASE sgn
    WHEN -1 THEN 'down'
    WHEN  0 THEN 'same'
    WHEN  1 THEN 'up'
    ELSE  'unknown'
  END AS trend
FROM dbo.VGrp
GROUP BY sgn, grp;
----------检索视图------------
SELECT start_range, end_range, trend
FROM dbo.VTrends
ORDER BY start_range;

5.视图的更新 update
视图不仅可以修改,而且可以更新.因为对它操作时会影响基础表的。
这里我就说一个注意点:当你的视图是由2个表链接查询出来的,而你做的操作是更新的时候,要注意了：会出现状况:
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.VCustOrders') IS NOT NULL
  DROP VIEW dbo.VCustOrders;
GO
IF OBJECT_ID('dbo.Orders') IS NOT NULL
  DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers') IS NOT NULL
  DROP TABLE dbo.Customers;
GO
---建Customers表
CREATE TABLE dbo.Customers
(
  cid   INT         NOT NULL PRIMARY KEY,
  cname VARCHAR(25) NOT NULL
)
INSERT INTO dbo.Customers(cid, cname) VALUES(1, 'Cust 1');
INSERT INTO dbo.Customers(cid, cname) VALUES(2, 'Cust 2');
---建Orders 表
CREATE TABLE dbo.Orders
(
  oid INT NOT NULL PRIMARY KEY,
  cid INT NOT NULL REFERENCES dbo.Customers
)
INSERT INTO dbo.Orders(oid, cid) VALUES(1001, 1);
INSERT INTO dbo.Orders(oid, cid) VALUES(1002, 1);
INSERT INTO dbo.Orders(oid, cid) VALUES(1003, 1);
INSERT INTO dbo.Orders(oid, cid) VALUES(2001, 2);
INSERT INTO dbo.Orders(oid, cid) VALUES(2002, 2);
INSERT INTO dbo.Orders(oid, cid) VALUES(2003, 2);
GO
-----创建视图VCustOrders 显示2表的链接内容
CREATE VIEW dbo.VCustOrders
AS
SELECT C.cid, C.cname, O.oid
FROM dbo.Customers AS C
  JOIN dbo.Orders AS O
    ON O.cid = C.cid;
GO
--显示数据
SELECT cid, cname, oid FROM dbo.VCustOrders;
GO
/*
cid         cname                     oid
----------- ------------------------- -----------
1           Cust 1                    1001
1           Cust 1                    1002
1           Cust 1                    1003
2           Cust 2                    2001
2           Cust 2                    2002
2           Cust 2                    2003
*/
--更新：我们想把订单号为1001的那个公司名cname 改个名字,初期是只想改这么个订单（这是本来想做的）
UPDATE dbo.VCustOrders
  SET cname = 'Cust 42'
WHERE oid = 1001;
GO
--再次查询视图
SELECT cid, cname, oid FROM dbo.VCustOrders;
GO
/*
cid         cname                     oid
----------- ------------------------- -----------
1           Cust 42                   1001
1           Cust 42                   1002
1           Cust 42                   1003
2           Cust 2                    2001
2           Cust 2                    2002
2           Cust 2                    2003
*/

------其实这里很好理解,修改的是表Customers 而不是视图 所以改了再查视图 视图又是从表那边链接过来 当然三个就一起变了----

还请大家多支持偶声情斑竹哈
http://topic.csdn.net/u/20091110/14/a357b769-9971-478b-88eb-544b1d69229e.html

Answer 1

6.视图的一些选项
a.ENCRYPTION 
对触犯器 视图 UDF 存储过程的创建文本可以加密.
不过听说已经找到解密的方法http://www.360doc.com/content/050909/13/717_11438.html 
b.SCHEMABINDING
把视图绑定到一个基对象的框架上,SQL将不允许删除基对象或者修改视图里引用的列
create table  tb (a int )
insert tb values(1) 
insert tb values(2) 
IF OBJECT_ID('dbo.VCustsWithOrders') IS NOT NULL
  DROP VIEW dbo.VCustsWithOrders;
GO
CREATE VIEW dbo.VCustsWithOrders WITH  SCHEMABINDING
AS
select A from dbo.tb   -----这里必要用dbo.tb来表示tb表
GO
--尝试修改表结构
ALTER TABLE dbo.tb DROP COLUMN a;
GO
/*
对象'VCustsWithOrders' 依赖于 列'a'。
*/
c.check option 
使用了这个选项的视图会防止与视图的查询筛选器有冲突的INSERT 和 UPDATE 操作.举个例子:
create table v(a int)
insert v select 8 
insert v select 2 
insert v select 3 
insert v select 4 
go
create  view v_v 
as
select * from v where a>2
with check option
go
--insert操作
insert v_v  select 6--成功
insert v_v  select 1--失败,视图定义了check option 而规定a>2
--update操作
update v_v set a=1 where A=4--错误了,更新完的a值为1 小于2 所以不符合视图where条件
update v_v set a=3 where A=4--成功
--delete操作 肯定成功 因为它是删除掉》。。
4.索引视图 indexed_view 
一个没有索引的视图只不过是一个虚拟的表,拥有了索引,视图等于获得新生,他所包含的数据将要物理化,就要变成真正的表。
它会同步于基础表,就是说你修改基础表会同时更新索引视图,这里修改的性能会降低的.
但是索引视图同样提高了一些聚合查询和高成本的链接性能.
创建索引的限制:
a.第一个索引必须是唯一聚集索引.
b.视图必须用SCHEMABINDING,要求看上面;
c.如果对视图的查询进行了聚合操作,它的SELECT列表必须包含COUNT_BIG(*)这个聚合函数
CREATE VIEW dbo.VEmpOrders WITH SCHEMABINDING
AS
SELECT O.EmployeeID, SUM(OD.Quantity) AS TotalQty, COUNT_BIG(*) AS Cnt
FROM dbo.Orders AS O
  JOIN dbo.[Order Details] AS OD
    ON OD.OrderID = O.OrderID
GROUP BY O.EmployeeID;
GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_empid ON dbo.VEmpOrders(EmployeeID);
GO
---上面这个视图就是一个典型的索引视图---
---索引视图有个好处,比如你在外部基础表上查询,注意基础表上是没索引的,但是你可以再查询时候用到视图的索引.
SELECT O.EmployeeID, SUM(OD.Quantity) AS TotalQty, AVG(OD.Quantity) AS AvgQty, COUNT_BIG(*) AS Cnt
FROM dbo.Orders AS O
  JOIN dbo.[Order Details] AS OD
    ON OD.OrderID = O.OrderID
GROUP BY O.EmployeeID;
--这个查询就是用到了视图上的聚集索引,而且AVG(OD.Quantity)这个视图里不包含的聚合函数也用到了-----
d.还有几个SQL2005用到索引视图的情况:
1.当外部查询的where 条件或者ON 条件的筛选区间是试图筛选器的子区间的时候,查询可以用到索引视图.
比如：外部查询n<100 视图查询n<200 
2.外部查询使用一个与视图查询等价的逻辑筛选条件
比如:外部 n=100 视图100=n
3.关于NULL值插入
你使用UNIQUE约束想约束表中非NULL值要唯一,但是NULL值可以多次插入,但是UNIQUE约束认为NULL值是相等的,所以你在表中该列
已经存在NULL值情况下是不能再次插入的.这个时候你就可以使用索引视图解决这个问题
USE tempdb;
GO
IF OBJECT_ID('dbo.V1') IS NOT NULL
  DROP VIEW dbo.V1;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  keycol  INT         NULL,
  datacol VARCHAR(10) NOT NULL
);
GO
CREATE VIEW dbo.V1 WITH SCHEMABINDING
AS
SELECT keycol FROM dbo.T1 WHERE keycol IS NOT NULL--注意这里的where 条件
GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_keycol ON dbo.V1(keycol);
GO
-- 插入数据
INSERT INTO dbo.T1(keycol, datacol) VALUES(1,    'a');
INSERT INTO dbo.T1(keycol, datacol) VALUES(1,    'b'); -- 这条失败的
INSERT INTO dbo.T1(keycol, datacol) VALUES(NULL, 'c');
INSERT INTO dbo.T1(keycol, datacol) VALUES(NULL, 'd');
GO
--进行查询
SELECT keycol, datacol FROM dbo.T1;
GO
/*
keycol      datacol
----------- ----------
1           a
NULL        c
NULL        d
*/
----视图索引保证不准插入重复值,但是因为WHERE keycol IS NOT NULL 所以它没有限定NULL的重复性.--
------这里也再次说明对基础表的操作又一次牵动了视图索引------------
还请大家多支持偶声情斑竹哈 
http://topic.csdn.net/u/20091110/14/a357b769-9971-478b-88eb-544b1d69229e.html