文章目录
数值型函数 ABS(x) ceil(x) floor(x) mod(x,y) round(x[,y]) 字符型函数 CONCAT(c1,c2) INITCAP(c1) LOWER(c1) UPPER(c1) LENGTH(c1) REPLACE(c1,c2[,c3]) 日期函数 sysdate add_months(d1,n1) last_day(d1) months_between(d1,d2) next_day(d1[,c1]) 转换函数 TO_CHAR(x[[,c2],C3]) TO_DATE(X[,c2[,c3]]) TO_NUMBER(X[[,c2],c3]) 聚组函数 AVG([distinct|all]x) SUM([distinct|all]x) count(*|[distinct|all]x) MAX([distinct|all]x) MIN([distinct|all]x)
数值型函数
ABS(x)
【功能】返回x的绝对值
【参数】x,数字型表达式
【返回】数字
【示例】
select abs( 100 ) , abs( - 100 ) from dual;
ceil(x)
【功能】返回大于等于x的最小整数值
【参数】x,数字型表达式
【返回】数字
【示例】
select ceil( 3.1 ) , ceil( 2.8 + 1.3 ) , ceil( 0 ) from dual;
返回4 ,5 ,0
floor(x)
【功能】返回小于等于x的最大整数值
【参数】x,数字型表达式
【返回】数字
【示例】
select floor( 3.1 ) , floor( 2.8 + 1.3 ) , floor( 0 ) from dual;
返回4 ,5 ,0
mod(x,y)
【功能】返回x除以y的余数
【参数】x, y,数字型表达式
【返回】数字
【示例】
select mod ( 23 , 8 ) , mod ( 24 , 8 ) from dual;
返回:7 , 0
round(x[,y])
【功能】返回四舍五入后的值
【参数】x, y,数字型表达式, 如果y不为整数则截取y整数部分,如果y> 0 则四舍五入为y位小数,如果y小于0 则四舍五入到小数点向左第y位。
【返回】数字
【示例】
select round ( 5555.6666 , 2.1 ) , round ( 5555.6666 , - 2.6 ) , round ( 5555.6666 ) from dual;
返回: 5555.67 , 5600 , 5556
【相近】trunc( x[ , y] )
返回截取后的值,用法同round ( x[ , y] ) , 只是不四舍五入
字符型函数
CONCAT(c1,c2)
【功能】连接两个字符串
【参数】c1,c2 字符型表达式
【返回】字符型
同:c1|| c2
【示例】
select concat( '010-' , '88888888' ) || '转23' 高乾竞电话 from dual;
高乾竞电话
010 - 88888888 转23
INITCAP(c1)
【功能】返回字符串并将字符串的第一个字母变为大写,其它字母小写;
【参数】c1字符型表达式
【返回】字符型
【示例】
SQL > select initcap( 'smith abc aBC' ) upp from dual;
UPP
Smith Abc Abc
LOWER(c1)
【功能】:将字符串全部转为小写
【参数】:c1,字符表达式
【返回】:字符型
【示例】
SQL > select lower( 'AaBbCcDd' ) AaBbCcDd from dual;
AABBCCDD
aabbccdd
UPPER(c1)
【功能】将字符串全部转为大写
【参数】c1,字符表达式
【返回】字符型
【示例】
SQL > select upper( 'AaBbCcDd' ) upper from dual;
UPPER
AABBCCDD
LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符( 汉字、全角符等) ,按1 个字符计算
【参数】C1 字符串
【返回】数值型
【示例】
SQL > select length( '高乾竞' ) , length( '北京市海锭区' ) , length( '北京TO_CHAR' ) from dual;
length( '高乾竞' ) length( '北京市海锭区' ) length( '北京TO_CHAR' )
3 6 9
REPLACE(c1,c2[,c3])
【功能】将字符表达式值中,部分相同字符串,替换成新的字符串
【参数】
c1 希望被替换的字符或变量
c2 被替换的字符串
c3 要替换的字符串,默认为空( 即删除之意,不是空格)
【返回】字符型
【示例】
SQL > select replace ( 'he love you' , 'he' , 'i' ) test from dual;
test
i love you
日期函数
sysdate
【功能】:返回当前日期。
【参数】:没有参数,没有括号
【返回】:日期
【示例】select sysdate hz from dual;
返回:2008 - 11 - 5
add_months(d1,n1)
功能】:返回在日期d1基础上再加n1个月后新的日期。
【参数】:d1,日期型,n1数字型
【返回】:日期
【示例】select sysdate, add_months( sysdate, 3 ) hz from dual;
返回:2008 - 11 - 5 , 2009 - 2 - 5
last_day(d1)
【功能】:返回日期d1所在月份最后一天的日期。
【参数】:d1,日期型
【返回】:日期
【示例】select sysdate, last_day( sysdate) hz from dual;
返回:2008 - 11 - 5 ,2008 - 11 - 30
months_between(d1,d2)
【功能】:返回日期d1到日期d2之间的月数。
【参数】:d1,d2 日期型
【返回】:数字
如果d1> d2,则返回正数
如果d1< d2,则返回负数
【示例】
select sysdate,
months_between( sysdate, to_date( '2006-01-01' , 'YYYY-MM-DD' ) ) ,
months_between( sysdate, to_date( '2016-01-01' , 'YYYY-MM-DD' ) ) from dual;
返回:2008 - 11 - 5 , 34.16 , - 85.84
next_day(d1[,c1])
【功能】:返回日期d1在下周,星期几( 参数c1) 的日期
【参数】:d1日期型, c1为字符型( 参数) ,c1默认为j(即当前日期)
【参数表】:c1对应:星期一,星期二,星期三……星期日
【返回】:日期
【示例】
select sysdate 当时日期,
next_day( sysdate, '星期一' ) 下周星期一,
next_day( sysdate, '星期二' ) 下周星期二,
next_day( sysdate, '星期三' ) 下周星期三,
next_day( sysdate, '星期四' ) 下周星期四,
next_day( sysdate, '星期五' ) 下周星期五,
next_day( sysdate, '星期六' ) 下周星期六,
next_day( sysdate, '星期日' ) 下周星期日 from dual;
转换函数
TO_CHAR(x[[,c2],C3])
【功能】将日期或数据转换为char 数据类型
【参数】
x是一个date 或number数据类型。
c2为格式参数
c3为NLS设置参数
如果x为日期nlsparm= NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。
如果x为数字nlsparm= NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符,以及货币符号。
NLS_NUMERIC_CHARACTERS = "dg" , NLS_CURRENCY= "string"
【返回】varchar2字符型
TO_DATE(X[,c2[,c3]])
【功能】将字符串X转化为日期型
【参数】c2, c3, 字符型,参照to_char( )
【返回】字符串
如果x格式为日期型( date ) 格式时,则相同表达:date x
如果x格式为日期时间型( timestamp ) 格式时,则相同表达:timestamp x
【相反】 to_char( date [ , c2[ , c3] ] )
【示例】
select to_date( '199912' , 'yyyymm' ) ,
to_date( '2000.05.20' , 'yyyy.mm.dd' ) ,
( date '2008-12-31' ) XXdate,
to_date( '2008-12-31 12:31:30' , 'yyyy-mm-dd hh24:mi:ss' ) ,
( timestamp '2008-12-31 12:31:30' ) XXtimestamp
from dual;
TO_NUMBER(X[[,c2],c3])
【功能】将字符串X转化为数字型
【参数】c2, c3, 字符型,参照to_char( )
【返回】数字串
【相反】 to_char( date [ [ , c2] , c3] )
【示例】
select TO_NUMBER( '199912' ) , TO_NUMBER( '450.05' ) from dual;
转换为16 进制。
TO_CHAR( 100 , 'XX' ) = 64
聚组函数
AVG([distinct|all]x)
【功能】统计数据表选中行x列的平均值。
【参数】all 表示对所有的值求平均值, distinct 只对不同的值求平均值,默认为all
如果有参数distinct 或all ,需有空格与x( 列) 隔开。
【参数】x,只能为数值型字段
【返回】数字值
【示例】
环境:
create table table3( xm varchar ( 8 ) , sal number( 7 , 2 ) ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 5555.55 ) ;
commit ;
执行统计:
select avg ( distinct sal) , avg ( all sal) , avg ( sal) from table3;
结果: 3333.33 2592.59 2592.59
SUM([distinct|all]x)
【功能】统计数据表选中行x列的合计值。
【参数】all 表示对所有的值求合计值, distinct 只对不同的值求合计值,默认为all
如果有参数distinct 或all ,需有空格与x( 列) 隔开。
【参数】x,只能为数值型字段
【返回】数字值
【示例】
环境:
create table table3( xm varchar ( 8 ) , sal number( 7 , 2 ) ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 5555.55 ) ;
commit ;
执行统计:
select SUM ( distinct sal) , SUM ( all sal) , SUM ( sal) from table3;
结果: 6666.66 7777.77 7777.77
count(*|[distinct|all]x)
【功能】统计数据表选中行x列的合计值。
【参数】
* 表示对满足条件的所有行统计,不管其是否重复或有空值( NULL )
all 表示对所有的值统计, 默认为all
distinct 只对不同的值统计,
如果有参数distinct 或all ,需有空格与x( 列) 隔开,均忽略空值( NULL ) 。
【参数】x,可为数字、字符、日期型及其它类型的字段
【返回】数字值
count ( * ) = sum ( 1 )
【示例】
环境:
create table table3( xm varchar ( 8 ) , sal number( 7 , 2 ) ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 5555.55 ) ;
insert into table3 values ( '' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 0 ) ;
commit ;
执行统计:
select count ( * ) , count ( xm) , count ( all xm) , count ( distinct sal) , count ( all sal) , count ( sal) , sum ( 1 ) from table3;
结果: 5 4 4 3 5 5 5
MAX([distinct|all]x)
【功能】统计数据表选中行x列的最大值。
【参数】all 表示对所有的值求最大值, distinct 只对不同的值求最大值,默认为all
如果有参数distinct 或all ,需有空格与x( 列) 隔开。
【参数】x,可为数字、字符或日期型字段
【返回】对应x字段类型
【示例】
环境:
create table table3( xm varchar ( 8 ) , sal number( 7 , 2 ) ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 5555.55 ) ;
insert into table3 values ( '' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 0 ) ;
commit ;
执行统计:
select MAX ( distinct sal) , MAX ( xm) from table3;
结果:5555.55 zhu
MIN([distinct|all]x)
【功能】统计数据表选中行x列的最大值。
【参数】all 表示对所有的值求最大值, distinct 只对不同的值求最大值,默认为all
如果有参数distinct 或all ,需有空格与x( 列) 隔开。
【参数】x,可为数字、字符或日期型字段
【返回】对应x字段类型
注:字符型字段,将忽略空值( NULL )
【示例】
环境:
create table table3( xm varchar ( 8 ) , sal number( 7 , 2 ) ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'gao' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 5555.55 ) ;
insert into table3 values ( '' , 1111.11 ) ;
insert into table3 values ( 'zhu' , 0 ) ;
commit ;
执行统计:
select MIN ( distinct sal) , MIN ( xm) , MIN ( distinct xm) , MIN ( all xm) from table3;
结果:0 gao gao gao