sqlserver等值連接非等值連接.pptVIP

An Introduction to Database System,連接操作的執(zhí)行過程,嵌套循環(huán)法(NESTED-LOOP) 首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。 表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。 重復(fù)上述操作，直到表1中的全部元組都處理完畢,An Introduction to Database System,排序合并法(SORT-MERGE),常用于=連接 首先按連接屬性對表1和表2排序 對表1的第一個元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時，對表2的查詢不再繼續(xù),An Introduction to Database System,排序合并法,找到表1的第二條元組，然后從剛才的中斷點處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續(xù) 重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止,An Introduction to Database System,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,An Introduction to Database System,一、等值與非等值連接查詢,等值連接：連接運算符為= 例33 查詢每個學(xué)生及其選修課程的情況 SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；,An Introduction to Database System,等值與非等值連接查詢（續(xù)）,查詢結(jié)果：,An Introduction to Database System,等值與非等值連接查詢（續(xù)）,自然連接： 例34 對例33用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；,An Introduction to Database System,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,An Introduction to Database System,二、自身連接,自身連接：一個表與其自己進(jìn)行連接 需要給表起別名以示區(qū)別 由于所有屬性名都是同名屬性，因此必須使用別名前綴 例35查詢每一門課的間接先修課（即先修課的先修課） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；,An Introduction to Database System,自身連接（續(xù)）,FIRST表（Course表）,An Introduction to Database System,自身連接（續(xù)）,SECOND表（Course表）,An Introduction to Database System,自身連接（續(xù)）,查詢結(jié)果：,An Introduction to Database System,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,An Introduction to Database System,三、外連接,外連接與普通連接的區(qū)別 普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出 例 36 改寫例33 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)；,An Introduction to Database System,外連接（續(xù)）,執(zhí)行結(jié)果：,An Introduction to Database System,四、復(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； /* 其他限定條件 */,An Introduction to Database System,嵌套查詢(續(xù)),SELECT Sname /*外層查詢/父查詢*/ FROM Student WHERE Sno IN （SELECT Sno /*內(nèi)層查詢/子查詢*/ FROM SC WHERE Cno= 2 ）；,An Introduction to Database System,嵌套查詢(續(xù)),子查詢的限制 不能使用ORDER BY子句 層層嵌套方式反映了 SQL語言的結(jié)構(gòu)化 有些嵌套查詢可以用連接運算替代,An Introduction to Database System,id name |id score | 1 lee |1 90 | 2 zhang |2 100 | 4 wang |3 70 | 一、外連接 1.概念：包括左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接 2.左連接：left join 或 left outer join select * from table1 left join table2 on table1.id=table2.id 1 lee 1 90 2 zhang 2 100 4 wang NULL NULL 右連接：right join 或 right outer join select * from table1 right join table2 on table1.id=table2.id 1 lee 1 90 2 zhang 2 100 NULL NULL 3 70 .內(nèi)連接：join 或 inner join select * from table1 join table2 on table1.id=table2.id 1 lee 1 90 2 zhang 2 100,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù)）,將第一步查詢嵌入到第二步查詢的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 劉晨 )； 此查詢?yōu)椴幌嚓P(guān)子查詢。,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introduction to Database System,二、帶有比較運算符的子查詢,當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，）。,An Introduction to Database System,帶有比較運算符的子查詢（續(xù)）,例：假設(shè)一個學(xué)生只可能在一個系學(xué)習(xí)，并且必須屬于一個系，則在例39可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 劉晨)；,An Introduction to Database System,帶有比較運算符的子查詢（續(xù)）,子查詢一定要跟在比較符之后 錯誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept；,An Introduction to Database System,帶有比較運算符的子查詢（續(xù)）,例41找出每個學(xué)生超過他選修課程平均成績的課程號。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);,相關(guān)子查詢,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introduction to Database System,三、帶有ANY（SOME）或ALL謂詞的子查詢,謂詞語義 ANY：任意一個值 ALL：所有值,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例42 查詢其他系中比計算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage CS ; /*父查詢塊中的條件 */,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,用聚集函數(shù)實現(xiàn)例42 SELECT Sname，Sage FROM Student WHERE Sage CS ;,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例43 查詢其他系中比計算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage CS ;,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,方法二：用聚集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage CS ;,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,1. EXISTS謂詞 存在量詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值 若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值 2. NOT EXISTS謂詞 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值 若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )；,An Introduction to Database System,帶有EXISTS謂詞的