Oracle SQL总结

全部调试通过的。

select * from dual;

-- Oracle 日期格式函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
select to_char(sysdate,'yyyy') as nowYear from dual;
select to_char(sysdate,'mm') as nowMonth from dual;
select to_char(sysdate,'dd') as nowDay from dual;
select to_char(sysdate,'hh24') as nowHour from dual;
select to_char(sysdate,'mi') as nowminute from dual;
select to_char(sysdate,'ss') as nowSecibd from dual;

--获得小时数 
 select sysdate ,to_char(sysdate,'hh') from dual;
 select sysdate ,to_char(sysdate,'hh24') from dual;

select to_date('2016-10-10 12:10:10','yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
select to_date('2016-10-10','yyyy-mm-dd hh24:mi:ss') as nowTime from dual;
select to_date('2016-10-10','yyyy-mm-dd') as nowTime from dual;

select to_char(to_date(222,'J'),'Jsp') from dual;

--求某天是星期
select to_char(to_date('2016-7-13','yyyy-mm-dd'),'day') as weekDay from dual;

select to_char(to_date('2016-7-13','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American') as weekDay from dual;

--  两个日期间的天数 
select floor(sysdate-to_date('2016-7-10','yyyy-mm-dd')) as day from dual;
select floor(to_date('2016-10-10','yyyy-mm-dd')-to_date('2016-7-10','yyyy-mm-dd')) from dual;

-- 时间为null的用法  
select 1,to_date(null) from dual;

--变量列表
select * from nls_session_parameters;
select * from V$nls_Parameters where parameter='NLS_LANGUAGE';

-- 查找月份
select months_between(to_date('2016-9-10','yyyy-mm-dd'),to_date('2016-1-10','yyyy-mm-dd')) as allMonth from dual;
select months_between(to_date('2016-5-20','yyyy-mm-dd'),to_date('2016-1-10','yyyy-mm-dd')) as allMonth from dual;

-- 当前时间
select sysdate from dual;
 
--  找出今年的天数

select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual;

-- 一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual;
 

-- round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1, round(sysdate) S2 , round(sysdate,'year') YEAR, round(sysdate,'month') MONTH , round(sysdate,'day') DAY from dual

-- trunc[截断到最接近的日期,单位为天] ,返回的是日期类型

select sysdate S1,trunc(sysdate) S2,trunc(sysdate,'year') YEAR,trunc(sysdate,'month') MONTH ,trunc(sysdate,'day') DAY from dual;        

-- 返回日期列表中最晚日期
 select greatest('2001-1-04','2020-9-04','2010-2-04') from dual;
 
-- 更新时间
select sysdate,to_char(sysdate+1*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,add_months(sysdate,3) as newTime from dual ;
select sysdate,to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual ;

-- 字符串截取
select substr('this is one char for test',1,5) from dual;

-- 查找子串位置
select instr('this is one char for test','char') from dual;

--字符串连接
select 'hello'||'HanMeiMei' as sayHello from dual;

select concat( 'hello','HanMeiMei') as sayHello from dual;

-- 去掉字符串中的空格
select ltrim(' this') s1,rtrim('zhangsan ') s2,trim(' lisi ') s3 from dual;

-- 去掉前导和后缀
select trim(leading 1 from 11111345345345111111) s1,trim(trailing 1 from 11111345345345111111) s2,trim(1 from 11111345345345111111) s3 from dual;

-- 字符串长度 
select length('this is test for oracle') from dual;

-- initcap（首字母变大写） ,lower(变小写),upper(变大写)
select lower('ABC') s1, upper('def') s2, initcap('efg') s3 from dual;

-- Replace
select replace('abc','b','xy') from dual;

-- translate
select translate('abc','b','qqqq') from dual;

-- lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;

-- decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值
select grade_id,decode(grade_id,1,'一年级',2,'二年级',3,'三年级','无分班') from student;

-- case[实现switch ..case 逻辑]
select 
 case when grade_id=1 then '一年级' 
   when grade_id=2 then '二年级' 
    when grade_id=3 then '三年级' 
      else 'unkonwn' end
        from student;

-- 取整函数（ceil 向上取整,floor 向下取整）
 select ceil(66.6) N1,floor(66.6) N2 from dual;

-- 求余数
select mod(9,2) from dual;

-- 返回固定小数位数 (round:四舍五入，trunc:直接截断)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

-- 返回值的符号(正数返回为1,负数为-1)
select sign(-32),sign(293) from dual;

-- to_number() 转换为数字类型
select to_number(to_char(sysdate,'hh12')) from dual;

-- nvl(ex1,ex2):ex1值为空则返回ex2,否则返回该值本身ex1（常用）
select empno,sum(sal+nvl(comm,0)) from emp group by empno;

--返回列表中第一个非空表达式
select comm,sal,coalesce(comm,sal,sal*10) from emp;

-- nvl2(ex1,ex2,ex3) :如果ex1不为空，显示ex2,否则显示ex3
select nvl2(comm,ename,'') as HaveCommName,comm from emp;

-- 分组函数; 求部门30 的最高工资，最低工资,平均工资，总人数，有工作的人数，工种数量及工资总和
select * from emp where deptno=30;
select max(sal) as maxSal,min(sal) as minSal,avg(sal) as avgSal,count(*) as TotalEMP,sum(sal) as totalSal,count(job),count(distinct(job)) from emp where deptno=30;

-- 按部门分组求最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和
select max(sal) as maxSal,min(sal) as minSal,count(*) as TotalEMP,count(job) as hasJob,count(distinct(job)) as jobCount,sum(sal) as totalSal from emp group by deptno;

-- 部门30的最高工资，最低工资，总人数，有工作的人数，工种数量及工资总和 
select max(sal) as maxSal,min(sal) as minSal,count(*) as TotalEMP,count(job) as hasJob,count(distinct(job)) as jobCount,sum(sal) as totalSal from emp group by deptno having deptno=30;
 

参考文章：http://www.cnblogs.com/chuncn/archive/2009/04/29/1381282.html

转载于:https://my.oschina.net/royweb/blog/710998