Oracle数据库----------------------------复杂查询

Oracle数据库的学习记录：

二十一、复杂查询
1.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数
**确定要使用的数据表：
  |-emp表：姓名，薪金
  |-dept表：部门名称
  |-emp表：统计出部门人数
**确定已知的关联字段：
  |-雇员和部门：emp.deptno=dept.deptno;
//////   自己写的结果，可能会有错
select MAX(sal) from emp where deptno=30;
select e.ename,e.sal,d.dname,COUNT(e.empno) from emp e,dept d where e.sal > (
            select MAX(sal) from emp where deptno=30)
            AND e.deptno=d.deptno;
//////
第一步：找出部门30工作的所有雇员的工资，返回多行单列
select sal from emp where deptno=30;
第二步：只能够在WHERE子句中使用子查询，且使用>ALL,找到员工的姓名、薪金
select e.ename,e.sal from emp e where sal>ALL(select sal from emp where deptno=30) ;
第三步：除了雇员信息的查询，还需要查找到部门信息，那么需要在FROM子句之后引入dept表，但是一旦引入新的表，要在WHERE子句里增加消除笛卡尔积的条件

select e.ename,e.sal,d.dname

from emp e,dept d

where sal>ALL(

             select sal from emp where deptno=30)

             AND e.deptno=d.deptno;

第四步：随后要统计部门人数，但是对于部门人数可以先在子查询之中完成统计
select deptno dno,COUNT(empno) count from emp GROUP BY deptno;
第五步：返回的是多行多列的数据，按照使用原则来讲应该在FROM子句中使用

select e.ename,e.sal,d.dname,temp.count

from emp e,dept d ,(

      select deptno dno,COUNT(empno) count from emp GROUP BY deptno) temp
where sal>ALL(

      select sal from emp where deptno=30)

     AND e.deptno=d.deptno AND temp.deptno=d.deptno;

2.列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数，领导姓名
**确定要使用的数据表：
  |-emp表：员工信息
  |-dept表：部门名称
  |-emp表：统计出部门人数
  |-emp表：找到领导信息
**确定已知的关联字段：
  |-雇员和部门：emp.deptno=dept.deptno;
  |-雇员和领导：emp.mgr=memp.empno;
第一步：先找到SCOTT的工作，次查询返回单行单列，一般用于WHERE和HAVING子句
select job from emp where ename='SCOTT';
第二步：找到符合此要求的所有信息

select e.ename,e.job,e.sal

from emp e

where e.job=(

       select job from emp where ename='SCOTT');
第三步：查找对应的部门名称信息，加入dept表，同时要增加消除笛卡尔积的条件

select e.ename,e.job,e.sal,d.dname

from emp e,dept d

where e.job=(

       select job from emp where ename='SCOTT') AND e.deptno=d.deptno;
第四步：部门人数可以单独进行统计

select e.ename,e.job,e.sal,d.dname,temp.count

from emp e,dept d,(

        select deptno dno,COUNT(empno) count
        from emp

        GROUP BY deptno) temp

where e.job=(

       select job from emp where ename='SCOTT')

       AND e.deptno=d.deptno AND temp.dno=d.deptno;

第五步：查找雇员对应的领导信息,实现自身关联

select e.ename,e.job,e.sal,d.dname,temp.count,m.ename 

from emp e,dept d,(

        select deptno dno,COUNT(empno) count
        from emp

        GROUP BY deptno) temp,emp m

where e.job=(

       select job from emp where ename='SCOTT')

       AND e.deptno=d.deptno AND temp.dno=d.deptno AND e.mgr=m.empno;

第六步：消除SCOTT数据

select e.ename,e.job,e.sal,d.dname,temp.count,m.ename 

from emp e,dept d,(

        select deptno dno,COUNT(empno) count
        from emp

        GROUP BY deptno) temp,emp m

where e.job=(

       select job
       from emp   where ename='SCOTT') AND e.deptno=d.deptno

       AND temp.dno=d.deptno AND e.mgr=m.empno AND e.ename<>'SCOTT';

3.列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名、部门人数，平均工资、最高及最低工资
**确定要使用的数据表：
  |-emp表：员工编号、姓名
  |-dept表：部门名称  
  |-emp表：找到领导信息
  |-emp表：统计出部门数据
**确定已知的关联字段：
  |-雇员和部门：emp.deptno=dept.deptno;
  |-雇员和领导：emp.mgr=memp.empno;
第一步：找到“SMITH”和“ALLEN”的薪金
select sal from emp where ename IN('SMITH','ALLEN');
第二步：
select e.empno,e.ename,e.sal
from emp e
where e.sal > ANY(
            select sal
            from emp
            where ename IN('SMITH','ALLEN'))
            AND e.ename NOT IN('SMITH','ALLEN');
第三步：找到领导信息
select e.empno,e.ename,e.sal,m.ename
from emp e,emp m
where e.sal > ANY(
            select sal
            from emp
            where ename IN('SMITH','ALLEN'))
            AND e.ename NOT IN('SMITH','ALLEN')
            AND e.mgr=m.empno(+);
第四步：统计部门的信息,在FROM子句之中完成
select e.empno,e.ename,e.sal,m.ename,d.dname,temp.dno,temp.count,temp.avg,temp.max,temp.min
from emp e,emp m,(
            select deptno dno,COUNT(empno) count,AVG(sal) avg,MAX(sal) max,MIN(sal) min
            from emp
            GROUP BY deptno) temp,dept d
where e.sal > ANY(
            select sal
            from emp
            where ename IN('SMITH','ALLEN'))
            AND e.ename NOT IN('SMITH','ALLEN')
            AND e.mgr=m.empno(+)
            AND temp.dno=d.deptno
            AND e.deptno=d.deptno;

4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数
**确定要使用的数据表：
  |-emp表：员工编号、姓名
  |-dept表：部门名称 、部门位置
  |-emp表：统计出部门人数
  |-emp表：找到领导雇佣日期，作为自身关联使用
**确定已知的关联字段：
  |-雇员和部门：emp.deptno=dept.deptno;
  |-雇员和领导：emp.mgr=memp.empno;    
第一步：实现emp的自身关联，找到受雇日期早于其直接上级的所有员工的编号、姓名
select e.empno,e.ename
from emp e,emp m
where e.mgr=e.empno(+) AND e.hiredate<m.hiredate;
第二步：找到部门信息
select e.empno,e.ename,d.dname,d.loc
from emp e,emp m,dept d
where e.mgr=e.empno(+)
      AND e.hiredate<m.hiredate
      AND e.deptno=d.deptno;
第三步：找到部门人数，在FROM子句里统计部门人数
select e.empno,e.ename,d.dname,d.loc,temp.count
from emp e,emp m,dept d,(
      select deptno dno,COUNT(empno) count
      from emp
      GROUP BY deptno) temp
where e.mgr=e.empno(+)
      AND e.hiredate<m.hiredate
      AND e.deptno=d.deptno
      AND d.deptno=temp.dno;

5.列出所有“CLERK”办事员的的姓名及其部门名称、部门的人数、工资等级
**确定要使用的数据表：
  |-emp表：员工姓名
  |-dept表：部门名称
  |-emp表：统计出部门人数
  |-salgrade表：工资等级
**确定已知的关联字段：
  |-雇员和部门：emp.deptno=dept.deptno;
  |-雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal
第一步：找到所有办事员的姓名
select e.ename
from emp e
where e.job='CLERK';
第二步：找到部门名称
select e.ename,d.dname
from emp e,dept d
where e.job='CLERK'
      AND e.deptno=d.deptno;
第三步：统计部门人数
select e.ename,d.dname,temp.count
from emp e,dept d,(
      select deptno dno,COUNT(empno) count
      from emp
      GROUP BY deptno) temp
where e.job='CLERK'
      AND e.deptno=d.deptno
      AND d.deptno=temp.dno;
第四步：查询出工资等级
select e.ename,d.dname,temp.count,s.grade
from emp e,dept d,(
      select deptno dno,COUNT(empno) count
      from emp
      GROUP BY deptno) temp,salgrade s
where e.job='CLERK'
      AND e.deptno=d.deptno
      AND d.deptno=temp.dno
      AND e.sal BETWEEN s.losal AND s.hisal;

      
二十二、数据更新（增加数据）
范例：复制emp表
CREATE TABLE myemp AS SELECT * FROM emp;
此类的语法只有Oracle才有支持

1.数据增加
INSERT INTO 表名称 [(列名称1，列名称2，...)] VALUES (值1，值2,...)
在增加数据的时候，针对于数据增加的内容，有如下说明：
**字符串：使用“'”声明
**数字：直接编写
**日期：当前日期（SYSDATE）、使用TO_DATE()转换，按照日期格式编写字符串(自动转换)

范例：使用完整格式编写，进行数据的增加
INSERT INTO myemp(empno,sal,job,comm,ename,mgr,hiredate,deptno)
VALUES (8888,9000.0,'清洁工',10.0,'张三',7369,TO_DATE('1979-10-10','yyyy-mm-dd'),40);
使用简化格式编写：省略增加数据的列名称(顺序要和表中数据列一致)
INSERT INTO myemp
VALUES (9999,'李四','清洁工',7369,TO_DATE('1979-10-10','yyyy-mm-dd'),9000.0,10.0,40);

强烈建议使用完整格式

对于空值的设置，一般有两种格式：
**方式一：明确的设置一个字段的内容为null
INSERT INTO myemp(empno,sal,job,comm,ename,mgr,hiredate,deptno)
VALUES (8888,9000.0,'清洁工',10.0,'张三',7369,null,40);
**方式二：不编写不需要的内容，则内容就使用默认值填充(如果没设置默认值就是空)
INSERT INTO myemp(empno,sal,job,comm,ename,mgr,deptno)
VALUES (6666,9000.0,'清洁工',10.0,'张三',7369,40);