SQLServer數(shù)據(jù)庫設(shè)計復(fù)習(xí)數(shù)據(jù)類型以及數(shù)據(jù)轉(zhuǎn)換函數(shù)第1頁目標(biāo)掌握SQL中慣用數(shù)據(jù)類型掌握部分數(shù)據(jù)類型范圍及各自之間相互區(qū)分在設(shè)計數(shù)據(jù)表時候會選擇適當(dāng)數(shù)據(jù)類型掌握使用Convert和Cast函數(shù)進行數(shù)據(jù)類型或者格式轉(zhuǎn)換第2頁數(shù)據(jù)類型分類字符數(shù)據(jù)類型準(zhǔn)確數(shù)值數(shù)據(jù)類型近似數(shù)值數(shù)據(jù)類型二進制數(shù)據(jù)類型日期和時間數(shù)據(jù)類型其它系統(tǒng)數(shù)據(jù)類型CLR集成-自定義數(shù)據(jù)類型第3頁字符數(shù)據(jù)類型字符數(shù)據(jù)類型（字符數(shù)據(jù)是由任何字母、符號和數(shù)字任意組合而成數(shù)據(jù)。）varcharchartextnvarcharncharntext第4頁字符、字節(jié)、Unicode字符字符：人們使用記號，抽象意義上一個符號。如'1','中','a','$','￥',……字節(jié)：計算機中存放數(shù)據(jù)單元，一個8位二進制數(shù)，是一個很詳細存放空間。如0x01,0x45,0xFA,……Unicode字符：Unicode（統(tǒng)一碼、萬國碼、單一碼）是一個在計算機上使用字符編碼。它為每種語言中每個字符設(shè)定了統(tǒng)一而且唯一二進制編碼，以滿足跨語言、跨平臺進行文本轉(zhuǎn)換、處理要求，每個字符占用2個字節(jié)。這么理論上一共最多能夠表示65,536(216次方)個字符?；緷M足各種語言使用。第5頁字符數(shù)據(jù)類型存放空間及范圍數(shù)據(jù)類型描述存放空間Char(n)N為1~8000字符之間n字節(jié)Nchar(n)N為1~4000Unicode字符之間(2n字節(jié))＋2字節(jié)額外開銷Ntext最多為230–1(1?073?741?823)Unicode字符每字符2字節(jié)Nvarchar(max)最多為230–1(1?073?741?823)Unicode字符2×字符數(shù)＋2字節(jié)額外開銷Text最多為231–1(2?147?483?647)字符每字符1字節(jié)Varchar(n)N為1~8000字符之間每字符1字節(jié)＋2字節(jié)額外開銷Varchar(max)最多為231–1(2?147?483?647)字符每字符1字節(jié)＋2字節(jié)額外開銷第6頁字符數(shù)據(jù)類型區(qū)分CHAR：CHAR存放定長數(shù)據(jù)很方便，CHAR字段上索引效率級高，比如定義char(10)，那么不論你存放數(shù)據(jù)是否到達了10個字節(jié)，都要占去10個字節(jié)空間,不足自動用空格填充。VARCHAR：存放變長數(shù)據(jù)，但存放效率沒有CHAR高。假如一個字段可能值是不固定長度，我們只知道它不可能超出10個字符，把它定義為VARCHAR(10)是最合算。VARCHAR類型實際長度是它值實際長度+1。為何“+1”呢？這一個字節(jié)用于保留實際使用了多大長度。從空間上考慮，用varchar適當(dāng)；從效率上考慮，用char適當(dāng)，關(guān)鍵是依據(jù)實際情況找到權(quán)衡點。TEXT：text存放可變長度非Unicode數(shù)據(jù)，最大長度為2^31-1(2,147,483,647)個字符。第7頁NCHAR、NVARCHAR、NTEXT。這三種從名字上看比前面三種多了個“N”。它表示存放是Unicode數(shù)據(jù)類型字符。我們知道字符中，英文字符只需要一個字節(jié)存放就足夠了，但漢字眾多，需要兩個字節(jié)存放，英文與漢字同時存在時輕易造成混亂，Unicode字符集就是為了處理字符集這種不兼容問題而產(chǎn)生，它全部字符都用兩個字節(jié)表示，即英文字符也是用兩個字節(jié)表示。nchar、nvarchar長度是在1到4000之間。和char、varchar比較起來，nchar、nvarchar則最多存放4000個字符，不論是英文還是漢字；而char、varchar最多能存放8000個英文，4000個漢字。能夠看出使用nchar、nvarchar數(shù)據(jù)類型時不用擔(dān)心輸入字符是英文還是漢字，較為方便，但在存放英文時數(shù)量上有些損失。第8頁尤其要提數(shù)據(jù)類型是text和ntext。text數(shù)據(jù)類型用于在數(shù)據(jù)頁內(nèi)外存放大型字符數(shù)據(jù)。應(yīng)盡可能少地使用這兩種數(shù)據(jù)類型，因為可能影響性能但可在單行列中存放多達2GB數(shù)據(jù)。與text數(shù)據(jù)類型相比，更加好選擇是使用varchar(max)類型，因為將取得更加好性能。另外，text和ntext數(shù)據(jù)類型在SQLServer一些未來版本中將不可用，所以現(xiàn)在開始還是最好使用varchar(max)和nvarchar(max)而不是text和ntext數(shù)據(jù)類型。第9頁準(zhǔn)確數(shù)值數(shù)據(jù)類型準(zhǔn)確數(shù)值數(shù)據(jù)類型bittinyintsmallintintbigintnumericdecimalmoneyfloatreal第10頁準(zhǔn)確數(shù)值數(shù)據(jù)類型存放空間及范圍數(shù)據(jù)類型描述存放空間bit0、1或Null1字節(jié)(8位)tinyint0～255之間整數(shù)1字節(jié)smallint–32?768～32?767之間整數(shù)2字節(jié)int–2?147?483?648～2?147?483?647之間整數(shù)4字節(jié)bigint–9?223?372?036?854?775?808～9?223?372?036?854?775?807之間整數(shù)8字節(jié)numeric(p,s)或decimal(p,s)–1?038＋1～1?038–1之間數(shù)值最多17字節(jié)money–922?337?203?685?477.580?8～922?337?203?685?477.580?78字節(jié)smallmoney–214?748.3648～2?14?748.36474字節(jié)第11頁numeric和decimal數(shù)據(jù)類型默認最大精度值是38。如decimal和numeric等數(shù)值數(shù)據(jù)類型可存放小數(shù)點右邊或左邊變長位數(shù)。Scale是小數(shù)點右邊位數(shù)。精度(Precision)定義了總位數(shù)，包含小數(shù)點右邊位數(shù)。比如，14.88531可為numeric(7,5)或decimal(7,5)。假如將14.25插入到numeric(5,1)列中，它將被舍入為14.3。p和s必須恪守規(guī)則：0<=s<=p<=38。對于decimal和numeric數(shù)據(jù)類型，Microsoft?SQLServer?將精度和小數(shù)位數(shù)每個特定組合看作是不一樣數(shù)據(jù)類型。比如，decimal(5,5)和decimal(5,0)被看成不一樣數(shù)據(jù)類型。第12頁近似數(shù)值數(shù)據(jù)類型floatreal第13頁近似數(shù)值數(shù)據(jù)類型存放空間及范圍數(shù)據(jù)類型描述存放空間float[(n)]–1.79E＋308~–2.23E–308,0,2.23E–308~1.79E＋308N<=24－4字節(jié)N>24－8字節(jié)real–3.40E＋38~–1.18E–38,0,1.18E–38~3.40E＋384字節(jié)第14頁Float和Real數(shù)據(jù)類型為近似數(shù)據(jù)。每個數(shù)據(jù)包含科學(xué)計數(shù)法，因為缺乏精度就造成了數(shù)據(jù)丟失。比如，三分之一這個分數(shù)記作0.3333333…，當(dāng)使用近似數(shù)據(jù)類型時能準(zhǔn)確表示。所以，從系統(tǒng)中檢索到數(shù)據(jù)可能與存放在該列中數(shù)據(jù)不完全一樣。你就能夠?qū)?，234，467，890存放為1.23E+9。也就是說1.23這個數(shù)據(jù)中小數(shù)點能夠向右移9位。你能夠看到，在用這種方法存放數(shù)據(jù)時，你丟失了一些小數(shù)。在轉(zhuǎn)換成科學(xué)計數(shù)法時，原數(shù)據(jù)（1，234，467，890）就變成了1，230，000，000。

現(xiàn)在返回到數(shù)據(jù)類型。Float和real數(shù)據(jù)類型能用科學(xué)計數(shù)法存放，唯一區(qū)分就是這些值得范圍和存放大小。上面表為這些數(shù)據(jù)類型值范圍。Real數(shù)據(jù)類型需要4個字節(jié)存放空間，而且固定精度為7。你能夠用這些浮點數(shù)據(jù)指定精度或整個數(shù)據(jù)數(shù)字，即從1到53。存放空間大小為4個字節(jié)（精度小于25時）到8個字節(jié)（精度為25到53）。

第15頁二進制數(shù)據(jù)類型binaryimageVarbinary第16頁二進制數(shù)據(jù)類型存放空間及范圍數(shù)據(jù)類型描述存放空間Binary(n)N為1~8000十六進制數(shù)字之間n字節(jié)Image最多為231–1(2?147?483?647)十六進制數(shù)位每字符1字節(jié)Varbinary(n)N為1~8000十六進制數(shù)字之間每字符1字節(jié)＋2字節(jié)額外開銷Varbinary(max)最多為231–1(2?147?483?647)十六進制數(shù)字每字符1字節(jié)＋2字節(jié)額外開銷第17頁varbinary、binary、varbinary(max)或image等二進制數(shù)據(jù)類型用于存放二進制數(shù)據(jù)，如圖形文件、Word文檔或MP3文件。其值為十六進制0x0~0xf。image數(shù)據(jù)類型可在數(shù)據(jù)頁外部存放最多2GB文件。image數(shù)據(jù)類型首選替換數(shù)據(jù)類型是varbinary(max)，可保留最多8KB二進制數(shù)據(jù)，其性能通常比image數(shù)據(jù)類型好。SQLServer新功效是能夠在操作系統(tǒng)文件中經(jīng)過FileStream存放選項存放varbinary(max)對象。這個選項將數(shù)據(jù)存放為文件，同時不受varbinary(max)2GB大小限制。第18頁日期和時間數(shù)據(jù)類型datedatetimedatetime2datetimeoffsetsmalldateTimetime第19頁日期和時間數(shù)據(jù)類型存放空間及范圍數(shù)據(jù)類型描述存放空間Date0001-1-1至9999-12-31【精準(zhǔn)到天yyyy-mm-dd

】3字節(jié)Datetime1753-1-1至9999-12-31【精準(zhǔn)到0.00333秒yyyy-mm-ddhh:mm:ss:nnn】8字節(jié)Datetime2(n)0001-1-1至9999-12-31【精準(zhǔn)到100納秒yyyy-mm-ddhh:mm:ss:nnnnnn】6~8字節(jié)Datetimeoffset(n)0001-1-1至9999-12-31(全球標(biāo)按時間)【精準(zhǔn)到100納秒yyyy-mm-ddhh:mm:ss:nnnnnn+|-hh:mm】8~10字節(jié)SmalldateTime1900-1-1至2079-6-6【精準(zhǔn)到1分鐘yyyy-mm-ddhh:mm:ss】4字節(jié)

Time(n)0:0:0.000000至23:59:59.999999【精準(zhǔn)到100納秒hh:mm:ss:nnnnnn

】3~5字節(jié)第20頁datetime和smalldatetime數(shù)據(jù)類型用于存放日期和時間數(shù)據(jù)。smalldatetime為4字節(jié)，存放1900年1月1日~2079年6月6日之間時間，且只準(zhǔn)確到最近分鐘。datetime數(shù)據(jù)類型為8字節(jié)，存放1753年1月1日~9999年12月31日之間時間，且準(zhǔn)確到最近3.33毫秒。SQLServer有4種與日期相關(guān)新數(shù)據(jù)類型：datetime2、dateoffset、date和time。經(jīng)過SQLServer聯(lián)機叢書可找到使用這些數(shù)據(jù)類型示例。datetime2數(shù)據(jù)類型是datetime數(shù)據(jù)類型擴展，有著更廣日期范圍。時間總是用時、分鐘、秒形式來存放。能夠定義末尾帶有可變參數(shù)datetime2數(shù)據(jù)類型--如datetime2(3)。這個表示式中3表示存放時秒小數(shù)精度為3位，或0.999。有效值為0~9之間，默認值為3。第21頁datetimeoffset數(shù)據(jù)類型和datetime2數(shù)據(jù)類型一樣，帶有時區(qū)偏移量。該時區(qū)偏移量最大為＋/-14小時，包含了UTC偏移量，所以能夠合理化不一樣時區(qū)捕捉時間。date數(shù)據(jù)類型只存放日期，這是一直需要一個功效。而time數(shù)據(jù)類型只存放時間。它也支持time(n)申明，所以能夠控制小數(shù)秒粒度。與datetime2和datetimeoffset一樣，n可為0~7之間。第22頁其它系統(tǒng)數(shù)據(jù)類型cursorhierarchyidSQL_Varianttabletimestamporrowversionuniqueidentifierxml第23頁CLR集成在SQLServer中，還可使用公共語言運行庫(CommonLanguageRuntime，CLR)創(chuàng)建自己數(shù)據(jù)類型和存放過程。這讓用戶能夠使用VisualBasic或C#編寫更復(fù)雜數(shù)據(jù)類型，以滿足業(yè)務(wù)需求。這些類型被定義為基本CLR語言中類結(jié)構(gòu)。第24頁CAST和CONVERTCAST和CONVERT用來更改一個值數(shù)據(jù)類型或格式。兩個函數(shù)目標(biāo)相同，但CONVERT有能力去更改格式。格式能夠用來把數(shù)字4431.334更改為慣用貨幣格調(diào)4,431.33（每3個數(shù)字一個逗號而且小數(shù)點后保留2位），或者把4位年更改位兩位年07。第25頁CAST函數(shù)CAST(expressionASdata_type)

第26頁CONVERT函數(shù)CONVERT(data_type,expression[,style])第27頁例子:

SELECT

CONVERT(varchar(30),getdate(),101)

now

結(jié)果為:

now

---------------------------------------

09/15/

第28頁SELECT

CONVERT(varchar(100),

GETDATE(),

0):

05

16

10:57AM

SELECT

CONVERT(varchar(100),

GETDATE(),

1):

05/16/06

SELECT

CONVERT(varchar(100),

GETDATE(),

2):

06.05.16

SELECT

CONVERT(varchar(100),

GETDATE(),

3):

16/05/06

SELECT

CONVERT(varchar(100),

GETDATE(),

4):

16.05.06

SELECT

CONVERT(varchar(100),

GETDATE(),

5):

16-05-06

SELECT

CONVERT(varchar(100),

GETDATE(),

6):

16

05

06

SELECT

CONVERT(varchar(100),

GETDATE(),

7):

05

16,

06

SELECT

CONVERT(varchar(100),

GETDATE(),

8):

10:57:46

SELECT

CONVERT(varchar(100),

GETDATE(),

9):

05

16

10:57:46:827AM

SELECT

CONVERT(varchar(100),

GETDATE(),

10):

05-16-06 SELECT

CONVERT(varchar(100),

GETDATE(),

11):

06/05/16

SELECT

CONVERT(varchar(100),

GETDATE(),

12):

060516

第29頁SELECT

CONVERT(varchar(100),

GETDATE(),

13):

16

05

10:57:46:937

SELECT

CONVERT(varchar(100),

GETDATE(),

14):

10:57:46:967

SELECT

CONVERT(varchar(100),

GETDATE(),

20):

-05-16

10:57:47

SELECT

CONVERT(varchar(100),

GETDATE(),

21):

-05-16

10:57:47.157

SELECT

CONVERT(varchar(100),

GETDATE(),

22):

05/16/06

10:57:47

AM

SELECT

CONVERT(varchar(100),

GETDATE(),

23):

-05-16

SELECT

CONVERT(varchar(100),

GETDATE(),

24):

10:57:47

SELECT

CONVERT(varchar(100),

GETDATE(),

25):

-05-16

10:57:47.250

SELECT

CONVERT(varchar(100),

GETDATE(),

100):

05

16

10:57AM

SELECT

CONVERT(varchar(100),

GETDATE(),

101):

05/16/

SELECT

CONVERT(varchar(100),

GETDATE(),

102