【数据库】Oracle常用函数整理（随机附带mysql）

Oracle常用函数整理（随机附带mysql）

2021/9/13 周一
有些东西很久不用就容易忘，在这里简单记录一下：

1. 字符串操作

substr(string, a)

截取字符串string，从索引a开始（0和1都表示第1位），到结尾

substr(string, a, b)

截取字符串string，从索引a开始（0和1都表示第1位），截取长度为b

mysql字符串便捷截取：
left(str,a) 从str的首位开始，截取长度为a
right(str,a) 从str的末尾开始，截取长度为a
例：SELECT left(‘test123’,4), right(‘test123’,3) FROM dualL
结果为 test 和123

concat(str1,str2)

字符串拼接，mysql可以拼接多个，而ORACLE则只支持2个参数，如果有多个参数，要用 || 符号

trim(str) 去除字符串str首尾两端空格

ltrim(str) 去除字符串str左边的空格

rtrim(str) 去除字符串str右边的空格

replace(‘abcda’,‘ab’,‘e’) = ‘ecda’

把abcd中的ab替换为e，需要替换的’ab’是作为一个整体

translate(‘abcda’,‘ab’,‘ef’) = ‘efcde’

'a’与’b’作为单独的字符分别替换，被换的’ab’与替换成的’ef’需要一一对应（个数一样）

2. 数字函数

ceil(number) 向上取整

floor(number) 向下取整

round(number, decimals)

对number四舍五入，decimals表示精度，为0表示取整，为负数表示小数点左侧继续四舍五入

trunc(number, decimals)

与round类似，但不进行四舍五入，直接按原值截取

3. 日期函数

round(date, fmt)

对date日期进行四舍五入，fmt是格式，不指定格式则返回一个最接近date参数的日期

trunc(date, fmt)

与round类似，截取date日期，但不进行四舍五入；也可以忽略fmt，则简单的截掉时分秒，返回年月日部分

add_months(sysdate,-12)

返回当前日期的12个月前的月份

类似的还有很多

4. 条件相关

nvl(str1, str2)

如果str1为空，则返回str2，也可以是数字

nvl(str1, str2, str3)

如果str1为空，则返回str2，否则返回str3

case when 条件语句

case when 条件1 then 值1 when 条件2 then 值2 ... else 值3 end

条件1成立的时候返回值1，条件2成立返回值2，以此类推，都不符合返回值3

还可以嵌套：

case when 条件1 then (case when 条件2 then 值1 else 值2 end) else 值3 end

decode(字段1, 值1, 赋值a, 值2, 赋值b, … , 值n, 赋值m, 赋值x)

字段1=值1时，返回赋值a，=值2时返回赋值b，以此类推，如果字段1的值都不在这些值里面，则返回赋值x

5. 类型转换

to_date(string, fmt)

字符串转为日期，字符串必须是能转换成日期的格式

如果是写SQL时限定具体日期，也可以直接用date’string’

mysql字符串转日期
str_to_date(str,‘%Y-%m-%d’)

to_char(date, fmt)

把日期转为指定格式的字符类型

to_number(str, fmt)

字符串转换为数值，str必须是能转换成数值的格式
fmt格式例：‘FM990.00’，9代表1个数字，0强迫显示0，FM表示清除前后空格和由9转化的0

6. 分析函数

sum(field) / count(field) / min(field) / max(field) / avg(field)

聚合函数

rollup(field1,field2,…)

可以在最后求和，有多个字段时，在每个字段的分组后面都会求和一次（按字段顺序）

rollup((field1,field2,…))

如果需要按多字段排序、并且只在最后求总和，要用2个括号

greatest(expr_1, expr_2, …expr_n)

从表达式（列、常量、计算值）expr_1, expr_2, … expr_n等中找出最大的数返回。
在比较时，OracIe会自动按表达式的数据类型进行比较，以expr_1的数据类型为准。

least(expr_1, expr_2, …expr_n)

同上，取最小值

row_number() over(partition by … order by …)

分组排序函数
over()是窗口函数，聚合函数也可以搭配窗口使用
PARTITION BY 分组，ORDER BY 排序，可以只用一个
row_number()以行号排序，1,2,3,4…

rank() over(partition by … order by …)

跳跃排序，1,2,2,4…

dense_rank() over(partition by … order by …)

连续排序，1,2,2,3…

7. mysql in函数的升级版 find_in_set(str,strlist)

str是要匹配的字符串，strlist是需要查询的字段
就是在strlist字段的结果里，查出所有含有str字符串的数据

（等之后mysql的笔记多了再单独拿出来总结一篇博客吧）