目录
函数介绍
Oracle函数:可以对数据进行加工处理,返回我们想要的结果。
作用:丰富了SQL的语言功能,为Oracle SQL 提供了更多的操作性。
主要使用两种类型的函数:
1、单行函数:对每一个函数应用在表的记录中时,只能输入一行中的列值作为输入参数(或常数),并且返回一个结果。
例如1:MOD(X,Y) 是求余函数,返回的X除以Y的余数,其中X和Y可以是列值,也可以是常数。
例如2:TO_CHAR(X,‘YYYYMMDD’)是时间类型转字符串的函数,其中X可以是行中某一时间类型(date)的列,也可以是一个时间类型的常数。
常用单行函数:
- 字符串函数:对字符串进行操作,例如:TO_CHAR()、SUBSTR()、DECODE()等等。
- 数值函数:对数值进行计算或操作,返回一个数字。例如:ABS()、MOD()、ROUND()等等。
- 转换函数:将一种数据类型转换成另外一种类型:例如:TO_CHAR()、TO_NUMBER()、TO_DATE()等等。
- 日期函数:对时间和日期进行操作的函数。例如:TRUNC()、SYSDATE()、ADD_MONTHS()等等。
2、聚合函数: 同时可以对多行数据进行操作,并返回一个结果。如 SUM(x)返回结果集中 x 列的总和。
Oracle字符型函数
用来处理字符串类型的函数,通过接收字符串参数,然后经过操作返回字符串结果的函数。
Oracle日期型函数
Oracle日期类型函数是操作日期、时间类型的相关数据,返回日期时间类型或数字类型结果,常用的函数有:SYSDATE()、ADD_MONTHS()、LAST_DAY()、TRUNC()、ROUND()等等。
系统日期、时间函数:
SYSDATE函数:该函数没有参数,可以得到系统的当前时间。
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now_date from dual;
SYSTIMESTAMP函数:该函数没有参数,可以得到系统的当前时间,该时间包含时区信息,精确到微秒。
案例代码:
select systimestamp from dual;
数据库时区函数:
DBTIMEZONE函数:该函数没有输入参数,返回数据库时区。
**select** **dbtimezone** **from** dual;
给日期加上指定的月份函数:
ADD_MONTHS(r, n)函数:该函数返回在指定日期r上加上一个月份数n后的日期。其中
r:指定的日期。
n:要增加的月份数,如果N为负数,则表示减去的月份数。
select to_char(add_months(to_date('2018-11-12','yyyy-mm-dd'),1),'yyyy-mm-dd'),
to_char(add_months(to_date('2018-10-31','yyyy-mm-dd'),1),'yyyy-mm-dd'),
to_char(add_months(to_date('2018-12-30','yyyy-mm-dd'),1),'yyyy-mm-dd')
from dual;
结果:(如果指定的日期是月份的最后一天,返回的也是新的月份的最后一天,如果新的月份比指定的月份日期少,将会自动调回有效日期)
根据结果可发现,日期2018-10-31加上一个月后,本应为2018-11-31,但11月只有30天,所以结果自动显示为2018-11-30;
日期2018-12-30,加上一个月后,自动变为2019年。
月份最后一天函数:
LAST_DAY®函数:返回指定r日期的当前月份的最后一天日期。
select last_day(sysdate) from dual;
当前日期实际为:2022-01-16 16:02:40,返回当前月份最后一天则为如下:2022-01-31 16:02:40
指定日期后一周的日期函数:
**NEXT_DAY(r,c)函数:**返回指定R日期的后一周的与r日期字符(c:表示星期几)对应的日期。
即返回指定日期后一周 星期几 所对应的日期。
c:为 1 ~ 7 或 Monday/Mon~Sunday。1表示星期日,2代表星期一…
select next_day(to_date('2022-01-16','yyyy-mm-dd'), 4) from dual;
当前日期为1月16号,4 代表星期三,即下周的星期三,则为1月19号。
返回指定日期中特定部分的函数:
**EXTRACT(time)函数:**返回指定time时间当中的年、月、日、分等日期部分。
select extract( year from timestamp '2022-11-12 15:36:01') as year,
extract( month from timestamp '2022-11-12 15:36:01') as month,
extract( day from timestamp '2022-11-12 15:36:01') as day,
extract( minute from timestamp '2022-11-12 15:36:01') as minute,
extract( second from timestamp '2022-11-12 15:36:01') as second
from dual;
返回两个日期间的月份数:
MONTHS_BETWEEN(r1,r2)函数: 该函数返回r1日期和r2日期之间的月份。当r1>r2时,返回的是正数,假如r1和r2是不同月的同一天,则返回的是整数,否则返回的小数。当r1<r2时,返回的是负数。
select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
to_date('2017-11-12', 'yyyy-mm-dd')) as zs, --整数
months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
to_date('2017-10-11', 'yyyy-mm-dd')) as xs, --小数
months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
to_date('2018-10-12', 'yyyy-mm-dd')) as fs --负数
from dual;
日期截取函数:
ROUND(r, f)函数: 将日期 r 按 f 的格式进行四舍五入。如果 f 不填,则四舍五入到最近的一天。
select sysdate, --当前时间
round(sysdate, 'yyyy') as year, --按年
round(sysdate, 'mm') as month, --按月
round(sysdate, 'dd') as day, --按天
round(sysdate) as mr_day, --默认不填按天
round(sysdate, 'hh24') as hour --按小时
from dual;
TRUNC(r, f)函数: 将日期 r 按 f 的格式进行截取。如果 f 不填,则截取到当前的日期。
select sysdate, --当前时间
trunc(sysdate, 'yyyy') as year, --按年
trunc(sysdate, 'mm') as month, --按月
trunc(sysdate, 'dd') as day, --按天
trunc(sysdate) as mr_day, --默认不填按天
trunc(sysdate, 'hh24') as hour --按小时
from dual;
Oracle 数值型函数
Oracle数值型函数可以是输入一个数值,并返回一个数值的函数,我们经常用到函数如下表:
Oracle 转换函数
Oracle转换函数是进行不同数据类型转换的函数,是我们平常数据库开发过程当中用的最多的内置函数。常用的函数有to_char()、to_number()、to_date()等等。详细分析如下表:
提醒:其中数值 f 的格式可以参考下表:
Oracle聚合函数
Oracle聚合函数同时可以对多行数据进行操作,并返回一个结果。比如经常用来计算一些分组数据的总和和平均值之类,常用函数有AVG()、SUM()、MIN()、MAX()等等。
AVG函数
AVG([distinct ] expr):该函数可以求列或列组成的表达式expr的平均值,返回的是数值类型。其中 distinct是可选参数,表示是否去掉重复行。
案例1、求学生信息表(stuinfo)中学生的平均年龄,代码如下:
select * from stuinfo;
select avg(t.age) from stuinfo t;
使用该函数和其他聚合函数时,都可以和where条件语句和分组GROUP BY 查询组合使用,得到特定的结果。
案例2,、按照班级求各班学生的平均年龄(其中年龄大于等于30岁的不计入在内)。代码:
select classno, avg(t.age)
from stuinfo t
where t.age < 30
group by t.classno;
#### COUNT函数 count(*|[distinct]expr)函数可以用来计算查询结果的**条数或行数**。函数中必须指定列名或者表达式expr,不然就要全选使用*号。
案例3、查询学生信息表中所有的学生的个数。代码如下:
select count(*) from stuinfo;
MAX/MIN函数
MAX([distinct] expr)、MIN([distinct] expr)函数可以返回指定列或列组成的表达式expr中的最大值或最小值。该函数也通常和where条件、group by分组结合在一起使用。
案例4、求学生信息表中年龄最大的学生的年龄:
select max(age) from stuinfo;
SUM函数
SUM([distinct] expr)函数可以对指定列或列组成的表达式expr进行求和,假如不使用分组group by ,那就是按照整表作为一个分组。
案例5、正好利用sum函数求和乘以人数进行求学生的平均年龄
select classno, sum(age), count(*), sum(age) / count(*), avg(age)
from stuinfo t
where t.age < 30
group by t.classno;