SQLServer创建一个表

要完成本章各课的学习，必须完成以下准备工作。

已经安装SQL Server 2005。

在SQL Server 2005实例上已经安装了AdventureWorks示例数据库的一个副本，或者创建了一个空的数据库。

第1课创建表
理解数据类型
为一个列选择数据类型时，应选择允许你期望存储的所有数据值的数据类型，同时使所需的空间量最小。

SQL Server数据类型有7类，如表3.1所示。

表3.1 SQL Server的7类数据类型
数据类型分类基本目的
(1)精确数字存储带小数或不带小数的精确数字
(3)货币存储带小数位的数值；专门用于货币值，最多可以有
4个小数位
(4)日期和时间存储日期和时间信息，并强制实施特殊的年代规则，
如拒绝2月30日这个值
(5)字符存储基于字符的可变长度的值
(6)二进制存储以严格的二进制(0和1)表示的数据
(7)专用数据类型要求专门处理的复杂数据类型，诸如XML文档或者全
bigint 8字节-2E63～2E63-1 存储非常大的正负整数int 4字节-2E31～2E31-1 存储正负整数
smallint
tinyint 1字节0～255 存储小范围的正整数
decimal(p,s) 依据不同的精
度，需要5～17
字节
-10E38+1～
10E38-1
最大可以存储38位十
进制数
numeric(p,s) 依据不同的精
度，需要5～17
字节
-10E38+1～
10E38-1
功能上等价于decimal，
并可以与decimal交换
使用
decimal和numeric数据类型接受参数来完成数据类型定义。

这些参数定义数据类型的精度和小数位数。

例如，decimal(12,4)定义了一个总共有12位数字的十进制值，其中小数点后面有4位数字。

在这组数据类型中，int和dedcimal是最常用的数据类型。

使用decimal
数据类型可以存储整型值，但这么做每行需要额外的存储字节，因此不要这么使用decimal数据类型。

如果在一个列中打算存储的值的范围不超过32 767，则通过使用smallint代替int，每行可以节省2个字节。

如果取值范围只是在0和255之间，则通过使用tinyint数据类型，每行可以节省3个字节。

近似数字数据类型
近似数字数据类型可以存储十进制值。

然而，float或real数据类型中存储的数据，只能精确到数据类型定义中指定的精度。

不能保证小数点右边的所有数字都被正确存储。

例如，如果把1.00015454存储在一个定义为float(8)的数据类型中，则该列只能保证精确地返回1.000154。

SQL Server存储数据时对小数点右边的数进行四舍五入。

因此，涉及这些数据类型的计算，会出现舍入误差。

在Intel处理器和AMD处理器之间传输包含涉及这些数据类型的表的数据库时，也会引入误差。

表3.3列出了SQL Server支持的近似数字数据类型。

表3.3 近似数字数据类型
数据类
型
存储取值范围作用
float(p) 4或8个字
节
-2.23E308～
2.23E308
存储大型浮点数，超过十进制数
据类型的容量
real 4个字节-3.4E38～3.4E38 仍然有效，但为了满足SQL-92
标准，已经被float替换了float数据类型在定义时接受一个参数，该参数决定了精确存储的位数。

例如，一个float(8)列精确存储7位数字，任何超过该数的位数都会遭遇舍入误差。

由于这些数据类型是不精确的，所以几乎不使用它们。

只有在精确数据类型不够大，不能存储数值时，才可以考虑使用float 。

货币数据类型
货币数据类型旨在存储精确到4个小数位的货币值。

表3.4列出了SQL Ser ver 支持的货币数据类型。

表3.4 货币数据类型
数据类型
存储空间 取值范围 作用
money 8字节 -922 337 203 685 477.5808～ 922 337 203 685 477.5807 存储大型货币值 smallmoney 4字节 -214 748.3648～
存储小型货币值
datetime 8字节 31,9999，精度为3.33毫秒 和时间值 smalldatetime 4字节 从January 1, 1900到June 6, 2079，精度为1分钟 存储较小范围的日期和时间
etime数据类型存储为一对2字节整数，它们一起表示自1900年1月1日午夜1 2点钟经过的分钟数。

前两个字节存储日期，后两个日期存储时间。

字符数据类型
存储字符数据时，选择一种为此目的而设计的数据类型。

每种字符数据类型使用1个或2个字节存储每个字符，具体取决于该数据类型使用ANSI(American National Standards Institute)编码还是Unicode编码。

Unicode数据类型前有一个n。

例如，nchar是Unicode数据类型，对应于使用ANSI编码的char数据类型。

定义一个字符数据类型时，指定该列允许存储的最大字节数。

例如，char(10)最多可以存储10个字符，因为每个字符要求1个字节的存储空间，而nchar(10)最多可以存储5个字符，因为每个Unicode字符要求使用两个字节的存储空间。

表3.6列出了SQL Server支持的字符数据类型。

表3.6 字符数据类型
数据类型存储空间字符数作用
char(n) 1～8 000
字节
最多8 000个字符
固定宽度的ANSI数据
类型
nchar(n)
字节最多4 000个字符
固定宽度的Unicode数
据类型
varchar(n) 1～8 000
字节
最多8 000个字符
固定宽度的ANSI数据
类型
varchar(max) 最大2 GB 最多1 073 741 824
个字符
可变宽度的ANSI数据
类型
nvarchar(n) 2～8 000
字节
最多4 000个字符
可变宽度的Unicode数
据类型
nvarchar(max) 最大2 GB 最多536 870 912个
字符
可变宽度的Unicode数
据类型
text 最大2 GB 最多1 073 741 824
个字符可变宽度的ANSI数据类型
ntext 最大2 GB 最多536 870 912个可变宽度的Unicode数
直到填满为该列指定的存储空间。

然而，一个varchar(30)列对该列中存储的每个字符只用1个字节。

text和ntext数据类型旨在存储大量基于字符的数据。

然而，text和ntex t列容许的操作不是很多。

例如，不能使用等于运算符比较它们，也不能连接它们。

很多系统函数也不能使用text和ntext数据类型。

由于这些限制，SQL Server 2005引入了varchar(max)和nvarchar(max)数据类型。

这些数据类型同时结合了text/ntext数据类型和varchar/nvarch数据类型的功能。

它们最多可以存储2 GB数据，并对执行它们的操作或者使用它们的函数没有任何限制。

二进制数据类型
有很多时候需要存储二进制数据。

因此，SQL Server提供了三种二进制数据类型，允许在一个表中存储各种数量的二进制数据。

表3.7列出了SQL Server 支持二进制数据类型。

表3.7 二进制数据类型
数据类型存储空间作用
binary(n)
节
存储固定大小的二进制数据
varbinary(n) 1～8 000字
节
存储可变大小的二进制数据
varbinary(max)
varbinary(max)数据类型是SQL Server 2005新增的一种数据类型。

它可以存储与image 数据类型相同大小的数据，并且可以使用它执行所有可以用binar y/varbinary 数据类型执行的操作和函数。

特殊数据类型
除了上述标准数据类型外，SQL Server 还提供了另外7种特殊数据类型。

表
3.8描述了这些特殊数据类型。

表3.8 特殊数据类型
数据类型 作用
bit 存储0、1或null 。

用于基本“标记”值。

TRUE 被转换为1，而
FALSE 被转换为0
timestamp 一个自动生成的值。

每个数据库都包含一个内部计数器，指
定一个不与实际时钟关联的相对时间计数器。

一个表只能有一个timestamp 列，并在插入或修改行时被设置到数据库
时间戳 uniqueidentifier 一个16位GUID ，用来全局标识数据库、实例和服务器中的一行
sql_variant 可以根据其中存储的数据改变数据类型。

最多存储8 000字cursor
供声明游标的应用程序使用。

它包含一个可用于操作的游标的引用。

该数据类型不能在表中使用 table
用来存储随后进行的处理的结果集。

该数据类型不能用于列。

该数据类型的唯一使用时机是在触发器、存储过程和函数中声明表变量时 Xml 存储一个XML 文档，最大大小为2GB 。

你可以指定选项，强
制只能存储格式良好的文档
警告 sql_variant
sql_variant 数据类型是SQL Server 2005新增的数据类型，是一种危险的数据类型，在我看来决不应把它加入SQL Server 。

该数据类型允许我们在声明一个列或变量时，不必决定用它存储哪种类型的数据。

然后，sql_variant 数据类型自动地将自己“转换为”写到它里面的数据的类型。

数据库是有用的，因为所有的数据都是显式声明的，并且是显式输入的。

允许一
种没有已定义的类型的数据类型，可能会引起各种各样的数据不匹配问题。

我们强烈建议大家不要使用sql_variant。

更多信息sql_variant
有关sql_variant数据类型的更多信息，请参见SQL Server 2005联机丛书文章“s ql_variant (Transact-SQL)”。

为空性(nullability)
列定义的第二个特征是是否要求它存储一个值。

数据库有一个称为null的特殊构造，你可以用它来表示某个值不存在——有点类似于“未知的”或“不可应用的”。

null不是一个值，也不占用任何存储空间。

在定义列时，可以指定是否允许null。

如果不允许null，则要求用户为该列指定一个值。

注意，因为不存在某个东西不可能等于不存在另一个东西——换句话说，一个null不能等于另一个null，所以不能对null进行比较。

标识
定义列时，还可以为一个表中的一个单独的列指定一个特殊的标识属性。

定义一个带有标识属性的列使SQL Server生成一个自动增长的数。

标识属性有两个参数：标识种子和标识增量。

种子值指定SQL Server使用的起始值；增量值指定SQL Server在生成每个后续值时把哪个数添加到该起始值。

可以对精确数字数据类型使用标识属性，诸如bigint，int，smallint，ti nyint，decimal和numeric。

如果对decimal或numeric数据类型设置标识属性，必须把它们定义为有0个小数位。

计算所得的列
我们还可以创建一种称为计算所得的列的特殊列，它包含一个涉及表中一个或多个其他列的计算公式。

在默认情况下，计算所得的列包含计算公式的定义，但在物理上不存储数据。

返回数据时，应用该计算公式以返回一个结果值。

然而，通过使用PERSISTED关键字，可以强制一个计算所得的列在物理上存储数据。

该关键字使公式计算在插入或修改行时发生，然后将计算结果存储在表中。

创建一个表
在SQL Server中可以创建三种不同类型的表：永久表、临时表和表变量。

更多信息规范化、命名约定和表设计
永久表
要创建一个表，应使用CREATE TABLE T-SQL命令。

该命令的基本语法如下：CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name ( { <column_definition> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]
要执行该命令，你必须是sysadmin固定服务器角色的成员、数据库所有者固定数据库角色的成员或者已经被授予CREATE TABLE权限。

使用该命令时，在数据库中创建一个可以被任何有合适权限的用户访问的表。

ON子句指定该表存放在物理存储器上的位置。

如果没有指定文件组，SQL S erver在默认的文件组中创建该表。

对前面的实例，可以如下使用CREATE TABLE命令创建CustomerAddress表：CREATE TABLE dbo.CustomerAddress
(AddressLine1 varchar(30)NOT NULL,
AddressLine2 varchar(30)NULL,
AddressLine3 varchar(30)NULL,
City varchar(50)NOT NULL,
StateProvinceIDint NULL,
PostalCodechar(10)NULL,
CountryID int NULL)
该表定义指定了如下内容：
●将以dbo架构创建该表；
●至少必须为每个顾客指定一个地址行，该列最多存储30个字符。

该列使用
的存储空间将等于该列中的字符数；
●可以指定1～2个可选的地址行，每个地址行最多存储30个字符，其所使用
的存储空间等于该列中的字符数；
●必须指定一个顾客所在的城市；City列最多可以存储50个字符，其所使用
的存储空间等于该列中的字符数；
●可以选择性地指定一个顾客所在的州/省。

该列使用4个字节，包含一个整
型值；
●可以选择性地为顾客指定一个邮政编码，该列使用10个字节；
●可以选择性地指定一个顾客所在的国家，该列使用4个字节，包含一个整型
值。

虽然前面的表定义准确地获取了所需的数据，但你也许已经注意到了几个问题。

一位顾客可能有一个或多个家庭住址、一个或多个商业地址以及一个或多个送货地址。

顾客还有可能会指定一个具体的地址作为主要地址。

因而，你可能总想着添加许多额外的列来处理这些情况。

但是，这种想法适合于电子表格，却不适合于数据库。

相反，只要向该表添加两个列，一列指定地址的类型，另一列指定主要地址，如下面的实例所示：
CREATE TABLE dbo.CustomerAddress
(AddressType char(4)NOT NULL,
PrimaryAddressFlag bit NOT NULL,
AddressLine1 varchar(30) NOT NULL,
AddressLine2 varchar(30)NULL,
AddressLine3 varchar(30)NULL,
City varchar(50)NOT NULL,
StateProvinceID int NULL,
PostalCode char(10) NULL,
CountryID int NULL)
我们暂时忽略有关StateProvinceID和CountryID列的问题，因为我们将在下一课关于约束的内容中介绍它们。

但是，该表定义还存在另一个问题。

我们可以获取地址，但是我们无法知道哪个地址对应哪个顾客。

为了完善该表的结构，使一个地址关联一个顾客，还需要向该表添加一个列：CustomerAddressID int列，并对它定义标识属性。

完善后的表定义如下所示：
CREATE TABLE dbo.CustomerAddress
(CustomerAddressID int IDENTITY(1,1),
AddressType char(4) NOT NULL,
PrimaryAddressFlag bit NOT NULL,
AddressLine1 varchar(30) NOT NULL,
AddressLine2 varchar(30)NULL,
AddressLine3 varchar(30)NULL,
City varchar(50)NOT NULL,
StateProvinceIDintNULL,
PostalCodechar(10) NULL,
CountryID intNULL)
提示删除表
DELETE命令用来从一个表中删除行。

而要删除整个表，应使用DROP TABLE 命令。

要执行该命令，你必须是sysadmin固定服务器角色的成员、数据库所有者固定数据库角色的成员或者是该表的所有者。

临时表
顾名思义，临时表是临时使用的表结构。

临时表既可以是全局的，也可以是局部的，并且可以由任何用户创建。

所有的临时表都是在tempdb数据库中创建的。

局部临时表只有创建该表的用户在用来创建该表的连接中可见。

局部临时表关联的连接被关闭时，局部临时表自动地被删除。

通过使用CREATE TABLE命令并在表名前添加一个字符(#)，可以创建局部临时表。

如下实例演示了作为局部临时表创建前面的CustomerAddress表的命令：CREATE TABLE #CustomerAddress
(CustomerAddressID int IDENTITY(1,1),
AddressType char(4) NOT NULL,
PrimaryAddressFlag bit NOT NULL,
AddressLine1 varchar(30) NOT NULL,
AddressLine2 varchar(30) NULL,
AddressLine3 varchar(30) NULL,
City varchar(50) NOT NULL,
StateProvinceID int NULL,
PostalCode char(10)NULL,
CountryID int NULL)
相反，全局临时表对SQL Server实例中的任何用户都是可见的。

全局临时表在访问该表的最后一个连接关闭时被删除。

通过使用CREATE TABLE命令并在表名前添加两个字符(##)，可以创建一个全局临时表，如下所示：
CREATE TABLE ##CustomerAddress
(CustomerAddressID int IDENTITY(1,1),
AddressType char(4) NOT NULL,
PrimaryAddressFlag bit NOT NULL,
AddressLine1 varchar(30) NOT NULL,
AddressLine2 varchar(30) NULL,
AddressLine3 varchar(30) NULL,
City varchar(50) NOT NULL,
StateProvinceID int NULL,
PostalCode char(10)NULL,
CountryID int NULL)
表变量
表变量提供了临时表的一种替代方案，并可以在函数、触发器和存储过程中使用。

表变量不是将表及其所有数据存储在磁盘上的tempdb数据库中的一个表中，而是将它和所有关联的数据存储在内存中。

然而，如果放入表变量中的数据量使它所得存储空间大于可用的内存时，则溢出部分将被缓存到磁盘上的tempd b中。

表变量是创建它们的函数、触发器或存储过程的局部变量，并在该对象退出时自动释放。

要以表变量的形式创建前面的顾客地址表，可以把该表声明为变量，即在表名前放置@字符，如下所示：
DECLARE @CustomerAddress TABLE
(CustomerAddressID int IDENTITY(1,1),
AddressType char(4) NOT NULL,
PrimaryAddressFlag bit NOT NULL,
AddressLine1 varchar(30) NOT NULL,
AddressLine2 varchar(30) NULL,
AddressLine3 varchar(30) NULL,
City varchar(50) NOT NULL,
StateProvinceID int NULL,
PostalCode char(10)NULL,
CountryID int NULL)
分配权限
创建表以后，还要为用户提供访问它的权限。

正如我们在第2章“配置SQL Server 2005”中所学的，SQL Server中的所有对象都受到保护。

此外，SQL Se rver不提供任何访问权限，除非显式地授予权限。

s ysadmin固定服务器角色的成员，已经被授予对SQL Server实例内的所有对象有无限制的权限，因此该角色的成员可以对表执行任何操作。

数据库所有者
固定数据库角色的成员，已经被授予对它所拥有的数据库内的任何对象执行任何操作的权限，因此该角色的成员可以对表执行任何操作。

此外，表的所有者已经被明确授予对所拥有的表执行任何操作的权限。

所有其他用户必须被分配使用一个表的权限。

最佳实践安全性分配
安全性最佳实践指示，绝不要直接把权限授予用户。

因此，要添加一个Microso ft Windows登录名到一个Windows组，并把该Windows组作为SQL Server的登录名。

然后，作为数据库中的用户添加该组。

接着，在一个与各作业函数对应的数据库中创建角色，并把数据库用户分配给合适的角色。

最后，将数据库中的对象上的安全性权限分配给数据库角色。

本书中所有有关安全性的实例都假设你实施了安全性最佳实践。

如表3.9所示，我们可以给表分配7种权限。

表3.9 表的权限
权限作用
CREATE TABLE 授予在数据库中创建任何表的权限
ALTER TABLE 授予更改数据库中的任何表的结构的权限SELECT 允许从指定的表中检索行
INSERT 允许把行插入指定的表中，同时也要求被授予SELECT权限
UPDATE 允许修改指定表中的行，同时也要求被授予SELECT 权限
DELETE 允许从指定表中删除行，同时也要求被授予SELECT
权限
REFERENCES 与外键约束一起使用；有关讨论参见下一课使用特殊的关键字ALL可以将表中所示的所有权限授予一个指定的角色。

然而，始终应当显式地列出每一个准许的权限。

分配权限的基本语法如下：GRANT { ALL [ PRIVILEGES ] }
| permission [ ( column [ ,...n ] ) ] [ ,...n ]
[ ON [ class :: ] securable ] TO principal [ ,...n ]
[ WITH GRANT OPTION ] [ AS principal ]
ON子句指定被授予权限的对象，而TO子句指定被授予权限的数据库角色。

对于表，可以将权限授予该表中的列的子集，但没有把权限授予一个表中的行的子集的工具。

WITH GRANT选项使我们能够把权限授予一个角色，而该角色的成员又能把这些权限授予其他用户或角色。

绝对不要使用该选项，因为它会使表的所有者无法控制安全性。

对于CustomerAddress表，将SELECT、INSERT、UPDATE和DELETE权限授予一个角色的命令如下：
GRANT SELECT, INSERT, UPDATE, DELETE ON CustomerAddress TO <dat abase role>
练习：创建一个表
本练习将创建另外3个表：Customer、StateProvince和Country，供我们在本课中创建的CustomerAddress表。

提示如果没有创建CustomerAddress表
创建CustomerAddress表的指令，参见本课前面的“永久表”。

Customer表将包含客户名、一个表示客户信用额度的值、一个表示客户的未付差额的值、一个表示可用信用的计算值以及该客户记录的创建日期。

StatePr ovince表将包含一个基于文本的列，用来存储该公司公认的一系列有效的州或省。

Country表将包含一个基于文本的列，它将存储一系列有效的国家。

记得创建一个引用列，与我们在CustomerAddress表中采用的相同方法引用每一行。

提示数据库上下文
本练习既可以在AdventureWorks数据库中完成，也可以在自己所选的另一个数据库中完成。

1. 打开SQL Server Management Studio(SSMS)，连接到SQL Server实例，然后打开一个“新建查询”窗口。

2. 构造一个创建Customer表的CREATE TABEL语句：
CREATE TABLE dbo.Customer
(CustomerID int IDENTITY(1,1),
CustomerName varchar(50)NOT NULL,
CreditLine smallmoneyNULL,
OutstandingBalance smallmoneyNULL,
AvailableCredit AS (CreditLine - OutstandingBalance),
CreationDate datetime NOT NULL)
3. 构造一个创建StateProvince表的CREATE TABLE语句：
CREATE TABLE dbo.StateProvince
(StateProvinceID int IDENTITY(1,1),
StateProvince varchar(50) NOT NULL)
4. 构造一个创建Country表的CREATE TABLE语句：
CREATE TABLE dbo.Country
(CountryID intIDENTITY(1,1),
Country varchar(50)NOT NULL)
本课总结
n 表是每个数据库的构造块，在SQL Server中用来存储所有的数据。

n 为了提供一个表所需的结构，必须在数字型、文本型、日期时间型和二进制型数据类型中选择一种合适的数据类型，以正确地存储数据。

n 也可以为列定义允许null值的特殊属性，把列定义为一个唯一的标识符列，以及允许一个列存储计算值。

n 定义一个表以后，必须授予该表上的权限，以允许用户检索和操纵数据。

课后测试
下面的问题旨在巩固本课介绍的关键内容。

如果喜欢用电子版的，可以参见配套CD。

提示参考答案
这些题目的参考答案以及对每个选项的具体解释，参见本书后面的“参考答案”部分。

1. 下列哪种数据类型可用来存储高达2 GB的文本数据，并且仍然能够使用标准的函数和运算符查询和操纵它？
A. text
B. varbinary
C. varchar(max)
D. varchar。