【oracle学习】1.子查询

子查询所要解决的问题：问题不能一步求解
实际上就是sql语句的嵌套，就是在一个select语句中嵌套另外一个select语句

首先我们使用控制台登录连接我们的oracle:


我们先创建员工信息表EMP:

create table EMP(    EMPNO NUMBER,    ENAME VARCHAR2(10),    JOB VARCHAR2(9),    MGR NUMBER,    HIREDATE DATE,    SAL BINARY_DOUBLE,    COMM BINARY_DOUBLE,    DEPTNO NUMBER);

其中job是职位，mgr是该员工的上司的id，sal是工资，comm是提成，deptno是所属部门。

然后加入一些数据:

insert into EMP (    EMPNO,ENAME ,JOB ,MGR ,HIREDATE ,SAL ,COMM ,DEPTNO)values(    1110,'张三','主管',1110,'12-3月 -14',5200,0,20);

以后数据的添加如上，这里不再赘述。

添加完成后结果
SQL> select * from emp;
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1110 张三                 主管                          1110 12-3月 -14      5200     0          20
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
           1114 李磊磊               会计                          1110 22-12月-15      2500     0          50
           1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20
           1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
           1118 沈倩                 会计                          1116 06-5月 -10      2100     0          50

然后创建一张部门表

create table dept(    DEPTNO NUMBER,    DNAME VARCHAR2(50));

添加数据之后结果
SQL> select * from dept t;

DEPTNO    DNAME
--------  --------
20        管理部门

30        销售部门

40        后勤部门

50        金融部门


我们来查询工资比“李磊磊”高的员工信息
(为了防止科学记数法的出现，我们先对工资和提成进行排版:
col sal for 9999
col comm for 9999)
(1)李磊磊的工资
select sal from emp where ename='李磊磊';

       SAL                                                                      
----------                                                                      
      2500
(2)查询比3000高的员工
select * from emp where sal >2500;
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1110 张三                 主管                          1110 12-3月 -14      5200     0          20
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20
           1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
 
使用子查询
select * from emp where
sal>(select sal from emp where ename='李磊磊');
结果如上

子查询要注意的问题：
1. 将子查询放入括号中

2. 采用合理的书写风格

3. 可以在主查询的where select from having后面，放置子查询
select deptno,min(sal) from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno=30);
         DEPTNO MIN(SAL)
--------------- --------
             20     5200
             40     2800

拆分一下，其中
select deptno,min(sal) from emp
group by deptno

         DEPTNO MIN(SAL)
--------------- --------
             30     2400
             20     5200
             40     2800
             50     2100

select min(sal) from emp where deptno=30;

MIN(SAL)
--------
    2400

所以找到的是min(sal)大于2400的数据

4. 不可以在group by后面放置子查询

5. 强调from后面放置子查询
select * from (select ename,sal from emp);

ENAME                  SAL
-------------------- -----
张三                  5200
李四                  3400
王五                  4400
赵二                  3450
李磊磊                2500
张少丽                2400
林建国                5700
马富邦                2800
沈倩                  2100

6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可
我们查询部门名称为“销售”的员工信息
select *
    from emp
    where deptno=(select deptno
                 from dept
                 where dname='销售部门');
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30

效果如同下面的多表查询:
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.dname='销售部门';

这里注意：
SQL优化： 如果子查询和多表查询都可以，理论上尽量使用多表查询

7. 一般不在子查询中使用order by;但在Top-N分析问题中，必须使用order by

8. 一般先执行子查询，再执行主查询；但相关子查询除外

9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
多行操作符
--in :在集合中
--查询部门名称为“销售部门”和“金融部门”的员工信息
select * from emp
where deptno in
(select deptno from dept where dname='销售部门' or dname='金融部门')
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1118 沈倩                 会计                          1116 06-5月 -10      2100     0          50
           1114 李磊磊               会计                          1110 22-12月-15      2500     0          50

效果如同下面的多表查询:
select e.* from emp e,dept d
where e.deptno=d.deptno and (d.name='销售部门' or d.name='金融部门');

像下面这种查询就是错误的，非法使用子查询

原因是“多行查询使用单行比较符”
 
10. 注意子查询中null
查询不是老板的员工信息(老板的上司mgr的id是他自己的id)
select * from emp
where empno not in (select mgr from emp where empno!=mgr);
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
           1114 李磊磊               会计                          1110 22-12月-15      2500     0          50
           1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30
           1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
           1118 沈倩                 会计                          1116 06-5月 -10      2100     0          50

查询是老板的员工信息
select * from emp
where empno in (select mgr from emp where empno=mgr);
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1110 张三                 主管                          1110 12-3月 -14      5200     0          20
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20

但是如果not in集合中含有空值的话，就会取得所有的数据。
所以我们在not in语句的子集合中，要进行空值得判断，不然就会取所有数据。



最后我们再看一些有用的查询，any、all
(1)any 和集合的任意一个值比较
查询工资比30号部门任意一个员工高的员工信息
select * from emp
where sal>any(select sal from emp where deptno=30);
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20
           1110 张三                 主管                          1110 12-3月 -14      5200     0          20
           1112 王五                 销售                          1116 25-4月 -12      4400   800          30
           1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
           1111 李四                 销售                          1116 03-11月-15      3400   500          30
           1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
           1114 李磊磊               会计                          1110 22-12月-15      2500     0          50

相当于
select * from emp
where sal > (select min(sal) from emp where deptno=30)

(2)all 和集合的所有值比较
查询工资比30号部门所有员工高的员工信息
select * from emp
where sal>all(select sal from emp where deptno=30);
          EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
           1110 张三                 主管                          1110 12-3月 -14      5200     0          20
           1116 林建国               主管                          1116 22-1月 -16      5700     0          20

相当于
select * from emp
where sal > (select max(sal) from emp where deptno=30)

转载请注明出处：http://blog.csdn.net/acmman/article/details/52300718