oracle 與 mysql 中的函式總結

建表語句如下:

create table person(
IdNumber varchar(20),
Name varchar(20),
BirthDay datetime,
RegDay datetime,
Weight decimal(10,2)
);

• 1
• 2
• 3
• 4
• 5
• 6
• 7

插入資料如下:

insert into person(IdNumber,Name,BirthDay,RegDay,Weight)
values('123456789120','Tom','1981-03-22','1998-05-01',56.67),
('123456789121','Jim','1987-01-18','1999-08-21',36.17),
('123456789122','Lily','1987-11-08','2001-09-18',40.33),
('123456789123','Kelly','1982-07-12','2000-03-01',46.23),
('123456789124','Sam','1983-02-16','1998-05-01',48.68),
('123456789125','Kerry','1984-08-07','1999-03-01',66.67),
('123456789126','Smith','1980-01-09','2002-09-23',51.28),
('123456789127','BillGates','1972-07-18','1995-06-19',60.32);

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

1.數學函式

    1).求絕對值

        例子:select Weight-50,abs(Weight-50),abs(-5.38) from person;

    2).求指數

        例子:select Weight,power(Weight,-0.5),power(Weight,2),power(Weight,3),power(Weight,4) from person;

    3).求平方根

        例子:select Weight,sqrt(Weight) from person;

    4).求隨機數

        例子:select rand();

    5).舍入到最大整數

        例子:select Name,Weight,ceiling(Weight),ceiling(Weight*-1) from person;

    6).舍入到最小整數

        例子:select Name,Weight,floor(Weight),floor(Weight*-1) from person;

    7).四捨五入

        round()函式的用法:兩個引數的和一個引數的

        兩個引數:round(m,d),m為待進行四捨五入的數值,而d則為計算精度.d還可以取負值.
        例子:select Name,Weight,round(Weight,1),round(Weight*-1,0),round(Weight,-1) from person;

        一個引數:round(m),相當於d=0
        例子:select Name,Weight,round(Weight),round(Weight*-1) from person;

    8).求正弦值

        例子:select Name,Weight,sin(Weight) from person;

    9).求餘弦值

        例子:select Name,Weight,cos(Weight) from person;

    10).求反正弦值

        例子:select Name,Weight,asin(1/Weight) from person;

    11).求反餘弦值

        例子:select Name,Weight,acos(1/Weight) from person;

    12).求正切值

        例子:select Name,Weight,tan(Weight) from person;

    13).求反正切值

        例子:select Name,Weight,atan(Weight) from person;

    14).求X/Y的反正切值

        例子:select Name,Weight,atan2(Weight,2) from person;

    15).求餘切

        例子:select Name,Weight,cot(Weight) from person;

    16).求圓周率pi

        例子:select Name,Weight,Weight*pi(),pi() from person;  

    17).弧度制轉換為角度制

        例子:select Name,Weight,degrees(Weight) from person; 

    18).角度制轉換為弧度制

        例子:select Name,Weight,radians(Weight) from person; 

    19).求符號

        數值大於0返回1，等於0返回0，小於0返回-1
        例子:select Name,Weight-48.68,sign(Weight-48.68) from person; 

    20).求整除餘數

        例子:select Name,Weight,mod(Weight,5) from person;

    21).求自然對數

        例子:select Name,Weight,log(Weight) from person;

    22).求以10為底的對數

        例子:select Name,Weight,log10(Weight) from person;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• 51
• 52
• 53
• 54
• 55
• 56
• 57
• 58
• 59
• 60
• 61
• 62
• 63
• 64
• 65
• 66
• 67
• 68
• 69
• 70
• 71
• 72
• 73
• 74
• 75
• 76
• 77
• 78
• 79
• 80
• 81
• 82
• 83
• 84
• 85
• 86
• 87
• 88
• 89
• 90
• 91
• 92
• 93
• 94
• 95

2.字串函式

    1).計算字串長度

        例子:select Name,length(Name) from person;

    2).轉換為小寫

        例子:select Name,lower(Name) from person;

    3).轉換為大寫

        例子:select Name,upper(Name) from person;

    4).截去字串左側空格

        例子:select Name,ltrim(Name),ltrim('   abc   ') from person;

    5).截去字串左側空格

        例子:select Name,rtrim(Name),rtrim('   abc   ') from person;

    6).截去字串兩側空格

        例子:select Name,trim(Name),trim('   abc   ') from person;

    7).取子字串

        第二個引數是擷取的起始位置，第三個引數是長度
        例子:select substring('abcdef111',2,3);
             select Name,substring(Name,2,3) from person;

    8).計運算元串的位置

        例子:select Name,instr(Name,'m'),instr(Name,'ly') from person;

    9).從左側開始取字串

        例子:select Name,left(Name,3),left(Name,2) from person;

    10).從右側開始取字串

        例子:select Name,right(Name,3),right(Name,2) from person; 

    11).字串替換

        第一個引數是字串，第二個是要被替換的子串，第三個是想要替換的子串.
        這個函式第三個引數如果為空串的話相當於刪除操作了.

        例子:select Name,replace(Name,'i','e'),IDNumber, replace(IDNumber,'2345','abcd') from person; 

    12).得到字元的ASCII碼

        ASCII()函式用來得到一個字元的ASCII碼，有且只有一個引數，如果引數為一個字串則函式返回第一個字元 的ASCII碼
        例子:select ASCII('a'),ASCII('abc');

    13).得到ASCII碼對應的字元

        例子:select char(56),char(90),'a',char(ASCII('a'));

    14).發音匹配

        soundex函式用於計算一個字串的發音特徵值，這個特徵值為一個四個字元的字串，特徵值的第一個字元總是初始字串中的第一個字元，而其後則是一個三位數字的數值。
        例子:select soundex('jack'),soundex('jeck'),soundex('joke'),soundex('juke'),soundex('look'),soundex('jobe');

             select name,soundex(name) from person;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• 51
• 52
• 53
• 54
• 55
• 56
• 57
• 58
• 59
• 60
• 61
• 62
• 63
• 64
• 65

3.日期時間函式

    1).獲取當前日期時間

        例子:select now(),sysdate(),current_timestamp;

    2).獲取當前日期

        例子:select curdate(),current_date;  

    3).獲取當前時間

        例子:select curtime(),current_time;  

    4).日期增減

        函式:date_add(date,interval expr type);它的別名是adddate()

        例子:select BirthDay,date_add(BirthDay,interval 1 
             week) as w1,date_add(BirthDay,interval 2 month) as m2,date_add(BirthDay,interval 5 quarter) as q5 from person;

             select BirthDay,date_add(BirthDay,interval '3 2:10' DAY_MINUTE) as dm,date_add(BirthDay,interval 1-6 YEAR_MONTH) as ym from person;

    5).計算日期差

        例子:select RegDay,BirthDay,datediff(RegDay,BirthDay),datediff(BirthDay,RegDay) from person;

    6).計算一個日期是星期幾

        例子:select BirthDay,dayname(BirthDay),RegDay,dayname(RegDay) from person;

    7).取得日期的指定部分

        函式:date_format(date,format);

        例子:select BirthDay,date_format(BirthDay,'%y-%M %D %W') as bd,RegDay,date_format(RegDay,'%Y年%m月%e日') as rd from person;

    8).型別轉換

        函式:cast(expression as type)
             convert(expression,type)

        例子:select cast('-30' as signed) as sig,convert('36',unsigned integer) as usig, cast('2008-08-08' as date) as d,convert('08:09:10',time) as t;

    9).空值處理

        例子:select name,birthday,regday,coalesce(birthday,regday,'2008-08-08') as ImoortDay from person