概述
MySQL数据库提供了很多函数包括:
- 数学函数
- 字符串函数
- 日期和时间函数
- 条件判断函数
- 流程控制函数
- 系统信息函数
- 加密函数
- 格式化函数
数学函数
函数名称 | 函数说明 |
---|---|
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回大于x的最小整数值 |
FLOOR(x) | 返回小于等于该值的最大整数 |
MOD(x,y) | 返回x/y的模 |
RAND(x) | 返回0~1的随机值 |
ROUND(x,y) | 返回参数x的四舍五入的有y位的小数的值 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根 |
POW(x,y) | 返回x的y次方 |
mysql> select abs(-98);
+----------+
| abs(-98) |
+----------+
| 98 |
+----------+
1 row in set (0.00 sec)
mysql> select ceil(4.5);
+-----------+
| ceil(4.5) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
mysql> select ceil(-4.5);
+------------+
| ceil(-4.5) |
+------------+
| -4 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(4.5);
+------------+
| floor(4.5) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> select rand(100);
+---------------------+
| rand(100) |
+---------------------+
| 0.17353134804734155 |
+---------------------+
1 row in set (0.00 sec)
mysql> select truncate(123456789,4);
+-----------------------+
| truncate(123456789,4) |
+-----------------------+
| 123456789 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select truncate(12.3456789,4);
+------------------------+
| truncate(12.3456789,4) |
+------------------------+
| 12.3456 |
+------------------------+
1 row in set (0.00 sec)
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
字符串函数
函数名称 | 函数说明 |
---|---|
CONCAT(s1,s2,……,sn) | 连接s1,s2,……,sn为一个字符串 |
CONCAT_WS(s,s1,s2,……,sn) | 同CONCAT(s1,s2,……,sn)函数,但是每个字符串之间要加上s |
CHAR_LENGTH(s) | 返回字符串s的字符数 |
LENGTH(S) | 返回字符串s的字节数,和字符集有关 |
INSERT(str, index, len, instr) | 将字符串str从第index位置开始,len个字符长的子串替换为字符串instr |
UPPER(s)或UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s)或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(s,n) | 返回字符串s最左边的n个字符 |
RIGHT(s,n) | 返回字符串s最右边的n个字符 |
LPAD(str,len,pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str,len,pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM([BOTH] s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(【LEADING】s1 FROM s) | 去掉字符串 s 开始处的 s1 |
TRIM(【TRAILING】s1 FROM s) | 去掉字符串 s 结尾处的 s1 |
REPEAT(str, n) | 返回 str 重复 n 次的结果 |
REPLACE(str, a, b) | 用字符串 b 替换字符串 str 中所有出现的字符串 a |
STRCMP(s1,s2) | 比较字符串 s1,s2 |
SUBSTRING(s,index,len) | 返回从字符串 s 的 index 位置其 len 个字符 |
mysql>
mysql>
mysql> select concat('a','b','c','d','e');
+-----------------------------+
| concat('a','b','c','d','e') |
+-----------------------------+
| abcde |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select concat('_','a','b','c','d','e');
+---------------------------------+
| concat('_','a','b','c','d','e') |
+---------------------------------+
| _abcde |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select char_length('tianjin天津');
+------------------------------+
| char_length('tianjin天津') |
+------------------------------+
| 9 |
+------------------------------+
1 row in set (0.00 sec)
mysql> select length('tianjin天津');
+-------------------------+
| length('tianjin天津') |
+-------------------------+
| 13 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select insert('tianjinzhongguoworld',3,4,'test');
+-------------------------------------------+
| insert('tianjinzhongguoworld',3,4,'test') |
+-------------------------------------------+
| titestnzhongguoworld |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select upper('tianjiN');
+------------------+
| upper('tianjiN') |
+------------------+
| TIANJIN |
+------------------+
1 row in set (0.00 sec)
mysql> select ucase('tiANJiN');
+------------------+
| ucase('tiANJiN') |
+------------------+
| TIANJIN |
+------------------+
1 row in set (0.00 sec)
mysql> select lower('tiANJiN');
+------------------+
| lower('tiANJiN') |
+------------------+
| tianjin |
+------------------+
1 row in set (0.00 sec)
mysql> select left('tianjinzhongguo',5);
+---------------------------+
| left('tianjinzhongguo',5) |
+---------------------------+
| tianj |
+---------------------------+
1 row in set (0.00 sec)
mysql> select right('tianjinzhongguo',5);
+----------------------------+
| right('tianjinzhongguo',5) |
+----------------------------+
| ngguo |
+----------------------------+
1 row in set (0.00 sec)
mysql> select lpad('tianjin',10,'9');
+------------------------+
| lpad('tianjin',10,'9') |
+------------------------+
| 999tianjin |
+------------------------+
1 row in set (0.01 sec)
mysql> select rpad('tianjin',10,'9');
+------------------------+
| rpad('tianjin',10,'9') |
+------------------------+
| tianjin999 |
+------------------------+
1 row in set (0.00 sec)
mysql> select ltrim(' tianjin');
+---------------------+
| ltrim(' tianjin') |
+---------------------+
| tianjin |
+---------------------+
1 row in set (0.00 sec)
mysql> select rtrim(' tianjin ');
+------------------------+
| rtrim(' tianjin ') |
+------------------------+
| tianjin |
+------------------------+
1 row in set (0.01 sec)
mysql> select substring('tianjinzhonguo',3,3);
+---------------------------------+
| substring('tianjinzhonguo',3,3) |
+---------------------------------+
| anj |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
日期时间函数
函数名称 | 函数说明 |
---|---|
CURDATE()或CURRENT_DATE() | 返回当前日期 |
CURTIME()或CURRENT_TIME() | 返回当前时间 |
NOW() SYSDATE() CURRENT_TIMESTAMP() LOCALTIME() LOCALTIMESTAMP() | 返回当前系统日期时间 |
YEAR(date) MONTH(date) DAY(date) HOUR(time) MINUTE(time) SECOND(time) | 返回具体的时间值 |
WEEK(date) WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFWEEK(date) | 返回周几,注意:周日是1,周一是2,……,周六是7 |
WEEKDAY(date) | 返回周几,注意:周一是0,周二是1……,周日是6 |
DAYNAME(date) | 返回星期:MONDAY,TUESDAY…SUNDAY |
MONTHNAME(date) | 返回月份:January,。。。。。 |
DATEDIFF(date1,date2) TIMEDIFF(time1,time2) | 返回date1-date2的日期间隔 返回time1-time2的时间间隔 |
DATE_ADD(datetime,INTERVALE expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
DATE_FORMAT(datetime, fmt) | 按照字符串fmt格式化日期datetime值 |
STR_TO_DATE(str,fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
mysql>
mysql>
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-04-07 |
+------------+
1 row in set (0.00 sec)
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-04-07 |
+----------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 23:50:58 |
+-----------+
1 row in set (0.00 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 23:51:07 |
+----------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-04-07 23:51:16 |
+---------------------+
1 row in set (0.00 sec)
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2020-04-07 23:51:24 |
+---------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2020-04-07 23:51:38 |
+---------------------+
1 row in set (0.00 sec)
mysql> select localtime();
+---------------------+
| localtime() |
+---------------------+
| 2020-04-07 23:51:48 |
+---------------------+
1 row in set (0.00 sec)
mysql> select localtimestamp();
+---------------------+
| localtimestamp() |
+---------------------+
| 2020-04-07 23:51:58 |
+---------------------+
1 row in set (0.00 sec)
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2020 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 7 |
+------------+
1 row in set (0.01 sec)
mysql> select hour(localtime());
+-------------------+
| hour(localtime()) |
+-------------------+
| 23 |
+-------------------+
1 row in set (0.00 sec)
mysql> select minute(localtime());
+---------------------+
| minute(localtime()) |
+---------------------+
| 52 |
+---------------------+
1 row in set (0.01 sec)
mysql> select second(localtime());
+---------------------+
| second(localtime()) |
+---------------------+
| 8 |
+---------------------+
1 row in set (0.00 sec)
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 14 |
+-------------+
1 row in set (0.00 sec)
mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
mysql> select weekday(now());
+----------------+
| weekday(now()) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Tuesday |
+----------------+
1 row in set (0.00 sec)
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| April |
+------------------+
1 row in set (0.00 sec)
mysql> select datediff(now(),'2020-04-06 23:51:16');
+---------------------------------------+
| datediff(now(),'2020-04-06 23:51:16') |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff(now(),'2020-04-01 23:51:16');
+---------------------------------------+
| datediff(now(),'2020-04-01 23:51:16') |
+---------------------------------------+
| 6 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select date_format(now(),'%Y-%m-%d %H:%i %s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i %s') |
+----------------------------------------+
| 2020-04-07 23:58 57 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>
DATE_FORMAT(datetime,fmt)和STR_TO_DATE(str,fmt)格式符说明
格式符 | 说明 | 格式符 | 说明 |
---|---|---|---|
%Y | 4位数字表示年份 | %y | 2位数字表示年份 |
%M | 月名表示月份(January……) | %m | 2位数字表示月份(01,02,……) |
%b | 缩写的月名(Jan.,Feb.,……) | %c | 数字表示月份(1,2,3……) |
%D | 英文后缀表示月中的天数(1st,2nd,3rd,……) | %d | 两位数字表示月中的天数(01,02…) |
数字形式表示月中的天数(1,2,3,4,5…) | |||
%H | 两位数字表示小数,24 小时制(01,02…) | %h 和%I | 两位数字表示小时,12 小时制(01,02…) |
%k | 数字形式的小时,24 小时制(1,2,3) | %l | 数字形式表示小时,12 小时制(1,2,3,4…) |
%i | 两位数字表示分钟(00,01,02) | %S 和%s | 两位数字表示秒(00,01,02…) |
%W | 一周中的星期名称(Sunday…) | %a | 一周中的星期缩写(Sun.,Mon.,Tues.,…) |
%w | 以 数 字 表 示 周 中 的 天 数 | ||
(0=Sunday,1=Monday…) | |||
以 3 位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周,(1,2,3。。)其中Sunday 为周中第一天 | |
以数字表示年中的第几周,(1,2,3。。) | |||
其中 Monday 为周中第一天 | |||
%T | 24小时制 | %r | 12小时制 |
%p | AM或PM | %% | 表示% |