光环大数据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教希望对大家有所帮助！