子查询

最新推荐文章于 2023-05-29 16:28:25 发布

JYLM

最新推荐文章于 2023-05-29 16:28:25 发布

阅读量511

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/m0_37905429/article/details/77951812

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

子查询

子查询就是在一个完整的查询语句中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式。

1.查询公司中工资最低的雇员的完整信息
  --第一步：查询公司中的最低工资
  select min(sal)
  from emp;
  --第二步：查询公司中雇员的完整信息
  select *
  from emp;
  --第三步：查询公司中工资最低的雇员的完整信息
 /* select *
  from emp
  where sal=800;*/
  select *
  from emp
  where sal=( select min(sal)
  from emp); //作为逻辑表达式的操作数

子查询出现的位置	描述
where子句	此时子查询返回的结果一般都是单行单列、单行多列、多行多列
having子句	此时子查询返回的结果都是单行单列数据,同时为了使用统计函数操作
from子句	此时子查询返回的结果一般都是多行多列,可以按照一张数据表(临时表)的形式操作.

子查询出现在where子句主要的作用：通过子查询中返回的结果进行数据的筛选

2.子查询返回单行单列数据应用：
2-1 查询出基本工资比ALLEN低的全部雇员信息
  --第一步：查询出ALLEN的基本工资
  select sal
  from emp
  where ename='ALLEN'; 
   --第二步：查询出全部雇员信息
    select *
  from emp;
   --第三步：查询出基本工资比ALLEN低的全部雇员信息
   select *
  from emp
  where sal<( select sal
  from emp
  where ename='ALLEN' );
2-2 查询基本工资高于公司平均薪资的全部雇员信息
  --第一步：查询公司平均薪资
  select avg(sal)
  from emp;
   --第二步：查询全部雇员信息
     select *
  from emp;
  --第三步： 查询基本工资高于公司平均薪资的全部雇员信息
    select *
  from emp
  where sal>(  select avg(sal)
  from emp);
2-3 查找出与ALLEN从事同一工作,并且基本工资高于
  雇员编号为7521的全部雇员信息.
   --第一步： ALLEN从事的工作,
    select job
  from emp
  where ename='ALLEN';
  --第二步： 查找出编号为7521的雇员的基本工资
    select sal
  from emp
  where empno=7521;
  --第三步：查询全部雇员信息
       select *
  from emp;
   --第四步： 查找出与ALLEN从事同一工作,并且基本工资高于雇员编号为7521的全部雇员信息.
select *
from emp
where job=(  select job
  from emp
  where ename='ALLEN')
  and sal>( select sal
  from emp
  where empno=7521)
  and ename<>'ALLEN';

子查询返回单行多列数据注意事项
如果子查询返回的数据是单行多列,则表示使用多个列，进行数据的判断,此时应该使用括号声明多个列。
如何判断列在子查询返回多行单列数据中：
使用in或=any或=some操作符
如何判断列不在子查询返回多行单列数据中：
使用not in或<>all操作符

 1 查询出与某个部门中最低工资相同的全部雇员信息
  --第一步：查询出每个部门的部门编号，部门名，以及部门的员工信息
  select d.deptno,dname,e.*
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno);
  --第二步：查询出每个部门中的最低工资
  select d.deptno, min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno;
  --第三步：查询出与某个部门中最低工资相同的全部雇员信息
  select *
  from emp
  where sal in(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);
  
  
   select *
  from emp
  where sal = any(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno); 
2 查询出不与每个部门中最低工资相同的全部雇员信息
  select *
  from emp
  where sal not in(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);
  
    select *
  from emp
  where sal<> all(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);

子查询返回多行单列数据使用in操作符进行条件判断
注意事项
子查询返回多行单列数据中存在一个null,则将不会有任何的查询结果返回.
如何判断列比多行单列子查询中返回的最小结果要大：使用>any或>some
如何判断列比多行单列子查询中返回的最大结果要小：使用<any或<some
如何判断列比多行单列子查询中返回的最大的值还要大：使用>all
如何判断列比多行单列子查询中返回的最小的值还要小：使用<all
如何判断子查询是否有数据返回（空数据判断）：
sql中提供了一个exists结构用于判断子查询是否有数据返回.如果子查询中有数据返回,则exists结构返回true,反之返回false

子查询返回多行单列数据中存在一个null,则将不会有任何的
查询结果返回(错误)
--查出每个雇员的comm
select comm
from emp;
--如下语句将返回comm不为null的所有记录
select *
from emp
where comm in(
select comm
from emp);
验证如何判断列比多行单列子查询中返回的最大结果要小
<any 或<some
  select *
  from emp
  where sal <any(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);
 验证如何判断列比多行单列子查询中返回的最小结果要大
>any 或>some
  select *
  from emp
  where sal >any(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);
  
    select *
  from emp
  where sal >some(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);
 验证判断列比多行单列子查询中返回的最大的值还要大.
 使用>all
   select *
  from emp
  where sal >all(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);
 验证如何判断列比多行单列子查询中返回的最小的值还要小
 <all
    select *
  from emp
  where sal <all(  select  min(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno)
  group by d.deptno);
  2.如何判断子查询是否有数据返回
  --判断emp表中是否有编号9999的雇员
  查询出编号是9999的雇员
  select *
  from emp
  where empno=9999;
  使用exists关键字判断emp表中是否有编号9999的雇员
  select *
  from emp
  where exists ( select *
  from emp
  where empno=9999
  );
1 查询部门编号、雇员人数、平均工资,并且要求这些部门的平均工资高于公司平均薪金.
--第一步：查询部门编号、雇员人数、平均工资
select d.deptno,count(e.empno),avg(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno）
   group by d.deptno;
--第二步：查询部门编号、雇员人数、平均工资，并且要求这些部门的平均工资高于公司薪金.
select d.deptno,count(e.empno),avg(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno）
   group by d.deptno
   having avg(nvl(e.sal,0))>(select avg(sal) from emp);
2 查询出每个部门平均工资最高的部门名称及平均工资
 --第一步：查询部门名，平均工资
select d.dname,avg(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno）
   group by d.dname; 
 --第二步：
 select d.dname,avg(nvl(e.sal,0))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno）
  group by d.dname
  having avg(nvl(e.sal,0))=(select max(avg(nvl(e.sal,0)))
  from dept d
  left outer join emp e
  on (d.deptno=e.deptno）
  group by d.dname);
四.在from子句中使用子查询
1 查询出每个部门的编号、名称、位置、部门人数、
  平均工资
 --第一步：获取每个部门的部门编号，部门人数，平均工资
 select deptno,count(empno) empcount,avg(sal) salavg
 from emp
 group by deptno;
 
 --第二步：查询出每个部门的编号、名称、位置、部门人数、平均工资
 
 select d.deptno,d.dname,d.loc,t.deptno,t.empcount,t.salavg
 from dept d
 left outer join( select deptno,count(empno) empcount,avg(sal) salavg
 from emp
 group by deptno) t
 on (d.deptno=t.deptno);
 
  select t.deptno,t.empcount,t.salavg ,d.deptno,d.dname,d.loc
 from ( select deptno,count(empno) empcount,avg(sal) salavg
 from emp
 group by deptno) t
 right outer join dept d
 on (d.deptno=t.deptno);
2 查询出所有在部门SALES(销售部)工作的员工的编号、
  姓名、基本工资、奖金、职位、雇佣日期、部门的最高
  和最低工资.
--第一步：获取每个部门(及10，20,30部门)的最低工资和最高工资
  select deptno, min(sal) salmin,max(sal）salmax
 from emp
 group by deptno;
--第二步：获取每个员工的编号， 姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资.
 select e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,t.salmin,t.salmax
 from emp e,(  select deptno, min(sal) salmin,max(sal）salmax
 from emp
 group by deptno) t
 where e.deptno=t.deptno;
--第三步：查询出所有在部门SALES(销售部)工作的员工的编号、 姓名、基本工资、奖金、职位、雇佣日期、部门的最高 和最低工资.
 select e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,t.salmin,t.salmax,d.dname,e.deptno,t.deptno
 from emp e,(  select deptno, min(sal) salmin,max(sal）salmax
 from emp
 group by deptno) t,dept d
 where e.deptno=t.deptno
 and  d.deptno=e.deptno and d.dname='SALES';
3 查询出所有薪金高于公司平均薪金的员工编号、姓名、
  基本工资、职位、雇佣日期,所在部门名称、位置,
  上级领导姓名,公司的工资等级,部门人数、平均工资、
  平均服务年限.
--第一步：获取每个部门(及10，20,30部门)的平均工资部门人数、和平均服务年限
 select deptno,avg(sal) salavg，count(empno) empcount,trunc(avg(months_between(sysdate,hiredate)/12)) yearavg
 from emp
 group by deptno; 
 --第二步：员工编号、姓名、基本工资、职位、雇佣日期,所在部门名称、位置,上级领导姓名,公司的工资等级,部门人数、平均工资、 平均服务年限
 select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename,s.grade,t.empcount,t.salavg,t.yearavg
 from emp e,dept d,emp m,salgrade s,(select deptno,avg(sal) salavg，count(empno) empcount,trunc(avg(months_between(sysdate,hiredate)/12)) yearavg
 from emp
 group by deptno) t
 where e.mgr=m.empno(+) and e.deptno=d.deptno and e.sal between  s.losal and s.hisal and e.deptno=t.deptno;
 
 
--第三步：查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期,所在部门名称、位置,上级领导姓名,公司的工资等级,部门人数、平均工资、 平均服务年限
 select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename,s.grade,t.empcount,t.salavg,t.yearavg
 from emp e,dept d,emp m,salgrade s,(select deptno,avg(sal) salavg，count(empno) empcount,trunc(avg(months_between(sysdate,hiredate)/12)) yearavg
 from emp
 group by deptno) t
 where e.mgr=m.empno(+) and e.deptno=d.deptno and e.sal between  s.losal and s.hisal and e.deptno=t.deptno
 and e.sal>(select avg(sal)
 from emp); 
4 列出薪金比ALLEN或CLARK多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名,部门人数
  --第一步：查询出每个部门的人数
  select deptno,count(empno)
  from emp
  group by deptno;
  --第二步：员工的编号、姓名， 基本工资、部门名称、其领导姓名,部门人数
  select e.empno,e.ename,e.sal,d.dname,m.ename,t.empcount
  from emp e,dept d,emp m,( select deptno,count(empno) empcount
  from emp
  group by deptno) t
  where e.mgr=m.empno(+) and e.deptno=d.deptno and e.deptno=t.deptno;
  --第三步：列出薪金比ALLEN或CLARK多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名,部门人数
   select e.empno,e.ename,e.sal,d.dname,m.ename,t.empcount
  from emp e,dept d,emp m,( select deptno,count(empno) empcount
  from emp
  group by deptno) t
  where e.mgr=m.empno(+) and e.deptno=d.deptno and e.deptno=t.deptno and e.sal >any (select sal
  from emp
  where ename in('ALLEN','CLARK'));
5 列出公司各个部门的经理(假设每个部门只有一个经理,job为MANAGER)的姓名、薪金、部门名称、部门人数、  部门平均工资
--第一步：查询出每个部门的人数，部门平均工资
select deptno,count(empno) empcount,avg(sal) salavg
from emp
group by deptno;
--第二步：员工的姓名，编号， 奖金、部门名称、部门人数，部门平均工资
select e.ename，e.ename,e.sal,d.dname,t.salavg,d.deptno,e.deptno,t.empcount
from emp e,dept d,(select deptno,count(empno) empcount,avg(sal) salavg
from emp
group by deptno) t
where d.deptno=e.deptno(+) and d.deptno=t.deptno(+);
--第三步：列出公司各个部门的经理的姓名、薪金、部门名称、部门人数、  部门平均工资
select e.ename，e.ename,e.sal,d.dname,t.salavg,d.deptno,e.deptno,t.empcount
from emp e,dept d,(select deptno,count(empno) empcount,avg(sal) salavg
from emp
group by deptno) t
where d.deptno=e.deptno(+) and d.deptno=t.deptno(+) and e.job ='MANAGER';