SQL函数分单行函数、多行函数(或叫组函数)。

一、字符函数

lower 转小写

upper 转大写

initcap 首字母大写，其余小写，以单词为单位

initcap('we-are-chinese') We Are Chinese

concat 两个字符串的连接， 多字串时可嵌套调用或直接用“||”

length 计算字符串长度，无论单、双字节字符或数字

length('12345') 5

length('中国人') 3

length(123.45) 6

lpad 格式化字符串，左填充(字串，宽度，填充符)，填充符默认为空格填充，字串宽度已经超出指定宽度时变为左截取，截取到指定宽度

lpad('12345',10, '0') 0000012345

lpad('12345',10) ' 12345'

lpad('12345',4,'0') 1234

lpad('12345',4) 1234

rpad 右填充，字串宽度已经超出指定宽度时变为左截取，截取到指定宽度

rpad('12345',10, '0') 1234500000

rpad('12345',4,'0') 1234

rpad('12345',4) 1234

该函数可以隐士转换，如果不是字符串

select lpad(sal,10,'*') from emp where empno=7934;

ltrim 删除左侧空格

rtrim 删除右侧空格

trim 删除左右两侧空格

trim('*' from '***hello***') 删除头尾字符，或trim(both '*' from '***hello***') hello

trim (leading '*' from '***hello***') 删除头部字符 hello***

trim(trailing '*' from '***hello***') 删除尾部字符 ***hello

trim中只能指定单个要删除的字符，不能是字符串

instr 获取字符串出现的位置，从指定位置开始，出现的第几次，最后两个参数默认为1，找不到时返回0

instr('HelloWorld','W') 6

instr('HelloWorld','W',1) 6

instr('HelloWorld','W',1,2) 0

substr 截取字符串，起始位置为1，为负时从反向位置向右截取

substr('HelloWorld', 6) World

substr('HelloWorld',1,5) Hello

substr('HelloWorld',-5,5) World

replace 字符串替换，没有替换串时用空字符替换，也就是删除查找串，未找到被替换串时原样返回

replace('jack and jue','j','bl') black and blue

replace('a#b#c','#') abc

replace函数也可以处理数字和日期数据，如

select replace(10000-3,'9','85') from dual; 8585857

select replace(sysdate, 'NOV','DEC') from dual; 25-DEC-16

translate 字符串翻译

translate('+-*/','1+2-3*4/','fajbocld') abcd

常用于去掉字串中的字母或数字

select translate('12fge3k4mab5z','#abcdefghijklmnopqrstuvwxyz',' ') from dual 12345

translate('12ab3c4de5f','#1234567890',' ') abcdef

其中单引号中为空格，不能为空，否则有NULL值做参数的表达式将直接返回NULL，

#号用于剔除跟第一个字符a或1符合的字符的翻译，否则翻译结果会多个空格。

reverse 反转字符串，一般只用于反转单字节字符，多字节字符是乱码

reverse('1234') 4321

wm_concat 字段合并

create table t(a number, b varchar2(20));

insert into t values(1, 'a');

insert into t values(2, 'c');

insert into t values(3, 'b');

insert into t values(2, 'a');

insert into t values(1, 'd');

insert into t values(3, 'e');

col merge for a20

select a, wm_concat(b) merge from t group by a;

A MERGE

---------- --------------------

1 a,d

2 c,a

3 b,e

select a, replace(wm_concat(b), ',', '|') merge from t group by a;

A MERGE

---------- --------------------

1 a|d

2 c|a

3 b|e

regexp_substr 字段拆分

select regexp_substr('a;b;c;d', '[^;]+', 1, level), level from dual connect by level < 5;

REGEXP_SUBSTR( LEVEL

-------------- ----------

a 1

b 2

c 3

d 4

二、数值函数

round 四舍五入

round(1601.916,1) 1601.9

round(1601.916,2) 1601.92

round(1601.916,-3) 2000

round(1601.916) 1602

trunc 截断，不四舍五入，小数部分按指定位数截断，整数部分的截断不改变整数位数

trunc(1601.916,1) 1601.9

trunc(1601.916,2) 1601.91

trunc(1601.916,-3) 1000

trunc(160,-1) 160

trunc(1601.916) 1601

mod 模运算，即取余数，mod(被除数，除数)，常用于区分奇数和偶数，除数可以为0

mod(5,2) 1

mod(5,0) 5

mod(3,5) 3

mod(5.2,2) 1.2

ceil 向上取整，取大值整数

ceil(23.45) 24

ceil(-23.45) -23

floor 向下取整，取小值整数

floor(23.45) 23

floor(-23.45) -24

sign 取正负性

sign(-10) -1

sign(10) 1

sign(0) 0

abs 取绝对值

sqrt 求平方根，参数不能为负

power 乘方运算，power(底数，指数)

greatest 求多个值中的最大值

least 求多个值中的最小值

三、日期函数

YY格式

与系统日期处于同一个世纪

RR格式

接近系统日期的世纪

默认是RR格式

DD-MM-RR

nls参数的设置：国家语言支持

alter session set nls_language=english;

修改会话日期时间格式显示：

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

alter session set nls_date_format='yyyy-mm-dd hh:mi:ss am';

sysdate 系统时间

sessiontimezone 当前会话时区

current_date 当前会话时区的当前时间

systimestamp 系统时间戳

current_timestamp 当前会话时区的当前时间戳

日期的加减计算

一个日期加一个数字表示增加天数后的新日期

两个日期相减表示相差的天数

有两个日期数据START_DATE，END_DATE，欲得到这两个日期的时间差(以天，小时，分钟，秒，毫秒)：?

天：?

ROUND(TO_NUMBER(END_DATE - START_DATE))?

小时：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)?

分钟：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)?

秒：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)?

毫秒：?

ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)

months_between 两个日期相差的月份数，离现在近的日期写在前面

add_months 加几个月后的日期，数字为负时反向推算

当日期为最后一天时，返回下一个月的最后一天，超出月份的最后一天时也返回最后一天

select add_months(to_date('2013-11-30','yyyy-mm-dd'), 1) from dual; 返回2013-12-31 00:00:00

select add_months(to_date('2013-1-31','yyyy-mm-dd'), 1) from dual; 返回2013-02-28 00:00:00

select add_months(to_date('2013-1-30','yyyy-mm-dd'), 1) from dual; 返回2013-02-28 00:00:00

函数中日期会隐式转换

select add_months('2013-1-30', 1) from dual; 返回2013-02-28 00:00:00

next_day 下个星期的那个日期，从星期日开始为1

select next_day(sysdate,2) from dual; 返回下个星期一的日期

last_day 指定日期所在月的最后一天

select last_day(to_date('2012-2-11','yyyy-mm-dd')) from dual; 返回2012-02-29 00:00:00

函数中日期会隐式转换

select last_day('2012-2-11') from dual; 返回2012-02-29 00:00:00

返回下个月的第一个星期一

select next_day(last_day(sysdate),'mon') from dual;

日期的四舍五入和截断

round 按年yy、月mm、日dd、时hh、分mi

select round(to_date('2013-11-22','yyyy-mm-dd'),'mm') from dual; 舍入到月 2013-12-01 00:00:00

select round(to_date('2013-11-12','yyyy-mm-dd'),'mm') from dual; 舍入到月 2013-11-01 00:00:00

select round(to_date('2013-11-12 21:29:35','yyyy-mm-dd hh24:mi:ss'),'dd') from dual; 舍入到日 2013-11-13 00:00:00

trunc 按年、月、日截断

select trunc(to_date('2013-11-12 21:29:35','yyyy-mm-dd hh24:mi:ss'),'dd') from dual; 截断到日 2013-11-12 00:00:00

extract 提取日期中的某个域，extract(域名 from 日期)

extract(month from sysdate) 11

只能提取年、月、日，不能从日期型中提取小时、分钟、秒

可提取到零时区的时间域extract(hour from systimestamp)，实际结果还要加上时区sessiontimezone，北京时间为东八区，即要+8

四、转换函数和格式串

chr 将ASCII码转换为字符，与ascii互为反函数

to_char 将数字格式化为字符串，to_char(原数值，格式串，NLS参数)

0 有数字填充数字，没有强制填充0

9 整数部分有数字填充数字，没有不填充，小数部分没有填充0

. 小数点的位置

D 小数分隔符的位置，默认为句点(D和G配合使用，不能D和,或者.和G混合使用)

, 逗号千分符的位置

G 组分隔符的位置，默认为逗号

FM 屏蔽掉没有必要的空格和0

$ 显示美元符号

L 显示系统环境的本地货币符号，如人民币符号“￥”

C 显示系统环境的本地货币种类符号，如中国元“CNY”，“C”不能与“L”或“$”并用

X 转换为十六进制显示，用大写字母，x表示用小写字母，如to_char(255,'XXX')返回FF

MI 表示负数的减号的位置

PR 包围在括号内的负数

EEEE 科学计数法

V 乘以10的n次方，n是V之后9的数量

S 前面加上+ 或-

to_number 将字符串格式化为数字，to_number(字符串，格式串，NLS参数)

掩码格式串类似to_char函数

to_number('$1,000.55', '$999,999.00') 1000.55

进制转换

to_number('ff', 'xxx') 255

to_char 将日期格式格式化为字符串，to_char(日期，格式串，NLS参数)

YEAR、Year、year 区分大小写的年份全拼

yyyy 四位年

yy 两位年

RR 两位已知世纪年

q 季度

mm 两位月份

MONTH、Month、month 区分大小写的月份全拼

MON、Mon、mon 区分大小写的三字母月份缩写

ww 一年中的第几周

w 一月中的第几周

d 一周中的第几天，周日为一周中的第一天

dd 一月中的第几天

ddd 一年中的第几天

DAY、Day、day 区分大小写的星期全拼

DY、Dy、dy 区分大小写的三字母星期缩写

th 后面带上th表示第几天

sp 第几天的英文全拼

AM、PM、A.M.、P.M. 指示上下午

hh、hh12 12时制小时

hh24 24时制小时

mi 分钟

ss 秒

sssss 午夜之后的秒

ff 毫秒

以下是两个日期表达的例子

当天日期的全拼表示

select to_char(sysdate, 'fmDdthsp "of" Month, Year') from dual;

TO_CHAR(SYSDATE,'FMDDTHSP"OF"MONTH,YEAR')

--------------------------------------------------------------------

Seventh of 9月, Twenty Seventeen

下个月的第一个星期一

select to_char(next_day(last_day(sysdate), 2), 'dd "is the first Monday for" fmmonth rrrr') from dual;

TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),2),

--------------------------------------

02 is the first Monday for 10月 2017

显示NLS会话参数的视图：nls_session_parameters

show parameter nls_territory;

修改地区

alter session set nls_territory=america;

修改日期语言

alter session set nls_date_language=american;

alter session set nls_date_language='simplified chinese';

语句级别的NLS控制

select to_char(sysdate, 'mm-dd-yyyy hh:mi:ss am', 'nls_date_language = american') from dual;

select to_char(sysdate, 'mm-dd-yyyy hh:mi:ss am', 'nls_date_language = japanese') from dual;

select to_char(sysdate, 'mm-dd-yyyy hh:mi:ss am', 'nls_date_language = ''simplified chinese''') from dual;

注意第三行语句中需要追加两个单引号

to_date 将字符串格式化为日期，to_date(字符串，格式串，NLS参数)

掩码格式串类似to_char函数

fx 类似于to_char函数使用的fm修饰符，指定该修饰符时，不完全匹配格式掩码的字符项会产生错误。

应避免数据类型的隐式转换，出现隐式转换会不使用索引

强制数据类型转换

cast(原数据 as 新的数据类型)

利用原表重建一个新表，并更改列的数据类型

create table salary2 as

select cast(s.salary_id as varchar2(20)) salary_id,

cast(employee_id as varchar2(20)) employee_id,

cast(salary as varchar2(20)) salary

from salary s;

五、条件函数

nvl(original, ifnull)，判断原值original是否为空，为空返回ifnull的值，不为空原样返回

ifnull数据类型应与original一致，否则要求能向original转换，函数返回类型与original一致

如

select nvl(1, 'a') from dual; 错误

select nvl('a', 1) from dual; 正确

另外注意该函数的合理使用，如下查询不能返回'00000'

select nvl(dummy, '00000') from dual where 1=2;

改为如下形式则能返回'00000'

select nvl(max(dummy), '00000') from dual where 1=2;

nvl2(original, ifnotnull, ifnull) 不为空返回ifnotnull的值，为空返回ifnull的值

ifnull数据类型应与ifnotnull一致，否则要向ifnotnull转换，不能是long类型，函数返回类型与ifnotnull一致

nvl和nvl2的返回位置概念是反的

nullif(expr1, expr2) 相等返回空，否则返回第一个表达式值

coalesce(expr1, expr2, ...) 返回第一个非空的表达式，否则返回空值

所有非空参数的数据类型必须与第一个非空参数的数据类型一致

decode函数

decode(expr, 值1, 结果1,

值2, 结果2,

...

否则结果)

decode函数认为两个空值是相等的

后面各个值的数据类型应当与值1的数据类型一致，否则要求能向值1转换

函数返回的数据类型转换到与结果1一致

如

select decode(sysdate, null, 1, 'a') from dual; 错误

select decode(sysdate, null, 'a', 1) from dual; 正确

函数也可以只包含三个参数，当不满足值1时返回空的结果

如以下查询如果日期不符合将返回空

select decode(to_char(sysdate, 'yyyy-mm-dd'), '2017-08-15', 'b') from dual;

case语句

case expr when 条件1 then 结果1

when 条件2 then 结果2

else 否则结果

end

参数的数据类型必须一致

六、分组函数(聚合函数)

avg 分组求平均值

sum 分组求和

min 分组最小值

max 分组最大值

count 分组计数

count(*)、count(1) 未指定列，返回记录数量包含重复项和空值

count(列名) 指定了列，返回记录数量包含重复项，但排除空值

count(distinct 列名) 返回记录数量不包含重复项和空值

组函数的计算忽略空值

group by 分组显示

查询中包含正常列和分组函数的列时，需要对正常列使用group by子句，否则报错。

group by的表达式内容必须与select中的表达式保持一致，如下表达不一致是错误的

select to_char(hiredate, 'rr'), avg(sal) from emp group by to_char(hiredate, 'yyyy');

并且group by的表达式不可以用列的别名，如下是错误的

select to_char(hiredate, 'rr') year, avg(sal) from emp group by year;

嵌套分组函数需要group by子句，如以下是错误的

select max(count(*)) from emp;

但加入group by后可以执行，虽然这里的嵌套分组并没有意义

select max(count(*)) from emp group by 1;

group by支持多分组，以下是两种多分组模式rollup和cube

rollup(a, b, c)的分组模式为(a,b,c)，(a,b)，(a)，(全表)

select department_id, job_id, sum(salary)

from employees

group by rollup(department_id, job_id);

cube(a, b, c)的分组模式为(a,b,c)，(a,b)，(a,c)，(b,c)，(a)，(b)，(c)，(全表)

select department_id, job_id, sum(salary)

from employees

group by cube(department_id, job_id);

having 分组后的筛选

筛选条件不一定需要存在于select的列表达式中，如以下查询是可以的

select deptno from emp where comm is null group by deptno having count(*)>2;

组函数不能用于where子句中，如果要用，必需放在having子句中。但组函数可以在where子句的子查询中构建。

七、SQL函数的几个练习

以下将日期“19-Mar-2001”表示为“Nineteenth of March 2001 12:00:00 AM”的形式

alter session set nls_date_language=american;

alter session set nls_date_format='DD-Mon-YYYY';

select to_char(to_date('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') new_date from dual;

或

select to_char(to_date('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdthsp "of" Month YYYY fmHH:MI:SS AM') new_date from dual;

以下将日期“11-Mar-2007”表示为“Eleventh of October, Two Thousand Seven”的形式：

select to_char(to_date('11-oct-2007', 'DD-Mon-YYYY'), 'fmDdthsp "of" Month, Year') new_date from dual;

此处如果没有sp：

select to_char(to_date('11-oct-2007', 'DD-Mon-YYYY'), 'fmDdth "of" Month, Year') new_date from dual;

则查询结果为：

11th of October, Two Thousand Seven

查询hr用户的employees表

显示干了多少年零多少个月

LAST_NAME YEARS MONTHS 按年和月降序

select last_name,

trunc(months_between(sysdate, hire_date) / 12) years,

trunc(mod(months_between(sysdate, hire_date), 12)) month

from employees

order by years desc, months desc;

查询scott用户下的emp表

显示总人数和入职日期为以下各年的人数

total 1980 1981 1982 1987

select count(*) total,

sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",

sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",

sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",

sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"

from emp;

将一列的查询分开在两列里显示

select a.id, b.id from t1 a, t1 b where a.id = b.id - 5;

select a.id, b.id from t1 a, t1 b where a.id + (select count(*) from t1) / 2 = b.id;

select a.id, b.id

from t1 a, t1 b

where a.id = b.id(+) - round((select count(*) from t1) / 2)

and a.id <= round((select count(*) from t1) / 2);

查员工工资在其所在部门平均工资之上的员工，列出其所在部门及该部门的平均工资

select a.last_name, a.salary, a.department_id, round(b.salavg, 2)

from employees a,

(select avg(t1.salary) salavg, t2.department_id depid

from employees t1, departments t2

where t1.department_id = t2.department_id

group by t2.department_id) b

where a.department_id = b.depid

and a.salary > b.salavg;