SQL第6章T-SQL編程課件

1第6章T-SQL編程6.1Transact-SQL簡介6.2T-SQL中的批處理

6.3T-SQL中的注釋6.4T-SQL數據類型及轉換

6.5T-SQL運算符6.6T-SQL中的常量和變量6.7T-SQL的流程控制6.8T-SQL中常用函數26.1Transact-SQL簡介

結構化查詢語言（SQL）是由美國國家標準協(xié)會（AmericanNationalStandardsInstitute，ANSI）和國際標準化組織（InternationalStandardsOrganization，ISO）定義的標準，而T-SQL是Microsoft公司對此標準的一個實現。T-SQL是在SQL上發(fā)展而來的，T-SQL在SQL的基礎上添加了流程控制，是SQL語言的擴展。因此，SQL是幾乎所有的關系型數據庫都支持的語言，而T-SQL是MicrosoftSQLserver支持的語言。36.1Transact-SQL簡介

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

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

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

1、--注釋

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

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

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

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

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

常量，也稱為文字值或標量值，是一個代表特定值的符號，是一個不變的值。常量的格式取決于它所表示的值的數據類型。

字符串常量:‘a’，’I’mback’，’’

Unicode常量:N‘a’，N‘’

Bit常量:1，0

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

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

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

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

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

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

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

select@局部變量名=表達式[,n]注意：聲明的變量名，其第一個字符必須是@必須指定變量的數據類型及長度默認情況下，系統(tǒng)將聲明后的變量設置為NULL296.6T-SQL中的常量和變量局部變量的作用域：局部變量的作用域從聲明它們的地方開始到聲明它們的批處理或存儲過程的結尾?！救蝿铡渴褂聾birthday存儲出生日期，實用@age存儲年齡，實用@name存儲姓名。同時為所聲明的@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'------變量的輸出結果------'PrintN'@birthday的值'Print@birthdayPrintN'最大年齡'Print@agePrint@name6.6T-SQL中的常量和變量316.7T-SQL的流程控制1）BEGIN……END語句塊用于將多個T-SQL語句組合在一個語句塊中,其語法格式為:BEGIN

語句1

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

[ELSE

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

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

BEGIN

PRINTN'Chang的單價低于20元'

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

FROM[orderdetails]

WHERE[orderdetails].productid=@productid

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

BEGIN

PRINTN'Chang的單價低于8元'

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

FROM[orderdetails]

WHERE[orderdetails].productid=@productid

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

BEGIN

PRINTN'Chang的單價高于8元'

SELECT@count=sum(unitsinstock)

FROMproducts

WHEREproductid=@productid

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

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

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

WHENscore<60THEN‘不及格’

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

WHENscore>=70ANDscore<80THEN‘中’

WHENscore>=80ANDscore<90THEN‘良’

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

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

BEGIN

語句序列1[BREAK]

語句序列2[CONTINUE]

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

SELECT@productname=productnameFROMproducts

WHEREproductid=@id

PRINT@productname

SET@id=@id+1END446.7T-SQL的流程控制例11：編程計算1~100之間所有能被3整除的數的個數和總和。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‘個數是’+STR(@nums)

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

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

year(date)

返回表示指定日期的年份的整數monthday

getdate()

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

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

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

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

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