oracle--2

1、日期函数:  日期 数据库所在服务器的日期  
 
 1)、当前日期: sysdate == current_date
    select sysdate from dual;
    select current_date from dual;

 2)、加入天数 + -
    select sysdate+10 from dual;
    select ename,hiredate,hiredate+20 afterH from emp;

 3)、加入月份  add_months()
    select ename,hiredate,add_months(hiredate,5) addm  from emp;（雇用日期5个月之后的时间）

 4)、最后一天 last_day()
    select last_day(sysdate) from dual;
    select ename,hiredate,last_day(hiredate) lastday from emp;（雇佣日期的当月最后一天的日期）

 5)、日期间隔月份 months_between(d1,d2)
    select ename,months_between(sysdate,hiredate) betw from emp;（雇佣日期离 现在的月份）
 
 6)、下一个的日期 next_day(d1[,c1])
    select next_day(sysdate,'星期三') monday from dual;




2、日期的转换（ 转换函数）
 格式: y m mon d  hh12-->默认 hh24  mi  s  (不区分大小写)

    to_date():字符转日期
    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') d from dual;
    select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') d from dual;

     to_char():日期转字符
    select to_date('2012-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss') mydate from dual;



3、其他函数

   1) nvl(字段，默认值) 字段==null?默认值:字段值
    select nvl(comm,0) nvlc from emp;（如果comm为空，comm=0,如果不为空，为原值）
    
   2) decode()    -->if else if else if ...else
    select ename,deptno,sal,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3,sal*1.4) raiseSal from emp;
    （如果deptno为10，sal乘以1.1）
    （如果deptno为20，sal乘以1.2）
    （如果deptno为30，sal乘以1.3）
    （否则，sal乘以1.4）

   3) case when  then else end --> switch case default
    select ename,deptno,sal,
           (case deptno
             when 10 then sal * 1.1
             when 20 then sal * 1.2
             when 30 then sal * 1.3
             else sal * 1.4 end) raiseSal
      from emp;


4、练习题：
  1)、查询82年员工
    select * from emp where to_char(hiredate,'yyyy') =1982;
    select * from emp where hiredate like '%82%';

  2)、显示员工雇佣期 6 个月后下一个星期一的日期
    select ename,hiredate,next_day(add_months(hiredate,6),'星期一') myday from emp ;


  3)、找没有上级的员工，把mgr的字段信息输出为 "boss"

    select empno,ename, 'boss' mgr from emp where mgr is null;（为mgr 设置虚别名）

    （用nvl(字段,值)，来代替其默认值）
    select empno,ename,  nvl(to_char(mgr),'boss') mgr from emp where mgr is null;



5、
count ：统计记录数
min max :最小 最大
avg :平均值
sum :求和
    
  1)、count ：统计记录数
    select count(*) n from emp;（统计员工数：2种）
    select count(empno) n from emp;
    select count(*) n from emp where deptno=20;（统计部门编号为20的员工数）
      理解：
    select 1 t,ename  from emp;（1 t为伪列）
    select count(1) n from emp;（在对表结构不清晰时，构建一个伪列，并进行统计）
      
      注意：组函数出现后，select 里面 不能出现非组即单条信息

  2)、min max :最小 最大
    select ename from emp where sal = (select min(sal) from emp);（求出最低工资的员工姓名）
 
  3)、avg :平均值
    （检索 员工的薪水 大于  所在 岗位的平均薪水的员工名称）
    select ename,sal from emp e1 where sal >(
        select avg(sal) from emp e2 where e2.job =e1.job);

  4)、sum :求和
    select sum(sal) from emp where 1=1;（薪水总和）
    select sum(sal+nvl(comm,0))*12 total from emp;(年薪总和)



6、练习题：

  1)、员工的平均薪水
    select avg(sal) from emp where 1=1;

  2)、所有员工的平均佣金 处理 null
    select avg(nvl(comm,0)) from emp where 1=1;

  3)、所有员工的薪水、佣金总和
    select sum(sal+nvl(comm,0)) from emp;


  4)、员工人数
    select count(1) from emp;

  5)、部门编号为30的员工人数、平均薪水、最高工资、最低工资、工资总和
    select count(1) ,avg(sal),max(sal) ,min(sal),sum(sal) from emp where deptno=30;


  6)、检索员工薪水超过所在部门的平均薪水的员工名称
    select ename from emp e where sal>(select avg(sal) from emp where deptno =e.deptno);




7、分析函数 sum ..over（在之前基础上进行累加）

    select deptno,ename,sum(1) over(order by deptno) n from emp ;


    select deptno,ename,sal
        from emp
         order by deptno;
     
     
    select deptno,ename,sal,
         sum(sal) over (order by ename) 连续求和,
         sum(sal) over () 总和,                -- 此处sum(sal) over () 等同于sum(sal)
         100*round(sal/sum(sal) over (),4) "份额(%)"
          from emp;
          
    select deptno,ename,sal,
       sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
       sum(sal) over (partition by deptno) 部门总和,   -- 部门统计的总和，同一部门总和不变
       100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
       sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
       sum(sal) over () 总和,   -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
       100*round(sal/sum(sal) over (),4) "总份额(%)"
       from emp;
    


8、分组 group by: 将符合条件的记录 进一步分组


1、解析步骤
from where  group by having  select  order by
2、 group by 分组字段  ， -->多个字段
3、group by 之后，当前层的 select 只能出现  分组函数、分组字段
  group by之后的字段，可以出现在select 中
4、having 过滤组
   where 过滤每条记录   
   不能使用当前层的别名和伪列
5、组函数 可以出现的位置为
  1)、select
  2)、having
  不能出现在where   


  1)、按部门 的岗位求出平均薪水
    select deptno,job,avg(sal) avs from emp where 1=1 group by deptno,job order by deptno desc;


  2)、查看 部门的平均薪水 大于2500的部门编号  分组的同时过滤组数据

    （分组的同时过滤组数据）
    select deptno,avg(sal) avs from emp where 1=1 group by deptno having avg(sal)>=2500;
    
    （先分组 ，后过滤 行记录）
    select *
      from (select deptno, avg(sal) avgsal from emp where 1 = 1 group by deptno)
     where avgsal >= 2500;



9、group by 和having的练习

  1)、求部门平均薪水
    select deptno, avg(Sal) from emp group by deptno;

  2)、按部门求出工资大于1300人员的 部门编号、平均工资、最小佣金、最大佣金,并且最大佣金大于100
    select deptno, avg(sal), min(comm), max(comm) from emp where sal
        > 1300 group by deptno having max(comm) > 100;

  3)、查询10号部门中编号最新入职的员工，工龄最长的员工的个人信息。
    select *
  from emp
 where deptno = 10
   and hiredate in
       (select max(hiredate)
          from emp
         where deptno = 10
        union
        select min(hiredate) from emp where deptno = 10);

  4)、使用一条sql语句，查询每门课都大于80分的学生姓名。
    
id name course score
1  张三    语文     81
2  张三    数学     75
3  李四    语文     81
4  李四    数学     90
5  王五    语文     81
6  王五    数学     100
7  王五    英语     90

create table tb_student(
       id number(4) ,
       name varchar2(20),
       course varchar2(20),
       score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;


select * from tb_Student;（统计 课程数 去重 再统计）

select count(distinct course ) cn from tb_student;
（查询每门课都大于80分  -->按学生分组 过滤组)

select name ,min(score) from tb_student group by name having min(score)>80;
--综合
select name, min(score),count(*)
  from tb_student
 group by name
having min(score) > 80 and count(*)=(select count(distinct course) cn from tb_student) ;