數(shù)據(jù)庫Oracle第3章2.ppt

數(shù)據(jù)庫應(yīng)用技術(shù) 第三章 深入SQL,樣例數(shù)據(jù)庫,共5個表，在Patrick ONeil, Elizabeth ONeil著數(shù)據(jù)庫原理、編程與性能中示例數(shù)據(jù)庫的基礎(chǔ)上修改而成。 1、位置信息：locations,2、顧客信息：customers,3、代理商信息：agents,4、產(chǎn)品信息：products,5、訂單信息：orders,3.2.3 子查詢,子查詢：出現(xiàn)在另外一個SQL語句中的查詢。 里面出現(xiàn)的SQL語句也被稱為子查詢(subquery)或內(nèi)查詢(inner query) 外面的查詢被稱為外查詢(outer query) 子查詢出現(xiàn)的位置 一般出現(xiàn)在where子句中 按照不同的擴(kuò)展語法，也可以出現(xiàn)在select、having和from子句中,在where子句使用子查詢，標(biāo)準(zhǔn)的語法有 字段表達(dá)式 比較運(yùn)算符 (子查詢) 字段表達(dá)式 比較運(yùn)算符 量詞 (子查詢) 字段表達(dá)式 IN (子查詢) NOT EXISTS (子查詢),子查詢返回1個數(shù)據(jù)時，可直接參與比較運(yùn)算。 注意：子查詢應(yīng)出現(xiàn)在比較運(yùn)算符的右端。,例1：查詢與編號為A01的代理商工資相同的其他人。 SELECT * FROM agents WHERE salary = ( SELECT salary FROM agents WHERE aid = A01 );,例2：查詢比Smith工資高的代理商信息。 SELECT * FROM agents WHERE salary ( SELECT salary FROM agents WHERE aname = Smith );,需要保證只有一個叫Smith的人！,例3：查詢比平均工資高的人員信息。 SELECT * FROM agents WHERE salary ( SELECT AVG(salary) FROM agents );,例4：列出單筆銷售額第二高的銷售額。 SELECT MAX(dollars) FROM orders WHERE dollars ( SELECT MAX(dollars) FROM orders );,上面的例子都要求子查詢必須返回0條或者1條結(jié)果! 如果子查詢返回0條數(shù)據(jù)，作為NULL值處理； 否則，產(chǎn)生運(yùn)行時錯誤（非語法錯誤）。 用戶需要在邏輯上和數(shù)據(jù)上保證返回，一般使用主鍵檢索或唯一性聚組函數(shù)。,在where子句使用子查詢，標(biāo)準(zhǔn)的語法有 字段表達(dá)式 比較運(yùn)算符 (子查詢) 字段表達(dá)式 比較運(yùn)算符 量詞 (子查詢) 字段表達(dá)式 IN (子查詢) NOT EXISTS (子查詢),子查詢返回多行結(jié)果時，需要使用量詞和關(guān)系運(yùn)算符。 量詞包括ALL，SOME（ANY） ALL相當(dāng)于謂詞邏輯中的全稱量詞，而SOME相當(dāng)于存在量詞。 ALL意味著子查詢所返回的所有記錄的值均滿足條件； SOME表示存在一些記錄的值滿足條件； ANY的意義與SOME相同，但由于ANY在英語中有歧義（有任意的意思），所以一般不使用。,例5：找出代理商信息，他的工資高于所有工作在L01的代理商。 SELECT * FROM agents WHERE salary ALL ( SELECT salary FROM agents WHERE lid = L01 );,WHERE salary ( SELECT MAX(salary) FROM agents WHERE lid = L01 );,工作在L01，且具有最高工資的那個人是否被返回？,例6：列出曾經(jīng)通過工作在L01的代理商購買過貨物的顧客的ID。 SELECT cid FROM orders WHERE aid = SOME ( SELECT aid FROM agents WHERE lid = L01 );,例7：列出薪水最高的代理商的ID和姓名。 （請在紙上練習(xí)寫SQL） SELECT aid, aname FROM agents WHERE salary = ( SELECT MAX(salary) FROM agents );,WHERE salary = ALL ( SELECT salary FROM agents);,不需要DISTINCT,在where子句使用子查詢，標(biāo)準(zhǔn)的語法有 字段表達(dá)式 比較運(yùn)算符 (子查詢) 字段表達(dá)式 比較運(yùn)算符 量詞 (子查詢) 字段表達(dá)式 IN (子查詢) NOT EXISTS (子查詢),WHERE aname IN(Smith, Mary, Kate); 常量集合 變量集合 IN 等價于 = SOME NOT IN 等價于 ALL,例6：列出曾經(jīng)通過工作在L01的代理商購買過貨物的顧客的ID。 SELECT cid FROM orders WHERE aid = SOME ( SELECT aid FROM agents WHERE lid = L01 );,WHERE aid IN ( SELECT id FROM agents WHERE lid = L01 );,不需要DISTINCT,例8：列出從未訂過貨的顧客的ID和姓名。 SELECT cid, cname FROM customers WHERE cid NOT IN ( SELECT cid FROM orders );,WHERE cid ALL ( SELECT cid FROM orders );,？ WHERE cid IN (customers中cid orders中cid),例9：找出工作地點(diǎn)和薪水都與Smith相同的其他代理商信息。（請在紙上練習(xí)寫SQL） SELECT * FROM agents a1, agents a2 WHERE a1.lid = a2.lid AND a1.salary = a2.salary AND a2.aname = Smith;,用IN(子查詢)的方式寫 SELECT * FROM agents WHERE lid IN ( SELECT lid FROM agents WHERE aname = Smith) AND salary IN ( SELECT salary FROM agents WHERE aname = Smith);,多個叫Smith的人？且工資和工作地點(diǎn)不一樣怎么辦？,Smith: L01, 2000 Smith: L02, 3000 ? Mary: L01, 3000,在Oracle中WHERE可以寫作 SELECT * FROM agents WHERE (lid, salary) IN ( SELECT lid, salary FROM agents WHERE aname = Smith);,例10：找出住在Duluth的顧客通過工作在L01的代理商訂貨的訂單號。（請在紙上練習(xí)寫SQL） SELECT ordno FROM orders o, customers c, agents a WHERE o.aid = a.aid AND o.cid = c.cid AND c.city = Duluth AND a.lid = L01);,用IN(子查詢)的方式寫 SELECT ordno FROM orders WHERE cid IN ( SELECT cid FROM customers WHERE city = Duluth) AND aid IN ( SELECT aid FROM agents WHERE lid = L01);,在Oracle中WHERE可以寫作 SELECT ordno FROM orders WHERE (cid, aid) IN ( SELECT cid, aid FROM customers c, agents a WHERE c.city = Duluth AND a.lid = L01);,表上沒有合適的連接條件，所以與題意不符。,比較運(yùn)算符跟一個量詞可以轉(zhuǎn)換為其他的寫法。,在where子句使用子查詢，標(biāo)準(zhǔn)的語法有 字段表達(dá)式 比較運(yùn)算符 (子查詢) 字段表達(dá)式 比較運(yùn)算符 量詞 (子查詢) 字段表達(dá)式 IN (子查詢) NOT EXISTS (子查詢),使用EXISTS和NOT EXISTS可以判斷子查詢是否返回記錄（不考慮具體數(shù)據(jù)）。 使用時，通常將外部的數(shù)據(jù)引入到子查詢，作為子查詢內(nèi)部條件的參數(shù)。,例9：找出工作地點(diǎn)和薪水都與Smith相同的其他代理商信息。 SELECT * FROM agents a WHERE EXIST ( SELECT * FROM agents WHERE aname = Smith AND lid = a.lid AND salary = a.salary);,例11：列出從未通過工作在L05的代理商訂過貨的顧客的姓名。（請在紙上練習(xí)寫SQL） SELECT ame FROM customers c WHERE NOT EXISTS ( SELECT * FROM orders o, agents a WHERE o.aid = a.aid AND a.lid = L05 AND o.cid = c.cid); 這條語句會返回在其他代理商處也從未訂過貨的顧客！,只關(guān)心是否有返回結(jié)果，不關(guān)心返回什么,若想濾掉可以 SELECT ame FROM orders o1, customers c WHERE o.cid = c.cid AND NOT EXISTS ( SELECT * FROM orders o2, agents a WHERE o2.aid = a.aid AND a.lid = L05 AND o2.cid = c.cid);,或者 SELECT ame FROM customers c WHERE EXISTS ( SELECT * FROM orders o1 WHERE o1.cid = c.cid) AND 0 = ( SELECT COUNT(*) FROM orders o2, agents a WHERE o2.aid = a.aid AND a.lid = L05 AND o2.cid = c.cid);,使用NOT EXISTS進(jìn)行For-All查詢 例12：列出在所有代理商處都訂過貨的顧客的ID。 （請在紙上練習(xí)寫SQL） 對于顧客cid，不存在他沒訂過貨的代理商！ SELECT DISTINCT cid FROM customers c WHERE NOT EXISTS ( SELECT * FROM agents a WHERE NOT EXISTS ( SELECT * FROM orders o WHERE o.aid = a.aid AND o.cid = c.cid );,子查詢的結(jié)果作為集合的使用方式,在where子句使用子查詢，標(biāo)準(zhǔn)的語法有 字段表達(dá)式 比較運(yùn)算符 (子查詢) 字段表達(dá)式 比較運(yùn)算符 量詞 (子查詢) 字段表達(dá)式 IN (子查詢) NOT EXISTS (子查詢),子查詢 都在右側(cè),3.2.4 集合運(yùn)算,SELECT查詢出來的結(jié)果是一個集合，兩個結(jié)果集合可以進(jìn)行集合運(yùn)算。 查詢的來源可以沒有任何關(guān)系，只要求查詢表達(dá)式的數(shù)目和對應(yīng)數(shù)據(jù)類型一致。,集合運(yùn)算有如下幾種 并集運(yùn)算：UNION 交集運(yùn)算：INTERSECT 差集運(yùn)算：MINUS（Oracle） EXCEPT（MSSql）,例如： SELECT city FROM locations UNION SELECT city FROM customers; UNION：合并完全相同的數(shù)據(jù)形成一個結(jié)果記錄。 UNION ALL：保留重復(fù)的記錄。,需要對集合運(yùn)算結(jié)果進(jìn)行排序時，可以在語句的最后面寫ORDER BY。這時，一般用序號作為排序標(biāo)識。 例如： SELECT aname, aid FROM agents UNION ALL SELECT cname, cid FROM customers ORDER BY 1;,3.2.5 TOP-N問題,TOP-N問題是一個在實(shí)踐中經(jīng)常遇到的典型問題。 假設(shè)：表ranks（主鍵字段id, 值字段score）。 問題：按照值字段的次序只查詢出排名在某個范圍的記錄。 這類問題在實(shí)際應(yīng)用中經(jīng)常出現(xiàn)，如網(wǎng)站瀏覽數(shù)據(jù)時分頁顯示。此時，把所有的數(shù)據(jù)傳送到應(yīng)用程序，然后只顯示其中某個區(qū)間的記錄，效率很。,具體區(qū)分有如下幾種 M1，基本的TOP-N問題：按照score（增序）排序，列出排在最前面N位的記錄。score重復(fù)（并列）時，準(zhǔn)確地取出前N條記錄。 M2：按照score （增序）排序，排在最前面N位的記錄，和所有與第N條等值的記錄。返回記錄數(shù)目可能大于N。 M3：按照score （增序）排序，返回對應(yīng)于N個不同score值的所有記錄。 M4，廣義的TOP-N問題：按照score （增序）排序，排名在N1到N2之間的記錄。一般不考慮并列，只考慮記錄數(shù)目。,專用方案 MySql中SELECT語句的選項(xiàng)LIMIT 一個參數(shù)N：返回查詢的前N個結(jié)果。 兩個參數(shù)N1和N2：返回結(jié)果集中從第N1條記錄（從零開始計數(shù)）開始的N2個記錄。,例1：M1問題的MySql解。 SELECT * FROM ranks ORDER BY score LIMIT 3; 例2：M4問題的MySql解。 SELECT * FROM ranks ORDER BY score LIMIT 2, 3; 在MySql中沒有直接專用方法解決M2和M3問題。,MSSql中SELECT語句的選項(xiàng)TOP n WITH TIES TOP n：返回查詢的前N個結(jié)果。 TOP n WITH TIES：返回查詢的前N個結(jié)果，如果有與第N個結(jié)果相同的記錄，也返回。,例3：M1問題的MSSql解。 SELECT TOP 3 * FROM ranks ORDER BY score; 例4：M2問題的MSSql解。 SELECT TOP 3 WITH TIES * FROM ranks ORDER BY score;,例5：M4問題的MSSql解。 SELECT TOP 3 * FROM ranks WHERE id NOT IN ( SELECT TOP 2 id FROM ranks ORDER BY