ORACLE 常用

select sysdate from dual;--2020-03-24 23:07:53
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; --日期转化为字符串  2020-03-24 23:08:13
select to_char(sysdate,'yyyy') as nowYear from dual; --获取时间的年 2020
select to_char(sysdate,'mm') as nowMonth from dual; --获取时间的月 03
select to_char(sysdate,'dd') as nowDay from dual; --获取时间的日  24 
select to_char(sysdate,'hh24') as nowHour from dual; --获取时间的时  23 
select to_char(sysdate,'mi') as nowMinute from dual; --获取时间的分  07
select to_char(sysdate,'ss') as nowSecond from dual; --获取时间的秒 53


select to_date('2020-03-24 23:07:53','yyyy-mm-dd hh24:mi:ss') from dual;
select to_char( to_date(222,'J'),'Jsp') from dual ;--显示Two Hundred Twenty-Two 


 select to_char(to_date('2020-03-24','yyyy-mm-dd'),'dd') from dual; --   24
  select to_char(to_date('2020-03-24','yyyy-mm-dd'),'mm') from dual; --  03
    select to_char(to_date('2020-03-24','yyyy-mm-dd'),'yyyy') from dual;  --2020
------------------------------------
    select sysdate from  dual ;--2020/3/24 23:20:46
    select ''||sysdate||'' from dual ;--24-3月 -20
    
    
 select to_char(to_date('2020-03-24','yyyy-mm-dd'),'dd-mm-yyyy','NLS_DATE_LANGUAGE = American') from dual;--twenty twenty  
 
 SELECT TO_CHAR(sysdate,'MM/DD/YYYY HH:MI AM',  'NLS_DATE_LANGUAGE = American')  
 FROM DUAL;--03/24/2020 11:28 PM
 
 --求两个日期差
 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;   --6563
  select floor(sysdate - date'2002-04-05') from dual;--6535
  
  
   --求月份差
   select months_between( date'2010-9-1'，sysdate) MONTHS FROM DUAL;  --   -114.773613724612
   
   --trunc
   select trunc(sysdate) from  dual;--   2020/3/24
   select trunc(sysdate,'year') from  dual;--  2020/1/1
   select trunc(sysdate,'month') from  dual;--2020/3/1  当月第一天
   select trunc(sysdate,'day') from  dual;--2020/3/22 本周第一天 星期天
   
select   trunc(sysdate,'month')-1 from dual;--2020/2/29   上月最后一天

 

 

select* from emp  ;

 

 

--row_number() 顺序排序 常见去重
select *from (
select t.* , row_number()over (partition by job , deptno order by sal desc) as row_num
from emp t  order by row_num desc )
where row_num =1 ;

 

 

 

 --分析函数

select t.*, sum(sal)over(order by job , sal  ) as job与sal累加,
sum(sal)over() as 总和
 from emp t    ;

 

 

　

 --跳跃排序
 select t.*, rank()over(order by job   ) as 排名
 from emp t    ;
 

 

 

--连续排序
 select t.*, dense_rank()over(order by job   ) as 排名
 from emp t    ;