[Investor Relations]  |  官方微博

大数据培训

美国上市公司 · 亿元级外企Java培训企业

  • 咨询电话4001118989
大数据培训 > 资料库 > 达内Oracle培训学习教程
  • 达内Oracle培训学习教程

    发布:达内  来源:达内  时间: 2015年01月21日

  • 近日,由达内教学部项目经理提供了一部分达内java培训学员正在学习的Oracle培训教程,现分享给大家...

  • 近日,由达内教学部项目经理提供了一部分达内java培训学员正在学习的Oracle培训教程,现分享给大家:


    1.高级函数
      
      *1)分支判断函数(适合==判断)
        decode(字段或计算表达式,
              条件值1,结果值1,
              条件值2,结果值2[,默认值]
              )
     
       if(字段或计算表达式 == 条件值1){
          return 结果值1;
       }else if(字段或计算表达式 == 条件值2){
          return 结果值2;
       }else{
          return 默认值;
       }
     
       //将查询的sal列显示大写形式
      select empno,ename,
             decode(sal,800,'捌佰元',
               1100,'壹仟一佰元',
               1300,'壹仟叁佰元',
               1600,'壹仟陆佰元',
               '默认值') upper
      from emp;
      //统计部门编号,工资大于等于2000的人数,
      //小于2000的人数,该部门总人数
      select deptno,count(*) total,
       sum(decode(sign(sal-2000),-1,0,1)) great,
       sum(decode(sign(sal-2000),-1,1,0)) least
      from emp
      where deptno is not null
      group by deptno;
      
    ==========sign函数介绍(补充)===========
      sign(n):判断n>0返回1;n=0返回0;n<0返回-1.
      select sign(10),sign(0),sign(-2)
      from dual;
     
      select ename,sign(sal-2000) from emp;
    =====================================
     
     *2)case函数 (适合区间,>,<判断)
       case when 判断表达式 then
            when 判断表达式 then
            .....
       end
     
      select deptno,count(*) total,
       sum(case when sal>=2000 then 1
           end) great,
       sum(case when sal<2000 then 1
           end) least
      from emp
      where deptno is not null
      group by deptno;
     
    //统计部门中男人数和女人数
     create table j20(
      id number(7),
      name varchar(20),
      sex char(1), --'M'或'F'
      deptno number(7));
     
     select deptno,
            sum(decode(sex,'M',1,0)) as male,
            sum(decode(sex,'F',1,0)) as female
     from j20
     group by deptno;
     
     select deptno,
            sum(case when sex='M' then 1 
                else 0 end) as male,
            sum(case when sex='F' then 1
                else 0 end) as female
     from j20
     group by deptno;
     //基于EMP表查询部门编号,工资1000以内人数,
     1000-2000人数,2000以上人数
     select deptno,
        sum(case when sal<1000 then 1
                 else 0 
            end) "1000以内",
        sum(case when sal>=1000 and sal<2000 then 1
                 else 0
            end) "1000-2000",
        sum(case when sal>=2000 then 1
                 else 0
            end) "2000以上"
     from emp
     group by deptno;
     
     //查询调薪结果,规则:MANAGER涨10%,CLERK涨20%,
      其他人涨5%
     select empno,ename,job,sal,
            decode(job,'MANAGER',sal*1.1,
                       'CLERK',sal*1.2,
                       sal*1.05) "加薪之后"
     from emp;
     
     
     3)集合操作
     
      A={1,3,5,7,8}
      B={3,5,6,9}
      A和B并集={1,3,5,6,7,8,9} //合并两个集合元素union
      A和B交集={3,5} //两个集合相同元素 intersect
      A-B求差={1,7,8} //A有的而B没有的元素minus
      B-A求差={6,9} //B有的而A没有的元素minus
     
      *a.求并
       union : 将两个select结果合并(去除重复记录)
       union all :将两个select结果合并(不去除重复记录)
      
      select empno,ename,sal
      from emp
      where sal>=1000 and sal<=2000
      union
      select empno,ename,sal
      from emp
      where sal>=1500 and sal<=3000;
     
     b.求交集
       intersect:将两个select结果相同记录提取.
     
      select empno,ename,sal
      from emp
      where sal>=1000 and sal<=2000
      intersect
      select empno,ename,sal
      from emp
      where sal>=1500 and sal<=3000;
     
     c.求差集
      minus : 计算前面select有的而后面select没有的记录
     
      select empno,ename,sal
      from emp
      where sal>=1000 and sal<=2000
      minus
      select empno,ename,sal
      from emp
      where sal>=1500 and sal<=3000;
     
      
      select empno,ename,sal
      from emp
      where sal>=1500 and sal<=3000 
      minus
      select empno,ename,sal
      from emp
      where sal>=1000 and sal<=2000
     
     //查询工资最高的前3名和最低前3名的员工信息
    select empno,ename,sal
    from
      (select empno,ename,sal 
       from emp
       order by sal desc)
    where rownum<=3
    union
    select empno,ename,sal
    from
      (select empno,ename,sal 
       from emp
       order by sal asc)
    where rownum<=3
    order by sal asc; --将合并结果按sal升序排列
     
     //查询既是工资排名前5的,也是最早入职前5的员工
    select empno,ename
    from
      (select empno,ename 
       from emp
       order by sal desc)
    where rownum<=5
    intersect
    select empno,ename
    from
      (select empno,ename
       from emp
       order by hiredate)
    where rownum<=5;
     
     
     =====集合操作注意事项======
    --想控制结果排序,需要在第二个select中使用order by
    --两个select语句查询的列数必须相同
    --结果集字段名以第一个select的列名为准
    --两个select列数相同,类型相同就可以进行集合操作
     (名称可以不同)
     
     4)排名函数(分析函数)
       row_number(),rank(),dense_rank()
      使用格式:
      row_number() over(partition by 字段1 
                        order by 字段2)
      作用:按将查询结果按字段1分组,然后按字段2排序,
      再调用row_number(),rank(),dense_rank()编号.
     
      *a.row_number()
        不允许重复,编号唯一并且连续。
       select empno,ename,sal,
         row_number() over(order by sal desc) as rn
       from emp;
       //将查询结果按deptno分组,组内记录再按sal降序排列
       //然后调用row_number()对组内记录编号
       select ename,deptno,sal,
         row_number() over(partition by deptno
                      order by sal desc) as rn
       from emp;
     
      *b.rank()
        允许并列排名(重复),编号不连续。例如1,2,3,3,5,6
       select empno,ename,sal,
         rank() over(order by sal desc) as rn
       from emp;
     
      c.dense_rank()
        允许并列排名(重复),编号连续。例如1,2,3,3,4,5,6
       select empno,ename,sal,
         dense_rank() over(order by sal desc) as rn
       from emp;
     
    //查询工资最高的前3名,(允许并列情况,并列后编号跳跃)
    select empno,ename,sal,rn
    from(
      select empno,ename,sal,
          rank() over(order by sal desc) as rn
      from emp
     ) where rn<=3;
    //where rn>=5 and rn<=10;--查询工资排在5-10位的
    //查询每个部门工资最高的前2名(不允许重复)
    select empno,ename,sal,deptno
    from (
      select empno,ename,sal,deptno,
             row_number() over(partition by deptno
                 order by sal desc) rn
      from emp
    )
    where rn<=2;
    //查询每个职位工资最高的员工信息(允许重复)
    select empno,ename,job,sal
    from(
      select empno,ename,job,sal,
         rank() over(partition by job
                     order by sal desc) rn
      from emp
    )
    where rn=1;
     
    ====row_number()和rownum区别=====
    --rownum是一个伪劣字段;row_number()是个函数
    --rownum序号列是唯一连续的编号;row_number()也可以
      唯一连续的编号.
    --row_number()可以进行组内编号,每个组都从1开始。
     
     5)高级聚合函数
       rollup(),cube(),grouping sets()
      上面这几个函数,是对group by分组功能做的功能扩展。
     
     a.rollup()
      功能:在原结果基础上追加一行总合计记录
      rollup(字段1,字段2)会追加按字段1进行的合计记录,
      最后再追加一个总合计记录
     
      select deptno,count(*)
      from emp
      group by rollup(deptno);
     
      select deptno,job,count(*)
      from emp
      group by rollup(deptno,job)
      order by deptno;
      ----等价于下面写法-----
      select deptno,job,count(*)
      from emp
      group by deptno,job
      union
      select deptno,null,count(*)
      from emp
      group by deptno
      union
      select null,null,count(*)
      from emp
      order by deptno;
      
      结论:group by rollup(字段1,字段2,字段3)
        --查询按字段1,字段2,字段3分组统计结果
        --追加按字段1和字段2的合计
        --追加按字段1的合计
        --追加总合计记录
     
      b.cube()
     
      select deptno,count(*)
      from emp
      group by cube(deptno)
      order by deptno;
     
      cube(字段1):作用于rollup(字段1)相同。
       追加一行总合计记录。
     
      select deptno,job,count(*)
      from emp
      group by cube(deptno,job)
      order by deptno;
      --查询group by deptno,job分组统计结果
      --查询group by deptno分组统计结果
      --查询group by job分组统计结果
      --查询没有group by统计结果
     
     结论:group by cube(字段1,字段2,字段3)
      --查询group by 字段1,字段2,字段3统计结果
      --查询group by 字段1,字段2统计结果
      --查询group by 字段1统计结果
      --查询group by 字段2统计结果
      --查询group by 字段3统计结果
      --查询group by 字段2,字段3统计结果
      --查询group by 字段1,字段3统计结果
      --查询没有group by统计结果
     
     c.grouping sets()
      
      select deptno,count(*)
      from emp
      group by grouping sets(deptno)
      order by deptno;
      --上面和没加grouping sets()效果一样
     
      select deptno,job,count(*)
      from emp
      group by grouping sets(deptno,job)
      order by deptno;
     --只返回cube规则追加的分组统计结果
     --不包含没有group by和group by deptno,job的统计
      
     
    ==========作业(面试题)==============
     
    1.1.18. 数据库有两张表一个学生表(id,name,sex),
    一个学生成绩表(id,chineses,English,math),
    要求查询学生基本信息以及各科成绩和总成绩,
    总成绩要求在200到300之间,学生姓名降序。 【锐志信息】
    1.1.19. 现有关系数据库表如下:
    学生表(学号 char(6), 姓名,性别,身份证号)
    课程表(课号 char(6), 名称)
    成绩表(id,学号,课号,分数)
     
    用sql实现下面2题:
    1.检索姓马的女同学情况(姓名,身份证号)
    2.检索有一门或一门以上课程成绩大于等于90的所有学生信息(学号,姓名)
    1.1.20. 有三张表,学生表 Student,课程 Coruse,
    学生课程表 SC,学生可以选修多门课程,
    一门课程可以被多个学生选修,通过 SC 表关联。
    1)写出建表语句;
    2)写出 SQL 语句,查询选修了所有选修课程的学生;
    3)写出 SQL 语句,查询选修了至少2门以上的课程的学生。


    以上java教希望对大家有所帮助!
  • 上一篇:数据库知识总结

    下一篇:For、Foreach与Iterator的相同点与不同点

网站导航
2001-2016 达内时代科技集团有限公司 版权所有 京ICP证8000853号-56