5.5数据库关系图

5.5数据库关系图
5 .5 数据库关系图
⼀旦创建了数据库及其对象，就可以很⽅便地创建和底层结构链接的实体关系图，这
样，任何必要改动(尤其是创建外键约束)都可以通过⼀个⽅便的图形环境应⽤到数据库中。

SQL Server Management Studio中的数据库关系图就提供了这种功能。

但是它不能完全替代
成熟的数据库设计⼯具。

数据库关系图更多地⽤在数据库部署的测试和开发阶段中。

通过 SQL Server Management Studio的 “对象资源管理器”中的⽤户数据库节点可访
问数据库关系图功能。

在创建第⼀个数据库关系图前，需要安装关系图⽀持对象。

⽅法是
右击 “数据库关系图”节点并选择“安装关系图⽀持程序”命令。

如果不进⾏安装，那么
在第⼀次尝试创建数据库关系图时，系统会弹出⼀个消息通知缺少“⼀个或多个⽀持对象”，
并询问是否要安装它们。

安装⽀持对象或选择“是”会使 SQL Server创建⼀个名为 dbo.sysdiagrams的由系统所有的表，它将包括创建的任意关系图的定义。

通过下⾯的步骤可创建和修改⼀个数据库关系图：
(1) 展幵 “数据库 ”节点，然后展开 AdventureWorks2008数据库节点。

右击 AdventureWorkCOOS中的 “数据库关系图”节点，然后单击“新建数据库关系图”命令。

此时将显⽰“数据库关系图”窗格，以及⼀个“添加表”对话框，它按字母顺序列出了数
据库中所有的⽤户表。

(2) 选择Address(Person)表。

单击 “添加”按钮将Person.Address表添加到关系图中，然
后单击“添加表”对话框上的“关闭”按钮。

也可以双击列表中的表将其添加⾄关系图中。

(3) 右击Address(Person)表，然后单击“添加相关表”命令。

这将把所有和Address(Person)
表有关系的表添加到关系图中，如果您不熟悉数据库结构，那么这个功能⾮常⽅便。

注意，在关系图中，所有的表都只是互相堆矜在⼀起的。

可以⼿动重新排列它们，或
者右击关系图的空⽩处，然后选择 “排列表”命令。

SQL Server会把表整齐地排列在窗格上，使表和它们之间的关系易于査看。

由于关系图中空间有限，可以创建多个关系图把数据库分成功能区域，或者可以显⽰
关系图上的分页符，把⼀个⼤型关系图分成多页。

要显⽰分页符，可右击关系图上的空⽩
处，然后选择“査看分页符”命令。

右击任何表都可获得改变表在关系图中显⽰⽅式的命令、从数据库中删除表的命令、
从关系图中移除表的命令，以及⼀些通常在“表设计器”⼯具栏上访问的表修改命令。

5 .
6 视图
SQL Server 2008视图就是保存的命名查询，可以独⽴于它们引⽤的表进⾏管理。

视图和它们所引⽤的表⾮常相似，只是它们默认是逻辑对象⽽⾮物理对象。

但也有例外：当在
视图上创建了⼀个唯⼀聚集索引时，该视图就会被“物化”。

通常，创建视图是为了抽象复
杂的数据库设计，通过允许访问视图⽽不是多个表来简化权限，以及安排将数据导出到其
他数据存储器中。

有关视图创建和其他编程对象的内容不在本书讨论范围之内。

要了解有关如何和为什么
创建视图的更多信息，请参阅Paul Turley和 Dan Wood编写的《T-SQL编程⼊门经典(涵盖
SQL Server 2008 & 2005)》⼀书。

有关保护视图的信息，请阅读第6 章。

系统视图
如第4 章所述，系统视图是数据库管理员的系统对象视图。

因为有太多的系统视图，
所以这⾥⽆法⼀~描述。

您可以查阅SQL Server 2008联机从书获取相关信息。

系统视图可分成4 类：
• 信息架构视图—— 信息架构视图是属于INFORMATION_SCHEMA这个特殊架构
的预定义视图。

SQL Server 2008实现了 INFORMATION_SCHEMA的 ISO标准定
义，并提供了 SQL Server元数据的⼀致视图，即它们在版本之间没有改变。

• ⽬录视图—— ⽬录视图是从SQL Server中检索元数据的另⼀种⽅法。

由于⽬录视图是最常⽤的SQL Server元数据界⾯，因此建议使⽤该视图⽽不是信息架构视图。

它们提供了⼤量可⽤于排除问题和维护SQL Server 2008的有⽤信息。

如果在固定脚本⼱使⽤它们，要确保⽤名称指定列。

Microsoft保留了在⽬录视图末尾添加额外
列的权利，这可能会破坏现有代码。

事实上，当在SQL Server 2005和 SQL Server
2008之间选择⽬录视图时就会发⽣这种情况。

• 动态管理视图—— 动态管理视图返回⽤来监视SQL Server进程、诊断问题以及优化性能的服务器状态信息。

第4 章对其做了简要介绍。

• 兼容性⽬录视图—— 由于 SQL Server 2000中的系统表被弃⽤，因此 SQL Server 2008提供了很多和之前的系统表同名的视阁。

这些视图仅返冋和SQL Server 2000
兼容的SQL Server 200S功能，并严格规定⽤于在SQL Server 2000上设计的对象
和脚本。

后⾯的开发⼯作应使⽤返回SQL Server 2008特定信息的新⽬录视图，因为在未来版本中将删除这些兼容性⽬录视图。

5 . 7 同义词
所谓同义词，是指给SQL Server架构范围内的数据库对象指定⼀个名称，数据库应⽤程序可使⽤该名称来代替其由两部分、三部分或四部分组成的名称。

例如，如果⼀个数据
库应⽤程序引⽤了另⼀台服务器上的⼀个表，它通常需要使⽤⼀个由四部分组成的名称。

定义⼀个同义词实际上就是取⼀个直接映射到表的别名，⽽不必对表进⾏完全限定。

下列
代码将在 AdventureWorks2008数据库中创建⼀个名为Products的同义词，它将引⽤
AdventureWorksDW2008 数据库中的 dbo.DimProduct 表。

在有了⼀个新的同义词后，现在打开⼀个新的査询窗⼝，然后输⼊下列代码:
USE AdventureWorks2008 GO
SELECT ProductKey, EnglishProductName, StandardCost FROM dbo.Products
注意，该査询从AdventureWorksDW数据库中返回了 606⾏，但不需要像下⾯的⽰例这样限定对象名称：
USE AdventureWorks2008 GO
SELECT ProductKey, EnglishProductName, StandardCost FROM AdventureWorksDW2008.dbo.DimProduct
同义词可以⽤来引⽤任意数据库或⼀个链接服务器上的视图、表、存储过程和函数，
从⽽简化应⽤程序的数据访问。

5 .8 编程对象
如前所述，有关编程对象创建及其逻辑的内容不在本书的讨论范围之内，但这些对象
的⽬的和基本⽤途与这⾥的讨论还是有关的。

数据库管理员需要理解编程对象如何影响数
据库⾏为。

最重要的⼀个⽅⾯通常是安全性，第6 章将会介绍相关内容。

5 . 8 . 1 存储过程
存储过程是存储在服务器的数据库中的T-SQL或托管代码的命名集合。

SQL Server存储过程和其他编程语⾔中的过程很相似，因为它们都被⽤来封装重复性任务。

存储过程⽀
持⽤户声明的变量、条件执⾏以及许多其他编程功能。

可以使⽤传统的T-SQL语⾔，或像C#或 等.NET托管语⾔编写存储过程。

第 14章将讨论使⽤托管代码创建复杂的存储过程的好处，它们可以突破T-SQL的局限性。

存储过程最主要的⽤途是封装业务功能，并创建可重⽤的应⽤逻辑。

由于存储过程存
储在服务器上，因此对业务逻辑的更改可以在⼀个位置完成。

存储过程还提供对数据库数据的可控修改。

通常，给予⽤户修改数据库表中数据的权
限是⼀个很糟糕的想法。

可以创建仅执⾏应⽤程序所需的修改的存储过程。

然后可以授予
⽤户执⾏存储过程的权限来完成所需的数据修改。

⽤户创建的存储过程⽐即席Transact-SQL更有效率，也更安全。

它们⼤⼤减少了查询和修改数据库所需的⽹络数据包数,⽽为了有效地重⽤这些存储过程,可以编译并长期缓存它们。

除了⽤户创建的存储过程之外，SQL Server还提供了数百种系统存储过程。

这些系统存储过程可以⽤来检索系统信息和修改底层系统对象、它们涵盖了从返回⼀个所有登录⽤
户列表的简单存储过程，到创建数据库维护作业的复杂存储过程。

后⾯章节在讨论相关主
题时会涉及其中⼀些存储过程。

5 .8 .2 函数
SQL Server 2008⽀持三种类型的⽤户⾃定义函数：标量函数、表值函数和聚合函数。

SQL Server函数和其他编程语⾔中的函数⾮常相似。

它们接受参数，然后基于输⼊参数执⾏操作并返回值。

表值函数总是返回table数据类型。

标量函数和聚合函数可以返回除text、 ntext和image之外的任何数据类型。

⽤户定义函数都可以使⽤T-SQL或托管代码创建，但聚合函数除外，它总是使⽤托管
代码创建。

从效率和安全⽅⾯来说，⽤户⾃定义函数提供了许多和存储过程相同的好处。

它们的区别在于函数不能执⾏修改数据库状态的代码，⽽存储过程可以。

在 SQL Server Management Studio的 “对象资源管理器”中，系统函数被分成⼉类。

⼀些函数⽤来操作⽤户数据(如聚合函数和字符串函数)，⽽另⼀些则⽤来检索系统信息(如
安全性函数和元数据函数)。

5 .8 .3 触发器
触发器是由系统中其他⼀些动作引发并执⾏的存储的T-SQL或托管代码对象，它不能
直接执⾏。

SQL Server 2008中有两种触发器：DML触发器和DLL触发器。

1.DML触发器
数据操作语⾔(Data Manipulation Language, DML)触发器是由于 DML命令(INSERT、 UPDATE、DELETE)执⾏⽽执⾏的。

SQL Server 2008中有两种 DML触发器：After触发器和 Instead O f触发器。

After触发器
传统的触发器是After触发器，因为它们是在DML语句在具有已定义的触发器的表上执⾏ “之后”才执⾏的。

触发器中的代码其实也是导致触发器执⾏的事务的⼀部分。

触发
器主体中的任意ROLLBACK命令都会导致触发器和相关事务回滚。

Instead O f触发器
之所以称为Instead O f触发器，是因为执⾏的是该触发器中的命令，⽽不是实际上导致该触发器执⾏的事务。

Instead O f触发器主要⽤作将更新发送⾄包含UNION操作符的视图中引⽤的表的⽅法，因为不能直接更新这些视图。

要想了解更多有关Instead Of触发器和这些分区视图的信息，请参阅 Paul Turley和 Dan Wood编写的《T-SQL编程⼊门经典(涵
盖 SQL Server & 2005)》⼀书。

2. DDL触发器
数据定义语⾔(DDL)触发器是由于DDL命令(CREATE、DROP、ALTER)执⾏⽽执⾏的,
其作⽤域可在数据库或服务器范围。

DDL触发器可以审核或防⽌数据库和服务器修改。

下⾯的例⼦演⽰了如何创建⼀个数据库级别的DDL触发器，以此审核对数据库做出的修改。

⾸先创建⼀个表，记录数据库中发⽣的所有DDL事件。

⽅法是运⾏下列脚本：
USE AdventureWorks2008
GO
CREATE TABLE AuditLog ( EventID int IDENTITY (1,1) NOT NULL, LoginName EventTime DDLEvent Eventdata
varchar(75) NOT NULL, datetime NOT NULL, varchar(100) NULL, xml NOT NULL)
GO
接着创建⼀个触发器，它将在任何DDL级别的事件执⾏时执⾏。

该触发器使⽤了⼀个名
为 EVENTDATA的系统函数，后者返回⼀个XML结果集，包含有关DDL事件的所有信息。

该触发器使⽤XQUERY命令将XML数据拆解成关系结果集，然后将其插⼊到审核表中。

USE AdventureWorks2008
GO
CREATE TRIGGER DatabaseAudit ON DATABASE
FOR DDL_DATABASE⼀LEVEL_EVENTS
AS
DECLARE @data XML = EVENTDATA() INSERT AuditLog(LoginName, EventTime,DDLEvent,EventData)
VALUES
(SYSTEM_USER
r
GETDATE()
r
@data.value(' (/EVENT_INSTANCE/TSQLCommand) [1] * r ，nvarchar (2000)•) ,@data) RETURN
GO
现在测试该触发器，创建并删除⼀个名为TriggerTest的表，然后査询审核表看是否捕获了想要的信息：
USE AdventureWorks2008
GO
CREATE TABLE TriggerTest ( Columnl int ,Column2 int) DROP TABLE TriggerTest SELECT * FROM AuditLog
结果应该是与图5-31类似的两⾏(当然，您的 LoginName和 EventTime会有所不同)。

注意：
为了确保此触发器不会⼲扰本书后⾯的⼀些练习，可以执⾏下列命令刪除它。

DROP trigger DatabaseAudit ON DATABASE
5 . 8 . 4 程序集
程序集是包含数据库编程对象的⽂件，通过使⽤Visual Studio创建。

它们可以包含使⽤任何托管语⾔(如 C#或 )编写的存储过程、函数、触发器、聚合函数和数据类型。

由于集成了公共语⾔运⾏时(CLR),所以可在数据库引擎中可以直接访问它们。

在某些特定的情况下，例如需要密集⽽递归的数学操作或复杂的字符串操作时，托管代码
⽐传统的T-SQL编程更有优势。

第 14章将更详细介绍CLR对象和集成。

从第 14章可以知道，数据库管理员和开发⼈员之间关系紧张。

通常，这种紧张程度会
因为数据库管理员缺乏编程技能⽽加剧。

随着CLR和数据库引擎集成，数据库管理员了解
编程并与和系统交互的开发⼈员沟通变得⽐以往更重要。

通过 Visual Studio、Transact-SQL 或 SQL Server Management Studio 可将 CLR 程序集导⼊到数据库⼱。

这⾥主要讨论 Transact-SQL 和 SQL Server Management Studio。

为了理解
这些内容，您需要 t 传⼀个⽂件。

第 14章将介绍如何创建这个⽂件，⽬前只能想象。

要使⽤ SQL Server Management Studio添加新程序集，依次展开 “数据库 ” | “AdventureWOrks2008” | “可编程性”节点，右击 “程序集 ”节点，然后单击 “新建程序集”命令。

在 “新建程序集”对话框(如图5-32所⽰)中，浏览⾄该程序集，指定程序集所有者并
为其设置权限。

权限集定义了授予程序集多少访问权来执⾏包含的操作。

“安全”列表项把程序集限
于当前数据库和连接。

“外部访问”列表项使程序集能够与操作系统、⽹络和⽂件系统交互。

“⽆限制”列表项使程序集有“外部访问”的所有权限，并且还可以调⽤⾮托管代码。

第
6 章和第14章将更详细地讨论程序集权限集。

在将程序集添加到数据库中之后，就可以将链接⾄此程序集的存储过程、函数、触发
器、类型或聚合函数添加到数据库中(要了解具体过程，可参阅第14章)。

5 .8 .5 类型
类型包括系统数据类型、⽤户定义数据类型、⽤户定义表类型、⽤户定义类型以及数
据库中使⽤的任意XML架构集合。

第4 章介绍了系统数据类型，因此这⾥只介绍其他类型。

1 .⽤户定义数据类型
⽤户定义数据类型是系统类型的别名。

这些类型仅存在于创建它们的数据库中。

⽤户
定义数据类型常⽤于提供⼀个直观的数据类型名称及维护不同表之间数据类型⼀致性。

例如，如果我要求5 位不同的数据库开发⼈员创建⼀个存储个⼈信息的表，那我可能
会得到5种不同的解决⽅案。

该表可能包含个⼈姓、名、地址和电话号码列，但很可能这
5 位不同的数据库开发⼈员对于任⼀指定的字段提供出3 种不同的数据类型。

例如，⼀位
开发⼈员可能使⽤varchar(13)表⽰电话号码，认为电话号码应表⽰为(111)111-1111。

另⼀位幵发⼈员可能考虑得更全⾯，提供了国际代码，因此将电话号码的类型指定为varchar(25)。

为避免后⾯可能发⽣的类型冲突，可以指定使⽤⽤户定义数据类型。

要以图形化⽅式创建⽤户定义数据类型，可在“对象资源管理器”中依次展开“数据
库” | AdventureWorks2008| “可编程性”⼁“类型”节点，右击 “⽤户定义数据类型”节点，然后单击“新建⽤户定义数据类型”命令。

图 5-33展⽰了在.dbo架构中创建⼀个基于系统类型char(5)的 ZipCode数据类型。

通过在合适的⽂本框中进⾏指定，⽤户定义数据类型也可以绑定⾄数据库默认值和规则。

默认
值和规则将在本章后⾯介绍。

⽤户定义数据类型有⼀些缺点。

例如，它们对数据库应⽤程序是不透明的。

例如，应
⽤程序程序员将⽆法在使⽤ZipCode数据类型的应⽤层实例化变量。

程序员必须知道基类型是char (5)。

除了应⽤层的可见性之外，⽤户定义数据类型只存在于创建它们的数据库中。

例如，AdventureWorks2008 数据库中的 ZipCode 数据类型可能与 AdventureWorksDW2008
数据库中的ZipCode数据类型不同。

⽽且，这种数据类型⼀旦创建就不能更改。

换句话说，如果后⾯想将ZipCode数据类型改成char(9)来存放“zip+4” 编号形式的邮编，必须删除并重建它。

遗憾的是，要删除它，要求不能在任何地⽅使⽤它。

2 .⽤户定义表类型
SQL Server 2008提供了创建表⽰表定义的⽤户定义表类型的功能。

可使⽤⽤户定义表类型声明变量或将其⽤作存储过程和函数的参数，使使⽤信息集更加容易。

要创建⽤户定
义表类型，使⽤CREATE TYPE提供表定义。

下列代码创建了⼀个⽤于表⽰⼀组客户的表
结构，然后将它⽤作⼀个存储过程的输⼊参数：
CREATE TYPE Customers AS TABLE ( CustomerName varchar (50), CreditLimit decimal, Address varchar(50), PhoneNumber
varchar(10));
GO
DECLARE Scustomers Customers INSERT INTO ^customers(CustomerName, CreditLimit, Addressr PhoneNumber) VALUES (••' 2300.00, •，），
(••, 2300.00, ••),
(•\ 2300.00,'，、 " ）
GO
EXEC usp_AddCustomers @customers GO
提⽰：
上⾯的代码只是作演⽰之⽤，不会真正执⾏，因为Adventure Works数据库中并没有名
为 usp AddCustomers的存储过程.
3 .⽤户定义类型
⽤户定义类型(UDT)和⽤户定义数据类型⾮常相似，只是它们由托管代码创建，并被
定义在导⼊到SQL Server数据库的程序集中，UDT可以⾮常复杂，⽽且可以定义没有对应系统类型的⾃定义数据类型。

例如，可以创建⼀个UDT来定义⼀个真正存储为数字的
社会安全号码数据类型，但不截断前导零。

我们还可以在托管代码中利⽤正则表达式，以
更容易并更准确地验证社会安全号码。

UDT的其他优势在于它们对应⽤层是可见的。

由于它们定义在程序集中，⽽程序集可
以在数据库应⽤程序引⽤，这样就可以使⽤本地UDT实例化参数。

然⽽，⽤户定义类型
并不是完美的，在跨数据库应⽤程序中，⽤户定义类型可能会产⽣问题，因为UDT是特
定于数据库的。

不过，如果在每个数据库创建UDT时引⽤了同样的CLR程序集，这个限
制就会减弱。

如前所述，第 14章包含有关CLR程序集和可⽤它创建的数据库对象(包括
UDT)的更多信息。

5 .8 .
6 默认值
可以在数据库级创建⼀个单独的默认值，然后将它绑定到数据库中的任意表列，⽽不
是在表列上创建默认约束。

默认值已被废弃，所以建议在新的开发⼯作中不再使⽤它们。

可以在“对象资源管理器”⼁“数据库” | “可编程性”节点下找到默认值，但是必须⽤T-SQL
创建它们。

下⾯的例⼦展⽰了如何创建⼀个默认的社会安全号码，然后将其绑定到
dbo.Driver 表的 SocSecNum 列。

USE AdventureWorks2008 GO
IF EXISTS(SELECT * FROM sys.default_constraints WHERE name = •DF⼀Driver⼀SocSecNum1> ALTER TABLE dbo.Driver DROP CONSTRAINT DF_Driver⼀SocSecNum CREATE DEFAULT dfItSocSecNum AS '000000000'
GO
sp bindefault •dfItSocSecNum*, 1dbo.Driver.SocSecNum1
5 .8 .7 规则
和默认值⼀样，规则也被弃⽤了。

规则类似于CHECK约束。

不过，它是在数据库级
别创建的，然后绑定到匹配指定数据类型的任意列。

下⾯的例⼦展⽰了如何创建⼀条规则,
在基于字符的列上强制数字型数据，以及如何把该规则绑定到SocSecNum列。

USE AdventureWorks2008 GO
CREATE RULE AllNumRule AS @value LIKE 1 [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].
GO
sp_bindrule 'AllNumRule *, * dbo.Driver.SocSecNum *
5 . 9 ⼩结
本章包含⼤量信息，但仍然有⼤景内容未能介绍。

单单 SQL Server数据库及其包括的功能就可以写⼀本书，但是本书并不是这样⼀本朽。

本章的⽬的是帮助您了解SQL Server 数据库中提供的对象，以及如何创建和管理它们。

后续的章节将从数据库管理员的⾓度深
⼊讨论 SQL Server 2008其他各个⽅⾯。

在第 6章中，您将学会如何保护SQL Server 2008服务器、数据库和所有组成SQL Server
的相关对象的安全。

该章将详细介绍许多新特性(如 SQL Server证书、凭据和加密)，以及核⼼的安全功能，通过这些内容的学习可确保您的服务器尽可能安全。