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的最小整数。
floor(n)函数返回小于或等于n的最大整数
用to_char直接转的话会报错用字段才可以,我有一篇博客写了这个,这里就不再写了;
ceil:向上取整:
3.Add_months //向指定日期中加上若干个月数
代码:
select add_months(to_date('2020-05-01','yyyy-mm-dd'),1) from dual
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
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
根据部门分组,然后按照名字排序连续求和:
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