1、高級子查詢,講師：宋紅康 新浪微博：尚硅谷-宋紅康,書寫多列子查詢 在 FROM 子句中使用子查詢 在SQL中使用單列子查詢 書寫相關(guān)子查詢 使用 EXISTS 和 NOT EXISTS 操作符 使用子查詢更新和刪除數(shù)據(jù) 使用 WITH 子句,子查詢,子查詢是嵌套在 SQL 語句中的另一個SELECT 語句,SELECT . FROM . WHERE .,(SELECT . FROM . WHERE .),主查詢,子查詢,子查詢,子查詢 (內(nèi)查詢) 在主查詢執(zhí)行之前執(zhí)行 主查詢(外查詢)使用子查詢的結(jié)果,SELECTselect_list FROMtable WHEREexpr operato

2、r (SELECT select_list FROM table);,SELECT last_name FROM employees WHERE salary (SELECT salary FROM employees WHERE employee_id = 149) ;,子查詢應(yīng)用舉例,問題：查詢工資大于149號員工工資的員工的信息,一、多列子查詢,Main query,WHERE (MANAGER_ID, DEPARTMENT_ID) IN,Subquery,100 90102 60124 50,主查詢與子查詢返回的多個列進行比較,列比較,多列子查詢中的比較分為兩種: 成對比較 不成對比較

3、,不成對比較舉例,SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141) AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141) ANDemployee_id NOT IN(174,141);,問題：查詢與141號或174號員工的manage

4、r_id和department_id相同的其他員工的employee_id, manager_id, department_id,成對比較舉例,SELECTemployee_id, manager_id, department_id FROMemployees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (141,174) ANDemployee_id NOT IN (141,174);,問題：查詢與141號或174號員工的m

5、anager_id和department_id相同的其他員工的employee_id, manager_id, department_id,select last_name,department_id,salary, (select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salary from employees e1 where salary (select avg(salary) from employees e2 where e1

6、.department_id = e2.department_id -group by department_id ),二、在 FROM 子句中使用子查詢,問題：返回比本部門平均工資高的員工的last_name, department_id, salary及平均工資,方法一,SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE

7、 a.department_id = b.department_id AND a.salary b.salavg;,二、在 FROM 子句中使用子查詢,問題：返回比本部門平均工資高的員工的last_name, department_id, salary及平均工資,方法二,三、單列子查詢表達(dá)式,單列子查詢表達(dá)式是在一行中只返回一列的子查詢 Oracle8i 只在下列情況下可以使用, 例如: SELECT 語句 (FROM 和 WHERE 子句) INSERT 語句中的VALUES列表中 Oracle9i中單列子查詢表達(dá)式可在下列情況下使用: DECODE 和 CASE SELECT 中除 GRO

8、UP BY 子句以外的所有子句中,單列子查詢應(yīng)用舉例,在 CASE 表達(dá)式中使用單列子查詢,SELECT employee_id, last_name, (CASE department_id WHEN THEN Canada ELSE USA END) location FROM employees;,(SELECT department_id FROM departments WHERE location_id = 1800),問題：顯式員工的employee_id,last_name和location。其中，若員工department_id與location_id為1800的depart

9、ment_id相同，則location為Canada,其余則為USA。,單列子查詢應(yīng)用舉例,SELECT employee_id, last_name FROM employees e ORDER BY,(SELECT department_name FROM departments d WHERE e.department_id = d.department_id);,在 ORDER BY 子句中使用單列子查詢,問題：查詢員工的employee_id,last_name,要求按照員工的department_name排序,四、相關(guān)子查詢,相關(guān)子查詢按照一行接一行的順序執(zhí)行，主查詢的每一行都執(zhí)行

10、一次子查詢,GET 從主查詢中獲取候選列,EXECUTE 子查詢使用主查詢的數(shù)據(jù),USE 如果滿足內(nèi)查詢的條件則返回該行,相關(guān)子查詢,SELECT column1, column2, . FROM table1 WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = .expr2);,outer,outer,子查詢中使用主查詢中的列,SELECT last_name, salary, department_id FROM employees outer WHERE salary ,相關(guān)子查詢舉例,(SEL

11、ECT AVG(salary) FROM employees WHERE department_id = outer.department_id) ;,問題：查詢員工中工資大于本部門平均工資的員工的last_name, salary和其department_id,相關(guān)子查詢舉例,SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 = (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id);,問題：若employees表中employe

12、e_id與job_history表中employee_id相同的數(shù)目不小于2，輸出這些相同id的員工的employee_id,last_name和其job_id,五、EXISTS 操作符,EXISTS 操作符檢查在子查詢中是否存在滿足條件的行 如果在子查詢中存在滿足條件的行: 不在子查詢中繼續(xù)查找 條件返回 TRUE 如果在子查詢中不存在滿足條件的行: 條件返回 FALSE 繼續(xù)在子查詢中查找,SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT X FRO

13、M employees WHERE manager_id = outer.employee_id);,EXISTS 操作符應(yīng)用舉例,問題：查詢公司管理者的employee_id,last_name,job_id,department_id信息,SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT X FROM employees WHERE department_id = d.department_id);,NOT EXISTS 操作符應(yīng)用舉例,問題：查詢departments表中，不

14、存在于employees表中的部門的department_id和department_name,六、相關(guān)更新,使用相關(guān)子查詢依據(jù)一個表中的數(shù)據(jù)更新另一個表的數(shù)據(jù),UPDATE table1 alias1 SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);,相關(guān)更新應(yīng)用舉例,ALTER TABLE employees ADD(department_name VARCHAR2(14);,UPDATE employees e SET department_name = (SE

15、LECT department_name FROM departments d WHERE e.department_id = d.department_id);,1),2),DELETE FROM table1 alias1 WHERE column operator (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);,相關(guān)刪除,使用相關(guān)子查詢依據(jù)一個表中的數(shù)據(jù)刪除另一個表的數(shù)據(jù),DELETE FROM employees E WHERE employee_id in (SELECT empl

16、oyee_id FROM emp_history WHERE employee_id = E.employee_id);,相關(guān)刪除應(yīng)用舉例,問題：刪除表employees中，其與emp_history表皆有的數(shù)據(jù),七、WITH 子句,使用 WITH 子句, 可以避免在 SELECT 語句中重復(fù)書寫相同的語句塊 WITH 子句將該子句中的語句塊執(zhí)行一次并存儲到用戶的臨時表空間中 使用 WITH 子句可以提高查詢效率,WITH 子句應(yīng)用舉例,WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(