使用transactsql查詢數(shù)據(jù)庫

1、第5章使用Transact-SQL查詢數(shù)據(jù)庫SQL Server 2005數(shù)據(jù)庫開發(fā)與實(shí)現(xiàn)第1章：SQL Server 2005啟航第2章：安裝和配置SQL Server 2005第3章：創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)庫文件第4章：創(chuàng)建數(shù)據(jù)類型和表第5章：使用Transact-SQL查詢數(shù)據(jù)庫第6章：創(chuàng)建和優(yōu)化索引第7章：實(shí)現(xiàn)數(shù)據(jù)完整性第8章：實(shí)現(xiàn)視圖第9章：實(shí)現(xiàn)存儲過程和函數(shù)第10章：使用 XML第11章：災(zāi)難恢復(fù)第12章：管理安全性第13章：監(jiān)視 SQL Server第14章：維持高可用性第15章：自動執(zhí)行管理任務(wù)第16章：使用 Service Broker第17章：傳輸數(shù)據(jù)第18章：復(fù)制功能簡介第5章

2、：使用Transact-SQL查詢數(shù)據(jù)庫檢索數(shù)據(jù)數(shù)據(jù)分組與匯總多表聯(lián)接子查詢修改數(shù)據(jù)實(shí)驗(yàn)習(xí)題可以使用SELECT語句指定想要從表中檢索的數(shù)據(jù)的行和列使用SELECT語句檢索數(shù)據(jù)SELECT ALL | DISTINCT FROM ,.,n WHERE 5.1.1 使用SELECT語句檢索數(shù)據(jù)使用WHERE子句過濾數(shù)據(jù)使用比較運(yùn)算符使用字符串比較符使用邏輯運(yùn)算符檢索一定范圍內(nèi)的值使用值列表作為搜索條件檢索未知值USE AdventureWorksSELECT ProductID, NAMEFROM .Production.ProductWHERE Class =H5.1.2 使用WHERE子句設(shè)

3、置結(jié)果集格式可以通過設(shè)置結(jié)果集格式，改善結(jié)果集的可讀性使用ORDER BY使用DISTINCT消除重復(fù)行改變列名使用字面值5.1.3 設(shè)置結(jié)果集格式課堂練習(xí)：檢索數(shù)據(jù)本課堂練習(xí)的目標(biāo)是使用SELECT語句和相關(guān)的子句在AdventureWorks 數(shù)據(jù)庫中獲得數(shù)據(jù)。使用SELECT語句獲得數(shù)據(jù)使用各種比較符號過濾數(shù)據(jù)格式化結(jié)果集第5章：使用Transact-SQL查詢數(shù)據(jù)庫檢索數(shù)據(jù)數(shù)據(jù)分組與匯總多表聯(lián)接子查詢修改數(shù)據(jù)實(shí)驗(yàn)習(xí)題使用TOP n列出前n個(gè)記錄可以用TOP n關(guān)鍵字列出結(jié)果集中前n個(gè)記錄USE AdventureWorksSELECT Top 5 Salesorderid, produ

4、ctid, OrderQty FROM Sales.SalesOrderDetail ORDER BY OrderQty DESCGO5.2.1 使用TOP n列出前n個(gè)記錄使用聚合函數(shù)聚合函數(shù)的功能是計(jì)算平均值和總和COUNTMIN 、 MAXSUM 、 AVG5.2.2 使用聚合函數(shù)GROUP BY的基礎(chǔ)知識如果想在一列中生成多個(gè)匯總值，可以使用聚合函數(shù)與GROUP BY子句USE AdventureWorksSELECT Size, AVG(ListPrice) AS Average ListPriceFROM Production.ProductGROUP BY SizeORDER B

5、Y Size5.2.3 GROUP BY的基礎(chǔ)知識聯(lián)合使用GROUP BY子句和HAVING子句當(dāng)使用HAVING子句時(shí)，注意以下事項(xiàng)和原則：HAVING子句只有與GROUP BY子句聯(lián)用才能對分組進(jìn)行約束。只使用HAVING子句而不使用GROUP BY子句是沒有意義的。可以引用任何出現(xiàn)在選擇列表中的列。不要與HAVING子句一起使用ALL關(guān)鍵字，因?yàn)镠AVING子句會忽略ALL關(guān)鍵字而返回只滿足自己條件的分組。5.2.4 聯(lián)合使用GROUP BY子句和HAVING子句在結(jié)果集中生成匯總值使用帶有ROLLUP運(yùn)算符的GROUP BY子句使用帶有CUBE運(yùn)算符的GROUP BY子句使用GROUP

6、ING函數(shù)5.2.5 在結(jié)果集中生成匯總值使用COMPUTE和COMPUTE BY子句生成某一列的明細(xì)值和匯總值的報(bào)表為組中的子集生成明細(xì)值和匯總值的報(bào)表5.2.6 使用COMPUTE和COMPUTE BY子句推薦操作當(dāng)使用子句和運(yùn)算符匯總數(shù)據(jù)時(shí)，應(yīng)注意以下問題：對經(jīng)常聚合的列進(jìn)行索引可以提高查詢效率。例如，增加quantity列的索引能加快匯總操作的執(zhí)行，甚至在使用了ROLLUP運(yùn)算符時(shí)也是如此。應(yīng)避免對包含空值的列中使用聚合函數(shù)，因?yàn)榻Y(jié)果集可能并沒有正確地返回你要查詢的數(shù)據(jù)。在結(jié)果集中使用ORDER BY子句可保證返回?cái)?shù)據(jù)的排列順序。如果不使用ORDER BY子句，SQL Server將不

7、保證結(jié)果順序。因?yàn)镽OLLUP運(yùn)算符比CUBE運(yùn)算符更高效，所以只要可能就應(yīng)使用它。ROLLUP運(yùn)算符之所以高效是因?yàn)樗褏R總數(shù)據(jù)作為具體數(shù)據(jù)處理。CUBE運(yùn)算符在執(zhí)行時(shí)需要進(jìn)行大量的運(yùn)算，所以它會占用更多的資源。使用COMPUTE或COMPUTE BY子句，是因?yàn)樗鼈兛梢杂兄诓榭春洼敵鲇糜跍y試應(yīng)用程序的結(jié)果集。但是，它們產(chǎn)生的額外匯總記錄不符合關(guān)系型格式，所以它們不適合作為產(chǎn)品數(shù)據(jù)庫的輸出。5.2.7 推薦操作課堂練習(xí)：數(shù)據(jù)分組與匯總本課堂練習(xí)的目標(biāo)是對AdventureWorks數(shù)據(jù)庫進(jìn)行數(shù)據(jù)分組與匯總。使用聚合函數(shù)匯總數(shù)據(jù)使用GROUP匯總分組數(shù)據(jù)第5章：使用Transact-SQL查

8、詢數(shù)據(jù)庫檢索數(shù)據(jù)數(shù)據(jù)分組與匯總多表聯(lián)接子查詢修改數(shù)據(jù)實(shí)驗(yàn)習(xí)題使用表的別名使用表的別名可以增強(qiáng)腳本的可讀性，有利于編寫復(fù)雜聯(lián)接，同時(shí)簡化對Transact-SQL的維護(hù)SELECT * FROM server.database.schema.tableAS table_alias5.3.1 使用表的別名組合多個(gè)表中的數(shù)據(jù)聯(lián)接概述使用內(nèi)聯(lián)接使用外聯(lián)接使用交叉聯(lián)接聯(lián)接兩個(gè)以上的表自聯(lián)接（Self-Join）合并多個(gè)結(jié)果集5.3.2 組合多個(gè)表中的數(shù)據(jù)推薦操作下面的推薦操作會有助于你執(zhí)行查詢：根據(jù)主鍵和外鍵聯(lián)接表當(dāng)表中使用組合鍵時(shí)，ON子句中一定要引用組合主鍵中的所有列盡量在聯(lián)接中限制表的個(gè)數(shù)。因?yàn)槟?/p>

9、要聯(lián)接的表越多，SQL Server要完成這個(gè)查詢需要的時(shí)間也就越長5.3.3 推薦操作課堂練習(xí)：使用多表聯(lián)接本課堂練習(xí)的目標(biāo)是使用Transact-SQL建立多表聯(lián)接。使用JOIN查詢多個(gè)表組合多個(gè)表中的數(shù)據(jù)第5章：使用Transact-SQL查詢數(shù)據(jù)庫檢索數(shù)據(jù)數(shù)據(jù)分組與匯總多表聯(lián)接子查詢修改數(shù)據(jù)實(shí)驗(yàn)習(xí)題子查詢介紹子查詢是一種SELECT語句的使用方法，它嵌套在SELECT、INSERT、UPDATE、DELETE語句或其他的子查詢語句中使用子查詢的原因使用聯(lián)接查詢而不使用子查詢的原因子查詢的使用方法5.4.1 子查詢介紹把子查詢用作派生表可以用子查詢產(chǎn)生一個(gè)派生表，用于代替WHERE、HA

10、VING或者FROM子句中的表USE AdventureWorksSELECT SalesOrderID, ProductID, OrderQtyFROM Sales.SalesOrderDetailWHERE UnitPrice (SELECT AVG(UnitPrice)FROM Sales.SalesOrderDetail)5.4.2 把子查詢用作派生表把子查詢用作表達(dá)式所有使用表達(dá)式的地方，都可以用子查詢代替。此時(shí)子查詢必須取值為標(biāo)量值或單個(gè)列值列表SELECT Author ID=A.au_id,Author=CONVERT(varchar(20), RTRIM(au_lname)

11、+ , + RTRIM(au_fname), stateFROM authors A WHERE A.au_id NOT IN (SELECT B.au_id FROM titleauthor B)5.4.3 把子查詢用作表達(dá)式使用子查詢關(guān)聯(lián)數(shù)據(jù)計(jì)算一個(gè)關(guān)聯(lián)子查詢模擬JOIN子句使用EXISTS和NOT EXISTS子句5.4.4 使用子查詢關(guān)聯(lián)數(shù)據(jù)推薦操作下面推薦的操作有助于你執(zhí)行高級查詢：用子查詢分解復(fù)雜的查詢。你可以通過使用子查詢的單個(gè)語句來解決一個(gè)復(fù)雜的問題。當(dāng)你的查詢依賴于另一個(gè)查詢的結(jié)果時(shí)子查詢會很有用。在相關(guān)子查詢中使用表的別名。為了區(qū)分開內(nèi)部查詢和外部查詢的表，SQLServe

12、r要求使用別名來引用易引起歧義的表名。INSERT SELECT語句可以從其他數(shù)據(jù)源將行添加到現(xiàn)有表中。使用INSERT SELECT語句，比編寫多個(gè)單行的INSERT語句的效率要高得多。盡可能使用EXISTS運(yùn)算符而不是IN運(yùn)算符，這樣可以不用檢索整個(gè)子查詢的結(jié)果集。5.4.5 推薦操作課堂練習(xí)：進(jìn)行子查詢本課堂練習(xí)的目標(biāo)是使用Transact-SQL進(jìn)行子查詢?；咀硬樵兪褂藐P(guān)聯(lián)子查詢第5章：使用Transact-SQL查詢數(shù)據(jù)庫檢索數(shù)據(jù)數(shù)據(jù)分組與匯總多表聯(lián)接子查詢修改數(shù)據(jù)實(shí)驗(yàn)習(xí)題使用事務(wù)啟動事務(wù)顯式事務(wù)自動提交事務(wù)隱式事務(wù)模式結(jié)束事務(wù)COMMITROLLBACK5.5.1 使用事務(wù)插入數(shù)

13、據(jù)用VALUES子句插入一行數(shù)據(jù)使用INSERT SELECT語句使用SELECT INTO語句創(chuàng)建表插入部分?jǐn)?shù)據(jù)5.5.2 插入數(shù)據(jù)刪除數(shù)據(jù)使用DELETE語句使用TRUNCATE TABLE 語句刪除基于其他表的行5.5.3 刪除數(shù)據(jù)更新數(shù)據(jù)根據(jù)表中數(shù)據(jù)更新行根據(jù)其他表更新行5.5.4 更新數(shù)據(jù)性能考慮在事務(wù)中發(fā)生的數(shù)據(jù)修改會影響SQLServer的性能。當(dāng)進(jìn)行數(shù)據(jù)修改時(shí)，應(yīng)注意：事務(wù)中的數(shù)據(jù)鎖定可阻止其他事務(wù)或查詢的運(yùn)行直到事務(wù)結(jié)束。修改表可以更改數(shù)據(jù)的物理存儲方式，同時(shí)導(dǎo)致事務(wù)一定會進(jìn)行數(shù)據(jù)頁的分配。如果修改具有索引的數(shù)據(jù)列，則那些列上的索引會被作為事務(wù)的一部分進(jìn)行修改。把索引放置在W

14、HERE子句中使用的列上可以改善性能。5.5.5 性能考慮推薦操作下面的推薦操作將幫助你完成基礎(chǔ)的查詢：在你真正修改數(shù)據(jù)之前要編寫一個(gè)SELECT語句。本章實(shí)驗(yàn)將驗(yàn)證哪些行會受 INSERT、UPDATE或 DELETE 語句的影響。通過把列名改為別名或使用文字替換結(jié)果集的值可提高計(jì)算結(jié)果的可讀性。這些格式選項(xiàng)只修改了數(shù)據(jù)的表達(dá)形式，而不是數(shù)據(jù)的本身。SQLServer會刪除或更新表中所有的行，除非在DELETE或UPDATE語句中包含WHERE子句。5.5.6 推薦操作課堂練習(xí)：修改數(shù)據(jù)本課堂練習(xí)的目標(biāo)是能夠使用Transact-SQL修改數(shù)據(jù)。向表中插入數(shù)據(jù)在表中刪除數(shù)據(jù)修改表中的數(shù)據(jù)處理

15、事務(wù)第5章：使用Transact-SQL查詢數(shù)據(jù)庫檢索數(shù)據(jù)數(shù)據(jù)分組與匯總多表聯(lián)接子查詢修改數(shù)據(jù)實(shí)驗(yàn)習(xí)題實(shí)驗(yàn)Adventure Works 自行車制造是一家大型跨國生產(chǎn)公司。公司的每位客戶都將在數(shù)據(jù)庫中擁有一條記錄。隨著公司市場規(guī)模的擴(kuò)大，客戶也越來越多，為了快速找到所需的客戶資料及庫存情況，表關(guān)系間的查詢使用顯得至關(guān)重要。使用SELECT語句查詢查詢個(gè)人客戶的信息。使用UPDATE、INSERT和DELETE語句修改個(gè)人客戶的信息。使用JOIN和UNION查詢商店客戶的信息。5.6實(shí)驗(yàn)第5章：使用Transact-SQL查詢數(shù)據(jù)庫檢索數(shù)據(jù)數(shù)據(jù)分組與匯總多表聯(lián)接子查詢修改數(shù)據(jù)實(shí)驗(yàn)習(xí)題習(xí)題1.小王

16、需要顯示從2001年1月1日到2001年12月31日雇傭的所有職員的姓名和雇傭日期。職員信息表tblEmployees包含列Name和列HireDate，下面哪些語句能完成該功能？A. SELECT Name, HireDate FROM tblEmployeesB. SELECT Name, HireDate FROM tblEmployees WHERE HireDate =2001-01-01OR2001-12-31C. SELECT Name, HireDate FROM tblEmployees WHERE HireDate BETWEEN 2000-12-31AND2002-01-

17、01D. SELECT Name, HireDate FROM tblEmployees WHERE HireDate DATEPART(yy, HireDate) =20015.7習(xí)題習(xí)題（續(xù)）2在SQL Server 2005 創(chuàng)建了訂單表，創(chuàng)建語句如下： CREATE TABLE 訂單表 ( 訂單代號 int IDENTITY(1,1) PRIMARY KEY, 客戶代號 int not null, 雇員代號 int not null, -經(jīng)手該筆業(yè)務(wù)的雇員的代號 訂單日期 datetime not null, 銷售金額 money not null, 備注 varchar(200) n

18、ull )小王需要獲得訂單信息列表，包括雇員代號、銷售金額和訂單日期。他想按日期從近到 早的順序顯示，并且對于每一天的訂單，按銷售金額從大到小的順序排序。假設(shè)列“銷售日期”的時(shí)間部分的值都是0，下面哪條語句能夠準(zhǔn)確地完成該任務(wù)？5.7習(xí)題習(xí)題（續(xù)）A. SELECT 雇員代號, 銷售金額, 訂單日期 FROM 訂單表 ORDER BY銷售金額, 訂單日期 DESCB. SELECT 雇員代號, 銷售金額, 訂單日期 FROM 訂單表 ORDER BY 訂單日期, 銷售金額DESCC. SELECT 雇員代號, 銷售金額, 訂單日期 FROM 訂單表 ORDER BY 訂單日期 DESC, 銷售

19、金額 DESCD. SELECT 雇員代號, 銷售金額, 訂單日期 FROM 訂單表 ORDER BY 銷售金額 DESC, 訂單日期 DES5.7習(xí)題習(xí)題（續(xù)）3現(xiàn)在要做一個(gè)電話查詢網(wǎng)頁，供人們查詢某大學(xué)的教職工的家庭電話號碼。使用者在網(wǎng)頁輸入教職工的部分姓名，網(wǎng)頁就返回匹配該查詢條件的所有教職工的姓名和電話號碼。輸入的部分姓名可以多種多樣，比如，查找“張小明”，可以輸入“張”、“小”、“明”、“張明”、“小明”等。假設(shè)教職工的姓名和電話號碼存放在表tblTelphone，局部變量PartialName存放輸入的部分姓名，請用Transact-SQL語句寫一段代碼完成該查詢。5.7習(xí)題習(xí)題（

20、續(xù)）4小王在SQL Server 2005數(shù)據(jù)庫中創(chuàng)建了訂單表，其創(chuàng)建語句如下： CREATE TABLE 訂單表 ( 訂單號 int IDENTITY(1,1) PRIMARY KEY, 雇員代號 int NOT NULL, 地區(qū)代號 int NOT NULL, 訂購日期 datetime NOT NULL, 訂購金額 money NOT NULL )銷售經(jīng)理想要獲得總的銷售金額和按地區(qū)分組的總銷售金額。下面哪條語句能完成該任務(wù)？5.7習(xí)題習(xí)題（續(xù)）A. SELECT 雇員代號,地區(qū)代號,訂購金額 FROM 訂單表 ORDER BY 地區(qū)代號 COMPUTE SUM(訂購金額)B. SELE

21、CT 雇員代號,地區(qū)代號,訂購金額 FROM 訂單表 ORDER BY 地區(qū)代號 COMPUTE SUM(訂購金額) BY 地區(qū)代號 COMPUTE SUM(訂購金額)C. SELECT 雇員代號,地區(qū)代號,SUM(訂購金額) FROM 訂單表 GROUP BY 雇員代號,地區(qū)代號D. SELECT 雇員代號,地區(qū)代號,SUM(訂購金額) FROM 訂單表 GROUP BY 雇員代號,地區(qū)代號5.7習(xí)題習(xí)題（續(xù)）5某班級的學(xué)生成績表包含 “學(xué)號”列、 “課程代號”列、 “成績”列、 “學(xué)期號”列，請統(tǒng)計(jì)學(xué)期號為“第三學(xué)期”的每個(gè)學(xué)生所有科目的平均成績。5.7習(xí)題習(xí)題（續(xù)）6小王在SQL Ser

22、ver 2005數(shù)據(jù)庫中創(chuàng)建了如下兩個(gè)表： CREATE TABLE 雇員表 ( 雇員代號 int IDENTITY(10001,1) PRIMARY KEY NONCLUSTERED, 雇員姓名 varchar(20) NOT NULL, 通信地址 varchar(200) NULL ) CREATE TABLE 訂單表 ( 訂單號 int IDENTITY(1,1) PRIMARY KEY, 雇員代號 int NOT NULL, 客戶代號 int NOT NULL, 訂購日期 datetime NOT NULL, 訂購金額 money NOT NULL )他需要獲得2003年9月1日每個(gè)雇

23、員的最高的一筆銷售金額，要求列出“雇員代號”、“雇員姓名”、“訂購日期”、最高的“訂購金額”。下面哪個(gè)語句能完成該功能？5.7習(xí)題習(xí)題（續(xù)）A. SELECT a.雇員代號,a.雇員姓名,b.訂購日期,b.訂購 金額 FROM 雇員表 AS a LEFT OUTER JOIN 訂單表 AS b ON a.雇員代號 = b.雇員代號 WHERE b.訂購日期 = 09/01/2003 AND 訂購金額 IN (SELECT MAX(訂購金額) FROM 訂單表)B. SELECT a.雇員代號,a.雇員姓名,b.訂購日期,MAX(訂購金額) FROM 雇員表 AS a LEFT OUTER JO

24、IN 訂單表 AS b ON a.雇員代號 = b.雇員代號 AND b.訂購日期 = 09/01/2003 GROUP BY a.雇員代號,a.雇員姓名,b.訂購日期5.7習(xí)題習(xí)題（續(xù)）C. SELECT a.雇員代號,a.雇員姓名,b.訂購日期,MAX(訂購金額) FROM 雇員表 AS a INNER JOIN 訂單表 AS b ON a.雇員代號 = b.雇員代號 WHERE b.訂購日期 = 09/01/2003 GROUP BY a.雇員代號,a.雇員姓名,b.訂購日期,b.訂單號D. SELECT a.雇員代號,a.雇員姓名,b.訂購日期,MAX(訂購金額) FROM 雇員表 A

25、S a INNER JOIN 訂單表 AS b ON a.雇員代號 = b.雇員代號 WHERE b.訂購日期 = 09/01/2003 AND 訂購金額 IN (SELECT MAX(訂購金額) FROM 訂單表)5.7習(xí)題習(xí)題（續(xù)）7在SQL Server 2005數(shù)據(jù)庫中創(chuàng)建了如下的一些表：CREATE TABLE 部門表( 部門號 char(2) not null PRIMARY KEY, 部門名稱 varchar(30) not null)CREATE TABLE 雇員表( 雇員代號 char(4) not null PRIMARY KEY, 雇員姓名 varchar(20) not

26、 null)CREATE TABLE 任職表( 雇員代號 char(4) not null, 部門號 char(2) not null, 開始時(shí)間 datetime not null, 備注 varchar(1000) null,CONSTRAINT PK_Works PRIMARY KEY(雇員代號,部門號, 開始時(shí)間), CONSTRAINT FK_Employees FOREIGN KEY (雇員代號) REFERENCES 雇員表(雇員代號) ON DELETE CASCADE, CONSTRAINT FK_Departments FOREIGN KEY (部門號) REFERENCES 部門表(部門號) )現(xiàn)在，人事經(jīng)理需要獲得在每個(gè)部門都工作過的雇員的名單，請寫出帶有子查詢的SQL語句來完成改任務(wù)。5.7習(xí)題習(xí)題（續(xù)）8小王在SQL Server 2005數(shù)據(jù)庫中創(chuàng)建了訂單表： CREATE TABLE 訂單表 ( 訂單號 int IDENTITY(100001,1) PRIMARY KEY, 雇員代號 int NOT NULL,