建表语句如下:
create table person(IdNumber varchar(20),Name varchar(20),BirthDay datetime,RegDay datetime,Weight decimal(10,2));
插入数据如下:
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.数学函数
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