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

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

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

,(unitsinstock-unitsonorder)--

計(jì)算存貨

,supplierID

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

*/USEnorthwindSELECTunitprice,(unitprice*1.1),productname

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

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

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

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

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

FROMOPENQUERY

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

selectday(GetDate())as當(dāng)前日selectday('/09/28')注意:常量字符串加‘’第43頁(yè)3.Month()功效:返回代表指定日期月份整數(shù)。語(yǔ)法:Month(date)舉例:selectMonth(GetDate())as當(dāng)前月selectMonth('/09/28')注意:常量字符串加‘’4.Year()功效:返回代表指定日期年份整數(shù)。語(yǔ)法:Year(date)舉例:selectYear(GetDate())as當(dāng)前年份selectYear('/09/28')第44頁(yè)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頁(yè)3數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用于對(duì)數(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頁(yè)1.Floor()功效:返回小于或等于所給數(shù)字表示式最大整數(shù)。語(yǔ)法:FLOOR(numeric_expression)舉例:SELECTFLOOR(123.45),FLOOR(-123.45),FLOOR($123.45)2.Round()功效:返回?cái)?shù)字表示式并四舍五入為指定長(zhǎng)度或精度。語(yǔ)法:ROUND(numeric_expression,length[,function])

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

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

運(yùn)算符類(lèi)別所包含運(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è)九、表示式各種符號(hào)和運(yùn)算符組合對(duì)單個(gè)數(shù)據(jù)進(jìn)行操作表示式結(jié)果數(shù)據(jù)類(lèi)型由表示式中組員決定USEnorthwindSELECTOrderID,ProductID,(UnitPrice*Quantity)asExtendedAmountFROM[OrderDetails]WHERE(UnitPrice*Quantity)>10000GO示例第55頁(yè)十、流控制語(yǔ)言元素語(yǔ)句級(jí)：流程控制語(yǔ)句是指那些用來(lái)控制程序執(zhí)行和流程分支命令，在SQLServer中，流程控制語(yǔ)句主要用來(lái)控制SQL語(yǔ)句、語(yǔ)句塊或者存放過(guò)程執(zhí)行流程。go、print、executeBEGIN…END塊IF…ELSE無(wú)條件轉(zhuǎn)移GOTOWHILE、break、continueReturnWaitforTry。。。catch行級(jí)CASE語(yǔ)句第56頁(yè)Go語(yǔ)句:是批處理結(jié)束語(yǔ)句。批處理是一起提交并作為一個(gè)組執(zhí)行若干sql語(yǔ)句Print語(yǔ)句：向客戶端返回用戶定義消息printgetdate()Execute：用于執(zhí)行transact-sql批中命令字符串、字符串或執(zhí)行以下模塊之一：系統(tǒng)存放過(guò)程、用戶定義存放過(guò)程、標(biāo)量值用戶定義函數(shù)或擴(kuò)展存放過(guò)程。Sqlserver擴(kuò)展了execute語(yǔ)句，以使其可用于向鏈接服務(wù)器發(fā)送傳遞命令Executesp_help第57頁(yè)第58頁(yè)IF…ELSE語(yǔ)句IF…ELSE語(yǔ)句是條件判斷語(yǔ)句，其中，ELSE子句是可選，最簡(jiǎn)單IF語(yǔ)句沒(méi)有ELSE子句部分。IF…ELSE語(yǔ)句用來(lái)判斷當(dāng)某一條件成立時(shí)執(zhí)行某段程序，條件不成立時(shí)執(zhí)行另一段程序。SQLServer允許嵌套使用IF…ELSE語(yǔ)句，而且嵌套層數(shù)沒(méi)有限制。IF…ELSE語(yǔ)句語(yǔ)法形式IFBoolean_expression

{sql_statement|statement_block}

[ELSE

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

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

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

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

sql_statement

|statement_block

END

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

label:改變執(zhí)行：

GOTOlabel第62頁(yè)無(wú)條件轉(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頁(yè)WHILE..CONTINUE..BREAK語(yǔ)句WHILE…CONTINUE…BREAK語(yǔ)句用于設(shè)置重復(fù)執(zhí)行SQL語(yǔ)句或語(yǔ)句塊條件。只要指定條件為真，就重復(fù)執(zhí)行語(yǔ)句。其中，CONTINUE語(yǔ)句能夠使程序跳過(guò)CONTINUE語(yǔ)句后面語(yǔ)句，回到WHILE循環(huán)第一行命令。BREAK語(yǔ)句則使程序完全跳出循環(huán)，結(jié)束WHILE語(yǔ)句執(zhí)行。WHILEBoolean_expression

sql_statement|statement_block

[BREAK]

sql_statement|statement_block

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

SET@x=@X+1

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

ENDGO執(zhí)行結(jié)果：x=1x=2x=3第65頁(yè)【例】利用循環(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頁(yè)【例】利用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頁(yè)Waitfor語(yǔ)句語(yǔ)法：waitfordelay時(shí)間|time時(shí)間Delay用來(lái)設(shè)定等候時(shí)間Time用來(lái)設(shè)定等候結(jié)束時(shí)間點(diǎn)時(shí)間必須是datetime類(lèi)型數(shù)據(jù)，如14:25:36，不能包含日期--系統(tǒng)等候2小時(shí)30分鐘后執(zhí)行Waitfordelay‘2:30:0’Select*fromstudent第68頁(yè)Try…catch類(lèi)似于c++和c#異常處理功效。用來(lái)處理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頁(yè)CASE函數(shù)CASE函數(shù)能夠計(jì)算多個(gè)條件式，并將其中一個(gè)符合條件結(jié)果表示式返回。CASE函數(shù)按照使用形式不一樣，能夠分為簡(jiǎn)單CASE函數(shù)和搜索CASE函數(shù)。第70頁(yè)CASE搜索函數(shù)：CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[

ELSEelse_result_expression]

END第71頁(yè)簡(jiǎn)單CASE函數(shù)：

CASEinput_expression

WHENwhen_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]

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

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

CASEr.TypeID

WHEN3THEN'能夠借x本書(shū)!' WHEN2THEN'能夠借y本書(shū)!' WHEN1THEN'能夠借z本書(shū)!' ELSE'無(wú)要求'ENDFROMReaderr,ReaderTypertWHEREr.TypeID=rt.TypeIDGO第73頁(yè)【例】取得系統(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)過(guò)DATEPART函數(shù)取得當(dāng)前時(shí)間中小時(shí)數(shù)，并保留在@sj變量中，然后結(jié)構(gòu)CASE函數(shù)，用于確定不一樣條件分支。將變量@sj分別與CASE函數(shù)中條件依次進(jìn)行比較，直到滿足條件時(shí)輸出結(jié)果。第74頁(yè)閱讀以下程序，功效？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頁(yè)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頁(yè)十一、游標(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)過(guò)在整個(gè)行集中修改或者甚至選取數(shù)據(jù)來(lái)取得所需要結(jié)果，需要逐一處理這些行。第77頁(yè)游標(biāo)使用需要不止一個(gè)語(yǔ)句。主要部分有：申明；打開(kāi)；使用或?qū)Ш剑魂P(guān)閉；釋放。第78頁(yè)申明游標(biāo)基本語(yǔ)法以下所表示：DECLARE<游標(biāo)名>CURSORFOR<SELECT語(yǔ)句>游標(biāo)名除了不需要“@”前綴外，與任何其它變量名一樣，游標(biāo)名必須服從SQLServer命名規(guī)范。SELECT語(yǔ)句能夠是任何有效返回結(jié)果集SELECT語(yǔ)句。要注意，一些結(jié)果集是不可更新。只申明游標(biāo)是不夠，我們還必須打開(kāi)它：OPEN<游標(biāo)名>會(huì)執(zhí)行作為FOR子句對(duì)象查詢，不過(guò)在我們能夠使用地方，依然沒(méi)有任何東西。必須進(jìn)行以下兩件事情：獲取（或FETCH）第一條統(tǒng)計(jì)；有必要話，循環(huán)，獲取其余統(tǒng)計(jì)。第79頁(yè)發(fā)出第一個(gè)FETCH——這是一個(gè)表明要檢索特定統(tǒng)計(jì)命令。另外，還必須說(shuō)明想要把值放置在哪一個(gè)變量中。FETCHNEXTFROM游標(biāo)名稱INTO變量名1,變量名2,變量名3,...現(xiàn)在，有了第一條統(tǒng)計(jì)，接下來(lái)，我們能夠執(zhí)行游標(biāo)集上操作：WHILE@@FETCH_STATUS=0 BEGIN SQL語(yǔ)句執(zhí)行過(guò)程...... FETCHNEXTFROM游標(biāo)名稱INTO變量名1,變量名2,變量名3,... END第80頁(yè)每當(dāng)提取一行時(shí)，就會(huì)更新@@FETCH_STATUS以通知我們提取進(jìn)行得怎樣。其可能值是：0Fetch語(yǔ)句成功——一切正常；-1Fetch語(yǔ)句失敗——找不到統(tǒng)計(jì)（還沒(méi)有抵達(dá)游標(biāo)末尾，但自打開(kāi)游標(biāo)以后，統(tǒng)計(jì)已經(jīng)被刪除），很快將在本章后面做深入討論；-2Fetch語(yǔ)句失敗——這一次是因?yàn)橐呀?jīng)超出了游標(biāo)中最終一條（或者第一條）統(tǒng)計(jì)，一樣，在本章后面將更詳細(xì)地討論。依據(jù)這里目標(biāo)可知，一旦退出循環(huán)，說(shuō)明我們已經(jīng)完成了對(duì)游標(biāo)使用，所以，關(guān)閉游標(biāo)：CLOSE游標(biāo)名稱然而，關(guān)閉游標(biāo)并沒(méi)有釋放與游標(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頁(yè)使用游標(biāo)--游標(biāo)普通格式：DECLARE

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

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