mysql 常用函数，使用sql必然避免不了使用函数，函数往往能让你的工作事半功倍，常用的函数包括：字符串的处理，数值的运算，日期函数，流程控制。

字符串函数

字符串的拼接、替换、裁剪

这些函数是最常见的，务必牢记

1. CONCAT(s1,s2,…,sn) 拼接字符串
   下面例子将aa，bb，cc三个字符串拼接，另外任何字符串与NULL连接的结果都将是NULL

   mysql> select concat('aa','bb','cc'),concat('aa',null);
   +-------------------------+----------------------+
   | concat('aa','bb','cc')  | concat('aa',null)    |
   +-------------------------+----------------------+
   | aabbcc                  | NULL                 |
   +-------------------------+----------------------+
   1 row in set(0.00 sec)

2. INSERT、REPLACE 两个函数都是替换字符串
   下面例子将abcd字符串替换成abcc，通过两个方法

   mysql> select insert('abcd',3,2,'cc'),replace('abcd','d','c');
   +-------------------------+-------------------------+
   | insert('abcd',3,2,'cc') | replace('abcd','d','c') |
   +-------------------------+-------------------------+
   | abcc                    | abcc                    |
   +-------------------------+-------------------------+
   1 row in set (0.00 sec)

3. SUBSTRING、LEFT、RIGHT 三个函数都可以对字符串进行裁剪
   substring相对灵活很多，left和right函数比较局限，只能截取左边或右边的子串，看三个例子

   mysql> select substring('abcdefg',2,5), left('abcdefg',4),right('abcdefg',4);
   +--------------------------+-------------------+--------------------+
   | substring('abcdefg',2,5) | left('abcdefg',4) | right('abcdefg',4) |
   +--------------------------+-------------------+--------------------+
   | bcdef                    | abcd              | defg               |
   +--------------------------+-------------------+--------------------+
   1 row in set (0.00 sec)

字符串的字符处理，对比

1. LOWER、UPPER、LTRIM、RTRIM、TRIM 等函数可以对字符串进行处理
   LOWER、UPPER 用于大小写转换，LTRIM、RTRIM、TRIM用于对字符串空格处理。看例子

   mysql> select upper('abcd'),lower('ABCD'),ltrim(' $abcd'),rtrim('abcd$ '),trim(' $abcd$ ');
   +---------------+---------------+-----------------+-----------------+------------------+
   | upper('abcd') | lower('ABCD') | ltrim(' $abcd') | rtrim('abcd$ ') | trim(' $abcd$ ') |
   +---------------+---------------+-----------------+-----------------+------------------+
   | ABCD          | abcd          | $abcd           | abcd$           | $abcd$           |
   +---------------+---------------+-----------------+-----------------+------------------+
   1 row in set (0.00 sec)

2. LPAD、RPAD 填充字符串函数
   左右填充字符串，直到长度为n，看例子

   mysql> select lpad('defg',8,'abcd'),rpad('abcd',8,'defg');
   +-----------------------+-----------------------+
   | lpad('defg',8,'abcd') | rpad('abcd',8,'defg') |
   +-----------------------+-----------------------+
   | abcddefg              | abcddefg              |
   +-----------------------+-----------------------+
   1 row in set (0.00 sec)

3. REPEAT、STRCMP两个相对特殊一点的函数
   见例子，strcmp比较的是字符串的ASCII码大小，相等返回0，小返回-1，大返回1

   mysql> select repeat('abc',3),strcmp('a','a'),strcmp('aa','ab'),strcmp('b','a');
   +-----------------+-----------------+-------------------+-----------------+
   | repeat('abc',3) | strcmp('a','a') | strcmp('aa','ab') | strcmp('b','a') |
   +-----------------+-----------------+-------------------+-----------------+
   | abcabcabc       |               0 |                -1 |               1 |
   +-----------------+-----------------+-------------------+-----------------+
   1 row in set (0.00 sec)

数值函数

1. ABX(x) 绝对值

   mysql> select abs(-2),abs(2),abs(-5);
   +---------+--------+---------+
   | abs(-2) | abs(2) | abs(-5) |
   +---------+--------+---------+
   |       2 |      2 |       5 |
   +---------+--------+---------+
   1 row in set (0.00 sec)

2. CEIL(x)、FLOOR(x) 最小整数值

   mysql> select ceil(2.5),floor(2.5);
   +-----------+------------+
   | ceil(2.5) | floor(2.5) |
   +-----------+------------+
   |         3 |          2 |
   +-----------+------------+
   1 row in set (0.01 sec)

3. MOD(x,y) 取模

   mysql> select mod(9,2),mod(17,5);
   +----------+-----------+
   | mod(9,2) | mod(17,5) |
   +----------+-----------+
   |        1 |         2 |
   +----------+-----------+
   1 row in set (0.00 sec)

4. RAND() 0~1随机数
   可以通过组合方式，产生0~9的随机数，或者更大的随机数

   mysql> select rand(),floor(rand()*10);
   +--------------------+------------------+
   | rand()             | floor(rand()*10) |
   +--------------------+------------------+
   | 0.9055393793671759 |                7 |
   +--------------------+------------------+
   1 row in set (0.00 sec)

5. ROUND(x,y)、TRUNCATE(x,y) 四舍五入和截取

   mysql> select round(4.32354,3),round(4.32354,2),truncate(4.32354,3);
   +------------------+------------------+---------------------+
   | round(4.32354,3) | round(4.32354,2) | truncate(4.32354,3) |
   +------------------+------------------+---------------------+
   |            4.324 |             4.32 |               4.323 |
   +------------------+------------------+---------------------+
   1 row in set (0.00 sec)

日期函数

DATE_ADD(date,INTERVAL exper type)重点说明一下，
其中 INTERVAL 是间隔类型关键字，expr 是表达式， type是类型，
类型表如下

例如
```sql
mysql> select date_add(‘2013-01-18’, interval ‘1 2’ YEAR_MONTH);
+——————————————————————————-+
| date_add(‘2013-01-18’, interval ‘1 2’ YEAR_MONTH) |
+——————————————————————————-+
| 2014-03-18 |
+——————————————————————————-+

mysql> select date_add('2013-01-18', interval '1-2' YEAR_MONTH);
+----------------------------------------------------+
| date_add('2013-01-18', interval '1-2' YEAR_MONTH) |
+----------------------------------------------------+
| 2014-03-18                                         |
+----------------------------------------------------+

```

其他函数