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中函數的作用是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。