1.绝对值函数abs(value)
MySQL> select abs(-120);
+-----------+
| abs(-120) |
+-----------+
| 120 |
+-----------+
1 row in set (0.00 sec)
2.地板函数floor(value)，获取小于value的最大整数
mysql> select floor(23.8);
+-------------+
| floor(23.8) |
+-------------+
| 23 |
+-------------+
1 row in set (0.00 sec)
3.天花板函数ceiling（value)，获取大于value的最小整数
mysql> select ceiling(23.8);
+---------------+
| ceiling(23.8) |
+---------------+
| 24 |
+---------------+
1 row in set (0.00 sec)
4.四舍五入函数round(value,position）,四舍五入到小数点后几位
mysql> select round(23.27); --默认是0
+--------------+
| round(23.27) |
+--------------+
| 23 |
+--------------+
1 row in set (0.00 sec)
mysql> select round(23.27,1);
+----------------+
| round(23.27,1) |
+----------------+
| 23.3 |
+----------------+
1 row in set (0.00 sec)
5.随机数函数rand()，获取0到1之间随机数
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.12718137365503365 |
+---------------------+
1 row in set (0.00 sec)
二、字符串函数
1.连接函数concat(str1,str2)
mysql> select concat('hello','world');
+-------------------------+
| concat('hello','world') |
+-------------------------+
| helloworld |
+-------------------------+
1 row in set (0.00 sec)
2.带分隔符的连接函数concat_ws(separator,str1,str2)
mysql> select concat_ws('#','hello','world');
+--------------------------------+
| concat_ws('#','hello','world') |
+--------------------------------+
| hello#world |
+--------------------------------+
1 row in set (0.00 sec)
3.字符串所占字节数length(str)
mysql> select length('helloworld我');
+------------------------+
| length('helloworld我') |
+------------------------+
| 12 |
+------------------------+
1 row in set (0.00 sec)
4.字符串的字符个数char_length(str)
mysql> select char_length('helloworld我');
+-----------------------------+
| char_length('helloworld我') |
+-----------------------------+
| 12 |
+-----------------------------+
1 row in set (0.00 sec)
5.字符串判断函数
a. IF(exp1,exp2,exp3)：若是exp1 为真，返回exp2；若是exp1为假，返回exp3
mysql> select if(2>1,'hello','world');
+-------------------------+
| if(2>1,'hello','world') |
+-------------------------+
| hello |
+-------------------------+
1 row in set (0.00 sec)
mysql> select if(null=1,'hello','world');
+----------------------------+
| if(null=1,'hello','world') |
+----------------------------+
| world |
+----------------------------+
1 row in set (0.08 sec)
b. IFNULL(exp1,exp2)：若是exp1 IS NOT NULL，返回exp1，否则返回exp2
mysql> select IFNULL('hello','world');
+-------------------------+
| IFNULL('hello','world') |
+-------------------------+
| hello |
+-------------------------+
1 row in set (0.00 sec)
mysql> select IFNULL(null,'world');
+----------------------+
| IFNULL(null,'world') |
+----------------------+
| world |
+----------------------+
1 row in set (0.06 sec)
c. NULLIF(exp1,exp2)：若是exp1=exp2，返回NULL，否则返回exp1
mysql> select NULLIF('hello','hello');
+-------------------------+
| NULLIF('hello','hello') |
+-------------------------+
| NULL |
+-------------------------+
1 row in set (0.00 sec)
mysql> select NULLIF('hello','hello11');
+---------------------------+
| NULLIF('hello','hello11') |
+---------------------------+
| hello |
+---------------------------+
1 row in set (0.00 sec)
6. 字符串转换函数
a. LTRIM(exp1)：去掉exp1中字符串开头(LEFT)的空格