Stairway to T-SQL DML Level 3

系列
这篇文章是阶梯系列的一部分：通向t-sql DML的阶梯
这段阶梯将为您提供一个基本的理解，即如何使用SQL Server的Transact-SQL（T-SQL）方言来处理来自SQL Server表的数据。DML是数据操作语言，它是处理数据的语言的一个方面。它包括语句选择、插入、更新和删除。这段阶梯还将提供一些SQL语言的历史和一些关于集合理论的一般概念。每一个级别都将建立在前面的级别上，所以在您完成的时候，您将很好地理解如何从SQL Server中选择和修改数据。
在这段阶梯的前一层，我向您提供了关于基本SELECT语句和SQL历史的信息。这些级别为您提供了理解如何检索数据的基础，以及随着技术和技术解决方案的变化，SQL环境是如何演变的。在这个级别上，我将探索如何实现一个基于关系模型的简单SQL Server数据库。在开始创建数据库之前，首先让我分享一下关于关系模型创建者的一些历史。

在SQL Server 中实现关系模型
这个阶段不打算教你有关数据模型或是数据库设计的知识，仅仅是告诉你然后从一个关系模型中创建一个SQL Server 数据库。但是在我给你提供一个创建SQL Server 数据库的代码块之前，我们首先要做的是探索这个将被执行的关系数据模型。我的简单模型将会包含几个实体(数据表)其中包含主键定义和不同实体之间的一些关系(外键约束)。我的简单模型将会是一个酒店预订系统这个预订系统需要追踪客户的预订信息。图一阐述我即将要用T_SQL实现的简单关系模型。

图一:一个由6个表组成的简单关系数据模型
通过查看此模型，你可以看到它包含一些实体(由框表示)来追踪相关信息。每个实体都包含一些属性(列)，其中一个或多个属性被识别成主键(带粗体或是下划线的名称)。还表示了实体(由箭头表示)之间的关系，它显示了不同实体是如何相互关系的。我将会使用这种模型的实体、属性、主键和关系，然后开发一个表示该关系模型设计的物理SQL Server数据库。
要从此模型构建物理数据库，我们需要根据此模型识别SQL Server中将要定义的不同对象。对于表一的每个实体或是框，我将会在SQL Server创建一个表。对于每个实体中的每个属性，我将会在关联的表中创建一个列。对于每个主键，我将会创建一个唯一的聚簇索引(注意:主键也可以使用唯一的非聚簇索引创建。有关索引的更对信息，请参阅索引:http://www.sqlservercentral.com/stairway/72399/ )。最后，对于每个关系，我将创建一个外键约束。
要开始构建数据库，我首先要创建一个SQL Server数据库来保存我打算创建的新的数据库对象。我的数据库将命名为RoomReservation。我将使用以下T_SQL代码来创建我的数据库:

要开始从我的模型中构建我的RoomReservation数据库，我将创建表对象。要在SQL Server创建一个表，我需要使用CREATE TABLE语句。使用CREATE TABLE 语句，我将能够定义每个表和在表中的每个列。以下是创建SQL Server表的简单语法:

Where:
= Name of table
= column_name data_type,[NULL | NOT NULL]
CREATE TABLE 语句的完整语法是指线上的SQL Server 。
我创建的第一个表是Coustomer,使用清单1中的代码。

清单1:创建Customer表
在这段代码中，当我创建了我的客户表时，我创建了我需要的所有列，但是我也指定了当这个表中插入或更新记录时，列是否需要一个值。我通过在某些列上指定NOT NULL来实现，而其它列我也指定了NULL。
如果一个列被定义为NOT NULL，这就意味着只有当你用一个实际值填充列时你才可以创建记录。而使用NULL定义一个列时意味着你可以创建一行而不指定此列的值，或是在劣中允许空值。在我上面的CRWATE TABLE语句中，我允许列Address2和EmailAddress为空值，而其余所有列都需要在创建行时提供一个值。
当它呈现以上的关系数据库模型是，CREATE TABLE 语句并没有完全定义Costemer表。我仍需要在列CustomerID中创建一个主键约束。这个主键约束将确保在表中没有两个相同的CustomerID值的记录……创建主键的约束如清单2。

清单2:将一个PRIMARY KEY 约束添加到Customer表
此ALTER TABLE语句向我的Customer表添加了一个主键约束。该主键将以名为PK_Customer的聚簇索引的形式创建。
在Transact_SQL语言中，通常有多种方式来完成一件事。或者，我可以通过运行清单3中的CREATE TABLE 语句一次创建我的Customer表和主键。

清单3：用主键创建Customer表的另一种方法
在这一点上，我已经向您展示了如何用一个已定义的主键来创建一张表。剩下要向您展示的是如何创建一个外键约束。但在此之前，让我首先向您提供在上面的关系数据库模型中创建其余表和主键的脚本。您可以在清单4中找到它。
USE RoomReservation;
GO

CREATE TABLE dbo.Reservation (
ReservationId INT NOT NULL,
ArrivalDate DATETIME NOT NULL,
DepartureDate DATETIME NOT NULL,
DailyRate SMALLMONEY NOT NULL,
ReservationStatusID INT NOT NULL,
CustomerId INT NOT NULL,
RoomTypeID INT NOT NULL);

ALTER TABLE dbo.Reservation ADD CONSTRAINT
PK_Reservation PRIMARY KEY CLUSTERED (ReservationId);

CREATE TABLE dbo.RoomType (
RoomTypeId INT NOT NULL,
RoomDesc NVARCHAR(1000) NOT NULL);

ALTER TABLE dbo.RoomType ADD CONSTRAINT
PK_RoomType PRIMARY KEY CLUSTERED (RoomTypeId);

CREATE TABLE dbo.ReservationStatus (
ReservationStatusId INT NOT NULL,
ReservationStatusDesc NVARCHAR(50) NOT NULL);

ALTER TABLE dbo.ReservationStatus ADD CONSTRAINT
PK_ReservationStatus PRIMARY KEY CLUSTERED (ReservationStatusId);

CREATE TABLE dbo.PaymentType (
PaymentTypeId INT NOT NULL,
PaymentTypeDesc NVARCHAR(50) NOT NULL);

ALTER TABLE dbo.PaymentType ADD CONSTRAINT
PK_PaymentType PRIMARY KEY CLUSTERED (PaymentTypeId);

CREATE TABLE dbo.CustomerPaymentType (
PaymentTypeId INT NOT NULL,
CustomerId INT NOT NULL,
PaymentNotes NVARCHAR(2000) NULL);

ALTER TABLE dbo.CustomerPaymentType ADD CONSTRAINT
PK_CustomerPaymentType PRIMARY KEY CLUSTERED (PaymentTypeId, CustomerId);
清单4：创建额外的表和主键约束
一个外键约束加强了两个相互关联的表之间的引用完整性。
外键约束定义的表是“引用表”，它被要求在另一张表中有一个相关的记录，即所谓的“引用”表，任何时候在表中插入或更新一行。在图1的关系模型中，这些外键关系由箭头表示。外键约束只在关系中的一个表上定义。在我的图中，外键约束将被定义在那些有箭头尾部（非尖端）的表上。

为了在我的关系模型中定义这些外键限制，我需要修改每个引用表来添加约束。
清单5是我可以用来在预约表上创建外键约束的T-SQL代码。
这种约束确保在预约表中没有插入或更新记录，除非客户表中找到匹配的记录，基于CustomerId。
清单5：在预约表中引用Customer表创建外键约束
为了完成我的设计，我需要实现图1中我的模型中确定的所有其他外键。
清单6包含ALTER TABLE语句，以便在我的数据模型中创建额外的外键约束。

清单6：创建额外的外键限制

验证数据库设计
自从我完成了从数据模型构建数据库的工作，我就应该验证已实现的设计，以确保它是正确的。这个验证过程是为了确保我在物理数据库中构建的所有数据完整性规则都得到了正确的实现。在我的设计中，这是我需要验证的规则
插入或更新的所有行必须为定义为非空的任一列定义特定的值。
主键的列不允许重复的值
有外键的列不允许在引用表中没有匹配记录的数据
在我验证数据完整性规则之前，我首先需要用一些有效数据填充引用的表。我将使用列表7中的代码用一些有效数据来填充这些表：
USE RoomReservation;
GO
SET NOCOUNT ON;
– Create PaymentType records
INSERT INTO PaymentType VALUES(1,’Visa’);
INSERT INTO PaymentType VALUES(2,’MasterCard’);
INSERT INTO PaymentType VALUES(3,’American Express’);
– Create Customer
INSERT INTO Customer VALUES
(1,’Greg’,’Larsen’,’123 Some Place’
,NULL,’123-456-7890’,Null,’MyCity’,’MA’,’12345’);
– Create Reservation Status
INSERT INTO ReservationStatus VALUES (1,’Booked’);
INSERT INTO ReservationStatus VALUES (2,’Cancelled’);
– Create Room Type
INSERT INTO RoomType VALUES (1,’Kingsize’);
INSERT INTO RoomType VALUES (2,’Queen’);
INSERT INTO RoomType VALUES (3,’Double’);
列表7：插入初始数据
为了验证我在数据库中建立的数据完整性规则，我将运行列表8中的INSERT语句。
USE RoomReservation;
GO
– Violates NOT NULL constraint
INSERT INTO Reservation VALUES(1,’2011-8-1 5:00 PM’
,’2011-8-2 9:00 AM’
, 150.99,NULL,1,1);
– Violates Primary Key Constraint
INSERT INTO RoomType VALUES (3,’Suite’);
– Violates Foreign Key Constraint
INSERT INTO CustomerPaymentType VALUES (1,2,’Will need an internet connection’);
列表8：用INSERT语句测试各种约束条件
每一个这些INSERT语句应该都不合格，因为它们违反了建立在RoomReservation数据库中的数据完整性规则。第一个INSERT语句违反了ReservationStatusID列的非空验证检查。
第二个INSERT语句违反了放在RoomType表上的主键约束。这个INSERT语句试图为RoomTypeID列插入3的值。问题是这里早已经有一个记录在RoomType表中并且RoomTypeID的值为3。
最后一个INSERT语句违反了CustomerPaymentType表的外键约束。在这个特殊的INSERT语句中，Customer表中没有值为2的CustomerID。
要正确地插入这些记录，将需要清除已经插入的数据的值。一旦数据被清理干净，我就可以将这些新数据插入到适当的表中。列表9包含清理过的INSERT语句，它将传递所有数据完整性验证，并成功地在RoomReservation数据库中插入适当的表：
USE RoomReservation;
GO
– Violates NOT NULL constraint
INSERT INTO Reservation VALUES(1,’2011-8-1 5:00 PM’
,’2011-8-2 9:00 AM’
, 150.99,1,1,1);
– Violates Primary Key Constraint
INSERT INTO RoomType VALUES (4,’Suite’);
– Violates Foreign Key Constraint
INSERT INTO CustomerPaymentType VALUES (1,1,’Will need an internet connection’);
列表9：附加约束测试
关系数据库设计
我保留的示例演示了如何使用关系模型并使用它来实现SQL Server数据库。通过使用非空、主键和外键约束，这允许我在基本数据库定义中执行这些规则，而不是必须在我的业务处理层中编写代码来验证这些数据规则。
通过这样做，我允许SQL Server数据库引擎为我执行这些数据完整性检查。通过理解和创建你的围绕关系数据库模型的数据库设计，你将建立一个健壮且高效的数据库，你可以在其中构建数据完整性检查到数据库中。
这篇文章是通往T-SQL DML阶梯的一部分
注册到我们的RSS订阅源，就能及时得到我们发布在阶梯上的新标准的通知！