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可刪除用戶定義的數據類型。