Oracle函数
一、函数类型
- 单行函数
- 多行函数(聚合函数)
二、字符、日期、数字转换函数
- 字符函数
大小写处理函数 | |
lower('SQL Course') | 全部转小写 'sql course' |
upper('SQL Course') | 全部转大写 'SQL COURSE' |
initcap('SQL Course') | 单词首字母大写,其他小写'Sql Course' |
文本操作函数 | |
concat('Hello','World') | 文本合并 'HelloWorld' |
substr('HelloWorld',1,5) | 取文本前5个字节 Hello |
length('HelloWorld') | 返回文本长度 10 |
instr('HelloWorld',‘w’) | 返回参数所在位置 6 |
lpad(salary,10,'*') | 左填充 ******24000 |
rpad(salary,10,'*') | 右填充 24000****** |
trim('H',from 'HelloWorld') | 删除指定文本 elloWorld |
repalce(123456789,456,'***) | 替换文本123***789 |
- 数字函数
round(123.456,2) | 四舍五入指定位小数 123.46 |
trunc(123.456,2) | 截取指定位小数 123.45 |
mod(1600,300) | 取余 |
- 日期函数
months between('2020/10/08','2021/10/08') | 返回两个日期之间相差的月数 12 |
add_months(sysdate,1) | 在当前时间基础上加上1个月 |
next_day(sysdate,1) | 当前时间的下一个星期日(1~7,1为星期日) |
last_day(sysdate) | 返回当前月份的最后一天 |
round(sysdate,'months') | 四舍五入日期,第二个参数不填默认截取到天 |
trunc(sysdate,'mm') | 截取日期,截取到月,天数为默认值1 |
日期计算时:
date + n :从日期加或者减一个数,结果是一个日期值
date - date:两个日期相减,得到两个日期之间的天数
date + n/24:用小时数除以24,可以加小时到日期上
- 转换函数
从 | 到 |
varchar2 or char | number |
varchar2 or char | date |
number | varchar2 |
date | varchar2 |
三、通用函数
通用函数可适用任意数据类型,并且适用于空值
NVL(expr1,expr2) | 检查参数一,参数一不为空时返回参数一,否则返回参数二。 |
NVL2(expr1,expr2,rxpr3) | 检查参数一,参数一不为空时返回参数二,否则返回参数三。 |
NULLIF(expr1,expr2) | 比较两个表达式,如果相等,返货空,否则返回表达式一(表达式一不能为空)。 |
COALESCE(expr1,expr2,......,exprn) | 返回参数列表第一个非空参数。 |
CASE表达式:CASE表达式可以让你在SQL语句中使用IF-THEN-ELSE逻辑。如果没有WHEN....THEN满足条件,并且ELSE子句存在,
Oracle返回else_expr。否则返回null。所以的表达式(expr、 comparison_expr1、retum_expr)必须是相同数据类型。
case expr when comparison_expr1 then return_expr1
when comparison_expr2 then return_expr2
when comparison_expr3 then return_expr3
else else_expr]
end
decode表达式:
decode(expr,comparison_expr1,return_expr1,
comparison_expr2,return_expr2,
comparison_expr3,return_expr3)
四、多行函数(聚合函数)
AVG():平均值
COUNT():计数
MAX():最大值
MIN():最小值
SUM():合计
参数的数据类型可以是CHAR、VARCHAR2、NUMBER、DATE。
所以组函数忽略空值,可以用NVL、NVL2、COALESCE函数代替空值运算。
COUNT函数有三种格式:
COUNT(*)
COUNT(expr)
COUNT DISTINCT(expr)
五、GROUP BY函数
select id,name,sum(age) from table_name group by id,name order by id;
过滤条件
select id,name,avg(age) from table_name group by id,name having age>18 order by id;
in(a,b,c) | 等于其中一个即可(属于) |
any(a,b,c) | 比较子查询返回的每一个值(或) |
all | 比较子查询返回的全部值(且) |