1、1,Sql server 2005 程序設計與管理基礎,Sql server 2005 程序設計與管理基礎,第8章 T-SQL編程,2,第8章 T-SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL中常用函數,3,8.1 Transact-SQL簡介,結構化查詢語言（SQL）是由美國國家標準協會（American National Standards Institute，ANSI）和國際標準化組織

2、（International Standards Organization，ISO）定義的標準，而T-SQL是Microsoft公司對此標準的一個實現。 T-SQL是在SQL上發展而來的， T-SQL在SQL的基礎上添加了流程控制，是SQL語言的擴展。因此，SQL是幾乎所有的關系型數據庫都支持的語言，而T-SQL是Microsoft SQL server支持的語言。,4,8.1 Transact-SQL簡介,在Microsoft SQL server 2005系統中，T-SQL可以創建、維護、保護數據庫對象，并且可以操作對象中的數據，所以Transact-SQL語言是一種完整的語言。根據T-S

3、QL語言的執行功能特點，可以將T-SQL語言分為三種類型： 數據定義語言 數據操縱語言 數據控制語言,5,8.1 Transact-SQL簡介,數據定義語言（Data Definition Language，DDL）數據定義語言DDL用于在SQL中創建或修改數據庫及數據庫對象，如創建表、視圖、存儲過程、函數等數據庫對象。在DDL中主要包括CREATE語句、ALTER語句和DROP語句，分別用于創建、修改、刪除數據庫及數據庫對象。,6,8.1 Transact-SQL簡介,數據操縱語言（ Data Manipulation Language，DML）數據操縱語言DML是指用來操縱數據庫中數據的語

4、句，主要包括SELECT語句、INSERT語句、UPDATE語句、DELETE語句、CURSOR語句等。 數據控制語言（ Data Control Language，DCL）數據控制語言DCL是用來確保數據庫安全的語句，主要用于控制數據庫組件的存取許可、存取權限等權限管理問題。其主要包括GRANT語句、REVOKE語句、DENY。,7,第8章 T-SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL

5、中常用函數,8,8.2 T-SQL中的批處理,在SQL Server2005中，可以一次執行多個T-SQL語句，這些多個T-SQL語句稱為“批”。 SQL Server2005會將一批T-SQL語句當成一個執行單元，將其編譯后一次執行，而不是將一個個T-SQL語句編譯后再一個個執行。 在SQL Server2005中同樣允許一次使用多個批，不同的批之間用“GO”來分隔。查詢編輯器會自動根據GO指令來將T-SQL語句分為多個批來編譯執行。,9,8.2 T-SQL中的批處理,注意： 1、go并不是T-SQL語句，只有查詢編輯器才能識別并處理，編寫其他應用程序就不能使用go指令。 2、由于批與批之間

6、是獨立的，所以，當其中一個批出現錯誤時，不會影響其他批的運行。,10,第8章 T-SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL中常用函數,11,8.3 T-SQL中的注釋,在T-SQL程序里加入注釋語句，可以增加程序的可讀性。SQL Server不會對注釋的內容進行編輯和執行，在T-SQL中支持兩種注釋方式。 1、-注釋 -注釋的有效范圍只能到該行結束的地方，也就是說，從開始，到本行結束為止

7、，都可以是注釋的內容，如果有多行注釋內容的話，每一行的最前面都必須加上-。 2、/*/注釋 當要進行比較長的注釋時，可以使用/*/注釋， /*/可以對多行語句進行注釋，其有效范圍是從“/*”開始，到“*/”結束，中間可以跨越多行。,12,第8章 T-SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL中常用函數,13,8.4 T-SQL數據類型及轉換,在SQL Server中，無論是數據表的字段、常

8、量、變量、表達式還是參數，都具有一個相對應的數據類型。數據類型是一種屬性，用于指定對象可保存的數據的類型。 1、使用CAST轉換數據類型 當要對不同類型的數據進行運算時，就必須將其轉換成相同的數據類型才能進行運算。在SQL Server里提供了兩個函數可以進行數據類型的轉換，其中一個是CAST。,14,8.4 T-SQL數據類型及轉換,語法格式如下： CAST（expression AS data_type(length)） 其中expression為任何有效的表達式，data_type為要轉換的數據類型， length為數據類型的長度，一般只有在nchar、nvarchar、char、var

9、char、binary和varbinary這幾種數據類型才需要使用，是可選參數。,15,8.4 T-SQL數據類型及轉換,例1：查看northwind數據庫products表中產品及單價，并在一列中顯示出來. 程序代碼如下： SELECT productname+的單價為：+CAST(unitprice as varchar(10)+元 AS 產品介紹 FROM products,16,8.4 T-SQL數據類型及轉換,同步任務1：查看northwind數據庫orders表中訂單號和訂單時間，并在一列中顯示出來 SELECT CAST(orderid as varchar(10)+的時間為：+

10、CAST(orderdate as varchar(20) AS 訂單時間 FROM orders,17,8.4 T-SQL數據類型及轉換,2、使用CONVERT轉換數據類型 CONVERT函數與CAST函數類似，作用也是轉換數據類型，其語法格式如下： CONVERT （ data_type(length) ，expression,style） data_type為要轉換的數據類型 length為數據類型的長度 expression為任何有效的表達式 style是樣式,一般用于將datetime或smalldatetime數據轉換為字符數據（nchar、nvarchar、char、varcha

11、r數據類型）的日期格式的樣式；或者用于將float、real、money或smallmoney數據轉換為字符數據的字符串格式。如果style為NULL，則返回的結果也為NULL。,18,8.4 T-SQL數據類型及轉換,例2：查看northwind數據庫orders表中訂單號、 訂購日期、 到貨日期、 發貨日期。 select orderid as 訂單號, convert(varchar(20),orderdate,1)as 訂購日期, convert(varchar(20),requireddate,102)as 到貨日期, convert(varchar(20),shippeddate,

12、103)as 發貨日期 from orders 同步任務2：查看northwind數據庫products表中單價,19,8.4 T-SQL數據類型及轉換,3 隱式數據類型轉換 在進行不同類型的數據運算時，不一定都必須要使用CAST或CONVERT來進行數據類型轉換，在SQL Server里，系統會自動將一些數據類型進行轉換，這種轉換稱為“隱式轉換”。而用CAST或CONVERT轉換數據類型稱為“顯式轉換”。,20,8.4 T-SQL數據類型及轉換,例3：查看northwind數據庫products表中產品庫存量所值的資金 SELECT productname,unitprice*unitsin

13、stock AS jiyazijin FROM products 同步任務3：查看northwind數據庫order details表中每個訂單的總金額 SELECT orderid,sum(unitprice*quantity) AS jine FROM order details GROUP BY orderid,21,第8章 T-SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL中常用函數,

14、22,8.5 T-SQL運算符,算術運算符 邏輯運算符 字符串連接運算符 一元運算符 比較運算符,23,第8章 T-SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL中常用函數,24,8.6 T-SQL中的常量和變量,1、常量 常量，也稱為文字值或標量值，是一個代表特定值 的符號，是一個不變的值。常量的格式取決于它所表示 的值的數據類型。 字符串常量: a，Im back， Unicode常量:

15、N a，N Bit常量: 1，0 Datetime常量: August 3,2006，2006-8-3，06/08/06 Integer常量，decimal常量，float和real常量，money常量,25,8.6 T-SQL中的常量和變量,2、變量 T-SQL中的變量可以分為全局變量和局部變量兩 種，全局變量是以開頭命名的變量，局部變量是以 開頭命名的變量。 全局變量是SQL SERVER 系統提供并賦值的變量。 用戶不能建立及修改全局變量。全局變量是一組特殊的 函數，它們的名稱是以開始,且不需要任何參數。 SQL SERVER提供33個全局變量。,26,8.6 T-SQL中的常量和變量,

16、ERROR:返回最后執行的一條T-SQL語句的錯誤代碼 Identity:返回最后插入的標識值 ROWCOUNT:返回受到上一語句影響的行數 VERSION:返回當前的SQL Server安裝的版本信息 例4：查看版本信息 print version 例5：查看select后的記錄集里的記錄數 select * from employees print 一共查詢了+CAST(rowcount as varchar(5)+條記錄,27,8.6 T-SQL中的常量和變量,3、局部變量 指在批處理或腳本中用來保存數據值的對象,局部變量名總是以符號開始,必須符合標識符命名規則。在使用一個局部變量前，必

17、須使用DECLARE語句來聲明這個局部變量，指定其變量名和數據類型。 局部變量聲明的語法格式為： DECLARE 局部變量名 數據類型,n,28,8.6 T-SQL中的常量和變量,局部變量賦值： SET 局部變量名=表達式 也可以使用select語句 select 局部變量名=表達式,n,29,8.6 T-SQL中的常量和變量,注意： 聲明的變量名，其第一個字符必須是 必須指定變量的數據類型及長度 默認情況下，系統將聲明后的變量設置為NULL,30,8.6 T-SQL中的常量和變量,局部變量的作用域： 局部變量的作用域從聲明它們的地方開始到聲明它們的批處理或存儲過程的結尾。,31,第8章 T-

18、SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL中常用函數,32,8.7 T-SQL的流程控制,1）BEGINEND語句塊 用于將多個T-SQL語句組合在一個語句塊中,其語法格式為: BEGIN 語句1 語句n END,33,8.7 T-SQL的流程控制,2) IFELSE語句 語法格式為: IF 條件表達式 語句 ELSE 語句,34,8.7 T-SQL的流程控制,例6：向products表插

19、入一條記錄，如果插入成功則輸出插入記錄成功，否則輸出出現錯誤 INSERT products(productid,productname,discontinued) VALUES(112,computer,1) IF error0 PRINT 出現錯誤 ELSE PRINT 插入記錄成功 GO,35,8.7 T-SQL的流程控制,例7：查看northwind數據庫products中產品名為 Chang的單價是否低于20元，如果低于20元的話，查看其訂購量。,分析： 1、最后及過程中需要查詢的內容是什么？ 2、查詢用到哪些表？ 3、查詢過程中的條件是什么？ 4、查詢過程中需要定義哪些變量？,36

20、,8.7 T-SQL的流程控制,DECLARE price money DECLARE productid int DECLARE sum_total int SELECT price=unitprice,productid=productid FROM products WHERE productname=Chang IF price20 BEGIN PRINT Chang的單價低于20元 SELECT sum_total=sum(order details.quantity) FROM order details WHERE order ductid=producti

21、d PRINT 其訂購數量為：+CAST(sum_total as varchar(5) END,37,8.7 T-SQL的流程控制,例8：查看northwind數據庫products表中產品名為Chang的單價是否低于8元，如果低于8元的話，查看其訂購量，否則查看其庫存量,38,8.7 T-SQL的流程控制,DECLARE price money DECLARE productid int DECLARE count int SELECT price=unitprice,productid=productid FROM products WHERE productname=Chang IF

22、price8 BEGIN PRINT Chang的單價低于8元 SELECT count=sum(order details.quantity) FROM order details WHERE order ductid=productid PRINT 其訂購數量為：+CAST(count as varchar(5) END,39,8.7 T-SQL的流程控制,ELSE BEGIN PRINT Chang的單價高于8元 SELECT count=sum(unitsinstock) FROM products WHERE productid=productid PRINT

23、其庫存量為：+CAST(count as varchar(5) END,40,8.7 T-SQL的流程控制,3）CASE表達式 CASE表達式可在程序中處理多個條件，完成不同的分支操作，CASE表達式的結果可應用到SELECT或UPDAT等語句中，其語法格式為： CASE WHEN 條件1 THEN 表達式結果1 WHEN 條件2 THEN 表達式結果2 WHEN 條件n THEN 表達式結果n END,41,8.7 T-SQL的流程控制,例9：對學生成績表查詢出所有學生的成績情況： 要求凡成績為空者輸出【缺考】，小于60分的輸出【不及格】，60-69分間的輸出【及格】，70-79分間的輸出【

24、中】，80-89分間的輸出【良】，90分以上的輸出【優】。,42,8.7 T-SQL的流程控制,SELECT stu_id 學號,course_id 課程號, score 成績, 總評成績= CASE WHEN score IS NULL THEN 缺考 WHEN score=60 AND score=70 AND score=80 AND score=90 THEN 優 END FROM stu_score,43,8.7 T-SQL的流程控制,4) WHILE語句 語法格式為: WHILE 條件表達式 BEGIN 語句序列1 BREAK 語句序列2 CONTINUE 語句序列3 END,44

25、,8.7 T-SQL的流程控制,例10：輸出 northwind數據庫products表中產品編號為10以內的產品名。 DECLARE id int DECLARE productname varchar(40) SET id=1 WHILE id10 BEGIN SELECT productname=productname FROM products WHERE productid=id PRINT productname SET id=id+1 END,45,8.7 T-SQL的流程控制,例11：編程計算1100之間所有能被3整除的數的個數和總和。 DECLARE sum INT,i SM

26、ALLINT, nums SMALLINT BEGIN SET sum=0 SET i=1 SET nums=0,46,8.7 T-SQL的流程控制,WHILE (i=100) BEGIN IF (i%3=0) BEGIN SET sum=sum+i SET nums=nums+1 END SET i=i+1 END PRINT 總和是+STR(sum) PRINT 個數是+STR(nums) END,47,8.7 T-SQL的流程控制,5) Return語句 Return語句會終止目前T-SQL語句的執行，從查詢或過程中無條件地退出來，并且可以返回一個整數值給調用該代碼的程序。與break不

27、同，Return可以在任何時候從過程、批處理或語句塊中退出，而不是跳出某個循環或跳到位置。 語法格式為: RETURN integer_expression,48,第8章 T-SQL編程,8.1 Transact-SQL簡介 8.2 T-SQL中的批處理 8.3 T-SQL中的注釋 8.4 T-SQL數據類型及轉換 8.5 T-SQL運算符 8.6 T-SQL中的常量和變量 8.7 T-SQL的流程控制 8.8 T-SQL中常用函數,49,8.8 T-SQL中常用函數,1、日期和時間函數 year(date) 返回表示指定日期的年份的整數 month day getdate() 返回系統當前日期和時間 dateadd(datepart,number,da