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

视图

 

概念:

 

一个没有索引的视图其实只是一个虚拟表,没有任何物理的数据,它就是一段代码组成的东西,包含了描述它的元数据,如结构,依赖性等.
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; GO

 

 

3.视图的模块化解题
 如果一个题目解题思路写成一个语句比较长 比较难读 可以尝试用视图分开它 依次攻破 --(书上的例子,懒了下,盗用数据说明问题)
 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 而不是视图 所以改了再查视图 视图又是从表那边链接过来 当然三个就一起变了----
 
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的重复性.
   ------这里也再次说明对基础表的操作又一次牵动了视图索引------------