《SQL Server數(shù)據(jù)庫實(shí)用案例教程》 實(shí)驗(yàn)6- 多種方式多表查詢 參考代碼-不要先發(fā)學(xué)生

提示：不要把答案先發(fā)給學(xué)生，實(shí)驗(yàn)結(jié)束后再公布答案。要讓學(xué)生先自己思考做題，老師對學(xué)生不會的題目適當(dāng)提示，一題題解決困惑。實(shí)驗(yàn)6多種方式多表查詢參考代碼繼續(xù)使用實(shí)驗(yàn)2創(chuàng)建的銀行儲蓄數(shù)據(jù)庫，客戶信息(customerinfo)表、賬戶信息(accountinfo)表、交易信息(transInfo)表三個表的表結(jié)構(gòu)見表2-18?表2-20。(1)查詢賬戶余額低于100元的客戶的客戶編號、客戶姓名、電話、地址郵編(連接查詢、嵌套)。提示：在交易信息表中修改數(shù)據(jù)，制造數(shù)據(jù)驗(yàn)證查詢效果。--說明：本題涉及兩張表“select*from表名”查看表中列名和數(shù)據(jù)select*fromcustomerinfoselect*fromaccountinfo-連接查詢是將涉及的表都放在FROM后面，關(guān)聯(lián)條件寫在where后面selectac.customerlD,customerName,phone,addressfromcustomerinfocu,accountinfoac 一本題只需兩張表wherecu.customerlD=ac.customerlD 一關(guān)聯(lián)條件,表別名?列名andaccountMoney<100-嵌套查詢是FROM后面只寫一個表，where條件中關(guān)聯(lián)另一個表-不管是連接查詢還是嵌套查詢，關(guān)聯(lián)條件不變，都是customerlDselectcustomerlD,customerName,phone,addressfromcustomerinfo --只寫一個張表wherecustomerlDin --用in不能用二，因?yàn)椴樵兘Y(jié)果可能多行(selectcustomerlDfromaccountinfo 一嵌套一個完整查詢語句whereaccountMoney<100)里結(jié)果魚消息phoneaddress431111phoneaddress431111安徽蕪湖551111NULL1 |4 |張元?dú)? 5 亮晶晶(2)查詢本月存款超過三次的賬號所屬客戶的詳細(xì)信息，包括客戶編號、客戶姓名、證件類型、證件號碼、電話、地址郵編。(連接查詢、嵌套查詢)-說明：本題涉及三張表“select*from表名”查看表中列名和數(shù)據(jù)select*fromcustomerinfoselect:fromaccountlnfoselect:fromtransInfo-三個表，兩級嵌套查詢selectcustomerlD,customerName,cardType,cardID,phone,addressfromcustomerinfo 一一個表wherecustomerlDin 一第一級嵌套，關(guān)聯(lián)到accountinfo表(selectcustomerlDfromaccountinfowhereaccountIDin --第二級嵌套，關(guān)聯(lián)到transInfo表(selectaccountIDfromtransInfowhereMONTH(transDate)=MONTH(GETDATE())―本月的條件andtransType廿存入，groupbyaccountIDhavingCOUNT(^)>3))--存款超過三次-說明：沒有超過三次的可以改為havingCOUNT(*)〉2-連接查詢，所有查詢列都放在groupby后面進(jìn)行分組selectac.customerlD,customerName,cardType,cardID,phone,addressfromcustomerinfocu,accountinfoac,transInfotr --三個表wherecu.customerlD=ac.customerlDandac.accountID=tr.accountIDandMONTH(transDate)=MONTH(GETDATE())andtransType廿存入'groupbyac.customerlD,customerName,cardType,cardID,phone,addresshavingCOUNT(*)>3 -存款超過三次(3)查詢哪些客戶既有存款，也有取款操作，顯示客戶姓名、電話、賬號(嵌套查詢、集合查詢)。-說明：本題涉及三張表“select*from表名”查看表中列名和數(shù)據(jù)select*fromcustomerinfoselect*fromaccountinfoselect*fromtransInfo-從交易信息(transInfo)表能看到存款還是取款-交易信息表與賬戶信息(accountinfo)表通過賬號關(guān)聯(lián)--賬戶信息表與客戶信息(customerinfo)表通過客戶編號關(guān)聯(lián)selectcustomerName客戶姓名，phone電話,accountID賬號fromcustomerinfocu,accountinfoac -先寫兩個表wherecu.customerlD=ac.customerlDandaccountIDin(selectaccountIDfromtransInfowheretransType廿存入'andMONTH(transDate)=MONTH(GETDATE()))andaccountIDin(selectaccountIDfromtransInfowheretransType廿支取'andMONTH(transDate)=MONTH(GETDATE()))-集合查詢：分別查詢存款和取款的，然后取交集selectcustomerName客戶姓名，phone電話，ac.accountID賬號fromcustomerinfocu,accountinfoac,transInfotr --三個表wherecu.customerlD=ac.customerlDandac.accountID=tr.accountIDandtransType廿存入'andMONTH(transDate)=MONTH(GETDATE())intersect -取兩個查詢結(jié)果的交集selectcustomerName客戶姓名，phone電話，ac.accountID賬號fromcustomerinfocu,accountinfoac,transInfotr --三個表wherecu.customerlD=ac.customerlDandac.accountID=tr.accountIDandtransType廿支取'andMONTH(transDate)=MONTH(GETDATE())口結(jié)果4消息客戶姓名電話禰的茗字I111111客戶姓名電話禰的茗字I111111賬號2001(4)查詢哪些客戶在今年只有存款，沒有取款，顯示客戶姓名(嵌套查詢、集合查詢)o-在上一題的基礎(chǔ)上修改就很簡單--連接查詢+嵌套查詢，比上題僅修改一處notinselectcustomerName客戶姓名，phone電話，accountID賬號fromcustomerinfocu,accountinfoac 一先寫兩個表wherecu.customerlD=ac.customerlDandaccountIDin(selectaccountIDfromtransInfowheretransType廿存入'andMONTH(transDate)=MONTH(GETDATE()))andaccountIDnotin(selectaccountIDfromtransInfo一沒有取款notinwheretransType廿支取'andMONTH(transDate)=MONTH(GETDATE()))-集合查詢：分別查詢存款和取款的，然后取交集selectcustomerName客戶姓名，phone電話，ac.accountID賬號fromcustomerinfocu,accountinfoac,transInfotr --三個表wherecu.customerlD=ac.customerlDandac.accountID=tr.accountIDandtransType廿存入'andMONTH(transDate)=MONTH(GETDATE())except 一-不是取交集了，改為差操作selectcustomerName客戶姓名，phone電話，ac.accountID賬號fromcustomerinfocu,accountinfoac,transInfotr --三個表wherecu.customerlD=ac.customerlDandac.accountID=tr.accountIDandtransType廿支取'andMONTH(transDate)=MONTH(GETDATE())國結(jié)果3消息客戶姓名1客戶姓名1rwi-2'…同學(xué)5……22111110103311111011(5)查詢存款余額最低的賬戶的客戶信息，顯示客戶編號、客戶姓名、電話(嵌套查詢、基于派生表查詢)。-說明：本題涉及兩張表“select*from表名”查看表中列名和數(shù)據(jù)selectfromcustomerinfoselect二fromaccountTnfoorderbyaccountMoney--嵌套查詢，兩級嵌套,自己查看表中數(shù)據(jù)進(jìn)行驗(yàn)證selectcustomerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfo 一一個表wherecustomerlDin 一第一級嵌套(selectcustomerlDfromaccountinfowhereaccountMoney= --第二級嵌套，最低余額只有一個，用二即可(selectMIN(accountMoney)fromaccountinfo)) --查最低余額-基于派生表查詢，兩個基本表和一個派生表連接查詢selectcu.customerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfocu,accountinfoac,(selectMIN(accountMoney)mfromaccountinfo)ss-4卮生表wherecu.customerlD=ac.customerlDandaccountMoney=m國結(jié)果為消息客戶編號客戶姓名電話???????????????????????????????????????????I1 |4 張亮亮431111(6)查詢單筆存款金額最大的客戶的客戶編號、客戶姓名、電話(嵌套查詢、基于派生表查詢)。-說明：本題涉及三張表“select*from表名”查看表中列名和數(shù)據(jù)select*fromcustomerinfoselectfromaccountinfoselectfromtransInfo-嵌套查詢，三級嵌套,自己查看表中數(shù)據(jù)進(jìn)行驗(yàn)證selectcustomerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfo --一個表wherecustomerlDin --第一級嵌套(selectcustomerlDfromaccountinfowhereaccountIDin --第二級嵌套(selectaccountIDfromtransInfowheretransMoney= 一第三級嵌套，子查詢結(jié)果確定一行，用=即可(selectMAX(transMoney)fromtransInfowheretransType='存入')))-基于派生表查詢，三個基本表和一個派生表連接查詢selectcu.customerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfocu,accountinfoac,transInfotr,(selectMAX(transMoney)mmfromtransInfowheretransType='存入')sswherecu.customerlD=ac.customerlDandac.accountID=tr.accountIDandtransMoney=mm口結(jié)果」消息客戶編號客戶姓名電話1Pi I你的名字 mm(7)查詢哪些客戶從未取款，顯示客戶編號、客戶姓名、電話(嵌套查詢、外連接查詢、集合查詢)。-說明：本題涉及三張表“select*from表名”查看表中列名和數(shù)據(jù)select二fromcustomerinfoselect:fromaccountinfoselect*fromtransInfo--嵌套查詢，三級嵌套,自己查看表中數(shù)據(jù)進(jìn)行驗(yàn)證selectcustomerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfo 一一個表wherecustomerlDnotin --第一級嵌套notin(selectcustomerlDfromaccountinfowhereaccountIDin --第二級嵌套(selectaccountIDfromtransInfowheretransType='支取'))-外連接+內(nèi)連接selectdistinctcu.customerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfoculeftjoin(selectcustomerlDfromaccountinfoac,transInfotrwhereac.accountID二tr.accountIDandtransType='支取')aaoncu.customerlD=aa.customerlD --外連接要有on接連接條件whereaa.customerlDisnull --外連接其他條件還是在where子句-集合查詢selectcustomerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfoexcept 一差運(yùn)算selectcu.customerlD客戶編號,customerName客戶姓名，phone電話fromcustomerinfocu,accountinfoac,transInfotrwherecu.customerlD=ac.customerlDandac.accountID=tr.accountID口結(jié)果3消息客戶編號3口結(jié)果3消息客戶編號34567客戶姓名電話同學(xué)2 |331111張亮亮 431111亮晶晶 551111張華 661111李莉華 771111(8)統(tǒng)計日營業(yè)額，顯示日期、存款金額、取款金額、余額(存款金額-取款金額)，查詢結(jié)果按照日期降序(外連接查詢、基于派生表查詢)。-說明：本題只涉及交易信息(transInfo)表select*fromtransInfo-基于派生表查詢+外連接查詢,自己查看表中數(shù)據(jù)進(jìn)行驗(yàn)證有的月份只有存款，或者只有取款，用內(nèi)連接會無法取出selectbl.rq,bl.je存款金額,b2.je取款金額,bl.je-b2.je余額from(selectCONVERT(char,transDate,102)rq,SUM(transMoney)jefromtransInfowheretransType='存入'groupbyCONVERT(char,transDate,102))blleftjoin(selectCONVERT(char,transDate,102)rq,SUM(transMoney)jefromtransInfowheretransType='支取’groupbyCONVERT(char,transDate,102