mysql常用函数
一、常用字符串函数
select concat('a','b','c'),concat('aaa',null); #连接a,b,c为一个字符串：'abc',与null进行连接的结果都是null
select insert("Hello world!",7,5,"World"); #将字符串从第7个字符开始以后的5个字符替换为“World”:'Hello World!'
select lower("HELLO"); #将所有字符变小写:'hello'
select upper("hello"); #将所有字符变大写:'HELLO'
select left('abcdefgh',4); #返回最左边的4个字符：'abcd'; 如果第二个参数为null，则不返回任何字符串
select right('abcdefgh',4); #返回最右边的4个字符：'efgh'; 如果第二个参数为null，则不返回任何字符串
select lpad('abc',12,'###'); #用字符串"###"对'abc'最左边进行填充，直到长度为12个字符长度：'#########abc'
select rpad('abc',12,'###'); #用字符串"###"对'abc'最右边进行填充，直到长度为12个字符长度：'abc#########'
select ltrim(" abc def "); #去掉字符串左侧的空格：'abc def '
select rtrim(" abc def "); #去掉字符串左侧的空格：' abc def'
select trim(" abc def "); #去掉字符串行尾和行头的空格：'abc def'
select repeat('abc',3); #返回重复字符串3次的结果:'abcabcabc'
select replace('abcabcabcabc','a','x'); #用字符串'x'替换原字符串中所有出现的字符串'a':'xbcxbcxbcxbc'
select strcmp('a','b'),strcmp('b','b'),strcmp('c','a'); #比较两个字符串，s1>s2返回：1；s1=s2返回：0；s1<s2返回：-1
select substring('Hello world!',2,4); #返回从字符串第2个字符位子开始，4个字符长度的子串：'ello'
二、常用数值函数
select abs(-0.8),abs(0.8); #返回绝对值的结果：0.8, 0.8
select ceil(-0.8),ceil(0.8); #返回大于数值的最小整数：0, 1
select floor(-0.8),floor(0.8); #返回小于数值的最大整数：-1, 0
select mod(15,10),mod(1,11),mod(null,10); #取模，即a%p，a除以p的余数：5, 1, null;任何一个数为null,结果都为null
select rand(),rand(); #返回0~1内的随机数：0.14930191370593562, 0.2753286429451114
select ceil(100*rand()),ceil(100*rand()); # 返回0~100内的任意随机整数
select round(1.1),round(1.1,2),round(1,2); #返回参数x的四舍五入的有y位小数的值：1, 1.10, 1.00
select round(1.357,2), truncate(1.357,2); #返回数字x截断位y位小数的结果，不进行四舍五入：1.36, 1.35
三、日期和时间函数
select curdate(); #返回当前日期，只包含年月日：'2018-03-07'
select curtime(); #返回当前时间，只包含时分秒：'10:41:40'
select now(); # 返回当前日期和时间：'2018-03-07 10:41:40'
select unix_timestamp(now()); #返回日期now()的unix时间戳：'1520390610'
select from_unixtime(1520390610); #返回unixtime时间戳的日期值（与unix_timestamp互逆）：'2018-03-07 10:43:30'
select week(now()),year(now()); #返回所给日期是一年的第几周和是哪一年：'9', '2018'
select hour(curtime()),minute(curtime()); #返回所给时间的小时和分钟：'10', '48'
select monthname(now()); #返回now()的英文月份名称：'March'
select date_format(now(),'%M,%D,%Y'); #按字符串'%M,%D,%Y'格式化日期：'March,7th,2018'
#date_add(date,interval expr type):返回与所给日期date相差interval时间段的日期
select now() current,date_add(now(),INTERVAL 31 day) after31days, date_add(now(), INTERVAL '1_2' year_month) after_oneyear_twomonth;
# current, after31days, after_oneyear_twomonth
# 2018-03-07 10:57:19 2018-04-07 10:57:19 2019-05-07 10:57:19
select now() current,date_add(now(),INTERVAL -31 day) after31days, date_add(now(), INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
# current, after31days, after_oneyear_twomonth
# 2018-03-07 10:55:57 2018-02-04 10:55:57 2017-01-07 10:55:57
select datediff('2018-08-08',now()); #计算两个日期之间相差的天数：'154'
四、流程函数
use test;
create table salary (userid int, salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
select * from salary;
userid, salary
'1', '1000.00'
'2', '2000.00'
'3', '3000.00'
'4', '4000.00'
'5', '5000.00'
'1', NULL
# if(value,t,f) 如果value真，返回t;否则返回f
select if(salary>2000,'high','low') from salary;
if(salary>2000,'high','low') #在2000元以上的属于高薪，用"high"表示；而2000以下的属于低薪，用"low"表示
'low'
'low'
'high'
'high'
'high'
'low'
#ifnull(value1,value2) 如果value1不为空，返回value1，否则返回value2
select ifnull(salary,0) from salary;
ifnull(salary,0) #把null值用0来替换，注：null是不能参与数值运算的
'1000.00'
'2000.00'
'3000.00'
'4000.00'
'5000.00'
'0.00'
# case when [value] then [result]...else [default] end 如果value真，返回result，否则返回default
select case when salary<=2000 then 'low' else 'high' end from salary;
case when salary<=2000 then 'low' else 'high' end
low
low
high
high
high
high
# case [expr] when [value1] then [result1]...else [default] end 如果expr等于value1,返回result1...否则返回default
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
high
high
high
high
low
mid
五、其他常用函数
select database(); # 返回当前数据库名:'test'
select version(); # 返回当前数据库版本:'5.6.33-log'
select user(); # 返回当前登录用户名:'root@192.168.100.60'
select inet_aton('192.168.100.21'); # 返回IP地址的数字表示:'3232261141'
select inet_ntoa('3232261142'); # 返回数字代表的ip地址:'192.168.100.22'
select password('abcdefg'); # 返回字符串str的加密版本:'*DCB7DF5FFC82C441503300FFF165257BC551A598'
select md5('abc'); # 返回字符串str的md5值:'900150983cd24fb0d6963f7d28e17f72'
create table iplist(ipaddr varchar(20));
insert into iplist value('192.168.1.1'),('192.168.10.1'),('192.168.1.10'),('192.168.2.1'),('192.168.1.100');
select * from iplist;
'192.168.1.1'
'192.168.10.1'
'192.168.1.10'
'192.168.2.1'
'192.168.1.100'
select * from iplist where ipaddr>='192.168.1.1' and ipaddr<='192.168.1.100';
192.168.1.1
192.168.1.10
192.168.1.100
select * from iplist where inet_aton(ipaddr)>=inet_aton('192.168.1.1') and inet_aton(ipaddr)<=inet_aton('192.168.1.100');
192.168.1.1
192.168.1.10
192.168.1.100
