常用函数
字符串函数
1.concat （concatenate）拼接
mysql> select concat ('a','b'),concat ('aa',null);
+------------------+--------------------+
| concat ('a','b') | concat ('aa',null) |
+------------------+--------------------+
| ab | NULL |
+------------------+--------------------+
1 row in set (0.00 sec)
2.大小写
mysql> select lower('BEIjing2008'),upper('BEIjing2008');
+----------------------+----------------------+
| lower('BEIjing2008') | upper('BEIjing2008') |
+----------------------+----------------------+
| beijing2008 | BEIJING2008 |
+----------------------+----------------------+
1 row in set (0.00 sec)
3.去除空格
mysql> select ltrim(' |beijing'),rtrim('beijing| ');
+---------------------+---------------------+
| ltrim(' |beijing') | rtrim('beijing| ') |
+---------------------+---------------------+
| |beijing | beijing| |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select trim(' |beijing| ');
+------------------------+
| trim(' |beijing| ') |
+------------------------+
| |beijing| |
+------------------------+
1 row in set (0.00 sec)
数值函数
| 函数 | 功能 |
|---|---|
| ABS(x) | 绝对值 |
| CEIL(x) | '天花板'，向上取整数，比x大的整数 |
| FLOOR(x) | '地板'，向下取整，比x小的最大整数 |
| MOD(x,y) | 返回x/y的模 |
| RAND | (1,0)之间的随机小数 |
| ROUND(x,y) | x四舍五入，保留y位小数 |
| TRUNCATE(x,y) | x的小数截断为y位置 |
mysql> select abs(-0.18),abs(11);
+------------+---------+
| abs(-0.18) | abs(11) |
+------------+---------+
| 0.18 | 11 |
+------------+---------+
1 row in set (0.00 sec)
mysql> select ceil(0.18),floor(0.18);
+------------+-------------+
| ceil(0.18) | floor(0.18) |
+------------+-------------+
| 1 | 0 |
+------------+-------------+
1 row in set (0.00 sec)
mysql> select mod(5,9),mod(9,5);
+----------+----------+
| mod(5,9) | mod(9,5) |
+----------+----------+
| 5 | 4 |
+----------+----------+
1 row in set (0.01 sec)
mysql> select rand(),100*rand();
+---------------------+-------------------+
| rand() | 100*rand() |
+---------------------+-------------------+
| 0.14313656943211012 | 56.13369816554123 |
+---------------------+-------------------+
1 row in set (0.00 sec)
mysql> select round(1.123456,5),truncate(1.123456,5);
+-------------------+----------------------+
| round(1.123456,5) | truncate(1.123456,5) |
+-------------------+----------------------+
| 1.12346 | 1.12345 |
+-------------------+----------------------+
1 row in set (0.00 sec)
时间函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 年月日 |
| CURTIME() | 时分秒 |
| NOW() | 年月日时分秒 |
mysql> select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate() | curtime() | now() |
+------------+-----------+---------------------+
| 2019-07-10 | 03:11:37 | 2019-07-10 03:11:37 |
+------------+-----------+---------------------+
1 row in set (0.00 sec)
流程函数
| 函数 | 功能 |
|---|---|
| if(value,t,f) | value为真为t |
| ifnull(v1,v2) | v1不为空返回v1,否则返回v2 |
mysql> select if(true,1,2),if(false,1,2),ifnull(null,1);
+--------------+---------------+----------------+
| if(true,1,2) | if(false,1,2) | ifnull(null,1) |
+--------------+---------------+----------------+
| 1 | 2 | 1 |
+--------------+---------------+----------------+
1 row in set (0.00 sec)