1.2　数据类型

1.2.1　整数数据类型

整数数据类型是SQL语言中最常用的数据类型之一，包括int、smallint、tinyint和bigint多种数据类型，它可以存储一定范围的整数。

1．int（integer）

int数据类型可存储−231～231−1（−2 147 483 648～2 147 483 647）的所有正负整型数据，存储空间为4个字节。32位的存储空间其中一位表示整型数据值的正负号，其他31位表示整型数据值的长度和大小。

2．smallint

smallint数据类型可存储−215～215−1（−32 768～32 767）的所有正负整型数据，存储空间为2个字节，是比int数据类型存储容量小的数据类型。

3．tinyint

tinyint数据类型可存储0～255的所有正整型数据，存储空间为1个字节。

4．bigint

bigint数据类型可存储−263～263−1（-9 223 372 036 854 775 808～9 223 372 036 854 775 807）的所有正负整型数据，存储空间为8个字节。

1.2.2　浮点数据类型

浮点数据类型用来存储必须精确计算的正负小数。浮点数据类型的优点是能够存储大范围的数字。缺点是容易发生舍入误差。例如，如果某列的精度是30，大于30的位数可以存储，但却不能保证其精度。舍入误差只能影响一个数超过精度的右边各位，所以在精度范围内数据是准确的。

1．real

real数据类型可存储−3.40e38～3.40e38的所有精度正负小数。一个real类型的数据占用4个字节的存储空间。

2．float

float数据类型可存储−1.79e308～1.79e308的浮点精度数字。一个float类型的数据占用8个字节的存储空间。float数据类型定义的形式为：

     float［(n)］

其中，n用于存储科学记数法为float型数据尾数的位数，其大小为1～53。

3．decimal（numeric）

decimal数据类型的存储数据范围是−e38～e38−1的固定精度和小数位的数字数据。一个decimal类型的数据占用2～17个字节。decimal数据类型定义的形式为：

     decimal[(p[，s])]

其中，p是指精度，指定小数点左边和右边可以存储的十进制数字的最大个数。精度必须是1～38的值。s是小数位数，小数位数必须介于0～精度p。

4．numeric

numeric数据类型与decimal数据类型完全相同，表示数据的范围、所占的存储空间及定义的形式都一样。

1.2.3　字符数据类型

字符数据类型用来存储数字符号、字母以及特殊符号。使用字符型数据时，一般要给数据加上单引号或双引号。

1．char

char数据类型使用固定长度来存储字符，最大长度为8000个字符。char数据类型的定义形式为：

     char[(n)]

其中，n表示所有字符占用的存储空间，以字节为单位。n必须是一个1～8000的数值。若不指定n值，则系统默认为1。

利用char数据类型来定义表列或者定义变量时，应该给定数据的最大长度。如果实际数据的字符长度小于给定的最大长度，则多余的字节会以空格填充；如果实际数据的字符长度超过给定的最大长度，则超过的字符将会被截断。在使用字符型常量为字符数据类型赋值时，必须使用双引号或单引号将字符型常量括起来，如'Happy' '乐'。

2．nchar

nchar用来定义固定长度的Unicode数据，最大长度为4000个字符。与char类型相似，nchar数据类型的定义形式为：

     nchar{[n]}

其中，n表示所有字符占用的存储空间，以字节为单位。n必须是一个1～4000的数值。

nchar类型采用Unicode标准的数据类型，多占用一倍的存储空间。使用Unicode标准的好处是因其使用2个字节做存储单位，故其中一个存储单位的容量就大大增加了，可以将多种语言文字囊括在内，如在一个数据列中可以同时出现中文、英文、法文和德文等，而不会出现编码冲突。

3．varchar

varchar用来存储最长8000个字符的变长字符数据。与char数据类型不同，varchar数据类型的存储空间随存储在表列中每一个数据的字符数的不同而变化。

例如，如果定义表列为varchar(20)，那么存储在该列的数据最长为20个字节。但是，在每列数据没有达到20个字节时，并不会在多余的字节上填充空格。

varchar数据类型的定义形式为：

     varchar[(n)]

其中，n表示所有字符占用的存储空间，以字节为单位。n必须是一个1～8000的数值。若输入的数据过长，SQL将会截掉其超出的部分。

4．nvarchar

用来定义可变长度的二进制数据，最大长度为4000个字符。nvarchar数据类型的定义形式为：

     Nvarchar[(n)]

其中，n表示所有字符占用的存储空间，以字节为单位。n必须是一个1～4000的数据。nvarchar与nchar的区别和varchar与char的区别类似。

1.2.4　日期和时间数据类型

日期和时间数据类型可以存储日期和时间的组合数据，包括datetime和salldatetime两类。

1．datetime

datetime数据类型所占用的存储空间为8个字节，其中前4个字节用于存储1900年1月1日以前或以后的天数，数值分正负，正数表示在此日期之后的日期，负数表示在此日期之前的日期；后4个字节用于存储从此日零时起所指定的时间经过的毫秒数。如果在输入数据时省略了时间部分，则系统将12:00:00:000AM作为时间默认值；如果省略了日期部分，则系统将1900年1月1日作为日期默认值。

datetime数据类型用于存储日期和时间的结合体，它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达1.33毫秒。

2．smalldatetime

smalldatetime数据类型使用4个字节存储数据。其中前2个字节存储从1900年1月1日以后的天数，后2个字节存储此日零时起所指定的时间经过的分钟数。

smalldatetime数据类型与datetime数据类型相似，但日期时间范围较小，为从1900年1月1日到2079年6月6日。此数据类型精度较低，只能精确到分钟，分钟个位上为根据秒数四舍五入的值，即以30秒为界四舍五入。例如，当datetime时间为14:38:30.283时，smalldatetime认为是14:39:00。

1.2.5　货币数据类型

货币数据类型用于存储货币值。在使用货币数据类型时，应在数据前加上货币符号，系统才能辨识其为哪国的货币，如果不加货币符号，则默认为“￥”。

1．money

money数据类型使用8个字节存储。货币数据值为−263～263−1（−9 223 372 036 854 775 808～9 223 372 036 854 775 807）。数据精度为万分之一货币单位。

2．smallmoney

smallmoney货币数据值为−2 147 483 648～2 147 483 647，存储空间为4个字节。其存储的货币值范围比money数据类型小。

1.2.6　二进制数据类型

二进制数据是一些用十六进制来表示的数据。例如，十进制数据245表示成十六进制数据应该是F5。在SQL中，使用两种数据类型存储二进制数据，分别是binary和varbinary。

1．binary

binary数据类型用于存储二进制数据。在使用时必须指定binary类型数据的大小，至少应为1个字节。binary数据类型占用n+4个字节的存储空间。其定义形式为：

     binary (n)

n表示数据的长度，取值为1～8000。

在输入数据时，必须在数据前加上字符“0x”，作为二进制数据类型标识。例如，要输入“very”则应输入“0xvery”。如果输入的数据过长，系统将会自动截掉其超出部分。如果输入的数据位数为奇数，则系统会在起始符号“0x”后添加一个0，如上述的“0xvery”会被系统自动变为“0x0very”。

2．varbinary

varbinary数据类型用于存储可变长度的二进制数据，存储长度等于实际数值长度加上4个字节。varbinary数据类型的定义形式如下：

     varbinary (n)

n的取值也为1～8000，如果输入的数据过长，系统将会截掉超出部分。

1.2.7　文本和图像数据类型

前面介绍的几种数据类型存储的容量有限，当需要存储大量字符及二进制数据时，就需要使用文本和图像数据类型。SQL提供了text、ntext和image 3种文本和图像数据类型。

1．text

text数据类型用于存储大量文本数据，其容量理论上为1～231−1（2 147 483 647）个字节，在实际应用时需要视硬盘的存储空间而定。

在SQL中，通常将text和image类型的数据直接存放到表的数据中，而不是存放到不同的数据页中。这就减少了存储text和image数据的空间，并相应减少了磁盘处理这类数据的I/O数量。

2．ntext

ntext数据类型与text类型相似。不同的是，ntext类型采用UNICODE标准字符集。因此，其理论容量为230−1（1 073 741 823）个字节。

3．image

image数据类型是可变长度的二进制数据类型，最大长度为231−1个字符。通常用来存储图形等对象连接与嵌入OLE（Object Linking and Embedding）对象。在输入数据时与binary数据类型一样，必须在数据前加上字符“0x”作为二进制标识。

1.2.8　用户自定义数据类型

用户自定义数据类型并不是真正的数据类型，它只是提供了一种加强数据库内部元素和基本数据类型之间一致性的机制。通过使用用户自定义数据类型能够简化对常用规则和默认值的管理。

在SQL Server数据库中，可以使用系统数据类型sp_addtype创建用户自定义数据类型。语法格式如下：

     sp_addtype[@typename=]type，
     [@phystype=]system_data_type
     [，[@nulltype=]'null_type']
     [，[@owner=]'owner_name']

参数说明如下。

　[@typename=]type：指定待创建的用户自定义数据类型的名称。用户自定义数据类型名称必须遵循标识符的命名规则，而且在数据库中唯一。

　[@phystype=]system_data_type：指定用户自定义数据类型所依赖的系统数据类型。

　[@nulltype=]'null_type'：指定用户自定义数据类型的可空属性，即用户自定义数据类型处理空值的方式。取值为NULL，NOT NULL或NONULL。

【例1.2】　使用系统数据类型sp_addtype创建用户自定义数据类型。（实例位置：资源包\TM\sl\1\2）

SQL语句如下：

     use db_mrsql --使用db_mrsql数据库
     --使用sp_addtype创建用来存储邮政编码信息的postalcode用户的定义数据类型
     EXEC sp_addtype mrVarChar,'varchar(20)','not null'

执行此SQL语句，将创建自定义数据类型“mrVarChar”。

注意

如果已经使用Management Studio创建了同名的用户自定义类型，那么执行此SQL代码时会出现如图1.2所示的错误。

图1.2　创建同名自定义数据类型时报错

创建了用户自定义数据类型后，就可以像系统数据类型一样使用它。例如，在db_mrsoft数据库的表中创建新的字段，为字段“学生姓名”指定数据类型时，可以在下拉列表框中选择刚刚创建的数据类型mrVarChar，如图1.3所示。

图1.3　创建字段时选择mrVarChar数据类型

另外，根据需要，还可以修改和删除用户自定义的数据类型。使用系统存储过程sp_droptype可删除用户定义的数据类型。