Transact-Sql介紹.ppt

1、數(shù)據(jù)庫程序設(shè)計(jì) SQL Server 2000 數(shù)據(jù)庫程序設(shè)計(jì),第1章 SQL Server 概述 第2章 創(chuàng)建和管理數(shù)據(jù)庫 第3章 創(chuàng)建數(shù)據(jù)類型和表 第4章 實(shí)現(xiàn)數(shù)據(jù)完整性 第5章 Transact-SQL介紹 第6章 使用 Transact-SQL查詢工具 第7章 檢索數(shù)據(jù) 第8章 數(shù)據(jù)分組與匯總 第9章 多表聯(lián)接 第10章 子查詢 第11章 修改數(shù)據(jù),第12章 全文索引查詢 第13章 規(guī)劃索引 第14章 創(chuàng)建和維護(hù)索引 第15章 實(shí)現(xiàn)視圖 第16章 實(shí)現(xiàn)存儲(chǔ)過程 第17章 實(shí)現(xiàn)用戶定義函數(shù) 第18章 實(shí)現(xiàn)觸發(fā)器 第19章 多服務(wù)器編程 第20章 優(yōu)化查詢性能 第21章 分析查詢 第22章

2、 管理事務(wù)和鎖,第5章 Transact-SQL 介紹,Transact-SQL 編程語言 Transact-SQL 語句的種類 Transact-SQL 語法的要素,Transact-SQL 編程語言,實(shí)現(xiàn)入口級別 ANSI SQL-92 ISO 標(biāo)準(zhǔn) 能夠在任何入口級別兼容的產(chǎn)品中運(yùn)行 包含另外的幾種獨(dú)特性能,5.1 Transact-SQL 編程語言,第5章 Transact-SQL 介紹,Transact-SQL 編程語言 Transact-SQL 語句的種類 Transact-SQL 語法的要素,Transact-SQL 語句的種類,數(shù)據(jù)定義語言（DDL）語句 數(shù)據(jù)控制語言（DCL）

3、語句 數(shù)據(jù)操作語言（DML）語句,5.2 Transact-SQL 語句的種類,數(shù)據(jù)定義語言（DDL）語句,定義數(shù)據(jù)庫對象 CREATE 對象名稱 ALTER 對象名稱 DROP 對象名稱 必須擁有適當(dāng)?shù)臋?quán)限,USE northwind CREATE TABLE customer (cust_id int, company varchar(40), contact varchar(30), phone char(12) ) GO,5.2.1 數(shù)據(jù)定義語言（DDL）語句,數(shù)據(jù)控制語言（DCL）語句,設(shè)置或更改權(quán)限 GRANT 允許一個(gè)用戶能夠訪問數(shù)據(jù)或運(yùn)行某些 Transact-SQL 語句 D

4、ENY 剝奪某個(gè)安全賬戶的訪問許可并阻止某個(gè)用戶、用戶組或角色成員繼承原有的權(quán)限 REVOKE 刪除一個(gè)以前授于或拒絕的許可 必須擁有適當(dāng)?shù)臋?quán)限,USE northwind GRANT SELECT ON products TO public GO,5.2.2 數(shù)據(jù)控制語言（DCL）語句,數(shù)據(jù)操作語言（DML）語句,使用 DML 更改數(shù)據(jù)庫中的數(shù)據(jù)或查詢數(shù)據(jù)庫的信息 SELECT INSERT UPDATE DELETE 必須擁有適當(dāng)?shù)臋?quán)限,USE northwind SELECT categoryid, productname, productid, unitprice FROM produ

5、cts GO,5.2.3 數(shù)據(jù)操作語言（DML）語句,第5章 Transact-SQL 介紹,Transact-SQL 編程語言 Transact-SQL 語句的種類 Transact-SQL 語法的要素,Transact-SQL 語法要素,批處理命令 注釋語句 標(biāo)識(shí)符 標(biāo)識(shí)符的命名原則 數(shù)據(jù)類型 變量,系統(tǒng)函數(shù) 系統(tǒng)函數(shù)示例 運(yùn)算符 表達(dá)式 流控制的語言元素 保留的關(guān)鍵字,5. 3 Transact-SQL 語法要素,11.2 批 處 理,批是一個(gè)以GO為結(jié)束標(biāo)志的SQL語句組，批中的所有SQL語句作為一個(gè)整體編譯成一個(gè)執(zhí)行單元后一次提交給SQL Server服務(wù)器執(zhí)行,若其中有一個(gè)語句出現(xiàn)

6、編譯錯(cuò)誤，則SQL Server將取消該批內(nèi)所有語句的執(zhí)行 若批通過了編譯而在運(yùn)行時(shí)出現(xiàn)錯(cuò)誤，則批內(nèi)發(fā)生錯(cuò)誤之前的語句將被執(zhí)行，發(fā)生錯(cuò)誤的語句不被執(zhí)行；發(fā)生錯(cuò)誤之后的語句視具體情況可能被執(zhí)行或不被執(zhí)行,批的執(zhí)行的特點(diǎn)：,有待實(shí)際中具體對待,批處理命令,GO 向 SQL Server 描述由 Transact-SQL 語句組成的批處理 實(shí)際上并不是 Transact-SQL 語句 EXEC 用于執(zhí)行用戶定義的函數(shù)、系統(tǒng)存儲(chǔ)過程、用戶自定義存儲(chǔ)過程或一個(gè)擴(kuò)展的存儲(chǔ)過程 在一個(gè) Transact-SQL 批處理內(nèi)部，EXEC 能控制一個(gè)字符串的運(yùn)行,5.3.1 批處理命令,11.1 程序注釋語句,注

7、釋語句是不可執(zhí)行語句，SQL Server不對其進(jìn)行編譯 注釋語句在查詢分析器中用綠色字體顯示,注釋語句有兩種類型：,- 類型：這是兩個(gè)英文短橫線，在 - 后書寫注釋語句內(nèi)容，不能換行，僅用于單行注釋語句的書寫。注意短橫線之間不能有空格 /* */ 類型：在兩個(gè)星號(hào)之間書寫注釋語句內(nèi)容，可以換行，用于多行注釋語句的書寫,注釋語句,嵌于行內(nèi)的注釋語句 塊注釋語句,SELECT productname, (unitsinstock - unitsonorder) 計(jì)算存貨, supplierIDFROM products GO,/* 這部分代碼檢索產(chǎn)品表的所有行并且顯示單價(jià)，增加了10%的單價(jià)和產(chǎn)

8、品名稱。 */ USE northwind SELECT unitprice, (unitprice * 1.1), productname FROM products GO,示例 2,示例 1,5.3.2 注釋語句,標(biāo)識(shí)符,標(biāo)準(zhǔn)標(biāo)識(shí)符 第一個(gè)字符必須是字母，az 或 AZ 第一個(gè)字符后可以是數(shù)字、字母或各種符號(hào) 當(dāng)標(biāo)識(shí)符的第一個(gè)字符是符號(hào)時(shí)，代表它有特殊用處 1. 以開頭的標(biāo)識(shí)符代表局部變量或參數(shù) 2. 以#開頭的標(biāo)識(shí)符代表臨時(shí)表或存儲(chǔ)過程 3. 以#開頭的標(biāo)識(shí)符代表一個(gè)全局臨時(shí)對象,5.3.3 標(biāo)識(shí)符,標(biāo)識(shí)符（續(xù)）,限定標(biāo)識(shí)符 當(dāng)對象名稱包含空格時(shí) 當(dāng)保留關(guān)鍵字被用作對象名或?qū)ο蟛糠值拿?/p>

9、時(shí) 用中括號(hào)表示的括號(hào)標(biāo)識(shí)符（ ） 用雙引號(hào)表示的引用標(biāo)識(shí)符（“ ”）,SELECT * FROM 表名中的空格,SELECT * FROM “表名中的空格”,5.3.3 標(biāo)識(shí)符,標(biāo)識(shí)符的命名原則,使用短的名字 盡量使用有意義的名字 使用簡單明了的命名約定 使用能夠區(qū)分出對象類型的標(biāo)識(shí)符 視圖 存儲(chǔ)過程 保持對象名和用戶名的一致性 sales 表和 sales 角色,5.3.4 標(biāo)識(shí)符的命名原則,數(shù)據(jù)類型,數(shù)字型 代表數(shù)字，包括各種整數(shù)如 int、tinyint、smallint 和 bigint 日期型 代表日期和日期范圍，包括 datetime 和 smalldatetime 字符型 用于

10、表示字符數(shù)據(jù)或字符串，包括 char、nchar、varchar 和 nvarchar 二進(jìn)制型 在存儲(chǔ)與結(jié)構(gòu)上與字符型數(shù)據(jù)相似，包括 binary 和varbinary,5.3.5 數(shù)據(jù)類型,數(shù)據(jù)類型（續(xù)）,惟一標(biāo)識(shí)符（GUID） 代表一個(gè)全局的特殊標(biāo)識(shí)符，是一個(gè) 16 位 16 進(jìn)制的值 SQL 變量 代表 SQL Server 所支持的各種數(shù)據(jù)類型 圖像和文本 屬于大型二進(jìn)制對象結(jié)構(gòu)（BLOB），代表定長或變長的數(shù)據(jù)類型 表 代表一個(gè)表結(jié)構(gòu)，在 SQL Server 2000 中，可以把表保存在字段中,5.3.5 數(shù)據(jù)類型,數(shù)據(jù)類型（續(xù)）,游標(biāo) 用于存儲(chǔ)過程的編程中，也會(huì)和低層客戶接口同

11、時(shí)使用。游標(biāo)從不用于數(shù)據(jù)定義語言（DDL）語句中 用戶自定義數(shù)據(jù)類型 基于系統(tǒng)數(shù)據(jù)類型，由數(shù)據(jù)庫管理員生成,5.3.5 數(shù)據(jù)類型,11.4 變 量,SQL Server 的變量有兩種，局部與全局變量,局部變量是在批或存儲(chǔ)過程中聲明與使用的 而全局變量是一種由SQL Server提供的特殊函數(shù),局部變量是在批或存儲(chǔ)過程內(nèi)由用戶定義并使用的變量 用戶在使用局部變量前必須事先聲明，而它的使用范圍也僅限于聲明它的批或存儲(chǔ)過程以內(nèi),一、 局部變量,1聲明局部變量 命令格式： DECLARE ( variable_name datatype ,. n ),說明：,DECLARE ：該關(guān)鍵字表示將要聲明變量

12、variable_name：表示局部變量名，它必須以一個(gè)開頭 datatype：數(shù)據(jù)類型可以是除text、ntext和image外的數(shù)據(jù)類型或用戶定義的數(shù)據(jù)類型 ,. n：表示一個(gè)DECLARE語句中可以聲明多個(gè)局部變量，這些變量用逗號(hào)相隔,默認(rèn)系統(tǒng)自動(dòng)為變量賦初值NULL。若需要另外賦值，可以使用SET或SELECT語句,2局部變量的賦值,格式： SELECT variable_name = expression FROM table_name ,. n WHERE clause ,. n 或 SET variable_name = expression,說明：,expression：可以是

13、 FROM子句用于向變量所賦的值源于由一個(gè)表中查詢所得數(shù)據(jù)的情形,一個(gè)具體的數(shù)據(jù)如數(shù)字、字符串等 一個(gè)表達(dá)式或另一個(gè)局部變量或全局變量 從一個(gè)查詢語句中查詢出來的數(shù)據(jù),說明：,一個(gè)SELECT語句可以為多個(gè)變量賦值，但一個(gè)SET語句僅能為一個(gè)變量賦值 若使用SELECT語句為變量賦值，則不能與其查詢功能同時(shí)使用 若使用SELECT語句從表中取數(shù)為變量賦值，則其返回的數(shù)據(jù)必須惟一，否則僅將最后一個(gè)數(shù)據(jù)賦給變量,declare x1 int,x2 int select x1=3,x2=9 select x1,x2,二、全局變量,全局變量以開頭 在SQL Server中全局變量作為一種特殊函數(shù)由系統(tǒng)

14、預(yù)先定義 全局變量的作用范圍是整個(gè)系統(tǒng) 通常利用全局變量來檢測系統(tǒng)的設(shè)置值或執(zhí)行查詢命令后的狀態(tài)值,例：,SELECT GETDATE() AS Todays Date and Time, CONNECTIONS AS Login Attempts 下面是結(jié)果集： Todays Date and Time Login Attempts - - 1998-04-09 14:28:46.940 18,返回自上次啟動(dòng) Microsoft SQL Server 以來連接或試圖連接的次數(shù),變量,用 DECLARE 語句定義局部變量 用 SET 或 SELECT 語句給局部變量賦值 局部變量只具有局部作用

15、范圍 只能在定義它的語句、批處理或過程中使用,5.3.6 變量,USE northwind DECLARE EmpID varchar(11) ,vlName char(20) SET vlname = Dodsworth SELECT EmpID = employeeid FROM employees WHERE LastName = vlname SELECT EmpID AS EmployeeID GO,示例,變量（續(xù)）,全局變量 用兩個(gè) 標(biāo)識(shí)，由 SQL Server 提供，用戶不能創(chuàng)建全局變量 設(shè)置變量 提供 SQL Server 當(dāng)前各種特性和參數(shù)的設(shè)置信息 統(tǒng)計(jì)變量 提供 SQL

16、 Server 自啟動(dòng)后的運(yùn)行信息 系統(tǒng)變量 提供最近對表操作的信息,5.3.6 變量,系統(tǒng)函數(shù),聚集函數(shù) 標(biāo)量函數(shù) 行集函數(shù),SELECT * FROM OPENQUERY (OracleSvr, SELECT name, id FROM owner.titles),USE northwind SELECT AVG (unitprice) AS AvgPrice FROM products GO,USE northwind SELECT DB_NAME() AS database GO,5.3.7 系統(tǒng)函數(shù),系統(tǒng)函數(shù)（續(xù)）,其他系統(tǒng)函數(shù) 日期函數(shù) 數(shù)學(xué)函數(shù) 字符串函數(shù) 系統(tǒng)函數(shù) 內(nèi)建的不帶參

17、數(shù)的函數(shù)（Niladic 函數(shù)）,5.3.7 系統(tǒng)函數(shù),SELECT ANSI:, CONVERT(varchar(30), GETDATE(), 102) AS StyleUNIONSELECT Japanese:, CONVERT(varchar(30), GETDATE(), 111)UNIONSELECT European:, CONVERT(varchar(30), GETDATE(), 113) GO,結(jié)果,示例,系統(tǒng)函數(shù)示例,5.3.8 系統(tǒng)函數(shù)示例,運(yùn)算符,運(yùn)算符的種類 算術(shù)運(yùn)算符 比較運(yùn)算符 字符串聯(lián)運(yùn)算符 邏輯運(yùn)算符 運(yùn)算符優(yōu)先級,5.3.9 運(yùn)算符,表達(dá)式,各種符號(hào)和運(yùn)算

18、符的組合 對單個(gè)數(shù)據(jù)進(jìn)行操作 表達(dá)式結(jié)果的數(shù)據(jù)類型由表達(dá)式中的成員決定,USE northwind SELECT OrderID, ProductID ,(UnitPrice * Quantity) as ExtendedAmount FROM Order Details WHERE (UnitPrice * Quantity) 10000 GO,5.3.10 表達(dá)式,示例,流控制的語言元素,語句級 BEGIN END 塊 IF ELSE 塊 WHILE 塊 行級 CASE 語句,5.3.11 流控制的語言元素,DECLARE n tinyint SET n = 5 IF (n BETWEEN

19、 4 and 6) BEGIN WHILE (n 0) BEGIN SELECT n AS Number ,CASE WHEN (n % 2) = 1 THEN EVEN ELSE ODD END AS Type SET n = n - 1 END END ELSE PRINT NO ANALYSIS GO,示例,程序控制語句,一、BEGINEND語句,BEGIN.END語句相當(dāng)于其他計(jì)算機(jī)語言中的大括號(hào)或復(fù)合語句，它將多條T-SQL語句封裝成為一個(gè)整體的語句塊 T-SQL中允許使用嵌套的BEGIN.END語句 通常把BEGIN.END語句放在語句IF.ELSE或者WHILE中，使其中的語句作

20、為一個(gè)整體來執(zhí)行,格式： BEGIN T-SQL 語句塊 END 說明：,BEGIN、END分別表示語句塊的開始和結(jié)束 T-SQL語句塊可以是一條或一條以上的T-SQL 語句,IF.ELSE 結(jié)構(gòu)是一種選擇結(jié)構(gòu)，用于進(jìn)行條件判斷，它可以用于批處理、存儲(chǔ)過程或特殊查詢，并且可以嵌套使用 格式： IF boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block ,二、 IFELSE語句,說明：,boolean_expression：是返回“真”（TRUE）或“偽”（FALSE）的布爾

21、表達(dá)式 sql_statement | statement_block：只能是一條T-SQL 語句或一個(gè)BEGINEND語句塊,三、 WHILE語句,WHILE 語句是一種條件循環(huán)語句，在滿足條件時(shí)執(zhí)行循環(huán)體，不滿足條件時(shí)跳出循環(huán)結(jié)構(gòu) WHILE 語句可以嵌套執(zhí)行，嵌套時(shí)首先運(yùn)行內(nèi)層循環(huán)，內(nèi)層循環(huán)結(jié)束之后再運(yùn)行外層循環(huán),格式：,WHILE Boolean_expression sql_statement | statement_block 命令說明： sql_statement | statement_block 為循環(huán)體 可以是一條語句或一個(gè)語句塊,格式： CASE input_expres

22、sion WHEN when_expression THEN result_expression . n ELSE else_result_expression END,四、 CASE語句,1簡單CASE函數(shù),例：,Declare x varchar(10) Set x=3 Select case x when 3 then 1:this is 3 when 2 then 2:this is 2 else error end as x的判斷,說明：,input_expression：輸入表達(dá)式，用于進(jìn)行比較WHEN 后面給出用于進(jìn)行比較的表達(dá)式 when_expressioninput_exp

23、ression 和每個(gè)when_expression 的數(shù)據(jù)類型必須相同，或者能夠隱性轉(zhuǎn)換 THEN后面給出當(dāng) input_expression = when_expression為 真（TRUE）時(shí)系統(tǒng)返回的結(jié)果表達(dá)式Result_expression,4、.n 表示可以有多個(gè)WHEN . THEN結(jié)構(gòu) 5、當(dāng)when和case的值無一匹配時(shí)，語句的值為else后 Else_result_expression的值；如沒定義else，則此時(shí)取值為null,格式： CASE WHEN Boolean_expression THEN result_expression.n ELSE else_result_expression END,2CASE 搜索函數(shù),例：,Use teach Go Select 學(xué)號(hào)=score.sno,姓名=student.sname, 分?jǐn)?shù)= case when score =60 then 60 else error end from s