MySQL常用函数总结

文章目录

一、数值型函数
二、字符串函数
三、日期函数
四、流程控制函数

一、数值型函数

函数	功能
ABS	求绝对值
SQRT	求二次方根
POW	求n次方
MOD	求余数
CEIL	向上取整
FLOOR	向下取整
RAND	生成0-1的随机数
SIN	求正弦值
COS	求余弦值

mysql> SELECT ABS(-4),SQRT(4),POW(2,2),MOD(63,8),CEIL(2.5),FLOOR(2.5),RAND(),SIN(0.5*PI());
+---------+---------+----------+-----------+-----------+------------+---------------------+---------------+
| ABS(-4) | SQRT(4) | POW(2,2) | MOD(63,8) | CEIL(2.5) | FLOOR(2.5) | RAND()              | SIN(0.5*PI()) |
+---------+---------+----------+-----------+-----------+------------+---------------------+---------------+
|       4 |       2 |        4 |         7 |         3 |          2 | 0.31499378971289266 |             1 |
+---------+---------+----------+-----------+-----------+------------+---------------------+---------------+
1 row in set

二、字符串函数

函数	功能
LENGTH	返回字符串长度
CONCAT	合并字符串
INSERT	替换字符串
LOWER	字符串字母转小写
UPPER	字符串字母转大写
LEFT	从字符串左边取子串
RIGHT	从字符串右边取子串
TRIM	删除字符串左右两边的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串
REVERSE	字符串反转

mysql> SELECT LENGTH('hello world') AS length,
    -> CONCAT('hello','world') AS concat,
    -> INSERT('hello',1,2,'md'),
    -> LOWER('HELLO') AS lower,
    -> TRIM('  hello    ') AS trim,
    -> SUBSTRING('hello',1,2) as sub;
+--------+------------+--------------------------+-------+-------+-----+
| length | concat     | INSERT('hello',1,2,'md') | lower | trim  | sub |
+--------+------------+--------------------------+-------+-------+-----+
|     11 | helloworld | mdllo                    | hello | hello | he  |
+--------+------------+--------------------------+-------+-------+-----+
1 row in set

三、日期函数

函数	功能
CURDATE	返回当前系统日期
CURTIME	返回当前系统时间
NOW	返回当前系统日期和时间
MONTH	获取指定日期的月份
WEEK	获取指定日期是一年中的第几周
YEAR	获取年份，返回值范围是 1970〜2069
SUBTIME	时间减法运算，在原始时间上减去指定的时间
ADDTIME	时间加法运算，在原始时间上加上指定的时间
DATEDIFF	获取两个日期的间隔天数
WEEKDAY	返回日期对应的工作日索引

mysql> SELECT CURDATE(),CURTIME(),NOW();
+------------+-----------+---------------------+
| CURDATE()  | CURTIME() | NOW()               |
+------------+-----------+---------------------+
| 2019-10-22 | 10:35:11  | 2019-10-22 10:35:11 |
+------------+-----------+---------------------+
1 row in set

mysql> SELECT MONTH('2019-10-22'),YEAR(NOW()),WEEK('2019-10-22',1),WEEKDAY('2019-10-22');
+---------------------+-------------+----------------------+-----------------------+
| MONTH('2019-10-22') | YEAR(NOW()) | WEEK('2019-10-22',1) | WEEKDAY('2019-10-22') |
+---------------------+-------------+----------------------+-----------------------+
|                  10 |        2019 |                   43 |                     1 |
+---------------------+-------------+----------------------+-----------------------+
1 row in set

mysql> SELECT SUBTIME('2019-10-22 23:59:59','0:1:1') AS sub,
    -> ADDTIME('2019-10-22 23:59:59','0:1:1') AS addT,
    -> DATEDIFF('2019-10-20','2019-10-22') AS diff;
+---------------------+---------------------+------+
| sub                 | addT                | diff |
+---------------------+---------------------+------+
| 2019-10-22 23:58:58 | 2019-10-23 00:01:00 |   -2 |
+---------------------+---------------------+------+
1 row in set

四、流程控制函数

函数	功能
IF	判断是否正确
IFNULL(v1,v2);	如果 v1 不为 NULL，则 IFNULL 函数返回 v1; 否则返回 v2 的结果。
CASE	搜索语句

mysql> SELECT IF(1<2,1,0) c1,IF(1>5,'√','×') c2,IF(STRCMP('abc','ab'),'yes','no') c3;
+----+----+-----+
| c1 | c2 | c3  |
+----+----+-----+
|  1 | ×  | yes |
+----+----+-----+
1 row in set

mysql> SELECT IFNULL(5,8),IFNULL(NULL,'OK'),IFNULL(SQRT(-8),'FALSE'),SQRT(-8);
+-------------+-------------------+--------------------------+----------+
| IFNULL(5,8) | IFNULL(NULL,'OK') | IFNULL(SQRT(-8),'FALSE') | SQRT(-8) |
+-------------+-------------------+--------------------------+----------+
|           5 | OK                | FALSE                    | NULL     |
+-------------+-------------------+--------------------------+----------+
1 row in set

SELECT CASE WEEKDAY(NOW()) WHEN 0 THEN '星期一' WHEN 1 THEN '星期二' WHEN
2 THEN '星期三' WHEN 3 THEN '星期四' WHEN 4 THEN '星期五' WHEN 5 THEN '星期六'
ELSE '星期天' END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());
+---------+---------------------+----------------+----------------+
| COLUMN1 | NOW()               | WEEKDAY(NOW()) | DAYNAME(NOW()) |
+---------+---------------------+----------------+----------------+
| 星期二  | 2019-10-22 10:54:57 |              1 | Tuesday        |
+---------+---------------------+----------------+----------------+
1 row in set