Oracel:常用函数：单行函数、多行函数（组函数）

一、单行函数

单行函数是指每一行数据执行操作后都会返回一行数据

单行函数可以进行嵌套，嵌套函数的顺序是由内到外

单行函数分为5类：字符、数值、日期、转换、通用函数

1）大小写控制函数

lower('str'):大写转小写

select lower('ORACLE') from dual;--oracle

upper('str')：小写转大写

select upper('oracle') from dual;--ORACLE

initcap('str'):字符串首字母大写，其他全部小写

select initcap('oraCLE') from dual;--Oracle

字符控制

concat('str1,'str2')：字符串连接

select concat('oracle','study') from dual;--oraclestudy

ubstr('str',start,n）：对字符str从位置start开始，往后截取n个字符 （字符串str字母下标从1开始）

select substr('oracle',2,4) from dual;--racl

length('str')：获取字符串长度

select length('oracle') from dual;--6

instr('str','value')：查找该字母在字符串首次出现的位置（字符串str字母下标从1开始）
instr('str','value',start,n)：指定从start位置开始查找字符value出现第n次的位置（字符串str字母下标从1开始）
select instr('hellooracle','o') from dual;--5
select instr('hellooracle','o',3,2) from dual;--6
 

lpad('str',num,'value')：左填充,当字符str的长度小于num，则以'value'来填充字符左边缺失的位置（字符串str字母下标从1开始）
select lpad('oracle',8,'$') from dual;--$$oracle
rpad('str',num,'value')：右填充,当字符str的长度小于num，则以'value'来填充字符右边缺失的位置（字符串str字母下标从1开始）
select rpad('oracle',8,'$') from dual;--oracle$$

 

trim([leading/trailing/both] 'value' from 'str')：剔除字符串左/右/两边字符value（/空格）

trim('str'):不指明剔除方式，只能剔除字符串两边的空格

 参数value只能是一个字符

 leading:从字符串左边开始剔除字符value

trailing:从字符串右边开始剔除字符value

both:从字符串两边开始同时剔除字符value

select trim(leading 'h' from 'hhoraclehh') from dual;--oraclehh

select trim(trailing 'h' from 'hhoraclehh') from dual;--hhoracle

select trim(both 'h' from 'hhoraclehh') from dual;--oracle

select trim('   hhoraclehh') from dual;--hhoraclehh

replace('str','value1','value2')：将字符穿中所有字符value1均替换为value2

select replace('hhoraclehh','hh','hi') from dual;--hioraclehi

（3）ASCII码函数

ascii(value)：返回一个字符的ASCII码，参数str只能是一个字符

select ascii('d') from dual;--100

chr(num)：返回给出ASCII码值所对应的字符，参数num表示一个ASCII码值

select chr(100) from dual;--d

2、数值函数
round(num1,num2)：四舍五入（保留num2位小数,不给定num2时默认不保留小数位）
select round(123.123,2) from dual;--123.12
select round(123.523) from dual;--124

trunc(num1,num2)：小数截断（保留num2位小数，不进行四舍五入）

select trunc(123.126123,2) from dual;--123.12

mod(num1,,num2)： 求余

select mod(13,6) from dual;--1

abs(num):返回num的绝对值

select abs(-100) from dual;--100

ceil(num)：返回大于或等于num的最小整数

select ceil(7.8),ceil(8) from dual;--8    8

floor(num)：返回小于或等于num的最大整数

select floor(7.8),floor(8) from dual;--7    8

power(num1,num2)：返回num1的num2次方

select power(2,3) from dual;--8

sign(num)：若num为正数，返回1；若为负数，返回-1；若为0，返回0

select sign(-2),sign(2),sign(0) from dual;--    -1    1    0

sqrt(num)：返回num的平方根

select sqrt(4) from dual;--2

3、日期函数

日期相关变量含义：
sysdate：当前系统时间，精确到秒
current_date：当前系统日期，精确到秒
systimestamp:：当前系统时间，包含时区信息，精确到微秒
dbtimezone：返回数据库时区
 

select sysdate from dual;--2024/1/17 19:35:53
select current_date from dual;--2024/1/17 19:36:19
select systimestamp from dual;--17-1月 -24 07.37.09.588000 下午 +08:00
select dbtimezone from dual;--+00:00

具体函数：

months_between(date1, date2)：返回date1与date2之间相差几个月（差值计算是用date1-date2）

select months_between(to_date('2024-01-01','yyyy-mm-dd'),to_date('2023-09-01','yyyy-mm-dd')) from dual;--4

add_months(date,num)：返回在当前日期上加num个月

select add_months(to_date('2024-01-01','yyyy-mm-dd'),4) from dual;--2024/5/1

next_day(date,'星期几')：返回在当前日期的基础上，下一个星期几对应日期
select next_day(to_date('2024-01-01','yyyy-mm-dd'),'星期一') from dual;--2024/1/8

last_day(date)：返回本月最后一天

select last_day(to_date('2024-01-01','yyyy-mm-dd')) from dual;--2024/1/31

round(date,'mm')：日期按月进行四舍五入，返回四舍五入后该月第一天

round(date,'yyyy')：日期按年进行四舍五入，返回四舍五入后该年第一个月第一天

select round(to_date('2024-01-17','yyyy-mm-dd'),'mm') from dual;--2024/2/1
select round(to_date('2024-01-17','yyyy-mm-dd'),'yyyy') from dual;--2024/1/1

trunc(date,'yyyy')：返回当年第一天
trunc(date,'mm')：返回当月第一天
trunc(date,['dd'])：返回日期date
trunc(date,'d')：返回当前日期所在星期的第一天（默认周日为一周的第一天）
trunc(sysdate,'hh')：返回当前日期和时间，时间具体到小时
trunc(sysdate,'mi')：返回当前日期和时间，时间具体到分钟

select trunc(to_date('2024-01-17','yyyy-mm-dd'),'yyyy') from dual;--2024/1/1
select trunc(to_date('2024-01-17','yyyy-mm-dd'),'dd') from dual;--2024/1/17
select trunc(to_date('2024-01-17','yyyy-mm-dd'),'d') from dual;--2024/1/14
select trunc(sysdate,'hh') from dual;--2024/1/17 16:00:00
select trunc(sysdate,'mi') from dual;--2024/1/17 16:12:00

 

转换函数

（1）隐形转换

date<—>varchar2<—>number（若字符串中没有特殊的字符，oracle可以自动完成）

/*varchar2和number类型之间转换*/
select '12'+4 from dual;--16
/*date和number类型之间转换*/
select to_date('2024-01-17','yyyy-mm-dd')+2 from dual;--2024/1/19
/*date和varchar2类型之间转换*/
select to_date('2024-01-17','yyyy-mm-dd')+'2' from dual;--2024/1/19

显性转换

（2.1）to_char

作用1：用于将字段转换为字符串

select to_char(999) from dual;--999

作用2：用作日期转换：to_char(date,'日期格式')
常用日期格式：yyyy-mm-dd，yyyy/mm/dd
yyyy"年"mm"月"dd"日" ，mm"月"dd"日"yyyy"年"
YYYY-MM-DD HH24:MI:SS

select to_char(sysdate,'yyyy-mm-dd') from dual;--2024-01-17
select to_char(sysdate,'yyyy/mm/dd') from dual;--2024/01/17
select to_char(sysdate,'yyyy"年"mm"月"dd"日"' ) from dual;--2024年01月17日

作用3：用作数据处理：to_char(num,'格式')
格式：
，：千分位，可以作为分组符号使用，根据需要也可以当百分位、十分位使用，根据两个，
之间间隔的数字个数而定
.：小数点，只能出现在小数点对应的位置，且只能出现一次
$：美元符，可以出现在任意位置
0：零，每一个位置返回对应的字符，若没有则用0填充
9：数字，在小数位表示转换为对应字符，没有则用0表示；在整数位，没有则不填充字符，
为空
L：人民币，可以放在最前面或者最后面

/*，：千/百/十/分位*/
select to_char(123456789,'999,999,999') from dual;-- 123,456,789
select to_char(12345,'99,99,99') from dual;-- 1,23,45
select to_char(12345,'9,9,9,9,9') from dual;-- 1,2,3,4,5
/*.：小数点*/
select to_char(1234,'9999.9') from dual;;-- 1234.0
/*$：美元符*/
select to_char(1234,'9999.$9') from dual;-- $1234.0
/*0：零*/
select to_char(1234,'09999.99') from dual;--01234.00
/*9：数字*/
select to_char(1234,'9999.99') from dual;-- 1234.00
/*L：人民币*/
select to_char(1234,'9999.99L') from dual;-- 1234.00￥

作用4：可以进行进制转换，10进制转换为16进制

数值必须是大于等于0的整数，前面只能是0或者FM组合使用

2.2）to_number
作用1：将varchar类型转换为number类型
select to_number('123456') from dual;--123456
select to_number('123,456.89','999,999.99') from dual;--123456.89

作用2：可用来实现进制转换，16进制转换为10进制

1. select to_number('17f','xxx') from dual;--383
2. select to_number('f','x') from dual;--15

2.3）to_date

可以用作日期转换：to_date('date','格式')
常用格式：yyyy-mm-dd，yyyy/mm/dd
yyyy"年"mm"月"dd"日" ，mm"月"dd"日"yyyy"年"
yyyy-mm-dd hh24:mi:ss，yyyy-mm-dd hh:mi:ss

select to_date('2022-06-10','yyyy-mm-dd') from dual;--2022/6/10
select to_date('2022-06-10 16:23:54','yyyy-mm-dd hh24:mi:ss') from dual;--2022/6/10 16:23:54

5、通用函数

可用于任何数据类型，也适用于空值

（1）空值转换函数

nvl(str1,str2)：将空值转换为一个已知的值，可以使用的数据类型有日期、字符、数字

 

select t.empno,t.ename,t.comm,nvl(t.comm,0) comm_1 from emp t;

 

nvl2(str1,str2,str3)：当str 1不为null，返回str2；为null，则返回str3

select t.empno,t.ename,t.comm,nvl2(t.comm,'非空','空') comm_1 from empt;

 

coalesce(expr1,expr2,...,exprn)：返回所有表达式中第一个非空的表达式，若expr1为空，返回expr2的值，以此类推，若所有表达式均为空返回null

--原表数据
select t.empno,t.ename,t.comm,t.mgr,t.sal from emp t
where t.empno in ('7369','7566','7788','7839');

（2）字符比较函数

nullif(str1,str2)：相等返回null,不等返回str1

select nullif(1,2),nullif(2,2) from dual;

二、多行函数
多行函数是指多行数据执行完操作返回一行数据，也称为分组函数或聚合函数
avg、sum、min、max、stddev、variance都会忽略空值
count（*）不会忽略空值，count(column)会忽略空值

avg(str)：求平均值

select avg(t.comm) from emp t;--550

sum(str)：求和

select t.empno,t.ename,sum(t.comm) from emp t;--2200

min(str)：取最小值

select min(t.comm) from emp t;--0

max(str)：取最大值

select max(t.comm) from emp t;--1400

count(str)：统计数据记录数

select count(t.empno) from emp t;--14

stddev( [ distinct | all ] column )：统计数据标准差，（distinct表示只统计不重复出现的数据， all表示统计满足条件的所有数据，不指定时默认是all）
select stddev(grade),stddev(all grade),stddev(distinct grade)
from student_score
where subject = '数学';--17.6974574445032 17.6974574445032 19.55334583475

variance( [ distinct | all ] column ):统计数据方差（distinct表示只统计不重复出现的数据， all表示统计满足条件的所有数据，不指定时默认是all）
select variance(grade),stddev(all grade),stddev(distinct grade)
from student_score
where subject = '数学';--313.2 17.6974574445032 19.5533458347

group by、order by、having一般会结合组函数一起使用

group by str1,str2……：按字段str1和str2进行分组（str1，str2值均相同的分为一组）
select t.deptno, t.empno, max(t.sal)
from emp t
where t.empno in ('7654', '7566', '7839', '7788', '7782')
group by t.deptno, t.empno;

order by str [desc/asc]：按字段str排序，默认是asc升序
select t.deptno, t.empno, max(t.sal)
from emp t
where t.empno in ('7654', '7566', '7839', '7788', '7782')
group by t.deptno, t.empno
order by t.deptno desc, t.empno desc;

having 条件：对分组后的数据进行筛选
where与having的区别：where是对数据行的筛选，having是对分组后的数据进行筛选
select t.deptno,t.empno,max(t.sal) from emp t
where t.empno in ('7654','7566','7839','7788','7782')
group by t.deptno,t.empno
having t.deptno = '10'
order by t.deptno desc,t.empno desc