數(shù)據(jù)庫技術(shù)及應(yīng)用（SQL Server ）2.5

數(shù)據(jù)庫技術(shù)及應(yīng)用（SQLServer）教學(xué)單元2.5第7章

SELECT數(shù)據(jù)查詢（ANSI和子查詢）案例2-7-1圖書管理ANSI連接查詢案例2-7-2圖書管理SELECT子查詢SELECT數(shù)據(jù)查詢學(xué)習(xí)導(dǎo)航2SELECT數(shù)據(jù)查詢知識(shí)框架3單元2.5SELECT數(shù)據(jù)查詢（ANSI和子查詢）能力目標(biāo)能夠根據(jù)數(shù)據(jù)庫應(yīng)用系統(tǒng)的功能需求對(duì)表進(jìn)行ANSI連接查詢能夠根據(jù)數(shù)據(jù)庫應(yīng)用系統(tǒng)的功能需求對(duì)表進(jìn)行子查詢能夠根據(jù)數(shù)據(jù)庫應(yīng)用系統(tǒng)的功能需求對(duì)表進(jìn)行聯(lián)合查詢能夠閱讀并熟練書寫SELECT查詢語句和有關(guān)參數(shù)(英文)知識(shí)目標(biāo)ANSI連接查詢的語法子查詢的基本應(yīng)用聯(lián)合查詢的基本應(yīng)用通過了解“圖書館集成管理系統(tǒng)”的查詢界面（圖書數(shù)據(jù)查詢、讀者數(shù)據(jù)查詢、流通數(shù)據(jù)查詢、流通日志查詢），培養(yǎng)解決復(fù)雜問題的能力。*拓展閱讀7圖書館集成管理系統(tǒng)—應(yīng)用界面24單元2.5SELECT數(shù)據(jù)查詢（ANSI和子查詢）知識(shí)目標(biāo)ANSI連接查詢的語法子查詢的基本應(yīng)用聯(lián)合查詢的基本應(yīng)用素質(zhì)目標(biāo)培養(yǎng)與用戶的溝通能力，以便很好地滿足用戶的應(yīng)用需求培養(yǎng)應(yīng)用所學(xué)知識(shí)解決實(shí)際問題的能力5案例2圖書管理系統(tǒng)案例2-7-1圖書管理ANSI連接查詢案例2-7-2圖書管理SELECT子查詢工作任務(wù)單元2.5SELECT數(shù)據(jù)查詢（ANSI和子查詢）6單元2.5SELECT數(shù)據(jù)查詢（ANSI和子查詢）7ANSI連接查詢一子查詢二聯(lián)合查詢?nèi)?、ANSI連接查詢8案例2-7-1圖書管理ANSI連接查詢根據(jù)圖書管理系統(tǒng)的功能需求，應(yīng)用T-SQL的SELECT查詢語句對(duì)數(shù)據(jù)庫“Library”中所創(chuàng)建的表進(jìn)行ANSI連接查詢。工作任務(wù)回顧：SELECT查詢語句基本結(jié)構(gòu)9SELECT[ALL|DISTINCT][TOPn]表達(dá)式列表[INTO新表名]FROM{基表|視圖}[,...n]

--回顧表的連接，視圖見后續(xù)[WHERE查詢條件][GROUPBY分組列名表][HAVING邏輯表達(dá)式][ORDERBY排序列名表[ASC|DESC]]SELECT…FROM一、ANSI連接查詢10說明：在SQLServer中，可以使用兩種語法形式FROM子句（已介紹），連接條件寫在WHERE子句的邏輯表達(dá)式中，從而實(shí)現(xiàn)表的連接（早期）。ANSI（AmericanNationalStandardsInstitute—美國(guó)國(guó)家標(biāo)準(zhǔn)學(xué)會(huì)）連接語法形式，在FROM子句中使用JOIN…ON關(guān)鍵字，連接條件寫在ON之后，從而實(shí)現(xiàn)表的連接。SQLServer2022推薦使用ANSI形式的連接。SELECT…FROM…JOIN…ON一、ANSI連接查詢11語法：FROM表名1{[連接類型]JOIN表名2ON連接條件}[...n]說明：實(shí)現(xiàn)表與表的兩兩連接，表1和表2連接之后還可以繼續(xù)與表3,…,表n連接，最多可以連接256個(gè)表。連接條件放在ON關(guān)鍵字后。特別注意的是此語句也可以連接視圖，下一章介紹。連接類型:INNERJOIN：內(nèi)連接。LEFT[OUTER]JOIN：左外連接。RIGHT[OUTER]JOIN：右外連接。CROSSJOIN：交叉連接。1.SELECT…FROM…JOIN…ON一、ANSI連接查詢12語法：FROM表名1INNERJOIN表名2ON

連接表達(dá)式說明：從兩個(gè)或兩個(gè)以上的表的笛卡兒積中，選出符合連接條件的數(shù)據(jù)行。如果數(shù)據(jù)行無法滿足連接條件，則將其丟棄。內(nèi)連接消除了與另一個(gè)表中不匹配的數(shù)據(jù)行。2.內(nèi)連接一、ANSI連接查詢13【例7-34】從圖書管理數(shù)據(jù)庫“Library”中查詢每位讀者的詳細(xì)信息（讀者及讀者類型），允許有重復(fù)列。代碼：USELibraryGOSELECTReader.*,ReaderType.*FROMReaderINNERJOINReaderTypeONReader.TypeID=ReaderType.TypeID--內(nèi)連接（1）等值連接一、ANSI連接查詢14代碼：SELECTReader.*,ReaderType.*FROMReaderINNERJOINReaderTypeON

Reader.TypeID=ReaderType.TypeID--內(nèi)連接查詢結(jié)果：（1）等值連接一、ANSI連接查詢15【例7-35】查詢每個(gè)讀者的詳細(xì)信息（讀者及借閱圖書的信息），不允許有重復(fù)列。代碼：SELECTReader.*,Borrow.LendDate,Borrow.ReturnDate,Book.BID,Book.Bname,Book.Author,Book.Price--投影消除重復(fù)列FROMReaderINNERJOINBorrowON

Reader.RID=Borrow.RID--表1內(nèi)連接表2INNERJOINBookON

Borrow.BID=Book.BID--再內(nèi)連接表3（2）自然連接一、ANSI連接查詢16查詢結(jié)果：（2）自然連接一、ANSI連接查詢17外連接返回FROM子句中指定的至少一個(gè)表或視圖中的所有行，只要這些行符合任何WHERE選擇（不包含ON之后的連接條件）或HAVING限定條件。外連接又分為左外連接、右外連接和全外連接。左外連接對(duì)連接中左邊的表不加限制；右外連接對(duì)連接中右邊的表不加限制；全外連接對(duì)兩個(gè)表都不加限制，兩個(gè)表中的所有行都會(huì)包括在結(jié)果集中。3.外連接一、ANSI連接查詢18語法：FROM表名1LEFT[OUTER]JOIN表名2ON連接表達(dá)式說明：連接結(jié)果保留表1沒形成連接的行，表2相應(yīng)的各列為NULL值。1.左外連接一、ANSI連接查詢19【例7-36】從表“Reader”和表“Borrow”中查詢出讀者的借閱情況，包括沒有借過書的讀者情況。代碼：SELECTReader.*,Borrow.RID,BIDFROMReaderLEFTOUTERJOINBorrow--左外連接ONReader.RID=Borrow.RID查詢結(jié)果：1.左外連接一、ANSI連接查詢20說明：連接結(jié)果保留了表“Reader”中不滿足等值條件的第3行和第4行，表“Borrow”中相應(yīng)的各列為NULL值，說明讀者張英和李亞茜沒有借過書。1.左外連接一、ANSI連接查詢21語法：FROM表名1RIGHT[OUTER]JOIN表名2ON連接表達(dá)式說明：連接結(jié)果保留表2沒形成連接的行，表1相應(yīng)的列為NULL值。案例：從表“Borrow”和表“Book”中查詢出圖書被借閱的情況，包括沒有被借的圖書情況。2.右外連接一、ANSI連接查詢22【例7-37】從表“Borrow”和表“Book”中查詢出圖書被借閱的情況，包括沒有被借的圖書情況。代碼：SELECTBorrow.RID,Borrow.BID,Book.BID,Bname,AuthorFROMBorrowRIGHTOUTERJOINBook--右外連接ONBorrow.BID=Book.BID2.右外連接一、ANSI連接查詢23查詢結(jié)果：2.右外連接I一、ANSI連接查詢24說明：連接結(jié)果加入了表“Book”中不滿足等值條件的第1、4、8、10行，表“Borrow”中相應(yīng)的列為NULL，說明圖書《ERP從內(nèi)部集成開始》、《ERP系統(tǒng)的集成應(yīng)用》、《SQLServer2008數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)》和《數(shù)據(jù)庫系統(tǒng)概論》四本書沒有被借出過，《SAP基礎(chǔ)教程》被借出過兩次。2.右外連接一、ANSI連接查詢25語法：FROM表名1FULL[OUTER]JOIN表名2ON連接表達(dá)式說明：連接結(jié)果保留表1沒形成連接的元組，表2相應(yīng)的列為NULL值；連接結(jié)果也保留表2沒形成連接的元組，表1相應(yīng)的列為NULL值。3.全外連接一、ANSI連接查詢26【例7-38】借閱和讀者全外連接。代碼：SELECTReader.*,Borrow.RID,Borrow.BIDFROMBorrowFULLOUTERJOINReader--全外連接ONBorrow.RID=Reader.RID查詢結(jié)果：查詢結(jié)果與左外連接相同，因?yàn)榻栝啽碇凶x者的編號(hào)均在讀者表中存在，都能形成連接。3.全外連接一、ANSI連接查詢27語法：FROM表名1

別名1JOIN表名1

別名2ON

連接表達(dá)式說明：表可以通過自連接實(shí)現(xiàn)自身的連接運(yùn)算。自連接可以看作是一張表的兩個(gè)副本之間進(jìn)行的連接，在自連接中，必須為表指定兩個(gè)不同的別名，使之在邏輯上成為兩張表。4.自連接一、ANSI連接查詢28【例7-39】從圖書管理數(shù)據(jù)庫“Library”中查詢出借了兩本以上圖書的讀者的借書信息。步驟1：表“Borrow”與表“Borrow”自連接代碼：SELECTx.RID,x.BID,x.ReturnDate,y.RID,y.BIDFROMBorrowxJOINBorrowyONx.RID=y.RID--讀者編號(hào)等值條件自連接WHEREx.ReturnDateISNULLANDy.ReturnDateISNULL--還期為空，即尚未歸還4.自連接一、ANSI連接查詢29查詢結(jié)果：4.自連接一、ANSI連接查詢30說明：仔細(xì)觀察，可以發(fā)現(xiàn)連接查詢結(jié)果的第5行和第6行數(shù)據(jù)中讀者編號(hào)“RID”相同，但書號(hào)“BID”不同，說明該讀者至少借閱了兩本書。在以上查詢的WHERE子句中加上“x.BID<>y.BID”查詢條件，就可以查詢出借閱了兩本以上的讀者編號(hào)了。這里“x.ReturnDateISNULL”描述了讀者借閱圖書尚未歸還的條件。4.自連接一、ANSI連接查詢31步驟2：對(duì)表“Borrow”與表“Borrow”自連接加選擇條件和投影操作。代碼：SELECTx.RID,x.BID,x.ReturnDate--投影去重復(fù)列名FROMBorrowxJOINBorrowyONx.RID=y.RID--讀者編號(hào)等值條件自連接WHEREx.ReturnDateISNULLANDy.ReturnDateISNULL--還期為空，即尚未歸還ANDx.BID<>y.BID--圖書編號(hào)不同，即兩本不同的書4.自連接一、ANSI連接查詢32查詢結(jié)果：說明：仔細(xì)觀察，可以發(fā)現(xiàn)連接查詢結(jié)果的第5行和第6行數(shù)據(jù)中讀者編號(hào)“RID”相同，但書號(hào)“BID”不同，說明該讀者至少借閱了兩本書。在以上查詢的WHERE子句中加上“x.BID<>y.BID”查詢條件，就可以查詢出借閱了兩本以上的讀者編號(hào)了。這里“x.ReturnDateISNULL”描述了讀者借閱圖書尚未歸還的條件。4.自連接一、ANSI連接查詢33語法：FROM表名1CROSSJOIN表名2說明：兩個(gè)表進(jìn)行笛卡兒積計(jì)算，等價(jià)于FROM表名1，表名2之后不加WHERE連接條件邏輯表達(dá)式。一般沒有意義5.交叉連接單元2.5SELECT數(shù)據(jù)查詢（ANSI和子查詢）34ANSI連接查詢一子查詢二聯(lián)合查詢?nèi)?、子查?5案例2-7-2圖書管理SELECT子查詢根據(jù)圖書管理系統(tǒng)的功能需求，應(yīng)用T-SQL的SELECT子查詢對(duì)數(shù)據(jù)庫“Library”中所創(chuàng)建的表進(jìn)行數(shù)據(jù)查詢和數(shù)據(jù)更新操作。工作任務(wù)二、子查詢36功能說明：子查詢指在一個(gè)SELECT查詢語句的WHERE子句中包含另一個(gè)SELCET查詢語句，或者將一個(gè)SELECT查詢語句嵌入在另一個(gè)語句中成為其一部分。在查詢語句中，在外層SELECT查詢語句稱為主查詢，WHERE子句中的SELECT查詢語句被稱為子查詢。WHERE子句中的子查詢可描述復(fù)雜的查詢條件，也稱為嵌套查詢。嵌套查詢一般會(huì)涉及到兩個(gè)以上的表，所做的查詢有的也可以采用連接查詢或者用幾條查詢語句完成。采用子查詢有時(shí)會(huì)提高算法的時(shí)間和空間效率，但算法不易讀懂，讀者應(yīng)權(quán)衡利弊進(jìn)行選擇。SELECT…FROM…SELECT…（一）IN子查詢37語法：表達(dá)式[NOT]IN(列表|子查詢)說明：如果表達(dá)式的值（NOT，不）與子查詢返回的任何值相等，邏輯表達(dá)式的值為真。子查詢的SELECT投影列表中只能指定一個(gè)表達(dá)式。此表達(dá)式的行數(shù)據(jù)構(gòu)成了括號(hào)內(nèi)集合的所有元素，與集合的概念相同，集合內(nèi)的元素是消除了重復(fù)值的。[NOT]IN子查詢（一）IN子查詢38【例7-41】從圖書管理數(shù)據(jù)庫“Library”中查詢出借閱過“人民郵電出版社”出版圖書的讀者編號(hào)（不包括重復(fù)的行）。代碼：USELibrarySELECTDISTINCTRID--投影滿足條件的讀者編號(hào)，去重復(fù)行FROMBorrow--對(duì)于表“Borrow”中的每一行數(shù)據(jù)--判斷其所借圖書的編號(hào)是否在對(duì)表“Book”子查詢的集合中WHEREBIDIN(SELECTBIDFROMBookWHEREPublisher='人民郵電出版社')--人民郵電出版社圖書的編號(hào)集合查詢結(jié)果：[NOT]IN子查詢（一）IN子查詢39【例7-42】從圖書管理數(shù)據(jù)庫“Library”中查詢出沒有借過書的讀者信息。代碼：SELECT*FROMReader --對(duì)于表中的每一位讀者--判斷其讀者編號(hào)是否不在子查詢的集合中WHERERIDNOTIN(SELECTRIDFROMBorrow) --借了書的讀者編號(hào)集合查詢結(jié)果：[NOT]IN子查詢語法：表達(dá)式比較運(yùn)算符SOME|ANY(子查詢)說明：若表達(dá)式的值在比較關(guān)系上滿足子查詢返回的任何一個(gè)值，則邏輯表達(dá)式的值為真。子查詢的SELECT投影列表中只能指定一個(gè)表達(dá)式。SOME和ANY的用法相同?！?SOME|ANY”等價(jià)于“IN”，“<>SOME|ANY”沒有意義。1.SOME|ANY子查詢（二）SOME|ANY和ALL子查詢40=SOME|ANY(子查詢)等價(jià)IN(子查詢)【例7-43】從圖書數(shù)據(jù)庫“Library”中查詢當(dāng)前借出的清華大學(xué)出版社的圖書借期等信息。代碼：SELECTBorrow.RID,Borrow.BID,Borrow.LendDate,Book.Bname,Book.Publisher,Book.LentOutFROMBorrow,Book --對(duì)于借閱表中的每借出的一本書WHEREBorrow.BID=SOME--圖書編號(hào)是否在子查詢集合中(SELECTBID--已經(jīng)借出清華大學(xué)出版社的圖書編號(hào)集合FROMBook--已經(jīng)借出的清華大學(xué)出版社出版的圖書WHERELentOut='True'ANDPublisher='清華大學(xué)出版社')ANDBorrow.BID=Book.BID --等值連接條件（1）SOME|ANY子查詢（二）SOME|ANY和ALL子查詢41【例7-43】從圖書數(shù)據(jù)庫“Library”中查詢當(dāng)前借出的清華大學(xué)出版社的圖書借期等信息。查詢結(jié)果：（1）SOME|ANY子查詢（二）SOME|ANY和ALL子查詢42語法：表達(dá)式比較運(yùn)算符ALL(子查詢)說明：如果表達(dá)式的值在比較關(guān)系上滿足子查詢返回的每一個(gè)值，則邏輯表達(dá)式的值為真。子查詢的SELECT投影列表中只能指定一個(gè)表達(dá)式?！?lt;>ALL”等價(jià)于“NOTIN”，“=ALL”沒有意義。2.ALL子查詢（二）SOME|ANY和ALL子查詢43<>ALL(子查詢)等價(jià)NOTIN(子查詢)（二）SOME|ANY和ALL子查詢44【例7-44】從借閱表“Borrow”中查詢出讀者編號(hào)RID最大的讀者的借書情況。代碼：SELECT*FROMBorrowWHERERID>=ALL(SELECTRIDFROMReader)--子查詢查詢結(jié)果：2.ALL子查詢RID>=ALL(SELECTRIDFROMReader)等價(jià)RID=(SELECTMAX(RID)FROMReader)語法：[NOT]EXISTS(子查詢)說明：當(dāng)子查詢的結(jié)果存在（不為空集）時(shí)，邏輯表達(dá)式的值為真，不存在（空集）邏輯表達(dá)式的值為假。NOTEXISTS則與EXISTS相反。在EXISTS引入子查詢時(shí)，在子查詢的SELECT投影列表中可以指定多個(gè)表達(dá)式。3.EXISTS(子查詢)（三）EXISTS子查詢45（三）EXISTS子查詢46【例7-45】從圖書管理數(shù)據(jù)庫“Library”中，用EXISTS子查詢查詢出借閱了“人民郵電出版社”出版圖書的讀者編號(hào)。代碼：SELECTDISTINCTRIDFROMBorrow--對(duì)于每一條借閱信息的圖書編號(hào)BIDWHEREEXISTS--判定子查詢是否有滿足子查詢條件的返回值--主查詢中的Borrow.BID等于子查詢中的Book.BID(SELECT*FROMBookWHEREBorrow.BID=Book.BID

ANDPublisher='人民郵電出版社')--人民郵電出版社出版的圖書EXISTS(子查詢)（三）EXISTS子查詢47【例7-45】從圖書管理數(shù)據(jù)庫“Library”中，用EXISTS子查詢查詢出借閱了“人民郵電出版社”出版圖書的讀者編號(hào)。查詢結(jié)果：注意：代碼中WHERE子句后的“Borrow.BID=Book.BID”并不是等值連接條件，而是子查詢中的選擇條件，判斷主查詢的“Borrow.BID”與子查詢的“Book.BID”是否相等。EXISTS(子查詢)等價(jià)語句：SELECTDISTINCTBorrow.RIDFROMBook,BorrowWHEREBook.BID=Borrow.BIDANDPublisher='人民郵電出版社'UPDATE更新數(shù)據(jù)語句：回顧[例6-15】計(jì)算讀者表“Reader”中的已借書數(shù)量列“Lendnum”代碼：UPDATEReaderSETLendnum=--從借閱表“Borrow”中統(tǒng)計(jì)出每個(gè)讀者借書的冊(cè)數(shù)(SELECTCOUNT(*)FROMBorrowWHEREReturnDateISNULLANDReader.RID=Borrow.RID)在UPDATE語句中的使用（四）子查詢?cè)谄渌Z句中的使用48【例7-46】計(jì)算借閱表“Borrow”中的應(yīng)還日期列“SReturnDate”值。代碼：UPDATEBorrow--更新借閱表數(shù)據(jù)SETSReturnDate=--對(duì)借閱表的每一行的應(yīng)還日期列賦值--函數(shù)的第二個(gè)參數(shù)“限借天數(shù)”加上第三個(gè)參數(shù)“借期”得到“應(yīng)還日期”DATEADD(dd,(SELECTReaderType.LimitDays--子查詢得到限借天數(shù)FROMReaderINNERJOINReaderType--等值連接ONReader.TypeID=ReaderType.TypeID--等值條件描述WHEREBorrow.RID=Reader.RID),--借閱表對(duì)應(yīng)的讀者編號(hào)條件描述Borrow.LendDate)--函數(shù)的第三個(gè)參數(shù)為借期在UPDATE語句中的使用（四）子查詢?cè)谄渌Z句中的使用49（四）子查詢?cè)谄渌Z句中的使用50【例7-46】“SreturnDate”值。查詢結(jié)果：在UPDATE語句中的使用主要區(qū)別：子查詢中的表和主查詢中的表分為內(nèi)外嵌套的查詢，而連接查詢是同在一個(gè)層面的查詢。（五）子查詢與連接查詢的比較51【例7-47】查詢出圖書表“Book”中價(jià)格最低的圖書的編號(hào)和書名，采用子查詢完成此任務(wù)。代碼：SELECTBIDAS圖書編號(hào),BnameAS書名FROMBookWHEREPrice=(SELECTMIN(Price)FROMBook)查詢結(jié)果：圖書編號(hào)

書名-------------------------------------------------------TP311.138/78數(shù)據(jù)庫系統(tǒng)概論（五）子查詢與連接查詢的比較52子查詢子查詢(內(nèi))：SELECTMIN(Price)FROMBook為25.00主查詢(外)：Price=SOME(SELECTMIN(Price)FROMBook)等價(jià)Price=(SELECTMIN(Price)FROMBook)等價(jià)Price=25.00【例7-48】查詢讀者編號(hào)、讀者姓名、所借圖書名和借閱時(shí)間。代碼：SELECTReader.RID,Reader.Rname,Book.Bname,Borrow.LendDateFROMReader,Borrow,BookWHEREReader.RID=Borrow.RIDANDBook.BID=Borrow.BID連接查詢（五）子查詢與連接查詢的比較53（五）子查詢與連接查詢的比較54代碼：SELECTReader.RID,Reader.Rname,Book.Bname,Borrow.LendDateFROMReader,Borrow,BookWHEREReader.RID=Borrow.RIDANDBook.BID=Borrow.BID連接查詢?nèi)齻€(gè)表同在一個(gè)層面上單元2.5SELECT數(shù)據(jù)查詢（ANSI和子查詢）55ANSI連接查詢一子查詢二聯(lián)合查詢?nèi)?、?lián)合查詢56語法：SELECT_1UNION[ALL]SELECT_2UNION[ALL]SELECT_3]...說明：UNION操作符對(duì)查詢進(jìn)行并運(yùn)算，ALL參數(shù)表示運(yùn)算結(jié)果包括重復(fù)行。使用UNION運(yùn)算符合并的所有查詢必須在其目標(biāo)列表中有相同數(shù)目的表達(dá)式。1.UNION操作符三、聯(lián)合查詢57【例7-49】從圖書管理數(shù)據(jù)庫“Library”的圖書表“Book”中，查詢出“人民郵電出版社”出版的圖書和“清華大學(xué)出版社”出版的圖書的作者名，不包括重復(fù)的行。代碼：SELECTAuthorFROMBookWHEREPublisher='人民郵電出版社'UNION

--并運(yùn)算，不包括重復(fù)行SELECTAuthorFROMBookWHEREPublis