數(shù)據(jù)庫原理與技術(shù)安徽農(nóng)業(yè)大學(xué)信息與計算機學(xué)院.ppt

數(shù) 據(jù) 庫 原 理 與 技 術(shù),安徽農(nóng)業(yè)大學(xué)信息與計算機學(xué)院,An Introduction to Database System 第三章 關(guān)系數(shù)據(jù)庫標準語言SQL (續(xù)1）,數(shù)據(jù)庫原理與技術(shù),數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語句的一般形式,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4.2 連接查詢,連接查詢：同時涉及多個表的查詢 連接條件或連接謂詞：用來連接兩個表的條件 一般格式： . . . BETWEEN . AND . 連接字段：連接謂詞中的列名稱 連接條件中的各連接字段類型必須是可比的，但名字不必是相同的,數(shù) 據(jù) 庫 原 理 與 技 術(shù),連接操作的執(zhí)行過程,嵌套循環(huán)法(NESTED-LOOP) 首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。 表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。 重復(fù)上述操作，直到表1中的全部元組都處理完畢,數(shù) 據(jù) 庫 原 理 與 技 術(shù),排序合并法(SORT-MERGE),常用于=連接 首先按連接屬性對表1和表2排序 對表1的第一個元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時，對表2的查詢不再繼續(xù),數(shù) 據(jù) 庫 原 理 與 技 術(shù),排序合并法,找到表1的第二條元組，然后從剛才的中斷點處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續(xù) 重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止,數(shù) 據(jù) 庫 原 理 與 技 術(shù),索引連接(INDEX-JOIN),對表2按連接字段建立索引 對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組,數(shù) 據(jù) 庫 原 理 與 技 術(shù),連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,數(shù) 據(jù) 庫 原 理 與 技 術(shù),一、等值與非等值連接查詢,等值連接：連接運算符為= 例33 查詢每個學(xué)生及其選修課程的情況 SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),等值與非等值連接查詢（續(xù)）,查詢結(jié)果：,數(shù) 據(jù) 庫 原 理 與 技 術(shù),等值與非等值連接查詢（續(xù)）,自然連接： 例34 對例33用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,數(shù) 據(jù) 庫 原 理 與 技 術(shù),二、自身連接,自身連接：一個表與其自己進行連接 需要給表起別名以示區(qū)別 由于所有屬性名都是同名屬性，因此必須使用別名前綴 例35查詢每一門課的間接先修課（即先修課的先修課） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),自身連接（續(xù)）,FIRST表（Course表）,數(shù) 據(jù) 庫 原 理 與 技 術(shù),自身連接（續(xù)）,SECOND表（Course表）,數(shù) 據(jù) 庫 原 理 與 技 術(shù),自身連接（續(xù)）,查詢結(jié)果：,數(shù) 據(jù) 庫 原 理 與 技 術(shù),連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,數(shù) 據(jù) 庫 原 理 與 技 術(shù),三、外連接,外連接與普通連接的區(qū)別 普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出 例 36 改寫例33 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),外連接（續(xù)）,執(zhí)行結(jié)果：,數(shù) 據(jù) 庫 原 理 與 技 術(shù),外連接（續(xù)）,左外連接 列出左邊關(guān)系（如本例Student）中所有的元組 右外連接 列出右邊關(guān)系中所有的元組,數(shù) 據(jù) 庫 原 理 與 技 術(shù),連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,數(shù) 據(jù) 庫 原 理 與 技 術(shù),四、復(fù)合條件連接,復(fù)合條件連接：WHERE子句中含多個連接條件 例37查詢選修2號課程且成績在90分以上的所有學(xué)生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 連接謂詞*/ SC.Cno= 2 AND SC.Grade 90； /* 其他限定條件 */,數(shù) 據(jù) 庫 原 理 與 技 術(shù),復(fù)合條件連接（續(xù)）,例38查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績 SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course /*多表連接*/ WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語句的一般形式,數(shù) 據(jù) 庫 原 理 與 技 術(shù),嵌套查詢(續(xù)),嵌套查詢概述 一個SELECT-FROM-WHERE語句稱為一個查詢塊 將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢,數(shù) 據(jù) 庫 原 理 與 技 術(shù),嵌套查詢(續(xù)),SELECT Sname /*外層查詢/父查詢*/ FROM Student WHERE Sno IN （SELECT Sno /*內(nèi)層查詢/子查詢*/ FROM SC WHERE Cno= 2 ）；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),嵌套查詢(續(xù)),子查詢的限制 不能使用ORDER BY子句 層層嵌套方式反映了 SQL語言的結(jié)構(gòu)化 有些嵌套查詢可以用連接運算替代,數(shù) 據(jù) 庫 原 理 與 技 術(shù),嵌套查詢求解方法,不相關(guān)子查詢： 子查詢的查詢條件不依賴于父查詢 由里向外 逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。,數(shù) 據(jù) 庫 原 理 與 技 術(shù),嵌套查詢求解方法（續(xù)）,相關(guān)子查詢：子查詢的查詢條件依賴于父查詢 首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表 然后再取外層表的下一個元組 重復(fù)這一過程，直至外層表全部檢查完為止,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,數(shù) 據(jù) 庫 原 理 與 技 術(shù),一、帶有IN謂詞的子查詢,例39 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 劉晨 ； 結(jié)果為： CS,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有IN謂詞的子查詢（續(xù)）, 查找所有在IS系學(xué)習(xí)的學(xué)生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= CS ； 結(jié)果為：,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有IN謂詞的子查詢（續(xù)）,將第一步查詢嵌入到第二步查詢的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 劉晨 )； 此查詢?yōu)椴幌嚓P(guān)子查詢。,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有IN謂詞的子查詢（續(xù)）,用自身連接完成例39查詢要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 劉晨；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有IN謂詞的子查詢（續(xù)）,例40查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名 SELECT Sno，Sname 最后在Student關(guān)系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC關(guān)系中找出選 FROM SC 修了3號課程的學(xué)生學(xué)號 WHERE Cno IN (SELECT Cno 首先在Course關(guān)系中找出 FROM Course “信息系統(tǒng)”的課程號，為3號 WHERE Cname= 信息系統(tǒng) ) );,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有IN謂詞的子查詢（續(xù)）,用連接查詢實現(xiàn)例40 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系統(tǒng)；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,數(shù) 據(jù) 庫 原 理 與 技 術(shù),二、帶有比較運算符的子查詢,當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，）。 與ANY或ALL謂詞配合使用,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有比較運算符的子查詢（續(xù)）,例：假設(shè)一個學(xué)生只可能在一個系學(xué)習(xí)，并且必須屬于一個系，則在例39可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 劉晨)；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有比較運算符的子查詢（續(xù)）,子查詢一定要跟在比較符之后 錯誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有比較運算符的子查詢（續(xù)）,例41找出每個學(xué)生超過他選修課程平均成績的課程號。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);,相關(guān)子查詢,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有比較運算符的子查詢（續(xù)）,可能的執(zhí)行過程： 1. 從外層查詢中取出SC的一個元組x，將元組x的Sno值（200215121）傳送給內(nèi)層查詢。 SELECT AVG(Grade) FROM SC y WHERE y.Sno=200215121; 2. 執(zhí)行內(nèi)層查詢，得到值88（近似值），用該值代替內(nèi)層查詢，得到外層查詢： SELECT Sno， Cno FROM SC x WHERE Grade =88；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有比較運算符的子查詢（續(xù)）,3. 執(zhí)行這個查詢，得到 （200215121，1） （200215121，3） 4.外層查詢?nèi)〕鱿乱粋€元組重復(fù)做上述1至3步驟，直到外層的SC元組全部處理完畢。結(jié)果為: （200215121，1） （200215121，3） （200215122，2）,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,數(shù) 據(jù) 庫 原 理 與 技 術(shù),三、帶有ANY（SOME）或ALL謂詞的子查詢,謂詞語義 ANY：任意一個值 ALL：所有值,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,需要配合使用比較運算符 ANY 大于子查詢結(jié)果中的某個值 ALL 大于子查詢結(jié)果中的所有值 = ANY 大于等于子查詢結(jié)果中的某個值 = ALL 大于等于子查詢結(jié)果中的所有值 ）ANY 不等于子查詢結(jié)果中的某個值 !=（或）ALL 不等于子查詢結(jié)果中的任何一個值,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例42 查詢其他系中比計算機科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage CS ; /*父查詢塊中的條件 */,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,結(jié)果： 執(zhí)行過程： 1.RDBMS執(zhí)行此查詢時，首先處理子查詢，找出 CS系中所有學(xué)生的年齡，構(gòu)成一個集合(20，19) 2. 處理父查詢，找所有不是CS系且年齡小于 20 或 19的學(xué)生,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,用聚集函數(shù)實現(xiàn)例42 SELECT Sname，Sage FROM Student WHERE Sage CS ;,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例43 查詢其他系中比計算機科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage CS ;,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,方法二：用聚集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage CS ;,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,表3.5 ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價轉(zhuǎn)換關(guān)系,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,1. EXISTS謂詞 存在量詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值 若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值 由EXISTS引出的子查詢，其目標列表達式通常都用* ，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義 2. NOT EXISTS謂詞 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值 若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,例44查詢所有選修了1號課程的學(xué)生姓名。 思路分析： 本查詢涉及Student和SC關(guān)系 在Student中依次取每個元組的Sno值，用此值去檢查SC關(guān)系 若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno= 1，則取此Student.Sname送入結(jié)果關(guān)系,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,用連接運算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1;,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,例45 查詢沒有選修1號課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,不同形式的查詢間的替換 一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換 所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換 用EXISTS/NOT EXISTS實現(xiàn)全稱量詞(難點) SQL語言中沒有全稱量詞 （For all） 可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞： (x)P ( x( P),數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,例：例39查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。 可以用帶EXISTS謂詞的子查詢替換： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 劉晨)；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,例46 查詢選修了全部課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,用EXISTS/NOT EXISTS實現(xiàn)邏輯蘊函(難點) SQL語言中沒有蘊函(Implication)邏輯運算 可以利用謂詞演算將邏輯蘊函謂詞等價轉(zhuǎn)換為： p q pq,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,例47查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號碼。 解題思路： 用邏輯蘊函表達：查詢學(xué)號為x的學(xué)生，對所有的課程y，只要200215122學(xué)生選修了課程y，則x也選修了y。 形式化表示： 用P表示謂詞 “學(xué)生200215122選修了課程y” 用q表示謂詞 “學(xué)生x選修了課程y” 則上述查詢?yōu)? (y) p q,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,等價變換： (y)p q (y (p q ) (y ( p q) ) y(pq) 變換后語義：不存在這樣的課程y，學(xué)生200215122選修了y，而學(xué)生x沒有選。,數(shù) 據(jù) 庫 原 理 與 技 術(shù),帶有EXISTS謂詞的子查詢(續(xù)）,用NOT EXISTS謂詞表示： SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 200215122 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語句的一般形式,數(shù) 據(jù) 庫 原 理 與 技 術(shù),3.4.4 集合查詢,集合操作的種類 并操作UNION 交操作INTERSECT 差操作EXCEPT 參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對應(yīng)項的數(shù)據(jù)類型也必須相同,數(shù) 據(jù) 庫 原 理 與 技 術(shù),集合查詢（續(xù)）,例48 查詢計算機科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19； UNION：將多個查詢結(jié)果合并起來時，系統(tǒng)自動去掉重復(fù)元組。 UNION ALL：將多個查詢結(jié)果合并起來時，保留重復(fù)元組,數(shù) 據(jù) 庫 原 理 與 技 術(shù),集合查詢（續(xù)）,方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；,數(shù) 據(jù) 庫 原 理 與 技 術(shù),集合查詢（續(xù)）,例49 查詢選修了課程1或者選修了課程2的學(xué)生。 SELECT Sno FROM