oracle——SQL复习05

--返回季度 1 2 3 4 
select to_char(sysdate, 'Q') from dual;  
--返回本月第几周  
SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL;  
--DD 当月第几天 
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL; 
--D 周内第几天 
SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL; 
-- 周几
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL; 
--下周几的时间
select next_day(sysdate,'星期四') from dual ; 
--返回两个时间的年代数
select to_char(sysdate,'YYYY') - to_char(t.hiredate,'YYYY') year from emp t where t.empno = 7369 ;
--返回两个时间精确的年数
select months_between(sysdate,t.hiredate)/12 year from emp t where t.empno = 7369 ;
--返回两个时间的精确月份数
select months_between(sysdate,t.hiredate) month from emp t where t.empno = 7369 ;
--返回两个时间的精确天数
select trunc(sysdate - t.hiredate) day from emp t where t.empno = 7369 ;
 




select * from ctl_01;  
select * from ctl_02;  
select * from ctl_01_bak; 
select * from ctl_02_bak; 

--create table ctl_01_bak as select * from ctl_01;

--merge into函数  匹配的数据就更新,不匹配的数据就添加
merge into ctl_01   
 using ctl_02  
 on (ctl_01.id = ctl_02.id)
when matched then  update set ctl_01.age = ctl_02.age 
when not matched then insert values(ctl_02.id,'HELLO',ctl_02.age);

Rollback; 

select * from ctl_01 t;  
--any 配合< <= 时 获得的是最大值   
select * from ctl_02 t2 where t2.id <= any (select id from ctl_01);
--相当于
select * from ctl_02 t2 where t2.id <= (select max(id) from ctl_01);

-- select * from ctl_02 t2 where t2.id >= any (select id from ctl_01); 
--all 配合 < = 获得的是最小值   
select * from ctl_02 t2 
where t2.id <= all (select id from ctl_01); 
-- select * from ctl_02 t2 where t2.id >= all (select id from ctl_01); 

--取偶数列
select * from ctl_02 t where mod(t.id,2) = 0 ;  



--大写
select upper('hello world') from dual;
--小写
select lower('HELLO WORLD') from dual; 
--首字母大写
select initcap('hello world,nice!') from dual;  

--字段截取
select substr('hello world, nice!',7,5) from dual ;  
--字段连接
select concat('Hello ','World') col from dual;  
select 'Hello' || ' World' col from dual;  

--替换函数
select t.name, replace(t.name,'s','x') from ctl_01 t; 



--转换函数nvl
select nvl('xzy',0) from dual; 
select nvl(null,0) from dual; 
--空值为第三个值,非空值就替换成'xzy'
select nvl2(null,'xyz',0) from dual; 
select nvl2(123,'xyz',0) from dual ; 

--匹配函数case when  .. then . when .. then ..else.end 
select t.comm,
       case
         when nvl(t.comm, 0) = 0 then
          '没有'
         when nvl(t.comm, 0) < 1000 then
          '一般'
         else
          '不错'
       end comm
  from emp t;

--匹配函数 decode()  
select decode(sign(nvl(t.comm, 0)),  0, '没', 1, '有奖金'),
       sign(nvl(t.comm, 0)),
       t.comm
  from emp t;

select decode(sign(nvl(t.comm, 0) - 1000), -1, '一般', 1, '不错'), t.comm
  from (select decode(sign(nvl(t.comm, 0)), 0, '没', 1, '有奖金') bak,
               sign(nvl(t.comm, 0)),
               t.comm
          from emp t) t
 where t.bak = '有奖金'