大数据培训
美国上市大数据培训机构

400-111-8989

热门课程

达内Oracle培训学习教程

  • 时间:2015-01-21
  • 发布:达内
  • 来源:达内

近日,由达内教学部项目经理提供了一部分达内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的相同点与不同点

达内Java教程:Java中this的用法总结

数据库知识总结

选择城市和中心
贵州省

广西省

海南省

台湾