Oracle数据库之SQL语句子查询

1. /*  
2. ====================================================================================================  
3. -- 非关联子查询:单行子查询,多行子查询  
4. --  非关联子查询就是位于SELECT、UPDATE、或DELETE语句中内部的查询语句  
5. --子查询(内部查询)在执行主查询之前执行一次 (只执行1次)， 然后主查询(外部查询)会使用该子查询的结果 遍历全部记录  
6. -- -- Notice:子查询的结果有多个值，oracle会去掉重复的记录，再将结果返回给主查询  
7. =====================================================================================================  
8. */  
9.   
10. -- ===========单行子查询================================================================================  
11. -- 子查询只返回一条记录.单行子查询 ,用 <> , < , >, <=, >=  , = 操作  
12.   
13. --在WHERE 子句中使用子查询    
14. select ename,job from emp where empno = (select empno from emp  where mgr = 7902 );     
15. --分析:  
16.     --1.先执行select empno from emp  where mgr = 7902(只执行1次)得到查询结果7369.  
17.     --2. 再执行外部查询:select ename,job from emp where empno =7369. 得到最终结果.  
18.       
19.  --使用分组函数的子查询  
20.  select ename,job,sal from emp  where sal > (select avg(sal) from emp);  
21.  -- from 后面的子查询  
22.  select ename, job from (select * from emp where empno = 7369);   
23.  --单行子查询中的常见错误  
24.         --子查询的结果返回多于一行  
25. select empno,ename  from emp where sal =  (select sal from emp where deptno = 20); --error  
26.         --子查询中不能包含ORDER BY子句,任何的排序在外部查询中完成  
27.  select empno,ename from emp where sal > (select avg(sal) from emp order by empno); -- error  
28.  select empno,ename from emp where sal > (select avg(sal) from emp)  order by empno; -- ok   
29.         --子查询内部没有返回行，如下语句可以正确执行，但没有数据返回  
30.  select ename,job  from emp where empno = (select empno  from emp where mgr = 8000); --error未选定行  
31.   
32.  -- ===========多行子查询================================================================================  
33.  --子查询 结果返回多条记录给外部查询(同样执行1次),外部查询用in, any , all 处理  
34.  -- 外部查询用in , any , all 处理之前必须用一个  =, <>, <, >, <= , >= 操作符.  
35.    
36.  --查询每个部门中薪水最高的员工姓名  
37.  select empno,ename,job from empwhere sal in  (select max(sal)  from emp group by deptno);  
38. --分析:  
39. -- 1.执行select max(sal)  from emp group by deptno ; 返回多条记录.  
40. -- 2.执行select empno,ename,job from emp where sal in ( 从1中返回的多条记录进行一条一条的匹配 ) ;  
41.    
42.  --查询每个部门中低于平均薪水的员工姓名 (any   任何一个)  
43.  select empno,ename,job  from emp where sal < any    (select avg(sal)    from emp    group by deptno);  
44.   
45.   
46.  /*  
47. ====================================================================================================  
48. -- 关联子查询:单行子查询,多行子查询  
49. ---- 子查询中使用了主查询中的某些字段，主查询每扫描一行都要执行一次子查询   
50.   
51. -- 关联子查询:(采用loop的方式进行)  
52. -- 1.外部查询得到一条记录(从外部表中读取数据) 并将其传入到内部查询  
53. -- 2.内部查询基于传入的值执行  
54. -- 3.内部查询从其结果中把值传回到外部查询，  
55. -- 外部查询使用这些值来完成其处理，若符合条件，外部表的那条记录就放入结果集中，  
56.    -- 否则放弃(表示该记录不符合条件)  
57.      
58. -- 4. 重复1-3 .直到把outer表中的所有记录判断一遍  
59. =====================================================================================================  
60. */  
61.     --查询工资高于同一部门的员工的部门号，姓名，工资  
62.     select deptno,ename,sal from emp outer  where sal >(  
63.                 select avg(sal)from emp inner where inner.deptno = outer.deptno);  
64. --分析:1. 取出外部outer的全部记录一条一条的传递给子查询(作为子查询的条件)  
65. --        2.子查询根据外部查询的条件执行1次子查询  
66. --      3.直到外部记录全部遍历完为止，才返回最终的结果  
67. -- 重点:主查询每扫描一行都要执行一次子查询   
68.   
69.    
70.  /*  
71. ====================================================================================================  
72.  -- Exists:用来检查子查询返回行的存在性  
73.  -- 执行过程:  
74. -- 1.外部查询得到一条记录(从外部表中读取数据) 并将其传入到内部查询  
75. -- 2.对inner表依次扫描，若根据条件存在一条记录与 outer表中的记录匹配，立即停止扫描，  
76.         返回ture.立即停止扫描inner表.那么该outer 表中的记录放入结果集中，  
77.         若扫描了全部记录，没有任何一条记录符合匹配条件，  
78.         返回false, outer表的记录被过滤掉，不能出现在结果集中.  
79.      
80. -- 3.重复步骤1-2.直至遍历完outer表中所有的记录  
81.    
82. =====================================================================================================  
83. */  
84.             
85.   
86.     --查询负责管理其它员工的员工记录(使用exists),即是领导的员工  
87. select empno,ename from emp outer where exists  
88.                 (select empno from emp inner where inner.mgr = outer.empno);  
89.                   
90. --分析:  
91.     -- 1. 把outer表中的1行记录的empno传进子查询select empno from emp inner where inner.mgr =xx中.  
92.     -- 2.子查询进行全部数据的遍历,检查是否符合where条件的记录  
93.     -- 3.如果存在,那么就把outer表中的该条记录放入结果集.  
94.     --4.不存在,outer表中的该条记录 不放入结果集.  
95.     -- 5.重复 1.- 4步,直到遍历完outer表中的全部记录,生成最终的结果集  
96.       
97. -- Notice:exists只关心子查询有没有结果，并不需要返回值，所以上述语句调整为(返回常量):  
98.  select empno,ename from emp outer where exists  
99.                 (select 1 from emp inner where inner.mgr = outer.empno);  
100.   
101.    
102.  --========= not Exists 用Exists执行过程一样,not Exists表示子查询没有返回则把outer表放入结果集  
103.   --查询不管理其它员工的职员(not exists),查询不是领导的员工  
104. select empno,ename from emp outer where not exists  
105.     (select empno from emp inner where inner.mgr = outer.empno)  
106.   
107.  --分析:  
108.     -- 1. 把outer表中的1行记录的empno传进子查询select empno from emp inner where inner.mgr =xx中.  
109.     -- 2.子查询进行全部数据的遍历,检查是否符合where条件的记录  
110.     -- 3.如果  不 存在,那么就把outer表中的该条记录放入结果集.  
111.     --4.存在,outer表中的该条记录 不放入结果集.  
112.     -- 5.重复 1.- 4步,直到遍历完outer表中的全部记录,生成最终的结果集  
113.    
114.    
115.  --EXISTS 和NOT EXISTS 与IN 和NOT IN 的比较  
116.   
117.  --       EXISTS与IN的不同：  
118.   
119.   --          EXISTS只检查行的存在性,IN 要检查实际值的存在性(一般情况下EXISTS的性能高于IN)  
120.   
121.   --      NOT EXISTS 和NOT IN   
122.   
123.    --        当值列表中包含空值的情况下,NOT EXISTS 则返回true,而NOT IN 则返回false.  
124.   
125.    
126.  -- update 的子查询  
127.  update emp set salary = (select avg(salary) from emp ) where deptno = 10;  
128.  -- delete的子查询  
129.  delete from emp where salary > (select avg(salary) from emp);  
130.   
131.   
132. 笔记：group by ... having 子句的用法:  
133.   
134. select sum(salary) from emp group by deptno having deptno > 10;  
135. 或者  
136. select sum(salary) from emp group by deptno having sum(salary) > 5000 ;