子查询

子查询

子查询指嵌入在其他SQL中的select语句,也称嵌套查询.

按照子查询返回结果,可将子查询分为:

   等值

单行单列  col = (sub_q)

单行多列  (col1,col2) = (sub_q)

多行单列  col in (sub_q)

多行多列  (col1,col2) in (sub_q)

   不等值

不涉及多列

单行单列  > < !=

多行单列  > any  <==>  col > (sub_q min(col))

 > all  <==>  col > (sub_q max(col))

select .. from t where col=(select .. from t);

1. 父查询 from  14行

2. 父查询 where  第一行

2.1 子查询 from

2.2 子查询 from

2.3 后续的单条语句正常路线

2.4 最终返回一个结果

3. 父查询 where 使用子查询的结果进行比较 得到true false

2. 父查询 where  第二行

2.1 子查询 from

2.2 子查询 from

2.3 后续的单条语句正常路线

2.4 最终返回一个结果

3. 父查询 where 使用子查询的结果进行比较 得到true false

结论： 父查询的表有多少行 where的子查询运行多少次

select col,(select .. from ..) from .. where ...

父查询的where条件有多少行为true 子查询运行多少次

特点:

1.优先执行子查询,主查询再使用子查询的结果

2.子查询返回的列数和类型要匹配

3.子查询要用括号括起来

4.子查询返回多行要用多行关系运算符

单行子查询

子查询返回一行记录

查询和scott在同一部门的员工

SCOTT@ora10g> select deptno,ename,sal from emp where deptno=(select deptno from emp where ename='SCOTT');

    DEPTNO ENAME      SAL

---------- ---------- ----------

20 SMITH      800

20 JONES     2975

20 SCOTT     3000

20 ADAMS     1100

20 FORD       3000

SCOTT@ora10g> 

也可以把子查询结果当成一列

SCOTT@ora10g> select deptno,ename,(select deptno from emp where ename='SCOTT') AA from emp where deptno=10;

    DEPTNO ENAME       AA

---------- ---------- ----------

10 CLARK       20

10 KING         20

10 MILLER       20

SCOTT@ora10g> 

多行子查询

多行子查询指返回多行数据的子查询语句

当在where中使用时,必须使用多行比较符(in all any)

ALL和any操作符不能独立使用 要与单行比较符(= > < >= <= <>)结合使用

in   匹配于子查询结果的任一个值即可

ALL  必须要符合子查询结果的所有值

ANY  只要符合子查询结果的任意一个值即可

in 操作

SCOTT@ora10g> select empno from emp where deptno=10;

     EMPNO

----------

      7782

      7839

      7934

SCOTT@ora10g> select empno,ename,sal from emp where empno in (select empno from emp where deptno=10);

     EMPNO ENAME      SAL

---------- ---------- ----------

      7782 CLARK     2450

      7839 KING       5000

      7934 MILLER     1300

SCOTT@ora10g>

查找每个部门的最高工资的员工姓名

SCOTT@ora10g> select deptno,max(sal) from emp group by deptno;

    DEPTNO   MAX(SAL)

---------- ----------

30  2850

20  3000

10  5000

SCOTT@ora10g> select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

    DEPTNO ENAME      SAL

---------- ---------- ----------

30 BLAKE     2850

20 SCOTT     3000

10 KING       5000

20 FORD       3000

SCOTT@ora10g>

any 操作

小于最大的即可

SCOTT@ora10g> select deptno,ename,sal from emp where deptno < any (select distinct deptno from emp where deptno = 20 or deptno = 30);

    DEPTNO ENAME      SAL

---------- ---------- ----------

10 CLARK     2450

10 KING       5000

10 MILLER     1300

20 JONES     2975

20 FORD       3000

20 ADAMS     1100

20 SMITH      800

20 SCOTT     3000

8 rows selected.

SCOTT@ora10g> 

ALL 操作

小于最小的即可

SCOTT@ora10g> select deptno,ename,sal from emp where deptno < all (select distinct deptno from emp where deptno = 20 or deptno = 30);

    DEPTNO ENAME      SAL

---------- ---------- ----------

10 CLARK     2450

10 KING       5000

10 MILLER     1300

SCOTT@ora10g> 

多列子查询

指子查询返回多个列的数据

当多个列只有一行数据时 可以使用单行比较符

当多个列有多行数据时,还是需要 IN

ALL ANY 实际中已经不实用了

多列单行

就是多条件比较

查询和SMITH相同部门 相同岗位的人

SCOTT@ora10g> select deptno,ename,job,sal from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH');

    DEPTNO ENAME      JOB        SAL

---------- ---------- --------- ----------

20 SMITH      CLERK        800

20 ADAMS      CLERK       1100

SCOTT@ora10g> 

IN 

找出领导和工资与SCOTT和WARD一致的人

SCOTT@ora10g> select ename,mgr,sal from emp where ename in ('SCOTT','WARD');

ENAME   MGR      SAL

---------- ---------- ----------

WARD  7698     1250

SCOTT  7566     3000

SCOTT@ora10g> select deptno,ename,mgr,sal from emp where (mgr,sal) in (select mgr,sal from emp where ename in ('SCOTT','WARD')) and ename not in ('SCOTT','WARD');

    DEPTNO ENAME      MGR SAL

---------- ---------- ---------- ----------

30 MARTIN     7698       1250

20 FORD       7566       3000

SCOTT@ora10g> 

关联子查询

将主查询的内容传递给子查询 子查询再把查询结构反馈给主查询

子查询执行的次数取决于主查询传递值的次数

找出每个部门工资最高的人

不使用关联子查询 使用in分组方式实现

SQL> select max(sal) from emp group by deptno;

  MAX(SAL)

----------

      2850

      3000

      5000

SQL> select deptno,ename,sal from emp where sal in (select max(sal) from emp group by deptno);

    DEPTNO ENAME      SAL

---------- ---------- ----------

30 BLAKE     2850

20 SCOTT     3000

10 KING       5000

20 FORD       3000

SQL> 

一一比较模式

SQL> select deptno,ename,sal from emp where sal = (select max(sal) from emp where deptno=10);

    DEPTNO ENAME      SAL

---------- ---------- ----------

10 KING       5000

SQL> select deptno,ename,sal from emp where sal = (select max(sal) from emp where deptno=20);

    DEPTNO ENAME      SAL

---------- ---------- ----------

20 SCOTT     3000

20 FORD       3000

SQL> select deptno,ename,sal from emp where sal = (select max(sal) from emp where deptno=30);

 

    DEPTNO ENAME      SAL

---------- ---------- ----------

30 BLAKE     2850

SQL> 

使用关联子查询模式

SQL> select deptno,ename,sal from emp e where sal = (select max(sal) from emp where deptno=e.deptno);

    DEPTNO ENAME      SAL

---------- ---------- ----------

30 BLAKE     2850

20 SCOTT     3000

10 KING       5000

20 FORD       3000

SQL> 

子查询

1.比BLAKE工资高的雇员有哪些?  s elect sal,ename from emp where sal>(select sal from emp where ename='BLAKE');

select ename,sal from emp where sal >(select sal from emp where ename='BLAKE')

2.高于30部门最高工资的雇员有哪些?select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

3.大于10部门最小工资的雇员有哪些?select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=10);

4.除了总裁 工资最高的人是谁?

select ename,sal from emp where sal = (select max(sal) from emp where ename !='KING');

5.和ALLEN同部门，工资高于MARTIN的雇员有哪些?select ename,deptno,sal from emp where sal>(select sal from emp where ename='MARTIN') and deptno=(select deptno from emp where ename='ALLEN');

6.工作和部门与SMITH相同，工资高于JAMES的雇员有哪些?select job,deptno,sal from emp where job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH') and sal>(select sal from emp where ename='JAMES');

7.工资高于本部门平均工资的人（拿上游工资的人）有哪些？

select * 

from 

(select deptno,ename,sal from emp) a,

(select deptno,avg(sal) avg_sal from emp group by deptno) b 

where 

a.deptno=b.deptno 

    and sal > avg_sal;

select deptno,ename,sal 

from emp e  14  

where sal >(select avg(sal) 

from emp 

where deptno=e.deptno);

select deptno,ename,sal 

from emp 

where sal>(select avg(sal) 

from emp 

where deptno=10) 

   and deptno=10

union all

select deptno,ename,sal 

from emp 

where sal>(select avg(sal) 

from emp 

where deptno=20) 

   and deptno=20

union all

select deptno,ename,sal 

from emp 

where sal>(select avg(sal) 

from emp 

where deptno=30) 

   and deptno=30

8.按所有人的工资排序 找出排在6~10的人?

SCOTT@orasid> select * from (select rownum rn,a.* from (select ename,sal from emp order by sal) a ) where rn >=6 and rn <=10;

        RN ENAME             SAL

---------- ---------- ----------

         6 MILLER           1300

         7 TURNER           1500

         8 ALLEN            1600

         9 CLARK            2450

        10 BLAKE            2850

SCOTT@orasid> 

rownum

伪列

1. 是以where条件为ture时递增 ++

2. 每一个select都有各自的rownum

父查询和子查询都有各自的rownum

父查询要取子查询的rownum时 子查询的rownum要取别名

否则子查询的rownum［列名］和父查询的rownum［关键字］冲突

以关键字优先

3. where条件中比较时 必须是从1开始的 或者说 包含1的条件

否则选择不到数据

col pct_used for a10

col pct_free for a10

select a.tablespace_name, 

round(curr_size/1048576) curr_size_mb,

round(free_size/1048576) free_size_mb,

round((curr_size-free_size)/curr_size*100)||'%' PCT_USED,

round(free_size/curr_size*100)||'%' PCT_FREE

from (select tablespace_name,sum(bytes) free_size

from dba_free_space 

group by tablespace_name) a,

     (select tablespace_name,sum(bytes)  curr_size

from dba_data_files group by 

tablespace_name) b

where

a.tablespace_name=b.tablespace_name

TABLESPACE_NAME CURR_SIZE_MB FREE_SIZE_MB PCT_USED   PCT_FREE

--------------- ------------ ------------ ---------- ----------

UNDOTBS1                 225          183 18%        82%

SYSAUX                   280           15 95%        5%

USERS                     80           57 28%        72%

SYSTEM                   530            2 100%       0%

ABC                       30           30 0%         100%

IND_TBS                   20           20 0%         100%

XYZ                       30           30 0%         100%

7 rows selected.

SYS@orasid> 

alter database backup controlfile to trace;

    select value||'/orasid_ora_'||spid||'.trc'

    from v$parameter pa,v$session se , v$process pr

    where

       pa.name='user_dump_dest'

        and

       se.sid=(select sid from v$mystat where rownum=1)

        and

      se.paddr=pr.addr

select value||'/orasid_ora_'||

(select spid 

 from v$process 

 where addr=(select paddr 

from v$session 

where sid=(select sid 

from v$Mystat 

where rownum=1

   )

     )

)||'.trc'

from v$parameter

    where

       pa.name='user_dump_dest'

9I  10G 的版本里 转储文件要自己写定位

11G v$diag_info

           

dba_   v$