先举一个例子,在Northwind数据库中,如果要查看一个订单是由哪个雇员签订的,签订的时间是什么时候,买了什么产品,价格是多少,由谁来送货,送货的地址是什么等内容,那么就要从产品、订单、订单明细、雇员和运货商五个表中查询记录,其查询代码如下:
SELECT 订单.订单ID, 雇员.姓氏, 雇员.名字, 产品.产品名称,
订单明细.单价, 订单明细.数量, 订单明细.折扣, 运货商.公司名称,
订单.货主名称, 订单.货主地址, 订单.货主城市, 订单.订购日期,
订单.发货日期
FROM 订单 INNER JOIN
订单明细 ON 订单.订单ID = 订单明细.订单ID INNER JOIN
雇员 ON 订单.雇员ID = 雇员.雇员ID INNER JOIN
产品 ON 订单明细.产品ID = 产品.产品ID INNER JOIN
运货商 ON 订单.运货商 = 运货商.运货商ID
如果要查询具体某一个订单的详细内容,还要在其后增加一个“WHERE 订单.订单ID =”的语句。
如果经常需要查询相同的字段内容(只是条件不同,如上例中可能只是订单的ID号不同而已),那么每次都重复地写这么一大串相同的代码,无疑会增加工作量和影响工作效率。
再看一下图9.1,其显示出来的界面与在SQL Server Management Studio中打开一个表的界面是否十分相似?如果将这个查询的结果集视为一个表,那么这个表就是一个视图,下面是将该查询结果创建成视图的代码:
CREATE VIEW 订单详细视图
AS
SELECT 订单.订单ID, 雇员.姓氏, 雇员.名字, 产品.产品名称,
订单明细.单价, 订单明细.数量, 订单明细.折扣, 运货商.公司名称,
订单.货主名称, 订单.货主地址, 订单.货主城市, 订单.订购日期,
订单.发货日期
FROM 订单 INNER JOIN
订单明细 ON 订单.订单ID = 订单明细.订单ID INNER JOIN
雇员 ON 订单.雇员ID = 雇员.雇员ID INNER JOIN
产品 ON 订单明细.产品ID = 产品.产品ID INNER JOIN
运货商 ON 订单.运货商= 运货商.运货商ID
创建完视图之后,如果还要以同样的条件进行查询,只要输入以下一行代码就可以得到查询结果:
SELECT * FROM 订单详细视图
即使要查询某个订单的详细情况,也只要输入以下代码,而不用输入那么一长串代码了。
SELECT * FROM 订单详细视图
WHERE 订单ID = 10248
视图具备了数据表的一些特性,数据表可以完成的功能,如查询、修改(虽然在修改记录时有些限制)、删除等操作,在视图中都可以完成。同时,视图也和数据表一样能成为另一个视图所引用的表。使用视图有以下几个优点:
l 简化查询语句:通过视图可以将复杂的查询语句变成很简单。
l 增加可读性:由于在视图中可以只显示有用的字段,并且可以使用字段别名,能方便用户浏览查询的结果。
l 方便程序的维护:如果用应用程序使用视图来存取数据,那么当数据表的结构发生改变时,只需要更设视图存储的查询语句即可,不需要更改程序。
l 增加数据的安全性和保密性:针对不同的用户,可以创建不同的视图,此时的用户只能查看和修改其所能看到的视图中的数据,而真正的数据表中的数据甚至连数据表都是不可见不可访问的,这样可以限制用户浏览和操作的数据内容。另外视图所引用的表的访问权限与视图的权限设置也是相互不影响的。
注意:视图是个虚拟的表,其存储的是查询语句而不是数据。视图中的数据都存储在其引用的数据表中,除非在视图中建立了索引。
2 创建视图
创建视图与创建数据表一样,可以使用SQL Server Management Studio和T-SQL语句两种方法,下面分别介绍这两种方法:
在SQL Server Management Studio中创建视图
在SQL Server Management Studio中创建视图的方法与创建数据表的方法不同,下面举例说明如何在SQL Server Management Studio中创建视
图:
(1)启动【SQL Server Management Studio】,连接到本地默认实例,在【对象资源管理器】窗口里,选择本地数据库实例à【数据库】à
【Northwind】à【视图】。
(2)右击【视图】,在弹出的快捷菜单里选择【新建视图】选项。
(3)出现的如图9.2所示的视图设计对话框,其上有个【添加表】对话框,可以将要引用的表添加到视图设计对话框上,在本例中,添加产品
、订单、订单明细、雇员和运货商五个表。
(4)添加完数据表之后,单击【关闭】按钮,返回到如图9.3所示的【视图设计】窗口。如果还要添加新的数据表,可以右击【关系图窗格】
的空白处,在弹出的快捷菜单里选择【添加表】选项,则会弹出如图9.2中所示的【添加表】对话框,然后继续为视图添加引用表或视图。如果
要移除已经添加的数据表或视图,可以右击在【关系图窗格】里选择要移除的数据表或视图,在弹出的快捷菜单里选择【移除】选项,或选中
要移除的数据表或视图后,直接按【Delete】按钮移除。
(5)在【关系图窗格】里,可以建立表与表之间的JOIN…ON关系,如【产品】表的“产品ID”与【订单明细】表中的“产品ID”相等,那么只
要将【产品】表中的“产品ID”字段拖拽到【订单明细】表中的“产品ID”字段上即可。此时两个表之间将会有一根线连着的。
(6)在【关系图窗格】里选择数据表字段前的复选框,可以设置视图要输出的字段,同样,在【条件窗格】里也可设置要输出的字段。
(7)在【条件窗格】里还可以设置要过滤的查询条件。
(8)设置完后的SQL语句,会显示在【SQL窗格】里,这个Select语句也就是视图所要存储的查询语句。
(9)所有查询条件设置完毕之后,单击【执行SQL】按钮,试运行Select语句是否正确。
(10)在一切测试都正常之后,单击【保存】按钮,在弹出的对话框里输入视图名称,再单击【确定】按钮完成操作。
2.2 用Create view创建视图
2.2.1 基本语法
用T-SQL的create view语句可以创建视图,其语法为:
CREATE VIEW [ schema_name . ] view_name --架构名.视图名
[ (column [ ,...n ] ) ] --列名
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ ; ] --搜索语句
[ WITH CHECK OPTION ] --强制修改语句都必须符合在select_ statement中设置的条件
<view_attribute> ::=
{
[ ENCRYPTION ] --加密
[ SCHEMABINDING ] --绑定架构
[ VIEW_METADATA ] } --返回有关视图的元数据信息
2.2.2 参数说明
Create view的参数有:
l schema_name:视图所属架构名
l view_name:视图名
l column:视图中所使用的列名,一般只有列是从算术表达式、函数或常量派生出来的或者列的指定名称不同于来源列的名称时,才需要使用
。
l select_statement:搜索语句。
l WITH CHECK OPTION:强制针对视图执行的所有数据修改语句都必须符合在select_ statement中设置的条件。
l ENCRYPTION:加密视图。
l SCHEMABINDING:将视图绑定到基础表的架构。
l VIEW_METADATA:指定为引用视图的查询请求浏览模式的元数据时,SQL Server 实例将向 DB-Library、ODBC 和 OLE DB API 返回有关视图
的元数据信息,而不返回基表的元数据信息。
2.2.3 简单用法
例一、创建一个视图,用于查看产品、类别和供应商,其代码如下:
--创建视图
CREATE VIEW view_例一
AS
SELECT 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称
FROM 产品
JOIN 类别 ON 产品.类别ID = 类别.类别ID
JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
GO
--查看视图
SELECT * FROM view_例一
GO
其运行结果如图9.4所示:
2.2.4 给视图字段加上别名
例二、创建一个视图,用于查看产品、类别和供应商,并修改其字段名,其代码如下:
CREATE VIEW view_例二(产品编号,产品名称,产品类别,供应商名称)
AS
SELECT 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称
FROM 产品
JOIN 类别 ON 产品.类别ID = 类别.类别ID
JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
GO
SELECT * FROM view_例二
GO
其运行结果如图9.5所示,视图的字段名与图9.4中所显示的不一样。
2.2.5 注意事项
在用create view创建视图时,select子句里不能包括以下内容:
l 不能包括compute、compute by子句
l 不能包括order by子句,除非在select子句里有top子句
l 不能包括option子句
l 不能包括into关键字
l 不能引用临时表或表变量
例三、创建一个视图,查看最新100项产品的产品名称、类别名称和供应商。以下的代码是错误的:
CREATE VIEW view_例三
AS
SELECT 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称
FROM 产品
JOIN 类别 ON 产品.类别ID = 类别.类别ID
JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
ORDER BY 产品.产品ID DESC --不能在select子句里使用order by子句
只有在创建好视图后才可以使用order by子句,如:
SELECT top 100 * FROM view_例三
ORDER BY 产品ID DESC
但是如果在select子句里指了top的话,可以使用order by子句,以下代码就是正确的:
CREATE VIEW view_例三
AS
SELECT top 100 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称
FROM 产品
JOIN 类别 ON 产品.类别ID = 类别.类别ID
JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
ORDER BY 产品.产品ID DESC
3 查看与修改视图
由于视图与数据表很类似,所以在查看视图内容方面,与查看数据表内容十分相似,但在修改视图方面就会有些区别。
3.1 查看视图
在SQL Server Management Studio中查看视图内容的方法与查看数据表内容的方法几乎一致,下面以查看视图【view_例一】为例介绍如何查看
视图:
(1)启动【SQL Server Management Studio】,连接到本地默认实例,在【对象资源管理器】窗口里,选择本地数据库实例à【数据库】à
【Northwind】à【视图】à【view_例一】。
(2)右击【view_例一】,在弹出的快捷菜单里选择【查看视图】选项,出现如图9.6所示查看视图的对话框,该对话框界面与查看数据表的对
话框界面几乎一致,在此就不再赘述了。
图6 查看视图
在T-SQL语句里,使用select语句可以查看视图的内容,其用法与查看数据表内容的用法一样,区别只是把数据表名改为视图名,在此也不再赘
述了。
3.2 在SQL Server Management Studio中修改视图
使用SQL Server Management Studio修改视图事实上只是修改该视图所存储的T-SQL语句,下面以修改视图【view_例一】为例介绍如何在SQL
Server Management Studio中修改视图:
(1)启动【SQL Server Management Studio】,连接到本地默认实例,在【对象资源管理器】窗口里,选择本地数据库实例à【数据库】à
【Northwind】à【视图】à【view_例一】。
(2)右击【view_例一】,在弹出的快捷菜单里选择【修改】选项,出现如图9.7所示修改视图的对话框,该对话框界面与创建视图的对话框相
似,其操作也十分类似,在此就不再赘述了。
图7 修改视图
(3)修改完毕后记得存盘。
3.3 用Alter view修改视图
使用T-SQL语句的alter view可以修改视图,其语法代码如下:
ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
<view_attribute> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }
从上面代码可以看出,alter view语句的语法和create view语句完全一样,只不过是以“alter view”开头,下面举例说明alter view的用法
:
例四、修改视图“view_例三”,只查看最新的50个产品内容,其代码如下:
ALTER VIEW view_例三
AS
SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称
FROM 产品
JOIN 类别 ON 产品.类别ID = 类别.类别ID
JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
ORDER BY 产品.产品ID DESC
4.加密视图 在SQL Server 2005中每个数据库的系统视图里都有一个名为“INFORMATION_SCHEMA.VIEWS”的视图,该视图里记录了该数据库中所有视图的信 息,使用“SELECT * FROM INFORMATION_SCHEMA.VIEWS”可以查看该视图内容,如图9.8所示:
如果不想让别人看到该视图里的内容,可以使用with encryption参数来为视图加密。 例五、创建一个加密视图,内容与例三中的视图一样,其代码如下: CREATE VIEW view_例五 WITH ENCRYPTION AS SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID ORDER BY 产品.产品ID DESC 再使用“SELECT * FROM INFORMATION_SCHEMA.VIEWS”查看视图内容,其结果如图9.9所示,在“view_例五”记录上显示的视图内容为NULL, 而事实上,“view_例五”视图的内容并不为null,而是加密后用户无法查看而已。
创建完加密视图之后,在SQL Server Management Studio中也不能对其进行修改,如图9.10所示,“view_例五”视图前的小图标与其他视图的 不同,上面有一把小锁,代码将视图是加密视图。右击该视图名,在弹出的快捷菜单里【修改】选项也是灰色的不能在此进行修改。
虽然在SQL Server Management Studio中不能修改加密视图,但是并不意味着加密视图就不能被修改,使用alter view语句可以修改加密视图 。因为使用alter view语句修改视图和使用SQL Server Management Studio修改视图不同,它不需要先显示视图的代码。 例六、修改加密的“view_例五”视图,去掉加密性,其代码如下: ALTER VIEW view_例五 AS SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID ORDER BY 产品.产品ID DESC 注意:虽然视图加密后看不到其存储的select语句,但并不影响对它的使用。 5.限制视图所用的表或视图不能更改设计和删除 由于视图和数据表是数据库中独立的两种对象,虽然视图要引用数据表,但是当引用的数据表删除或修改时,视图本身并不会被删除或修改, 因此往往在删除数据表之后,会引起视图运行错误。如果在创建视图时使用了with schemabinding参数,就可以防止引用的数据表或视图删除 或修改。 例七、创建两个数据表,再创建一个带with schemabinding参数的引用这两个数据表的视图,再试图修改和删除数据表。其代码如下: --创建两个数据表 CREATE TABLE 例七_1( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, username nchar(10) NULL ) CREATE TABLE 例七_2( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, newname nchar(10) NULL ) GO --创建视图 CREATE VIEW view_例七 WITH SCHEMABINDING AS SELECT 例七_1.username,例七_2.newname FROM dbo.例七_1 JOIN dbo.例七_2 ON 例七_1.id = 例七_2.id GO --修改数据表 PRINT '' ALTER TABLE 例七_1 ALTER COLUMN username nvarchar(100) GO PRINT '' ALTER TABLE 例七_2 ALTER COLUMN newname nvarchar(100) GO --删除数据表 PRINT '' DROP TABLE 例七_1 GO PRINT '' DROP TABLE 例七_2 GO
一个没有任何索引的视图不需要任何存储空间。当一个语句使用它的时候,SQL Server会将视图的定义与语句合并,并对其进行优化,生成有 关执行计划并获取数据。在视图处理或联接很多行的时候,这个过程的开销会很大。在这种情况下,如果视图经常被请求,那么对其进行索引 可以大大提高查询性能。 自动维护这个索引。视图索引会显著提高对视图数据的访问速度,但在基表数据发生变化的时候,维护视图上的索引会增加额外的开销,这是 肯定的。因此,在视图处理多行数据或与聚合函数同时使用的时候,或者在基表中的数据并不经常改变的时候可以考虑使用索引视图。 包括一个聚合函数,SQL Server优化器发现一个索引视图已经包含这个聚合,就会从索引获取聚合结果而不必重新计算它。 确定性的;行集函数、派生表和子查询都不能在索引视图中使用。创建索引视图的完整要求列表可以在SQL Server Books Online 的主题 “Creating Indexed Views”中找到。 CreatingAndUsingIndexedViews.sql中。 “Ctrl+L”来查看估计的执行计划,如图6.15所示。 图6.15 查看估计的执行计划 YearTotal聚合。因此可以看出,现在的索引视图可以在不记录查询本身的情况下提高查询速度。 |