SQLServer实用基础教程第8章视图和用户定义数据类型

1、第8章 视图和用户定义数据类型 本章学习目标在数据库中，有时为了查询的方便，可以创建一个视图，然后通过视图来简化查询。而有时在设计表时， SQL Server提供的系统数据类型并不能完全满足需要，这时就需要数据库的实现者自己来定义一些数据类型。本章主要介绍了视图和用户自定义数据类型的各种具体操作方法，主要内容包括视图的基本知识，视图的创建、查看、修改和删除方法，以及用户自定义数据类型的创建、查看和删除方法等。通过本章的学习，读者就应能够掌握视图和用户自定义数据类型的各种具体操作方法，并能够在对数据库的操作过程中对其进行应用，真正做到学以致用。 学习重点与难点 视图的概念和优缺点 视图的查看和管

2、理 视图的修改和删除 用户自定义数据类型的创建 用户自定义数据类型的管理81 视图的概念视图是从一个或多个表（视图）导出的表，是数据库用户使用数据库的观点。它是用户查看数据库表中数据的一种方式，是基于某个查询结果的虚拟表，用户通过它来浏览表中感兴趣的部分或全部数据。而数据的物理存放位置仍然在表中，这些表称作视图的基表。一个视图可以派生于一个或多个基表，也可以从其它视图中派生。视图实际上发挥着过滤数据的作用。定义视图的查询语句可以从一个表、多个表或者其他视图中引用数据。发布式数据库查询语句还可以定义、引用不同数据源的数据库视图。一般地，视图的内容包括：基表的列的子集或行的子集；两个或者多个基表的

3、联合；两个或者多个基表的连接；基表的统计汇总；另外一个视图的子集；视图和基表的混合。视图和表是有本质区别的：视图在数据库中存储的是视图的定义，而不是查询的数据。当SQL SERVER处理视图的操作时，它会在数据库中找到视图的定义，然后把对视图的查询转化为视图基本表的查询。82 视图的优缺点视图常用于聚焦、简化和定制用户对数据库的感知和控制，它也可以作为一种安全机制，以便使用户只能通过视图来访问数据，而不给其访问基表的权限。具体来讲，使用视图有下列优点：（1）聚焦特定的数据。视图使用户只能看到和操纵与他们有关的数据，不需要了解和使用的数据不加入视图，这样可以提高数据的安全性。（2）简化数据操作。

4、通过将复杂查询（如多表的连接查询）定义为视图，可以简化操作，不必在每次需要数据时都要提供所需的条件、限制等。（3）定制用户数据。视图可以让使用同一数据库的不同用户看到不同的数据。（4）导入/导出数据。可以使用视图将数据导出到其他应用程序，也可以将数据文件中的数据导入到视图中。（5）合并分离的数据。使用UNION关键字可以将两个或更多基于不同表的查询结果合并为一个单独的结果集，可以将这样的结果集创建为一个视图，让用户看起来就象一个单独的表一样。（6）屏蔽数据库的复杂性（隔离变化）。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。（7）简化用户权限的管理。只需授予

5、用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。（8）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。使用视图也有其相应的缺点。例如，造成系统性能下降，并且会使用户对数据的修改受到很大限制等。83 创建视图用户在数据库中创建了一个或多个表后，为了方便查询其中的数据，就可以为其创建相应的视图，用以代替表来完成从表中查询、插入、更新和删除数据的操作。创建视图有两种方法：使用企业管理器和使用T-SQL语句。8.3.1 使用企业管理器创建视图使用企业管理器创建视图的步骤如下：（1）在企业管理器的树形目录中展开服务器组、服务器。（

6、2）展开Supermarket数据库，用鼠标右键单击“视图”目录，在弹出的快捷菜单中选择“新建视图”选项，打开如图8-1所示的窗口。图8-1新建视图窗口（3）在图8-1所示窗口的图表窗格（即最上面的窗格）中单击鼠标右键，在弹出的快捷菜单中选择“添加表”命令，弹出如图8-2所示的对话框。图8-2 添加表（4）此对话框包括三个选项卡，分别显示了当前数据库的用户表、用户视图和函数。选定表后单击“添加”按钮，可以添加创建视图的基表，重复此操作可以添加多个基表。添加完毕后单击“关闭”按钮。（5）添加完基表，可以如图8-1所示窗口的图表窗格中看到新添加的基表以及基表之间的外键引用关系，如图8-3所示。图8

7、-3 添加Goods表和Supplier表作为基表（6）在图8-3中，每个基表的每一列的左边都有一个复选框，选择该复选框，可以指定该列在视图中被引用。图8-3的窗口中第二个窗格是条件窗格，在这个窗格中可以指定查询条件。条件窗格中显示了所有在图表中选中的、要在视图中引用的列。也可以对每一列选中或取消选中“输出”复选框，来控制该列是否在视图中出现。（7）在基表中选中“货品名称”列、“存货量”列、“进货价格”列、“供应商”列和“负责人”列前面的复选框。如图8-5所示。（8）图8-5所示窗口的条件窗格中有“准则”列，它用于输入对在视图中出现的列的限制条件，该条件相当于定义视图的查询语句中的WHERE子

8、句。例如，在“存货量”列输入10，即创建的视图只包括存货量大于10的货品记录。（9）要在视图的定义中依照某一列进行分组，可以在图8-5所示窗口的条件窗格中用鼠标右键单击该列，并在弹出的快捷菜单中选择“分组”命令。（10）在图8-5所示窗口的任何地方单击鼠标右键，在弹出的快捷菜单中选择“属性”命令，可以打开如图8-6所示的对话框。图8-5 选择视图出现的列图8-6 属性对话框（11）在属性对话框中，选中“顶端”复选框可以限制视图最多可以包含多少条记录，选中“DISTINCT值”复选框可以指定在视图中不包含相同的记录，选中“加密浏览”复选框可以对视图定义加密。所有设置进行完毕后，可以在创建视图的窗

9、口中的第三个窗口中查看视图查询条件的T-SQL语句，如图8-7所示。用户也可以自己修改该T-SQL语句，修改完成后可以单击工具栏上的按钮，检查该T-SQL语句的语法是否正确。（12）要运行并输出该视图的结果，可以单击鼠标右键，在弹出的快捷菜单中选择“运行”命令，或是在工具栏中单击按钮。在窗口的最下面的输出窗格中将会显示按照中间的T-SQL语句生成的视图内容。（13）单击工具栏上的按钮，可以保存创建的视图。视图创建完成。8.3.2 使用T-SQL语句创建视图使用T-SQL语句创建视图的语法格式为：CREATE VIEW view_name (column ,.n)WITH ENCRYPTIONA

10、S select_statement WITH CHECK OPTION图8-7 查看T-SQL语句其中各选项的含义如下：u view_name：所创建的视图名。u Column：参数定义视图列名，省略该参数时，视图中的列名来自源表中列的名称。如果查询中包含计算列，那么必须指定列名。u Select_statement：为视图定义语句，它定义通过视图可以浏览到的表或其他视图中的哪些数据，它是SELECT语句。SELECT语句中可以使用多个表及其他视图，也可以使用UNION关键词合并起来的多个SELECT语句。u WITH ENCRYPTION： SQL Server将创建视图的Create V

11、iew 语句的文本存储在Syscomments系统表中，使用WITH ENCRYPTION 选项要求在存储Create View 语句的文本时对它进行加密，这使得任何人无法使用系统存储过程SP_HELPTEXT或其他方法从Syscomments系统表中检索系统定义文本。u WITH CHECK OPTION：强制所有通过视图修改的数据满足select_ statement语句中指定的条件。当通过视图修改一条记录时，WITH CHECK OPTION保证修改的数据被提交前仍然可以通过视图看到。如果不使用该选项，那么当通过视图添加或修改数据记录时，如果它们不满足定义视图的查询条件（即select_

12、 statement语句中指定的条件），则它们将从视图中消失。例如，以下代码在Supermarket数据库中创建视图view_supermarket，显示每种货品的名称、存货量、进货价格、供应商和负责人，并要求对视图定义文本进行加密存储：USE SupermarketGOCREATE VIEW view_supermarketWITH ENCRYPTIONASSELECT 货品名称,存货量,进货价格,供应商,负责人FROM Goods,SupplierWHERE Goods. 供应商号=Supplier.供应商号再如，以下代码在Sales数据库中创建一个视图view_sales，其内容为每个货

13、品的订单金额总和。USE SalesGOCREATE VIEW view_sales(货品名称,金额)WITH ENCRYPTIONASSELECT 货品名称,SUM(数量*单价)FROM OrdersGROUP BY 货品名称8.3.3 创建和使用视图的注意事项学会了创建视图之后，还需了解一下在其创建和使用过程中要注意的相关事项。一般来讲，创建和使用视图进，需注意下列事项：（1）只有在当前数据库中才能创建视图。（2）视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是惟一的，即多不同用户，即使定义相同的视图，也必须使用不同的名字。（3）不能把规则、默认值定义绑定在视图之上

14、，不能将触发器与视图相关联。（4）不能在视图上建立任何索引，包括全文索引。（5）一个视图最多可以引用1024个列。（6）可以将视图建立在其他视图或者引用视图的过程之上，SQL Server 2000中允许最多32层的视图嵌套。（7）定义视图的查询语句中不能包括ORDER BY、COMPUTE、COMPUTE BY子句或是INTO等关键词。（8）不能创建临时视图，也不能在临时视图是创建视图。（9）不能对视图进行全文查询，尽管基表上可能有全文索引而使用全文查询。（10）默认状态下，视图中的列继承它们在基表中的名称。存在以下情况时，在创建视图时需要明确给出每一列的名称：u 视图中的某些列来自表达式、

15、函数或常数。u 视图中两个或多个列在不同表中具有相同的名称。u 希望在视图中的列使用不同于基表中的列名时。在创建视图时，并非所有的SELECT子查询都是合法的，有的SQL语句在这里是禁止使用的，例如：SELECT INTO、COMPUTE或COMPUTE BY子句以及 ORDER BY子句等。除此之外，生成的临时表也是禁止使用的。但在对视图进行查询时，仍可像对表格一样使用ORDER BY等语句，尽管有这些限制，但视图中可以包含的内容依然非常丰富。在视图中可以使用的内容包括：统计函数、GROUP BY子句、JOIN 子句、DISTINCT子句以及UNION子句等。视图可以建立在其他视图的基础上（

16、最多可以嵌套32层）。84 管理视图对视图所进行的管理以通常包括查询、修改、删除和更新等操作。一般来讲，视图一经定义以后，就可以像表一样随时被查询、修改、删除或更新。8.4.1 查看视图的基本信息SQL SERVER允许用户获得视图的一些有关信息，如：视图的名称、视图的所有者、创建的时间等等。视图的信息存放在以下几个系统表中：u sysobjects：存放视图的名称等信息。u syscolumns：存放视图中定义的列。u sysdenpends：存放视图的依赖关系。u syscomments：存放定义视图的文本。有四种方法可以查看视图的信息：使用企业管理器、使用T_SQL语句、使用存储过程和查

17、询information_schema .views获得视图信息。1、使用企业管理器使用企业管理器查看视图信息的具体操作步骤如下：（1）在企业管理器的树形目录中，展开服务器组、服务器。（2）展开“数据库”，再展开Sales数据库。（3）选中“视图”目录，右窗口中就会显示当前数据库中所有的视图，以及它们的类型、拥有者和创建时间，如图8-8所示。图8-8 查看Sales数据库中的所有视图2、使用T_SQL语句使用T_SQL语句的SELECT语句也可以查看视图的基本信息。例如，查看view_sales视图的基本信息。USE SalesSELECT *FROM sysobjects WHERE xty

18、pe=V and name=view_sales3、使用存储过程使用存储过程查看视图信息的语法格式为：SP_HELP view_name该语句将显示视图的名称、拥有者及创建时间等信息。例如，查看Sales数据库中view_sales视图的信息。USE SalesGOsp_help view_sales其执行结果如图8-10所示。图8-10查询Sales数据库中所有已经定义的视图的基本信息4、查询information_schema .views获得视图信息在查询分析器中输入information_schema .views语句，然后单击“执行”按钮，即可显示出当前或指定数据库中所有已经定义的视

19、图的基本信息。例如，要查询Sales数据库中所有已经定义的视图的基本信息，则可以在查询分析器中输入以下语句：USE SalesGOinformation_schema .views8.4.2 查看视图的定义信息1、使用企业管理器使用企业管理器查看视图的定义信息的具体操作步骤如下：（1）在企业管理器的树形目录中，展开服务器组、服务器。（2）展开Sales数据库。选中“视图”目录，在右窗口的列表中用鼠标右键单击view_sales视图，在弹出的快捷菜单中选择“属性”命令，打开如图8-10所示的窗口。图8-10 查看属性窗口（3）属性窗口中“文本”框中的文字就是视图的定义。 2、使用系统存储过程可用

20、于查看视图的定义信息的系统存储过程的语法格式为：SP_HELPTEXT view_name例如，查看Sales数据库中view_sales视图的定义。USE SalesGOsp_helptext view_sales或：USE SalesEXEC sp_helptext view_sales8.4.3 查看视图与其他数据库对象之间的依赖关系有时候要查看视图与其他数据库之间的依赖关系，比如，视图在哪些表的基础上创建、又有哪些数据库对象的定义引用了该视图。查看视图与其他数据库之间的依赖关系的方法有两种：1、使用企业管理器使用企业管理器查看视图与其他数据库对象之间的依赖关系的具体操作步骤如下：（1）

21、在企业管理器的树形目录中，展开服务器组、服务器。（2）展开Sales数据库，单击“视图”目录，在右窗口中用鼠标右键单击view_sales视图，在弹出的快捷菜单中选择“所有任务”|“显示相关性”命令，打开如图8-12所示的对话框。（3）该对话框由两个列表框组成，分别显示了该视图所依赖的数据库对象和该视图的数据库对象。图8-12 view_sales视图与其他数据库对象的依赖关系2、使用系统存储过程 使用系统存储过程查看视图与其他数据库对象之间的依赖关系的语法格式如下：SP_DEPENDS view_name例如，查看view_sales视图与其他数据库对象之间的依赖关系。USE SalesEX

22、EC SP_DEPENDS view_sales查询结果如图8-13所示。图8-13 view_sales视图与其他数据库对象之间的依赖关系85 视图的修改和删除在使用一个视图的过程中，用户可以根据自己的需要，对其定义进行修改。如果一个视图已经没有用了，也可以将其删除。8.5.1 视图的修改修改视图的定义可用两种方法，即使用企业管理器和使用T-SQL的ALTER VIEW语句。1、使用企业管理器使用企业管理器修改视图定义的步骤如下：图8-14 检查语法成功（1）在企业管理器的树形目录中，展开服务器组、服务器。（2）展开Sales数据库，单击“视图”目录，在右窗口中鼠标右键单击view_sale

23、s，在弹出的快捷菜单中选择“属性”命令，弹出如图8-10所示的窗口。属性窗口中“文本”框中的文字就是视图的定义，可以在这里对视图定义进行修改，修改完毕后，可以单击“检查语法”按钮进行语法检查。如果语法正确，会弹出如图8-14所示的对话框，提示检查成功。2、使用ALTER VIEW语句使用ALTER VIEW语句修改视图的语法格式为：ALTER VIEW view_name (column ,.n)WITH ENCRYPTIONASselect_statement WITH CHECK OPTION例如，对Supermarket数据库中的view_supermarket视图进行修改。USE Su

24、permarketGOALTER VIEW view_supermarketWITH ENCRYPTIONASSELECT 货品名称,进货价格,供应商FROM Goods,SupplierWHERE Goods. 供应商号=Supplier.供应商号此外，你还可以删除原来的视图，并重新创建它。但此时你必须重新分配权限。8.5.2 视图的删除删除视图也可用两种方法，分别为使用企业管理器和使用T-SQL的DROP VIEW语句。1、使用企业管理器使用企业管理器删除视图的具体操作步骤如下：（1）在企业管理器的树形目录中，展开服务器组、服务器。（2）展开Sales数据库，单击“视图”目录，在右窗口中用

25、鼠标右键单击view_sales视图，在弹出的快捷菜单中选择“删除”命令，弹出如图8-15所示的对话框，单击“全部除去”按钮，删除此视图。图8-15 删除视图2、使用DROP VIEW语句使用DROP VIEW语句删除视图的语法格式为： DROP VIEW VIEW_NAME,N例如，删除Supermarket数据库中的view_supermarket视图。USE SupermarketDROP VIEW view_supermarket86 通过视图修改基表中的数据对视图可以进行查询操作，对视图的查询实际上仍是在查询基表是的数据，因为视图是不在物理结构上存储数据的。同样，在SQL SERVE

26、R中，对视图中的记录进行插入、修改、删除等更新操作也是作用在基表上的。对视图进行插入、修改、删除的语法与表完全一样，但有如下以下限制：u 定义视图的查询语句中没有集合函数（如AVG、SUM、COUNT等），也没有TOP、DISTINCT、GROUP BY和UNION子句。也即查询语句的结果集中的列应没有对基表中的列做修改。u 定义视图的查询语句的FROM子句至少要引用一个表。u 在通过视图更新数据之前，不能在一个修改语句中对多个基表进行修改。u 对某些列不能进行修改，如计算列（在2000中可以）。u 在通过视图修改或插入数据时，必须保证修改表中未显示的列有值（允许为空或有默认值）。u 如果在视

27、图定义中指定了WITH CHECK OPTION选项，那么要验证所修改的数据。下面离子为在Sales数据库中创建视图并添加数据。首先，在Sales数据库中使用T-SQL语句创建视图view_sales。USE SalesGOCREATE VIEW view_sales(姓名,公司,职位)ASSELECT 姓名,公司,职位FROM Customers然后，使用T-SQL语句添加数据。USE SalesINSERT INTO view_sales(姓名,公司,职位)VALUES(李果,河北详建商贸股份有限公司,业务部经理)在查询分析器中执行该语句，返回如下结果：（所影响的行数为 1 行）该结果说明

28、INSERT操作成功执行，该记录被插入到Customers表中。另外，通过视图对基表进行删除时，还要求该视图的基表只有一个，即视图定义的FROM子句只引用了一个表。87 用户定义数据类型在设计表时，为每一列指定一个适当的数据类型是很重要的。有时候，SQL Server提供的系统数据类型并不能完全满足需要，所以就需要数据库的实现者自己定义一些数据类型。用户定义数据类型并不是真正的数据类型，它只提供了一种加强数据库内部元素和基本数据类型之间一致性的机制。通过使用这些数据类型能够简化对常用规则和默认值的管理。用户定义数据类型并不是数据库对象，有关它的记录不会列在sysobjects表中。用户自定义数

29、据类型被记录在systypes表中，但其命名严格遵守对象命名规则。当在几个列中必须存储同一种数据类型时，并且为了保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。用户定义的数据类型可以实现下面的功能：u 利用用户定义的数据类型可以使在不同表中重复出现的各列具有相同的特性、使相似的数据种类标准化。u 可以将默认值和规则绑定到用户定义的数据类型上，那么该规则和默认值就可以约束使用此数据类型的所有列。用户定义的数据类型是在SQL Server的系统数据类型的基础上创建的，比如，可以定义一个基于字符类型的电子邮件地址类型。用户定义的数据类型与系统数据一样，都是用来限制用户可以输入

30、的数据的种类和长度。它可以用于使用系统数据类型的任何地方。如果在model数据库中创建了用户定义的数据类型，则它将出现在所有以后新建的数据库中，但是定义在用户数据库中的用户定义数据类型，则只会出现在定义它的数据库中。8.7.1 创建用户定义的数据类型创建用户定义的数据类型必须提供三个参数：数据类型的名称、所基于的系统数据类型和数据类型的可空性。创建用户定义的数据类型可以有两种方法：使用企业管理器和使用系统存储过程。1、使用系统存储过程来定义使用系统存储过程来定义用户自定义数据类型的语法格式为：sp_addtype type_name，system_type，null_type其中type_na

31、me为用户定义的数据类型名，这个名称必须在数据库中是惟一的。system_type为用户定义的数据类型所基于的系统数据类型，可以包括数据的长度、精度等。当系统数据类型中包括标点符号时，应用引号括起来。Null_type指定该数据类型能否接受空值。其值可以为NULL、NOT NULL或NONULL，其中NULL为默认值。凡是包含了诸如“（）”或“，”等分割符的系统数据类型，如char(10)，必须使用引号括起来，即“char(10)”。例如，在Sales数据库中创建一个用户定义数据类型telephone:：USE SalesEXEC sp_addtype telephone, varchar(2

32、0),null在查询分析器中执行该语句，返回如下结果：（所影响的行数为 1 行）类型已添加。2、使用企业管理器创建用户定义的数据类型。使用企业管理器创建用户定义的数据类型的步骤如下：（1）在企业管理器的树形目录中展开服务器组、服务器。（2）展开Sales数据库，用鼠标右键单击“用户定义的数据类型” 目录，在弹出的快捷菜单中选择“新建用户定义数据类型”命令，或单击“用户定义的数据类型”，在右窗口中单击鼠标右键，打开如图8-16所示的对话框。（3）在图8-16中的“名称”栏中输入新建用户定义数据类型的名称。（4）在“数据类型”下拉列表框中选择用户定义数据类型所基于的系统数据类型。如果选择的基类型是

33、可以设定长度的（如char），则需要在“长度”栏中设定数据类型的长度。（5）如果允许为空，则需要选中“允许NULL值”复选框。（6）如果希望该数据类型与规则或默认值绑定，则分别在“规则”和“默认值”下拉列表框中选择要绑定的规则和默认值，否则选择“无”。图8-16 新建用户定义数据类型对话框（7）单击“确定”按钮，关闭对话框，完成用户定义数据类型 的创建。可以直接使用用户定义的数据类型来定义表格，但是用户定义的数据类型更常与默认值或规则等配合使用。8.7.2 查看用户定义的数据类型当不知道如何使用一个用户自定义数据类型时，可以使用企业管理器和系统存储过程来查看用户自定义数据类型的信息，包括它基于

34、的系统数据类型，它的长度、精度、是否允许为空值，以及在这一数据类型上捆绑的规则和默认值。1、使用企业管理器图8-17 查看用户定义数据类型的信息使用企业管理器查看用户定义数据类型信息的步骤如下：（1）在企业管理器的树形目录中展开服务器组、服务器。（2）展开Sales数据库，单击“用户定义的数据类型”目录，在右窗口中，显示的是此数据库中的用户自定义数据类型。用鼠标右键单击要查看信息的用户自定义数据类型，在弹出的快捷菜单中选择“属性”命令，打开如图8-17所示的对话框。在此对话框中，显示此用户自定义数据类型的名称、基于系统的数据类型、长度、是否允许为空以及捆绑的规则和默认值等信息。2、使用系统存储过程使用系统存储过程查看用户定义数据类型信息的语法格式为：sp_help type_name例如，查看用户定义数据类型telephone的信息。USE SalesEXEC sp_help telephone8.7.3 重新命名用户定义的数据