SQLServer创建一个表.docx
- 文档编号:675826
- 上传时间:2023-04-29
- 格式:DOCX
- 页数:18
- 大小:25.36KB
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
({
[
[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中。
表变量是创建它们的函数、触发器或存储过程的局部变量,并在该对象退出时自动 释放。
要以表变量的形式创建前面的顾客地址表,可以把该表声明为变量,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 创建 一个