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

共计 10027 个字符，预计需要花费 26 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家介绍 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

注意：

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

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

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

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

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

向 AI 问一下细节