oracle学习笔记(3)-SQL函数

SQL函数:
数值函数:
ABS(n)----------------------返回n的绝对值;
CEIL(n)---------------------返回大于或等于n的最大整数;
EXP(n)----------------------返回e的n次幂;
FLOOR(n)-------------------返回小于或等于n的最大整数;
LN(n)-----------------------返回以e为底n的对数;
LOG(m,n)--------------------返回以m为底n的对数;
MOD(m,n)--------------------返回m除以n的余数;
PI()------------------------π的常数值;
POWER(m,n)------------------返回m的n次方;
ROUND(m,n)------------------返回m的四舍五入结果,n表示保留的小数位数;
SIGN(n)---------------------判断n的正负,大于0返回1,=0返回0,<0返回-1;
SORT(n)---------------------返回n的平方根;
SQUARE(n)-------------------返回n的平方;

TRUNC(m,n)-----------------对m进行截断操作;截断到小数点后n位,n默认为0,当n为负数时,小数点左边的数删除为0;

select 
    ABS(-12), --12
    CEIL(12.1),--13
     FLOOR(12.9), --12
    MOD(12,5),--2
     POWER(2,3),--8  
    ROUND(12.3456,3),--12.346
    SIGN(-12), --  -1
    TRUNC(12.34567,2)--12.34
from dual

字符函数:
ASCII(char)-----------------返回字符串首字符的ASC码值;
CHR(n)----------------------返回ASC码值为n的字符;
CONCAT('','')---------------把两个列值拼接起来,"||"更通用;
INITCAP(char)---------------将字符串中每个单词首字母大写;
INSTR(char1,char2,a,b)------返回指定字符串的位置;
SUBSTR(char,m[,n])----------返回char从m到n的子字符串;
LENGTH(char)----------------计算字符串的长度;
REPLACE(str1,str2,str3)-----将str1中的str2替换成str3;
LOWER(char)-----------------返回小写;
UPPER(char)-----------------返回大写;
LPAD(char1,n,char2)---------从左侧用char2将char1填充至长度为n;
RPAD(char1,n,char2)---------从右侧用char2将char1填充至长度为n;
LTRIM(char,SET)-------------把char字符最左侧的若干个字符干掉,以使其首字符不在set中;

RTRIM(char,SET)-------------把char字符最左侧的若干个字符干掉,以使其首字符不在set中;

select 

    ASCII('a'),--97
    CONCAT('aa','bb'),--aabb
    INITCAP('hello world'),--Hello World
    INSTR('12abc67abc','bc',2,2),--9
    INSTR('12abc67abc','bc',6,1),--9
    INSTR('12abc67abc','bc',1,1),--4
    SUBSTR('abcdef',2,4),--bcde
    LENGTH('abcdef'),--6
    REPLACE('abcdef','bc','ww'),--awwdef
    LPAD('12.23',6,'0'),--012.23
    RPAD('12.23',6,'0')--12.230
from dual;

    日期函数:
Oracle数据库日期的默认格式是:DD-MON-YY;可通过参数NLS_DATE_FORMAT设置当前会话的默认日期格式.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ADD_MONTHS(d,n)-----------------------返回日期d加n月后的日期;
CURRENT_DATE--------------------------返回系统当前的日期和时间;
SYSTDATE----------------------------返回系统的当前时间和日期;
CURRENT_TIMESTAMP[(p)]----------------返回系统当前时间戳;
SYSTIMESTAMP--------------------------返回系统当前时间戳;
EXTRACT(depart FROM d)----------------返回d中depart对应的部分;
LAST_DAY(d)---------------------------返回当前日期月份的最后一天;
LOCALTIMESTAMP(p)---------------------返回本地时间戳;
MONTHS_BETWEEN(d1, d2)----------------返回两个时间之间相隔的月数;
NEXT_DAY(d, day)----------------------d后第一周指定的日期;
ROUND(d, format)----------------------日期d按照format格式进行舍入;
TRUNC(d,format)-----------------------返回结尾到format格式的日期;
TO_CHAR(d, format )-------------------将日期转换为指定格式的字符串;

TO_TIMESTAMP(char, format, nlsparam)--将符合特定日期格式的字符串转成TIMESTAMP格式;

select 
    ADD_MONTHS(sysdate,5),--2018/9/23 18:02:35
    CURRENT_DATE,--2018/4/23 18:02:35
    SYSTIMESTAMP,--23-APR-18 06.02.35.984000 PM +08:00
    LAST_DAY(sysdate),--2018/4/30 18:02:35
    NEXT_DAY(sysdate, 5),--2018/4/26 18:02:35
    ROUND(sysdate, 'DD'),--2018/4/24
    TRUNC(sysdate,'MM'),--2018/4/1
    TO_CHAR(sysdate, 'YYYY/MM/DD' ),--2018/04/23
    TO_TIMESTAMP('2018/4/23', 'YYYY/MM/DD')--23-APR-18 12.00.00.000000000 AM
from dual;

转换函数:
CAST(expr AS datatype)----------------将表达式按指定的类型返回;
CHARTOROWID(char)---------------------将字符串转换为ROWID类型;
HEXTORAW(char)------------------------十六进制转二进制;
RAWTOHEX(n)---------------------------二进制转十六进制;
ROWIDTOCHAR(rowid)--------------------将rowid类型转换为字符类型;
TO_CHAR(n,format)---------------------转换为字符串;
TO_DATE(char,format)------------------字符串转成指定格式的日期;
TO_CLOB(char)-------------------------字符转CLOB类型;

TO_NUMBER(char,format)----------------字符转数字;

select 
    TO_CHAR(sysdate,'YYYY/MM/DD'),--2018/04/23
    TO_DATE('2018/4/23','YYYY/MM/DD'),--2018/4/23
    TO_CLOB('aabbcc'),--
    TO_NUMBER('123')--123
from dual;

其他函数:

GREATEST(expr1, expr2,...)------------返回最大的结果;
LEAST(expr1, expr2,...)---------------返回最小的结果;
NULLIF(expr1, expr2)------------------如果expr1, expr2相等,返回null;否则返回expr1;
NVL(expr1, expr2)---------------------如果expr1为null,返回expr2,否则返回expr1;
NVL2(expr1, expr2,expr3)--------------如果expr1为null,返回expr3.否则返回expr2;
UID-----------------------------------返回当前回话的用户ID;

USER----------------------------------返回当前数据库用户名;

select 
    GREATEST(1, 2,3),--3
    LEAST(1, 2,3),--1
    NVL('aaa', 'bbb'),--aaa
    NVL('', 'bbb'),--bbb
    NVL2('aaa', 'bbb','ccc'),--bbb
    NVL2(null, 'bbb','ccc')--ccc
from dual;