數(shù)據(jù)庫技術與應用-課件-第7章

1、第七章 T-SQL在SQL Server 2005中的應用第一節(jié) SQL 概述 SQL自從推出以來，就得到了廣泛的應用，國際上很多計算機公司在它們開發(fā)和經銷的數(shù)據(jù)庫管理系統(tǒng)產品中都支持SQL的各種版本軟件。目前廣泛應用的，無論是像Oracle、Sybase、SQL Server、Informix這些大型的數(shù)據(jù)庫管理系統(tǒng)，還是像PowerBuilder、Visual Foxpro、Access這些微機上常用的數(shù)據(jù)庫開發(fā)系統(tǒng)，都支持了SQL標準。 一、SQL 的發(fā)展歷程二、T-SQL特點（一）T-SQL的組成。1.SQL語句的標準語言部分 SQL語句的標準部分，主要利用這些標準的SQL編寫應用程序

2、和腳本，利用編寫好的程序和腳本可以自如地移到其他的關系型數(shù)據(jù)庫管理系統(tǒng)中執(zhí)行。2.在標準SQL語句上的擴充 Microsoft公司在標準SQL語句上增加了許多新的功能，如語句的注釋、變量、運算符、函數(shù)和流程控制語句，而且還增加了可編程性和靈活性。1.一體化的特點2.T-SQL具有交互式和嵌入式兩種使用方式3.高度非過程化、采用集合的操作方式4.語言簡潔、易學易用5.支持三級數(shù)據(jù)模式結構（二）T-SQL的特點（一）T-SQL的語法格式約定（二）T-SQL的運算符 T-SQL所使用的運算符可以分為算術運算符、賦值運算符、按位運算符、比較運算符、邏輯運算符、字符串串聯(lián)運算符和一元運算符7種。（三）函

3、數(shù) 三、T-SQL的基礎知識第二節(jié) 使用T-SQL進行數(shù)據(jù)定義（一）T-SQL語句創(chuàng)建數(shù)據(jù)庫的語法格式CREATE DATABASE 數(shù)據(jù)庫名 / 設置數(shù)據(jù)庫的名稱 ON / 定義數(shù)據(jù)庫的數(shù)據(jù)文件 PRIMARY / 設置主文件組,n / 設置數(shù)據(jù)文件的屬性,FILEGROUP 文件組名 ,n / 設置次數(shù)據(jù)文件組及數(shù)據(jù)文件屬性 LOG ON / 定義數(shù)據(jù)庫的日志文件 ,n / 設置日志文件的屬性一、數(shù)據(jù)庫定義其中和為以下屬性的組合：（NAME=邏輯文件名， / 設置在SQL Server中引用時的名稱 FILENAME= 物理文件名 / 設置文件在磁盤上存放的路徑和名稱,SIZE=文件初始容

4、量 / 設置文件的初始容量,MAXSIZE=文件最大容量|UNLIMITED / 設置文件的最大容量,FILEGROWTH=文件增長率 / 設置文件的自動增量。（二）使用T-SQL語句創(chuàng)建數(shù)據(jù)庫實例1.創(chuàng)建數(shù)據(jù)庫的步驟（1）在SQL Server Management Studio窗口中，單擊工具欄上的“新建查詢”按鈕，打開查詢編輯器。（2）在查詢編輯器中輸入要創(chuàng)建數(shù)據(jù)庫的T-SQL語句 （3）單擊工具欄中的“分析”按鈕，檢查輸入T-SQL語句的語法無錯誤后，單擊“執(zhí)行”按鈕，運行結果如圖所示。（二）使用T-SQL語句創(chuàng)建數(shù)據(jù)庫實例（4）刷新“對象資源管理器”的“數(shù)據(jù)庫”節(jié)點，就可看到定義的t

5、sdgdb數(shù)據(jù)庫，如圖所示。二、基本表的定義（一）T-SQL語句創(chuàng)建基本表1.使用T-SQL語句創(chuàng)建基本表的語法格式CREATE TABLE 數(shù)據(jù)庫名. 表的所有者. 表名 / 設置表名（ / 定義列屬性 / 設置列約束 ,n / 定義其他列)的語法為：列名 數(shù)據(jù)類型（長度） / 設置列名和數(shù)據(jù)類型 DEFAULT 常量表達式 / 定義默認值| IDENTITY（初值，增量） / 定義標識列的語法為：CONSTRAINT 約束名 / 設置約束名NULL | NOT NULL / 設置空或非空值約束|DEFAULT / 設置默認值約束| PRIMARY KEY | UNIQUE / 設置主鍵或唯

6、一性約束 CLUSTERED | NONCLUSTERED / 設置聚簇或非聚簇索引 | FOREIGN KEY（外關鍵字列1,n ) / 設置外鍵約束REFERENCES 參照表名（列1,n ) | CHECK（邏輯表達式） / 設置檢查約束例：為tsdgdb數(shù)據(jù)庫，創(chuàng)建圖書表（ts）、客戶表（kh）、圖書訂購表（tsdg），其表的結構要求為：圖書表包含的字段有圖書編號（tsbh）、圖書名（tsm）、類別（lb）、出版社（cbs）、單價（dj）、作者（zz）、ISBN，其中圖書編號為主鍵，圖書名不能為空?？蛻舯戆淖侄斡锌蛻艟幪枺╧hbh）、客戶姓名（khm）、客戶地址（khdz）、聯(lián)系電

7、話（lxdh），其中客戶編號為主鍵，客戶地址不能為空。圖書訂購表包含的字段有客戶編號（khbh）、圖書編號（tsbh）、訂購日期（dgrq）、訂購數(shù)量（dgsl），要求客戶編號、圖書編號分別來自客戶表和圖書表。創(chuàng)建表實例（1）在查詢編輯器代碼窗口輸入如下創(chuàng)建ts表的語句：USE tsdgdb / 切換當前數(shù)據(jù)庫為tsdgdb GOCREATE TABLE ts（tsbh char(10) PRIMARY KEY, / 設置主鍵約束 tsm char(20) NOT NULL, / 設置非空值約束 lb char(10), cbs char(20),dj money,zz char(10), i

8、sbn char(13)）GO（2）單擊工具欄的“分析”按鈕，檢查語法通過后，單擊“執(zhí)行”按鈕，可以執(zhí)行該SQL語句，并打開查詢結果窗口，看到“命令已成功完成”執(zhí)行信息，然后刷新“對象資源管理器”中的tsdgdb，會看到新建的圖書表（ts），結果如圖所示。 （二）T-SQL語句修改基本表語法格式如下：ALTER TABLE 表名 ADD ,n / 定義要添加的列，設置列約束 | ADD ,n / 定義要添加的列，設置列約束| DROP COLUMN 列名| CONSTRAINT 約束名 ,n / 刪除列或列約束| ALTER COLUMN 列名 / 指定要修改的列名 新數(shù)據(jù)類型 （新數(shù)據(jù)寬度）

9、 / 設置新的數(shù)據(jù)類型 NULL|NOT NULL / 設置是否為空 | WITHCHECK|NOCHECK / 啟用或禁用約束檢查CHECK|NOCHECK CONSTRAINT ALL|約束名,n / 啟用或禁用約束 （三）T-SQL語句刪除基本表刪除基本表使用命令動詞DROP，其語法格式如下：DROP TABLE 基本表名三、視圖的定義（一）T-SQL語句創(chuàng)建視圖其語法格式為：CREATE VIEW （,）AS 子查詢WITH CHECK OPTION例 ：在tsdgdb數(shù)據(jù)庫，為圖書表（ts）創(chuàng)建視圖v_lb，要求通過該視圖，可以查詢類別為“計算機類”的所有圖書信息。（1）在查詢編輯器

10、代碼窗口輸入如下語句：CREATE VIEW v_lbAS SELECT *FROM ts WHERE LB=計算機類（2）執(zhí)行語句，看到“命令成功完成”的消息。在查詢編輯器代碼窗口輸入如下查詢視圖語句，執(zhí)行結果如圖所示。（二）T-SQL語句修改和刪除視圖1.修改視圖其語法格式如下：ALTER VIEW （,）AS 子查詢WITH CHECK OPTION2.刪除視圖刪除視圖通常需要使用DROP VIEW語句進行，其語法格式如下：DROP VIEW 四、索引定義（二）使用T-SQL語句創(chuàng)建索引1.T-SQL語句創(chuàng)建視圖的語法格式 其語法格式如下：CREATE UNIQUECLUSTEREDNO

11、NCLUSTEREDINDEX 索引名ON 表名|視圖名（列名1ASC|DESC,n）例：為tsdgdb數(shù)據(jù)庫的圖書表（ts）創(chuàng)建一個唯一索引tsm_index，依據(jù)字段tsm進行升序排序。 （1）在查詢編輯器代碼窗口輸入如下語句：USE tsdgdbCREATE UNIQUE INDEX tsm_index ON ts（tsm）GO（2）執(zhí)行語句，刷新“對象資源管理器”，即可看到所創(chuàng)建的索引。（二）使用T-SQL語句修改和刪除索引1.修改索引 其語法格式如下：ALTER INDEX 索引名 ON 表名|視圖名REBUILD|REORGANIZE|DISABLE2.刪除索引 其語法格式如下：D

12、ROP INDEX 索引名五、存儲過程定義（一）T-SQL語句創(chuàng)建存儲過程 其語法格式為：CREATE PROCEDURE 參數(shù) 數(shù)據(jù)類型WITH RECOMPILE|ENCRYPTIONAS T-SQL語句塊 （二）使用T-SQL語句執(zhí)行存儲過程在SQL Server中使用EXECUTE語句來執(zhí)行存儲過程，其語法格式如下： EXECUTE 存儲過程名 參數(shù)=參量值|變量 ,n六、觸發(fā)器定義（一）觸發(fā)器的分類1.DML觸發(fā)器 2.DDL觸發(fā)器 （二）T-SQL語句創(chuàng)建觸發(fā)器 1.創(chuàng)建DML觸發(fā)器語法格式如下： CREATE TRIGGER 觸發(fā)器名 ON 表|視圖|服務器|數(shù)據(jù)庫 WITH E

13、NCRYPTION FOR|AFTER|INSTEAD OF INSERT, UPDATE,DELETE AS T-SQL語句 2.創(chuàng)建DDL觸發(fā)器語法格式如下： CREATE TRIGGER 觸發(fā)器名 ON ALL服務器|數(shù)據(jù)庫FOR | AFTER AS T-SQL語句第三節(jié) 使用T-SQL進行數(shù)據(jù)查詢一、SELECT語句介紹 語法格式如下：SELECT ALL|DISTINCTTOP nPERCENT 字段列表INTO 新表名FROM WHERE GROUP BY HAVING ORDER BYALL ASC|DESC,nCOMPUTE ,nBY 列名 ,n二、數(shù)據(jù)查詢（一）簡單查詢1.

14、選擇表中的若干列 SELECT語句格式如下： SELECT 字段列表 FROM 表名【例】 查詢客戶表的客戶編號（khbh）、客戶姓名（khxm）、聯(lián)系電話（lxdh）。USE tsdgdbGOSELECT khbh,khxm,lxdhFROM khGO2.選擇表中若干行（1）通過TOP和DISTINCT關鍵字限制返回的行數(shù) 【例】 查詢圖書表（ts）的前兩條記錄信息。SELECT top 2*FROM ts（2）查詢滿足條件的元組。 基本的SELECT語句格式如下：SELECT 字段列表 FROM 表名 WHERE 查詢條件【例】 查詢計算機類或管理類圖書定價在20元以上的圖書信息。SELE

15、CT *FROM ts WHERE lb=管理類 or lb=計算機類AND dj203.對查詢結果進行排序基本語法格式如下：SELECT 字段列表FROM 表名ORDER BY 列名|列號 ASC|DESC,n【例】 查詢圖書表中書名中有“數(shù)據(jù)庫”的圖書信息，并按定價降序排序。SELECT *FROM tsWHERE tsm LIKE %數(shù)據(jù)庫%ORDER BY dj DESC4.匯總查詢（1）使用聚集函數(shù) 【例】 查詢圖書表中計算機類圖書的冊數(shù)。SELECT count(*) AS 總冊數(shù)FROM tsWHERE lb =計算機類（2）使用GROUP BY子句分類匯總 語法格式如下：GRO

16、UP BY 列名HAVING 篩選條件表達式其中“HAVING 篩選條件表達式”表示對生成的組篩選后，再對滿足條件的組進行統(tǒng)計。（3）使用COMPUTE子句明細分類匯總 語法格式如下：COMPUTE ,n BY 列名 ,n【例】 統(tǒng)計圖書訂購表中客戶訂購的圖書總數(shù)，并顯示明細信息。SELECT khbh,tsbh,dgslFROM tsdgCOMPUTE sum(dgsl)【例】 統(tǒng)計圖書訂購表中每個客戶訂購的圖書總數(shù)，并顯示明細信息。SELECT khbh,tsbh,dgslFROM tsdgORDER BY khbhCOMPUTE sum(dgsl) BY khbh 其語法格式有兩種：一種

17、是ANSI語法形式，另一種是SQL Server語法形式。ANSI連接查詢的語法格式如下：SELECT 目標列FROM 表名1連接類型 JOIN 表名2 JOIN 表名nON 連接條件SQL Server連接查詢的語法格式如下：SELECT 目標列FROM 表名1,nWHERE 查詢條件 AND |OR 連接條件n。2）刪除單元格 （二）連接查詢1.內連接（INNER JOIN） 內連接是將多個表中的共同列的值進行比較，把滿足連接條件的記錄橫向連接起來，作為查詢結果。當未指明連接類型時，默認為內連接。【例】 查詢訂購日期在2010-5-10之后訂購圖書的客戶姓名、圖書名、訂購數(shù)量。SELECT

18、 khxm,tsm,dgslFROM kh,tsdg,tsWHERE tsdg.khbh=kh.khbh AND tsdg.tsbh=ts.tsbh AND dgrq2010-5-102.外連接（OUTER JOIN）（1）左外連接（LEFT OUTER JOIN）【例】 查詢同一個圖書編號的圖書訂購數(shù)量大于15冊的圖書編號（thbh）、圖書名(tsm)和訂購數(shù)量（dgsl）。SELECT ts.tsbh,tsm,dgslFROM ts LEFT JOIN tsdg ON ts.tsbh =tsdg.tsbh AND dgsl152.外連接（OUTER JOIN）（1）左外連接（LEFT OU

19、TER JOIN）【例】 查詢同一個圖書編號的圖書訂購數(shù)量大于15冊的圖書編號（thbh）、圖書名(tsm)和訂購數(shù)量（dgsl）。SELECT ts.tsbh,tsm,dgslFROM ts LEFT JOIN tsdg ON ts.tsbh =tsdg.tsbh AND dgsl15（2）右外連接（RIGHT OUTER JOIN）（3）全外連接（FULL OUTER JOIN）。 3.自連接（JOIN）自連接，是使用內連接或外連接把一個表中的行與該表中另外一些行連接起來，主要用于查詢比較相同的信息。 【例】 在查詢結果集的每一行上顯示每個客戶訂購的兩種圖書編號的訂購數(shù)量。SELECT X

20、.khbh,X.tsbh,X.dgsl,Y.tsbh,Y.dgslFROM tsdg X JOIN tsdg Y ON X.khbh=Y.khbhWHERE X.tsbh Y.tsbh（三）嵌套查詢1.非相關子查詢非相關子查詢是指子查詢的執(zhí)行不依賴于父查詢，并且每個子查詢都只執(zhí)行一次。 2.相關子查詢相關子查詢指的是子查詢的執(zhí)行依賴于父查詢，每個子查詢都只執(zhí)行多次。其執(zhí)行方式為：首先父查詢將值傳遞給子查詢后，執(zhí)行子查詢，然后根據(jù)子查詢的執(zhí)行結果判斷父查詢的條件是否滿足要求，如果條件為真則顯示結果，否則不顯示，重復直到父查詢的表全部處理完。這類查詢主要針對的是帶有NOTEXISTS運算符的嵌套查

21、詢?！纠?查詢客戶編號為kh0002的客戶所訂購圖書的圖書編號、圖書名。SELECT tsbh,tsmFROM tsWHERE exists(SELECT * FROM tsdg WHERE ts.tsbh=tsdg.tsbh AND khbh=kh0002)（四）組合查詢組合查詢，就是將多個SELECT語句的查詢結果再進行集合運算。SQL的組合查詢運算符有UNION（并操作）、INTERSECT（交操作）、EXCEPT（差操作）3種。 【例】 查詢訂購了圖書編號為ts10001，但沒有訂購圖書編號為ts10002圖書的客戶的編號。解決本例題有兩種方法。（1）使用EXCEPT操作，T-SQL

22、語句如下：SELECT khbhFROM tsdg XWHERE tsbh=ts10001 EXCEPTSELECT khbhFROM tsdg WHERE tsbh=ts10002（2）使用EXISTS運算符，T-SQL語句如下：SELECT khbhFROM tsdg XWHERE tsbh=ts10001 and NOT EXISTS (SELECT * FROM tsdg Y WHERE X.khbh=Y.khbh AND tsbh=ts10002) 第四節(jié) 使用T-SQL進行數(shù)據(jù)更新一、插入數(shù)據(jù)（一）使用常量插入單個元組語法格式如下：INSERTINTO (,)VALUES(, )

23、【例】 向tsdgdb數(shù)據(jù)庫的圖書表（ts）插入兩行數(shù)據(jù)。INSERT INTO ts (tsbh,tsm,lb,cbs,dj,zz,isbn)VALUES (ts20002,電子商務案例分析教程,管理類,電子工業(yè)出版社,29,斯林勝, 9787121102486)INSERT INTO tsVALUES (ts20003,新編電子商務概論,管理類,電子工業(yè)出版社,20,孟澤云, 9787121111570) 語法格式如下：INSERTINTO (，) （二）使用子查詢插入多個元組【例】把books數(shù)據(jù)庫中的圖書表數(shù)據(jù)記錄插入tsdgdb數(shù)據(jù)庫中ts表中。USE tsdgdbGOINSERT

24、INTO tsSELECT *FROM books.dbo.圖書表GO二、修改數(shù)據(jù)修改數(shù)據(jù)的T-SQL語句的一般格式為： UPDATE SET =，=，n WHERE 【例】 將將計算機類圖書統(tǒng)一降價5元。UPDATE ts SET dj=dj-5WHERE lb= 計算機類三、刪除數(shù)據(jù)數(shù)據(jù)刪除的語法格式如下：DELETE FROMWHERE T-SQL的擴展語句刪除記錄的語法格式為：DELETE FROM JOIN ON 連接條件WHERE 【例】 刪除客戶張燕的所有訂購記錄。DELETE tsdgFROM tsdg INNER JOIN kh ON tsdg.khbh=kh.khbhWHERE khxm=張燕或使用帶有嵌套查詢的DE