1、chp11- SQL 語言的資料型別本節所使用的版本為 SQL Server 2008、SQL Server 200518-1. 實作：利用 SQL Server Management Studio 工具 - 建立資料表 .218-2. 觀念： SQL 如何設定整數（根據可以處理的整數大小，整數共有 5 種） .518-3. 觀念： SQL 如何設定有小數點數字：精確位數：共有 2 種：numeric, decimal.618-4. 觀念： SQL 如何設定有小數點數字：近似浮點數：共有 2 種：float, real .718-5. 觀念： SQL 如何設定日期資料：共有 2 種：datet
2、ime, smalldatetime.818-6. 觀念： SQL 如何設定貨幣資料：共有 2 種：money, smallmoney918-7. 觀念： SQL 如何設定字串資料：共有 4 種：char(n), varchar(n), varchar(max), text.1018-8. 觀念： SQL 如何設定 Unicode 字串資料：共有 4 種：nchar(n), nvarchar(n), nvarchar(max), ntext .1118-9. 觀念： SQL 如何設定布林(Boolean)資料：SQL 沒有 boolean 資料型別，但一般都用 bit 整數來取代，0 (fal
3、se)， 1 (true) 1218-10. 觀念：SQL 如何設定二元碼字串資料：共有 4 種：binary (n), varbinary (n), varbinary (max), image1318-11. 觀念：SQL 如何設定點矩陣圖形、GIF、JPEG、Word 文件、Excel 檔案資料：image .1318-12. 觀念：SQL 如何設定標記型 資料：共有 2 種：timestamp, uniqueidentifier .1418-13. 觀念：SQL 如何設定儲存 XML 文件格式欄位資料：xml .1518-14. 觀念：SQL 的特殊資料類型：sql_variant、c
4、ursor、table 1718-1.實作：利用 SQL Server Management Studio工具 - 建立資料表 (1).先建立資料庫：專案-1(2).再建立兩個資料表(3).第 1個資料表：專案表（專案編號，專案名稱，起始日期，終止日期，預算）(4).各種資料行屬性 小數位數：設定欄位的小數點位數，只有 decimal 及 numeric 資料型別可以設定此屬性。 整數位數：設定欄位的整數位數，只有 decimal 及 numeric 資料型別可以設定此屬性。 預設值或繫結：設定欄位的預設值，有設定預設值時，新增資料若沒有給定欄位初始值，就會以此預設值為其初始值。繫結是指建立一
5、預設值資料庫物件，在將它繫結到資料表欄位，後面章節會再做說明。如果欄位屬 uniqueidentifier 資料類型，該屬性預設值為 newid() RowGuide：設定是否由 SQL Server 自動產生全域唯一的欄位值，只有uniqueidentifier 資料類型的欄位才有此屬性。 定序：設定欄位要套用的定序名稱及排序選項，只有char、 varchar、text 、nchar、nvarchar、ntext 字串資料類型的欄位才有此屬性。 計算資料行規格：設定計算欄位的公式。 描述：用來說明輸入的欄位，每個欄位都有此屬性。 識別規格：是否允許欄位值自動編號。 (Is Identity
6、)：設定為是，表示允許欄位值自動編號。資料表只能有一個資料行具有識別屬性，並且資料類型需為bigint、decimal 、int、numeric、smallint 或 tinyint。此外，識別項資料行不可允許 Null，並且不可有預設值。 識別值種子：設定自動編號的起始值，預設起始值是 1。 識別值增量：設定自動編號的增加值，預設增加值為 1。 (1).第 2個資料表：員工表（員工編號，姓名，專案編號，部門，到職日，薪資）18-2.觀念：SQL 如何設定整數（根據可以處理的整數大小，整數共有 5種）資料類型 資料範圍 儲存空間bit 0、1 或 NULL 18 bit 的欄位實際將佔 1 b
7、yte916 bit 的欄位實際將佔 2 bytes tinyint微整數0 255 1 byte smallint短整數-32,768 32,767 2 bytesint一般整數最常用-2147483648 2147483647 4 bytesbigint長整數-9223372036854775808 92233720368547758088 bytes18-3.觀念：SQL 如何設定有小數點數字：精確位數：共有 2種：numeric, decimal資料類型 資料範圍 儲存空間nemeric decimal-1038+1 1038-1 依精確度而定1 9 位數：5 bytes10 19 位
8、數： 9 bytes20 28 位數： 13 bytes29 38 位數： 17 bytes 注意： numeric, decimal，這兩種資料型別完全相同 使用時，必須指明精確度（即全部有效位數） ，與小數點位數 範例：numeric(5,2)，表示 精確度為 5 位數，其中 3 位整數2 位小數 若沒有指定精確度，則預設為 numeric(18,0) 精確度使用範圍 138 小數點位數最少為 0，最多不可超過精確度18-4.觀念：SQL 如何設定有小數點數字：近似浮點數：共有 2種：float, real資料類型 資料範圍 儲存空間float -1.79E + 308 1.79E + 3
9、08 最多可以表達到 15 位數8 bytes real 3.40E + 38 3.40E + 38 最多可以表達到 7 位數4 bytes 使用時機： 當數值非常大（或非常小）時，可以用近似浮點數值來取其近似值 範例：數值 23456646677799 近似為 2.35E+13注意： E 為科學記號 E+13，表示：10 的 13 次方18-5.觀念：SQL 如何設定日期資料：共有 2種：datetime, smalldatetime資料類型 資料範圍 儲存空間datetime 1753/1/1 9999/12/31datetime 資料型別的時間精準度可以到 3.33 毫秒 輸入格式：yy
10、yy-mm-dd h:m:s範例：2009-5-10 15:42:26.7998 bytessmalldatetime 1900/1/1 2079/6/6 smalldatetime 時間精準度可以到 分輸入格式：yyyy-mm-dd h:m範例：2009-5-10 15:424 bytes(1).SQL敘述範例範例 日期類型資料 create table 日期表 ( 編號 int , 日期 datetime ) insert 日期表 values (1, 2007/2/1) insert 日期表 values (2, 2007/2/1 03:50:10) insert 日期表 values
11、(3, 2/2/2007) insert 日期表 values (4, 2002-2-3) insert 日期表 values (5, 12) - 從 1900/1/1 算起 12 天後的日期 insert 日期表 values (6, getdate() select * from 日期表 執行結果： 18-6.觀念：SQL 如何設定貨幣資料：共有 2種：money, smallmoney資料類型 資料範圍 儲存空間money -263 263 1-922337203685477.5808 922337203685477.5808可以精確到小數第 4 位8 bytessmallmoney -
12、231 231 1-214748.3648 214748.3648可以精確到小數第 4 位4 bytes注意 貨幣型資料型別代表正數或負數的金額。 數字前會有$符號 每個千分位(每 3 個位數)都會有 1 個,來分開 18-7.觀念：SQL 如何設定字串資料：共有 4種：char(n), varchar(n), varchar(max), text資料類型 資料範圍 儲存空間char(n) 18000 個字元 固定長度，一個字元 1 byte，儲存空間 = nvarchar(n) 18000 個字元 變動長度，儲存空間視字元數決定 varchar(max) 1231-1 個字元 變動長度，一個
13、字元 1 byte ，儲存空間視字元數決定，上限 2G bytestext 1231-1 個字元 變動長度，一個字元 1 byte，儲存空間視字元數決定注意： SQL Server 2005 新增 varchar(max)/nvarchar(max)/varbinary(max)這幾種所謂大數值資料類型 微軟建議讀者往後儘量以這種新的資料類型取代 text、ntext 、image 這類的儲存大量資料的舊版資料類型來使用。 所以，以後萬一某個欄位的字串長度可能有幾萬字，微軟建議將之設定為varchar(max) （而不要再用 text 了）注意： char(n), varchar(n)在使用時
14、必須指定字元長度 n，例如：varchar(50) ， 若 varchar 未設定 n，預設為 1注意：char(n), varchar(n)的字串長度限制 char(n), varchar(n)最多只能放8000 個字元 結論：如果某一欄位的字串很長，超過 8000 字，就要使用varchar(max)注意：char(n), varchar(n)哪一個好？ 因為 varchar(50)為一變動長度型別，所以比 char(50)固定長度型別還要好，所以 varchar(50)比較省空間 所以建議：設定字串採用varchar(50)，或是 varchar(max)比較好，因為省空間18-8.觀念
15、：SQL 如何設定 Unicode字串資料：共有 4種：nchar(n), nvarchar(n), nvarchar(max), ntext資料類型 資料範圍 儲存空間nchar(n) 14000 個字元 固定長度，一個字元 2 bytes，儲存空間 = 2nnvarchar(n) 14000 個字元 變動長度，一個字元 2 bytes，儲存空間視字元數決定 nvarchar(max) 1230-1 個字元 變動長度，一個字元 2 byte ，儲存空間視字元數決定，上限 2G bytesntext 1230-1 個字元 變動長度，一個字元 2 byte，儲存空間視字元數決定比較：SQL 的一
16、般字串，與 unicode 字串有何不同 Unicode 字串是雙位元文字編碼 Unicode 字串與一般字串的使用，相當類似 但Unicode 字串的 一個字元是用2 bytes來儲存 一般字串的一個字元是用1 bytes來儲存18-9.觀念：SQL 如何設定布林(Boolean)資料：SQL 沒有 boolean資料型別，但一般都用 bit整數來取代，0 (false)， 1 (true)SQL 沒有 boolean 資料型別但一般都用 bit 整數來取代資料類型 資料範圍 儲存空間bit 0、1 或 Null 18 bit 的欄位實際將佔 1 byte916 bit 的欄位實際將佔 2
17、bytes 0 代表 false，否1 代表 true，是18-10.觀念：SQL 如何設定二元碼字串資料：共有 4種：binary (n), varbinary (n), varbinary (max), image18-11.觀念：SQL 如何設定點矩陣圖形、GIF、JPEG、Word 文件、Excel檔案資料：image資料類型 資料範圍 儲存空間binary(n) 18000 個字元 固定長度，儲存空間 = n+4varbinary(n) 18000 個字元 變動長度，儲存空間 = 字元數+ 4 varbinary(max) 1231-1 個字元 變動長度，儲存空間視字元數決定，上限
18、2G bytesimage 0231-1 (2,147,483,647)變動長度，儲存空間= 字元數+ 4 可用來儲存 Word 文件、 Excel 試算表、聲音檔及圖型檔等。使用時機： 二元碼字串資料型別可以用來處理二元碼資料(binary)，例如：0x5F 二元碼字串資料多以 16 進位表示，且要加上 0x 字頭 Image 可以用來處理點矩陣圖形、GIF、JPEG、Word 文件、Excel 檔案資料18-12.觀念：SQL 如何設定標記型 資料：共有 2種：timestamp, uniqueidentifier資料類型 資料內容 儲存空間timestamp 在同一資料庫中該欄位值是唯一
19、的 8 bytes 的 16 進位值範例：0x00000000000000001308 bytesuniqueidentifier 是一個全域唯一識別碼(GUID)，用來識別每一筆資料的唯一性 16bytes 的 16 進位值範例：4C047CB3-B007-11D2-9C59-0080C846994D16 bytes使用時機： rowversion(timestamp)：記錄資料更新的時間戳記， 當記錄有變動時，該筆記錄的 timestamp 欄位就會自動產生新值，而且此值是唯一的。 uniqueidentifier：全域唯一識別碼 (GUID)，用來識別每一筆資料的唯一性。(1).SQL敘
20、述範例：範例 時間戳記範例 USE 練習庫 create table 物品 ( 品名 varchar(20) primary key, 單價 int, 異動日期 datetime, 時戳 timestamp ) insert into 物品( 品名,單價 ,異動日期) values ( 招牌咖啡,35,getdate() insert into 物品( 品名,單價 ,異動日期) values ( 拿鐵咖啡,60,getdate() select * from 物品 -查詢資料 update 物品 - 資料有被異動(修改) set 單價 = 40 where 品名= 招牌咖啡 select *
21、from 物品 -查詢資料 18-13.觀念：SQL 如何設定儲存 XML文件格式欄位資料：xml資料類型 資料內容 儲存空間xml 符合 xml 格式的任何資料 最多可儲存 2GB特色： 某個欄位若被設定為 xml 型別，則其可以儲存的資料內容不必是單一的值，而可以是多筆資料，甚至是一整張訂單（有多筆多欄位） ，或任何資料（只要符合 xml 格式即可）優點： 存放資料更有彈性，與可擴增性 對於資料的對應更加直覺與便利 若此欄位是一整張訂單，那這樣即不需要再去將此訂單正規化、化成另外一張資料表、以及建立關聯了，這樣太麻煩了注意： Xml 型別為 SQL Server 2005 新增的系統資料類
22、型，可以儲存 XML 文件格式的欄位，儲存最大空間為 2GB。 XML 資料類型的使用方式與其他的系統資料類型 (如：int、char等)使用方式相同。 你也可以將一般資料型態經由 CONVERT 或 CAST 函數將之轉換成 XML的資料類型資料 。 範例： XML 資料類型練習create table 訂單 ( 訂單編號 int PRIMARY KEY, 負責業務 char(6), 訂單日期 datetime, 相關資料 xml ) GO INSERT INTO 訂單 VALUES (1,陳啟泰,2007/2/8, CAST( 1 37 吋液晶電視 40000 120 as xml ) G
23、O SELECT * FROM 訂單 結果：滑鼠點一下18-14.觀念：SQL 的特殊資料類型：sql_variant、cursor、table資料類型 資料內容sql_variant 可以用來儲存各種型別的資料，但 text、ntext、image、timestamp 及 sql_variant 除外。若某個欄位可能會儲存不同型別的資料時，即可使用 sql_variant 此型別cursor 供變數或預存程序 OUTPUT 參數使用的資料型別 table 可以用來儲存查詢結果，為表格型式的資料 (1).範例：範例 table 資料表型態 USE 練習庫 DECLARE tblDept TABLE ( 部門 ID int IDENTITY(1,1), 部門 varchar(10) ) GO INSERT tblDept SELECT DISTINCT 部門 FROM 華中資訊 SELECT * FROM tblDept 結果：
