SQLServer创建一个表.docx
《SQLServer创建一个表.docx》由会员分享，可在线阅读，更多相关《SQLServer创建一个表.docx（18页珍藏版）》请在冰点文库上搜索。
SQLServer创建一个表
要完成本章各课的学习，必须完成以下准备工作。
已经安装SQLServer2005。
在SQLServer2005实例上已经安装了AdventureWorks示例数据库的一个副本，或者创建了一个空的数据库。
第1课 创建表
理解数据类型
为一个列选择数据类型时，应选择允许你期望存储的所有数据值的数据类型，同时使所需的空间量最小。
SQLServer数据类型有7类，如表3.1所示。
表3.1 SQLServer的7类数据类型
数据类型分类
基本目的
（1）精确数字
存储带小数或不带小数的精确数字
（2）近似数字
存储带小数或不带小数的数值
（3）货币
存储带小数位的数值；专门用于货币值，最多可以有4个小数位
（4）日期和时间
存储日期和时间信息，并强制实施特殊的年代规则，如拒绝2月30日这个值
（5）字符
存储基于字符的可变长度的值
（6）二进制
存储以严格的二进制（0和1）表示的数据
（7）专用数据类型
要求专门处理的复杂数据类型，诸如XML文档或者全局唯一的标识符（GUID）
精确数字数据类型
精确数字数据类型用来存储没有小数位或有多个小数位的数值。
使用任何算术运算符都可以操纵这些数据类型中存储的数值，而不需要任何特殊处理。
精确数字数据类型的存储也是精确定义的，因此，无论是Intel处理器架构还是AMD处理器架构，这些数据类型中存储的任何数据都返回和计算得到相同的值。
表3.2列出了SQLServer支持的精确数字数据类型。
表3.2 精确数字数据类型
数据类型
存储
值域
作用
bigint
8字节
-2E63～2E63-1
存储非常大的正负整数
int
4字节
-2E31～2E31-1
存储正负整数
smallint
2字节
-32768～32767
存储正负整数
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数据类型。
如果在一个列中打算存储的值的范围不超过32767，则通过使用smallint代替int，每行可以节省2个字节。
如果取值范围只是在0和255之间，则通过使用tinyint数据类型，每行可以节省3个字节。
近似数字数据类型
近似数字数据类型可以存储十进制值。
然而，float或real数据类型中存储的数据，只能精确到数据类型定义中指定的精度。
不能保证小数点右边的所有数字都被正确存储。
例如，如果把1.00015454存储在一个定义为float（8）的数据类型中，则该列只能保证精确地返回1.000154。
SQLServer存储数据时对小数点右边的数进行四舍五入。
因此，涉及这些数据类型的计算，会出现舍入误差。
在Intel处理器和AMD处理器之间传输包含涉及这些数据类型的表的数据库时，也会引入误差。
表3.3列出了SQLServer支持的近似数字数据类型。
表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列出了SQLServer支持的货币数据类型。
表3.4 货币数据类型
数据类型
存储空间
取值范围
作用
money
8字节
-922 337 203 685 477.5808～
922 337 203 685 477.5807
存储大型货币值
smallmoney
4字节
-214 748.3648～214 748.3647
存储小型货币值
在数据库中几乎不定义smallmoney数据类型，尽管对很多处理产品和订单的应用程序而言，这种数据类型是最精确的选择。
由于不正确地使用了money数据类型，使每行数据浪费了4个字节的存储空间，这种情况是比较普遍的。
虽然money和smallmoney数据类型旨在存储货币值，但在金融应用程序中几乎不使用它们。
相反，这些应用程序使用decimal数据类型，因为它们需要执行精确到6个、8个甚至12个小数位的计算。
日期和时间数据类型
表3.5列出了SQLServer支持的日期和时间数据类型。
表3.5 日期和时间数据类型
日期类型
存储空间
取值范围
作用
datetime
8字节
从January1,1753到December31,9999，精度为3.33毫秒
存储大型日期和时间值
smalldatetime
4字节
从January1,1900到June6,2079，精度为1分钟
存储较小范围的日期和时间值
datetime和smalldatetime数据类型在计算机内部是作为整数存储的。
datetime数据类型存储为一对4字节整数，它们一起表示自1753年1月1日午夜12点钟经过的毫秒数。
前4个字节存储日期，而后4个字节存储时间。
smalldatetime数据类型存储为一对2字节整数，它们一起表示自1900年1月1日午夜12点钟经过的分钟数。
前两个字节存储日期，后两个日期存储时间。
字符数据类型
存储字符数据时，选择一种为此目的而设计的数据类型。
每种字符数据类型使用1个或2个字节存储每个字符，具体取决于该数据类型使用ANSI（AmericanNationalStandardsInstitute）编码还是Unicode编码。
Unicode数据类型前有一个n。
例如，nchar是Unicode数据类型，对应于使用ANSI编码的char数据类型。
定义一个字符数据类型时，指定该列允许存储的最大字节数。
例如，char（10）最多可以存储10个字符，因为每个字符要求1个字节的存储空间，而nchar（10）最多可以存储5个字符，因为每个Unicode字符要求使用两个字节的存储空间。
表3.6列出了SQLServer支持的字符数据类型。
表3.6 字符数据类型
数据类型
存储空间
字符数
作用
char（n）
1～8000字节
最多8000个字符
固定宽度的ANSI数据类型
nchar（n）
2～8000字节
最多4000个字符
固定宽度的Unicode数据类型
varchar（n）
1～8000字节
最多8000个字符
固定宽度的ANSI数据类型
varchar（max）
最大2GB
最多1073741824个字符
可变宽度的ANSI数据类型
nvarchar（n）
2～8000字节
最多4000个字符
可变宽度的Unicode数据类型
nvarchar（max）
最大2GB
最多536870912个字符
可变宽度的Unicode数据类型
text
最大2GB
最多1073741824个字符
可变宽度的ANSI数据类型
ntext
最大2GB
最多536870912个字符
可变宽度的Unicode数据类型
为什么有那么多看起来好像相互等价的字符数据类型呢？
数据类型的区别可能不怎么明显，但是它们是重要的。
一个char数据类型，无论是ANSI标准还是Unicode标准，都是固定宽度的数据类型。
因此，不管列中存储多少个字符，它们总是消耗相同的存储空间。
例如，一个char（30）列使用30字节存储空间，而不管在该列存储1个字符还是30个字符。
任何未被使用的空间都用空格填补，直到填满为该列指定的存储空间。
然而，一个varchar（30）列对该列中存储的每个字符只用1个字节。
text和ntext数据类型旨在存储大量基于字符的数据。
然而，text和ntext列容许的操作不是很多。
例如，不能使用等于运算符比较它们，也不能连接它们。
很多系统函数也不能使用text和ntext数据类型。
由于这些限制，SQLServer2005引入了varchar（max）和nvarchar（max）数据类型。
这些数据类型同时结合了text/ntext数据类型和varchar/nvarch数据类型的功能。
它们最多可以存储2GB数据，并对执行它们的操作或者使用它们的函数没有任何限制。
二进制数据类型
有很多时候需要存储二进制数据。
因此，SQLServer提供了三种二进制数据类型，允许在一个表中存储各种数量的二进制数据。
表3.7列出了SQLServer支持二进制数据 类型。
表3.7 二进制数据类型
数据类型
存储空间
作用
binary（n）
1～8000字节
存储固定大小的二进制数据
varbinary（n）
1～8000字节
存储可变大小的二进制数据
varbinary（max）
最多2GB
存储可变大小的二进制数据
image
最多2GB
存储可变大小的二进制数据
二进制数据类型基本上用来存储SQLServer中的文件。
binary/varbinary数据类型用来存储小文件，诸如一组4KB或6KB文件，其中包含各种以本机格式表示的数据的文件。
image数据类型是这组数据类型中最流行的数据类型。
虽然可以用image数据类型存储图片，但也可以使用这种数据类型存储Word、Excel、PDF和Visio文档。
使用image数据类型可以存储任何一个小于或等于2GB的文件。
这种数据类型的最著名的实现之一是TerraServer项目，这是一个高达几TB的陆地图形数据库，大家可以在上访问它。
varbinary（max）数据类型是SQLServer2005新增的一种数据类型。
它可以存储与image数据类型相同大小的数据，并且可以使用它执行所有可以用binary/varbinary数据类型执行的操作和函数。
特殊数据类型
除了上述标准数据类型外，SQLServer还提供了另外7种特殊数据类型。
表3.8描述了这些特殊数据类型。
表3.8 特殊数据类型
数据类型
作用
bit
存储0、1或null。
用于基本“标记”值。
TRUE被转换为1，而FALSE被转换为0
timestamp
一个自动生成的值。
每个数据库都包含一个内部计数器，指定一个不与实际时钟关联的相对时间计数器。
一个表只能有一个timestamp列，并在插入或修改行时被设置到数据库时间戳
uniqueidentifier
一个16位GUID，用来全局标识数据库、实例和服务器中的一行
sql_variant
可以根据其中存储的数据改变数据类型。
最多存储8000字节
续表
数据类型
作用
cursor
供声明游标的应用程序使用。
它包含一个可用于操作的游标的引用。
该数据类型不能在表中使用
table
用来存储随后进行的处理的结果集。
该数据类型不能用于列。
该数据类型的唯一使用时机是在触发器、存储过程和函数中声明表变量时
Xml
存储一个XML文档，最大大小为2GB。
你可以指定选项，强制只能存储格式良好的文档
警告 sql_variant
sql_variant数据类型是SQLServer2005新增的数据类型，是一种危险的数据类型，在我看来决不应把它加入SQLServer。
该数据类型允许我们在声明一个列或变量时，不必决定用它存储哪种类型的数据。
然后，sql_variant数据类型自动地将自己“转换为”写到它里面的数据的类型。
数据库是有用的，因为所有的数据都是显式声明的，并且是显式输入的。
允许一种没有已定义的类型的数据类型，可能会引起各种各样的数据不匹配问题。
我们强烈建议大家不要使用sql_variant。
更多信息 sql_variant
有关sql_variant数据类型的更多信息，请参见SQLServer2005联机丛书文章“sql_variant（Transact-SQL）”。
为空性（nullability）
列定义的第二个特征是是否要求它存储一个值。
数据库有一个称为null的特殊构造，你可以用它来表示某个值不存在——有点类似于“未知的”或“不可应用的”。
null不是一个值，也不占用任何存储空间。
在定义列时，可以指定是否允许null。
如果不允许null，则要求用户为该列指定一个值。
注意，因为不存在某个东西不可能等于不存在另一个东西——换句话说，一个null不能等于另一个null，所以不能对null进行比较。
标识
定义列时，还可以为一个表中的一个单独的列指定一个特殊的标识属性。
定义一个带有标识属性的列使SQLServer生成一个自动增长的数。
标识属性有两个参数：
标识种子和标识增量。
种子值指定SQLServer使用的起始值；增量值指定SQLServer在生成每个后续值时把哪个数添加到该起始值。
可以对精确数字数据类型使用标识属性，诸如bigint，int，smallint，tinyint，decimal和numeric。
如果对decimal或numeric数据类型设置标识属性，必须把它们定义为有0个小数位。
计算所得的列
我们还可以创建一种称为计算所得的列的特殊列，它包含一个涉及表中一个或多个其他列的计算公式。
在默认情况下，计算所得的列包含计算公式的定义，但在物理上不存储数据。
返回数据时，应用该计算公式以返回一个结果值。
然而，通过使用PERSISTED关键字，可以强制一个计算所得的列在物理上存储数据。
该关键字使公式计算在插入或修改行时发生，然后将计算结果存储在表中。
创建一个表
在SQLServer中可以创建三种不同类型的表：
永久表、临时表和表变量。
更多信息 规范化、命名约定和表设计
永久表
要创建一个表，应使用CREATETABLET-SQL命令。
该命令的基本语法如下：
CREATETABLE
[database_name.[schema_name].|schema_name.]table_name
（{|}
[][,...n]）
[ON{partition_scheme_name（partition_column_name）|filegroup
|"default"}]
[{TEXTIMAGE_ON{filegroup|"default"}]
[;]
要执行该命令，你必须是sysadmin固定服务器角色的成员、数据库所有者固定数据库角色的成员或者已经被授予CREATETABLE权限。
使用该命令时，在数据库中创建一个可以被任何有合适权限的用户访问的表。
ON子句指定该表存放在物理存储器上的位置。
如果没有指定文件组，SQLServer在默认的文件组中创建该表。
对前面的实例，可以如下使用CREATETABLE命令创建CustomerAddress表：
CREATETABLEdbo.CustomerAddress
（AddressLine1varchar（30）NOTNULL,
AddressLine2 varchar（30）NULL,
AddressLine3 varchar（30）NULL,
City varchar（50）NOTNULL,
StateProvinceIDintNULL,
PostalCodechar（10）NULL,
CountryID intNULL）
该表定义指定了如下内容：
●将以dbo架构创建该表；
●至少必须为每个顾客指定一个地址行，该列最多存储30个字符。
该列使用的存储空间将等于该列中的字符数；
●可以指定1～2个可选的地址行，每个地址行最多存储30个字符，其所使用的存储空间等于该列中的字符数；
●必须指定一个顾客所在的城市；City列最多可以存储50个字符，其所使用的存储空间等于该列中的字符数；
●可以选择性地指定一个顾客所在的州/省。
该列使用4个字节，包含一个整型值；
●可以选择性地为顾客指定一个邮政编码，该列使用10个字节；
●可以选择性地指定一个顾客所在的国家，该列使用4个字节，包含一个整型值。
虽然前面的表定义准确地获取了所需的数据，但你也许已经注意到了几个问题。
一位顾客可能有一个或多个家庭住址、一个或多个商业地址以及一个或多个送货地址。
顾客还有可能会指定一个具体的地址作为主要地址。
因而，你可能总想着添加许多额外的列来处理这些情况。
但是，这种想法适合于电子表格，却不适合于数据库。
相反，只要向该表添加两个列，一列指定地址的类型，另一列指定主要地址，如下面的实例所示：
CREATETABLEdbo.CustomerAddress
（AddressTypechar（4）NOTNULL,
PrimaryAddressFlag bitNOTNULL,
AddressLine1varchar（30）NOTNULL,
AddressLine2 varchar（30）NULL,
AddressLine3 varchar（30）NULL,
City varchar（50）NOTNULL,
StateProvinceIDintNULL,
PostalCodechar（10） NULL,
CountryID intNULL）
我们暂时忽略有关StateProvinceID和CountryID列的问题，因为我们将在下一课关于约束的内容中介绍它们。
但是，该表定义还存在另一个问题。
我们可以获取地址，但是我们无法知道哪个地址对应哪个顾客。
为了完善该表的结构，使一个地址关联一个顾客，还需要向该表添加一个列：
CustomerAddressIDint列，并对它定义标识属性。
完善后的表定义如下所示：
CREATETABLEdbo.CustomerAddress
（CustomerAddressID intIDENTITY（1,1）,
AddressTypechar（4）NOTNULL,
PrimaryAddressFlag bitNOTNULL,
AddressLine1varchar（30）NOTNULL,
AddressLine2 varchar（30）NULL,
AddressLine3 varchar（30）NULL,
City varchar（50）NOTNULL,
StateProvinceIDintNULL,
PostalCodechar（10） NULL,
CountryID intNULL）
提示 删除表
DELETE命令用来从一个表中删除行。
而要删除整个表，应使用DROPTABLE命令。
要执行该命令，你必须是sysadmin固定服务器角色的成员、数据库所有者固定数据库角色的成员或者是该表的所有者。
临时表
顾名思义，临时表是临时使用的表结构。
临时表既可以是全局的，也可以是局部的，并且可以由任何用户创建。
所有的临时表都是在tempdb数据库中创建的。
局部临时表只有创建该表的用户在用来创建该表的连接中可见。
局部临时表关联的连接被关闭时，局部临时表自动地被删除。
通过使用CREATETABLE命令并在表名前添加一个字符（#），可以创建局部临时表。
如下实例演示了作为局部临时表创建前面的CustomerAddress表的命令：
CREATETABLE#CustomerAddress
（CustomerAddressID int IDENTITY（1,1）,
AddressTypechar（4）NOTNULL,
PrimaryAddressFlag bitNOTNULL,
AddressLine1varchar（30）NOTNULL,
AddressLine2varchar（30）NULL,
AddressLine3varchar（30）NULL,
City varchar（50）NOTNULL,
StateProvinceID intNULL,
PostalCode char（10）NULL,
CountryIDintNULL）
相反，全局临时表对SQLServer实例中的任何用户都是可见的。
全局临时表在访问该表的最后一个连接关闭时被删除。
通过使用CREATETABLE命令并在表名前添加两个字符（##），可以创建一个全局临时表，如下所示：
CREATETABLE##CustomerAddress
（CustomerAddressID int IDENTITY（1,1）,
AddressTypechar（4）NOTNULL,
PrimaryAddressFlag bitNOTNULL,
AddressLine1varchar（30）NOTNULL,
AddressLine2varchar（30）NULL,
AddressLine3varchar（30）NULL,
City varchar（50）NOTNULL,
StateProvinceID intNULL,
PostalCode char（10）NULL,
CountryIDintNULL）
表变量
表变量提供了临时表的一种替代方案，并可以在函数、触发器和存储过程中使用。
表变量不是将表及其所有数据存储在磁盘上的tempdb数据库中的一个表中，而是将它和所有关联的数据存储在内存中。
然而，如果放入表变量中的数据量使它所得存储空间大于可用的内存时，则溢出部分将被缓存到磁盘上的tempdb中。
表变量是创建它们的函数、触发器或存储过程的局部变量，并在该对象退出时自动 释放。
要以表变量的形式创建前面的顾客地址表，可以把该表声明为变量，
升级会员 