MYSQL常用函数

1.字符串函数

函数	作用
CONCAT(S1,S2…Sn)	将S1，S2，Sn合并为一个字符串
INSERT(str,x,y,instr)	将字符串str从位置x开始y个字符长的子串替换为instr
LOWER(str)	变小写
UPPER(str)	变大写
LEFT(str,x)	返回字符串左边的x个字符
RIGHT(str,x)	返回字符串右边x个字符
LPAD(str,n,pad)	用pad对str最左边进行填充，直到长度为n个字符的长度
RPAD(str,n,pad)	用pad对str最右边进行填充，直到长度为n个字符的长度
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符str右侧的空格
REPEAT(str,x)	将str重复x次
REPLACE(str,a,b)	用字符串b替换str中所有的字符串a
STRCMP(s1,s2)	比较字符s1与s2的ASCII码。
TRIM(str)	去掉字符串行尾和行头的空格
SUBSTRING(str,x,y)	返回str的从x开始，长度为y的子串

用法示例：

CONCAT:
select concat('aa','bb','cc'),concat('aaa',null);

结果：aabbcc | NULL
注意：任何字符串与NULL进行连接结果都是NULL。

---

INSERT:
select insert('thisIsAStringinsert',5,3,'Repelace');

结果：thisReplaceStringinsert
注意：Repelace部分若为NULL，结果为NULL 。

---

LOWER(str)和UPPER(str) :
select LOWER("aaaBBBCCC"),UPPER("aaABBbccC");

结果：aaabbbccc|AAABBBCCC

---

LEFT(str,x)和RIGHT(STR,X):
select left("ThisIsATestString",4),right("ThisIsATestString",6);

结果:This | String
注意：若第二个参数（长度）为NULL,结果显示是一个空字符串，不是NULL

---

LPAD(str,n,pad)和RPAD(str,n,pad):
select lpad('str','20','fill'),rpad('str','20','fill');

结果：fillfillfillfillfstr | strfillfillfillfillf
用pad的值反复填充直到长度为n。

---

LTRIM(str)和RTRIM(str)与TRIM(str): 
select ltrim(' ltrim '),rtrim(' rtrim '),trim(' trim ');
select length(ltrim(' ltrim ')),length(rtrim(' rtrim ')),length(trim(' trim '));

结果: |ltrim | rtrim|trim|
6 | 6 | 4

---

REPEAT(str,x):
select repeat(‘repeat’,3);

---

REPLACE(str,a,b)：
select replace('thisIsATestString_Test','Test','Replace');

---

结果：thisIsAReplaceString_Replace

STRCMP(s1,s2): 
select strcmp('a','b'),strcmp('b','b'),strcmp('b','a');

结果：-1 | 0 | 1

---

SUBSTRING(str,x,y):
select substring('thisIsASubStringTest','8',9);

结果：SubString

---

2.数值函数

函数	作用
ABS(x)	求x的绝对值
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	等价于 x%y
LEFT(str,x)	返回字符串左边的x个字符
RAND()	返回0到1内的随机值
ROUND(x,y)	返回x保留y位小数，四舍五入
TRUNCATE(x,y)	返回x保留y位小数，截断

用法示例：

ABS(x)：
select ABS(-1),ABS(1);

结果：1|1

---

CEIL(x):
select CEIL(-5.5),CEIL(5.5);

结果：-5|6

---

FLOOR(x):
select FLOOR(-5.5),FLOOR(5.5);

结果：-6|5

---

MOD(x,y):
select MOD(5,4);

结果：1
注意：模数和被模数任何一个为NULL结果都为NULL

---

RAND():产生0到1内的随机数 select RAND();
可以配合ceill或者floor使用来取得一定范围的整数。

select ceill(100*rand());

---

ROUND(x,y):
如果x是整数，小数位会有y个0；
如果不写y，默认y为0，即四舍五入取整。

TRUNCATE(x,y):与ROUND类似，但不进行四舍五入操作。
区别:

select round(1.23456,4),truncate(1.23456,4);

结果：1.2346|1.2345

---

3.日期和时间函数

函数	作用
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date和UNIX时间戳
FROM_UNIXTIME	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时值
MINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type)
DATEDIFF(dateBegin,dateEnd)	返回dateBegin与dateEnd直接的天数

用法示例：

CURDATE()：返回当前日期值，只包含年月日

select CURDATE();

---

CURTIME():返回当前时间，只包含时分秒

select CURTIME();

---

NOW():返回值包括日期和时间，即年月日时分秒

select NOW();

---

UNIX_TIMESTAMP(date):返回日期date的UNIX时间戳

select UNIX_TIMESTAMP(now());

结果：1472266091

---

FROM_UNIXTIME:返回UNIXTIME时间戳的日期值。

select FROMUNIXTIME(1472266091)

结果：2016-08-27 10:48:11

---

WEEK(DATE)：返回所给日期是一年中的第几周，
YEAR(DATE) :返回年份。

select WEEK(now()),YEAR(now());

结果：34 | 2016

---

HOUR(time):返回所给时间的小时数。参数可以是time,也可以是datetime，但不能是date
MINUTE(time):返回所给时间的分钟数。参数可以是time,也可以是datetime，但不能是date

select HOUR(now()),MINUTE(now());

结果:10 | 55

---

MONTHNAME(date):返回date的英文月份名称

DATE_FORMAT(date,fmt):将date按fmt格式化(格式化参数很多，详情查表)。

select DATE_FORMAT(now(),'%Y年,%c月,%e日 %H:%i:%s');

结果:2016年8月27日 11:08:59

---

DATE_ADD(date,INTERVAL expr type):返回与所给日期date相差INTERVAL时间段的日期。
INTERVAL为间隔类型关键字。expr为时间长度，type为类型。

select now(),date_add(now(),INTERVAL 31 DAY);

结果：2016-08-27 11:21:23 | 2016-09-27 11:21:23

---

DATEDIFF(date1,date2):返回两个时期相差几天

select DATEDIFF(DATE_ADD(now(),INTERVAL 1 YEAR),now());

结果:365天

---

4.流程函数

函数	作用
IF(value,t,f)	如果value为真，返回t；否则返回f
IFNULL(value1,value2)	如果value1不为空返回value1,否则返回value2 等价于 value1!=NULL?value1:value2
CASE WHEN [value1] THEN [result1] … Else [default]	如果value1为真，返回result1,否则返回defalut
CASE [expr] WHEN [value1] THEN[result1] … ELSE[default] END	如果expr等于value1,返回result1,否则返回default

用法示例：

    select if(salary>2000,'high','low') from salary;

    select ifnull(salary,"zero") from salary;

    select case when salary<2000 then 'low' when salary=2000 then 'mid' when salary>2000 then 'high' else "zero" end from salary;

    select case salary when 1000 then 'low'  when 2000 then 'mid'  else 'high' end from salary;

---

5.其他常用函数

函数	作用
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	IP地址转数字
INET_NTOA(num)	数字转回IP
PASSWORD(str)	返回字符串str的加密版本
MD5(str)	返回字符串str的MD5值

INET_ATON将IP转换为数字后，可以便于比较IP的大小。

参考资料：《深入浅出MySQL》