Ch8-1補(bǔ)充-09101數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用-語法與流程控制市公開課特等獎(jiǎng)市賽課微課一等獎(jiǎng)?wù)n

SQLServer編程—語法與流程控制第1頁Transact-SQL語言特點(diǎn)Transact-SQL語言是微軟對SQL語言擴(kuò)展。Transact-SQL語言是一個(gè)交互式查詢語言，含有功效強(qiáng)大、簡單易學(xué)特點(diǎn)。Transact-SQL語言有4個(gè)特點(diǎn)：一是一體化特點(diǎn)，集數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言、數(shù)據(jù)控制語言和附加語言元素為一體。二是兩種使用方式，交互式和嵌入到高級語言使用方式。前者適合于非數(shù)據(jù)庫專業(yè)人員使用，后者適合于數(shù)據(jù)庫專業(yè)開發(fā)人員使用。三是非過程化語言，只需要提出“干什么”，不需要指出“怎樣干”，語句操作過程由系統(tǒng)自動(dòng)完成。四是，類似于人思維習(xí)慣，輕易了解和掌握。第2頁Transact-SQL語言組成元素?cái)?shù)據(jù)控制語言語句數(shù)據(jù)定義語言語句數(shù)據(jù)操縱語言語句附加語言元素第3頁數(shù)據(jù)控制語言語句安全性管理是分布式數(shù)據(jù)庫系統(tǒng)主要特征。安全性管理就是確保數(shù)據(jù)庫中數(shù)據(jù)和操作不能被未授權(quán)用戶使用和執(zhí)行。數(shù)據(jù)控制語言語句就是用來進(jìn)行安全性管理，它能夠確定哪些用戶能夠查看或修改數(shù)據(jù)、或執(zhí)行數(shù)據(jù)庫中操作等，這些語句主要包含GRANT、DENY、REVOKE等語句。語句功效說明GRANT授予權(quán)限可把語句許可或?qū)ο笤S可權(quán)限授予其它用戶和角色REVOKE收回權(quán)限與GRANT功效相反，但不影響該用戶或角色從其它角色中作為組員繼承許可權(quán)限D(zhuǎn)ENY收回權(quán)限，并禁止從其它角色繼承許可權(quán)限功效與REVOKE相同，不一樣之處：除收回權(quán)限外，還禁止從其它角色繼承許可權(quán)限第4頁數(shù)據(jù)定義語言語句數(shù)據(jù)定義語言語句用來執(zhí)行數(shù)據(jù)庫任務(wù)、創(chuàng)建數(shù)據(jù)庫以及數(shù)據(jù)庫中各種對象，這些語句包含CREATE、ALTER、DROP等語句。在MicrosoftSQLServer系統(tǒng)中，數(shù)據(jù)庫及其對象包含數(shù)據(jù)庫、表、缺省、規(guī)則、視圖、觸發(fā)器、存放過程等。這些對象創(chuàng)建、修改和刪除等管理都能夠經(jīng)過使用CREATE、ALTER、DROP等語句來完成。語句功效說明CREATE創(chuàng)建數(shù)據(jù)庫或數(shù)據(jù)庫對象不一樣數(shù)據(jù)庫對象，其CREATE語句語法形式不一樣ALTER對數(shù)據(jù)庫或數(shù)據(jù)庫對象進(jìn)行修改不一樣數(shù)據(jù)庫對象，其ALTER語句語法形式不一樣DROP刪除數(shù)據(jù)庫或數(shù)據(jù)庫對象不一樣數(shù)據(jù)庫對象，其DROP語句語法形式不一樣第5頁數(shù)據(jù)操縱語言語句數(shù)據(jù)操縱語言語句，用來在數(shù)據(jù)庫中操縱各種對象，檢索和修改數(shù)據(jù)，這些語句包含SELECT、INSERT、UPDATE、DELETE等。這些語句都是操縱數(shù)據(jù)庫最慣用到語句。語句功效說明SELECT從表或視圖中檢索數(shù)據(jù)是使用最頻繁SQL語句之一INSERT將數(shù)據(jù)插入到表或視圖中

UPDATE修改表或視圖中數(shù)據(jù)既可修改表或視圖一行數(shù)據(jù)，也可修改一組或全部數(shù)據(jù)DELETE從表或視圖中刪除數(shù)據(jù)可依據(jù)條件刪除指定數(shù)據(jù)第6頁附加語言元素附加語言元素不是ISOSQL-3標(biāo)準(zhǔn)內(nèi)容，而是Transact-SQL語言為了編程而增加語言元素。這些語言元素包含變量、運(yùn)算符、函數(shù)、流程控制語言和注釋等等。下面就要介紹這些Transact-SQL語法要素第7頁一、批處理命令批處理是多條Transact-SQL語句組成集合。SQLServer將批處理語句進(jìn)行編譯形成一個(gè)可執(zhí)行單元，稱為執(zhí)行計(jì)劃。執(zhí)行計(jì)劃中語句一次執(zhí)行每一條語句，假如在編譯過程中出現(xiàn)語法錯(cuò)誤，那么批處理中全部語句均無法正常執(zhí)行。假如在運(yùn)行階段出現(xiàn)錯(cuò)誤時(shí)，普通都會中止當(dāng)前以及其后語句執(zhí)行，只有在少數(shù)情況下，如違反約束時(shí)，僅中止當(dāng)前犯錯(cuò)語句而繼續(xù)執(zhí)行其它語句。第8頁在建立批處理時(shí)，應(yīng)該遵照以下規(guī)則：不能在批處理中引用其它批處理中所定義變量。CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW語句不能在批處理中與其它語句組合使用。不允許在一個(gè)批處理中更改表結(jié)構(gòu)、并引用新字段。假如EXECUTE語句是批處理中第一句，則不需要EXECUTE關(guān)鍵字。假如EXECUTE語句不是批處理中第一條語句，則需要EXECUTE關(guān)鍵字。EXECUTE/EXEC用于執(zhí)行用戶定義函數(shù)、系統(tǒng)存放過程、用戶自定義存放過程或一個(gè)擴(kuò)展存放過程。在一個(gè)Transact-SQL批處理內(nèi)部，EXEC能控制一個(gè)字符串運(yùn)行一個(gè)完整批處理需要使用GO語句作為結(jié)束標(biāo)識。GO實(shí)際上并不是Transact-SQL語句，向SQLServer描述由Transact-SQL語句組成批處理第9頁二、注釋注釋是程序代碼中不執(zhí)行文本字符串。在SQLServer中，能夠使用兩種類型注釋字符：一個(gè)是ANSI標(biāo)準(zhǔn)注釋符“--”，它用于單行注釋；另一個(gè)是與C語言相同注釋符號，即“/**/”。第10頁注釋語句嵌于行內(nèi)注釋語句塊注釋語句SELECTproductname

,(unitsinstock-unitsonorder)--

計(jì)算存貨

,supplierID

FROMproductsGO/*這部分代碼檢索產(chǎn)品表全部行而且顯示單價(jià)，增加了10%單價(jià)和產(chǎn)品名稱。

*/USEnorthwindSELECTunitprice,(unitprice*1.1),productname

FROMproductsGO示例2示例1第11頁三、標(biāo)識符標(biāo)準(zhǔn)標(biāo)識符：第一個(gè)字符必須是字母（a~z或A~Z），第一個(gè)字符后能夠是數(shù)字、字母或各種符號特殊標(biāo)識符：當(dāng)標(biāo)識符第一個(gè)字符是符號時(shí)，代表它有特殊用處以@開頭標(biāo)識符代表局部變量或參數(shù)以@@開頭標(biāo)識符代表全局變量或參數(shù)以#開頭標(biāo)識符代表暫時(shí)表或存放過程以##開頭標(biāo)識符代表一個(gè)全局暫時(shí)對象限定標(biāo)識符當(dāng)對象名稱包含空格時(shí)當(dāng)保留關(guān)鍵字被用作對象名或?qū)ο蟛糠置謺r(shí)第12頁限定標(biāo)識符限定標(biāo)識符能夠用中括號表示括號標(biāo)識符（[]）

用雙引號表示引用標(biāo)識符（“”）SELECT*FROM[含空格表名]SELECT*FROM“含空格表名”第13頁標(biāo)識符命名標(biāo)準(zhǔn)使用短名字盡可能使用有意義名字使用簡單明了命名約定使用能夠區(qū)分出對象類型標(biāo)識符視圖存放過程保持對象名和用戶名一致性sales表和sales角色第14頁四、數(shù)據(jù)類型數(shù)字型：包含各種整數(shù)如int、tinyint、smallint和bigint日期型：包含datetime和smalldatetime字符型：包含char、nchar、varchar和nvarchar二進(jìn)制型：在存放與結(jié)構(gòu)上與字符型數(shù)據(jù)相同，包含binary和varbinary惟一標(biāo)識符（GUID）：代表一個(gè)全局特殊標(biāo)識符，是一個(gè)16位16進(jìn)制值SQL變量：代表SQLServer所支持各種數(shù)據(jù)類型圖像和文本：屬于大型二進(jìn)制對象結(jié)構(gòu)（BLOB），代表定長或變長數(shù)據(jù)類型表：代表一個(gè)表結(jié)構(gòu)，在SQLServer中，能夠把表保留在字段中游標(biāo)：用于存放過程編程中，也會和低層客戶接口同時(shí)使用。游標(biāo)從不用于數(shù)據(jù)定義語言（DDL）語句中用戶自定義數(shù)據(jù)類型：基于系統(tǒng)數(shù)據(jù)類型，由數(shù)據(jù)庫管理員生成第15頁五、常量常量,也稱為字面值或標(biāo)量值,是表示一個(gè)特定數(shù)據(jù)值符號,在運(yùn)行過程中其值不會變量。常量格式取決于它所表示值數(shù)據(jù)類型。包含：字符型常量，整型常量，實(shí)型常量，日期常量等。Example:

‘Thisis…’1212.3‘-09-09’第16頁六、變量變量對于一個(gè)語言來說是必不可少組成部分。變量有兩種形式，局部變量：用戶自己使用DECLARE語句定義，名字前要有一個(gè)標(biāo)識符“@”。全局變量：系統(tǒng)提供，引用全局變量時(shí)，名字前要有兩個(gè)標(biāo)識符“@@”。第17頁局部變量局部變量只含有局部作用范圍

只能在定義它語句、批處理或過程中使用用DECLARE@語句定義局部變量用SET或SELECT@語句給局部變量賦值【例】創(chuàng)建局部變量@var1、@var2，并賦值，然后輸出變量值。DECLARE@var1varchar(20),@var2varchar(40)SET@var1='中國'/*一個(gè)SET語句只能給一個(gè)變量賦值*/SET@var2=@var1+'是一個(gè)偉大國家'select@var1,@var2go第18頁【例】創(chuàng)建一個(gè)名為sex局部變量，并在SELECT語句中使用該局部變量查找表student中全部女同學(xué)學(xué)號、姓名。DECLARE@sexchar(2)SET@sex='女'SELECTSNO,SNAMEFROMstudentWHERESSEX=@sexGO【例】使用查詢給變量賦值。DECLARE@studentchar(8)SET@student=(SELECTSNAMEFROMstudentWHERESNO='15121')SELECT@studentGO用SELECT語句賦值語法格式：SELECT{@local_variable=expression}[,…n]第19頁全局變量全局變量：用兩個(gè)@標(biāo)識，由SQLServer提供，用戶不能創(chuàng)建全局變量設(shè)置變量提供SQLServer當(dāng)前各種特征和參數(shù)設(shè)置信息統(tǒng)計(jì)變量提供SQLServer自開啟后運(yùn)行信息系統(tǒng)變量提供最近對表操作信息第20頁一些慣用全局變量：@@CONNECTIONS：返回自上次開啟SQLServer以來連接或試圖連接次數(shù)。@@MAX_CONNECTIONS：返回SQLServer上允許同時(shí)用戶連接最大數(shù)。@@ERROR：返回最終執(zhí)行T-SQL語句錯(cuò)誤代碼。@@TRANCOUNT：返回當(dāng)前連接活動(dòng)事務(wù)數(shù)。@@VERSION：返回SQLServer當(dāng)前安裝日期、版本和處理器類型。@@SERVERNAME：返回運(yùn)行SQLServer當(dāng)?shù)胤?wù)器名稱。@@SERVICENAME：返回SQLServer正在其下運(yùn)行注冊表鍵名。若當(dāng)前實(shí)例為默認(rèn)實(shí)例，則返回MSSQLServer；若當(dāng)前實(shí)例是命名實(shí)例，則該函數(shù)返回實(shí)例名。第21頁@@SPID：返回當(dāng)前用戶進(jìn)程服務(wù)器進(jìn)程標(biāo)識符(ID)。@@ROWCOUNT：返回受上一語句影響行數(shù)。@@NESTLEVEL：返回當(dāng)前存放過程執(zhí)行嵌套層次(初始值為0)。@@LANGUAGE：返回當(dāng)前使用語言名。@@OPTIONS：返回當(dāng)前SET選項(xiàng)信息。@@CPU_BUSY：返回自上次開啟SQLServer以來CPU工作時(shí)間,單位為毫秒(基于系統(tǒng)計(jì)時(shí)器分辨率)。@@IDLE：返回SQLServer自上次開啟后閑置時(shí)間，單位為毫秒(基于系統(tǒng)計(jì)時(shí)器分辨率)。@@IDENTITY：返回最終插入標(biāo)識值。INSERTINTOjobs(job_desc,min_lvl,max_lvl)VALUES('Accountant',12,125)SELECT@@IDENTITYAS'Identity'

第22頁使用全局變量時(shí)應(yīng)該注意以下幾點(diǎn)：①全局變量不是由用戶程序定義，它們是在服務(wù)器級定義。②用戶只能使用預(yù)先定義全局變量。③引用全局變量時(shí)，必須以“@@”開頭。④局部變量名稱不能與全局變量名稱相同，不然會在應(yīng)用程序中出現(xiàn)不可預(yù)測結(jié)果。第23頁七、函數(shù)在Transact-SQL語言中，函數(shù)被用來執(zhí)行一些特殊運(yùn)算以支持SQLServer標(biāo)準(zhǔn)命令。Transact-SQL編程語言提供了三種函數(shù)：㈠行集函數(shù)：行集函數(shù)能夠在Transact-SQL語句中看成表引用。㈡聚合函數(shù)：聚合函數(shù)用于對一組值執(zhí)行計(jì)算并返回一個(gè)單一值。㈢標(biāo)量函數(shù)：標(biāo)量函數(shù)用于對傳遞給它一個(gè)或者多個(gè)參數(shù)值進(jìn)行處理和計(jì)算，并返回一個(gè)單一值。第24頁系統(tǒng)函數(shù)聚集函數(shù)標(biāo)量函數(shù)行集函數(shù)SELECT*

FROMOPENQUERY

(OracleSvr,'SELECTname,idFROMowner.titles')USEnorthwindSELECTAVG(unitprice)ASAvgPriceFROMproductsGOUSEnorthwindSELECTDB_NAME()AS'database‘GO第25頁SQLServer中最慣用幾個(gè)函數(shù)字符串函數(shù)日期和時(shí)間函數(shù)數(shù)學(xué)函數(shù)轉(zhuǎn)換函數(shù)系統(tǒng)函數(shù)聚合函數(shù)用戶自定義函數(shù)第26頁1字符串函數(shù)字符串函數(shù)能夠?qū)ΧM(jìn)制數(shù)據(jù)、字符串和表示式執(zhí)行不一樣運(yùn)算,大多數(shù)字符串函數(shù)只能用于char和varchar數(shù)據(jù)類型以及明確轉(zhuǎn)換成char和varchar數(shù)據(jù)類型,少數(shù)幾個(gè)字符串函數(shù)也能夠用于binary和varbinary數(shù)據(jù)類型。另外,一些字符串函數(shù)還能夠處理text、ntext、image數(shù)據(jù)類型數(shù)據(jù)。第27頁字符串函數(shù)分類:基本字符串函數(shù):UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。字符串查找函數(shù):CHARINDEX、PATINDEX。長度和分析函數(shù):DATALENGTH、RIGHT、SUBSTRING。轉(zhuǎn)換函數(shù):ASCH、CHAR、STR、SOUNDEX、DIFFERENCE。第28頁1.Upper功效:返回將小寫字符數(shù)據(jù)轉(zhuǎn)換為大寫字符表示式。語法:UPPER(character_expression)其中,character_expression由字符數(shù)據(jù)組成表示式。能夠是常量、變量，也能夠是字符或二進(jìn)制數(shù)據(jù)列,它返回類型為varchar。2.Lower功效:將大寫字符數(shù)據(jù)轉(zhuǎn)換為小寫字符表示式。語法:LOWER(character_expression)其中,character_expression是字符或二進(jìn)制數(shù)據(jù)表示式。character_expression能夠是常量、變量或列。第29頁Upper()和Lower()函數(shù)舉例USEpubsGOSELECTlower(SubString(title,1,20))AS書名小寫,upper(Substring(title,1,20))AS書名大寫,LOWER(UPPER(SUBSTRING(title,1,20)))AsLowerUpperFROMtitlesWHEREpricebetween11.00and20.00第30頁USEpubsGOSELECTRTRIM(au_lname)+','+SPACE(5)+LTRIM(au_fname)FROMauthorsORDERBYau_lname,au_fnameGO3.Space功效:返回由重復(fù)空格組成字符串。語法:SPACE(integer_expression)integer_expression是表示空格個(gè)數(shù)正整數(shù)。假如integer_expression為負(fù)，則返回空字符串。第31頁舉例:DECLARE@string_to_trimvarchar(60)SET@string_to_trim='前面有五個(gè)空格'SELECT'現(xiàn)在沒有空格:'+LTRIM(@string_to_trim)GO4.Ltrim()功效:刪除起始空格后返回字符表示式。語法:LTrim(character_expression)character_expression是字符或二進(jìn)制數(shù)據(jù)表示式。第32頁舉例:DECLARE@string_to_trimvarchar(60)SET@string_to_trim='后面有四個(gè)空格'SELECT'現(xiàn)在沒有空格:'+CHAR(13)+RTRIM(@string_to_trim)GO5.RTrim功效:截?cái)嗳课搽S空格后返回一個(gè)字符串。語法:RTrim(character_expression)character_expression是字符或二進(jìn)制數(shù)據(jù)表示式。第33頁SELECTSTUFF('abcdef',2,3,'ijklmn')GO6.STUFF功效:刪除指定長度字符并在指定起始點(diǎn)插入另一組字符。語法:STUFF(character_expression,start,length,character_expression)character_expression由字符數(shù)據(jù)組成表示式Start是一個(gè)整型值,指定刪除和插入開始位置。Length是一個(gè)整數(shù)，指定要?jiǎng)h除字符數(shù)。第34頁7.REVERSE功效:返回字符表示式反轉(zhuǎn)。語法:REVERSE(character_expression)舉例:selectReverse('abcd')第35頁舉例:USEpubsGOSELECTlength=DATALENGTH(pub_name),pub_nameFROMpublishersORDERBYpub_nameGOselectdatalength('abcd')8.DATALENGTH功效:返回任何表示式所占字節(jié)數(shù)。語法:DATALENGTH(expression)expression是任何類型表示式。第36頁9.SubString功效:返回字符、binary、text或image表示式一部分。語法:SUBSTRING(expression,start,length)expression是字符串、二進(jìn)制字符串、text、image列或包含列表示式。不要使用包含聚合函數(shù)表示式。Start是一個(gè)整數(shù)，指定子串開始位置。Length是一個(gè)整數(shù)，指定子串長度(要返回字符數(shù)或字節(jié)數(shù)）。第37頁舉例:(1)在字符串上使用SUBSTRINGUSEpubsSELECTau_lname,SUBSTRING(au_fname,1,1)FROMauthorsORDERBYau_lname(2)在text、ntext和image數(shù)據(jù)上使用SUBSTRINGUSEpubsSELECTpub_id,SUBSTRING(logo,1,10)ASlogo,SUBSTRING(pr_info,1,10)ASpr_infoFROMpub_infoWHEREpub_id='1756'SELECTSUBSTRING('abcdefgh',1,5)第38頁10.STR功效:由數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換來字符數(shù)據(jù)。語法:STR(float_expression[,length[,decimal]])float_expression是帶小數(shù)點(diǎn)近似數(shù)字(float)數(shù)據(jù)類型表示式。不要在STR函數(shù)中將函數(shù)或子查詢用作float_expression。Length是總長度，包含小數(shù)點(diǎn)、符號、數(shù)字或空格。默認(rèn)值為10。Decimal是小數(shù)點(diǎn)右邊位數(shù)。舉例:(1).SELECTSTR(123.45,6,1)(2).SELECTSTR(123.45,2,2)注意:當(dāng)表示式超出指定長度時(shí)返回**第39頁11.LEFT功效:返回從字符串左邊開始指定個(gè)數(shù)字符。語法:LEFT(charexpression,intexpression)。Charexpression字符或二進(jìn)制數(shù)據(jù)表示式,能夠是常量、變量或列。intexpression是正整數(shù),假如為負(fù)，則返回空字符串。舉例:(1)selectleft('abc123',5)(2)USEpubsGOSELECTLEFT(title,5)FROMtitlesORDERBYtitle_idGO第40頁2日期和時(shí)間函數(shù)日期和時(shí)間函數(shù)用于對日期和時(shí)間數(shù)據(jù)進(jìn)行各種不一樣處理和運(yùn)算，并返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。在SQLServer中，日期和時(shí)間函數(shù)類型如表所表示。第41頁表日期和時(shí)間函數(shù)類型函數(shù)參數(shù)DATEADD(datepart,number,date)DATEDIFF(datepart,date1,date2)DATENAME(datepart,date)DATEPART(datepart,date)DAY(date)GETDATE（）MONTH(date)YEAR(date)第42頁1.GetDate()功效:返回當(dāng)前系統(tǒng)日期和時(shí)間。語法:GETDATE()舉例:selectGetDate()as當(dāng)前日期和時(shí)間2.Day()功效:返回代表指定日期天日期部分整數(shù)。語法:DAY(date)舉例:

selectday(GetDate())as當(dāng)前日selectday('/09/28')注意:常量字符串加‘’第43頁3.Month()功效:返回代表指定日期月份整數(shù)。語法:Month(date)舉例:selectMonth(GetDate())as當(dāng)前月selectMonth('/09/28')注意:常量字符串加‘’4.Year()功效:返回代表指定日期年份整數(shù)。語法:Year(date)舉例:selectYear(GetDate())as當(dāng)前年份selectYear('/09/28')第44頁5.Dateadd()功效:返回一個(gè)日期加上一個(gè)指定時(shí)間間隔數(shù)后時(shí)間SELECTdateadd(year,1,getdate())6.Datediff()功效:返回兩個(gè)指定日期間時(shí)間間隔Selectdatediff(y,’-1-1’,getdate())7.datename()功效:返回指定日期指定部分字符串形式表示Selectdatename(year,getdate())8.datepart()功效:返回指定日期指定部分整數(shù)形式表示第45頁3數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用于對數(shù)字表示式進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。數(shù)學(xué)函數(shù)能夠?qū)QLServer提供數(shù)字?jǐn)?shù)據(jù)（decimal、integer、float、real、money、smallmoney、smallint和tinyint）進(jìn)行處理。第46頁1.Floor()功效:返回小于或等于所給數(shù)字表示式最大整數(shù)。語法:FLOOR(numeric_expression)舉例:SELECTFLOOR(123.45),FLOOR(-123.45),FLOOR($123.45)2.Round()功效:返回?cái)?shù)字表示式并四舍五入為指定長度或精度。語法:ROUND(numeric_expression,length[,function])

舉例:SELECTROUND(123.9994,3),ROUND(123.9995,3)

第47頁3.power()功效:返回給定表示式乘指定次方值。語法:POWER(numeric_expression,y)舉例:selectpower(3,2)4.rand()功效:返回0到1之間隨機(jī)float值。語法:rand(seed)舉例:selectrand()第48頁4轉(zhuǎn)換函數(shù)普通情況下，SQLServer會自動(dòng)處理一些數(shù)據(jù)類型轉(zhuǎn)換。比如，假如比較char和datetime表示式、smallint和int表示式、或不一樣長度char表示式，SQLServer能夠?qū)⑺鼈冏詣?dòng)轉(zhuǎn)換，這種轉(zhuǎn)換被稱為隱性轉(zhuǎn)換。不過，無法由SQLServer自動(dòng)轉(zhuǎn)換或者是SQLServer自動(dòng)轉(zhuǎn)換結(jié)果不符合預(yù)期結(jié)果，就需要使用轉(zhuǎn)換函數(shù)做顯示轉(zhuǎn)換。轉(zhuǎn)換函數(shù)有兩個(gè)：CONVERT和CAST。第49頁CONVERT和CAST函數(shù)將某種數(shù)據(jù)類型表示式顯式轉(zhuǎn)換為另一個(gè)數(shù)據(jù)類型。CAST和CONVERT提供相同功效。語法CAST(expressionASdata_type)CONVERT(data_type[(length)],expression)第50頁舉例USEpubsSELECTtitle,ytd_salesFROMtitlesWHERECAST(ytd_salesASchar(20))LIKE'15%'ANDtype='trad_cook‘USEpubsGOSELECTSUBSTRING(title,1,30)ASTitle,ytd_salesFROMtitlesWHERECONVERT(char(20),ytd_sales)LIKE'3%'GO第51頁5聚合函數(shù)主要包含AVG,COUNT,MAX,MIN,SUM等。聚合函數(shù)對一組值執(zhí)行計(jì)算并返回單一值。除COUNT函數(shù)之外，聚合函數(shù)忽略空值。聚合函數(shù)經(jīng)常與SELECT語句GROUPBY子句一同使用。舉例USEpubsGO--AggregatefunctionsSELECTtype,SUM(price),SUM(advance)FROMtitlesWHEREtypeLIKE'%cook'GROUPBYtypeORDERBYtypeGO第52頁6用戶自定義函數(shù)內(nèi)置函數(shù)。用戶自定義函數(shù)可帶參數(shù)，也可不帶，但數(shù)據(jù)類型不能是時(shí)間戳(timestamp)、游標(biāo)(cursor)和表(table)。1.函數(shù)定義利用createfunction語句創(chuàng)建函數(shù)。2.函數(shù)調(diào)用函數(shù)調(diào)用形式:全部者名.函數(shù)名(實(shí)參1,…實(shí)參n)3.用戶函數(shù)刪除利用dropfunction語句進(jìn)行函數(shù)刪除。第53頁八、運(yùn)算符

運(yùn)算符類別所包含運(yùn)算符賦值運(yùn)算符=（賦值）算術(shù)運(yùn)算符+(加)、-（減）、*(乖)、/(除)、%（取模）按位運(yùn)算符&(位與)、|(位或)、^(位異或)字符串串聯(lián)運(yùn)算符（連接）比較運(yùn)算符=(等于)、>(大于)、>=(大于等于)、<(小于)、<=(小于等于)、<>(或!=不等于)、!<(大于)、!>(小于)邏輯運(yùn)算符all（全部）、and(與）、any（任意一個(gè)）、between(二者之間)、exists(存在)、in(在范圍內(nèi))、like(匹配)、not(非)、or(或)、some(任意一個(gè))一元運(yùn)算符、+（正）、-（負(fù)）、~（取反）第54頁九、表示式各種符號和運(yùn)算符組合對單個(gè)數(shù)據(jù)進(jìn)行操作表示式結(jié)果數(shù)據(jù)類型由表示式中組員決定USEnorthwindSELECTOrderID,ProductID,(UnitPrice*Quantity)asExtendedAmountFROM[OrderDetails]WHERE(UnitPrice*Quantity)>10000GO示例第55頁十、流控制語言元素語句級：流程控制語句是指那些用來控制程序執(zhí)行和流程分支命令，在SQLServer中，流程控制語句主要用來控制SQL語句、語句塊或者存放過程執(zhí)行流程。go、print、executeBEGIN…END塊IF…ELSE無條件轉(zhuǎn)移GOTOWHILE、break、continueReturnWaitforTry。。。catch行級CASE語句第56頁Go語句:是批處理結(jié)束語句。批處理是一起提交并作為一個(gè)組執(zhí)行若干sql語句Print語句：向客戶端返回用戶定義消息printgetdate()Execute：用于執(zhí)行transact-sql批中命令字符串、字符串或執(zhí)行以下模塊之一：系統(tǒng)存放過程、用戶定義存放過程、標(biāo)量值用戶定義函數(shù)或擴(kuò)展存放過程。Sqlserver擴(kuò)展了execute語句，以使其可用于向鏈接服務(wù)器發(fā)送傳遞命令Executesp_help第57頁第58頁IF…ELSE語句IF…ELSE語句是條件判斷語句，其中，ELSE子句是可選，最簡單IF語句沒有ELSE子句部分。IF…ELSE語句用來判斷當(dāng)某一條件成立時(shí)執(zhí)行某段程序，條件不成立時(shí)執(zhí)行另一段程序。SQLServer允許嵌套使用IF…ELSE語句，而且嵌套層數(shù)沒有限制。IF…ELSE語句語法形式IFBoolean_expression

{sql_statement|statement_block}

[ELSE

{sql_statement|statement_block}]第59頁例：下面示例顯示帶有語句塊IF條件。假如書平均價(jià)格不低于$15，那么就顯示文本：平均價(jià)格超出$15.USEpubsGOIF(SELECTAVG(price)FROMtitlesWHEREtype='mod_cook')<$15

BEGINPRINT‘以下是當(dāng)代烹調(diào)類書籍:'PRINT''SELECTSUBSTRING(title,1,35)ASTitleFROMtitlesWHEREtype='mod_cook'ENDELSE

PRINT'平均價(jià)格超出$15.'

第60頁IF…ELSE語句在實(shí)際程序中，IF…ELSE語句中不止包含一條語句，而是一組SQL語句。為了能夠一次執(zhí)行一組SQL語句，這時(shí)就需要使用BEGIN…END語句將多條語句封閉起來。其語法格式為：BEGIN

sql_statement

|statement_block

END

第61頁GOTO語句GOTO語句能夠使程序直接跳到指定標(biāo)有標(biāo)識符位置處繼續(xù)執(zhí)行，而位于GOTO語句和標(biāo)識符之間程序?qū)⒉粫粓?zhí)行。GOTO語句和標(biāo)識符能夠用在語句塊、批處理和存放過程中，標(biāo)識符能夠?yàn)閿?shù)字與字符組合，但必須以“：”結(jié)尾。GOTO語句語法形式定義標(biāo)簽：

label:改變執(zhí)行：

GOTOlabel第62頁無條件轉(zhuǎn)移（goto）--從sc表中取得sno為“15121”統(tǒng)計(jì)個(gè)數(shù)Declare@maxcounterint,@counterintSelect@maxcounter=count(*),@counter=1FromscWheresno=‘15121’Loop:If@counter<@maxcounterBeginselect@counterset@counter=@counter+1

gotoloopend第63頁WHILE..CONTINUE..BREAK語句WHILE…CONTINUE…BREAK語句用于設(shè)置重復(fù)執(zhí)行SQL語句或語句塊條件。只要指定條件為真，就重復(fù)執(zhí)行語句。其中，CONTINUE語句能夠使程序跳過CONTINUE語句后面語句，回到WHILE循環(huán)第一行命令。BREAK語句則使程序完全跳出循環(huán)，結(jié)束WHILE語句執(zhí)行。WHILEBoolean_expression

sql_statement|statement_block

[BREAK]

sql_statement|statement_block

[CONTINUE]第64頁例：一個(gè)小循環(huán)程序DECLARE@XintSET@X=0 WHILE@x<3BEGIN

SET@x=@X+1

PRINT'x='+convert(char(1),@x)--類型轉(zhuǎn)換函數(shù)convert

ENDGO執(zhí)行結(jié)果：x=1x=2x=3第65頁【例】利用循環(huán)計(jì)算1+2+3+…+99+100值。在查詢分析器中運(yùn)行以下命令：DECLARE@sumint,@iintSET@sum=0SET@i=1WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT@sum運(yùn)行結(jié)果為：5050。第66頁【例】利用WHILE循環(huán)計(jì)算當(dāng)前時(shí)間距離年1月1日之間天數(shù)。在查詢分析器中運(yùn)行以下命令：DECLARE@numint,@sjDATETIMESET@sj=GETDATE()PRINT'當(dāng)前時(shí)間為：'+CAST(@sjASCHAR(20))SET@num=0WHILE1=1BEGINSET@sj=DATEADD(day,1,@sj)SET@num=@num+1IFDATEPART(yy,@sj)=

BREAKENDPRINT'距離年1月1日還有'+CAST(@numASVARCHAR(10))+'天'第67頁Waitfor語句語法：waitfordelay時(shí)間|time時(shí)間Delay用來設(shè)定等候時(shí)間Time用來設(shè)定等候結(jié)束時(shí)間點(diǎn)時(shí)間必須是datetime類型數(shù)據(jù)，如14:25:36，不能包含日期--系統(tǒng)等候2小時(shí)30分鐘后執(zhí)行Waitfordelay‘2:30:0’Select*fromstudent第68頁Try…catch類似于c++和c#異常處理功效。用來處理transact-sql代碼中錯(cuò)誤。UsestudbGoBegintrydeclare@erintset@er=0select3/@er--產(chǎn)生除數(shù)為0錯(cuò)誤EndtryBegincatchselecterror_number()asenumber,error_severity()aseseverity,error_procedure()asepro,error_state()asestate,error_line()aseline,error_message()asemessEndcatchgo第69頁CASE函數(shù)CASE函數(shù)能夠計(jì)算多個(gè)條件式，并將其中一個(gè)符合條件結(jié)果表示式返回。CASE函數(shù)按照使用形式不一樣，能夠分為簡單CASE函數(shù)和搜索CASE函數(shù)。第70頁CASE搜索函數(shù)：CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[

ELSEelse_result_expression]

END第71頁簡單CASE函數(shù)：

CASEinput_expression

WHENwhen_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]

END【例】依據(jù)系統(tǒng)時(shí)間判斷當(dāng)前日期所對應(yīng)星期值而且輸出結(jié)果。在查詢分析器中運(yùn)行以下命令：DECLARE@dtDATETIMESET@dt=DATEPART(w,GETDATE())SELECTCASE@dtWHEN1THEN'星期天' WHEN2THEN'星期一' WHEN3THEN'星期二' WHEN4THEN'星期三' WHEN5THEN'星期四' WHEN6THEN'星期五' WHEN7THEN'星期六'

END該實(shí)例中，首先經(jīng)過DATEPART函數(shù)取得當(dāng)前時(shí)間所對應(yīng)星期數(shù)，范圍為1至7，其中1代表星期天，7代表星期六，結(jié)構(gòu)一個(gè)條件分支。然后將系統(tǒng)星期數(shù)值經(jīng)過CASE函數(shù)轉(zhuǎn)換為對應(yīng)字符串信息并顯示結(jié)果。第72頁例：顯示各讀者可借書數(shù)量。USELibraryGOSELECTRname,rt.TypenameAS類型,限借閱量=

CASEr.TypeID

WHEN3THEN'能夠借x本書!' WHEN2THEN'能夠借y本書!' WHEN1THEN'能夠借z本書!' ELSE'無要求'ENDFROMReaderr,ReaderTypertWHEREr.TypeID=rt.TypeIDGO第73頁【例】取得系統(tǒng)時(shí)間，而且判斷當(dāng)前時(shí)間在一天中所處時(shí)間段，而且在查詢分析器中輸出提醒信息。在查詢分析器中運(yùn)行以下命令：DECLARE@sjDATETIMESET@sj=DATEPART(hh,GETDATE())SELECTCASEWHEN@sj>=20and@sj<24THEN'晚上'WHEN@sj>=14and@sj<20THEN'下午'WHEN@sj>=12and@sj<14THEN'中午'WHEN@sj>=10and@sj<12THEN'早晨'WHEN@sj>=0and@sj<10THEN'臨晨'END本例中首先經(jīng)過DATEPART函數(shù)取得當(dāng)前時(shí)間中小時(shí)數(shù)，并保留在@sj變量中，然后結(jié)構(gòu)CASE函數(shù)，用于確定不一樣條件分支。將變量@sj分別與CASE函數(shù)中條件依次進(jìn)行比較，直到滿足條件時(shí)輸出結(jié)果。第74頁閱讀以下程序，功效？DECLARE@ntinyintSET@n=5IF(@nBETWEEN4and6)BEGINWHILE(@n>0)BEGINSELECT@nAS'Number',CASEWHEN

(@n%2)=1

THEN‘ODD'

ELSE‘EVEN'

ENDAS'Type'SET@n=@n-1ENDENDELSEPRINT'NOANALYSIS‘GO第75頁CASE派生函數(shù)COALESCE

返回表示式列表中第一個(gè)非NULL表示式NULLIF

假如兩個(gè)表示式相等返回NULL，不然返回第一個(gè)表示式ISNULL假如第一個(gè)表示式為非NULL，返回第一個(gè)表示式，不然返回第二個(gè)表示式COALECSE(表示式1，表示式2,…,表示式n)NULLIF(表示式1，表示式2)ISNULL(表示式1，表示式2)第76頁十一、游標(biāo)使用什么是游標(biāo)游標(biāo)是取用一組數(shù)據(jù)并能夠一次與一個(gè)單獨(dú)統(tǒng)計(jì)進(jìn)行交互方法。SQL是面向集合，行集是由SQL產(chǎn)生或處理多條統(tǒng)計(jì)集合。有時(shí)，確實(shí)不能經(jīng)過在整個(gè)行集中修改或者甚至選取數(shù)據(jù)來取得所需要結(jié)果，需要逐一處理這些行。第77頁游標(biāo)使用需要不止一個(gè)語句。主要部分有：申明；打開；使用或?qū)Ш?；關(guān)閉；釋放。第78頁申明游標(biāo)基本語法以下所表示：DECLARE<游標(biāo)名>CURSORFOR<SELECT語句>游標(biāo)名除了不需要“@”前綴外，與任何其它變量名一樣，游標(biāo)名必須服從SQLServer命名規(guī)范。SELECT語句能夠是任何有效返回結(jié)果集SELECT語句。要注意，一些結(jié)果集是不可更新。只申明游標(biāo)是不夠，我們還必須打開它：OPEN<游標(biāo)名>會執(zhí)行作為FOR子句對象查詢，不過在我們能夠使用地方，依然沒有任何東西。必須進(jìn)行以下兩件事情：獲?。ɑ騀ETCH）第一條統(tǒng)計(jì)；有必要話，循環(huán)，獲取其余統(tǒng)計(jì)。第79頁發(fā)出第一個(gè)FETCH——這是一個(gè)表明要檢索特定統(tǒng)計(jì)命令。另外，還必須說明想要把值放置在哪一個(gè)變量中。FETCHNEXTFROM游標(biāo)名稱INTO變量名1,變量名2,變量名3,...現(xiàn)在，有了第一條統(tǒng)計(jì)，接下來，我們能夠執(zhí)行游標(biāo)集上操作：WHILE@@FETCH_STATUS=0 BEGIN SQL語句執(zhí)行過程...... FETCHNEXTFROM游標(biāo)名稱INTO變量名1,變量名2,變量名3,... END第80頁每當(dāng)提取一行時(shí)，就會更新@@FETCH_STATUS以通知我們提取進(jìn)行得怎樣。其可能值是：0Fetch語句成功——一切正常；-1Fetch語句失敗——找不到統(tǒng)計(jì)（還沒有抵達(dá)游標(biāo)末尾，但自打開游標(biāo)以后，統(tǒng)計(jì)已經(jīng)被刪除），很快將在本章后面做深入討論；-2Fetch語句失敗——這一次是因?yàn)橐呀?jīng)超出了游標(biāo)中最終一條（或者第一條）統(tǒng)計(jì)，一樣，在本章后面將更詳細(xì)地討論。依據(jù)這里目標(biāo)可知，一旦退出循環(huán)，說明我們已經(jīng)完成了對游標(biāo)使用，所以，關(guān)閉游標(biāo)：CLOSE游標(biāo)名稱然而，關(guān)閉游標(biāo)并沒有釋放與游標(biāo)相關(guān)聯(lián)內(nèi)存。關(guān)閉游標(biāo)只是釋放了與游標(biāo)相關(guān)聯(lián)鎖。為了確保完全釋放游標(biāo)使用全部資源，必須釋放游標(biāo)：DEALLOCATE游標(biāo)名稱第81頁使用游標(biāo)--游標(biāo)普通格式：DECLARE

游標(biāo)名稱CURSORFORSELECT字段,字段,字段,...FROM表名WHERE...OPEN

游標(biāo)名稱FETCHNEXTFROM游標(biāo)名稱INTO變量名,變量名,變量名,...WHILE@@FETCH_STATUS=0 BEGIN SQL語句執(zhí)行過程...... FETCHNEXTFROM游標(biāo)名稱INTO變量名,變量名,變量名,... ENDCLOSE游標(biāo)名稱DEALLOCATE游標(biāo)名稱第82頁--使用游標(biāo)打印輸出學(xué)生學(xué)號DECLARE@XHVARCHAR(10)DECLARESTUCURCURSORFORSELECTSNOFROMSTUDENTOPENSTUCURFETCHNEXTFROMSTUCURINTO@XHWHILE@@FETCH_STATUS=0BEGINPRINT@XHFETCHNEXTFROMSTUCURINTO@XHENDCLOSESTUCURDEALLOCATE