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

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

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

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

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

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

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

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

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

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

10、代碼窗口輸入如下語句：CREATE VIEW v_lbAS SELECT *FROM ts WHERE LB=計(jì)算機(jī)類（2）執(zhí)行語句，看到“命令成功完成”的消息。在查詢編輯器代碼窗口輸入如下查詢視圖語句，執(zhí)行結(jié)果如圖所示。（二）T-SQL語句修改和刪除視圖1.修改視圖其語法格式如下：ALTER VIEW （,）AS 子查詢WITH CHECK OPTION2.刪除視圖刪除視圖通常需要使用DROP VIEW語句進(jìn)行，其語法格式如下：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)建一個(gè)唯一索引tsm_index，依據(jù)字段tsm進(jìn)行升序排序。 （1）在查詢編輯器代碼窗口輸入如下語句：USE tsdgdbCREATE UNIQUE INDEX tsm_index ON ts（tsm）GO（2）執(zhí)行語句，刷新“對(duì)象資源管理器”，即可看到所創(chuàng)建的索引。（二）使用T-SQL語句修改和刪除索引1.修改索引 其語法格式如下：ALTER INDEX 索引名 ON 表名|視圖名REBUILD|REORGANIZE|DISABLE2.刪除索引 其語法格式如下：D

12、ROP INDEX 索引名五、存儲(chǔ)過程定義（一）T-SQL語句創(chuàng)建存儲(chǔ)過程 其語法格式為：CREATE PROCEDURE 參數(shù) 數(shù)據(jù)類型WITH RECOMPILE|ENCRYPTIONAS T-SQL語句塊 （二）使用T-SQL語句執(zhí)行存儲(chǔ)過程在SQL Server中使用EXECUTE語句來執(zhí)行存儲(chǔ)過程，其語法格式如下： EXECUTE 存儲(chǔ)過程名 參數(shù)=參量值|變量 ,n六、觸發(fā)器定義（一）觸發(fā)器的分類1.DML觸發(fā)器 2.DDL觸發(fā)器 （二）T-SQL語句創(chuàng)建觸發(fā)器 1.創(chuàng)建DML觸發(fā)器語法格式如下： CREATE TRIGGER 觸發(fā)器名 ON 表|視圖|服務(wù)器|數(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服務(wù)器|數(shù)據(jù)庫FOR | AFTER AS T-SQL語句第三節(jié) 使用T-SQL進(jìn)行數(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 表名【例】 查詢客戶表的客戶編號(hào)（khbh）、客戶姓名（khxm）、聯(lián)系電話（lxdh）。USE tsdgdbGOSELECT khbh,khxm,lxdhFROM khGO2.選擇表中若干行（1）通過TOP和DISTINCT關(guān)鍵字限制返回的行數(shù) 【例】 查詢圖書表（ts）的前兩條記錄信息。SELECT top 2*FROM ts（2）查詢滿足條件的元組。 基本的SELECT語句格式如下：SELECT 字段列表 FROM 表名 WHERE 查詢條件【例】 查詢計(jì)算機(jī)類或管理類圖書定價(jià)在20元以上的圖書信息。SELE

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

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

17、是ANSI語法形式，另一種是SQL Server語法形式。ANSI連接查詢的語法格式如下：SELECT 目標(biāo)列FROM 表名1連接類型 JOIN 表名2 JOIN 表名nON 連接條件SQL Server連接查詢的語法格式如下：SELECT 目標(biāo)列FROM 表名1,nWHERE 查詢條件 AND |OR 連接條件n。2）刪除單元格 （二）連接查詢1.內(nèi)連接（INNER JOIN） 內(nèi)連接是將多個(gè)表中的共同列的值進(jìn)行比較，把滿足連接條件的記錄橫向連接起來，作為查詢結(jié)果。當(dāng)未指明連接類型時(shí)，默認(rèn)為內(nèi)連接?！纠?查詢訂購日期在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）【例】 查詢同一個(gè)圖書編號(hào)的圖書訂購數(shù)量大于15冊的圖書編號(hào)（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）【例】 查詢同一個(gè)圖書編號(hào)的圖書訂購數(shù)量大于15冊的圖書編號(hào)（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）自連接，是使用內(nèi)連接或外連接把一個(gè)表中的行與該表中另外一些行連接起來，主要用于查詢比較相同的信息。 【例】 在查詢結(jié)果集的每一行上顯示每個(gè)客戶訂購的兩種圖書編號(hào)的訂購數(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.非相關(guān)子查詢非相關(guān)子查詢是指子查詢的執(zhí)行不依賴于父查詢，并且每個(gè)子查詢都只執(zhí)行一次。 2.相關(guān)子查詢相關(guān)子查詢指的是子查詢的執(zhí)行依賴于父查詢，每個(gè)子查詢都只執(zhí)行多次。其執(zhí)行方式為：首先父查詢將值傳遞給子查詢后，執(zhí)行子查詢，然后根據(jù)子查詢的執(zhí)行結(jié)果判斷父查詢的條件是否滿足要求，如果條件為真則顯示結(jié)果，否則不顯示，重復(fù)直到父查詢的表全部處理完。這類查詢主要針對(duì)的是帶有NOTEXISTS運(yùn)算符的嵌套查

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

22、語句如下：SELECT khbhFROM tsdg XWHERE tsbh=ts10001 EXCEPTSELECT khbhFROM tsdg WHERE tsbh=ts10002（2）使用EXISTS運(yùn)算符，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進(jìn)行數(shù)據(jù)更新一、插入數(shù)據(jù)（一）使用常量插入單個(gè)元組語法格式如下：INSERTINTO (,)VALUES(, )

23、【例】 向tsdgdb數(shù)據(jù)庫的圖書表（ts）插入兩行數(shù)據(jù)。INSERT INTO ts (tsbh,tsm,lb,cbs,dj,zz,isbn)VALUES (ts20002,電子商務(wù)案例分析教程,管理類,電子工業(yè)出版社,29,斯林勝, 9787121102486)INSERT INTO tsVALUES (ts20003,新編電子商務(wù)概論,管理類,電子工業(yè)出版社,20,孟澤云, 9787121111570) 語法格式如下：INSERTINTO (，) （二）使用子查詢插入多個(gè)元組【例】把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 【例】 將將計(jì)算機(jī)類圖書統(tǒng)一降價(jià)5元。UPDATE ts SET dj=dj-5WHERE lb= 計(jì)算機(jī)類三、刪除數(shù)據(jù)數(shù)據(jù)刪除的語法格式如下：DELETE FROMWHERE T-SQL的擴(kuò)展語句刪除記錄的語法格式為：DELETE FROM JOIN ON 連接條件WHERE 【例】 刪除客戶張燕的所有訂購記錄。DELETE tsdgFROM tsdg INNER JOIN kh ON tsdg.khbh=kh.khbhWHERE khxm=張燕或使用帶有嵌套查詢的DE