第九章高级子查询课堂笔记和练习

– 高级子查询

• 嵌套子查询
  – 在通常的子查询中，子查询是以嵌套的方式写在父查询的WHERE、HAVING、FROM子句中，所以被 称为嵌套子查询。
  注意：嵌套子查询可以单独运行
  – 嵌套子查询的执行过程：
  – 1.子查询首先执行一次；
  – 2.用来自子查询的值确认或取消父查询的候选行。

  查询比本部门平均薪水高的员工姓名，薪水
      select empno,ename,sal
        from emp e ,(select deptno,avg(sal) avgsal
                       from emp
                      group by deptno) d
       where e.deptno =d.deptno
         and e.sal >d.avgsal;
  

• 相关子查询
  相关子查询不可以单独运行，依赖于主查询的某列
  – 相关子查询的执行过程：
  – ① 主查询每执行一次，获取查询数据
  – ② 子查询使用步骤①中获取的数据，执行确认或取消的判断
  – ③ 循环步骤①②的操作，直到最后一条数据为止

  • 在select子句中使用相关子查询

    --  查询所有部门名称和人数
        select d.dname 部门名称, (select count(ename) 
                                   from emp 
                                  where deptno = d.deptno) 人数
          from dept d;
    
    --  注意连接条件与子查询的条件相等，一般需要给主查询的表取别名
    

  • 在where子句中使用相关子查询

    --  查询哪些员工是经理?
        select * from emp e
         where 0 < (select count(ename) 
          from emp where mgr = e.empno);
    --  如何理解？ 
    ->  当子查询中在主查询的记录中找到经理编号等于子查询中的员工编号的员工记录时
    ->  count(ename)+1，若再找到同编号，自动进行分组合并相同信息，对count(ename) + 1,
    ->  即只要查找到是经理的记录，count(ename)就大于0 
    ->  所以只要执行一次相关子查询就可以判断一次其信息是否为经理
    
    
    --  查询至少调过2次岗位的员工编号，姓名，岗位
        select e.empno 员工编号, e.ename 姓名, e.job 岗位
          from emp e
         where 2 <= (select count(empno) 
                       from emp_jobhistory 
                      where empno = e.empno);  
    
    --  理解： 
    --  与上题相似，主要区别为其emp_jobhistory表中只有KING的数据（第八章添加的）
    ->  因此只要找到相同编号的信息，count(empno)+1
    ->  假如只要查调过职位的员工信息，则只需把 2<= 改为 0< 即可
    

  – exists 存在，not exists 不存在

  • exists就是判断是否“存在”，只要找到相关记录子查询就不用再执行

       --  查询哪些人是经理？
           select e.*
             from emp e
            where exists (select * from emp where mgr = e.empno);
    

  • not exists就是判断是否“不存在”，只要没找到相关记录，就为真，找到一条即为假

       --  查询哪些人不是是经理？
           select e.*
             from emp e
            where not exists (select * from emp where mgr = e.empno);