SQL第6章T-SQL編程課件

1第6章T-SQL編程6.1Transact-SQL簡(jiǎn)介6.2T-SQL中的批處理

6.3T-SQL中的注釋6.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換

6.5T-SQL運(yùn)算符6.6T-SQL中的常量和變量6.7T-SQL的流程控制6.8T-SQL中常用函數(shù)26.1Transact-SQL簡(jiǎn)介

結(jié)構(gòu)化查詢語言（SQL）是由美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì)（AmericanNationalStandardsInstitute，ANSI）和國(guó)際標(biāo)準(zhǔn)化組織（InternationalStandardsOrganization，ISO）定義的標(biāo)準(zhǔn)，而T-SQL是Microsoft公司對(duì)此標(biāo)準(zhǔn)的一個(gè)實(shí)現(xiàn)。T-SQL是在SQL上發(fā)展而來的，T-SQL在SQL的基礎(chǔ)上添加了流程控制，是SQL語言的擴(kuò)展。因此，SQL是幾乎所有的關(guān)系型數(shù)據(jù)庫(kù)都支持的語言，而T-SQL是MicrosoftSQLserver支持的語言。36.1Transact-SQL簡(jiǎn)介

在MicrosoftSQLserver2005系統(tǒng)中，T-SQL可以創(chuàng)建、維護(hù)、保護(hù)數(shù)據(jù)庫(kù)對(duì)象，并且可以操作對(duì)象中的數(shù)據(jù)，所以Transact-SQL語言是一種完整的語言。根據(jù)T-SQL語言的執(zhí)行功能特點(diǎn)，可以將T-SQL語言分為三種類型：數(shù)據(jù)定義語言：Create、Alter和Drop等語句數(shù)據(jù)操縱語言：Insert、Update、Delete和Select語句數(shù)據(jù)控制語言：Grant、Revoke、Deny等語句附加的語言元素：Transact-SQL語言的附加語言元素，包括變量、運(yùn)算符、函數(shù)、注釋和流程控制語句等。46.1Transact-SQL簡(jiǎn)介數(shù)據(jù)定義語言（DataDefinitionLanguage，DDL）數(shù)據(jù)定義語言DDL用于在SQL中創(chuàng)建或修改數(shù)據(jù)庫(kù)及數(shù)據(jù)庫(kù)對(duì)象，如創(chuàng)建表、視圖、存儲(chǔ)過程、函數(shù)等數(shù)據(jù)庫(kù)對(duì)象。在DDL中主要包括CREATE語句、ALTER語句和DROP語句，分別用于創(chuàng)建、修改、刪除數(shù)據(jù)庫(kù)及數(shù)據(jù)庫(kù)對(duì)象。56.1Transact-SQL簡(jiǎn)介數(shù)據(jù)操縱語言（DataManipulationLanguage，DML）數(shù)據(jù)操縱語言DML是指用來操縱數(shù)據(jù)庫(kù)中數(shù)據(jù)的語句，主要包括SELECT語句、INSERT語句、UPDATE語句、DELETE語句、CURSOR語句等。數(shù)據(jù)控制語言（DataControlLanguage，DCL）數(shù)據(jù)控制語言DCL是用來確保數(shù)據(jù)庫(kù)安全的語句，主要用于控制數(shù)據(jù)庫(kù)組件的存取許可、存取權(quán)限等權(quán)限管理問題。其主要包括GRANT語句、REVOKE語句、DENY。66.2T-SQL中的批處理

在SQLServer2005中，可以一次執(zhí)行多個(gè)T-SQL語句，這些多個(gè)T-SQL語句稱為“批”。SQLServer2005會(huì)將一批T-SQL語句當(dāng)成一個(gè)執(zhí)行單元，將其編譯后一次執(zhí)行，而不是將一個(gè)個(gè)T-SQL語句編譯后再一個(gè)個(gè)執(zhí)行。在SQLServer2005中同樣允許一次使用多個(gè)批，不同的批之間用“GO”來分隔。查詢編輯器會(huì)自動(dòng)根據(jù)GO指令來將T-SQL語句分為多個(gè)批來編譯執(zhí)行。如：usenorthwindselect*fromcategoriesselect*fromcustomersgo76.2T-SQL中的批處理注意：1、go并不是T-SQL語句，只有查詢編輯器才能識(shí)別并處理，編寫其他應(yīng)用程序就不能使用go指令。2、由于批與批之間是獨(dú)立的，所以，當(dāng)其中一個(gè)批出現(xiàn)錯(cuò)誤時(shí)，不會(huì)影響其他批的運(yùn)行。86.3T-SQL中的注釋

在T-SQL程序里加入注釋語句，可以增加程序的可讀性。SQLServer不會(huì)對(duì)注釋的內(nèi)容進(jìn)行編輯和執(zhí)行，在T-SQL中支持兩種注釋方式。

1、--注釋

--注釋的有效范圍只能到該行結(jié)束的地方，也就是說，從—開始，到本行結(jié)束為止，都可以是注釋的內(nèi)容，如果有多行注釋內(nèi)容的話，每一行的最前面都必須加上--。

2、/*¨¨*/注釋當(dāng)要進(jìn)行比較長(zhǎng)的注釋時(shí)，可以使用/*¨¨*/注釋，/*¨¨*/可以對(duì)多行語句進(jìn)行注釋，其有效范圍是從“/*”開始，到“*/”結(jié)束，中間可以跨越多行。96.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換在SQLServer中，無論是數(shù)據(jù)表的字段、常量、變量、表達(dá)式還是參數(shù)，都具有一個(gè)相對(duì)應(yīng)的數(shù)據(jù)類型。數(shù)據(jù)類型是一種屬性，用于指定對(duì)象可保存的數(shù)據(jù)的類型。1、使用CAST轉(zhuǎn)換數(shù)據(jù)類型當(dāng)要對(duì)不同類型的數(shù)據(jù)進(jìn)行運(yùn)算時(shí)，就必須將其轉(zhuǎn)換成相同的數(shù)據(jù)類型才能進(jìn)行運(yùn)算。在SQLServer里提供了兩個(gè)函數(shù)可以進(jìn)行數(shù)據(jù)類型的轉(zhuǎn)換，其中一個(gè)是CAST。106.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換語法格式如下：CAST（expressionASdata_type[(length)]）其中expression為任何有效的表達(dá)式，data_type為要轉(zhuǎn)換的數(shù)據(jù)類型，length為數(shù)據(jù)類型的長(zhǎng)度，一般只有在nchar、nvarchar、char、varchar、binary和varbinary這幾種數(shù)據(jù)類型才需要使用，是可選參數(shù)。116.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換例1：查看northwind數(shù)據(jù)庫(kù)products表中產(chǎn)品及單價(jià)，并在一列中顯示出來.程序代碼如下：SELECTproductname+N'的單價(jià)為：'+CAST(unitpriceasvarchar(10))+N'元'AS產(chǎn)品介紹FROMproducts126.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換[同步任務(wù)1]：查看northwind數(shù)據(jù)庫(kù)orders表中訂單號(hào)和訂單時(shí)間，并在一列中顯示出來SELECTCAST(orderidasvarchar(10))+N‘的時(shí)間為：’+CAST(orderdateasvarchar(20))AS訂單時(shí)間FROMorders136.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換2、使用CONVERT轉(zhuǎn)換數(shù)據(jù)類型CONVERT函數(shù)與CAST函數(shù)類似，作用也是轉(zhuǎn)換數(shù)據(jù)類型，其語法格式如下：CONVERT

（data_type[(length)]，expression[,style]）data_type為要轉(zhuǎn)換的數(shù)據(jù)類型length為數(shù)據(jù)類型的長(zhǎng)度expression為任何有效的表達(dá)式style是樣式,一般用于將datetime或smalldatetime數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)（nchar、nvarchar、char、varchar數(shù)據(jù)類型）的日期格式的樣式；或者用于將float、real、money或smallmoney數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)的字符串格式。如果style為NULL，則返回的結(jié)果也為NULL。146.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換例2：查看northwind數(shù)據(jù)庫(kù)orders表中訂單號(hào)、訂購(gòu)日期、到貨日期、發(fā)貨日期。selectorderidas訂單號(hào),convert(varchar(20),orderdate,1)as訂購(gòu)日期,convert(varchar(20),requireddate,102)as到貨日期,convert(varchar(20),shippeddate,103)as發(fā)貨日期fromorders156.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換3隱式數(shù)據(jù)類型轉(zhuǎn)換在進(jìn)行不同類型的數(shù)據(jù)運(yùn)算時(shí)，不一定都必須要使用CAST或CONVERT來進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換，在SQLServer里，系統(tǒng)會(huì)自動(dòng)將一些數(shù)據(jù)類型進(jìn)行轉(zhuǎn)換，這種轉(zhuǎn)換稱為“隱式轉(zhuǎn)換”。而用CAST或CONVERT轉(zhuǎn)換數(shù)據(jù)類型稱為“顯式轉(zhuǎn)換”。166.4T-SQL數(shù)據(jù)類型及轉(zhuǎn)換

例3：查看northwind數(shù)據(jù)庫(kù)products表中產(chǎn)品庫(kù)存量所值的資金SELECTproductname,unitprice*unitsinstockASjiyazijinFROMproducts[同步任務(wù)3]：查看northwind數(shù)據(jù)庫(kù)orderdetails表中每個(gè)訂單的總金額SELECTorderid,sum(unitprice*quantity)ASjineFROM[orderdetails]GROUPBYorderid176.5T-SQL運(yùn)算符算術(shù)運(yùn)算符邏輯運(yùn)算符字符串連接運(yùn)算符一元運(yùn)算符比較運(yùn)算符

2024/4/16181.算術(shù)運(yùn)算符2.賦值運(yùn)算符算術(shù)運(yùn)算符包括加（+）、減（—）、乘（*）、除（/）和取模（%，返回兩個(gè)數(shù)相除后的余數(shù)）。Transact-SQL中只有一個(gè)賦值運(yùn)算符（=）6.5T-SQL運(yùn)算符運(yùn)算符名稱語法+加Expression1+Expression2-減Expression1-Expression2*乘Expression1*Expression2/除Expression1/Expression2%取余Expression1%Expression23.位運(yùn)算符運(yùn)算符含義&（與運(yùn)算）兩個(gè)位為1時(shí)，結(jié)果為1，否則為0|（或運(yùn)算）兩個(gè)位只要有一個(gè)為1，結(jié)果為1，否則為0^（異或運(yùn)算）兩個(gè)位不同時(shí)，結(jié)果為1，否則為0~（非運(yùn)算）對(duì)1運(yùn)算結(jié)果為0，對(duì)0運(yùn)算結(jié)果為1【例】輸出對(duì)1與0按位求與運(yùn)算、或運(yùn)算和異或運(yùn)算的結(jié)果。DECLARE@abit,@bbit,@cbitSET@a=1&0SET@b=1|0SET@c=1^0PRINT@aPRINT@bPRINT@c6.5T-SQL運(yùn)算符2024/4/16204.比較運(yùn)算符比較運(yùn)算符表運(yùn)算符說明運(yùn)算符說明=等于<=小于或等于>大于<>或!=不等于<小于!>不大于>=大于或等于!<不小于6.5T-SQL運(yùn)算符2024/4/16215.邏輯運(yùn)算符邏輯運(yùn)算符表運(yùn)算符說明AND如果被測(cè)試的表達(dá)式的布爾值都為TRUE，則結(jié)果為TRUE；否則結(jié)果為FALSEOR如果被測(cè)試的表達(dá)式的布爾值有一個(gè)為TRUE，則結(jié)果為TRUE；只有全為FALSE時(shí)，結(jié)果才為FALSE。NOT對(duì)任何布爾運(yùn)算的結(jié)果取反。ALL如果一組的比較都為TRUE，則比較結(jié)果才為TRUEANY如果一組的比較中任何一個(gè)為TRUE，則結(jié)果為TRUESOME如果一組的比較中，有些比較結(jié)果為TRUE，則結(jié)果為TRUE6.5T-SQL運(yùn)算符2024/4/16226.字符串聯(lián)運(yùn)算符字符串聯(lián)運(yùn)算符只有一個(gè)，即加號(hào)“+”。利用字符串運(yùn)算符可以將多個(gè)字符串連接起來，構(gòu)成一個(gè)新的字符串。例如，執(zhí)行語句SELECT‘a(chǎn)bc’+’def’，其結(jié)果為abcdef。【例題9.7】查詢student表中學(xué)生的姓名和學(xué)號(hào)，要求兩者要作為一列結(jié)果輸出，學(xué)號(hào)和姓名間用字符“--”隔開。USESCOREGOSELECTstudent_id+'--'+student_nameFROMstudent6.5T-SQL運(yùn)算符2024/4/1623運(yùn)算符的優(yōu)先級(jí)別表優(yōu)先級(jí)順序運(yùn)算符1~（非運(yùn)算）2乘、除、求模運(yùn)算符（*、/、%；）3加減運(yùn)算符（+、-）、連接運(yùn)算符（+）、位與運(yùn)算符（&）4比較運(yùn)算符(=、>、<、>=、<=、<>、!=、!>、!<)5位或和位異或運(yùn)算符（|、^）6NOT7AND8OR、ALL、ANY、SOME9賦值運(yùn)算符（=）6.5T-SQL運(yùn)算符246.6T-SQL中的常量和變量1、常量

常量，也稱為文字值或標(biāo)量值，是一個(gè)代表特定值的符號(hào)，是一個(gè)不變的值。常量的格式取決于它所表示的值的數(shù)據(jù)類型。

字符串常量:‘a(chǎn)’，’I’mback’，’’

Unicode常量:N‘a(chǎn)’，N‘’

Bit常量:1，0

Datetime常量:‘August3,2006’，’2006-8-3’，’06/08/06’

Integer常量，decimal常量，float和real常量，money常量256.6T-SQL中的常量和變量2、變量

T-SQL中的變量可以分為全局變量和局部變量?jī)煞N，全局變量是以@@開頭命名的變量，局部變量是以@開頭命名的變量。全局變量是SQLSERVER系統(tǒng)提供并賦值的變量。用戶不能建立及修改全局變量。全局變量是一組特殊的函數(shù)，它們的名稱是以@@開始,且不需要任何參數(shù)。SQLSERVER提供33個(gè)全局變量。266.6T-SQL中的常量和變量@@error:上一條SQL語句報(bào)告的錯(cuò)誤號(hào)@@rowcount:上一條SQL語句處理的行數(shù)@@identity:最后插入的標(biāo)識(shí)值@@fetch_status:上一條游標(biāo)Fetch語句的狀態(tài)@@nestlevel:當(dāng)前存儲(chǔ)過程或觸發(fā)器的嵌套級(jí)別@@servername:本地服務(wù)器的名稱@@spid:當(dāng)前用戶進(jìn)程的會(huì)話id@@cpu_busy:SQLServer自上次啟動(dòng)后的工作時(shí)間例4：查看版本信息print@@version例5：查看select后的記錄集里的記錄數(shù)select*fromemployeesprint'一共查詢了'+CAST(@@rowcountasvarchar(5))+'條記錄'276.6T-SQL中的常量和變量3、局部變量

指在批處理或腳本中用來保存數(shù)據(jù)值的對(duì)象,局部變量名總是以@符號(hào)開始,必須符合標(biāo)識(shí)符命名規(guī)則。在使用一個(gè)局部變量前，必須使用DECLARE語句來聲明這個(gè)局部變量，指定其變量名和數(shù)據(jù)類型。

局部變量聲明的語法格式為：

DECLARE@局部變量名數(shù)據(jù)類型[,n]286.6T-SQL中的常量和變量局部變量賦值：

SET@局部變量名=表達(dá)式也可以使用select語句

select@局部變量名=表達(dá)式[,n]注意：聲明的變量名，其第一個(gè)字符必須是@必須指定變量的數(shù)據(jù)類型及長(zhǎng)度默認(rèn)情況下，系統(tǒng)將聲明后的變量設(shè)置為NULL296.6T-SQL中的常量和變量局部變量的作用域：局部變量的作用域從聲明它們的地方開始到聲明它們的批處理或存儲(chǔ)過程的結(jié)尾?！救蝿?wù)】使用@birthday存儲(chǔ)出生日期，實(shí)用@age存儲(chǔ)年齡，實(shí)用@name存儲(chǔ)姓名。同時(shí)為所聲明的@birthday變量賦值“1999-4-14”（使用set語句）；然后將Employees表中的職員的最大年齡賦值給變量@age（使用Select語句）。Declare@birthdaydatetimeDeclare@ageint,@namechar(8)Set@birthday='1994-4-14'UsenorthwindSelect@age=max(year(getdate())-year(birthdate))fromemployeesPrintN'------變量的輸出結(jié)果------'PrintN'@birthday的值'Print@birthdayPrintN'最大年齡'Print@agePrint@name6.6T-SQL中的常量和變量316.7T-SQL的流程控制1）BEGIN……END語句塊用于將多個(gè)T-SQL語句組合在一個(gè)語句塊中,其語法格式為:BEGIN

語句1

語句nEND326.7T-SQL的流程控制2)IF……ELSE語句語法格式為:IF條件表達(dá)式語句

[ELSE

語句]336.7T-SQL的流程控制例6：向products表插入一條記錄，如果插入成功則輸出‘插入記錄成功’，否則輸出‘出現(xiàn)錯(cuò)誤’INSERTproducts(productid,productname,discontinued)VALUES(112,‘computer',1)IF@@error<>0PRINTN'出現(xiàn)錯(cuò)誤'ELSEPRINTN‘插入記錄成功'GO346.7T-SQL的流程控制

例7：查看northwind數(shù)據(jù)庫(kù)products中產(chǎn)品名為’Chang’的單價(jià)是否低于20元，如果低于20元的話，查看其訂購(gòu)量。分析：1、最后及過程中需要查詢的內(nèi)容是什么？2、查詢用到哪些表？3、查詢過程中的條件是什么？4、查詢過程中需要定義哪些變量？356.7T-SQL的流程控制DECLARE@pricemoneyDECLARE@productidintDECLARE@sum_totalintSELECT@price=unitprice,@productid=productidFROMproductsWHEREproductname='Chang'IF@price<20

BEGIN

PRINTN'Chang的單價(jià)低于20元'

SELECT@sum_total=sum([orderdetails].quantity)

FROM[orderdetails]

WHERE[orderdetails].productid=@productid

PRINTN'其訂購(gòu)數(shù)量為：'+CAST(@sum_totalasvarchar(5))END366.7T-SQL的流程控制例8：查看northwind數(shù)據(jù)庫(kù)products表中產(chǎn)品名為’Chang’的單價(jià)是否低于8元，如果低于8元的話，查看其訂購(gòu)量，否則查看其庫(kù)存量376.7T-SQL的流程控制DECLARE@pricemoneyDECLARE@productidintDECLARE@countintSELECT@price=unitprice,@productid=productidFROMproductsWHEREproductname='Chang'IF@price<8

BEGIN

PRINTN'Chang的單價(jià)低于8元'

SELECT@count=sum([orderdetails].quantity)

FROM[orderdetails]

WHERE[orderdetails].productid=@productid

PRINTN'其訂購(gòu)數(shù)量為：'+CAST(@countasvarchar(5))END386.7T-SQL的流程控制ELSE

BEGIN

PRINTN'Chang的單價(jià)高于8元'

SELECT@count=sum(unitsinstock)

FROMproducts

WHEREproductid=@productid

PRINTN'其庫(kù)存量為：'+CAST(@countasvarchar(5))

END396.7T-SQL的流程控制3）CASE表達(dá)式CASE表達(dá)式可在程序中處理多個(gè)條件，完成不同的分支操作，CASE表達(dá)式的結(jié)果可應(yīng)用到SELECT或UPDATE等語句中，其語法格式為：

CASEWHEN條件1THEN表達(dá)式結(jié)果1WHEN條件2THEN表達(dá)式結(jié)果2……WHEN條件nTHEN表達(dá)式結(jié)果nEND406.7T-SQL的流程控制例9：對(duì)學(xué)生成績(jī)表查詢出所有學(xué)生的成績(jī)情況：要求凡成績(jī)?yōu)榭照咻敵觥救笨肌浚∮?0分的輸出【不及格】，60-69分間的輸出【及格】，70-79分間的輸出【中】，80-89分間的輸出【良】，90分以上的輸出【優(yōu)】。416.7T-SQL的流程控制SELECTstu_id學(xué)號(hào),course_id課程號(hào),score成績(jī),總評(píng)成績(jī)=CASEWHENscoreISNULLTHEN‘缺考’

WHENscore<60THEN‘不及格’

WHENscore>=60ANDscore<70THEN‘及格’

WHENscore>=70ANDscore<80THEN‘中’

WHENscore>=80ANDscore<90THEN‘良’

WHENscore>=90THEN‘優(yōu)’

ENDFROMstu_score426.7T-SQL的流程控制4)WHILE語句語法格式為:WHILE條件表達(dá)式

BEGIN

語句序列1[BREAK]

語句序列2[CONTINUE]

語句序列3END436.7T-SQL的流程控制例10：輸出northwind數(shù)據(jù)庫(kù)products表中產(chǎn)品編號(hào)為10以內(nèi)的產(chǎn)品名。DECLARE@idintDECLARE@productnamevarchar(40)SET@id=1WHILE@id<10BEGIN

SELECT@productname=productnameFROMproducts

WHEREproductid=@id

PRINT@productname

SET@id=@id+1END446.7T-SQL的流程控制例11：編程計(jì)算1~100之間所有能被3整除的數(shù)的個(gè)數(shù)和總和。DECLARE@sumINT,@iSMALLINT,@numsSMALLINTBEGINSET@sum=0SET@i=1SET@nums=0456.7T-SQL的流程控制WHILE(@i<=100)BEGINIF(@i%3=0)BEGINSET@sum=@sum+@iSET@nums=@nums+1ENDSET@i=@i+1ENDPRINT‘總和是’+STR(@sum)PRINT‘個(gè)數(shù)是’+STR(@nums)

END466.7T-SQL的流程控制5)Return語句

Return語句會(huì)終止目前T-SQL語句的執(zhí)行，從查詢或過程中無條件地退出來，并且可以返回一個(gè)整數(shù)值給調(diào)用該代碼的程序。與break不同，Return可以在任何時(shí)候從過程、批處理或語句塊中退出，而不是跳出某個(gè)循環(huán)或跳到位置。語法格式為:RETURN[integer_expression]476.8T-SQL中常用函數(shù)1、日期和時(shí)間函數(shù)

year(date)

返回表示指定日期的年份的整數(shù)monthday

getdate()

返回系統(tǒng)當(dāng)前日期和時(shí)間

dateadd(datepart,number,date)在指定的日期上再加一個(gè)時(shí)間間隔，并返回新的日期

datediff(datepart,startdate,enddate)返回跨兩個(gè)指定日期的日期邊界數(shù)和時(shí)間邊界數(shù)

datename(datepart,date)返回表示指定日期的指定日期部分的字符串

datepart(datepart,date)返回表示指定日期的指定日期部分的整數(shù)486.8T-SQL中常用函數(shù)例11：查看今天的年月日，并以格式化的形式顯示。select'今天是'+datename(YY,get