SQL3

第三章

单行函数：输入一行，返回一行

多行函数：输入多行，返回一行

INITCAP：将字首字符大写，其它字符小写

>SELECT INITCAP('MY WORD') FROM DUAL;

SUBST:

>SELECT SUBSTR('MY WORD',2,4) FROM DUAL;

ROUND:四舍五入

TRUNC:截断数字

MOD:取模

SELECT ROUND(45.925,2) FROM DUAL;

SELECT ROUND(45.925) FROM DUAL;

SELECT ROUND(45.925,-1) FROM DUAL;

SELECT TRUNC(45.925,-1) FROM DUAL;

SYSDATE:返回当前系统日期

SELECT SYSDATE FROM DUAL;

DUAL:专门用于返回函数的值

SELECT MONTHS_BETWEEN('31-JUL-02','31-AUG-02') FROM DUAL; -1 NEXT_DAY:返回当前日期的下一个工作日

>SELECT NEXT_DAY('01-JUL-02','FRIDAY') FROM DUAL;

LAST_DAY:本月的最后一天

ROUND:以月中为界的四舍五入

>SELECT ROUND(01-JUL-02) FROM DUAL;

>SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

>SELECT ENAME,TO_CHAR(SAL,$99,999.99) FROM EMP;

TO_CHAR:将日期和数字转变成字符(指定日期格式)

TO_NUMBER:

TO_DATE:将字符转变为日期

INSERT INTO D VALUES('2002-12-90');错

INSERT INTO D VALUES('12-AUG-02');

INSERT INTO D VALUES(TO_DATE('2002-12-9','YYYY-MM-DD'));

SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE,'YYYY"月份:"MM') FROM DUAL;

RR(默认):

当前日期:0-49

插入数据:(0-49)-----当前世纪

(50-99)-----上一世纪

当前日期:50-99

插入数据:(0-49)-----下一世纪

(50-99)-----当前世纪

INSERT INTO D VALUES('12-AUG-34'); 2034-08-12

SELECT TO_CHAR(COLA,'YYYY') FROM DUAL;

嵌套函数

SELECT SUBSTR(UPPER('aaaaaabbbbbaaaba'),5,4) FROM DUAL;