[转]Sql Server 视图 --- http://bbs.51cto.com/thread-787942-1.html
SQL Server学习笔记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排序的
因为视图它就是个表 表是逻辑实体,本身里面的数据时随便放的
.看实例：
view plaincopy to clipboardprint?
- 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 *的时候
view plaincopy to clipboardprint?
- 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
- */
------------这里分享个一次刷新所有视图的代码（书上）-----------
--刷新所有视图
view plaincopy to clipboardprint?
- 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.视图的模块化解题
如果一个题目解题思路写成一个语句比较长 比较难读 可以尝试用视图分开它 依次攻破 --(书上的例子,懒了下,盗用数据说明问题)
view plaincopy to clipboardprint?
- 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
-----目标结果-----根据本月于上个月差来判断销售趋势
view plaincopy to clipboardprint?
- /*
- 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视图给每个月上编号
view plaincopy to clipboardprint?
- 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
view plaincopy to clipboardprint?
- 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分组 求出自己范围内的最大最小值 并且标识出对应的中文表示
view plaincopy to clipboardprint?
- 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;
----------检索视图------------
view plaincopy to clipboardprint?
- SELECT start_range, end_range, trend
- FROM dbo.VTrends
- ORDER BY start_range;
5.视图的更新 update
视图不仅可以修改,而且可以更新.因为对它操作时会影响基础表的。
这里我就说一个注意点:当你的视图是由2个表链接查询出来的,而你做的操作是更新的时候,要注意了：会出现状况:
view plaincopy to clipboardprint?
- 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
--显示数据
view plaincopy to clipboardprint?
- 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 改个名字,初期是只想改这么个订单（这是本来想做的）
view plaincopy to clipboardprint?
- 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将不允许删除基对象或者修改视图里引用的列
view plaincopy to clipboardprint?
- 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 操作.举个例子:
view plaincopy to clipboardprint?
- 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 seta=1 where A=4--错误了,更新完的a值为1 小于2 所以不符合视图where条件
- update v_v seta=3 where A=4--成功
- --delete操作 肯定成功 因为它是删除掉》。。
4.索引视图 indexed_view
一个没有索引的视图只不过是一个虚拟的表,拥有了索引,视图等于获得新生,他所包含的数据将要物理化,就要变成真正的表。
它会同步于基础表,就是说你修改基础表会同时更新索引视图,这里修改的性能会降低的.
但是索引视图同样提高了一些聚合查询和高成本的链接性能.
创建索引的限制:
a.第一个索引必须是唯一聚集索引.
b.视图必须用SCHEMABINDING,要求看上面;
c.如果对视图的查询进行了聚合操作,它的SELECT列表必须包含COUNT_BIG(*)这个聚合函数
view plaincopy to clipboardprint?
- 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
---上面这个视图就是一个典型的索引视图---
---索引视图有个好处,比如你在外部基础表上查询,注意基础表上是没索引的,但是你可以再查询时候用到视图的索引.
view plaincopy to clipboardprint?
- 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值情况下是不能再次插入的.这个时候你就可以使用索引视图解决这个问题
view plaincopy to clipboardprint?
- 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的重复性.
------这里也再次说明对基础表的操作又一次牵动了视图索引------------
浙公网安备 33010602011771号