《數(shù)據(jù)庫應(yīng)用基礎(chǔ)-SQL Server 》課件-CORE-06

第六章SQLServer2008數(shù)據(jù)庫高級(jí)查詢目標(biāo)深入掌握SQL語句在SQLServer2008數(shù)據(jù)庫的高級(jí)應(yīng)用：分組查詢、連接查詢、子查詢、合并查詢等概述、專業(yè)術(shù)語本章將在基本的SQL語句使用的基礎(chǔ)上學(xué)習(xí)高級(jí)SQL語句的使用，包括分組查詢、連接查詢、子查詢、合并查詢等。分組查詢當(dāng)執(zhí)行數(shù)據(jù)統(tǒng)計(jì)時(shí)，需要將表中的數(shù)據(jù)劃分成幾個(gè)組，最終統(tǒng)計(jì)每個(gè)組的數(shù)據(jù)結(jié)果數(shù)據(jù)分組統(tǒng)計(jì)是通過GROUPBY子句、聚合函數(shù)以及HAVING子句共同實(shí)現(xiàn)商品號(hào)商品數(shù)量支付總金額110100000.002202000.00330300.00例：在EBuy系統(tǒng)，通過輸入客戶號(hào)及訂單日期的上下限，獲得指定時(shí)間內(nèi)該客戶所下訂單中每種商品的購買數(shù)量及支付總金額：使用GROUPBY簡(jiǎn)單分組查詢聚合函數(shù)：對(duì)一組數(shù)據(jù)返回單一的值常用的聚合函數(shù)函數(shù)名功能AVG求平均值COUNT計(jì)數(shù)函數(shù)，計(jì)算組中成員個(gè)數(shù)(組中記錄行數(shù))MAX求最大值MIN求最小值SUM求和說明：除COUNT函數(shù)外，聚合函數(shù)忽略空值(NULL)

使用GROUPBY簡(jiǎn)單分組查詢例：查詢EBuy系統(tǒng)中orders(訂單表)中目前的訂單總數(shù)說明：把整個(gè)表的記錄作為一個(gè)組，求記錄總數(shù)select

count(*)fromordersGROUPBY<分組表達(dá)式>GROUPBY子句及單列分組例：查詢每種商品的平均訂購數(shù)量ordidcomidamount…1110…2220…3110…4230…按商品種類分組groupbycomid求每組中商品的平均數(shù)量avg(amount)selectcomid,avg(amount)as

平均商品數(shù)fromordersgroup

bycomid注意：SELECT選擇列表中的列或表達(dá)式必須出現(xiàn)在groupby子句中使用GROUPBY簡(jiǎn)單分組查詢多列分組：是指在GROUPBY子句中使用多個(gè)列生成分組統(tǒng)計(jì)數(shù)據(jù)例：查詢每個(gè)客戶、每種商品的平均訂購商品數(shù)。selectCusID,ComID,avg(Amount)fromordersgroup

byCusID,ComID在分組中使用HAVING子句HAVING子句用于限制分組顯示結(jié)果HAVING子句必須跟在GROUPBY后頭例：查詢每個(gè)客戶、每種商品的平均訂購商品數(shù)，而且平均商品數(shù)要大于10。selectCusID,ComID,avg(Amount)fromordersgroup

byCusID,ComIDhaving

avg(Amount)>10連接查詢連接查詢是指基于兩個(gè)或兩個(gè)以上的基表或視圖的查詢連接方式主要有：內(nèi)連接，外連接連接查詢的語法規(guī)則：SELECTtable1.column,table2.columnFROMtable1[INNER|LEFT|RIGHT|FULL|CROSS]JOINtable2ONtable1.column=table2.column;INNERJOIN：表示內(nèi)連接LEFTJOIN：表示左聯(lián)接RIGHTJOIN：表示右連接FULLJOIN：表示完全連接CROSSJOIN：交叉連接ON：后跟連接條件內(nèi)連接查詢內(nèi)連接是指：當(dāng)且僅當(dāng)連接條件成立時(shí)，才在結(jié)果集中產(chǎn)生一條連接記錄。當(dāng)左表中某記錄根據(jù)連接條件在右表中沒有匹配的記錄時(shí)，該記錄便被忽略內(nèi)連接可分為：等值連接、不等連接、自連接等內(nèi)連接查詢等值連接：使用等于“=”運(yùn)算符catidcatname1辦公用品2筆記本電腦3電腦外圍設(shè)備4家用電器comidcatidcomname12DELL筆記本電腦23IBM電腦外圍設(shè)備31辦公用品14nulltianjin-棉毛織品catnamecomname筆記本電腦DELL筆記本電腦電腦外圍設(shè)備IBM電腦外圍設(shè)備辦公用品辦公用品1selecta.catname,namefromcommdity_categoryasainnerjoincommditysascona.catid=c.catidcommodity_category表commoditys表查詢結(jié)果表selecta.catname,namefromcommdity_categoryasa,commditysascwherea.catid=c.catid內(nèi)連接查詢不等連接：使用除了等于運(yùn)算符外的比較運(yùn)算符selecto.cusid,a.cusnamefromcustomerasa,ordersasowherea.cusid<>o.cusid內(nèi)連接查詢自連接：使用同一個(gè)表的列進(jìn)行比較查詢EBuy應(yīng)用系統(tǒng)中，orders（訂單表）中商品單價(jià)為100的訂單部分信息cusidcomidamountpayamount1002110100000.0010012202000.001001110100000.0010012303000.00表orders中的部分字段cusidcomidamountpayamount1002110100000.0010012202000.001001110100000.0010012303000.00selecta.cusid,id,a.amount,a.payamountfromordersasa,ordersasbwhereb.payamount=a.amount*100外連接查詢外連接：不僅包含符合連接條件的記錄，而且還包含左表或右表連接中的所有行外連接一般分為左連接（LEFTJOIN）右連接（RIGHTJOIN）完整連接（FULLJOIN）外連接查詢左連接：LEFT[OUTER]JOINcatidcatname1辦公用品2筆記本電腦3電腦外圍設(shè)備4家用電器comidcatidcomname12DELL筆記本電腦23IBM電腦外圍設(shè)備31辦公用品14NULLtianjin-棉毛織品catnamecomname筆記本電腦DELL筆記本電腦電腦外圍設(shè)備IBM電腦外圍設(shè)備辦公用品辦公用品1家用電器NULLcommodity_category表commoditys表查詢結(jié)果表selecta.catname,namefromcommdity_categoryaleftjoincommditysbona.catid=b.catid外連接查詢右連接：RIGHT[OUTER]JOINcatidcatname1辦公用品2筆記本電腦3電腦外圍設(shè)備4家用電器comidcatidcomname12DELL筆記本電腦23IBM電腦外圍設(shè)備31辦公用品14NULLtianjin-棉毛織品catnamecomname筆記本電腦DELL筆記本電腦電腦外圍設(shè)備IBM電腦外圍設(shè)備辦公用品辦公用品1NULLshanghai-棉毛織品commodity_category表commoditys表查詢結(jié)果表selecta.catname,namefromcommdity_categoryarightjoincommditysbona.catid=b.catid外連接查詢完全連接：FULL[OUTER]JOINcatidcatname1辦公用品2筆記本電腦3電腦外圍設(shè)備4家用電器comidcatidcomname12DELL筆記本電腦23IBM電腦外圍設(shè)備31辦公用品14NULLtianjin-棉毛織品catnamecomname筆記本電腦DELL筆記本電腦電腦外圍設(shè)備IBM電腦外圍設(shè)備辦公用品辦公用品1家用電器NULLNULLtianjin-棉毛織品commodity_category表commoditys表查詢結(jié)果表selecta.catname,namefromcommdity_categoryfulljoincommditysbona.catid=b.catid子查詢子查詢也稱為內(nèi)部查詢和內(nèi)部選擇，是一個(gè)SELECT查詢，包含子查詢的語句也稱為外部查詢或外部選擇。子查詢類型

[NOT]IN子查詢[NOT]EXISTS子查詢由比較運(yùn)算符引出的子查詢子查詢[NOT]IN子查詢返回零個(gè)或多個(gè)值步驟：先執(zhí)行內(nèi)部查詢，再執(zhí)行外部查詢例：查詢商品信息表中價(jià)格大于1000的商品類別catidcatname1辦公用品2筆記本電腦3電腦外圍設(shè)備4家用電器catidcomnamecomprice2DELL筆記本電腦10000.003IBM電腦外圍設(shè)備100.001辦公用品110.00NULLtianjin-棉毛織品50.00commodity_category表commoditys表selectcatid,catnamefromcommodity_categorywherecatidin( selectcatidfromcommodityswherecomprice>1000)[NOT]EXISTS子查詢判斷子查詢結(jié)果中是否有數(shù)據(jù)存在子查詢的選擇列表常使用SELECT*格式例：查詢商品類別表中有哪些商品類別在商品信息表中有具體的商品存在catidCatname1辦公用品2筆記本電腦3電腦外圍設(shè)備4家用電器catidcomnamecomprice2DELL筆記本電腦10000.003IBM電腦外圍設(shè)備100.001辦公用品110.00NULLtianjin-棉毛織品50.00commodity_category表commoditys表selectcatid,catnamefromcommodity_categoryawhereexists(select*fromcommoditysb

whereb.catid=a.catid )子查詢由比較運(yùn)算符引出的子查詢外層查詢與子查詢之間用比較運(yùn)算符進(jìn)行連接例：查詢價(jià)格低于平均價(jià)格的商品編號(hào)和名稱comidcatidcomnamecomprice12DELL筆記本電腦10000.0023IBM電腦外圍設(shè)備100.0031辦公用品110.004NULLtianjin-棉毛織品50.00commoditysselectcomid,comnamefromcommodityswherecomprice<(select

avg(comprice)fromcommoditys)可以使用謂詞ANY和ALL與比較運(yùn)算符一同構(gòu)成子查詢>ANY：大于子查詢結(jié)果中的任意一個(gè)值（即大于最小值）>ALL：大于子查詢結(jié)果中的所有值（即大于最大值）catidcatname1辦公用品2筆記本電腦3電腦外圍設(shè)備4家用電器catidcomnamecompricesalpricestoamount2DELL筆記本電腦10000.0010000.00203IBM電腦外圍設(shè)備100.0090.0010001辦公用品110.009.0050NULLtianjin-棉毛織品50.0045.0010000commodity_categorycommoditys表selectcatid,catnamefromcommodity_categorywherecatid>any(selectcatidfromcommodityswhere(comprice-salprice)>5andstoamount>100)集合查詢集合查詢：將多個(gè)查詢結(jié)果合并成一個(gè)結(jié)果集可采用的集合查詢操作符UNIONUNIONALLINTERSECT集合查詢UNION：合并兩個(gè)結(jié)果集，自動(dòng)過濾掉重復(fù)數(shù)據(jù)行，并按輸出結(jié)果的第一列排序例：從商品信息表commoditys中查詢商品編號(hào)大于1及商品單價(jià)大于5的商品代碼和商品名稱selectcomid,comnamefromcommodityswherecomid>1unionselectcomid,comnamefromcommodityswherecomprice>5comidcatidcomnamecomprice12DELL筆記本電腦10000.0023IBM電腦外圍設(shè)備100.0031辦公用品110.004NULLtianjin-棉毛織品50.00commoditys集合查詢UNIONALL：合并兩個(gè)結(jié)果集，不會(huì)過濾掉重復(fù)數(shù)據(jù)行，也不作任何排序selectcomid,comnamefromcommodityswherecomid>1unionallselectcomid,comnamefromcommodityswherecomprice>5comidcatidcomnam