SQL 常见函数总结（oracle版）

@[TOC]SQL 常见函数总结（oracle版）

---

前言

SQL 函数总结（oracle版）
总结一些oracle 常见的函数，本文以emp表为例。

---

一、字符函数：

1.字符串连接

concat()
连接字符串,等同于||
例如：
select concat('my name is ',ename) from emp

||
||也可实现
例如：
select 'my name is '||ename from emp

2.转换字母函数

initcap()
将字符串的首字母大写
例如：
select initcap(ename) from emp ;

upper()
将字符串全部转换为大写
例如：
select upper(ename) from emp ;

lower()
将字符串全部转为小写
例如：
select lower(ename) from emp

3.填充字符串函数

lpad()
左填充，
字段，位数，填充符号
例如：
select lpad(ename,10,’*’) from emp

rpad()
右填充
字段，位数，填充符号
例如：
select rpad(ename,10,’*’) from emp

4.去除空格函数

trim()
去除字符串左右两别空格
例如：
select trim(name),name from student t

注意：去除左右两边空格

ltrim()
去除字符串里左边空格
例如：
select trim(name),name from student t where t.id=2

rtrim()
去除字符串里右边空格
例如：
select trim(name),name from student t where t.id=3

5.查找字符串位置，长度

instr()
字段，查找字符
例如：
select instr(‘ABSDSV’,‘A’)from dual

length()
查找字段长度
例如：
select length(ename),ename from emp

6.字符串截取，替换

substr()
截取字符串
字段，开始位数，结束位数
例如：
select substr(ename,0,2),ename from emp

replace()
替换操作
字段，原字段被替换字符，替换字符
例如：
select replace(ename,‘A’,’*’),ename from emp

二、数值函数

1.四舍五入函数

round()
给小数进行四舍五入操作，指定小数部分位数
字段值，位数
例如：
select
round(123.123,2)NUM1,
round(123.128,2)NUM2,
round(-123.128,2)NUM3 from dual

trunc()
截断函数，按照位数去截断，不会四舍五入
字段值，截断位数
例如：
select trunc(123.128,2) from dual

2.取模函数

mod()
正负数取模
例如：
select
mod(10,4)NUM1,
mod(-10,4)NUM2 from dual

3.向上，向下取整函数

ceil()
向上取整
例如：
select ceil(12.12) from dual

floor()
向下取整
例如：
select floor(12.55) from dual

3.取绝对值函数

abs()
取绝对值
例如：
select abs(-100),abs(100) from dual

3.获取正负值函数

sign()
数字，若为正值返回1，负值返回-1，0返回0
例如：
select sign(-150),sign(100),sign(0) from dual

4.计算幂次函数

power()
x的y次幂
例如：
select
power(5,2) NUM1,
power(1.5,0)NUM2,
power(20,-1)NUM3 from dual;

三、日期函数

1.获取当前日期函数

sysdate
获取当前 年月日时分秒
例如：
select sysdate from dual

current_date
例如：
select current_date from dual

2.添加指定月份函数

add_months()
日期，增加月份
例如：
select sysdate,add_months(sysdate,2) from dual

3.获取输入日期月份最后一天函数

last_day()
例如：
select sysdate,last_day(sysdate) from dual

4.获取两个日期间隔月份函数

months_between()
日期1，日期2
例如：
select months_between(sysdate,sysdate-365) from dual

4.获取日期最近一天函数

round()
当前日期
（日期）最近零点日期
（日期，‘day’）最近星期日
（日期，‘month’）最近月初
（日期，‘q’）最近季初日期
（日期，‘year’）最近年初日期
例如：
select
sysdate date1,
round(sysdate)date2,
round(sysdate,‘day’)date3,
round(sysdate,‘month’)date4,
round(sysdate,‘q’)date5,
round(sysdate,‘year’)date6 from dual

5.返回下周的星期几函数

next_day()
日期，“星期几”
例如：
select
sysdate date1,
next_day(sysdate,‘星期一’)date2,
next_day(sysdate,‘星期五’)date3　from dual

6.提取时间年、月、日、时、分、秒值函数

extract()
（second from timestamp ‘日期字符串’） 秒
（minute from timestamp ‘日期字符串’） 分钟
（hour from timestamp ‘日期字符串’）小时
（DAY from timestamp ‘日期字符串’）日
（MONTH from timestamp ‘日期字符串’）月
（YEAR from timestamp ‘日期字符串’）年
例如：
select
extract(second from timestamp '2001-2-16 2:38:40 ’ ) 秒,
extract(minute from timestamp '2001-2-16 2:38:40 ’ ) 分钟,
extract(hour from timestamp '2001-2-16 2:38:40 ’ ) 小时,
extract(DAY from timestamp '2001-2-16 2:38:40 ’ ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ’ ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ’ ) 年
from dual

7.返回日期时间戳

localtimestamp
例如：
select localtimestamp from dual

current_timestamp
例如：
select current_timestamp from dual

8.给指定的时间单位增加数值

interval
–加1秒(1/24/60/60)
–加1分钟(1/24/60)
–加1小时(1/24)
–加1天(1)
–加1月
–加1年
–加指定小时到秒
–加指定分钟到秒
–加指定小时到分钟
–加指定天数到分钟
例如：
select
trunc(sysdate)+(interval ‘1’ second)time1,
trunc(sysdate)+(interval ‘1’ minute)time2,
trunc(sysdate)+(interval ‘1’ hour)time3,
trunc(sysdate)+(INTERVAL ‘1’ DAY)time4,
trunc(sysdate)+(INTERVAL ‘1’ MONTH)time5,
trunc(sysdate)+(INTERVAL ‘1’ YEAR)time6,
trunc(sysdate)+(interval ‘01:02:03’ hour to second)time7,
trunc(sysdate)+(interval ‘01:02’ minute to second)time8,
trunc(sysdate)+(interval ‘01:02’ hour to minute)time9,
trunc(sysdate)+(interval ‘2 01:02’ day to minute)time10 from dual

四、转换函数

1.转换字符串函数

to_char()
日期转字符串
例如：
select to_char(sysdate,‘yyyy-MM-dd hh24:mi:ss’) from dual

数字转字符串
数字，转化格式
–9.在小数位,则表示转换为对应字符,如果没有则以0表示;在整数位,没有对应则不填充字符.
–注意事项:对于0和9而言,如果格式的位数不如数字的位数多,会返回’#’.
譬如to_char(12345,‘9999’)=’#####’

例如：
select
123.456789 NUM0,
to_char(123.456789,‘99999’)NUM1,
to_char(123.456789,‘0000.00’)NUM2,
to_char(123.456789,’$0000.00’)NUM3,
to_char(123.456789,‘L0000.00’) NUM4,
to_char(123456789,‘999,999,999,999’)NUM5 from dual

2.转换日期函数

to_date()
字符串转日期
日期字符串，转化格式
例如：
select
to_date(‘199912’,‘yyyymm’)date1,
to_date(‘2000.05.20’,‘yyyy.mm.dd’)date2,
to_date(‘2008-12-31 12:31:30’,‘yyyy-mm-dd hh24:mi:ss’)date3 from dual

3.转换数字函数

to_number()
字符串转数字
数字字符串，转换格式
例如：
select
to_number(‘123456789’)NUM1,
to_number(‘1234,5678,9’,‘9999,9999,9’)NUM2 from dual

五、条件函数

同一个例子展现两个函数写法：
问题：给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30%，其他涨薪15%

1.decode函数

decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
例如：
select ename,deptno,sal,decode(deptno,10,sal * 1.1,20,sal * 1.2,30,sal * 1.3,sal * 1.15)sal1 from emp　

2.case when函数

case [<表达式>]
when <表达式条件值1> then <满足条件时返回值1>
[when <表达式条件值2> then <满足条件时返回值2>
……
[else <不满足上述条件时返回值>]]
end
例如：
select ename, deptno, sal,
(case deptno when 10 then sal * 1.1
when 20 then sal * 1.2
when 30 then sal * 1.3
else sal*1.15 end)sal1 from emp

六、组函数

组函数,一般情况下，组函数都要和group by组合使用

1.求平均值函数

avg()
薪资平均值
例如：
select avg(sal) from emp

2.求最小值函数

min()
薪资最小值
例如：
select min(sal) from emp

3.求最大值函数

max()
薪资最大值
例如：
select max(sal) from emp

4.求和函数

sum()
薪资总和
例如：
select sum(sal) from emp

5.求数据总条数函数

count()
表数据总条数
例如：
select count(*) from emp
或select count(sal) from emp