第6章 Oracle_DML_高級查詢

1、Lesson 6 高級查詢主講：楊昊 Q：952063095 n掌握集合運算掌握集合運算n掌握子查詢掌握子查詢n掌握子查詢所能夠解決的各類問題掌握子查詢所能夠解決的各類問題n掌握多表連接查詢掌握多表連接查詢n使用自連接讓一個表連接它本身使用自連接讓一個表連接它本身n掌握子查詢解決程序中的各種業(yè)務(wù)邏輯掌握子查詢解決程序中的各種業(yè)務(wù)邏輯ABAAABBBUNION/UNION ALLINTERSECTMINUSnEMPLOYEES:提供關(guān)于當(dāng)前所有雇員的詳細(xì)信息提供關(guān)于當(dāng)前所有雇員的詳細(xì)信息nJOB_HISTORY: 當(dāng)一個雇員轉(zhuǎn)換工作的時候，記當(dāng)一個雇員轉(zhuǎn)換工作的時候，記錄前面工作開始和結(jié)束的日期

2、，以及工作的標(biāo)識錄前面工作開始和結(jié)束的日期，以及工作的標(biāo)識號和部門號和部門本課中使用的表：UNION運算符從兩個查詢中返回消除重復(fù)之后的結(jié)果AB顯示當(dāng)前和先前的所有雇員的的工作細(xì)節(jié)。每個雇員只顯示一次ABUNION ALL返回兩個查詢的結(jié)果，其中包括重復(fù)的顯示當(dāng)前和先前的所有雇員的部門AB顯示在開始公司的任期之前，擁有工作資格的雇員的IDs和工作IDsAB顯示那些一次都沒有轉(zhuǎn)換工作的雇員IDs誰的工資比Abel高？主查詢哪個雇員的工資比Abel高？Abel的工資是多少？子查詢？n子查詢（內(nèi)部查詢）在主查詢之前執(zhí)行一次子查詢（內(nèi)部查詢）在主查詢之前執(zhí)行一次n子查詢的結(jié)果被用于主查詢（外部查詢）子

3、查詢的結(jié)果被用于主查詢（外部查詢）n只返回一行結(jié)果只返回一行結(jié)果n使用單行比較運算符使用單行比較運算符nOracle首先執(zhí)行子查詢首先執(zhí)行子查詢nOracle返回結(jié)果到主查詢的返回結(jié)果到主查詢的HAVING子句子句運算符運算符含義含義IN與列表中的任一成員相等與列表中的任一成員相等ANY與子查詢返回的每一個值比較與子查詢返回的每一個值比較ALL與子查詢返回的所有值比較與子查詢返回的所有值比較n返回多行返回多行n使用多行比較運算符使用多行比較運算符子查詢參考父查詢表中的一列找到那些收入超過他們部門平均薪酬的所有雇員每次從外部查詢來的行被處理的時候，內(nèi)部查詢就被求值顯示那些至少已經(jīng)更換了兩次工作的

4、雇員nEXISTS運算符檢測子查詢的結(jié)果集中行的存在運算符檢測子查詢的結(jié)果集中行的存在n如果一個子查詢的行值被找到：如果一個子查詢的行值被找到： 內(nèi)部查詢中的搜索將不會繼續(xù)內(nèi)部查詢中的搜索將不會繼續(xù) 條件將會標(biāo)記為條件將會標(biāo)記為TRUEn如果一個子查詢的行值沒有被找到：如果一個子查詢的行值沒有被找到： 條件將會標(biāo)記為條件將會標(biāo)記為FALSE 內(nèi)部查詢中的搜索將會繼續(xù)內(nèi)部查詢中的搜索將會繼續(xù)找到那些至少有一個人向他們報告的雇員找到?jīng)]有任何職員的部門EMPLOYEESDEPARTMENTSn笛卡爾連接笛卡爾連接n等值連接等值連接n非等值連接非等值連接n外部連接外部連接n自連接自連接EMPLOYEE

5、S (20行)DEPARTMENTS (8行)笛卡爾積：20 x8=160行n笛卡爾積在下列情況下產(chǎn)生：笛卡爾積在下列情況下產(chǎn)生： 忽略了一個連接條件忽略了一個連接條件 一個連接條件失效一個連接條件失效 第一張表的所有行和第二張表的所有行連接第一張表的所有行和第二張表的所有行連接n為了避免笛卡爾積的產(chǎn)生，通常包含一個有效連為了避免笛卡爾積的產(chǎn)生，通常包含一個有效連接條件的接條件的WHERE子句。子句。使用一個連接去查詢多張表的數(shù)據(jù)在WHERE子句中設(shè)定連接條件當(dāng)多張表的列重名時，以表名作為列名的前綴。外鍵主鍵EMPLOYEESDEPARTMENTSn用表前綴在多張表中限定列名用表前綴在多張表中

6、限定列名n用表前綴提高系統(tǒng)性能用表前綴提高系統(tǒng)性能n用列別名區(qū)分不同表中同名的列用列別名區(qū)分不同表中同名的列n用表別名簡化查詢用表別名簡化查詢n用表名前綴提高性能用表名前綴提高性能EMPLOYEESDEPARTMENTSLOCATIONSl連接n張表至少需要n-1個連接條件。例如：連接3張表至少 要2個連接條件EMPLOYEESJOB_GRADESEMPLOYEES 表中的工資應(yīng)介于JOB_GRADES表的最低和最高工資之間在ID為190的部門中沒有任何雇員n利用一個外部連接查詢那些無法滿足連接條件利用一個外部連接查詢那些無法滿足連接條件的行的行n外部連接運算符是一個加號（外部連接運算符是一個

7、加號（+）nwhere e.dept_id(+) = d.id;n+ 號在左邊, 已右邊為準(zhǔn), 左邊補齊n+ 號在右邊, 已左邊為準(zhǔn), 右邊補齊EMPLOYEES (WORKER)EMPLOYEES (MANAGER)WORKER表中的MANAGER_ID等于MANAGER表中的EMPLOYEE_ID使用一個連接查詢多張表的數(shù)據(jù) Cross join Join On( ) Left outer join on ( ) Right outer join ( ) Full outer join on ( )CROSS JOIN產(chǎn)生了一個笛卡爾積（Cartesian product），就象是在連接兩

8、個表格時忘記加入一個WHERE子句一樣 例：SELECT last_name,dept_id FROM emp,dept; 例：SELECT ename,loc FROM emp CROSS JOIN dept;nON子句被用于當(dāng)在兩個表中的字段名不一致時來連接表 例： SELECT department_name, city FROM department, location WHERE department.location_id = location.loc_id;SELECT department_name, city FROM department d JOIN location l

9、 ON (d.location_id = l.id); 在LEFT OUTER JOIN中，會返回所有左邊表中的行，甚至在被連接的表中沒有可對應(yīng)的列名的情況下也如此。 例： SELECT last_name, dept_id FROM emp e, dept d WHERE e.department_id = d.department_id(+); SELECT last_name, dept_id FROM emp LEFT OUTER JOIN Dept ON e.dept_id = d.dept_id; n在RIGHT OUTER JOIN中返回的是表中所有右邊的行，甚至在被連接的表中沒

10、有對應(yīng)的情況下也如此。例： SELECT last_name, d.dept_id FROM employees e, departments d WHERE e.department_id(+) = d.department_id;SELECT last_name, d.dept_id FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); FULL OUTER JOIN=LEFT OUT JOIN+RIGHT OUT JOIN例： SELECT last_name, d.dept_id FROM employees e，departments d WHERE e.department_id = d.department_id（+）UNION ALLSELECT last_name, d.dept_id FROM employees e，departm