1 --子查询 2 --子查询 (内查询) 在主查询执行之前执行 3 --主查询(外查询)使用子查询的结果 4 --问题：查询工资大于149号员工工资的员工的信息 5 SELECT last_name 6 FROM employees 7 WHERE salary > 8 (SELECT salary 9 FROM employees 10 WHERE employee_id = 149) ; 11 12 --多利子查询 13 --主查询与子查询返回的多个列进行比较 14 --主查询与子查询返回的多个列进行比较 15 --多列子查询中的比较分为两种: 16 --成对比较 17 --不成对比较 18 19 --成对比较举例 20 --问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id 21 SELECT employee_id, manager_id, department_id 22 FROM employees 23 WHERE (manager_id, department_id) IN 24 (SELECT manager_id, department_id 25 FROM employees 26 WHERE employee_id IN (141,174)) 27 AND employee_id NOT IN (141,174); 28 29 --不成对比较举例 30 SELECT employee_id, manager_id, department_id 31 FROM employees 32 WHERE manager_id IN (SELECT manager_id 33 FROM employees 34 WHERE employee_id IN (174,141)) 35 AND department_id IN (SELECT department_id 36 FROM employees 37 WHERE employee_id IN (174,141)) 38 AND employee_id NOT IN(174,141); 39 40 --在 FROM 子句中使用子查询 41 --问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资 42 --方法一 43 select last_name,department_id,salary, 44 (select avg(salary)from employees e3 45 where e1.department_id = e3.department_id 46 group by department_id) avg_salary 47 from employees e1 48 where salary > 49 (select avg(salary) 50 from employees e2 51 where e1.department_id = e2.department_id 52 group by department_id 53 ) 54 55 --方法二 56 SELECT a.last_name, a.salary, 57 a.department_id, b.salavg 58 FROM employees a, (SELECT department_id, 59 AVG(salary) salavg 60 FROM employees 61 GROUP BY department_id) b 62 WHERE a.department_id = b.department_id 63 AND a.salary > b.salavg; 64 65 --单列子查询表达式 66 --单列子查询表达式是在一行中只返回一列的子查询 67 --Oracle8i 只在下列情况下可以使用, 例如: 68 -- SELECT 语句 (FROM 和 WHERE 子句) 69 -- INSERT 语句中的VALUES列表中 70 --Oracle9i中单列子查询表达式可在下列情况下使用: 71 -- DECODE 和 CASE 72 -- SELECT 中除 GROUP BY 子句以外的所有子句中 73 74 --在 CASE 表达式中使用单列子查询 75 --问题：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。 76 SELECT employee_id, last_name, 77 (CASE 78 WHEN department_id =(SELECT department_id FROM departments WHERE location_id = 1800) 79 THEN 'Canada' ELSE 'USA' END) location 80 FROM employees; 81 82 --在 ORDER BY 子句中使用单列子查询 83 --问题：查询员工的employee_id,last_name,要求按照员工的department_name排序 84 SELECT employee_id, last_name 85 FROM employees e 86 ORDER BY (SELECT department_name 87 FROM departments d 88 WHERE e.department_id = d.department_id); 89 90 --相关子查询 91 --相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询 92 --问题：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id 93 SELECT last_name, salary, department_id 94 FROM employees outer 95 WHERE salary >(SELECT AVG(salary) 96 FROM employees 97 WHERE department_id = outer.department_id) ; 98 99 --问题：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id 100 SELECT e.employee_id, last_name,e.job_id 101 FROM employees e 102 WHERE 2 <= (SELECT COUNT(*) 103 FROM job_history 104 WHERE employee_id = e.employee_id); 105 106 --EXISTS 操作符 107 --EXISTS 操作符检查在子查询中是否存在满足条件的行 108 --如果在子查询中存在满足条件的行: 109 -- 不在子查询中继续查找 110 -- 条件返回 TRUE 111 --如果在子查询中不存在满足条件的行: 112 -- 条件返回 FALSE 113 -- 继续在子查询中查找 114 --问题：查询公司管理者的employee_id,last_name,job_id,department_id信息 115 SELECT employee_id, last_name, job_id, department_id 116 FROM employees outer 117 WHERE EXISTS ( SELECT 'X' 118 FROM employees 119 WHERE manager_id = 120 outer.employee_id); 121 122 --NOT EXISTS 操作符应用举例 123 --问题：查询departments表中，不存在于employees表中的部门的department_id和department_name 124 SELECT department_id, department_name 125 FROM departments d 126 WHERE NOT EXISTS (SELECT 'X' 127 FROM employees 128 WHERE department_id 129 = d.department_id); 130 131 --相关更新 132 --使用相关子查询依据一个表中的数据更新另一个表的数据 133 --eg:1 134 ALTER TABLE employees 135 ADD(department_name VARCHAR2(14)); 136 --eg:2 137 UPDATE employees e 138 SET department_name = 139 (SELECT department_name 140 FROM departments d 141 WHERE e.department_id = d.department_id); 142 143 --相关删除 144 --问题：删除表employees中，其与emp_history表皆有的数据 145 DELETE FROM employees E 146 WHERE employee_id = 147 (SELECT employee_id 148 FROM emp_history 149 WHERE employee_id = E.employee_id); 150 151 --WITH 子句 152 --使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块 153 --WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中 154 --使用 WITH 子句可以提高查询效率 155 --问题：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息 156 WITH 157 dept_costs AS ( 158 SELECT d.department_name, SUM(e.salary) AS dept_total 159 FROM employees e, departments d 160 WHERE e.department_id = d.department_id 161 GROUP BY d.department_name), 162 avg_cost AS ( 163 SELECT SUM(dept_total)/COUNT(*) AS dept_avg 164 FROM dept_costs) 165 SELECT * 166 FROM dept_costs 167 WHERE dept_total > 168 (SELECT dept_avg 169 FROM avg_cost) 170 ORDER BY department_name;
eg：
1 --查询员工的last_name, department_id, salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可 2 select last_name, department_id, salary 3 from employees 4 where (salary,department_id) in ( 5 select salary,department_id 6 from employees 7 where commission_pct is not null 8 ); 9 10 --选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary 11 select last_name, job_id, salary 12 from employees 13 where salary > all( 14 select salary 15 from employees 16 where job_id = 'SA_MAN' 17 ); 18 19 --选择所有没有管理者的员工的last_name 20 select last_name 21 from employees e1 22 where not exists ( 23 select 'A' 24 from employees e2 25 where e1.manager_id = e2.employee_id 26 );