第14章 T-SQL 高級(jí)查詢

1、第第1414章章 T-SQL T-SQL 高級(jí)查詢高級(jí)查詢本章內(nèi)容n使用聚合函數(shù)（Aggregate Functions ）n使用group by分組n使用having篩選結(jié)果n連接多個(gè)表n使用子查詢使用聚合函數(shù)n什么是聚合函數(shù)？n聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算并返回單一的值。n有哪些聚合函數(shù)？AVG返回組中值的平均值?？罩祵⒈缓雎?。COUNT返回組中項(xiàng)目的數(shù)量。 COUNT (*)返回符合搜索條件的行的數(shù)目，包括含有空值的行MAX返回表達(dá)式的最大值。MIN返回表達(dá)式的最小值。 SUM返回表達(dá)式中所有值的和。使用聚合函數(shù)舉例（一）nCount語(yǔ)法：COUNT ( ALL | DISTINCT exp

2、ression | * ) n查詢titles表中書名的總數(shù)USE pubsGOSELECT COUNT(title) FROM titlesGO使用聚合函數(shù)舉例（二）nAVG語(yǔ)法：AVG ( ALL | DISTINCT expression ) n計(jì)算出 titles 表中所有商業(yè)類書籍的平均價(jià)格USE pubsGOSELECT AVG(price)FROM titlesWHERE type = business使用聚合函數(shù)注意n除 COUNT (*)函數(shù)之外，聚合函數(shù)忽略空值。n舉例： SELECT COUNT(*) FROM titlesSELECT COUNT(price) FROM

3、 titlesSelect avg(price) from titlesSelect sum(price)/count(price) from titlesSelect sum(price)/count(*) from titles課堂練習(xí)n1. 找出pubs數(shù)據(jù)庫(kù)titles表中計(jì)算機(jī)類圖書中價(jià)格最高的圖書的價(jià)格。n2. 查詢titles表中有幾類圖書。使用group by分組n什么時(shí)候使用group by子句？n當(dāng)需要按某一列數(shù)據(jù)的值進(jìn)行分類，在分類的基礎(chǔ)上對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，就需要使用group by子句。使用group by舉例n要求：查詢pub數(shù)據(jù)庫(kù)的titles表，按書的種類分類，求出

4、每種類型的書的平均價(jià)格。USE pubsGOSELECT type,avg(price) FROM titlesGROUP BY typeGO使用group by注意：n指定 GROUP BY 時(shí)，選擇列表中任一非聚合表達(dá)式內(nèi)的所有列都應(yīng)包含在 GROUP BY 列表中。n選擇列表中的列如果不在聚合函數(shù)里面，就必須在GROUP BY列表里面。否則語(yǔ)法上雖然不會(huì)出錯(cuò)，但是執(zhí)行起來會(huì)出錯(cuò)。使用group by舉例（二）USE pubsGOSELECT type,pub_id, avg(price) FROM titlesgroup by typeGOERROR!使用group by舉例（三）USE

5、 pubsGOSELECT type,pub_id, avg(price) FROM titlesgroup by type,pub_idorder by 1,2GOOK!課堂練習(xí)n3. 查詢pubs數(shù)據(jù)庫(kù)authors表，要求按照州進(jìn)行分類，查找每個(gè)州有幾名作者。n4. 查詢pubs數(shù)據(jù)庫(kù)titles表，要求按照出版商id進(jìn)行分類，查找每個(gè)出版商的書到目前為止的銷售額總和（ytd_sales）使用having篩選結(jié)果n當(dāng)完成對(duì)數(shù)據(jù)結(jié)果的查詢和統(tǒng)計(jì)后，可以使用having關(guān)鍵字對(duì)查詢和計(jì)算的結(jié)果進(jìn)行進(jìn)一步的篩選。n注意having和where的區(qū)別nWHERE 子句搜索條件在進(jìn)行分組操作之前應(yīng)

6、用；n而 HAVING 搜索條件在進(jìn)行分組操作之后應(yīng)用。nHAVING 語(yǔ)法與 WHERE 語(yǔ)法類似，但 HAVING 可以包含聚合函數(shù)。nHAVING 子句可以引用選擇列表中出現(xiàn)的任意項(xiàng)。使用having舉例（一）n查詢得到本年度截止到目前的銷售額超過 $40,000 的出版商。USE pubsSELECT pub_id, total = SUM(ytd_sales)FROM titlesGROUP BY pub_idHAVING SUM(ytd_sales) 40000使用having舉例（二）n查詢價(jià)格超過10美元的書的種類，以及他們的平均價(jià)格。USE pubsGOSELECT type

7、,avg(price) 平均價(jià)格 FROM titlesWHERE price 10GROUP BY typeGO使用having舉例（三）n在前一個(gè)查詢的結(jié)果的基礎(chǔ)上，找出平均價(jià)格大于18美元的書的種類。USE pubsGOSELECT type,avg(price) 平均價(jià)格 FROM titlesWHERE price 10GROUP BY typeHAVING avg(price) 18GO課堂練習(xí)n5. 在pubs數(shù)據(jù)庫(kù)的titles表中，找出平均價(jià)格大于18美元的書的出版商id。n6. 在pubs數(shù)據(jù)庫(kù)的titles表中，找出最高價(jià)格大于20美元的書的種類 。連接多個(gè)表n連接查詢多

8、個(gè)表中的數(shù)據(jù)n使用表的別名n連接多個(gè)結(jié)果集連接查詢多個(gè)表中的數(shù)據(jù)n什么是連接n內(nèi)連接（重點(diǎn)掌握）n外連接n交叉連接什么是連接n連接通過擴(kuò)展select語(yǔ)句的from子句，可以從多個(gè)表中選擇字段。n語(yǔ)法：FROM first_table second_table ON (join_condition)連接的類型n內(nèi)連接：INNER JOIN（默認(rèn)類型）n外連接：OUTER JOINn左外連接：LEFT OUTER JOINn右外連接：RIGHT OUTER JOINn全外連接：FULL OUTER JOIN n交叉連接：CROSS JOIN內(nèi)連接n僅顯示兩個(gè)聯(lián)接表中的匹配行的聯(lián)接。n注意事項(xiàng)：n

9、內(nèi)連接是SQL Server缺省的連接方式，可以把INNER JOIN簡(jiǎn)寫成JOIN；n在連接的條件中不要指定空值（NULL），因?yàn)榭罩岛推渌刀疾粫?huì)相等。內(nèi)連接舉例（一）n查找pubs數(shù)據(jù)庫(kù)中title和au_id的對(duì)應(yīng)關(guān)系。n分析：n含有title列的表：titles，titleauthor；n含有au_id列的表：titleauthor，authors。假設(shè)我們這樣寫Use pubsgoSelect title,au_idFrom titles,authorsGo想不想看看什么結(jié)果？應(yīng)該怎么考慮n在兩個(gè)表之間建立一種連接路徑title_idtitletypeau_idau_lnameau

10、_fnameau_idtitle_idau_ordTitles表authors表Titleauthor表應(yīng)該怎么寫方法一：Select titles.title,titleauthor.au_idFrom titles,titleauthorWhere titles.title_id = titleauthor.title_id方法二：Select titles.title,titleauthor.au_idFrom titles INNER JOIN titleauthorON titles.title_id=titleauthor.title_id內(nèi)連接連接舉例（二）n可以聯(lián)接 title

11、s 表和 publishers 表以創(chuàng)建顯示每個(gè)書名的出版商名稱的結(jié)果集。n注意：在內(nèi)聯(lián)接中，結(jié)果集內(nèi)不包含沒有出版商信息的書名，也不包含沒有書名的出版商。SELECT title, pub_nameFROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id使用表的別名n為什么使用表的別名？n增加語(yǔ)句的可讀性；n有利于編寫復(fù)雜的表連接操作語(yǔ)句；n語(yǔ)法：ntable_name AS table_aliasntable_name table_alias 使用表的別名舉例Select titles.title_id,

12、publishers.pub_nameFrom titles JOIN publishersON titles.pub_id=publishers.pub_idSelect t.title_id, p.pub_nameFrom titles t JOIN publishers pON t.pub_id=p.pub_idSelect t.title_id, p.pub_nameFrom titles AS t JOIN publishers AS pON t.pub_id=p.pub_id課堂練習(xí)n7. 找出title_id和pub_name的對(duì)應(yīng)關(guān)系。（查詢每一本書編號(hào)對(duì)應(yīng)的出版商）n8. 找

13、出title和pub_name的對(duì)應(yīng)關(guān)系。n9. 查詢每個(gè)作者的編號(hào)，姓名，所出的書的編號(hào)，并對(duì)結(jié)果排序。(注意使用表的別名注意使用表的別名 )外連接n為什么使用外連接n僅當(dāng)至少有一個(gè)同屬于兩表的行符合聯(lián)接條件時(shí)，內(nèi)聯(lián)接才返回行。n內(nèi)聯(lián)接消除與另一個(gè)表中的任何行不匹配的行。n外聯(lián)接會(huì)返回 FROM 子句中提到的至少一個(gè)表或視圖的所有行，只要這些行符合任何 WHERE 或 HAVING 搜索條件。外連接舉例（一）n假設(shè)在 city 列上聯(lián)接 authors 表和 publishers 表。結(jié)果只顯示在出版商所在城市居住的作者。USE pubsSELECT a.au_fname, a.au_lna

14、me, p.pub_nameFROM authors a INNER JOIN publishers pON a.city = p.city外連接舉例（二）n但是如果使用左外連接USE pubsSELECT a.au_fname, a.au_lname, p.pub_nameFROM authors a LEFT OUTER JOIN publishers p ON a.city = p.cityORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC交叉連接（cross join）n交叉聯(lián)接返回左表中的所有行，左表中的每一行與右表中的所有

15、行組合。n交叉聯(lián)接也稱作笛卡爾積。n如果表A有N條記錄，表B有M條記錄，那么交叉連接產(chǎn)生的結(jié)果集將會(huì)有N*M條記錄。連接多個(gè)結(jié)果集n使用UNION運(yùn)算符，把多個(gè)select語(yǔ)句返回的結(jié)果集合并到一個(gè)結(jié)果集中。n每一個(gè)查詢必須：n類似的數(shù)據(jù)類型（兼容的數(shù)據(jù)類型）n相同的列數(shù)n選擇列表中的列具有相同的順序連接多個(gè)結(jié)果集舉例（一）use pubsgoselect pub_id,pub_name from publishersunionselect title_id,title from titleswhere price 20連接多個(gè)結(jié)果集并排序n如果要對(duì)UNION操作的結(jié)果集進(jìn)行排序，必須把ord

16、er by子句寫在最后的select子句后面。n選擇排序的列必須是第一個(gè)select子句的列。n觀察多個(gè)結(jié)果集合并后的結(jié)果集的列標(biāo)題。連接多個(gè)結(jié)果集舉例（二）use pubsgoselect pub_id,pub_name from publishersunionselect title_id,title from titleswhere price 20order by pub_name保留合并后結(jié)果集重復(fù)的行nUNION子句會(huì)自動(dòng)刪除結(jié)果集中重復(fù)的行。n使用ALL關(guān)鍵字保留所有的行。連接多個(gè)結(jié)果集舉例（二）Use pubsgoSelect state from authorsUNION A

17、LLSelect state from publishersOrder by statego課堂練習(xí)n10. 從authors表中選擇state,city列，從publisher表中選擇state,city列，并把兩個(gè)查詢的結(jié)果合并為一個(gè)結(jié)果集，并對(duì)結(jié)果集按city列、state列進(jìn)行排序。n11. 對(duì)上面的查詢語(yǔ)句作修改，保留所有重復(fù)的記錄。使用子查詢（嵌套查詢）n子查詢介紹（subquery）n把子查詢用作派生的表n把子查詢用作表達(dá)式n使用IN的子查詢n用子查詢關(guān)聯(lián)數(shù)據(jù)n使用EXISTS和NOT EXISTS的子查詢子查詢介紹n什么是子查詢？n子查詢是一個(gè) SELECT 查詢，它返回單個(gè)值

18、且嵌套在 SELECT、INSERT、UPDATE、DELETE 語(yǔ)句或其它子查詢中。n為什么使用子查詢？n把一個(gè)復(fù)雜的查詢分解成一系列的邏輯步驟；n當(dāng)一個(gè)查詢依賴于另一個(gè)查詢時(shí)，子查詢會(huì)很有用。把子查詢用作派生的表n子查詢產(chǎn)生一個(gè)結(jié)果集，我們可以把它看成一個(gè)表（派生表），用來代替FROM子句中的表。把子查詢用作派生的表舉例USE northwindSELECT T.orderid, T.customerid FROM ( SELECT orderid, customerid FROM orders ) AS TGO把子查詢用作表達(dá)式n任何允許使用表達(dá)式的地方都可以使用子查詢。USE pubs

19、SELECT title, price ,( SELECT AVG(price) FROM titles) AS average ,price-(SELECT AVG(price) FROM titles) AS differenceFROM titlesWHERE type=popular_compGO使用子查詢的格式匯總n包括子查詢的語(yǔ)句通常采用以下格式中的一種： nWHERE expression NOT IN (subquery)nWHERE expression comparison_operator (subquery)nWHERE NOT EXISTS (subquery) 使用

20、IN的子查詢n通過 IN（或 NOT IN）引入的子查詢結(jié)果是一列零值或更多值。n子查詢返回結(jié)果之后，外部查詢將利用這些結(jié)果。使用IN的子查詢舉例n查找所有曾出版過商業(yè)書籍的出版商的名稱。USE pubsSELECT pub_nameFROM publishersWHERE pub_id IN (SELECT pub_id FROM titles WHERE type = business)使用IN的子查詢分析n首先，內(nèi)部查詢返回出版過商業(yè)書籍的出版商的標(biāo)識(shí)號(hào)（1389 和 0736）。SELECT pub_id FROM titlesWHERE type = businessn然后，這些值被

21、代入外部查詢中，在 publishers 中查找與上述標(biāo)識(shí)號(hào)相配的名字。SELECT pub_name FROM publishersWHERE pub_id in (1389, 0736)子查詢(subquery)與連接(join)n許多包含子查詢的 T-SQL 語(yǔ)句都可以改為用聯(lián)接表示。n而其它一些問題只能由子查詢提出。n在 Transact-SQL 中，包括子查詢的語(yǔ)句和不包括子查詢但語(yǔ)義上等效的語(yǔ)句在性能方面通常沒有區(qū)別。n但是，在一些必須檢查存在性的情況中，使用聯(lián)接會(huì)產(chǎn)生更好的性能。使用連接代替子查詢n查找所有曾出版過商業(yè)書籍的出版商的名稱。（使用連接）select pub_name

22、 from publishers join titleson publishers.pub_id = titles.pub_idwhere titles.type=business只能使用聯(lián)接的情況n使用聯(lián)接而不使用子查詢處理該問題及類似問題的一個(gè)不同之處在于，聯(lián)接使您可以在結(jié)果中顯示多個(gè)表中的列。只能使用聯(lián)接的情況舉例n查找所有曾出版過商業(yè)書籍的出版商的名稱和書籍的書名。USE pubsSELECT pub_name, titleFROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id AND type =

23、business課堂練習(xí)n12. 顯示所有來自CA州的作家的全部作品和作家代號(hào)。（使用IN，和連接兩種方法）用子查詢關(guān)聯(lián)數(shù)據(jù)n許多查詢都可以通過執(zhí)行一次子查詢并將結(jié)果值代入外部查詢的 WHERE 子句進(jìn)行評(píng)估。n在包括相關(guān)子查詢的查詢中，子查詢依靠外部查詢獲得值。n這意味著子查詢是重復(fù)執(zhí)行的，為外部查詢可能選擇的每一行均執(zhí)行一次。用子查詢關(guān)聯(lián)數(shù)據(jù)舉例（一）n查找獲得某本書 100 % 共享版稅 (royaltyper) 的所有作者名。USE pubsSELECT au_lname, au_fnameFROM authorsWHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id)用子查詢關(guān)聯(lián)數(shù)據(jù)舉例（二）n查詢定購(gòu)了23號(hào)產(chǎn)品50件以上的客戶。USE northwindSELECT orderid, customeridFROM orders AS or1WHERE 50 (SELECT quantity FROM order details AS od WHERE or1.orderid = od.orderid AND ductid = 23)