oracle常函数用例.docVIP

oracle常用函数一、运算符 算术运算符：+ - * / 可以在select 语句中使用 连接运算符：|| select deptno|| dname from dept; 比较运算符： = = != = like between is null in 逻辑运算符：not and or 集合运算符： intersect ，union， union all， minus 要求：对应集合的列数和数据类型相同 ???? 查询中不能包含long 列 ???? 列的标签是第一个集合的标签 ???? 使用order by时，必须使用位置序号，不能使用列名 ??? 例：集合运算符的使用： intersect ，union， union all， minus select * from emp intersect select * from emp where deptno=10 ; select * from emp minus select * from emp where deptno=10; select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行 select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行 二.ORACLE日期时间函数大全 ?? TO_DATE格式(以时间:2007-11-02?? 13:45:25为例) ?? ??????? Year:????? ??????? yy two digits 两位年??????????????? 显示值:07 ??????? yyy three digits 三位年??????????????? 显示值:007 ??????? yyyy four digits 四位年??????????????? 显示值:2007 ??????????? ??????? Month:????? ??????? mm??? number???? 两位月????????????? 显示值:11 ??????? mon??? abbreviated 字符集表示????????? 显示值:11月,若是英文版,显示nov???? ??????? month spelled out 字符集表示????????? 显示值:11月,若是英文版,显示november ????????? ??????? Day:????? ??????? dd??? number???????? 当月第几天??????? 显示值:02 ??????? ddd??? number???????? 当年第几天??????? 显示值:02 ??????? dy??? abbreviated 当周第几天简写??? 显示值:星期五,若是英文版,显示fri ??????? day??? spelled out?? 当周第几天全写??? 显示值:星期五,若是英文版,显示friday??????? ??????? ddspth spelled out, ordinal twelfth ???????????? ????????????? Hour: ????????????? hh??? two digits 12小时进制??????????? 显示值:01 ????????????? hh24 two digits 24小时进制??????????? 显示值:13 ????????????? ????????????? Minute: ????????????? mi??? two digits 60进制??????????????? 显示值:45 ????????????? ????????????? Second: ????????????? ss??? two digits 60进制??????????????? 显示值:25 ????????????? ????????????? 其它 ????????????? Q???? digit???????? 季度????????????????? 显示值:4 ????????????? WW??? digit???????? 当年第几周??????????? 显示值:44 ????????????? W??? digit????????? 当月第几周??????????? 显示值:1 ????????????? ??????? 2