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