Oracle連接多個表.ppt

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