SQL语句之高级查询

--高级查询
           --子查询在where子句中
                --需求：查看和king同部门的员工？
                 --1.先知道king所在的部门
                 select  ename,deptno from emp where ename='KING';
                 --2.根据King所在的部门去查询该部门的其他员工
                 select ename,sal,deptno from emp where deptno=10;
                 
                 select ename,sal,deptno from emp where deptno=(
                 select deptno
                 from emp
                 where ename='KING');
      --子查询：嵌入到SQL语句中的查询语句
          --目的：通常是为了外层SQL语句提供数据的支持
          --子查询不仅可以用在查询语句中，还可以用在DDL,DML中
             --子查询在DDL中
                 --创建一个表emp10，保存10号部门的所有员工
                 create table emp10 AS select * from emp where deptno=10;
                 select * from emp10;
            --子查询在DML中
                 --删除KING所在部门的所有员工
                  delete from  emp where deptno=(
                  select deptno 
                  from emp
                  where ename='KING'
                  );
                  rollback;
                  select * from emp;
                  update emp set  sal=sal*1.2 where deptno=(
                  select deptno
                  from emp
                  where ename='KING'
                  );
                  --查看和Manager相同部门的员工
                  select ename ,job,deptno
                  from emp
                  where deptno IN (SELECT DEPTNO FROM EMP WHERE JOB='MANAGER');
      --如果子查询返回的是多行，主查询中要使用多行比较操作符
          --多行比较操作符有：IN,ANY,ALL，其中ANY和ALL不能单独使用，需要配合>,>=,<,<=
  --子查询根据查询结果的字段数量与记录数分为：
         --单行单列子查询
         --多行单列子查询
         --单行多列子查询
         --多行多行子查询
      --单列子查询常用于where中作为过滤条件
      --多列常当作一张表来看待，常出现在from子句中作一张表来看待
                  --查找薪水比整个机构高的员工
                  SELECT EMPNO,ENAME,sal FROM EMP WHERE   SAL>(SELECT AVG(SAL) FROM EMP);
                  --查找与SALSMAN同部门的其他职位的员工
                  SELECT ENAME,EMPNO FROM EMP WHERE DEPTNO IN (select deptno from emp where job='SALESMAN') AND JOB!='SALESMAN';
                  --查看比所有SALESMAN和CLEERK工资都高的员工信息
                   select ENAME,EMPNO from emp where sal >ALL(select SAL from emp where job='SALESMAN' or job='CLEERK');
  --EXISTS：该关键字后跟一个子查询，只要该子查询能查询出一条数据，就返回true
      --在子查询中需要引用主查询的字段数据，使用exists关键字
                  --需求，查看有员工的部门
                  select DEPTNO,DNAME 
                  from dept d
                  where exists (select * from emp e where e.deptno=deptno); 
 --HAVING 子句 
                  --需求：列出最低薪水高于部门30的部门信息
       
                  select MIN(sal),DEPTNO from emp
                  GROUP BY deptno
                  having MIN(sal)>( select MAX(SAL) from emp where DEPTNO=30);
                  --需求：查看平均薪水高于名叫clerk的平均薪水的职位
                  select AVG(SAL),job from emp
                  group by job HAVING AVG(SAL)>(SELECT AVG(SAL) FROM EMP WHERE JOB='CLERK');
 --from子句 （多列子查询常被看做一张表，用在from子句中）
                  --需求：查看比自己所在部门的平均工资高的员工信息
                  select AVG(sal) avg_sal,deptno from emp
                  group by deptno;
                  select empno,ename,avg_sal,sal from emp e join (select AVG(sal) avg_sal,deptno from emp group by deptno) t
                  on e.deptno =t.deptno
                  where e.sal>t.avg_sal;
 --select子句
     --子查询出现在select子句，通常实现外连接效果
                  select ename,sal,dname
                  from emp e,dept t
                  where e.deptno=t.deptno;
                  
                  select e.ename,e.sal,(select dname from dept d where e.deptno=d.deptno ) 
                  from emp e;