Oracle常用知识总结

19.select grade,decode(sign(grade-90+1),
1,'A',
decode(sign(grade-75+1),
1,'B',
20.substr('hello',2,3),第一个参数为目标字符串，第二个参数为输出字符串的起点，第三个参数输出字符串的长度
select trim (trailing 'x' from 'xxxxDWEYExxxx') from dual; --xxxxDWEYE
select trim (leading 'x' from 'xxxxDWEYExxxx') from dual; --DWEYExxxx
/******************/
1.select * from provider where provider_name like '__西__工%' 【找出第三个字为西，第六个字为工的记录】
2.select name from provider_contact intersect select name from users 【在两个表中都存在的记录】
select least('3', '5') from dual; --取较小值
select greatest('3', '5') from dual -- 取最大值
15.回车chr(13),换行chr(10),空格chr(32)
select ascii('A') A from dual;看对应的数字
,extract(second from dt2-dt1) second
from (
select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1
,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
select translate('acdd','cd','ef') from dual; --aeff，替换字符
select replace('acdd','cd','ef') from dual; --aefd,替换字符串
tanslate ( 'char' , 'from_string' , 'to_string' )
from dual
)
13.abs(XX)求一个数字的绝对值，mod(A,B)取模运算%
14.sign(n),取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
判断是否为数字：
①.select provider_contact.* from provider_contact where ltrim(provider_,'0123456789.') is null
②.select nvl2(translate('abc123','#1234567890.','#'),'字符串','数字') from dual;
②对数字操作，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
TRUNC（89.985，2）=89.98,TRUNC（89.985）=89,TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记
这里的“trim_character”参数只允许包含一个字符，不支持多字符。
若select trim (leading 'xy' from 'xyxxDWEYExyyx')from dual; --error
可以先使用rtrim
select rtrim('xyxxDWEYExyyx','xy') from dual; --xyxxDWEYE
--本季
select trunc(sysdate,'Q') from dual;select add_months(trunc(sysdate,'Q'),3)-1 from dual;
--本年
select trunc(sysdate,'yyyy') from dual;select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;
10.trunc:①对日期操作：select trunc(sysdate,'DD') AA,trunc(sysdate,'MM') BB,trunc(sysdate,'yyyy') CC,trunc(sysdate,'day') DD,trunc(sysdate) EE from dual;
【AA，EE列得到当天，BB列得到当月第一天，CC列得到当年第一天，DD列得到本周第一天】
3.select name from provider_contact minus select name from users 【存在于第一个表中但不存在于第二个表中的记录】
4.max,min既可以处理数值也可以处理字符，max(name)将返回最高字符串
5.select task_serial.* , add_months(done_date,-1) as new_date from task_serial
decode(sign(grade-60+1),1,'C','D')
)
) grade_char
from t8
order by grade_char,grade;
--本周第一天与最后一天
select trunc(sysdate,'d')+1 from dual;select trunc(sysdate,'d')+7 from dual;
--本月
select trunc(sysdate,'mm') from dual;select last_day(trunc(sysdate)) from dual;
获取两个日期之间的具体时间间隔，extract函数是最好的选择
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
或者
select trim(translate('xyxxDWEYExyyx','xy',' ')) from dual; --DWEYE
select translate('xyxxDWEYExyyx','#xy','#') from dual; --DWEYE
“xy”不表示整个“xy”字符串进行匹配，而是发现任意的字符“x”或字符“y”均做删除操作。
然后使用ltrim
select ltrim('xyxxDWEYExyyx','xy') "e.g." from dual; --DWEYExyyx
联合使用rtrim和ltrim函数达到目的
select ltrim(rtrim('xyxxDWEYExyyx','xy'),'xy') from dual; --DWEYE
第二个参数是需要将字符串扩充的宽度，第三个参数是加宽部分用什么字符来填充
18.ltrim(column1,' '),rtrim(column1,' ')
若第二个参数不是空格，那么该函数会像剪除空格那样剪除所指定的字符
select ltrim(code,'S'),companies.* from companies
ceil(n) 取大于等于数值n的最小整数；floor(n)取小于等于数值n的最大整数
11.select cast(9.123 as number(4,3)) from dual,【number(4,3)表示一共4位数，其中小数3位】
select * from tablename order by cast(总积分 as int) desc
16.initcap(xx),将参数第一个字母变大写，此外其他的字母转换成小写
lower(xx),将参数全部转化为小写，upper(xx),将参数全部转化为大写
17.lpad(column1,10,'*'),rpad(column1,10,'*'),三个参数，第一个参数是需要处理的字符串，
如果from_string比to_string长，那么from_string中多余的字符将被移除。
select translate('acdd','acd','ef') from dual;--ef (a由e代替，c由f代替,d就被移除)