第3章 數(shù)據(jù)類型與常用函數(shù)

第3章數(shù)據(jù)類型與常用函數(shù)

系統(tǒng)數(shù)據(jù)類型3.1用戶自定義數(shù)據(jù)類型3.2系統(tǒng)內(nèi)置函數(shù)3.3認(rèn)識表：記錄：表中的每一行被稱為一條記錄，表 由一條和多條記錄組成。字段：表中每一列成為一個字段，一張表 中最多有1024個字段。4個字段3條記錄數(shù)據(jù)類型：

在創(chuàng)建表的時候，要為表中的每個字段定義一種數(shù)據(jù)類型，即每個字段數(shù)據(jù)的格式，因此要對字段中的數(shù)據(jù)類型進(jìn)行定義。3.1系統(tǒng)數(shù)據(jù)類型 1字符數(shù)據(jù)類型

2精確數(shù)字類型

3近似數(shù)字類型

4日期和時間數(shù)據(jù)類型

5Unicode字符串

6二進(jìn)制字符串

7其他數(shù)據(jù)類型

字符數(shù)據(jù)是由任意字母、符號和數(shù)字任意組合而成的數(shù)據(jù)，是現(xiàn)實工作中最常用的數(shù)據(jù)類型之一。 字符數(shù)據(jù)的類型包括Char、Varchar和Text。數(shù)據(jù)兩端需要用單引號。

3.1.1字符數(shù)據(jù)類型例如：‘helloSQLServer2008’char(n),varchar(n) char(n)固定分配n個字節(jié)的存儲單元

n<8000.(多退少補)

varchar(n)可變長，存儲單元視輸入的情況而定，最多為n個字節(jié)注意：

1.字符數(shù)據(jù)由字母、符號和數(shù)字的任意組合組成；

2.輸入時，字符數(shù)據(jù)必須由單引號’’括起來；

3.當(dāng)取空字符串時，只需寫NULL，而非‘NULL’

text舉例：字符數(shù)據(jù)“abcdABCD我們學(xué)習(xí)”共____個字符占___個字節(jié)。

（注:字符數(shù)據(jù)類型char(n),varchar(n)中一個中文占兩個字節(jié)，一個數(shù)字、英文各占一個字節(jié)）答案：12個字符，16個字節(jié) 請問char(10)/char(20),varchar(20)用來存儲該數(shù)據(jù)，能存儲么？分析：

char(10):僅存儲“abcdABCD我”

char(20):存儲“abcdABCD我們學(xué)習(xí)”占20字節(jié)

varchar(20):存儲“abcdABCD我們學(xué)習(xí)”占16字節(jié)Unicode是“統(tǒng)一字符編碼標(biāo)準(zhǔn)”，用于支持國際上非英語種的字符數(shù)據(jù)的存儲和處理。Unicode字符串是為了在數(shù)據(jù)庫中容納多種語言存儲數(shù)據(jù)，而制定的數(shù)據(jù)類型。 其所占用的存儲大小是使用非Unicode數(shù)據(jù)類型所占用的存儲大小的2倍。包括Nchar（長度固定）、Nvarchar（長度可變）和Ntext。3.1.5Unicode字符串·nchar(n),nvarchar(n) nchar(n)存儲固定長度的雙字節(jié)數(shù)據(jù)，其中n代表最大字符數(shù)，取值范圍為1～4000個字符。

nvarchar(n)數(shù)據(jù)類型用來存放可以變長度的雙字節(jié)數(shù)據(jù)，取值范圍為1～4000，其存儲也類似于varchar舉例：字符型字符串“abcdABCD我們學(xué)習(xí)”作為統(tǒng)一字符型共__個字符,占__個字節(jié)。注：每個字符占2個字節(jié)。答案：12個字符，24個字節(jié) 請問nchar(10)/nchar(20),nvarchar(20)用來存儲該數(shù)據(jù)，能存儲么？分析：

nchar(10)僅存儲“abcdABCD我們”

nchar(20)存儲“abcdABCD我們學(xué)習(xí)”占40字節(jié)

nvarchar(20)存儲“abcdABCD我們學(xué)習(xí)”占24字節(jié)數(shù)字類型精確數(shù)字類型整數(shù)精確小數(shù)貨幣類型位數(shù)據(jù)類型近似數(shù)字類型FloatReal3.1.2精確數(shù)字類型1.

整數(shù)數(shù)據(jù)類型·bigint 8字節(jié)，范圍－263～263－1·int 4字節(jié)，表示范圍－231～231－1·smallint2字節(jié)，范圍－215～215－1·tinyint 1字節(jié)，范圍0～255思考：存儲人的年齡使用什么類型？2.

位數(shù)據(jù)類型·bit類型：取值0,1，Null說明：字符串值TRUE和FALSE可轉(zhuǎn)換為

bit

值：

TRUE將轉(zhuǎn)換為1，F(xiàn)ALSE將轉(zhuǎn)換為0。轉(zhuǎn)換為bit會將任何非零值升為1。思考：存儲學(xué)生的性別使用什么類型？·money,smallmoney ·money占位8字節(jié)，保留到小數(shù)點后4位。例如：$315749.1234 ·smallmoney占位4字節(jié)，保留到小數(shù)點后四位，取值范圍：－214748.3648～214748.36473

貨幣類型4精確小數(shù)類型·decimal(p,s),numeric(p,s)

精確小數(shù)數(shù)據(jù)由精度位和小數(shù)位組成。其中，p為精度值或?qū)ο竽軌蚩刂频臄?shù)字個數(shù)，s為小數(shù)位數(shù)。

p,s滿足：

0<=s<=p<=38默認(rèn)值為p=18,s=0例如：decimal(8,6)的取值范圍為： －99.999999～99.999999

思考：存儲學(xué)生成績（含1位小數(shù)）使用什么類型？3.1.3近似數(shù)字類型·real,floatreal精度在1～7之間，存儲范圍是

-3.40E+38~3.40E+38存儲長度為4個字節(jié)。 （其中，3.40E+38=3.40×1038）float精度在7～15之間，存儲范圍

-1.79E+308~1.79E+308存儲長度為8字節(jié)思考：存儲PI≈3.1415926使用什么類型？·datetime,smalldatetime

datetime范圍:1753-1-1~9999-12-31,該類型占用空間為8個字節(jié)。

smalldatetime:1900-1-1~2079-12-31,占4個字節(jié)的存儲空間3.1.4日期和時間數(shù)據(jù)類型 SQLServer2008在原來數(shù)據(jù)類型的基礎(chǔ)上又引入了4種新的數(shù)據(jù)類型：datetime2、dateoffset、date和time。舉例：日期：mdy：‘4/15/1996’,‘4-15-1996’ ymd:‘1996/4/15’,‘1996-4-15’日期和時間：‘1982-5-612:30:00’

二進(jìn)制數(shù)據(jù)類型表示的是位數(shù)據(jù)流，一般用于存儲二進(jìn)制的大對象，比如聲音、圖片、多媒體等。包括Binary（固定長度）和Varbinary（可變長度）2種，可用來輸入和顯示前綴為0x的十六進(jìn)制值。3.1.6二進(jìn)制字符串Timestamp數(shù)據(jù)類型公開數(shù)據(jù)庫中自動生成的唯一二進(jìn)制數(shù)字的數(shù)據(jù)類型。

Uniqueidentifier具有更新訂閱的合并復(fù)制和事務(wù)復(fù)制功能。

Cursor數(shù)據(jù)類型是變量或存儲過程OUTPUT參數(shù)的一種數(shù)據(jù)類型，這些參數(shù)包含對游標(biāo)的引用。3.1.7其他數(shù)據(jù)類型（略）Sql_variant類型用于存儲SQLServer2005支持的各種數(shù)據(jù)類型（不包括Text、Ntext、Image、Timestamp和Sql_variant）的值。

Table是一種特殊的數(shù)據(jù)類型，用于存儲結(jié)果集以進(jìn)行后續(xù)處理。

Xml數(shù)據(jù)類型可以在SQLServer數(shù)據(jù)庫中存儲XML文檔和片段。

NULL值不是0也不是空格，更不是填入字符串“NULL”，而是表示“不知道”、“不確定”或“暫時沒有數(shù)據(jù)”的意思。當(dāng)某一字段可以接受NULL值時，表示該字?jǐn)嗟闹悼梢圆惠斎?。例如：?dāng)不知道或暫時沒有數(shù)據(jù)填入表格時，可以使用___NULL_____.3.2用戶自定義數(shù)據(jù)類型1．創(chuàng)建用戶自定義數(shù)據(jù)類型

創(chuàng)建用戶自定義數(shù)據(jù)類型可以使用Transact-SQL語句。語法格式如下：

sp_addtype{新數(shù)據(jù)類型名},[,系統(tǒng)數(shù)據(jù)類型][,'null_type']【例3.1】創(chuàng)建一個用戶定義的數(shù)據(jù)類型CellPhone，其基于的系統(tǒng)數(shù)據(jù)類型是定長為11的字符，不允許空。Execsp_addtypeCellPhone,'Char(11)',‘NotNull'【例3.2】創(chuàng)建一個用戶定義的數(shù)據(jù)類型birthday，其基于的系統(tǒng)數(shù)據(jù)類型是DateTime，允許空。Execsp_addtypebirthday,datetime,'Null'2．刪除用戶自定義的數(shù)據(jù)類型

當(dāng)用戶自定義的數(shù)據(jù)類型不需要時，可刪除。刪除用戶自定義的數(shù)據(jù)類型的命令是：

sp_droptype{'數(shù)據(jù)類型名'}【例3.3】刪除用戶自定義數(shù)據(jù)類型CellPhone。

Execsp_droptype'CellPhone'SQLServer常見的數(shù)據(jù)類型類型數(shù)據(jù)類型長度字符串Char1～8000個字符，1個字符占1個字節(jié)Varchar1～8000個字符，1個字符占1個字節(jié)Text1～231?1個字符，1個字符占1個字節(jié)精確數(shù)字類型Bigint8字節(jié)，?263～263?1Int4字節(jié)，?231～231?1Smallint2字節(jié)，?215～215?1Tinyint1字節(jié)，0～255Bit0、1或NULLDecimal2～17字節(jié)，視精確度而定Numeric2～17字節(jié)，視精確度而定Money8字節(jié)Smallmoney4字節(jié)類型數(shù)據(jù)類型長度近似數(shù)字類型Float8字節(jié)Real4字節(jié)日期和時間數(shù)據(jù)類型Datetime8字節(jié)Smalldatetime4字節(jié)Date2008新類型Time2008新類型Unicode字符串Nchar1～4000個字符，1個字符2個字節(jié)Nvarchar1～4000個字符，1個字符2個字節(jié)Ntext1～230?1個字符，1個字符2個字節(jié)二進(jìn)制字符串Binary1～8000個字節(jié)Varbinary1～8000個字節(jié)，存儲時需另外增加2字節(jié)Image0～2×109字節(jié)3.3系統(tǒng)內(nèi)置函數(shù)3.3.1數(shù)學(xué)函數(shù)3.3.2字符串處理函數(shù)3.3.3系統(tǒng)函數(shù)3.3.4日期時間函數(shù)3.3.1數(shù)學(xué)函數(shù)

1.ABS函數(shù)

ABS函數(shù)用于返回數(shù)值表達(dá)式的絕對值，其語法格式如下：ABS（數(shù)值表達(dá)式）【例3.4】求數(shù)值表達(dá)式22-33-15的絕對值。

selectabs(22-33-15)

返回值為26。2.EXP函數(shù)

EXP函數(shù)用于返回求e的指定次冪，其語法格式如下：

EXP（浮點表達(dá)式）【例3.5】計算e的3次冪。

selectexp(3)

返回值為20.10000000000773.3.1數(shù)學(xué)函數(shù)

3.3.1數(shù)學(xué)函數(shù)3.CEILING函數(shù)

CEILING函數(shù)用于返回大于或等于數(shù)值表達(dá)式的最小整數(shù)，其語法格式如下：

CEILING（數(shù)值表達(dá)式）【例3.6】計算大于或等于35.76+42.11-36.5的最小整數(shù)。

selectceiling(35.76+42.11-36.5)返回值為42。3.3.1數(shù)學(xué)函數(shù)4.FLOOR函數(shù)

FLOOR函數(shù)用于返回小于或等于數(shù)值表達(dá)式的最大整數(shù)，其語法格式如下：

FLOOR（數(shù)值表達(dá)式）【例3.7】計算小于或等于35.76的最小整數(shù)。

selectfloor(35.76)返回值為35。3.3.1數(shù)學(xué)函數(shù)5.LOG函數(shù)

LOG函數(shù)用于返回表達(dá)式以e為底的對數(shù)，即自然對數(shù)，其語法格式如下：

LOG（浮點表達(dá)式）【例3.8】求20.1000000000077的自然對數(shù)。

Selectlog(20.1000000000077)返回值為3,與【例3.5】相對應(yīng)。3.3.1數(shù)學(xué)函數(shù)6.LOG10函數(shù)

LOG10函數(shù)用于返回表達(dá)式以10為底的對數(shù)，其語法格式如下：

LOG10（浮點表達(dá)式）【例3.9】求1000以10為底的對數(shù)。

Selectlog10(1000)返回值為3。3.3.1數(shù)學(xué)函數(shù)7.PI函數(shù)

PI函數(shù)用于返回圓周率，即∏的值，無參數(shù)，其語法格式如下： PI（）【例3.10】計算半徑為2CM的圓的周長。

Select2*PI()*2返回值為12.1000000000092。3.3.1數(shù)學(xué)函數(shù)8.POWER函數(shù)

POWER函數(shù)用于返回數(shù)值表達(dá)式1的數(shù)值表達(dá)式2次冪的結(jié)果，其語法格式如下：

POWER（數(shù)值表達(dá)式1，數(shù)值表達(dá)式2）【例3.11】計算9的三次方。

SelectPOWER(9,3)返回值為729。3.3.1數(shù)學(xué)函數(shù)9.ROUND函數(shù)

ROUND函數(shù)返回數(shù)值表達(dá)式指定精度的四舍五入的結(jié)果，返回的數(shù)值和原數(shù)值的總位數(shù)沒有變化，其語法格式如下：

ROUND（數(shù)值表達(dá)式[,長度[,操作方式]]）

長度：舍入精度。操作方式：默認(rèn)為0遵循四舍五入，指定其他整數(shù)值則直接截斷。

【例3.12】以幾個有代表性的數(shù)字為例，通過不同的長度及操作方式返回不同的值。selectround(337.555,2)返回值為337.560selectround(337.555,2,1)返回值為337.5503.3.1數(shù)學(xué)函數(shù)10.RAND函數(shù)

RAND函數(shù)用于返回0-1之間的隨機(jī)float值。整數(shù)表達(dá)式為種子，使用相同的種子產(chǎn)生隨機(jī)數(shù)相同，不指定種子則系統(tǒng)會隨機(jī)生成種子，其語法格式如下：

RAND（整數(shù)表達(dá)式）【例3.13】求0-100之間的隨機(jī)值。

selectRAND()*100執(zhí)行多次，每次返回的值為0-100之間的隨機(jī)值，值不一樣。

selectRAND(20)*100執(zhí)行多次，返回的值均為71.1000000000029。3.3.1數(shù)學(xué)函數(shù)11.SIGN函數(shù)

SIGN函數(shù)用于返回數(shù)值表達(dá)式的正號（+）、負(fù)號（—）或零，表達(dá)式為正返回+1，表達(dá)式為負(fù)返回-1，表達(dá)式為零返回0，其語法格式如下：

SIGN（數(shù)值表達(dá)式）【例3.14】求28+19-65表達(dá)式運算結(jié)果為正數(shù)、負(fù)數(shù)還是零。

selectSIGN(28+19-65)返回值為-13.3.1數(shù)學(xué)函數(shù)12.SQUARE函數(shù)

SQUARE函數(shù)用于返回數(shù)值表達(dá)式的平方，其語法格式如下：

SQUARE（數(shù)值表達(dá)式）【例3.15】求半徑為2CM的圓的面積。

selectPI()*SQUARE(2)返回值為12.10000000000923.3.1數(shù)學(xué)函數(shù)13.SQRT函數(shù)

SQRT函數(shù)用于返回數(shù)值表達(dá)式的平方根，其語法格式如下：

SQRT（數(shù)值表達(dá)式）【例3.16】求10的平方根。

selectSQRT(10)返回值為3.10000000000838。3.3.1數(shù)學(xué)函數(shù)14.ACOS函數(shù)

ACOS函數(shù)用于返回其余弦是所指定的數(shù)值表達(dá)式的弧度，求反余弦，其語法格式如下：

ACOS（浮點表達(dá)式）ASIN函數(shù)、ATAN函數(shù)與ACOS函數(shù)語法格式類似，不再一一詳解?！纠?.17】求0.2的反余弦、反正弦及反正切值。selectacos(0.2)返回值為1.10000000000457。selectasin(0.2)返回值為0.100000000000331。selectatan(0.2)返回值為0.100000000009881。3.3.1數(shù)學(xué)函數(shù)15.COS函數(shù)COS函數(shù)用于返回指定弧度的余弦值，其語法格式如下：

COS（浮點表達(dá)式）COT函數(shù)、SIN函數(shù)、TAN函數(shù)與COS函數(shù)語法格式類似，不再一一詳解?！纠?.18】求四分之∏弧度（即45度角）的余弦、余切、正弦及正切值。selectcos(PI()/4)返回值為0.100000000006548。selectcot(PI()/4)返回值為1。selectsin(PI()/4)返回值為0.100000000006547。selecttan(PI()/4)返回值為1。3.3.1數(shù)學(xué)函數(shù)15.COS函數(shù)COS函數(shù)用于返回指定弧度的余弦值，其語法格式如下：

COS（浮點表達(dá)式）COT函數(shù)、SIN函數(shù)、TAN函數(shù)與COS函數(shù)語法格式類似，不再一一詳解?！纠?.18】求四分之∏弧度（即45度角）的余弦、余切、正弦及正切值。selectcos(PI()/4)返回值為0.100000000006548。selectcot(PI()/4)返回值為1。selectsin(PI()/4)返回值為0.100000000006547。selecttan(PI()/4)返回值為1。6.LEFT函數(shù)

LEFT函數(shù)用于返回字符串表達(dá)式中從左側(cè)開始指定個數(shù)的字符，若指定值為負(fù)數(shù)或0，則返回空字符串；若指定值超過字符串長度，則返回整個字符串，其語法格式如下：LEFT(字符串表達(dá)式，整數(shù)表達(dá)式)3.3.2字符串處理函數(shù)【例3.27】返回字符串的前6個字符。

selectLEFT('cometomyparty',7)返回值為“cometo”7.RIGHT函數(shù)

RIGHT函數(shù)用于返回字符串中從右邊開始指定個數(shù)的字符，若指定值為負(fù)數(shù)或0，則返回空字符串；若指定值超過字符串長度，則返回整個字符串，其語法格式如下：

RIGHT(字符串表達(dá)式，整數(shù)表達(dá)式)3.3.2字符串處理函數(shù)【例3.28】返回字符串的后8個字符及后8個字符中的前兩個字符。

selectright('cometomyparty',8)

返回值為“myparty”

selectleft(right('cometomyparty',8),2)

返回值為“my”8.LEN函數(shù)

LEN函數(shù)用于返回字符串表達(dá)式字符的數(shù)量，不包含尾隨的空格，其語法格式如下：

LEN(字符串表達(dá)式)注意：一個漢字也作為一個字符處理，若其中的字符串表達(dá)式為純數(shù)字，可以不使用‘’括起來，當(dāng)字符串中含有其他字符時，一定要使用‘’括起來，否則會產(chǎn)生語法錯誤3.3.2字符串處理函數(shù)【例3.29】計算下列字符串的長度。selectLEN('cometomyparty')返回值為16。selectLEN('來參加我的聚會')返回值為7。selectLEN(201314)返回值為6。9.LOWER函數(shù)

LOWER函數(shù)用于將字符的大寫形式轉(zhuǎn)換成小寫的形式，其語法格式如下：

LOWER(字符串表達(dá)式)3.3.2字符串處理函數(shù)【例3.30】將字符串中的所有大寫字符轉(zhuǎn)換成相應(yīng)的小寫字符。

selectLOWER('CoMeTomYParTy')返回值為“cometomyparty”。10.UPPER函數(shù)

UPPER函數(shù)用于將字符的小寫形式轉(zhuǎn)換成大寫的形式，其語法格式如下：

UPPER(字符串表達(dá)式)3.3.2字符串處理函數(shù)【例3.31】將字符串中的所有小寫字符轉(zhuǎn)換成相應(yīng)的大寫字符。

SelectUPPER('CoMeTomYParTy')返回值為“COMETOMYPARTY”。11.LTRIM函數(shù)

LTRIM函數(shù)返回刪除了前導(dǎo)空格的字符串表達(dá)式，其語法格式如下：

LTRIM(字符串表達(dá)式)3.3.2字符串處理函數(shù)【例3.32】刪除下列字符串前面的空格。

SelectLTRIM('therearethreespaceintheleft')返回值為“therearethreespaceintheleft”。

SelectLTRIM('therearethreespaceanda"tab"intheleft')返回值為“therearethreespaceanda”tab“intheleft”。 LTRIM函數(shù)只能用于刪除空格鍵產(chǎn)生的空間，不能去除Tab鍵產(chǎn)生的空間。13.REPLACE函數(shù)

REPLACE函數(shù)用第三個字符串表達(dá)式替換第一個字符串表達(dá)式中與第二個字符串表達(dá)式相匹配的的部分，其語法格式如下：

REPLACE(字符串表達(dá)式1，字符串表達(dá)式2，字符串表達(dá)式3)3.3.2字符串處理函數(shù)【例3.34】將字符串中的“dog”替換成“cat”。SelectREPLACE('thisisadog,abigdog','dog','cat')返回值為“thisisacat,abigcat”。15.REVERSE函數(shù)

REVERSE函數(shù)用于返回原字符串的逆反形式，其語法格式如下：

REVERSE(字符串表達(dá)式)3.3.2字符串處理函數(shù)【例3.36】將字符串中“來參加我的聚會”反轉(zhuǎn)輸出。

SelectREVERSE('來參加我的聚會')返回值為“會聚的我加參來”。16.SPACE函數(shù)

SPACE函數(shù)用于返回由指定數(shù)目的空格組成的字符串，其語法格式如下：

SPACE(整數(shù)表達(dá)式)3.3.2字符串處理函數(shù)【例3.37】在以下兩個字符串的中間加入4個的空格。

Select'hello'+SPACE(4)+'world'返回值為“helloworld”。17.STR函數(shù)

STR函數(shù)用于將數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換成字符數(shù)據(jù)，其語法格式如下：STR（float型小數(shù)[,總長度[,小數(shù)點后保留的位數(shù)]]）【例3.38】將下列數(shù)字按指定的要求轉(zhuǎn)換成字符。selectstr(123.456)返回值為“123”。selectstr(123.456,3)返回值為“123”。selectstr(123.456,7,3)返回值為“123.456”。selectstr(123.456,7,1)返回值為“123.5”。se