Oracle数据库之第二篇

/*
   多表查询  多个数据库表做连接查询
     使用场景： 查询的数据来源为多个表
    */
    --查询员工信息和员工的部门信息
    select * from emp;
    select * from dept;
    --使用关联条件 过滤无效数据
    select * from emp,dept where emp.deptno=dept.deptno
    /*
       内连接 隐式内连接  select * from A,B where A.列=B.列
              显式内连接  select * from A inner join B on A.列=B.列
       特点 做关联查询的两个表 必须双方条件数据完全匹配 才会提取
    */
    --使用显式内连接实现
    select * from emp inner join dept on emp.deptno = dept.deptno

    /*
      外连接   
           左外连接   select * from A left join B on A.列=B.列
                      
                      以左表为基准 左表数据全部显示 右表数据作为补充显示
                      如果没有数据 显示空
           
           右外连接   select * from B right join A on A.列=B.列
                      
                      以右表为基准 右表数据全部显示 左表数据作为补充显示
                      如果没有数据 显示空
    **/
    --查询部门信息和部门下的员工信息 没有员工的部门也要显示
    --左外连接实现
    select * from dept left join emp on dept.deptno = emp.deptno
    --右外连接实现
    select * from emp right join dept on dept.deptno = emp.deptno
    --特定要求部门显示左边
    select dept.*,emp.* from emp right join dept on dept.deptno = emp.deptno
    /*
     oracle数据库特有的外连接
        语法：使用符号（+） 实现外连接
            使用方法：根据需求 将符号放在 作为补充显示的表的列后面
            select * from A,B where A.列=B.列(+)
    */
    --使用oracle数据库特有外连接   跟等号左右无关
    select * from emp,dept where emp.deptno(+)=dept.deptno
    select * from emp,dept where dept.deptno=emp.deptno(+)
    /*
      自连接 自己跟自己做关联查询
         自连接查询 别名必须加
         select * from A A1,A A2 where A1.列=A2.列
         使用场景：
             关联的记录在同一个表内
    */
    --查询员工的信息和员工的领导信息
    select e.empno,e.ename,
           m.empno mgr_no,m.ename  mgr_name
        from emp e,emp m where e.mgr = m.empno
    --在上面基础上 再查询员工的部门信息 dept
    select * from dept
    select e.empno,e.ename,d.dname,
           m.empno mgr_no,m.ename  mgr_name
        from emp e,emp m,dept d
        where e.mgr = m.empno and d.deptno = e.deptno
    --在上面基础之上 再查询员工的工资等级  salgrade
    select * from salgrade

    select e.empno,
           e.ename,
           d.dname,
           s1.grade,
           m.empno  mgr_no,
           m.ename  mgr_name
     from emp e, emp m, dept d, salgrade s1
     
     where e.mgr = m.empno
       and d.deptno = e.deptno
       and e.sal between s1.losal and s1.hisal
    --在基础之上查询领导的工资等级
    select e.empno,
           e.ename,
           d.dname,
           s1.grade,
           m.empno  mgr_no,
           m.ename  mgr_name,
           s2.grade mgr_grade
     from emp e, emp m, dept d, salgrade s1,salgrade s2
     where e.mgr = m.empno
       and d.deptno = e.deptno
       and e.sal between s1.losal and s1.hisal
       and m.sal between s2.losal and s2.hisal
    --根据查询的数据 一张表一张表加的时候 分析表之间的关联关系
    --oracle的decode函数
    select e.empno,
           e.ename,
           d.dname,
           decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') emp_grade,
           m.empno  mgr_no,
           m.ename  mgr_name,
           s2.grade mgr_grade
     from emp e, emp m, dept d, salgrade s1,salgrade s2
     where e.mgr = m.empno
       and d.deptno = e.deptno
       and e.sal between s1.losal and s1.hisal
       and m.sal between s2.losal and s2.hisal



    ---错误示例
    select e.empno,
           e.ename,
           d.dname,
           s1.grade,
           m.empno  mgr_no,
           m.ename  mgr_name,
           s1.grade mgr_grade
     from emp e, emp m, dept d, salgrade s1
     where e.mgr = m.empno
       and d.deptno = e.deptno
       and e.sal between s1.losal and s1.hisal
       and m.sal between s1.losal and s1.hisal

    /*
      子查询 在查询语句中嵌套查询语句
           
            语法： 
                  单行子查询 select * from A where A.列= sql返回的唯一值
                  多行子查询 select * from A where A.列 in  sql返回单列多个值
                             select * from A,(sql语句返回多行多列临时表) t 
                                        where A.列 = t.列
    */
    --查询比雇员7654工资高，同时从事和7788相同工作的员工信息？
    --1.查询数据  员工信息
    --2.数据来源  emp表
    --3.查询条件  工资>7654的工资  工作=7788的工作
    select sal from emp where empno=7654  --1250
    select job from emp where empno=7788  --ANALYST
    --使用结果查询员工
    select * from emp where sal > 1250 and job = 'ANALYST'
    --使用sql语句替换查询条件
    select * from emp where 
         sal > (select sal from emp where empno=7654)
         and job = (select job from emp where empno=7788)
    --查询每个部门的最低工资，和最低工资的雇员 及他的部门名称
    --1.查询数据 员工信息  最低工资  部门名称
    select deptno,min(sal) d_min from emp group by deptno
    --2.数据来源  emp   sql语句得到的临时表          dept
    --3.查询条件  员工工资=部门最低工资  本部门
    select e.empno,e.ename,e.sal,d_m.d_min ,dept.dname
           from emp e,
           (select deptno,min(sal) d_min from emp group by deptno) d_m,
           dept 
           where e.deptno = d_m.deptno and e.sal = d_m.d_min
            and e.deptno = dept.deptno
    --查询每个部门最低工资的员工信息
    select * from emp where sal = 
        (select min(sal) d_min from emp group by deptno)   
    select * from emp where sal = (800,950,1300) 

    select * from emp where sal in
        (select min(sal) d_min from emp group by deptno)  
        
    --查询不是领导的员工信息
    --1.员工信息
    --2.emp
    --3.不是领导
    --子查询空值问题 空值判断 用is null is not null  其余判断结果为UNKNOW
    select * from emp where empno not in( select mgr from emp )
    --需要处理空值 
    select * from emp where empno not in( select mgr from emp where mgr is not null )
    select * from emp where empno not in( select nvl(mgr,0) from emp  )
    select * from emp where empno not in( select mgr from emp where mgr >0 )
    /*
      子查询特殊使用
        exists 存在 表达式 (sql语句)
            判断结果集是否存在 如果存在 exists表达式返回true
                                  不存在 返回false
    */
    --简单示例
    select * from emp where exists(select * from dept)--所有员工信息
    select * from emp where exists(select * from dept where deptno=123)-- 没有记录
    --查询有员工的部门信息
    --1.部门信息
    --2.dept
    --3.部门有员工
    select deptno from emp;  ---得到了有员工的部门编号
    --使用in的方式实现
    select * from dept where deptno in (select deptno from emp) 
    /*
      普通子查询 执行顺序是 先执行子查询得到结果用于主查询
      exists表达式执行顺序更改
    */
    select * from dept where 
          exists(select * from emp where emp.deptno = dept.deptno)

    /*
     
      mySql 使用limit 提取特定记录条数
      oracle 使用 rownum 实现提取记录  用于分页使用
          rownum 是oracle数据库查询到记录 生成的一系列的数值 (1,2,3,4)
      rownum用于做大于判断 没有结果 必须使用子查询先生成rownum
      rowun用于小于判断可以直接查询出结果
      
      rowunm的执行原理 :
        1: 执行sql语句;
        2: 取到第一条记录,赋值rownum为1;
        3: 判断rownum是否满足条件,如果不满足放弃该行,满足返回该行.(不满足条件,rownum还是从1开始进行判断)
        4: 继续提取记录,继续生成rownum;
        5: 循环步骤3;
      
    */
    --rownum的示例
    select rownum,emp.* from emp where  rownum >5   --没有任何记录
    select rownum,emp.* from emp where  rownum <5  --前四条记录
    select rownum,emp.* from emp where  rownum =1 --只有一条
    select rownum,emp.* from emp where  rownum >1 --没有
    select rownum,emp.* from emp where  rownum >=1  --所有记录 
    --先生成rownum 再使用rownum过滤5条记录以后
    select * from (select rownum r,emp.* from emp) where r>5

    --查询员工表中 工资最高的前三名
    --工资按照倒序排序 
    select * from emp order by sal desc 
    --加入rownum
    select rownum,emp.* from emp order by sal desc 
    --先按照工资排序 再排序基础之上生成rownum
    select rownum,t.* from (select * from emp order by sal desc)t
    --判断rownum提取前三条
    select rownum,t.* from (select * from emp order by sal desc)t where rownum<4
    --提取6--10条记录
    select * from (
             select rownum r,t.* from (select * from emp order by sal desc)t) tt
             where r> 5 and r <11
          

    --查询员工表中工资大于本部门平均工资的员工信息
    --1.员工信息
    select deptno,avg(sal) d_a from emp group by deptno
    --2.emp
    --3.工资>部门平均工资  必须本部门
    
    行列转换
    Total        1980        1981        1982        1987
    14            1            10            1            2
        
    
    1: 尝试竖起一列
        上面是用年的数值做的别名,下面是年对应的入职员工数
        if年1987显示值是2
            decode(hire_year,'1987',hire_count)
        
    2: 使用聚合函数处理空值    
        
    select *
      from emp e, (select deptno, avg(sal) d_a from emp group by deptno) d_avg
     where e.sal > d_avg.d_a
       and e.deptno = d_avg.deptno  

    --统计每年入职的员工个数
    select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
          from emp  group by to_char(hiredate,'yyyy')
    --对结果集格式处理  先竖起来一列
    select decode(t.hire_year,'1987',t.hire_count) "1987" from 
      (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
          from emp  group by to_char(hiredate,'yyyy') ) t
    --把空值的记录过滤掉 聚合函数忽略空值的记录
    select sum(decode(t.hire_year,'1987',t.hire_count)) "1987" from 
      (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
          from emp  group by to_char(hiredate,'yyyy') ) t
    --补全其余的列
    select sum(decode(t.hire_year,'1980',t.hire_count)) "1980",
           max(decode(t.hire_year,'1981',t.hire_count)) "1981",
           min(decode(t.hire_year,'1982',t.hire_count)) "1982",
           avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
      from 
      (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
          from emp  group by to_char(hiredate,'yyyy') ) t
    --使用sum对hire_count做求和运算补上total
    select sum(t.hire_count) total,
           sum(decode(t.hire_year,'1980',t.hire_count)) "1980",
           max(decode(t.hire_year,'1981',t.hire_count)) "1981",
           min(decode(t.hire_year,'1982',t.hire_count)) "1982",
           avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
      from 
      (select to_char(hiredate,'yyyy') hire_year,count(*) hire_count 
          from emp  group by to_char(hiredate,'yyyy') ) t
          
    补充知识点：Oracle 中的分页查询
        ROWNUM:表示行号，实际上只是一个列,但是这个列是一个伪列,此列可以在每张表中出
        现。
        ROWID:表中每行数据指向磁盘上的物理地址。
          
    /*
      集合的运算
         交集 取两个集合共同的部分 intersect A(1,2,3) B(2,3,4) A交B (2,3)
         并集 取两个集合最大的部分 union A(1,2,3) B(2,3,4) A并B (1,2,3,4)
                                   union all                  A并B (1,2,3,2,3,4)
         差集 从一个集合去掉另外一个集合剩余的部分  minus A差B (1) 
    */
    --范例：工资大于1500,或者是20部门下的员工
    --不使用集合实现
    select * from emp where sal>1500 or deptno=20
    --使用集合实现 --不包含重复记录
    select * from emp where sal>1500
    union
    select * from emp where deptno=20
    --union all
    select * from emp where sal>1500
    union all
    select * from emp where deptno=20

    --范例：工资大于1500,并且是20部门下的员工
    select * from emp where sal>1500 and deptno=20
    --使用集合实现
    select * from emp where sal>1500
    intersect
    select * from emp where deptno=20

    --1981年入职的普通员工（不包括经理，总裁）  
    --使用以前知识实现
    select * from emp where '1981' = to_char(hiredate,'yyyy')
                  and job not in ('MANAGER','PRESIDENT')

    --使用集合实现
    select * from emp where '1981' = to_char(hiredate,'yyyy')
    minus         
    select * from emp where  job  in ('MANAGER','PRESIDENT')
    /*
      集合的使用场景
        用于做跨表合并数据使用
      合并数据规则
        必须合并的列的数量一致  列的数值类型相同
    */
    --查询公司下所有的员工信息
    select empno buisiness_no,ename  buisiness_name from emp
    union
    select mid,mname from manager;
    --数据类型不一致不能合并
    select empno buisiness_no,ename  buisiness_name from emp
    union
    select mname,mid from manager;


    --领导表
    create table manager(
           mid number(9),
           mname varchar(10)
    )
    insert into manager values(1,'zs');
    insert into manager values(2,'lisi');
    commit;