3.2　创建数据表

SQL Server 2016是一个关系数据库，关系型数据库中的数据表之间存在一定的关联关系。关系数据库提供了3种数据完整性规则：实体完整性规则、参照完整性规则、用户定义完整性规则。其中实体完整性规则和参照完整性规则是关系模型必须满足的约束条件。

实体完整性：指每条记录的主键组成部分不能为空值，也就是必须得有一个确定的值。现实世界中的实体是可区分的，即它们具有某种唯一性标识。映射到关系模型中也就是记录是可区分的，区分记录靠的就是主键。如果主键为空，则记录不可区分，进而与之相对应的现实世界中的实体也是不可区分的，与现实矛盾。

1．约束方法：唯一约束、主键约束、标识列

参照完整性：一个表的外键可以为空值。如果不为空值，则每一个外键值必须等于相关联的另外那张表中主键的某个值。

2．约束方法：外键约束

用户定义完整性：它是设计者为了保证表中某些行或者列的数据满足具体应用需求而自定义的一些规则。关系模型提供定义和检验这类完整性的机制，以便使用统一的系统方法处理，而不必由应用程序承担这一功能。

3．约束方法：检查约束、存储过程、触发器

SQL Server创建表的过程就是规定数据列的属性的过程，同时也是实施数据完整性约束的过程。

创建数据表需要确定表的列名、数据类型、是否允许为空，还需要确定主键、必要的默认值、标识列和检查约束。

表是用来存储数据和操作数据的逻辑结构，用来组织和存储数据，关系数据库中的所有数据都表现为表的形式，数据表由行和列组成，对数据库的操作，基本上就是对数据表的操作。SQL Server中的数据表分为临时表和永久表，临时表存储在tempdb系统数据库中，当不再使用或者退出SQL Server时，临时表会自动删除；而永久表一旦创建之后，除非用户删除，否则将一直存放在数据库文件中。SQL Server 2016中提供了两种创建数据表的方法：一种是通过对象资源管理器创建，另一种是通过Transact-SQL语句进行创建，下面分别详细介绍这两种方法。

3.2.1　数据类型

数据类型是一种属性，用于指定对象可保存的数据的类型，SQL Server 2016中支持多种数据类型，包括字符类型、数值类型以及日期时间类型等。数据类型相当于一个容器，容器的大小决定了装的东西的多少，将数据分为不同的类型可以节省磁盘空间和资源。

SQL Server还能自动限制每个数据类型的取值范围，例如定义了一个数据类型为int的字段，如果插入数据时插入的值的大小在smallint或者tinyint范围之内，SQL Server会自动将类型转换为smallint或tinyint，这样一来，在存储数据时，占用的存储空间只有int数据类型的1/2或者1/4。

SQL Server数据库管理系统中的数据类型可以分为两类，分别是：系统默认的数据类型和用户自定义的数据类型，下面分别介绍这两大类数据类型的内容。

1．系统数据类型

SQL Server 2016提供的系统数据类型有以下几大类，共25种。SQL Server会自动限制每个系统数据类型的值的范围，当插入数据库中的值超过了数据类型允许的范围时，SQL Server就会报错。

（1）整数数据类型

整数数据类型是常用的数据类型之一，主要用于存储数值，可以直接进行数据运算而不必使用函数转换。

① bigint

每个bigint存储在8字节中，其中1个二进制位表示符号，其他63个二进制位表示长度和大小，可以表示-2⁶³~2⁶³-1范围内的所有整数。

② int

int或者integer，每个int存储在4字节中，其中1个二进制位表示符号，其他31个二进制位表示长度和大小，可以表示-2³¹~2³¹-1范围内的所有整数。

③ smallint

每个smallint类型的数据占用了两个字节的存储空间，其中一个二进制位表示整数值的正负号，其他15个二进制位表示长度和大小，可以表示-2¹⁵~2¹⁵-1范围内的所有整数。

④ tinyint

每个tinyint类型的数据占用了一个字节的存储空间，可以表示0~255范围内的所有整数。

（2）浮点数据类型

浮点数据类型存储十进制小数，用于表示浮点数值数据的大致数值数据类型。浮点数据为近似值；浮点数值的数据在SQL Server中采用只入不舍的方式进行存储，即当且仅当要舍入的数是一个非零数时，对其保留数字部分的最低有效位上的数值加1，并进行必要的进位。

① real

可以存储正的或者负的十进制数值，它的存储范围为-3.40E+38~-1.18E-38、0以及1.18E-38~3.40E + 38。每个real类型的数据占用4个字节的存储空间。

② float [( n )]

其中n为用于存储float数值尾数的位数（以科学记数法表示），因此可以确定精度和存储大小。如果指定了n，则它必须是介于1和53之间的某个值。n的默认值为53。

其范围为-1.79E+308~-2.23E-308、0以及2.23E–308~1.79E+308。如果不指定数据类型float的长度，它占用8个字节的存储空间。float数据类型可以写成float(n)的形式，n指定float数据的精度，n为1~53的整数值。当n取1~24时，实际上是定义了一个real类型的数据，系统用4个字节存储它；当n取25~53时，系统认为其是float类型，用8个字节存储它。

③ decimal[ (p[ , s] )]和numeric[ (p[ , s] )]

带固定精度和小数位数的数值数据类型。使用最大精度时，有效值为-10³⁸+1~10³⁸-1。numeric在功能上等价于decimal。

p（精度）指定了最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。该精度必须是从1到最大精度38之间的值。默认精度为18。

s（小数位数）指定小数点右边可以存储的十进制数字的最大位数。小数位数必须是从0到p之间的值。仅在指定精度后才可以指定小数位数。默认的小数位数为0；因此，0 <= s <= p。最大存储大小基于精度而变化。例如：decimal(10,5)表示共有10位数，其中整数5位，小数5位。

（3）字符数据类型

字符数据类型也是SQL Server中最常用的数据类型之一，用来存储各种字母、数字符号和特殊符号。在使用字符数据类型时，需要在其前后加上英文单引号或者双引号。

① char(n)

当用char数据类型存储数据时，每个字符和符号占用一个字节的存储空间。n表示所有字符所占的存储空间，n的取值为1~8000。若不指定n值，系统默认n的值为1。若输入数据的字符串长度小于n，则系统自动在其后添加空格来填满设定好的空间；若输入的数据过长，将会截掉其超出部分。

② varchar(n|max)

n为存储字符的最大长度，取值范围为1~8000，但可根据实际存储的字符数改变存储空间，max表示最大存储大小是2³¹-1个字节。存储大小是输入数据的实际长度加2个字节。所输入数据的长度可以为0个字符。如varchar(20)，则对应的变量最多只能存储20个字符，不够20个字符时按实际存储。

③ nchar(n)

n个字符的固定长度的Unicode字符数据。n值必须在1到4000之间（含），如果没有在数据定义或变量声明语句中指定n，默认长度为1。此数据类型采用Unicode标准字符集，因此每一个存储单位占两个字节，可将全世界文字囊括在内。

④ nvarchar(n | max)

与varchar相似，存储可变长度Unicode字符数据。n值在1到4000之间（含），如果没有在数据定义或变量声明语句中指定n，默认长度为1。max指示最大存储大小为2³¹-1字节。存储大小是所输入字符个数的两倍加2个字节。所输入数据的长度可以为0个字符。

（4）日期和时间数据类型

① date

存储用字符串表示的日期数据，可以表示0001-01-01到9999-12-31（公元元年1月1日到公元9999年12月31日）间的任意日期值。数据格式为“YYYY-MM-DD”：

• YYYY：表示年份的四位数字，范围为0001~9999。
• MM：表示指定年份中的月份的两位数字，范围为01~12。
• DD：表示指定月份中的某一天的两位数字，范围为01~31（最高值取决于具体月份）。

该类型数据占用3个字节的空间。

② time

以字符串形式记录一天中的某个时间，取值范围为00:00:00.0000000~23:59:59.9999999，数据格式为“hh:mm:ss[.nnnnnnn]”：

• hh：小时的两位数字，范围为0~23。
• mm：分钟的两位数字，范围为0~59。
• ss：秒的两位数字，范围为0~59。
• n*：是0到7位数字，范围为0~9999999，它表示秒的小数部分。

time值在存储时占用5个字节的空间。

③ datetime

用于存储时间和日期数据，从1753年1月1日到9999年12月31日，默认值为1900-01-01 00:00:00，当插入数据或在其他地方使用时，需用单引号或双引号括起来。可以使用“/”、“-”和“.”作为分隔符。该类型数据占用8个字节的空间。

④ datetime2

datetime类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度。默认格式是：YYYY-MM-DD hh:mm:ss[.fractional seconds]，日期存取范围是0001-01-01~9999-12-31（公元元年1月1日到公元9999年12月31日）。

⑤ smalldatetime

smalldatetime类型与datetime类型相似，只是其存取的范围是从1900年1月1日到2079年6月6日，当日期时间值精度较小时，可以使用smalldatetime，该类型数据占用4个字节的空间。

⑥ datetimeoffset

用于定义一个采用24小时制与日期相组合并可识别时区的一日内时间。默认格式是“YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]”：

• hh：两位数，范围为-14~+14。
• mm：两位数，范围为00~59。

这里hh是时区偏移量，该类型数据中保存的是世界标准时间（UTC）值，例如要存储北京时间2011年11月11日12点整，存储时该值将是2011-11-11 12:00:00+08:00，因为北京处于东八区，比UTC早8个小时。存储该类型数据时默认占用10个字节大小的固定存储空间。

（5）文本和图形数据类型

① text

用于存储文本数据，服务器代码页中长度可变的非Unicode数据，最大长度为2³¹-1（2 147 483 647）个字符。当服务器代码页使用双字节字符时，存储仍是2 147 483 647字节。

② ntext

与text类型作用相同，为长度可变的Unicode数据，最大长度为2³⁰-1（1 073 741 823）个字符。存储大小是所输入字符个数的两倍（以字节为单位）。

③ image

长度可变的二进制数据，为0~2³¹-1个字节。用于存储照片、目录图片或者图画，容量也是2 147 483 647个字节，由系统根据数据的长度自动分配空间，存储该字段的数据一般不能使用INSERT语句直接输入。

（6）货币数据类型

① money

用于存储货币值，取值范围为正负922 337 213 685 477.580 8之间。money数据类型中整数部分包含19个数字，小数部分包含4位数字，因此money数据类型的精度是19，存储时占用8个字节的存储空间。

② smallmoney

与money类型相似，取值范围为正负214 748.346 8之间，smallmoney存储时占用4个字节存储空间。输入数据时在前面加上一个货币符号，如人民币为￥或其他定义的货币符号。

（7）位数据类型

bit称为位数据类型，只取0或1为值，长度1字节。bit值经常当作逻辑值用于判断TRUE（1）和FALSE（0），输入非零值时系统将其换为1。

（8）二进制数据类型

① binary(n)

长度为n字节的固定长度二进制数据，其中n是1~8000的值。存储大小为n字节。在输入binary值时，必须在前面带0x，可以使用0~9和A~F表示二进制值，例如输入0xAA5代表AA5，如果输入数据长度大于定义的长度，超出的部分会被截断。

② varbinary(n|max)

可变长度二进制数据。n可以是1~8000的值。max指示最大存储大小为2³¹-1字节。存储大小为所输入数据的实际长度+2个字节。

在定义的范围内，不论输入的时间长度是多少，binary类型的数据都占用相同的存储空间；而对于varbinary类型的数据，在存储时根据实际值的长度使用存储空间。

（9）其他数据类型

① rowversion

每个数据库都有一个计数器，当对数据库中包含rowversion列的表执行插入或更新操作时，该计数器值就会增加。此计数器是数据库行版本。一个表只能有一个rowversion列。每次修改或插入包含rowversion列的行时，就会在rowversion列中插入经过增量的数据库行版本值。

公开数据库中自动生成的唯一二进制数字的数据类型。rowversion通常用作给表行加版本戳的机制。存储大小为8个字节。rowversion数据类型只是递增的数字，不保留日期或时间。

② timestamp

时间戳数据类型，timestamp的数据类型为rowversion数据类型的同义词，提供数据库范围内的唯一值，反映数据修改的相对顺序，是一个单调上升的计数器，此列的值被自动更新。

在CREATE TABLE或ALTER TABLE语句中，不必为timestamp数据类型指定列名，例如：

此时SQL Server数据库引擎将生成timestamp列名；但rowversion不具有这样的行为。在使用rowversion时，必须指定列名，例如：

③ uniqueidentifier

16字节GUID（Globally Unique Identifier，全球唯一标识符），是SQL Server根据网络适配器地址和主机CPU时钟产生的唯一号码，其中，每个位都是0~9或a~f范围内的十六进制数字。例如，6F9619FF-8B86-D011-B42D-00C04FC964FF，此号码可以通过调用newid()函数获得，在全世界各地的计算机经由此函数产生的数字不会相同。

④ cursor

游标数据类型，该类型类似于数据表，其保存的数据中包含行和列值，但是没有索引，游标用来建立一个数据的数据集，每次处理一行数据。

⑤ sql_variant

用于存储除文本、图形数据和timestamp数据外的其他任何合法的SQL Server数据，可以方便SQL Server的开发工作。

⑥ table

用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

⑦ xml

存储xml数据的数据类型。可以在列中或者xml类型的变量中存储xml实例。存储的xml数据类型表示实例大小不能超过2 GB。

2．自定义数据类型

SQL Server允许用户自定义数据类型，用户自定义数据类型是建立在SQL Server系统数据类型基础上的，自定义的数据类型使得数据库开发人员能够根据需要定义符合自己开发需求的数据类型。自定义数据类型虽然使用比较方便，但是需要大量的性能开销，所以使用时要谨慎。当用户定义一种数据类型时，需要指定该类型的名称、所基于的系统数据类型以及是否允许为空等。SQL Server为用户提供了两种方法来创建自定义数据类型。下面将分别介绍这两种定义数据类型的方法。

（1）使用对象资源管理器创建用户定义数据类型

首先连接到SQL Server服务器，自定义数据类型与具体的数据库相关，因此在对象资源管理器中创建新数据类型之前，需要选择要创建数据类型所在的数据库，这里，按照第2章介绍的创建数据库的方法，创建一个名称为test的数据库，使用系统默认的参数即可。

创建用户自定义数据类型的具体操作步骤如下。

数据库创建成功之后，在【对象资源管理器】中依次打开【test】|【可编程性】|【类型】节点，右击【用户定义数据类型】节点，在弹出的快捷菜单中选择【新建用户定义数据类型】菜单命令，如图3-1所示。

打开【新建用户定义数据类型】窗口，在【名称】文本框中输入需要定义的数据类型的名称，这里输入新数据类型的名称为mingchen，表示存储一个地址数据值，在【数据类型】下拉列表框中选择char的系统数据类型，【长度】指定为8000，如果用户希望该类型的字段值为空的话，可以选择【允许NULL值】复选框，其他参数不做更改，如图3-2所示。

单击【确认】按钮，完成用户定义数据类型的创建，即可看到新创建的自定义数据类型，如图3-3所示。

（2）使用存储过程创建用户定义数据类型

除了使用图形界面创建自定义数据类型，SQL Server 2016中的系统存储过程sp_addtype也可以为用户提供使用T-SQL语句创建自定义数据类型的方法，其语法形式如下：

其中，各参数的含义如下。

• type：用于指定用户定义的数据类型的名称。
• system_data_type：用于指定相应的系统提供的数据类型的名称及定义。注意，未能使用timestamp数据类型，当所使用的系统数据类型有额外说明时，需要用引号将其括起来。
• null_type：用于指定用户自定义的数据类型的null属性，其值可以为“null”“not null”或“nonull”。默认时与系统默认的null属性相同。用户自定义的数据类型的名称在数据库中应该是唯一的。

【例3.1】自定义一个地址HomeAddress2数据类型，输入语句如下。

新建一个使用当前连接进行的查询，在打开的查询编辑器中输入上面的语句，输入完成之后单击【执行】按钮，即可完成用户定义数据类型的创建，执行结果如图3-4所示。

执行完成之后，刷新【用户定义数据类型】节点，将会看到新增的数据类型，如图3-5所示。

删除用户自定义数据类型的方法也有两种。第一种是在对象资源管理器中右击想要删除的数据类型，在弹出的快捷菜单中选择【删除】菜单命令，如图3-6所示。

打开【删除对象】窗口，单击【确定】按钮即可，如图3-7所示。

另一种方法就是使用系统存储过程sp_droptype来删除，语法格式如下：

type为用户定义的数据类型，例如这里删除HomeAddress2，T-SQL语句如下：

3.2.2　使用对象资源管理器创建表

对象资源管理器提供的创建表的方法可以让用户轻而易举地完成表的创建，具体操作步骤如下。

启动SSMS，在【对象资源管理器】中展开【数据库】节点下面的【test】数据库。右击【表】节点，在弹出的快捷菜单中选择【表】菜单命令，如图3-8所示。

打开【表设计】窗口，在该窗口中创建表中各个字段的字段名和数据类型，这里定义一个名称为member的表，其结构如下：

根据member表结构，分别指定各个字段的名称和数据类型，如图3-9所示。

表设计完成之后，单击【保存】或者【关闭】按钮，在弹出的【选择名称】对话框中输入表名称member，单击【确定】按钮，完成表的创建，如图3-10所示。

单击【对象资源管理器】窗口中的【刷新】按钮，即可看到新增加的表，如图3-11所示。

3.2.3　使用Transact-SQL创建表

在Transact-SQL中，使用CREATE TABLE语句创建数据表，该语句非常灵活，其基本语法格式如下：

其中，各参数说明如下。

• database_name：指定要在其中创建表的数据库名称，不指定数据库名称，则默认使用当前数据库。
• schema_name：指定新表所属架构的名称，若此项为空，则默认为新表的创建者所在的当前架构。
• table_name：指定创建的数据表的名称。
• column_name：指定数据表中的各个列的名称，列名称必须唯一。
• data_type：指定字段列的数据类型，可以是系统数据类型，也可以是用户定义数据类型。
• NULL | NOT NULL：表示确定列中是否允许使用空值。
• DEFAULT：用于指定列的默认值。
• ROWGUIDCOL：指示新列是行GUID列。对于每个表，只能将其中的一个uniqueidentifier列指定为ROWGUIDCOL列。
• PRIMARY KEY：主键约束，通过唯一索引对给定的一列或多列强制实体完整性的约束。每个表只能创建一个PRIMARY KEY约束。PRIMARY KEY约束中的所有列都必须定义为NOT NULL。
• UNIQUE：唯一性约束，该约束通过唯一索引为一个或多个指定列提供实体完整性。一个表可以有多个UNIQUE约束。
• CLUSTERED | NONCLUSTERED：表示为PRIMARY KEY或UNIQUE约束创建聚集索引还是非聚集索引。PRIMARY KEY约束默认为CLUSTERED，UNIQUE约束默认为NONCLUSTERED。在CREATE TABLE语句中，可只为一个约束指定CLUSTERED。如果在为UNIQUE约束指定CLUSTERED的同时又指定了RIMARY KEY约束，则PRIMARY KEY将默认为NONCLUSTERED。
• [ ASC | DESC ]：指定加入到表约束中的一列或多列的排序顺序，ASC为升序排列，DESC为降序排列，默认值为ASC。

介绍完T-SQL中创建数据表的语句，下面举例说明。

【例3.2】使用T-SQL语句创建数据表authors，输入语句如下。

新建立一个当前连接查询，在查询编辑器中输入上面的代码，如图3-12所示。

执行成功之后，刷新数据库列表可以看到新建名称为authors的数据表，如图3-13所示。