在T-SQL DML 3级楼梯：在SQL Server实现关系模型

 

                                                                              在T-SQL DML 3级楼梯：在SQL Server实现关系模型         

    Gregory Larsen，2017 / 08 / 02（首次公布：2011 / 11 / 09）

该系列                                                            

这篇文章是楼梯系列：以T-SQL DML的楼梯             

这楼梯将为您提供一个基本的了解如何从SQL Server表的数据，使用SQL Server的Transact-SQL（T-SQL）方言。DML是数据操纵语言和数据处理语言方面。它包括语句选择、插入、更新和删除。这个楼梯还提供了SQL语言的一些历史和关于集合论的一些一般概念。每个级别都将建立在前一级，所以当您完成时，您将很好地理解如何从SQL Server中选择和修改数据。             在这个阶梯的前一级，我向您提供了基本SELECT语句和SQL历史的信息。这些级别为理解如何检索数据以及SQL环境是如何演变的提供了基础，因为技术和技术解决方案已经改变了超时工作。在这一层中，我将探讨如何实现基于关系模型的简单SQL Server数据库。在开始创建数据库之前，首先让我介绍一下关系模型创建者的一点历史。

关系数据建模之父             

关系数据库设计的概念最早由Edgar F. Codd在1970，有一篇题为“一个关系模型数据的大型共享数据银行”。Codd开发建模理论在IBM工作。IBM没有跳够快在科德的数据建模的概念，因此无法提供关系数据库引擎，开发Codd新关系数据建模理论的第一供应商。Codd的关系模型的概念，现在是用来在SQL Server和其他关系数据库引擎创建关系数据库的框架。             

Codd出生在波特兰的英国岛和研究数学和化学在加入英国皇家空军，成为一名二战飞行员。他于1948移居纽约，开始为IBM工作，在那里他是一位数学程序员。他四处漂流了几年，最后搬到了加利福尼亚，到IBM圣若泽研究实验室工作。科德继续工作细化和证明的关系数据模型，直到上世纪90年代，当他每况愈下的健康状况迫使他退休。2003年4月18日Edgar F. Codd去世享年79岁。             

在SQL Server中实现关系模型             

这个楼梯并不打算教您有关关系数据建模或数据库设计，而是只向您展示如何从关系模型中创建SQL Server数据库。但是在我为您提供创建SQL Server数据库的代码块之前，我们首先需要探索一个将要实现的关系数据模型。我的简单模型将包含几个实体（数据表），其中包含主键定义和不同实体之间的一些关系（外键约束）。我的简单关系模型将是一个简单的酒店预订系统。此预订系统将需要跟踪客户预订信息。图1说明了这个简单的关系模型，我将使用T-SQL实现：

图1：一个由6个表组成的简单关系数据库模型             通过查看这个模型，您可以看到它包含许多实体（由方框表示）来跟踪与保留相关的信息。每个实体由若干属性（列）组成，其中一个或多个属性被标识为主键（粗体和下划线）。也代表了实体之间的一些关系（由箭头表示），说明不同实体是如何相互关联的。我将采用这种实体、属性、主键和关系模型，然后开发一个表示该关系模型设计的物理SQL Server数据库。             要从这个模型构建一个物理数据库，我们需要根据这个模型来定义我们将要定义的SQL Server中的不同对象。对于图1中的每个实体或框，我将在SQLServer中创建一个表。对于每个实体的每个属性，我将在关联表中创建一个列。对于每个主键，我将创建一个唯一的聚集索引（注意，也可以使用唯一的非聚集索引创建主键）。在索引参考在HTTP指标楼梯更多信息：/ / www.sqlservercentral。COM /楼梯/ 72399 /）。最后，对于每个关系，我将创建外键约束。             为了开始构建数据库，我首先需要创建一个SQL Server数据库来保存我计划创建的所有新数据库对象。我的数据库将被称为roomreservation。我将用下面的T-SQL代码创建数据库：             

创建数据库roomreservation；             

从我的模型构建我roomreservation数据库对象，然后创建表对象。要在sql server中创建一个表，我需要使用创建表语句。通过创建表语句，我将能够定义每个表中的每个表和所有列。下面是创建SQL Server表的简单语法：             

创建表table_name >（<     < column_definition > […]）；             哪里:             

<表名> = table_name             < column_definition > = column_name data_type，[空不空| ]            

 有关创建表语句的完整语法，请参阅联机的SQLServer图书。             

我创建的第一个表将是使用清单1中的代码创建的Customer表。

使用roomreservation；             

创建表的客户（dbo。CustomerID int不空，             n

varchar（50）非空名，            

 nvarchar（50）不为空的姓，             

地址1 nvarchar（100）不为空，             

演说2 nvarchar（100）空，             

nvarchar（22）不为空的电话号码，            

 将varchar（100）空，             

varchar（100）不为空的城市，             

stateprovince nvarchar（100）不为空，             

为nvarchar（100）不为空）；

清单1：创建客户表             

在这个代码中，当我创建我的客户表时，我创建了我需要的所有列，但是我也指定了当记录插入或更新到这个表中时，列是否需要一个值。我通过在某些列上指定null而实现，而我指定的其他列为null。             

如果将列定义为非NULL，则意味着除非您用实际值填充此列，否则无法创建记录。而使用null规范定义一个列意味着可以创建一个行而不为该列指定一个值，或者另一种方法是列允许一个空值。我在创建表的语句上面我允许列演说2和EmailAddress支持空值，而所有其他的柱子需要值将提供创建一个行时。            

 这个创建表语句并没有完全定义我的客户表，因为它是在我上面的关系数据库模型中表示的。我还需要创建一个主键约束的列ID。这个主键约束将确保表中没有两个记录具有相同的ID值。该代码创建主键列表2。

使用roomreservation；            

 去            

 ALTER TABLE dbo.客户添加约束             

pk_customer主键聚集（ID）；

清单2：向客户表添加主键约束             

此表语句向客户表添加了主键约束。该主键将在聚集索引命名pk_customer窗体的创建。             

在Transact-SQL语言中，通常有不止一种方法来做同样的事情。另外，我可以通过运行清单3中的创建表语句一次性创建我的客户表和主键。             

使用roomreservation；             

去             创建表的客户（dbo。             

CustomerID INT NOT NULL约束pk_customer主键，             

nvarchar（50）非空名，            

 nvarchar（50）不为空的姓，             

地址1 nvarchar（100）不为空，            

 演说2 nvarchar（100）空，             

nvarchar（22）不为空的电话号码，             

将nvarchar（100）空，             

varchar（100）不为空的城市，             

stateprovince nvarchar（100）不为空，             

为nvarchar（100）不为空）；             

清单3：用主键创建客户表的另一种方法             

在这一点上，我已经向您展示了如何创建具有定义主键的表。唯一要展示给您的是如何创建外键约束。但在此之前，让我先向您提供创建上述关系数据库模型中其余表和主键的脚本。您可以在清单4中找到它。

使用roomreservation；             去             

创建表的预订（dbo.             

reservationid INT NOT NULL，             

到达日期日期不为空，             

日期日期不为空，             

dailyrate smallmoney不空，             

reservationstatusid INT NOT NULL，             

CustomerID int不空，            

 roomtypeid INT NOT NULL）；             

ALTER TABLE dbo。预订添加约束             

pk_reservation主键聚集（reservationid）；             

创建表dbo.roomtype（            

 roomtypeid INT NOT NULL，             

RoomDesc NVARCHAR（1000）不为空）；             

修改表dbo.roomtype添加约束             

pk_roomtype主键聚集（roomtypeid）；             

创建表dbo.reservationstatus（             

eservationstatusid INT NOT NULL，            

 reservationstatusdesc nvarchar（50）不为空）；             

修改表dbo.reservationstatus添加约束             

pk_reservationstatus主键聚集（reservationstatusid）；             

创建表dbo.paymenttype（             

paymenttypeid INT NOT NULL，            

 PaymentTypeDesc NVARCHAR（50）不为空）；             

修改表dbo.paymenttype添加约束             

pk_paymenttype主键聚集（paymenttypeid）；             

创建表dbo.customerpaymenttype（             

paymenttypeid INT NOT NULL，             CustomerID int不空，             

paymentnotes nvarchar（2000）空）；             

修改表dbo.customerpaymenttype添加约束             

pk_customerpaymenttype主键聚集（paymenttypeid，CustomerId）；             

清单4：创建附加表和主键约束

外键约束强制在相互关联的两个表之间执行引用完整性。外键约束定义的表是“引用表”，在表中插入或更新行的任何时刻，都需要在另一个表中有一个相关的记录，称为“引用”表。在图1的关系模型中，这些外键关系用箭头表示。外键约束仅在关系中的一个表上定义。在我的图表中，外键约束将在那些具有箭头尾部（非尖末端）的表上定义。             定义外键约束关系模型在我我需要改变每个引用表中添加约束。清单5是T-SQL代码可以用来创建预订表的外键约束。这个约束确保记录不被插入或更新表中保留不匹配的记录在客户表中发现，基于数字。             

使用roomreservation；             去             

ALTER TABLE dbo。预订             

添加外键约束fk_reservation_customerpaymenttype（ID）             

引用dbo.客户（ID）；             

清单5：在引用客户表的预订表上创建外键约束             

为了完成我的设计，我需要实现其他所有外键约束的图1在我的模型识别。清单6包含了在我的数据模型中创建额外外键约束的变更表语句。             

使用roomreservation；             去            

 ALTER TABLE dbo。预订             

添加外键约束fk_reservation_roomtype（roomtypeid）             

引用dbo.roomtype（roomtypeid）；             

ALTER TABLE dbo。预订             

添加外键约束fk_reservation_reservationstatus（reservationstatusid）             

引用dbo.reservationstatus（reservationstatusid）；             

修改表dbo.customerpaymenttype             

添加外键约束fk_customerpaymenttype_paymenttype（paymenttypeid）             

引用dbo.paymenttype（paymenttypeid）；             

修改表dbo.customerpaymenttype             

添加外键约束fk_customerpaymenttype_customer（ID）             

引用dbo.客户（ID）；

清单6：创建额外的外键约束             验证数据库的设计             一旦我完成了从一个数据模型构建一个数据库，我应该验证实现的设计，以确保它是正确的。这个验证过程是为了确保我构建到物理数据库中的所有数据完整性规则正确实现。在我的设计中，这里是我需要验证的规则。             

所有插入或更新的行必须为定义为null的任何列定义一个特定的值。             

主键的列不允许重复值。             

有外键约束不允许数据没有引用表中的列相匹配的记录             

在验证数据完整性规则之前，我首先需要用一些有效的数据填充引用的表。我将使用清单7中的代码用一些有效的数据填充这些表：            

 使用roomreservation；             去             

SET NOCOUNT ON；             

——创造paymenttype记录             

插入paymenttype值（1，'visa”）；             

插入paymenttype值（2，'mastercard”）；             

插入paymenttype值（3，美国运通）；             ——创造顾客             

插入客户值             

（1，'greg '，'larsen '，'123一些地方             

，空，'123-456-7890，null，'mycity '，'会'，'12345）；            

 ——创建保留状态            

 插入reservationstatus值（1，'booked”）；             

插入reservationstatus值（2，'cancelled”）；             

——Create Room Type             插

入类型的值（1，'kingsize”）；             

插入类型的值（2，'queen”）；            

 插入类型的值（3，‘’）；            

 清单7：插入初始数据             

为了验证我在数据库中构建的数据完整性规则，我将运行清单8中的INSERT语句。            

使用roomreservation；             去             

-违反非空约束             

插入到预定值（1，'2011-8-1 5:00”             

，'2011-8-2 9:00”             （150.99，null，1，1）；             

-违反主键约束            

插入类型的值（3，'suite”）；             -违反外键约束            

插入customerpaymenttype值（1,2，“需要一个互联网连接）；             清单8：用INSERT语句测试各种约束             

这些插入语句的每一个失败因为他们违反数据完整性规则，是建立在roomreservation数据库。第一个插入语句违反了reservationstatusid列的非空验证检查。             

第二插入语句违反主键约束，放在类型表。这是在INSERT语句插入3列的值为roomtypeid。问题是已经有一个记录表的类型3 roomtypeid价值。            

 最后一个INSERT语句违反了customerpaymenttype表的外键约束。在这个特殊的INSERT语句没有CustomerID和客户表中的值为2。            

 要正确插入这些记录，需要插入插入的数据值。一旦数据清理完毕，我就可以将这些新数据插入适当的表中。清单9包含清理INSERT语句将所有数据完整性检查和被成功地插入到数据库相应的表中的roomreservation：

使用客房预订；              去              

非空约束violates键槽              

插入到预定值（1，2011 - 8 - 1 5：00 PM的             

 “2011年8 2 9：00 AM的              ，150.99,1,1,1）；              

一级- violates Key约束              

插入到roomtype值（4，套房）；             

 ——violates对外键约束            

  插入到customerpaymenttype values（1,1，会需要个互联网连接的）；              

listing 9：额外的约束测试              

关系数据库的设计              

我预订以demonstrates如何采取一个关系模型的实施和使用它的SQL Server数据库。城市使用的空槽，主密钥，密钥和外汇的约束，在建的数据完整性规则吧，到我的数据库设计。这允许我驻扎在这些规则数据库定义的标的，而不是有两个写的代码在我的业务处理层的两个数据验证规则。这允许从做中学的SQLServer数据库引擎的数据完整性这两个perform支票给我。              

城市的理解和创造你的数据库在关系数据库设计的模型，你会大厦A鲁棒和高效的数据库实现，你可以建立在对数据完整性检查到的数据库。                            

 这条冰方《通往天国的T - SQL DML             

- 设计上我们的RSS饲料和get notified，只要我们发布一个新的水平的天堂！vv

转载于:https://www.cnblogs.com/dzxy17/p/7711375.html