mysql常用函数

常用函数

字符串函数

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)