mysql和oracle中函数的作用是什么

这篇文章给大家介绍MySQL和oracle中函数的作用是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：申请域名、网络空间、营销软件、网站建设、武陵网站维护、网站推广。

一、nvl()函数：

(1)oracle中的nvl()函数：

语法：

nvl(expr1,expr2)

含义：：

如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。

(2)mysql中的”nvl()”函数：

语法：

if null(expr1,expr2)

含义：：

如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。

二、decode()函数：

(1)oracle中的decode()函数：

语法：

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,默认值)。

含义：：

当条件的值等于值1的时候，返回值1，当条件的值等于值2的时候，返回值2，当条件的值等于值n的时候，返回值n，如果条件与值1-值n都不想等，则返回默认值。相当于java中的if-else。

(2)mysql中的”decode()”函数：

语法：

ifnull(

etl(

filed(col,…)

)

)

filed函数：

语法：filed(str,str1,str2,str3……..)

含义：返回str在str1,str2,str3…..中的位置，假如str=str1，则返回1,;假如str=str2，则返回2。。。。。假如str1-strn中都没有str的值，则返回0。

注意：如果str 为NULL，则返回值为0 ，原因是NULL不能同任何值进行同等比较。

elt函数：

语法：elt(N,str1,str2,str3,…strn)

含义：如果N=1，则返回str1，N=2，则返回str2，N>n,，则返回null。

ifnull函数：

语法：if null(expr1,expr2)

含义：如果expr1为空那么返回expr2，如果expr1值不为空，则返回expr1。

总结以上三个函数：

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,默认值)

等价于：

ifnull(elt(filed(条件，值1，值2….值n)，返回值1，返回值2…..返回值n)，默认值)

三、分页：

(1)oracle中的分页：

语法：

select * from(select t.* ,rownum r from 表名 t)where r>10 and r<20;

注意：

(1)表名必须要取别名：表名 t;

(2)*前面必须要指明表的别名;

(3)rownum 这个字段必须要用别名：,rownum r ;

(4)分页的范围必须要用别名进行判断： r>10 and r<20;

(2)mysql中的分页：

语法：

SELECT * FROM 表名 LIMIT 起始值-1, 取多少条记录;

举例：

SELECT * FROM 表名 LIMIT 20, 10 ;

说明：从第21条记录开始去10条记录，即取得是21,22,23,24,25,26,27,28,29,30这10条记录。

四、字符串截取：

(1)oracle中的字符串截取：

语法：

select substr(“目标字符串”，开始坐标，结束坐标) from dual;

注意：

oracle中的下标可以从0开始，也可以从1开始，而且两个效果是一样的，但是如果从-1开始则是取最后一个，截取长度这个参数将会失效。

注意：

oracle中是没有substring这个函数的。

(2)mysql中的字符串截取：

语法一：

select substr(“目标字符串”，开始坐标，截取长度) from dual;

注意：

1、myql中的下标不能从0开始;

2、mysql下标也可以从-1开始，情况和orace一样。

语法二：

select substring(“目标字符串”，开始坐标，截取长度) from dual;

注意：

myql中的substr和substring函数是一样的效果。

语法三：

select mid(“目标字符串”，开始坐标，截取长度) from dual;

注意：

myql中的substr、mid和substring这三个函数是一样的效果。

语法四：

select left(“目标字符串”，从左侧第一个开始的截取长度) from dual;

语法五：

select right(“目标字符串”，从右侧最后一个开始的截取长度) from dual;

五、结果集拼接(用，隔开)：

(1)oracle中的wm_concat函数：

语法：

select 字段1 wm_concat(字段2) from 表名 group by 字段1;

注意：

wm_concat是分组函数，前面查询的字段需要在后面group by一下，如果不group by一个字段的话将会把所有行的字段2都用逗号拼接起来。

例子：

(2)mysql中的group_concat函数：

语法：

select 字段1 group_concat(字段2)

from 表名 group by 字段1;

注意：

1、group_concat也是分组函数，前面查询的字段同样需要在后面group by一下，如果不group by一个字段的话将会把所有行的字段2都用逗号拼接起来—–例子和oracle一样，这里就不举例了;

2、如果字段2中的值为null的时候，group_concat函数是不会留，给那个null值的，比如有三条记录，中间那条记录的字段2是null值，那么group_concat之后拼接出来的字段就只有两个值，即：记录1的字段2，记录3的字段2，而不是记录1的字段2，，记录3的字段2。

六、uuid：

(1)oracle中获取uuid：

语法：

INSERT INTO 表名 VALUES ((select lower(sys_guid()) from dual), 值2, 值3);

(2)mysql中获取uuid：

语法：

INSERT INTO 表名 VALUES (replace(uuid(), ‘-‘,”), 值2,值3);

七、字符串转数字：

(1)oracle中数字转字符串：

语法：

select * from 表 order by to_number(字段);

(2)mysql中数字转字符串：

语法一：

select * from 表 where deleted=0 order by Convert(字段,int);

语法二：

select * from 表 where deleted=0 order by CAST(字段 as int);

语法三：

select * from 表 where deleted=0 order by (ordernumber+0);

第一、row_number() over(partition by

首先要了解下oracle中这个函数的用法，看个例子。

select t.*,row_number() over(partition by t.owner order by y.createDate desc) rn from test t

这个语句的意思就是，把test这个表的数据按照owner 分组并且给每个分组的里面的数据加上一个序列号，数据格式如下：

id name owner createDate rn

1 aa 001 1

2 bb 001 2

3 cc 001 3

4 dd 002 1

5 ee 002 2

6 ff 003 1

数据搞得不太正规，但是应该能够看懂它的意思吧，但是在mysql中是没有这个函数的于是乎，找啊找，总算找了个解决方法，如下：

SELECT

heyf_tmp.*,

IF(@pdept=heyf_tmp.owner ,@rn:=@rn+1,@rn:=1) AS rn,

@pdept:=heyf_tmp.owner

FROM

(

SELECT

yv.*

FROM

test yv

ORDER BY

yv.owner ,

yv.createDate DESC

)

heyf_tmp ,

(

SELECT

@rn :=0 ,

@pdept := NULL ,

@rn:=0

)

aa

具体是什么意思，不是很清楚，不过先解决问题再说。

第二、oracle树形查询

oracle树形查询现成的方法

select distinct t.id as id, t.name

from test t

start with id=‘’

connect by prior id = parentid

但是mysql中是没有这个方法的，于是只能自己定义函数或者过程，我这里用的是过程。

如下：

CREATE PROCEDURE Pro_GetTreeList`(in pid varchar(36))

begin

declare lev int;

set lev=1;

drop table if exists tmp1;

CREATE TABLE tmp1(id VARCHAR(40),name varchar(50),parentid varchar(40) ,levv INT);

INSERT tmp1 SELECT id,name,parent_id,1 FROM `test` WHERE parent_id=pid;

while row_count()>0

do set lev=lev+1;

INSERT tmp1 SELECT t.id,t.name,t.parent_id,lev from testt join tmp1 a on t.parent_id=a.id AND levv=lev-1;--查出子节点

end while ;

INSERT tmp1 SELECT id,name,parent_id,0 FROM test WHERE id=pid; --查出当前节点

end

这个存储过程应该都能看懂吧，就不做多解释了。

第三 、函数中定义类似一维、二维数组，oracle中可以直接定义的，如：

return number is newList := NewList((1,2,3),(4,5,6));这样可以直接返回 return newList(1,2) 返回的就是2了，但是mysql中却不能这样写的，不过也有这样的方法解决：

对于一维的来说，很简单，有函数的elt函数，可以直接return elt(index,'1','2','3'); 如果elt(3,'1','2','3') 这样返回的就是 3了，不过对于二维的来说好像没有现成的函数用了，我的解决方法是改成个表来做，把数据录入到表中，然后去查表，这丫要给你也是可以实现。

第四、 mysql中获取汉字的首个大写字母

mysql中遇到了这样的一个问题。

DROP FUNCTION IF EXISTS `getPY`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `getPY`(in_string VARCHAR(65534)) RETURNS mediumtext CHARSET utf8

BEGIN

DECLARE tmp_str VARCHAR(65534) charset gbk DEFAULT '' ; #截取字符串，每次做截取后的字符串存放在该变量中，初始为函数参数in_string值

DECLARE tmp_len SMALLINT DEFAULT 0;#tmp_str的长度

DECLARE tmp_char VARCHAR(2) charset gbk DEFAULT '';#截取字符，每次 left(tmp_str,1) 返回值存放在该变量中

DECLARE tmp_rs VARCHAR(65534) charset gbk DEFAULT '';#结果字符串

DECLARE tmp_cc VARCHAR(2) charset gbk DEFAULT '';#拼音字符，存放单个汉字对应的拼音首字符

SET tmp_str = in_string;#初始化，将in_string赋给tmp_str

SET tmp_len = LENGTH(tmp_str);#初始化长度

WHILE tmp_len > 0 DO #如果被计算的tmp_str长度大于0则进入该while

SET tmp_char = LEFT(tmp_str,1);#获取tmp_str最左端的首个字符，注意这里是获取首个字符，该字符可能是汉字，也可能不是。

SET tmp_cc = tmp_char;#左端首个字符赋值给拼音字符

IF LENGTH(tmp_char)>1 THEN#判断左端首个字符是多字节还是单字节字符，要是多字节则认为是汉字且作以下拼音获取，要是单字节则不处理。

SELECT ELT(INTERVAL(CONV(HEX(tmp_char),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC

,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1),

'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') INTO tmp_cc; #获得汉字拼音首字符

END IF;

SET tmp_rs = CONCAT(tmp_rs,tmp_cc);#将当前tmp_str左端首个字符拼音首字符与返回字符串拼接

SET tmp_str = SUBSTRING(tmp_str,2);#将tmp_str左端首字符去除

SET tmp_len = LENGTH(tmp_str);#计算当前字符串长度

END WHILE;

RETURN tmp_rs;#返回结果字符串

END;;

DELIMITER ;

第四、 图片clob的修改

系统中的功能是把图片存在blob字段中的，，原来用的是oracle，java中对应的字段是Object，但是mysql中如果这样做却不能独处图片，原因可能是因为在往Object中写数据时出错了，这个号解决，只要把java中对应字段的类型改成byte[] 就行了。

1. Oracle 中的 TO_DATE ()

示例： select * from admadjustmoney t where t.sendtime> to_date(?,'yyyy-mm-dd hh34:mi:ss') and t.sendtime< to_date(?,'yyyy-mm-dd hh34:mi:ss') 　　转换后：　　SELECT * 　　FROM `ADMADJUSTMONEY` t 　　WHERE t.SENDTIME > STR_TO_DATE(sysdate(), '%Y-%m-%d %h:%i:%s') AND

t.SENDTIME < STR_TO_DATE(sysdate(), '%Y-%m-%d %h:%i:%s') 注意表名字段名的大小写 MYSQL 大小写敏感 sql 统一大写 hql 统一小写　　5. Oracle 中的 trunc () 　　示例：　　from PUBCOINOPDETAIL where opervirtualCoin > 0 and status = 1 and operTime >= ADD_MONTHS(TRUNC(SYSDATE,'mm') ,-1) and operTime < TRUNC(SYSDATE,'mm') group by gene , geneType ,accountId 　　转换后：　　from PUBCOINOPDETAIL where opervirtualCoin > 0 and status = 1 and operTime >=DATE_SUB(date_sub(CURDATE(),INTERVAL EXTRACT( day from CURDATE())-1 day),INTERVAL 1 MONTH) [ 上个月第一天 ] and operTime;

再次提醒 mysql 大小写的问题 sql 统一大写 hql 统一小写 避免不必要的 BUG 出现。

网络资料附表：oracle 常用函数 与mysql 的对照表 s:mysql o ：oracle

1. 绝对值

S:select abs(-1) value

O:select abs(-1) value from dual

2. 取整 ( 大 )

S:select ceiling(-1.001) value

O:select ceil(-1.001) value from dual

3. 取整(小)

S:select floor(-1.001) value

O:select floor(-1.001) value from dual

4. 取整(截取)

S:select cast(-1.002 as int) value

O:select trunc(-1.002) value from dual

5. 四舍五入

S:select round(1.23456,4) value 1.23460

O:select round(1.23456,4) value from dual 1.2346

6.e 为底的幂

S:select Exp(1) value 2.7182818284590451

O:select Exp(1) value from dual 2.71828182

7. 取 e 为底的对数

S:select log(2.7182818284590451) value 1

O:select ln(2.7182818284590451) value from dual; 1

8. 取 10 为底对数

S:select log10(10) value 1

O:select log(10,10) value from dual; 1

9. 取平方

S:select SQUARE(4) value 16

O:select power(4,2) value from dual 16

10. 取平方根

S:select SQRT(4) value 2

O:select SQRT(4) value from dual 2

11. 求任意数为底的幂

S:select power(3,4) value 81

O:select power(3,4) value from dual 81

12. 取随机数

S:select rand() value

O:select sys.dbms_random.value(0,1) value from dual;

13. 取符号

S:select sign(-8) value -1

O:select sign(-8) value from dual -1

14. 圆周率

S:SELECT PI() value 3.1415926535897931

O: 不知道

15.sin,cos,tan 参数都以弧度为单位

例如： select sin(PI()/2) value 得到 1 ( SQLServer )

16.Asin,Acos,Atan,Atan2 返回弧度

17. 弧度角度互换 (SQLServer ， Oracle 不知道 )

DEGREES ：弧度 - 〉角度

RADIANS ：角度 - 〉弧度

数值间比较

18. 求集合最大值

S:select max(value) value from

(select 1 value

union

select -2 value

union

select 4 value

union

select 3 value)a

O:select greatest(1,-2,4,3) value from dual

19. 求集合最小值

S:select min(value) value from

(select 1 value

union

select -2 value

union

select 4 value

union

select 3 value)a

O:select least(1,-2,4,3) value from dual

20. 如何处理 null 值 (F2 中的 null 以 10 代替 )

S:select F1,IFNull(F2,10) value from Tbl

O:select F1,nvl(F2,10) value from Tbl

21. 求字符序号

S:select ascii('a') value

O:select ascii('a') value from dual

22. 从序号求字符

S:select char(97) value

O:select chr(97) value from dual

23. 连接

S:select '11'+'22'+'33' value

O:select CONCAT('11','22') 33 value from dual

23. 子串位置 -- 返回 3

S:select CHARINDEX('s','sdsq',2) value

O:select INSTR('sdsq','s',2) value from dual

23. 模糊子串的位置 -- 返回 2, 参数去掉中间 % 则返回 7

S:select patindex('%d%q%','sdsfasdqe') value

O:oracle 没发现，但是 instr 可以通过第四个参数控制出现次数

select INSTR('sdsfasdqe','sd',1,2) value from dual 返回 6

24. 求子串

S:select substring('abcd',2,2) value

O:select substr('abcd',2,2) value from dual

25. 子串代替 返回 aijklmnef

S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value

O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual

26. 子串全部替换

S: 没发现

O:select Translate('fasdbfasegas','fa',' 我 ' ) value from dual

27. 长度

S:len,datalength

O:length

28. 大小写转换 lower,upper

29. 单词首字母大写

S: 没发现

O:select INITCAP('abcd dsaf df') value from dual

30. 左补空格( LPAD 的第一个参数为空格则同 space 函数)

S:select space(10)+'abcd' value

O:select LPAD('abcd',14) value from dual

31. 右补空格( RPAD 的第一个参数为空格则同 space 函数)

S:select 'abcd'+space(10) value

O:select RPAD('abcd',14) value from dual

32. 删除空格

S:ltrim,rtrim

O:ltrim,rtrim,trim

33. 重复字符串

S:select REPLICATE('abcd',2) value

O: 没发现

34. 发音相似性比较 ( 这两个单词返回值一样，发音相同 )

S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')

O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual

SQLServer 中用 SELECT DIFFERENCE('Smithers', 'Smythers') 比较 soundex 的差

返回 0-4 ， 4 为同音， 1 最高

日期函数

35. 系统时间

S:select getdate() value

O:select sysdate value from dual

36. 前后几日

直接与整数相加减

37. 求日期

S:select convert(char(10),getdate(),20) value

O:select trunc(sysdate) value from dual

select to_char(sysdate,'yyyy-mm-dd') value from dual

38. 求时间

S:select convert(char(8),getdate(),108) value

O:select to_char(sysdate,'hh34:mm:ss') value from dual

39. 取日期时间的其他部分

S:DATEPART 和 DATENAME 函数 (第一个参数决定)

O:to_char 函数 第二个参数决定

参数 --------------------------------- 下表需要补充

year yy, yyyy

quarter qq, q ( 季度 )

month mm, m (m O 无效 )

dayofyear dy, y (O 表星期 )

day dd, d (d O 无效 )

week wk, ww (wk O 无效 )

weekday dw (O 不清楚 )

Hour hh,hh22,hh34 (hh22,hh34 S 无效 )

minute mi, n (n O 无效 )

second ss, s (s O 无效 )

millisecond ms (O 无效 )

----------------------------------------------

40. 当月最后一天

S: 不知道

O:select LAST_DAY(sysdate) value from dual

41. 本星期的某一天(比如星期日)

S: 不知道

O:SELECT Next_day(sysdate,7) vaule FROM DUAL;

42. 字符串转时间

S: 可以直接转或者 select cast('2004-09-08'as datetime) value

O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh34-mi-ss') vaule FROM DUAL;

43. 求两日期某一部分的差(比如秒)

S:select datediff(ss,getdate(),getdate()+12.3) value

O: 直接用两个日期相减(比如 d1-d2=12.3 )

SELECT (d1-d2)*24*60*60 vaule FROM DUAL;

44. 根据差值求新的日期(比如分钟)

S:select dateadd(mi,8,getdate()) value

O:SELECT sysdate+8/60/24 vaule FROM DUAL;

关于mysql和oracle中函数的作用是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。