礦大數(shù)據(jù)庫應(yīng)用第2章

第2章Transact-SQL語言基礎(chǔ)李政偉

T-SQL語言簡介

數(shù)據(jù)類型

系統(tǒng)數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型

變量、運(yùn)算符、函數(shù)

局部變量和全局變量

運(yùn)算符

函數(shù)

批處理和流程控制

批和腳本

流程控制第2章Transact-SQL語言基礎(chǔ)21970年6月，埃德加?考特(EdgarFrankCodd)在CommunicationsofACM上發(fā)表了《大型共享數(shù)據(jù)庫數(shù)據(jù)的關(guān)系模型》一文。首次明確而清晰地為數(shù)據(jù)庫系統(tǒng)提出了一種嶄新的模型，即關(guān)系模型。1970年以后，考特繼續(xù)致力于完善與發(fā)展關(guān)系理論。1972年，他提出了關(guān)系代數(shù)和關(guān)系演算的概念，定義了關(guān)系的并、交、投影、選擇、連接等各種基本運(yùn)算，為SQL語言的形成和發(fā)展奠定了理論基礎(chǔ)。1979年，SQL(StructureQueryLanguage)在商業(yè)數(shù)據(jù)庫中成功得到了應(yīng)用。2.1T-SQL語言簡介31986年，ANSI正式發(fā)表了編號(hào)為X3.135-1986的SQL標(biāo)準(zhǔn)，并且在1987年獲得了ISO組織的認(rèn)可，被命名為ISO9075-1987。后來這個(gè)標(biāo)準(zhǔn)在1992、1999、2001、2003年等不斷地得到了擴(kuò)充和完善。從SQL語言的歷史來看，T-SQL語言與SQL語言并不完全等同。不同的數(shù)據(jù)庫供應(yīng)商一方面采納了SQL語言作為自己數(shù)據(jù)庫的操作語言，另一方面又對SQL語言進(jìn)行了不同程度的擴(kuò)展。這種擴(kuò)展的主要原因是不同的數(shù)據(jù)庫供應(yīng)商為了達(dá)到特殊目的和實(shí)現(xiàn)新的功能，不得不對標(biāo)準(zhǔn)的SQL語言進(jìn)行擴(kuò)展，而這些擴(kuò)展往往又是SQL標(biāo)準(zhǔn)的下一個(gè)版本的主要實(shí)踐來源。2.1T-SQL語言簡介4T-SQL語句分類T-SQL語句相關(guān)例子數(shù)據(jù)操作語句SELECT、INSERT、DELETE、UPDATE數(shù)據(jù)定義語句CREATETABLE、DROPTABLE、ALTERTABLECREATEVIEW、DROPVIEWCREATEINDEX、DROPINDEXCREATEPROCEDURE、ALTERPROCEDURE、DROPPROCEDURECREATETRIGGER、ALTERTRIGGER、DROPTRIGGER數(shù)據(jù)控制語句GRANT、DENY、REVOKE附加的語言元素BEGINTRANSACTION/COMMIT、ROLLBACK、SETTRANSACTIONDECLARE、OPEN、FETCH、CLOSE、EXECUTE5SQL語句主要組成部分每條SQL語句均由一個(gè)動(dòng)詞（Verb）開始，該動(dòng)詞描述語句要產(chǎn)生的動(dòng)作。圖4-1中的動(dòng)詞SELECT

后緊接著一個(gè)或多個(gè)子句，子句中給出了被動(dòng)詞作用的數(shù)據(jù)或提供動(dòng)詞動(dòng)作的詳細(xì)信息。圖2-1SQLSERVER語句的結(jié)構(gòu)6SELECT語句是SQLServer中最基本和最重要的語句之一。SELECT語句是執(zhí)行全部命令的基礎(chǔ)。所謂查詢就是對SQLServer發(fā)出一個(gè)數(shù)據(jù)請求，數(shù)據(jù)查詢語句只是提出要“查詢什么”和“從什么地方查詢”，那“怎么查詢”則由數(shù)據(jù)庫管理系統(tǒng)DBMS來完成。SELECT語句的簡單用法：

SELECT<列名1,列名2,….,列名N>FROM<表或視圖>WHERE<查詢條件>T-SQL語句—數(shù)據(jù)查詢語句78標(biāo)準(zhǔn)工具欄9注釋程序代碼中不執(zhí)行的文本字符串，用于對代碼進(jìn)行說明或暫時(shí)禁用正在進(jìn)行診斷的部分語句。一般地，注釋主要描述程序名稱、作者名稱、變量說明、代碼更改日期、算法描述等。兩種注釋方式雙連字符(--)正斜杠星號(hào)字符對(/*…*/)10【例2-2】演示使用注釋11

數(shù)據(jù)類型：數(shù)據(jù)所代表信息的類型，它關(guān)系到系統(tǒng)在內(nèi)存或磁盤上開辟相應(yīng)的存儲(chǔ)空間。SQLServer2008提供了33種數(shù)據(jù)類型，分為數(shù)字?jǐn)?shù)據(jù)類型、字符數(shù)據(jù)類型、日期和時(shí)間數(shù)據(jù)類型、二進(jìn)制數(shù)據(jù)類型以及其他數(shù)據(jù)類型。需要使用數(shù)據(jù)類型的對象包括表中的列、視圖中的列、定義的局部變量、存儲(chǔ)過程中的參數(shù)、函數(shù)及存儲(chǔ)過程的返回值等。2.2數(shù)據(jù)類型12最常用的數(shù)據(jù)類型之一，主要用來存儲(chǔ)整數(shù)值，可直接進(jìn)行數(shù)據(jù)運(yùn)算。

int：4字節(jié)，取值范圍為-231（-2147483648）~231-1（2147483647）。

Smallint：2字節(jié)，取值范圍為-215（-32768）~215-1（32768）。

Tinyint：1字節(jié)，取值范圍為0~255。

Bigint：8字節(jié)，取值范圍為-263（-9223372036854775808）~263（9223372036854775807）。適用于存儲(chǔ)長度超過int范圍的整型數(shù)據(jù)。

Bit：1字節(jié)，兩種取值：0和1。一般用于保存用來表示邏輯值的數(shù)據(jù)。例如，是否會(huì)員，是否是新消息等。整型13用于存儲(chǔ)十進(jìn)制小數(shù)。

Real：取值范圍為-3.40E+38~3.40E+38（4字節(jié)）

。

Float[(n)]：n為用于存儲(chǔ)尾數(shù)的位數(shù)，n∈[1,53]，默認(rèn)值為53。范圍從-1.79E-308到1.79E+308，（4或8字節(jié)）。如不指定float的長度，會(huì)被存儲(chǔ)在8個(gè)字節(jié)中。當(dāng)指定長度為1~24間數(shù)值時(shí)，則實(shí)際上定義了一個(gè)real數(shù)據(jù)類型。如果1<=n<=24，則將n視為24。如果25<=n<=53，則將n視為53。浮點(diǎn)數(shù)據(jù)類型(Real,float)14Decimal[(p,s)]和Numeric[(p,s)]：兩者完全相同，可提供小數(shù)所需要的實(shí)際存儲(chǔ)空間，但也有限制，可用2到17個(gè)字節(jié)來存儲(chǔ)從-10^38+1到10^38-1之間的數(shù)值。精度存儲(chǔ)字節(jié)數(shù)1-9510-19920-281329-3817浮點(diǎn)數(shù)據(jù)類型(Decimal,numeric)注意：SQLSEVER沒有無符號(hào)類型。15主要用來存儲(chǔ)由字母、數(shù)字和符號(hào)組成的字符串。

Char[(n)]，固定長度，非Unicode字符數(shù)據(jù)。n的范圍為1至8,000，存儲(chǔ)大小是n個(gè)字節(jié)。

Varchar[(n|max)]

?？勺冮L度，非Unicode字符數(shù)據(jù)。n的范圍為1至8,000。max指示最大存儲(chǔ)大小是2^31-1個(gè)字節(jié)。存儲(chǔ)大小是輸入數(shù)據(jù)的實(shí)際長度加2個(gè)字節(jié)。

Nchar[(n)]

：n個(gè)字符的固定長度的Unicode字符數(shù)據(jù)。n值必須在1到4,000之間（含）。存儲(chǔ)大小為兩倍n字節(jié)。

Nvarchar[(n|max)]

：可變長度Unicode字符數(shù)據(jù)。n值在1到4,000之間（含）。max指示最大存儲(chǔ)大小為2^31-1字節(jié)。字符型16日期時(shí)間型

Date：用于存儲(chǔ)日期，范圍從0001年1月1日至9999年12月31日，占3字節(jié)。不含具體的時(shí)間。

Datetime：用于存儲(chǔ)日期和時(shí)間。范圍從1753年1月1日零時(shí)起到9999年12月31日23時(shí)59分59秒。

Smalldatetime：與datetime類似，但其日期時(shí)間范圍較小，范圍從1900年1月1日到2079年6月6日。datetime2[(fractionalsecondsprecision)]

：與datetime類似，不同之處是datetime2秒的小數(shù)部分精度更高，存儲(chǔ)范圍更大：0001年1月1日至9999年12月31日，秒數(shù)可以精確到小數(shù)點(diǎn)后7位。17

datetimeoffset[(fractionalsecondsprecision)]：用于存儲(chǔ)與日期和時(shí)區(qū)相關(guān)的日期時(shí)間數(shù)據(jù)。存儲(chǔ)的日期時(shí)間數(shù)據(jù)，需要轉(zhuǎn)化成為UTC（CoordinatedUniversalTime）值的時(shí)間，即需要根據(jù)時(shí)區(qū)關(guān)系進(jìn)行換算。格式為YYYY-MM-DDhh:mm:ss[.nnnnnnn][+|_]hh:mm。占用的存儲(chǔ)空間因n的取值不同而不同，在8至10個(gè)字節(jié)之間。

time[(fractionalsecondprecision)]：專用于存儲(chǔ)時(shí)間的數(shù)據(jù)類型。格式為hh:mm:ss[.nnnnnnn]，長度為3~5字節(jié)。日期時(shí)間型18例2.1將字符串轉(zhuǎn)換為各種date和time數(shù)據(jù)類型。SELECT

CAST('2011-11-0908:35:29.1234567+12:15'AStime(7))AS'time'

,CAST('2011-11-0908:35:29.1234567+12:15'ASdate)AS'date'

,CAST('2011-11-0908:35:29.123'ASsmalldatetime)AS

'smalldatetime'

,CAST('2011-11-0908:35:29.123'ASdatetime)AS'datetime'

,CAST('2011-11-0908:35:29.1234567+12:15'ASdatetime2(7))AS

'datetime2'

,CAST('2011-11-0908:35:29.1234567+12:15'ASdatetimeoffset(7))AS'datetimeoffset';19包括money和smallmoney兩種。money用于存儲(chǔ)貨幣值，范圍為

-263~263，（8字節(jié)）。smallmoney范圍比money數(shù)據(jù)類型小，其存儲(chǔ)范圍為-2147483468到2147483467之間（4字節(jié)）。注意輸入money或smallmoney類型數(shù)據(jù)時(shí)，(不是)必須在位置前加貨幣單位符號(hào)。money和smallmoney

類型精確到它們所代表的貨幣單位的萬分之一。貨幣型20【例2-2】演示MONEY數(shù)據(jù)類型21文本和圖形數(shù)據(jù)類型

Text：長度可變的非

Unicode數(shù)據(jù)，最大長度為2^31-1。

Ntext：長度可變的Unicode數(shù)據(jù)，最大長度為2^30–1。

Image：長度可變的二進(jìn)制數(shù)據(jù)，從0到2^31-1(2,147,483,647)個(gè)字節(jié)。在MicrosoftSQLServer的未來版本中將刪除ntext、text和image數(shù)據(jù)類型。請避免在新開發(fā)工作中使用這些數(shù)據(jù)類型，并考慮修改當(dāng)前使用這些數(shù)據(jù)類型的應(yīng)用程序。請改用nvarchar(max)、varchar(max)和varbinary(max)。

22Binary[(n)]

，長度為n字節(jié)的固定長度二進(jìn)制數(shù)據(jù)，其中n取值從1到8,000。存儲(chǔ)大小為n字節(jié)。當(dāng)輸入的二進(jìn)制數(shù)據(jù)長度小于n時(shí)，余下部分填充0。

Varbinary[(n|max)]

，可變長度二進(jìn)制數(shù)據(jù)。n取值從1到8,000。max指示最大存儲(chǔ)大小為2^31-1字節(jié)。二進(jìn)制型23程序用數(shù)據(jù)類型hierarchyid：新增的一種用于存儲(chǔ)層次化結(jié)構(gòu)型數(shù)據(jù)的數(shù)據(jù)類型。采用hierarchyid來存儲(chǔ)，可以利用hierarchyid提供的函數(shù)，非常方便地實(shí)現(xiàn)數(shù)據(jù)的存儲(chǔ)和節(jié)點(diǎn)搜索。geometry：用于存儲(chǔ)平面幾何對象（平面球）的數(shù)據(jù)類型，如點(diǎn)、多邊形、曲線等11種幾何度量中的一種。Geography：用于存儲(chǔ)GPS等全球定位類型的地理數(shù)據(jù)（橢圓球），以緯度和經(jīng)度為度量來存儲(chǔ)。XML：用于存放整個(gè)XML文檔或者部分片段。Table：用于存儲(chǔ)對表或者視圖處理后的結(jié)果集的數(shù)據(jù)類型。這種數(shù)據(jù)類型使得變量可以存儲(chǔ)一個(gè)表，從而使函數(shù)或過程返回查詢結(jié)果更加方便、快捷。24程序用數(shù)據(jù)類型Cursor：一種變量或存儲(chǔ)過程的輸出參數(shù)使用的數(shù)據(jù)類型，也稱游標(biāo)。提供了一種逐行處理查詢數(shù)據(jù)的功能。用cursor定義的變量只能用于定義游標(biāo)和與游標(biāo)有關(guān)的語句，不能在表設(shè)計(jì)時(shí)使用。sql_variant：允許存儲(chǔ)多個(gè)不同類型數(shù)據(jù)值的數(shù)據(jù)類型，除了varchar(max)、nvarchar(max)、text、image、sql_variant、sql_variant(max)、xml、ntext、rowversion等之外的數(shù)據(jù)類型都可以存儲(chǔ)。Uniqueidentifier：用于存儲(chǔ)一個(gè)16字節(jié)長的二進(jìn)制數(shù)據(jù)類型，它是SQLServer根據(jù)計(jì)算機(jī)網(wǎng)絡(luò)適配器地址和CPU時(shí)鐘產(chǎn)生的唯一號(hào)碼而生成的全局唯一標(biāo)識(shí)符代碼（GloballyUniqueIdentifier，簡寫為GUID）。具有更新訂閱的合并復(fù)制和事務(wù)復(fù)制使用uniqueidentifier列來確保在表的多個(gè)副本中唯一地標(biāo)識(shí)行。25通過對象資源管理器創(chuàng)建在“對象資源管理器”中展開數(shù)據(jù)庫，右擊“可編程性->類型”，單擊“新建”→“用戶定義數(shù)據(jù)類型”菜單項(xiàng)，在彈出的“用戶定義數(shù)據(jù)類型屬性”對話框中，輸入新建數(shù)據(jù)類型的名稱，選擇系統(tǒng)數(shù)據(jù)類型；更改此數(shù)據(jù)類型可存儲(chǔ)的最大數(shù)據(jù)長度，選擇“允許空值”。用戶定義數(shù)據(jù)類型26利用系統(tǒng)存儲(chǔ)過程創(chuàng)建語法：sp_addtype

type_name,phystype[(length)|([precision,scale]),null|notnull|identity]type_name，用戶定義的數(shù)據(jù)類型的名字；Phystype，基于的系統(tǒng)數(shù)據(jù)類型，可包括長度、精度、標(biāo)度；null|notnull|identity，用戶自定義數(shù)據(jù)類型的性質(zhì)，分別為允許空值、不允許為空值、具有標(biāo)識(shí)列性質(zhì)。提醒：后續(xù)版本的MicrosoftSQLServer將刪除該功能。

用戶定義數(shù)據(jù)類型27用戶定義數(shù)據(jù)類型28右擊“用戶定義數(shù)據(jù)類型”，單擊“刪除”，再單擊“確定”。也可用系統(tǒng)存儲(chǔ)過程來查看、重命名、刪除用戶定義的數(shù)據(jù)類型。 sp_helptype_name查看用戶自定義數(shù)據(jù)類型的特征；sp_renametype_name給用戶自定義數(shù)據(jù)類型重新命名；sp_droptypetype_name刪除一個(gè)已經(jīng)定義且未被使用的用戶自定義數(shù)據(jù)類型。刪除用戶定義數(shù)據(jù)類型2930

變量

局部變量和全局變量常量

運(yùn)算符運(yùn)算符的類別運(yùn)算符的優(yōu)先級(jí)別

系統(tǒng)函數(shù)數(shù)學(xué)函數(shù)、字符串函數(shù)日期和時(shí)間函數(shù)轉(zhuǎn)換函數(shù)系統(tǒng)函數(shù)聚合函數(shù)2.3變量、運(yùn)算符、函數(shù)31局部變量：由用戶根據(jù)需要定義的變量，使用范圍只局限于某一個(gè)語句或過程體內(nèi)。主要用于保存臨時(shí)數(shù)據(jù)或由存儲(chǔ)過程返回的結(jié)果。作用域是其被聲明時(shí)所在批處理。必須先用DECLARE語句定義后才可以使用。局部變量32定義語法形式：

DECLARE{{{@local_variable[AS]data_type}|[=value]}|{@cursor_variable_nameCURSOR}}[,...n]局部變量不能是text，ntext

或image數(shù)據(jù)類型。聲明局部變量之后，會(huì)將其設(shè)為NULL，如設(shè)定新值，須用SELECT或者SET命令。其語法形式為：

SET{{@local_variable=expression}或

SELECT{@local_variable=expression}[,...n]其中，參數(shù)expression是任何有效的SQLServer表達(dá)式。局部變量33例2.4，聲明一個(gè)長度為9個(gè)字符的變量@city，并賦值“Welcometochangsha”。DECLARE@cityCHAR(9)SELECT@city='Welcometochangsha'SELECT@city建議：當(dāng)執(zhí)行簡單變量賦值時(shí)，使用SET語句；當(dāng)基于查詢進(jìn)行變量賦值時(shí)，使用SELECT語句。局部變量定義與賦值34由SQLSERVER預(yù)先定義并負(fù)責(zé)維護(hù)，主要用于保存系統(tǒng)的某些參數(shù)值和性能統(tǒng)計(jì)數(shù)據(jù)，作用域覆蓋所有程序。全局變量都以標(biāo)記符“@@”開頭。分類，一是與SQLServer連接有關(guān)的全局變量，如@@rowcount表示受最近一個(gè)語句影響的行數(shù)；二是關(guān)于系統(tǒng)內(nèi)部信息有關(guān)的全局變量，如@@version表示SQLServer的版本號(hào)。@@IDENTITY：返回最后一條Insert語句執(zhí)行后的標(biāo)識(shí)值。注意：局部變量的名稱不能與全局變量的名稱相同，否則會(huì)在應(yīng)用程序中出現(xiàn)不可預(yù)測的結(jié)果。全局變量（系統(tǒng)函數(shù)）35例2.7返回當(dāng)前用戶進(jìn)程的會(huì)話ID、登錄名和用戶名。

SELECT@@SPIDAS'ID',SYSTEM_USERAS'LoginName',USERAS'UserName'全局變量示例36常量常量：程序運(yùn)行中值不變的量，也稱為文字值或標(biāo)量值。常量格式取決于其所表示的值的數(shù)據(jù)類型。例，'Thisisabook.'、'May1,2006'、98321等。對于字符常量或時(shí)間日期型常量，需用單引號(hào)引起來。分類：字符串常量、二進(jìn)制常量、bit常量、datetime常量、integer常量、decimal常量、float和real常量、money常量、uniqueidentifier常量等。37主要有六大類：算術(shù)運(yùn)算符、賦值運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符以及字符串連接運(yùn)算符(參見P29)。運(yùn)算符的優(yōu)先級(jí)別運(yùn)算符38內(nèi)置函數(shù) MicrosoftSQLServer2008系統(tǒng)提供了許多內(nèi)置函數(shù)，這些函數(shù)可以完成許多特殊的操作，增強(qiáng)了系統(tǒng)的功能，提高了系統(tǒng)的易用性。

39數(shù)學(xué)函數(shù)40字符串函數(shù)41例2.8先使用SUBSTRING函數(shù)從字符串中獲取指定的子字符串，然后使用LOWER函數(shù)、UPPER函數(shù)對子字符串進(jìn)行轉(zhuǎn)換，且把UPPER函數(shù)嵌套在LOWER函數(shù)內(nèi)或把LOWER函數(shù)嵌套在UPPER內(nèi)。DECLARE@string_lower_uppervarchar(40)SET@string_lower_upper='InformationModelCreationIssues.'SELECTLOWER(SUBSTRING(@string_lower_upper,1,11))ASLower,

UPPER(SUBSTRING(@string_lower_upper,13,5))ASUpper,

LOWER(UPPER(SUBSTRING(@string_lower_upper,19,8)))AsLowerUpper,

UPPER(LOWER(SUBSTRING(@string_lower_upper,28,7)))AsUpperLower字符串函數(shù)42日期和時(shí)間函數(shù)用于對日期和時(shí)間數(shù)據(jù)進(jìn)行各種不同的處理和運(yùn)算，并返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。

43轉(zhuǎn)換函數(shù)SQLServer能夠自動(dòng)處理某些數(shù)據(jù)類型的轉(zhuǎn)換。例如，char和varchar、smallint和int，可以將自動(dòng)轉(zhuǎn)換（隱式轉(zhuǎn)換）。但無法自動(dòng)轉(zhuǎn)換或者自動(dòng)轉(zhuǎn)換的結(jié)果不符合預(yù)期結(jié)果的，就需要使用轉(zhuǎn)換函數(shù)做顯式轉(zhuǎn)換。44data_type

作為目標(biāo)的系統(tǒng)提供數(shù)據(jù)類型。length

nchar、nvarchar、char、varchar、binary或varbinary