Oracle下SQL基本操作(三)(5)

SELECT TRUNC(5.687,2) FROM DUAL;
--POWER
SELECT POWER(2,3)  FROM DUAL;
--ABS
SELECT ABS(-12.69) FROM DUAL;
--MOD
SELECT MOD(12,7)   FROM DUAL;
--FLOOR
SELECT FLOOR(15.4 )  FROM DUAL;
SELECT FLOOR(-15.4) FROM DUAL;
---CEIL
SELECT CEIL(15.4)  FROM DUAL;
SELECT CEIL(-15.4)  FROM DUAL;
----SQRT
SELECT SQRT(4)  FROM DUAL;
-----日期函数
SELECT SYSDATE FROM DUAL;
--加两天
SELECT SYSDATE + 2 FROM DUAL;
----加四个小时
SELECT SYSDATE + 2/12 FROM DUAL;
---加了两个小时
SELECT SYSDATE + 2/24 FROM DUAL;
---加了两分钟
SELECT SYSDATE + 2/1440 FROM DUAL;
--日期相减
SELECT TO_DATE('2009-5-9','YYYY-MM-DD') - TO_DATE('2008-5-9','YYYY-MM-DD')   FROM DUAL;
---Add_moths
SELECT ADD_MONTHS(TO_DATE('2009-5-9','YYYY-MM-DD'),2) FROM DUAL;
---MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(TO_DATE('2009-7-19','YYYY-MM-DD'),TO_DATE('2009-6-11','YYYY-MM-DD')) FROM DUAL;
----ROUND 2010-9-11是星期六
SELECT ROUND(TO_DATE('2010-9-11','YYYY-MM-DD'),'DAY')  FROM DUAL; --0912
SELECT ROUND(TO_DATE('2010-9-8','YYYY-MM-DD'),'DAY')  FROM DUAL;---0905
SELECT ROUND(TO_DATE('2010-9-11','YYYY-MM-DD'),'MONTH')    FROM DUAL;--0901
SELECT ROUND(TO_DATE('2010-9-15','YYYY-MM-DD'),'MONTH')    FROM DUAL;--0901
SELECT ROUND(TO_DATE('2010-9-16','YYYY-MM-DD'),'MONTH')    FROM DUAL;--1001
SELECT ROUND(TO_DATE('2010-7-5','YYYY-MM-DD'),'YEAR')    FROM DUAL;--2010
SELECT ROUND(TO_DATE('2010-7-16','YYYY-MM-DD'),'YEAR')    FROM DUAL;--2011
SELECT ROUND(TO_DATE('2010-6-16','YYYY-MM-DD'),'YEAR')    FROM DUAL;--2011
SELECT ROUND(TO_DATE('2010-7-16 11:59:59','YYYY-MM-DD HH24:MI:SS'))    FROM DUAL;--0716
SELECT ROUND(TO_DATE('2010-7-16 12:00:00','YYYY-MM-DD HH24:MI:SS'))    FROM DUAL;--0716
--日期中没写时间,to_round控制不会显示
SELECT ROUND(TO_DATE('2010-7-16','YYYY-MM-DD HH24:MI:SS'))    FROM DUAL;
---日期中没写时间,to_char控制会显示
SELECT TO_CHAR(ROUND(TO_DATE('2010-7-16','YYYY-MM-DD HH24:MI:SS')),
'MM-DD-YYYY HH24:MI:SS')   FROM DUAL;
----NEXT_DATE
SELECT NEXT_DAY(TO_DATE('2010-9-11','YYYY-MM-DD'),'星期日')  FROM DUAL;
SELECT NEXT_DAY(TO_DATE('2010-9-11','YYYY-MM-DD'),'SUNDAY')  FROM DUAL;
-----修改语言环境
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';