第9章 T-SQL程序設計

1、SQL Server實用簡明教程(第三版)第3章 T-SQL語言第1頁第第9章章 T-SQL程序設計程序設計 本章概述 本章的學習目標主要內(nèi)容SQL Server實用簡明教程(第三版)第3章 T-SQL語言第2頁概述概述 l1986年，美國國家標準化組織正式發(fā)表了編號為年，美國國家標準化組織正式發(fā)表了編號為X3.135-1986的的SQL標準，并且在標準，并且在1987年獲得了國年獲得了國際標準化組織（際標準化組織（ISO）組織的認可，被命名為）組織的認可，被命名為ISO9075-1987。l后來這個標準在后來這個標準在1992、1999、2001、2003年等不斷年等不斷地得到了擴充和完善。

2、地得到了擴充和完善。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第3頁Transact-SQL語言語言lTransact-SQL語言是微軟公司在語言是微軟公司在MS SQL Server系系統(tǒng)中使用的語言，是對統(tǒng)中使用的語言，是對SQL語言的一種擴展形式語言的一種擴展形式 (下文簡稱下文簡稱T-SQL語言語言)。lT-SQL語言是一種交互式查詢語言，具有功能強大、語言是一種交互式查詢語言，具有功能強大、簡單易學的特點。簡單易學的特點。l該語言既允許用戶直接查詢存儲在數(shù)據(jù)庫中的數(shù)據(jù)，該語言既允許用戶直接查詢存儲在數(shù)據(jù)庫中的數(shù)據(jù)，也可以把語句嵌入到某種高級程序設計語言中來使也可

3、以把語句嵌入到某種高級程序設計語言中來使用用 SQL Server實用簡明教程(第三版)第3章 T-SQL語言第4頁T-SQL語言語言lT-SQL語言有語言有4個特點：個特點：一體化：集一體化：集數(shù)據(jù)定義語言數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言數(shù)據(jù)操縱語言、數(shù)據(jù)數(shù)據(jù)控制語言控制語言、事務管理語言和附加語言元素事務管理語言和附加語言元素為一體為一體兩種使用方式：兩種使用方式：交互使用方式交互使用方式和和嵌入高級語言嵌入高級語言中中使用。使用。非過程化：只需要提出非過程化：只需要提出“干什么干什么”，不需要指出，不需要指出“如何干如何干”，語句的操作過程由系統(tǒng)自動完成。，語句的操作過程由系統(tǒng)自動完成。類似于

4、人的思維習慣，容易理解和掌握。類似于人的思維習慣，容易理解和掌握。 SQL Server實用簡明教程(第三版)第3章 T-SQL語言第5頁T-SQL語言的特點和執(zhí)行方式語言的特點和執(zhí)行方式 l根據(jù)根據(jù)T-SQL語言的功能特點，可以分為語言的功能特點，可以分為5種類型種類型數(shù)據(jù)定義語言數(shù)據(jù)定義語言數(shù)據(jù)操縱語言數(shù)據(jù)操縱語言數(shù)據(jù)控制語言數(shù)據(jù)控制語言事務管理語言事務管理語言附加的語言元素附加的語言元素lSQL Server 2005中，主要在中，主要在SSMS中執(zhí)行中執(zhí)行T-SQL語語言編寫的查詢語句。言編寫的查詢語句。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第6頁數(shù)據(jù)定義語言

5、數(shù)據(jù)定義語言 l數(shù)據(jù)定義語言數(shù)據(jù)定義語言(Data Description LanguageDDL )用于用于創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫和和數(shù)據(jù)庫對象數(shù)據(jù)庫對象，為數(shù)據(jù)庫操作提供，為數(shù)據(jù)庫操作提供對象。對象。例如，數(shù)據(jù)庫以及表、觸發(fā)器、存儲過程、視圖、例如，數(shù)據(jù)庫以及表、觸發(fā)器、存儲過程、視圖、索引、函數(shù)、類型、用戶等都是數(shù)據(jù)庫中的對象，索引、函數(shù)、類型、用戶等都是數(shù)據(jù)庫中的對象，都需要通過定義才能使用。都需要通過定義才能使用。l在在DDL中，主要的語句包括中，主要的語句包括CREATE語句、語句、ALTER語句、語句、DROP語句。語句。SQL Server實用簡明教程(第三版)第3章 T-SQL

6、語言第7頁lCREATE語句：用于創(chuàng)建數(shù)據(jù)庫以及數(shù)據(jù)庫中的對語句：用于創(chuàng)建數(shù)據(jù)庫以及數(shù)據(jù)庫中的對象，例在數(shù)據(jù)庫中創(chuàng)建表象，例在數(shù)據(jù)庫中創(chuàng)建表lALTER語句：用于語句：用于更改數(shù)據(jù)庫以及數(shù)據(jù)庫對象的結(jié)更改數(shù)據(jù)庫以及數(shù)據(jù)庫對象的結(jié)構(gòu)。構(gòu)。ALTER語句的對象必須已經(jīng)存在語句的對象必須已經(jīng)存在。例如，對于表對象來說，在表中增加一個新列、例如，對于表對象來說，在表中增加一個新列、刪除一個列等操作都屬于對表結(jié)構(gòu)的更改。刪除一個列等操作都屬于對表結(jié)構(gòu)的更改。lDROP語句：刪除數(shù)據(jù)庫或數(shù)據(jù)庫對象的結(jié)構(gòu)。語句：刪除數(shù)據(jù)庫或數(shù)據(jù)庫對象的結(jié)構(gòu)。刪除對象結(jié)構(gòu)包括刪除該對象中的所有內(nèi)容和對刪除對象結(jié)構(gòu)包括刪除該對

7、象中的所有內(nèi)容和對象本身象本身。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第8頁數(shù)據(jù)操縱語言數(shù)據(jù)操縱語言 l數(shù)據(jù)操作語言數(shù)據(jù)操作語言DML(Data Manipulation Language)，通過它可以實現(xiàn)對數(shù)據(jù)庫的基本操作。如對表中數(shù)通過它可以實現(xiàn)對數(shù)據(jù)庫的基本操作。如對表中數(shù)據(jù)的查詢、插入、刪除和修改據(jù)的查詢、插入、刪除和修改 lDML語言包括語言包括INSERT、SELECT、UPDATE及及DELETE等語句，即通常說的等語句，即通常說的增、刪、改、查增、刪、改、查。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第9頁數(shù)據(jù)操縱語言數(shù)據(jù)操縱語言

8、 l創(chuàng)建表對象之后，初始狀態(tài)時該表是空的，需要使創(chuàng)建表對象之后，初始狀態(tài)時該表是空的，需要使用用INSERT語句向其中輸入數(shù)據(jù)。語句向其中輸入數(shù)據(jù)。l使用使用SELECT語句檢索表中數(shù)據(jù)語句檢索表中數(shù)據(jù)l如果表中數(shù)據(jù)不正確，可用如果表中數(shù)據(jù)不正確，可用UPDATE語句進行更新語句進行更新l也可以使用也可以使用DELETE語句刪除表中的數(shù)據(jù)。語句刪除表中的數(shù)據(jù)。l注意：注意：DELETE語句與語句與DROP語句不同語句不同DELETE語句刪除表中的數(shù)據(jù)，但是該表對象依語句刪除表中的數(shù)據(jù)，但是該表對象依然存在。然存在。DROP語句則刪除了表對象，表中的數(shù)據(jù)自然也語句則刪除了表對象，表中的數(shù)據(jù)自然也

9、不存在了。不存在了。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第10頁數(shù)據(jù)控制語言數(shù)據(jù)控制語言 l數(shù)據(jù)控制語言數(shù)據(jù)控制語言(DCL)主要用來執(zhí)行有關安全管理的操主要用來執(zhí)行有關安全管理的操作，該語言主要包括作，該語言主要包括GRANT語句、語句、REVOKE語句和語句和DENY語句。語句。GRANT語句可以將指定的安全對象的權(quán)限授予相語句可以將指定的安全對象的權(quán)限授予相應的主體應的主體REVOKE語句則刪除授予的權(quán)限語句則刪除授予的權(quán)限D(zhuǎn)ENY語句拒絕授予主體權(quán)限，并且防止主體通語句拒絕授予主體權(quán)限，并且防止主體通過組或角色成員繼承權(quán)限。過組或角色成員繼承權(quán)限。SQL S

10、erver實用簡明教程(第三版)第3章 T-SQL語言第11頁9.1.1 用戶定義數(shù)據(jù)類型用戶定義數(shù)據(jù)類型l用戶自定義數(shù)據(jù)類型是在基本數(shù)據(jù)的基礎上根據(jù)需用戶自定義數(shù)據(jù)類型是在基本數(shù)據(jù)的基礎上根據(jù)需要定義的數(shù)據(jù)類型。它提供了一種加強數(shù)據(jù)庫內(nèi)部要定義的數(shù)據(jù)類型。它提供了一種加強數(shù)據(jù)庫內(nèi)部元素和基本數(shù)據(jù)類型之間一致性的機制，用于簡化元素和基本數(shù)據(jù)類型之間一致性的機制，用于簡化對常用規(guī)則和默認值的管理。對常用規(guī)則和默認值的管理。l當多個表的列中要存儲同類型的數(shù)據(jù)，同時確保這當多個表的列中要存儲同類型的數(shù)據(jù)，同時確保這些列具有完全相同的數(shù)據(jù)類型、長度、是否為空值些列具有完全相同的數(shù)據(jù)類型、長度、是否為空

11、值時，可使用用戶自定義數(shù)據(jù)類型。時，可使用用戶自定義數(shù)據(jù)類型。l創(chuàng)建了別名數(shù)據(jù)類型之后，可以在創(chuàng)建了別名數(shù)據(jù)類型之后，可以在 CREATE TABLE 或或 ALTER TABLE 中使用它，也可以將默認值和規(guī)中使用它，也可以將默認值和規(guī)則綁定到別名數(shù)據(jù)類型。則綁定到別名數(shù)據(jù)類型。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第12頁創(chuàng)建別名數(shù)據(jù)類型創(chuàng)建別名數(shù)據(jù)類型l語法語法sp_addtype typename = type, phystype = system_data_type , nulltype = null_type ; l typename = type: 別名

12、數(shù)據(jù)類型的名稱別名數(shù)據(jù)類型的名稱l phystype = system_data_type：別名數(shù)據(jù)類：別名數(shù)據(jù)類型所基于的物理數(shù)據(jù)類型或型所基于的物理數(shù)據(jù)類型或 SQL Server 提供的數(shù)提供的數(shù)據(jù)類型。據(jù)類型。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第13頁創(chuàng)建別名數(shù)據(jù)類型創(chuàng)建別名數(shù)據(jù)類型l nulltype = null_type指示別名數(shù)據(jù)類型處理空值的方式。指示別名數(shù)據(jù)類型處理空值的方式。null_type 的數(shù)據(jù)類型為的數(shù)據(jù)類型為 varchar(8)，默認值為，默認值為 NULL，并，并且必須用單引號引起來（且必須用單引號引起來（NULL、NOT NU

13、LL 或或 NONULL）。）。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第14頁9.1.2 常量與變量常量與變量l常量，是表示一個特定數(shù)據(jù)值的符號。常量的格式常量，是表示一個特定數(shù)據(jù)值的符號。常量的格式取決于它所表示的值的數(shù)據(jù)類型。取決于它所表示的值的數(shù)據(jù)類型。l字符串常量字符串常量 字符串常量括在單引號內(nèi)并包含字母數(shù)字字符字符串常量括在單引號內(nèi)并包含字母數(shù)字字符（a-z、A-Z 和和 0-9）以及特殊字符，如感嘆號）以及特殊字符，如感嘆號 (!)、at 符符 () 和數(shù)字號和數(shù)字號 (#)。如果單引號中的字符串包含一個嵌入的引號，可如果單引號中的字符串包含一個嵌入的引

14、號，可以使用兩個單引號表示嵌入的單引號。以使用兩個單引號表示嵌入的單引號。Cincinnati、 OBrien SQL Server實用簡明教程(第三版)第3章 T-SQL語言第15頁9.1.2 常量與變量常量與變量l二進制常量：具有前輟二進制常量：具有前輟 0 x 并且是十六進制數(shù)字字符并且是十六進制數(shù)字字符串。這些常量不使用引號括起。串。這些常量不使用引號括起。 0 xAE 、0 x12Ef l datetime 常量：使用特定格式的字符日期值來表示，常量：使用特定格式的字符日期值來表示，并被單引號括起來。并被單引號括起來。 April 15, 1998 980415 04/15/98 時

15、間常量的示例：時間常量的示例： 14:30:24 04:24 PM l無日期的時間值，其日期定義為無日期的時間值，其日期定義為1900/1/1SQL Server實用簡明教程(第三版)第3章 T-SQL語言第16頁數(shù)值常量數(shù)值常量linteger 常量常量 以沒有用引號括起來并且不包含小數(shù)點的數(shù)字字以沒有用引號括起來并且不包含小數(shù)點的數(shù)字字符串來表示。符串來表示。 ldecimal 常量常量 由沒有用引號括起來并且包含小數(shù)點的數(shù)字字符由沒有用引號括起來并且包含小數(shù)點的數(shù)字字符串來表示串來表示 lfloat 和和 real 常量常量 使用科學記數(shù)法來表示使用科學記數(shù)法來表示SQL Server實

16、用簡明教程(第三版)第3章 T-SQL語言第17頁數(shù)值常量數(shù)值常量lmoney 常量常量 以前綴為可選的小數(shù)點和可選的貨幣符號的數(shù)字以前綴為可選的小數(shù)點和可選的貨幣符號的數(shù)字字符串來表示，不使用引號括起。字符串來表示，不使用引號括起。 $12 $542023.14 luniqueidentifier 常量常量 uniqueidentifier 常量是表示常量是表示 GUID 的字符串。的字符串。可以使用字符或二進制字符串格式指定?？梢允褂米址蚨M制字符串格式指定。 SQL Server實用簡明教程(第三版)第3章 T-SQL語言第18頁變量變量lSQL Server 2005系統(tǒng)中，變量也被

17、稱為系統(tǒng)中，變量也被稱為局部變量局部變量，是可以保存單個特定類型數(shù)據(jù)值的對象是可以保存單個特定類型數(shù)據(jù)值的對象l經(jīng)常在批處理和腳本中使用變量，用做經(jīng)常在批處理和腳本中使用變量，用做作為計數(shù)器計算循環(huán)執(zhí)行的次數(shù)或控制循環(huán)執(zhí)行作為計數(shù)器計算循環(huán)執(zhí)行的次數(shù)或控制循環(huán)執(zhí)行的次數(shù)的次數(shù)保存數(shù)據(jù)值以供控制流語句測試保存數(shù)據(jù)值以供控制流語句測試保存存儲過程返回代碼要返回的數(shù)據(jù)值或函數(shù)返保存存儲過程返回代碼要返回的數(shù)據(jù)值或函數(shù)返回值回值SQL Server實用簡明教程(第三版)第3章 T-SQL語言第19頁變量變量lT-SQL語言中，使用語言中，使用DECLARE語句聲明變量語句聲明變量l聲明過程聲明過程為變

18、量指定名稱，且名稱的第一個字符必須是為變量指定名稱，且名稱的第一個字符必須是指定該變量的數(shù)據(jù)類型和長度指定該變量的數(shù)據(jù)類型和長度將該變量值設置為將該變量值設置為NULL l有兩種為變量賦值的方式，即使用有兩種為變量賦值的方式，即使用SET語句為變量語句為變量賦值和使用賦值和使用SELECT語句選擇列表中當前所引用值語句選擇列表中當前所引用值來為變量賦值。來為變量賦值。 SQL Server實用簡明教程(第三版)第3章 T-SQL語言第20頁局部變量的賦值局部變量的賦值l局部變量的值使用局部變量的值使用SELECT、UPDATE和和SET語句進語句進行賦值行賦值DECLARE int_var i

19、ntSELECT int_var =12 /*給給int_var賦值賦值*/SELECT int_var /*輸出到屏幕上輸出到屏幕上*/l在未賦值前，局部變量的值為在未賦值前，局部變量的值為nullSQL Server實用簡明教程(第三版)第3章 T-SQL語言第21頁局部變量的賦值局部變量的賦值l在一條語句中可以同時對幾個變量進行賦值，例如，在一條語句中可以同時對幾個變量進行賦值，例如，DECLARE LastName char(8), Firstname char(8), BirthDate datetimeSELECT LastName=Smith, Firstname=David,

20、BirthDate=1985-2-20SELECT LastName, Firstname, BirthDateSQL Server實用簡明教程(第三版)第3章 T-SQL語言第22頁局部變量的賦值局部變量的賦值l例例9-1 使用使用SELECT語句從語句從customer表中檢索出顧表中檢索出顧客編號為客編號為C0002的行，再將顧客的名字賦給變量的行，再將顧客的名字賦給變量customer。DECLARE customer varchar(40),curdate datetimeSELECT customer=customer_name, curdate=getdate()FROM cus

21、tomerWHERE customer_id=C0002SQL Server實用簡明教程(第三版)第3章 T-SQL語言第23頁局部變量的賦值局部變量的賦值l如果如果SELECT返回多個結(jié)果，則只將返回的最后一返回多個結(jié)果，則只將返回的最后一行的值賦給局部變量行的值賦給局部變量l若檢索結(jié)果為空，則局部變量的值不變?nèi)魴z索結(jié)果為空，則局部變量的值不變SQL Server實用簡明教程(第三版)第3章 T-SQL語言第24頁使用使用SET為局部變量賦值為局部變量賦值l例例9-3 計算計算employee表的記錄數(shù)并賦值給局部變量表的記錄數(shù)并賦值給局部變量rows。DECLARE rows intSET

22、 rows=(SELECT COUNT(*) FROM employee)SELECT rowslSET一次只能為一個局部變量進行賦值一次只能為一個局部變量進行賦值SQL Server實用簡明教程(第三版)第3章 T-SQL語言第25頁全局變量全局變量l某些某些 Transact-SQL 系統(tǒng)函數(shù)的名稱以兩個系統(tǒng)函數(shù)的名稱以兩個 at 符號符號 () 打頭。在打頭。在 Microsoft SQL Server 的早期版本的早期版本中，中，functions 被稱為全局變量，但它們不是變被稱為全局變量，但它們不是變量，也不具備變量的行為。量，也不具備變量的行為。lfunctions 是是系統(tǒng)函數(shù)

23、系統(tǒng)函數(shù)，它們的語法遵循函數(shù)的，它們的語法遵循函數(shù)的規(guī)則。規(guī)則。 SQL Server實用簡明教程(第三版)第3章 T-SQL語言第26頁ERRORl如最后的如最后的 T-SQL 語句執(zhí)行成功，語句執(zhí)行成功，ERROR系統(tǒng)系統(tǒng)函數(shù)返回函數(shù)返回 0；l如此語句產(chǎn)生錯誤，如此語句產(chǎn)生錯誤， ERROR 返回返回錯誤號錯誤號l每一個每一個T-SQL語句完成時，語句完成時，ERROR 都會得到一都會得到一個新的值個新的值lERROR 可用以下兩種方法處理：可用以下兩種方法處理： 在在 T-SQL 語句后，馬上檢測或使用語句后，馬上檢測或使用ERROR在在 T-SQL 語句完成后，馬上把語句完成后，馬上

24、把 ERROR 存儲存儲到一個整型變量中。此變量的值可供以后使用到一個整型變量中。此變量的值可供以后使用SQL Server實用簡明教程(第三版)第3章 T-SQL語言第27頁ERRORSELECT * FROM not_found_tableGOSELECT ERROR AS N第一個錯誤號第一個錯誤號GOSELECT 5 + 6 AS N兩個數(shù)之和兩個數(shù)之和GOSELECT ERROR AS N第二個錯誤號第二個錯誤號GOSQL Server實用簡明教程(第三版)第3章 T-SQL語言第28頁練習：使用練習：使用ERRORl首先試圖以首先試圖以0作除數(shù)，這會導致錯誤作除數(shù)，這會導致錯誤SE

25、LECT 100/0SELECT ERRORSELECT ERRORl查看查看“結(jié)果結(jié)果”和和“消息消息”兩個選項卡的內(nèi)容。兩個選項卡的內(nèi)容。l這個例子說明了這個例子說明了ERROR變量值的變量值的生命周期生命周期SQL Server實用簡明教程(第三版)第3章 T-SQL語言第29頁l例例9-4 使服務器產(chǎn)生服務，并顯示錯誤號。使服務器產(chǎn)生服務，并顯示錯誤號。raiserror(miscellaneous error message,16,1) /*產(chǎn)生一個錯誤產(chǎn)生一個錯誤*/if error0SELECT error as last errorSQL Server實用簡明教程(第三版)第3

26、章 T-SQL語言第30頁l例例9-5 捕捉例捕捉例9-4中服務器產(chǎn)生的錯誤號，并顯示出中服務器產(chǎn)生的錯誤號，并顯示出來。來。DECLARE my_error intRAISERROR(miscellaneous error message,16,1)SELECT my_error=errorIF my_error0SELECT my_error as last errorSQL Server實用簡明教程(第三版)第3章 T-SQL語言第31頁9.1.3 運算符與表達式運算符與表達式 l運算符是一種符號，用來指定要在一個或多個表達運算符是一種符號，用來指定要在一個或多個表達式式 中執(zhí)行的操作。

27、中執(zhí)行的操作。l在在T-SQL語言中，語言中，表達式表達式是是標識符標識符、變量變量、常量常量、標量函數(shù)標量函數(shù)、子查詢子查詢、運算符等的組合運算符等的組合。lSQL Server 2005系統(tǒng)中，表達式可以在多個不同的系統(tǒng)中，表達式可以在多個不同的位置使用，這些位置包括查詢中檢索數(shù)據(jù)的一部分、位置使用，這些位置包括查詢中檢索數(shù)據(jù)的一部分、搜索數(shù)據(jù)的條件等。搜索數(shù)據(jù)的條件等。簡單表達式簡單表達式：一個變量、常量、列名或標量函數(shù)：一個變量、常量、列名或標量函數(shù)復雜表達式復雜表達式：由兩個或更多個簡單表達式通過使：由兩個或更多個簡單表達式通過使用運算符連接起來的表達式。用運算符連接起來的表達式。S

28、QL Server實用簡明教程(第三版)第3章 T-SQL語言第32頁9.1.3 運算符與表達式運算符與表達式 l在在SQL Server 2005系統(tǒng)中，可使用下列運算符：系統(tǒng)中，可使用下列運算符：算術(shù)運算符算術(shù)運算符邏輯運算符邏輯運算符賦值運算符賦值運算符字符串串聯(lián)運算符字符串串聯(lián)運算符按位運算符按位運算符比較運算符比較運算符SQL Server實用簡明教程(第三版)第3章 T-SQL語言第33頁運算符運算符l算術(shù)運算符算術(shù)運算符 +，-，*，/，%（模余）（模余） SELECT 12.0/5.0, 12/5, 12.0/15.0, 12/15, 12%7l例例9-9 使用使用“+”將將g

29、oods表中高于表中高于9000元的商品價元的商品價格增加格增加15元。元。SELECT goods_name,unit_price,(unit_price+15) AS nowpriceFROM goodsWHERE unit_price9000SQL Server實用簡明教程(第三版)第3章 T-SQL語言第34頁賦值運算符賦值運算符lT-SQL 中只有一個賦值運算符，即（中只有一個賦值運算符，即（=）。）。l賦值運算符使我們能夠?qū)?shù)據(jù)值指派給特定的對象賦值運算符使我們能夠?qū)?shù)據(jù)值指派給特定的對象l還可以使用賦值運算符改變查詢結(jié)果中顯示的列標還可以使用賦值運算符改變查詢結(jié)果中顯示的列標題題

30、DECLARE Ename VARCHAR(128)SET Ename = CleonGOSELECT FIRSTNAME = FirstName, LASTNAME = LastNameFROM Contact SQL Server實用簡明教程(第三版)第3章 T-SQL語言第35頁位運算符位運算符l位運算符能夠在整型數(shù)據(jù)或者二進制數(shù)據(jù)（位運算符能夠在整型數(shù)據(jù)或者二進制數(shù)據(jù)（image 數(shù)據(jù)類型除外）之間執(zhí)行位操作。數(shù)據(jù)類型除外）之間執(zhí)行位操作。運算符運算符含義含義&（按位（按位 AND）按位按位 AND（兩個操作數(shù)）（兩個操作數(shù)）|（按位（按位 OR）按位按位 OR（兩個操作數(shù)）（

31、兩個操作數(shù)）（按位異或（按位異或XOR） 按位異或按位異或XOR（兩個操作數(shù)）（兩個操作數(shù)）SQL Server實用簡明教程(第三版)第3章 T-SQL語言第36頁位運算符位運算符l20：0001 0100，12：0000 1100l20&12 0001 01000000 11000000 01004l20|120001 01000000 11000001 110028SQL Server實用簡明教程(第三版)第3章 T-SQL語言第37頁位運算符位運算符l20：0001 0100，12：0000 1100l2012 0001 01000000 11000001 100024SELEC

32、T 20&12, 20|12, 2012SQL Server實用簡明教程(第三版)第3章 T-SQL語言第38頁比較運算符比較運算符l比較運算符亦稱為比較運算符亦稱為關系運算符關系運算符，用于比較兩個表達，用于比較兩個表達式的大小或是否相同，其比較的結(jié)果是式的大小或是否相同，其比較的結(jié)果是布爾值布爾值，即，即TRUE、FALSE以及以及UNKNOWN。l除了除了 text，ntext 或或 image 數(shù)據(jù)類型的表達式外，數(shù)據(jù)類型的表達式外，比較運算符可以用于所有的表達式。比較運算符可以用于所有的表達式。l=、=、=、!=、!SQL Server實用簡明教程(第三版)第3章 T-SQL

33、語言第39頁邏輯運算符邏輯運算符 p177USE AdventureWorksSELECT *FROM ContactWHERE contactID 10 AND Title = Mr. OR FirstName = Toml查找合同編碼小于查找合同編碼小于10且稱謂為且稱謂為Mr.，或者，或者FirstName為為Tom的紀錄的紀錄WHERE (contactID And-OrSQL Server實用簡明教程(第三版)第3章 T-SQL語言第43頁9.2 函數(shù)函數(shù)l在在T-SQL語言中，函數(shù)被用來執(zhí)行一些特殊的運算語言中，函數(shù)被用來執(zhí)行一些特殊的運算以支持以支持SQL Server的標準命令

34、。的標準命令。lSQL Server包含多種不同的函數(shù)用以完成各種工作，包含多種不同的函數(shù)用以完成各種工作，每一個函數(shù)都有一個名稱，在名稱之后有一對小括每一個函數(shù)都有一個名稱，在名稱之后有一對小括號，如：號，如：gettime( )。l大部分的函數(shù)在小括號中需要一個或者多個參數(shù)。大部分的函數(shù)在小括號中需要一個或者多個參數(shù)。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第44頁函數(shù)的特點和類型函數(shù)的特點和類型 函函 數(shù)數(shù) 類類 別別描描 述述聚合函數(shù)聚合函數(shù)將多個數(shù)值合并為一個數(shù)值，例如計算合計值將多個數(shù)值合并為一個數(shù)值，例如計算合計值配置函數(shù)配置函數(shù)返回當前配置選項配置的信息

35、返回當前配置選項配置的信息加密函數(shù)加密函數(shù)支持加密、解密、數(shù)字簽名和數(shù)字簽名驗證支持加密、解密、數(shù)字簽名和數(shù)字簽名驗證游標函數(shù)游標函數(shù)返回有關游標狀態(tài)的信息返回有關游標狀態(tài)的信息日期和時間函數(shù)日期和時間函數(shù)可以執(zhí)行與日期、時間數(shù)據(jù)相關的操作可以執(zhí)行與日期、時間數(shù)據(jù)相關的操作數(shù)學函數(shù)數(shù)學函數(shù)執(zhí)行對數(shù)、指數(shù)、三角函數(shù)、平方根等數(shù)學運算執(zhí)行對數(shù)、指數(shù)、三角函數(shù)、平方根等數(shù)學運算元數(shù)據(jù)函數(shù)元數(shù)據(jù)函數(shù)用于返回數(shù)據(jù)庫和數(shù)據(jù)庫對象的屬性信息用于返回數(shù)據(jù)庫和數(shù)據(jù)庫對象的屬性信息SQL Server實用簡明教程(第三版)第3章 T-SQL語言第45頁函數(shù)的特點和類型函數(shù)的特點和類型 函函 數(shù)數(shù) 類類 別別描描

36、述述排名函數(shù)排名函數(shù)可返回分區(qū)中的每一行的排名值可返回分區(qū)中的每一行的排名值行集函數(shù)行集函數(shù)可返回一可用于代替可返回一可用于代替T-SQL語句中表引用的對象語句中表引用的對象安全函數(shù)安全函數(shù)返回有關用戶和角色的信息返回有關用戶和角色的信息字符串函數(shù)字符串函數(shù)可對字符數(shù)據(jù)執(zhí)行替換、截斷、合并等操作可對字符數(shù)據(jù)執(zhí)行替換、截斷、合并等操作系統(tǒng)函數(shù)系統(tǒng)函數(shù)對系統(tǒng)級的各種選項和對象進行操作或報告對系統(tǒng)級的各種選項和對象進行操作或報告系統(tǒng)統(tǒng)計函數(shù)系統(tǒng)統(tǒng)計函數(shù)返回有關返回有關SQL Server系統(tǒng)性能統(tǒng)計的信息系統(tǒng)性能統(tǒng)計的信息文本和圖像函數(shù)文本和圖像函數(shù)用于執(zhí)行更改用于執(zhí)行更改TEXT和和IMAGE值的

37、操作值的操作SQL Server實用簡明教程(第三版)第3章 T-SQL語言第46頁字符串函數(shù)字符串函數(shù)USE AdventureWorksSELECT FirstName, N名稱長度名稱長度 = LEN(FirstName), N名稱前名稱前3個字母的大寫形式個字母的大寫形式 = UPPER(SUBSTRING(FirstName, 1, 3), N名稱復制名稱復制3遍遍 = REPLICATE(FirstName,3)FROM Person.Contactl要求掌握：要求掌握：substring, left, rightSQL Server實用簡明教程(第三版)第3章 T-SQL語言第4

38、7頁數(shù)學函數(shù)數(shù)學函數(shù) p181SELECT N自然對數(shù)自然對數(shù) = LOG(10), N指數(shù)指數(shù) = EXP(12), N符號符號 = SIGN(2), N圓周率圓周率 = PI(), N正弦正弦 = SIN(PI()/2.0), N余弦余弦 = COS(PI()/2.0)SQL Server實用簡明教程(第三版)第3章 T-SQL語言第48頁數(shù)學函數(shù)數(shù)學函數(shù)l例例9-12 用用ceiling和和floor函數(shù)返回大于或等于指定值函數(shù)返回大于或等于指定值的最小整數(shù)值和小于或等于指定值的最大整數(shù)值。的最小整數(shù)值和小于或等于指定值的最大整數(shù)值。SELECT ceiling(123),floor(3

39、21), ceiling(12.3), ceiling(-32.1),floor(-32.1)l例例9-13 round函數(shù)的使用。函數(shù)的使用。SELECT round(12.34512,3),round(12.34567,3), round(12.345,-2),round(154.321,-2)SQL Server實用簡明教程(第三版)第3章 T-SQL語言第49頁日期和時間函數(shù)日期和時間函數(shù) p182l日期和時間函數(shù)用于對日期和時間數(shù)據(jù)進行各種不日期和時間函數(shù)用于對日期和時間數(shù)據(jù)進行各種不同的處理和運算，并返回一個字符串、數(shù)字值或日同的處理和運算，并返回一個字符串、數(shù)字值或日期和時間值。

40、期和時間值。l與其他函數(shù)一樣，可以在與其他函數(shù)一樣，可以在SELECT語句的語句的SELECT和和WHERE子句以及表達式中使用日期和時間函數(shù)。子句以及表達式中使用日期和時間函數(shù)。 SQL Server實用簡明教程(第三版)第3章 T-SQL語言第50頁日期和時間函數(shù)日期和時間函數(shù)函數(shù)函數(shù)參數(shù)參數(shù)功能功能DATEADD(datepart,number,datedatepart,number,date) )以以datepartdatepart指定的方式，返指定的方式，返回回date加上加上number之和之和DATEDIFF(datepart,date1,date2 ) datepart,dat

41、e1,date2 ) 以以datepartdatepart指定的方式，返指定的方式，返回回date2與與date1之差之差DATENAME (datepart,datedatepart,date ) ) 返回日期返回日期date中中datepartdatepart指指定部分所對應的字符串定部分所對應的字符串DATEPART (datepart,datedatepart,date ) ) 返回日期返回日期date中中datepartdatepart指指定部分所對應的整數(shù)值定部分所對應的整數(shù)值SQL Server實用簡明教程(第三版)第3章 T-SQL語言第51頁日期和時間函數(shù)日期和時間函數(shù)lSE

42、LECT GETDATE()函數(shù)函數(shù)參數(shù)參數(shù)功能功能DAY（datedate）返回指定日期的天數(shù)返回指定日期的天數(shù)GETDATE（）（）返回當前的日期和時間返回當前的日期和時間MONTH（datedate）返回指定日期的月份數(shù)返回指定日期的月份數(shù)YEAR（datedate）返回指定日期的年份數(shù)返回指定日期的年份數(shù)SQL Server實用簡明教程(第三版)第3章 T-SQL語言第52頁l例例9-14 使用使用datediff函數(shù)來確定貨物是否按時送給函數(shù)來確定貨物是否按時送給客戶?？蛻?。SELECT goods_id, datediff(dd,send_date,arrival_date)FRO

43、M purchase_orderl系統(tǒng)函數(shù)系統(tǒng)函數(shù) 不要求不要求 p183SQL Server實用簡明教程(第三版)第3章 T-SQL語言第53頁9.2.2 用戶定義函數(shù)用戶定義函數(shù)l用戶定義函數(shù)是接受參數(shù)、執(zhí)行操作并且將運算結(jié)用戶定義函數(shù)是接受參數(shù)、執(zhí)行操作并且將運算結(jié)果以值的形式返回的例程。果以值的形式返回的例程。返回值可以是返回值可以是單個標量值單個標量值，也可以是一個，也可以是一個結(jié)果集結(jié)果集lSQL Server 2005中，使用中，使用CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION語句來實語句來實現(xiàn)用戶定義函數(shù)的創(chuàng)建、修改和刪除?，F(xiàn)用戶定義

44、函數(shù)的創(chuàng)建、修改和刪除。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第54頁使用使用CREATE FUNCTION語句語句CREATE FUNCTION 所有者名稱所有者名稱.函數(shù)名函數(shù)名 ( 參數(shù)名稱參數(shù)名稱 AS 數(shù)據(jù)類型數(shù)據(jù)類型 =默認值默認值 , n ) RETURNS 返回值類型返回值類型 AS BEGIN 函數(shù)體函數(shù)體SQL語句語句 RETURN 數(shù)值表達式數(shù)值表達式 ENDSQL Server實用簡明教程(第三版)第3章 T-SQL語言第55頁使用使用CREATE FUNCTION語句語句 lSQL Server 2005中，使用中，使用CREATE FUNC

45、TION語句可以創(chuàng)建語句可以創(chuàng)建標量函數(shù)標量函數(shù)、內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)、多語句表多語句表值函數(shù)值函數(shù)如果如果RETURNS子句指定了一種標量數(shù)據(jù)類型，子句指定了一種標量數(shù)據(jù)類型，則該函數(shù)為則該函數(shù)為標量函數(shù)標量函數(shù)如果如果RETURNS子句指定了子句指定了TABLE，則該函數(shù)，則該函數(shù)為為表值函數(shù)表值函數(shù)。l根據(jù)函數(shù)主體的定義方式，表值函數(shù)可以分為根據(jù)函數(shù)主體的定義方式，表值函數(shù)可以分為內(nèi)嵌內(nèi)嵌函數(shù)函數(shù)或或多語句函數(shù)多語句函數(shù)。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第56頁標量值型自定義函數(shù)標量值型自定義函數(shù)l標量值型自定義函數(shù)的最大特點是標量值型自定義函數(shù)的最大

46、特點是返回單個值返回單個值，即，即標量值。標量值。l可以在允許使用標量表達式（可以在允許使用標量表達式（scalar expression）的地方使用標量的地方使用標量UDF。例如查詢、約束、計算列等。例如查詢、約束、計算列等。必須包含必須包含BEGIN/END塊以定義函數(shù)的主體塊以定義函數(shù)的主體調(diào)用時必須限定架構(gòu)調(diào)用時必須限定架構(gòu)調(diào)用時不允許忽略可選參數(shù)；或者為它們指定調(diào)用時不允許忽略可選參數(shù)；或者為它們指定DEFAULT關鍵字關鍵字SQL Server實用簡明教程(第三版)第3章 T-SQL語言第57頁標量值型自定義函數(shù)標量值型自定義函數(shù)l例例9-18 創(chuàng)建函數(shù)創(chuàng)建函數(shù)DatetoQuar

47、ter，輸入日期數(shù)據(jù)，輸入日期數(shù)據(jù)，輸出對應的季度值。如輸入輸出對應的季度值。如輸入2006-8-5，返回，返回3Q2006，表示，表示2006年年3季度。季度。CREATE FUNCTION DatetoQuarter(dqdate datetime)RETURNS char(6)ASBEGIN RETURN(datename(q,dqdate)+Q+ datename(yyyy,dqdate)ENDSQL Server實用簡明教程(第三版)第3章 T-SQL語言第58頁表值型自定義函數(shù)表值型自定義函數(shù)l返回值為數(shù)據(jù)表的自定義函數(shù)返回值為數(shù)據(jù)表的自定義函數(shù)，這類自定義函數(shù)通，這類自定義函數(shù)通

48、常被用于一條查詢語句的常被用于一條查詢語句的FROM子句中。子句中。l可以在任何使用一個數(shù)據(jù)表可以在任何使用一個數(shù)據(jù)表(或視圖或視圖)的地方使用一個的地方使用一個表值型自定義函數(shù)，由于表值型自定義函數(shù)可以根表值型自定義函數(shù)，由于表值型自定義函數(shù)可以根據(jù)需要使用相應的參數(shù)，比數(shù)據(jù)表或視圖更具動態(tài)據(jù)需要使用相應的參數(shù)，比數(shù)據(jù)表或視圖更具動態(tài)性。性。l表值型自定義函數(shù)包括表值型自定義函數(shù)包括內(nèi)嵌內(nèi)嵌(inline)和和多語句多語句(Multistatement)表值型自定義函數(shù)兩種表值型自定義函數(shù)兩種SQL Server實用簡明教程(第三版)第3章 T-SQL語言第59頁內(nèi)嵌表值型內(nèi)嵌表值型UDFl

49、1、返回類型為、返回類型為table。返回變量的格式由。返回變量的格式由RETURN 子句中的子句中的 SELECT 語句的結(jié)果集的格式設置。語句的結(jié)果集的格式設置。l2、function_body 不由不由BEGIN和和END分隔。分隔。l3、RETURN子句在括號中包含單個子句在括號中包含單個SELECT語句。語句。SELECT語句的結(jié)果集構(gòu)成函數(shù)所返回的表。語句的結(jié)果集構(gòu)成函數(shù)所返回的表。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第60頁內(nèi)嵌表值型自定義函數(shù)內(nèi)嵌表值型自定義函數(shù)l例例9-19 創(chuàng)建用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)goodsq，返回輸入商品，返回輸入商品編

50、號的商品名稱和庫存量。編號的商品名稱和庫存量。CREATE FUNCTION goodsq(goods_id varchar(30)RETURNS TABLEASRETURN(SELECT goods_name,stock_quantity FROM goods WHERE goods_id =goods_id)SQL Server實用簡明教程(第三版)第3章 T-SQL語言第61頁l由于視圖不支持在由于視圖不支持在WHERE子句的搜索條件中使用參子句的搜索條件中使用參數(shù)，內(nèi)嵌表值函數(shù)可彌補視圖的這一不足之處，即數(shù)，內(nèi)嵌表值函數(shù)可彌補視圖的這一不足之處，即內(nèi)嵌表值函數(shù)可用于實現(xiàn)內(nèi)嵌表值函數(shù)可用

51、于實現(xiàn)參數(shù)化視圖參數(shù)化視圖的功能。的功能。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第62頁多語句表值型自定義函數(shù)多語句表值型自定義函數(shù)l多語句表值型自定義函數(shù)是指那些需要多條查詢語多語句表值型自定義函數(shù)是指那些需要多條查詢語句才能完成既定目標的表值型函數(shù)。句才能完成既定目標的表值型函數(shù)。這類表值型自定義函數(shù)只能用在這類表值型自定義函數(shù)只能用在SELECT查詢語查詢語句中的句中的FROM子句中。子句中。SQL Server實用簡明教程(第三版)第3章 T-SQL語言第63頁多語句表值型自定義函數(shù)多語句表值型自定義函數(shù)l例例9-20 根據(jù)輸入的訂單編號，返回該訂單對應商品根

52、據(jù)輸入的訂單編號，返回該訂單對應商品的編號、名稱、類別編號、類別名稱。的編號、名稱、類別編號、類別名稱。CREATE FUNCTION good_info(in_o_id varchar(10)RETURNS goodinfo TABLE( o_id char(6), g_id char(6), g_name varchar(50), c_id char(6), c_name varchar(20)ASSQL Server實用簡明教程(第三版)第3章 T-SQL語言第64頁BEGIN DECLARE g_id varchar(10),g_name varchar(30) DECLARE c_i

53、d varchar(10),c_name varchar(30) SELECT g_id=goods_id FROM sell_order WHERE order_id1=in_o_id SELECT g_name=goods_name, c_id=classification_id FROM goods WHERE goods_id=g_idSQL Server實用簡明教程(第三版)第3章 T-SQL語言第65頁 SELECT c_name=classification_name FROM goods_classification WHERE c_id=classification_id I

54、NSERT goodinfo VALUES(in_o_id,g_id,g_name,c_id, c_name) RETURNENDSQL Server實用簡明教程(第三版)第3章 T-SQL語言第66頁l例：在例：在STUDENT庫中創(chuàng)建一個多語句表值函數(shù)庫中創(chuàng)建一個多語句表值函數(shù)CHENGJI，該函數(shù)可以根據(jù)輸入的課程名稱返回選，該函數(shù)可以根據(jù)輸入的課程名稱返回選修該課程的學生姓名和成績。修該課程的學生姓名和成績。 CREATE FUNCTION CHENGJI( inputkc as char(20) ) RETURNS chji TABLE (課程名課程名 char(20), 姓名姓名

55、char(8), 成績成績 tinyint) ASSQL Server實用簡明教程(第三版)第3章 T-SQL語言第67頁AS BEGIN INSERT chji SELECT c.課程名課程名,s.姓名姓名 ,k.成績成績 FROM 學生學生 as S INNER JOIN 選課選課 as SC ON S.學號學號 =SC.學號學號 inner join 課程課程 as C on C.課程號課程號=C.課程號課程號 WHERE C.課程名課程名=inputkc RETURN END GOSQL Server實用簡明教程(第三版)第3章 T-SQL語言第68頁執(zhí)行用戶自定義函數(shù)執(zhí)行用戶自定義函

56、數(shù) P188l調(diào)用方法與系統(tǒng)內(nèi)置函數(shù)相同，但要在函數(shù)名前加調(diào)用方法與系統(tǒng)內(nèi)置函數(shù)相同，但要在函數(shù)名前加上架構(gòu)名。上架構(gòu)名。l可用使用可用使用 EXECUTE 語句執(zhí)行標量值函數(shù)。語句執(zhí)行標量值函數(shù)。lSELECT dbo.DatetoQuarter (2006-8-5)lSELECT * FROM dbo.goodsq(G00002)lSELECT * FROM dbo.good_info(S00002) SQL Server實用簡明教程(第三版)第3章 T-SQL語言第69頁9.3 流程控制語句流程控制語句l結(jié)構(gòu)化程序設計語言的基本結(jié)構(gòu)是結(jié)構(gòu)化程序設計語言的基本結(jié)構(gòu)是順序順序、條件分支條件分

57、支和和循環(huán)循環(huán)。lT-SQL語言中，用于控制語句流的語言被稱為控制語言中，用于控制語句流的語言被稱為控制流語言。流語言。lSQL Server 2005系統(tǒng)提供了系統(tǒng)提供了8種控制流語句種控制流語句SQL Server實用簡明教程(第三版)第3章 T-SQL語言第70頁控制流語句控制流語句控制流語句描 述BEGINEND 定義語句塊，這些語句塊作為一組語句執(zhí)行，允許語句塊嵌套BREAK退出WHILE或IFELSE語句中最里面的循環(huán)。如果END關鍵字作為循環(huán)結(jié)束標記，那么執(zhí)行BREAK語句后將執(zhí)行出現(xiàn)在END關鍵字后面的任何語句GOTO使T-SQL批處理的執(zhí)行跳至指定標簽的語句。不執(zhí)行GOTO語

58、句和標簽之間的所有語句。該語句破壞了結(jié)構(gòu)化語句的結(jié)構(gòu)，盡量減少使用CONTINUE重新開始一個新的WHILE循環(huán)SQL Server實用簡明教程(第三版)第3章 T-SQL語言第71頁控制流語句控制流語句控制流語句描 述IFELSE指定T-SQL語句的執(zhí)行條件。如條件為TRUE，則執(zhí)行其后的T-SQL語句。當條件為FALSE時，可以使用ELSE關鍵字指定要執(zhí)行的T-SQL語句WHILE設置重復執(zhí)行T-SQL語句或語句塊的條件。當指定條件為真時，重復執(zhí)行循環(huán)語句。可在循環(huán)體內(nèi)設置BREAK和CONTINUE關鍵字，以控制循環(huán)語句的執(zhí)行SQL Server實用簡明教程(第三版)第3章 T-SQL語

59、言第72頁控制流語句控制流語句控制流語句描 述RETURN無條件終止查詢、存儲過程或批處理的執(zhí)行。存儲過程或批處理中RETURN語句后面的所有語句都不再執(zhí)行。當在存儲過程中使用RETURN語句時，可使用該語句指定返回給調(diào)用應用程序、批處理或過程的整數(shù)值。如果RETURN語句未指定值，則存儲過程的返回值是0WAITFOR懸掛起批處理、存儲過程或事務的執(zhí)行，直到發(fā)生以下情況為止：已超過指定的時間間隔、到達一天中指定的時間、指定的RECEIVE語句至少修改一行數(shù)據(jù)。該語句是通過暫停語句的執(zhí)行而改變語句的執(zhí)行過程SQL Server實用簡明教程(第三版)第3章 T-SQL語言第73頁9.3.1 語句塊

60、和注釋語句塊和注釋lBEGINEND語句：將多個語句：將多個T-SQL語句組合成一個語句組合成一個語句塊，并將它們視為一個單元處理。語句塊，并將它們視為一個單元處理。l在在條件語句條件語句(IF)和和循環(huán)循環(huán)(WHILE)等控制流程語句中，等控制流程語句中，當符合特定條件便要執(zhí)行兩個或者多個語句時，就當符合特定條件便要執(zhí)行兩個或者多個語句時，就需要使用需要使用BEGINEND語句。語句。 BEGIN sql_statement | statement_block END SQL Server實用簡明教程(第三版)第3章 T-SQL語言第74頁9.3.1 語句塊和注釋語句塊和注釋l例例9-21 顯示顯示Sales數(shù)據(jù)