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