SQL函数大全-优快云博客

本文链接：https://blog.youkuaiyun.com/chw65897/article/details/115873105

sql函数大全:

1.日期和字符转换函数用法：（to_char,to_date，to_number）

使用to_date函数将字符转换成日期。

使用to_char函数对日期的转换，并也可以对数字转换。

使用to_number函数将字符转换成数字

格式：必须包含在单引号中且大小写敏感；可以包含任意的有效的日期格式；日期之间用逗号隔开。

注意：（1）Dual 是一个‘伪表’作用于用来测试函数和表达式；（2）sysdate 格式化当前系统日期和时间

2.日期函数调试：

1.Months_between //两个日期相差的月数

代码：

select months_between(to_date('2020-05-01','yyyy-mm-dd'),to_date('2020-04-26','yyyy-mm-dd')) from dual

在这里插入图片描述

需要是整数的话需要用to_char转一下再用to_date然后在求月份差，也可以用ceil和floor来进行向上或向下的取整；

2.向上取整和向下取整CEIL和FLOOR函数

ceil( n )函数是返回大于或等于n的最小整数。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FvMEH5Vj-1618833895819)(C:\Users\MrChang\AppData\Roaming\Typora\typora-user-images\image-20210419104918268.png)]$

floor(n)函数返回小于或等于n的最大整数

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GHcswZNi-1618833895821)(C:\Users\MrChang\AppData\Roaming\Typora\typora-user-images\image-20210419104905470.png)]$

用to_char直接转的话会报错用字段才可以,我有一篇博客写了这个,这里就不再写了;

ceil:向上取整:

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LgKEacjX-1618833895824)(C:\Users\MrChang\AppData\Roaming\Typora\typora-user-images\image-20210419103353057.png)]$

3.Add_months //向指定日期中加上若干个月数

代码：

select add_months(to_date('2020-05-01','yyyy-mm-dd'),1) from dual

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CuiHwwln-1618833895825)(C:\Users\MrChang\AppData\Roaming\Typora\typora-user-images\image-20210419103543240.png)]$

4.Next_day //指定日期的下一个星期对应的日期

代码：

select next_day(to_date('2020-05-01','yyyy-mm-dd'),'星期二') from dual

在这里插入图片描述

5.Last_day //本月的最后一天

代码：

select last_day(to_date('2020-05-01','yyyy-mm-dd')) from dual

在这里插入图片描述

6.Round //日期四舍五入（数字四舍五入）

select round(to_date('2020-05-01','yyyy-mm-dd'),'year') from dual

在这里插入图片描述

select round(to_date('2020-05-09','yyyy-mm-dd'),'month') from dual

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OomiHudv-1618833895829)(C:\Users\MrChang\AppData\Roaming\Typora\typora-user-images\image-20210419104100897.png)]$

round(a,b)数字四舍五入 a表示要四舍五入的数字,b表示要四舍五入的位数默认值是0 正数表示取小数点后几位,负数表示取小数据点前几位

在这里插入图片描述

7.Trunc //日期截断（数字截取）

 返回当前年的第一天
select trunc(to_date('2020-05-09','yyyy-mm-dd'),'year') from dual

在这里插入图片描述

返回当月的第一天
select trunc(to_date('2020-05-09','yyyy-mm-dd'),'month') from dual

在这里插入图片描述

#/日期******/
1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
2.select trunc(sysdate, ‘mm’) from dual --2013-01-01 返回当月第一天.
3.select trunc(sysdate,‘yy’) from dual --2013-01-01 返回当年第一天
4.select trunc(sysdate,‘dd’) from dual --2013-01-06 返回当前年月日
5.select trunc(sysdate,‘yyyy’) from dual --2013-01-01 返回当年第一天
6.select trunc(sysdate,‘d’) from dual --2013-01-06 (星期天)返回当前星期的第一天
7.select trunc(sysdate, ‘hh’) from dual --2013-01-06 17:00:00 当前时间为17:35
8.select trunc(sysdate, ‘mi’) from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确

trunc(Number,Num_digits) 数字截取(截取时不进行四舍五入)
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。正数表示截取小数点后几位,负数表示截取小数据点前几位

SELECT trunc(123.456) FROM DUAL;

在这里插入图片描述

SELECT trunc(123.456,1) FROM DUAL;

在这里插入图片描述

SELECT trunc(123.456,-2) FROM DUAL;

在这里插入图片描述

8.系统的日期和时间格式

是指格式当前系统的日期和时间，并使用to_char数字转换函数。

代码：select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
在这里插入图片描述

9.数字符号转换格式

To_char格式：9数字、0零、$美元符、L本地货币符号、.小数点、,千位符

10.字符隐式格式

若字符串中有特殊字符，例如 ’123456789’,则无法进行隐式转换，需要使用to_numbar()来完成

11.查询相关的日期

对于把日期作为查询条件的查询，一般都使用to_date() 把一个字符串转为日期，这样可以不必关注日期格式

3.其他函数

1.decode函数

decode(X，A，B，C，D，E）
这个函数运行的结果是，当X = A，函数返回B；当X != A 且 X = C，函数返回D；当X != A 且 X != C，函数返回E。
其中，X、A、B、C、D、E都可以是表达式，这个函数使得某些sql语句简单了许多。

2.随机数:

oracle拼接随机数:
select 字段||SYS_GUID() from dual;

产生随机数

     SELECT DBMS_RANDOM.RANDOM FROM DUAL;          产生一个任意大小的随机数

    SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100)) FROM DUAL;          产生一个100以内的随机数

     SELECT TRUNC(100+900*dbms_random.value) FROM dual;          产生一个100～1000之间的随机数

     SELECT dbms_random.value FROM dual;          产生一个0～1之间的随机数

     SELECT dbms_random.value(10,20) FROM dual;          产生一个10～20之间的随机数

     SELECT dbms_random.normal FROM dual;          NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1，
	 期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。

3、产生随机字符串

select dbms_random.string(‘P’,20) from dual;

第一个参数 P 表示 printable，即字符串由任意可打印字符构成第二个参数表示返回字符串长度

DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。
要产生两位的随机数，可以DBMS_RANDOM.VALUE()*100，这样产生( 0,100 )的随机数，当产生( 0,10)之间的数时，只要加上10就可以保证产生的数都是两位了。

ORACLE的PL/SQL提供了生成随机数和随机字符串的多种方式，罗列如下:
1、小数( 0 ~ 1)
select dbms_random.value from dual
2、指定范围内的小数 ( 0 ~ 100 )

select dbms_random.value(0,100) from dual
3、指定范围内的整数 ( 0 ~ 100 )
select trunc(dbms_random.value(0,100)) from dual
4、长度为20的随机数字串
select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual
5、正态分布的随机数
select dbms_random.normal from dual
6、随机字符串
select dbms_random.string(opt, length) from dual
opt可取值如下:‘u’,‘U’:大写字母’l’,‘L’:小写字母’a’,‘A’:大、小写字母’x’,‘X’:数字、大写字母’p’,‘P’:可打印字符
7、随机日期
select to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J’) from dual
通过下面的语句获得指定日期的基数
select to_char(sysdate,‘J’) from dual
8、生成GUID
select sys_guid() from dual

GREATEST和least的用法相同

语法介绍：
1 语法
GREATEST(expr_1, expr_2, …expr_n)
2 说明
GREATEST(expr_1, expr_2, …expr_n)函数从表达式（列、常量、计算值）expr_1,expr_2, … expr_n等中找出最大的数返回。在比较时，OracIe会自动按表达式的数据类型进行比较，以expr_1的数据类型为准。

3 允许使用的位置
过程性语句和SQL语句。

expr_1为数值型。按大小进行比较。
全部为数值型，取出最大值为16：
SELECT GREATEST(2, 5, 12, 3, 16, 8, 9) A FROM DUAL;

在这里插入图片描述

部分为数值型，但是字符串可以根据expr_1的数据类型通过隐式类型转换转成数值型：
SELECT GREATEST(2, ‘5’, 12, 3, 16, 8, 9) A FROM DUAL;

在这里插入图片描述

least和GREATEST用法相同这里就不作介绍了;

substr函数格式 (俗称：字符截取函数)

格式1： substr(string string, int a, int b);

格式2：substr(string string, int a) ;

解析：

格式1：
    1、string 需要截取的字符串
    2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
    3、b 要截取的字符串的长度
格式2：
    1、string 需要截取的字符串
    2、a 可以理解为从第a个字符开始截取后面所有的字符串。

①select substr(‘HelloWorld’,-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色第三个注解）
②select substr(‘HelloWorld’,-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色第三个注解）
③select substr(‘HelloWorld’,-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
④select substr(‘HelloWorld’,-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）
（注：当a等于0或1时，都是从第一位开始截取(如：1和2)）
（注：假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）

（注：虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符；只要 |a| ≤ b,取a的个数(如：①、②、③)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：③和④)）
⑤select substr(‘HelloWorld’,-1) value from dual; //返回结果：d，从最后一个“d”开始往回截取1个字符
⑥select substr(‘HelloWorld’,-2) value from dual; //返回结果：ld，从最后一个“d”开始往回截取2个字符
⑦select substr(‘HelloWorld’,-3) value from dual; //返回结果：rld，从最后一个“d”开始往回截取3个字符
（注：当只有两个参数时；不管是负几，都是从最后一个开始往回截取(如：⑤、⑥、⑦)）

instr()函数的格式（俗称：字符查找函数）

格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2位置。

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。
实例

格式一

①select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
②select instr('helloworld','lo') from dual; --返回结果：4    即“lo”同时(连续)出现，“l”的位置
③select instr('helloworld','wo') from dual; --返回结果：6    即“w”开始出现的位置

格式二

①select instr('helloworld','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
②select instr('helloworld','l',3,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
③select instr('helloworld','l',4,2) from dual;  --返回结果：9    也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
④select instr('helloworld','l',-1,1) from dual;  --返回结果：9    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
⑤select instr('helloworld','l',-2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置
⑥select instr('helloworld','l',2,3) from dual;  --返回结果：9    也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
⑦select instr('helloworld','l',-2,3) from dual; --返回结果：3    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置

mod取模

mod(a,b) a是被除数;b是除数求出来的是余数,也就是取模
select mod(5,3) from dual

在这里插入图片描述

判断空值函数：

COALESCE()函数
主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下：
COALESCE ( expression,value1,value2……,valuen)
COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。

如果expression不为空值则返回expression；否则判断value1是否是空值，

如果value1不为空值则返回value1；否则判断value2是否是空值，

如果value2不为空值则返回value2；……以此类推，
如果所有的表达式都为空值，则返回NULL。

MYSQL:
IFNULL(expression,value)
MSSQLServer:
ISNULL(expression,value)
Oracle:
NVL(expression,value)

这几个函数的功能和COALESCE(expression,value)是等价的。

replace(字符串,要被替换的字符,替换后的字符)

如果替换后的内容为空，则变成删除作用了

regexp_replace函数（这个我之前在博客中有实例介绍可以查看一下）
REGEXP_REPLACE(source, pattern, replace_str)
source：对应字段
pattern: 正则表达式
replace_str：替换成什么
regexp_replace函数是replace函数的扩展函数，用于通过正则表达式来进行匹配替换，默认情况下，每次匹配到的正则，都替换为replace_str，返回的字符串与source字符集相同。如果source为非LOB类型，则返回varchar2数据类型，如果为LOB类型，则返回CLOB类型，该函数符合POSIX正则和Unicode正则。

INSERT(str,pos,len,newstr)不适用于oracle

语法格式:
str:指定字符串
pos:开始被替换的位置
len:被替换的字符串长度
newstr:新的字符串
总结:替换掉str范围为pos,pos+len的字符串
注意:如果pos>str长度以内,则返回str不会被替换掉;如果len>str剩余字符串的长度,则将str剩下所有字符都替换成newstr;如果任何参数是NULL,会报错。

select insert('helloworld',2,3,'new') from dual

如果pos>str长度以内,则返回str不会被替换掉:

在这里插入图片描述

如果len>str剩余字符串的长度,则将str字符串pos之后剩下所有字符都替换成newstr:

在这里插入图片描述

如果len的长度>newstr字符串的长度,则按照newstr把len的长度占满

在这里插入图片描述

limit分页操作:

limit (m,n) :m表示从序号几开始(默认为0),n表示取几条数据
eg. 从第二条数据开始取两条数据:

select * from table limit 1,2;

取前五条数据:

select * from table limit 5;

窗口函数over():(oracle数据库是支持hive中的窗口函数):

排名函数:

dense_rank() :排名相同时不跳跃,例如:1,1,2

select b.*,dense_rank() over(partition by deptno order by sal desc) rn  from emp b

在这里插入图片描述

row_number():按照顺序排序:出现相同时也是递增

select b.*,row_number() over(partition by deptno order by sal desc) rn from emp b

在这里插入图片描述

rank() :和dense_rank类似,相同时会跳跃:1,1,3

select b.*,rank() over(partition by deptno order by sal desc) rn from emp b

在这里插入图片描述

–排序，按照序号分组分成几组
NTILE() over()

按照工资降序然后分成五组:
select b.*,ntile(5) over(order by sal desc) nt from emp b

在这里插入图片描述

一共16行分成五组第一组四个,剩下都是三个;

分区内在分五组
select b.*,ntile(5) over(partition by deptno order by sal desc) nt from emp b

在这里插入图片描述

sum()over()

根据部门分组,然后求和,求和的内容是不变的就是部门工资总和:
select b.*,sum(sal) over(partition by deptno order by ename) sum from emp b

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jHVBoIlc-1618833895851)(C:\Users\MrChang\AppData\Roaming\Typora\typora-user-images\image-20210419163818622.png)]$

根据部门分组,然后按照名字排序连续求和:
select b.*,sum(sal) over(partition by deptno order by ename) sum from emp b

在这里插入图片描述

count() over()

分区累加:
select b.*,count(1) over(partition by deptno) sum from emp b

在这里插入图片描述

分区然后按照工资升序连续累加:
select b.*,count(1) over(partition by deptno order by sal) sum from emp b

在这里插入图片描述

sql常用函数大全

sql函数大全:

1.日期和字符转换函数用法：（to_char,to_date，to_number）

2.日期函数调试：

1.Months_between //两个日期相差的月数

2.向上取整和向下取整CEIL和FLOOR函数

3.Add_months //向指定日期中加上若干个月数

4.Next_day //指定日期的下一个星期 对应的日期

5.Last_day //本月的最后一天

6.Round //日期四舍五入（数字四舍五入）

7.Trunc //日期截断（数字截取）

8.系统的日期和时间格式

9.数字符号转换格式

10.字符隐式格式

11.查询相关的日期

3.其他函数

1.decode函数

2.随机数:

GREATEST和least的用法相同

substr函数格式 (俗称：字符截取函数)

instr()函数的格式 （俗称：字符查找函数）

mod取模

判断空值函数：

replace(字符串,要被替换的字符,替换后的字符)

INSERT(str,pos,len,newstr)不适用于oracle

limit分页操作:

窗口函数over():(oracle数据库是支持hive中的窗口函数):

排名函数:

4.Next_day //指定日期的下一个星期对应的日期

instr()函数的格式（俗称：字符查找函数）