Oracle 数据库基础学习 (六) 子查询

时间:2021-12-13 04:03:25

子查询在一个select中出现多个嵌套查询语句

1、在where子句中使用子查询(一般返回"单行单列" "单行多列" "多行单列"(可以提供in、any、all ))

示例1:查找低于平均工资的雇员信息(返回单行单列)

select * from emp where sal < (select avg(sal) from emp)

示例2:查找出公司最早雇佣的雇员信息(返回单行单列)

select * from emp where hiredate =(select min(hiredate) from emp) 

示例3:与scott从事同一个工作,且工资相同(返回多行单列)--但是一般不这么使用

select * from emp
where ((job, sal)= (select job, sal from emp where ename='SCOTT')) and (ename != 'SCOTT')

在子查询返回多行单列的时候,可以使用in、any、all 进行比较

⑴   in和not in(不能与null一起使用)的用法

示例:查询和职位是MANAGER工资不同的员工信息

select * from emp
where sal not in (select sal from emp where job='MANAGER') ;

⑵  any的用法(  “>any”大于最小的 “<any”小于最大的)

示例1:查询和职位是MANAGER工资相同的员工信息(”=any”和in一样用法)

select * from emp
where sal =any (select sal from emp where job='MANAGER') ;

示例2:查询比职位是MANAGER工资最低工资还高的员工信息(”>any”就是比最小的大)

select * from emp
where sal >any (select sal from emp where job='MANAGER') ;

示例3:查询比职位是MANAGER工资最高工资还低的员工信息(”<any”就是比最大的小)

select * from emp
where sal <any (select sal from emp where job='MANAGER') ;

⑶  all的用法(“>all”比最大的大 “<all”比最小的小)

示例1:查询比职位是MANAGER工资都高的员工信息(”>all”就是比最大的还大)

select * from emp
where sal >all (select sal from emp where job='MANAGER') ;

示例2:查询比职位是MANAGER工资都低的员工信息(”<all”就是比最小的还小)

select * from emp
where sal <all (select sal from emp where job='MANAGER') ;

2、在having子句中使用子查询,使用的情况只有在子查询返回单行单列,并且使用统计函数的情况

示例1:查询出高于公司平均工资的员工的职位名称,职位人数,平均工资

select job, count(empno), avg(sal)
from emp
group by job
having avg(sal) > (select avg(sal) from emp) ;

3、在select子句中使用子查询(返回单行单列,不过一般不使用)

示例1:查询每个雇员的编号、姓名、职位、部门名称

使用多表查询:

select e.empno, e.ename, e.job, d.dname
from emp e, dept d
where e.deptno=d.deptno(+) ;

使用select子查询:

select e.empno, e.ename, e.job, (select dname from dept d where e.deptno=d.deptno) dname
from emp e ;

4、在from子句中使用子查询(返回多行多列)

示例1:查询每个部门名称、位置、部门人数

使用多表查询:

select d.dname, d.loc, count(empno)
from emp e, dept d
where e.deptno=d.deptno(+)
group by d.dname, d.loc ;

使用from子查询:

  |-查询部门的名称、位置、编号

select deptno, dname, loc from dept ; 

  |-查询部门的编号和部门人

select  deptno, count(empno)
from emp
group by deptno ;

  |-实现在from的子查询,相当与将两张表做多表连接查询

select d.dname, d.loc, temp.count
from dept d, (select deptno, count(empno) count from emp group by deptno ) temp
where d.deptno=temp.deptno(+) ;

注意:1、当子查询返回单行单列、多行单列、单行多列的情况,在where子句中实现子查询

   2、having子句中使用子查询,使用的情况只有在子查询返回单行单列,并且使用统计函数的情况

     3、当子查询返回多行多列的情况,在from子句中实现子查询

     4、一般不在select子句中实现子查询