Oracle連接多個(gè)表.pptVIP

連接多個(gè)表,Oracle SQL和PL/SQL編程,目標(biāo),創(chuàng)建一個(gè)笛卡爾連接 使用WHERE子句創(chuàng)建相等連接 使用JOIN關(guān)鍵字創(chuàng)建相等連接 使用WHERE子句創(chuàng)建不等連接 使用JOINON方法創(chuàng)建不等連接 創(chuàng)建自我連接 區(qū)分內(nèi)部連接和外部連接 使用WHERE子句創(chuàng)建外部連接 使用OUTER關(guān)鍵字創(chuàng)建外部連接 使用集合運(yùn)算符結(jié)合多個(gè)查詢的結(jié)果 連接三個(gè)或更多表,笛卡爾連接,又稱為“笛卡爾乘積”或“交叉連接”，第一表中的每一個(gè)數(shù)據(jù)與第二個(gè)表中的每一個(gè)記錄配對(duì) 如果第一個(gè)表中有m條記錄，第二個(gè)表中有n條記錄，結(jié)果是m*n條記錄,笛卡爾連接傳統(tǒng)方法,查找每本書(shū)的出版社名稱,SELECT title,name FROM books,publisher;,笛卡爾連接JOIN方法,Oracle 中，可以使用CROSS關(guān)鍵字與JOIN關(guān)鍵字一起使用用來(lái)創(chuàng)建笛卡爾連接,SELECT title,name FROM books CROSS JOIN publisher;,相等連接,實(shí)際工作中，最常用的連接類型是對(duì)一個(gè)公共列中存儲(chǔ)了相同數(shù)據(jù)的兩個(gè)（或多個(gè)）表創(chuàng)建的，這種連接被稱為“相等連接”，也可以稱為“同等連接”，“內(nèi)部連接”或“簡(jiǎn)單連接”。 公共列是兩個(gè)或更多表中存在相同數(shù)據(jù)的列，比如， boooks和publisher表都包含一個(gè)Pubid的公共列，這個(gè)公共列標(biāo)識(shí)為每一個(gè)出版社指定的標(biāo)識(shí)代碼。,相等連接傳統(tǒng)方法,傳統(tǒng)方法是使用WHERE子句，等號(hào)用來(lái)指定對(duì)于在結(jié)果中連接和返回的行，每一個(gè)表中的Pubid列和內(nèi)容必須完全相等。,SELECT title,name FROM books,publisher WHERE books.pubid = publisher.pubid;,注意：WHERE子句中的pubid列名稱前面包括了對(duì)應(yīng)得表名稱。,相等連接傳統(tǒng)方法,需要輸出這個(gè)pubid時(shí)，必須在SELECT子句中指明哪個(gè)表的，以便引起多義性錯(cuò)誤，也就是說(shuō)連接查詢的兩個(gè)表中有相同列明的時(shí)候，要指明具體是哪個(gè)表的列要顯示,SELECT title,books.pubid,name FROM publisher,books WHERE publisher.pubid = books.pubid AND publisher.pubid=4;,相等連接傳統(tǒng)方法,查詢成本低于15.00美元或者出版商id為1的任何圖書(shū)書(shū)名，出版社編號(hào)，出版社名稱，書(shū)的成本價(jià),SELECT title,p.pubid,name,cost FROM publisher p,books b WHERE p.pubid=b.pubid AND (cost15 OR b.pubid=1);,用到了表別名，作用是為表臨時(shí)指定一個(gè)不同的名稱， 原因：表名過(guò)長(zhǎng)不便于使用，可以使用表別名，還可以減少需要的內(nèi)存,要記住一個(gè)規(guī)則：如果在FROM子句中指定了一個(gè)表別名，那么只要在這個(gè)SQL語(yǔ)句中引用這個(gè)表，就必須使用這個(gè)別名。,相等連接JOIN方法,可以使用三種JOIN關(guān)鍵字的方法來(lái)創(chuàng)建相等連接： NATURAL JOIN,JOINUSING和JOINON NATURAL JOIN關(guān)鍵字將在包括一個(gè)同等命名和定義的字段的兩個(gè)表之間自動(dòng)創(chuàng)建連接 使用USING子句可以根據(jù)在兩個(gè)表中具有相同名稱和定義的列創(chuàng)建連接 當(dāng)USING子句中連接的表沒(méi)有包括一個(gè)同等命名和定義的字段時(shí)，必須添加一個(gè)WHERE子句或者向JOIN關(guān)鍵字添加ON子句，以便制定這兩個(gè)表之間的關(guān)系。,相等連接JOIN方法,列出books表中每一本書(shū)的書(shū)名以及對(duì)應(yīng)的出版社ID和出版社名稱,SELECT title,pubid,name FROM publisher NATURAL JOIN books;,注意：不需要指定兩個(gè)表包含哪一個(gè)或哪些公共列，但暗示了至少存在一個(gè)具有相同名稱的公共列,相等連接JOIN方法,JOINUSING方式 JOINON方式,SELECT title,pubid,name FROM books JOIN publisher USING (pubid);,SELECT title ,name FROM books b JOIN publisher p ON b.pubid = p.pubid;,如果不查詢公共列時(shí),SELECT title,b.pubid,name FROM books b JOIN publisher p ON b.pubid = p.pubid;,如果查詢公共列，要指出具體出自哪個(gè)表,相等連接JOIN方法,隨JOIN關(guān)鍵字使用USING和ON子句，其間存在的兩個(gè)主要區(qū)別 只有當(dāng)連接的表包括一個(gè)具有相同名稱的公共列式才能使用USING子句，這不是ON子句所必須的 條件是在ON子句中指定的，這在USING中不允許，USING子句只能包含公共列的名稱,練習(xí)1,創(chuàng)建一個(gè)列表，顯示每本書(shū)的書(shū)名以及出版社辦公室中你再次定購(gòu)每本書(shū)時(shí)需要與之聯(lián)系的人的姓名和電話號(hào)碼。,不等連接,在不能使用等號(hào)連接相關(guān)列的時(shí)候，也就是表中不存在可以連接的相同行的時(shí)候，我們可以使用“不等連接” 不等連接允許你將一個(gè)范圍的最小值存儲(chǔ)在記錄的一列，這個(gè)范圍的最大值存儲(chǔ)在另一列,不等連接傳統(tǒng)方法,books表和promotion表之間的行不包含相等的值，所以你需要使用不等連接來(lái)確定客戶將在促銷活動(dòng)中得到什么禮品。,SELECT * FROM promotion;,SELECT title,gift FROM books,promotion WHERE retail BETWEEN minretail AND maxretail;,不等連接JOIN方法,SELECT title,gift FROM books JOIN promotion ON retail BETWEEN minretail AND maxretail;,練習(xí)2,訂購(gòu)圖書(shū)SHORTEST POEMS 的客戶將收到什么樣的禮品？,自我連接,一個(gè)表中的數(shù)據(jù)引用了同一個(gè)表中存儲(chǔ)的其他數(shù)據(jù),自我連接傳統(tǒng)方法,customers 表referred列存儲(chǔ)了介紹該新客戶的個(gè)人的客戶號(hào),SELECT r.firstname,r.lastname,c.lastname referred FROM customers c,customers r WHERE c.referred = r.customer#;,自我連接JOIN方法,用JOINON方法,SELECT r.firstname,r.lastname,c.lastname referred FROM customers c JOIN customers r ON c.referred = r.customer#;,外部連接,在執(zhí)行相等連接，不等連接，和自我連接時(shí)，只有當(dāng)查詢的每一個(gè)表都包括一個(gè)對(duì)應(yīng)的紀(jì)錄時(shí)才會(huì)返回一行，這些連接都可歸為“內(nèi)部連接”，因?yàn)橹挥挟?dāng)每一個(gè)表中都存在匹配數(shù)據(jù)時(shí)，記錄才會(huì)在結(jié)果中列出，事實(shí)上是JOIN關(guān)鍵字默認(rèn)的INNER關(guān)鍵字，INNER JOIN 當(dāng)需要在連接查詢的結(jié)果中包括存在于一個(gè)表中但是在另一個(gè)表中沒(méi)有對(duì)應(yīng)記錄時(shí)，就需要外部連接，使用關(guān)鍵字OUTER JOIN。也可以在帶有“外部連接運(yùn)算符+”,外部連接傳統(tǒng)方法,下面是customers 和orders表匹配的記錄,SELECT lastname,firstname,order# FROM customers c,orders o WHERE c.customer# = o.customer# ORDER BY c.customer#;,不匹配會(huì)怎樣？,外部連接傳統(tǒng)方法,使用外部運(yùn)算符（+）的外部連接,SELECT lastname,firstname,order# FROM customers c,orders o WHERE c.customer# = o.customer#(+) ORDER BY c.customer#;,結(jié)果中發(fā)現(xiàn)包含了NULL值，也就是說(shuō)如果一個(gè)客戶在customers表中，但最近沒(méi)有下達(dá)訂單，那么orders表中缺少對(duì)應(yīng)的行或者是不完整的表（也就是說(shuō)這個(gè)表缺少數(shù)據(jù)）。,因此，將外部連接運(yùn)算符（+）放在了引用不完整的orders表的where子句中的連接條件部分之后。,外部連接傳統(tǒng)方法,注意規(guī)則 外部連接運(yùn)算符只能用于連接條件中的一個(gè)表，換句話說(shuō)， 傳統(tǒng)方式時(shí)，不能同時(shí)在兩個(gè)表中創(chuàng)建NULL行 包括外部連接運(yùn)算符的條件不能使用IN或OR運(yùn)算符，因?yàn)檫@表明如果一行與其他表中的一行匹配或者滿足其他某個(gè)指定的條件，那么它就顯示在結(jié)果中。,外部連接JOIN方法,可以隨JOIN關(guān)鍵字包括LEFT，RIGHT，或FULL來(lái)指定關(guān)聯(lián)類型,SELECT lastname,firstname,order# FROM customers c LEFT OUTER JOIN orders o ON c.customer#=o.customer# ORDER BY c.customer#;,SELECT lastname,firstname,order# FROM customers c RIGHT OUTER JOIN orders o ON c.customer#=o.customer# ORDER BY c.customer#;,集合運(yùn)算符,用來(lái)結(jié)合兩個(gè)（或更多）SELECT語(yǔ)句的結(jié) oracle中的集合預(yù)算符：UNION，UNION ALL，INTERSECT和MINUS UNION：返回兩個(gè)查詢的結(jié)果，如果存在重復(fù)記錄，則將刪除它們 UNION ALL：在結(jié)果中包含重復(fù)的記錄 INTERSECT：只列出兩個(gè)查詢都返回的記錄 MINUS：將從輸出中刪除第二個(gè)查詢的結(jié)果,集合運(yùn)算符,需要customers表中最近下了訂單的所有客戶的列表,SELECT customer# FROM customers UNION SELECT customer# FROM orders;,SELECT customer# FROM customers UNION ALL SELECT customer# FROM orders;,SELECT customer# FROM customers INTERSECT SELECT customer# FROM orders;,SELECT customer# FROM customers MINUS SELECT customer# FROM orders;,連接三個(gè)或更多表 連接三個(gè)或更多表傳統(tǒng)方法,SELECT title,firstname,lastname FROM customers,orders,orderitems,books WHERE customers.customer# = orders. customer# AND orders.order# = orderitems.order# AND orderitems.isbn = books.isbn ORDER BY title;,連接三個(gè)或更多表 連接三個(gè)或更多表JOIN方法,JOIN ON NATURAL JOIN,SELECT title,firstname,lastname FROM customers JOIN orders ON customers.customer# = orders.customer# JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems.isbn = books.isbn ORDER BY title;,SELECT title,firstname,lastname FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books ORDER BY title;,練習(xí)3,生成居住在佛羅里達(dá)州并且訂購(gòu)