说明：本文总结自：《MySQL 5.5从零开始学》第六章
MySQL中的函数包括：
数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。
函数：
表示对输入参数值返回一个具有特定关系的值。
一、数学函数
主要的数学函数有：绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、
对数函数、随机函数等。（PS:在有错误产生时，数学函数将会返回空值NULL）
1.1 绝对值函数ABS(x)
mysql> SELECT ABS(1),ABS(-1.1),ABS(-33); +--------+-----------+----------+ | ABS(1) | ABS(-1.1) | ABS(-33) | +--------+-----------+----------+ | 1 | 1.1 | 33 | +--------+-----------+----------+ 1 row in set (0.03 sec)
1.2返回圆周率函数PI()
mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
1.3平方根函数SQRT(x)
PS:SQRT(x)范围非负数x的二次方根
mysql> SELECT SQRT(9),SQRT(10),SQRT(-9);
+---------+--------------------+----------+
| SQRT(9) | SQRT(10) | SQRT(-9) |
+---------+--------------------+----------+
| 3 | 3.1622776601683795 | NULL |--负数没有平方根
+---------+--------------------+----------+
1 row in set (0.00 sec)
1.4求余函数MOD(x,y)
mysql> select MOD(5,2),MOD(7,4),MOD(50,8); +----------+----------+-----------+ | MOD(5,2) | MOD(7,4) | MOD(50,8) | +----------+----------+-----------+ | 1 | 3 | 2 | +----------+----------+-----------+ 1 row in set (0.00 sec)
1.5获取整数的函数CEIL(x)、CEILING(x)、FLOOR(x)
PS:CEIL(x)、CEILING(x)意义相同
mysql> SELECT CEIL(-2.26),CEIL(2.26),CEILING(-2.26),CEILING(2.26); +-------------+------------+----------------+---------------+ | CEIL(-2.26) | CEIL(2.26) | CEILING(-2.26) | CEILING(2.26) | +-------------+------------+----------------+---------------+ | -2 | 3 | -2 | 3 | +-------------+------------+----------------+---------------+ 1 row in set (0.00 sec)
mysql> SELECT FLOOR(-2.26),FLOOR(2.26); +--------------+-------------+ | FLOOR(-2.26) | FLOOR(2.26) | +--------------+-------------+ | -3 | 2 | +--------------+-------------+ 1 row in set (0.00 sec)
1.6获取随机数的函数RAND()和RAND(x)
PS:a.RAND(x)返回一个随机浮点值v，范围在0到1之间。若已制定一个整数参数x，则它被用作种子值，用来产生重复序列。
b.不带参数的RAND()每次产生的随机数值不同
c.当RAND()参数相同时，将产生相同的随机数，不同的x产生的随机数值不同
mysql> SELECT RAND(),RAND(),RAND();
+--------------------+--------------------+--------------------+
| RAND() | RAND() | RAND() |
+--------------------+--------------------+--------------------+
| 0.0917864060884215 | 0.6539400337766298 | 0.9943409813515293 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT RAND(5),RAND(5),RAND(6);
+---------------------+---------------------+--------------------+
| RAND(5) | RAND(5) | RAND(6) |
+---------------------+---------------------+--------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.6563190842571847 |
+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)
1.7四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)
PS:a.ROUND(x,y)当y值为负数时，保留的小数点左边的相应位数直接保存为0，不进行四舍五入
b.ROUND(x,y)函数在截取值的时候会四舍五入，而TRUNCATE(x,y)直接截取值，不进行四舍五入
mysql> SELECT ROUND(-2.34),ROUND(-2.56),ROUND(2.34),ROUND(2.56); +--------------+--------------+-------------+-------------+ | ROUND(-2.34) | ROUND(-2.56) | ROUND(2.34) | ROUND(2.56) | +--------------+--------------+-------------+-------------+ | -2 | -3 | 2 | 3 | +--------------+--------------+-------------+-------------+ 1 row in set (0.00 sec)
mysql> SELECT ROUND(-2.34,1),ROUND(-20.56,-1),ROUND(2.35,1),ROUND(25.56,-1);
+----------------+------------------+---------------+-----------------+
| ROUND(-2.34,1) | ROUND(-20.56,-1) | ROUND(2.35,1) | ROUND(25.56,-1) |
+----------------+------------------+---------------+-----------------+
| -2.3 | -20 | 2.4 | 30 |
+----------------+------------------+---------------+-----------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(-2.34,1),TRUNCATE(-20.56,-1),TRUNCATE(2.35,1),TRUNCATE(25.56,-1);
+-------------------+---------------------+------------------+--------------------+
| TRUNCATE(-2.34,1) | TRUNCATE(-20.56,-1) | TRUNCATE(2.35,1) | TRUNCATE(25.56,-1) |
+-------------------+---------------------+------------------+--------------------+
| -2.3 | -20 | 2.3 | 20 |
+-------------------+---------------------+------------------+--------------------+
1 row in set (0.00 sec)
1.8符号函数SIGN(x)
PS:SIGN(x)返回参数的符号，x的值为负、零或正时结果依次为-1、0或1.
mysql> SELECT SIGN(-33),SIGN(0),SIGN(33); +-----------+---------+----------+ | SIGN(-33) | SIGN(0) | SIGN(33) | +-----------+---------+----------+ | -1 | 0 | 1 | +-----------+---------+----------+ 1 row in set (0.00 sec)
1.9幂运算函数POW(x,y)、POWER(x,y)和EXP(x)
PS:a.POW(x,y)、POWER(x,y)意义相同
b.EXP(x)返回e的x乘方后的值（e的值是多少？答：e=2.71828？？？？）
mysql> SELECT POW(2,2),POW(2,-2),POWER(2,2),POW(2,-2); +----------+-----------+------------+-----------+ | POW(2,2) | POW(2,-2) | POWER(2,2) | POW(2,-2) | +----------+-----------+------------+-----------+ | 4 | 0.25 | 4 | 0.25 | +----------+-----------+------------+-----------+ 1 row in set (0.00 sec)
mysql> SELECT EXP(3),EXP(-3),EXP(0);
+--------------------+----------------------+--------+
| EXP(3) | EXP(-3) | EXP(0) |
+--------------------+----------------------+--------+
| 20.085536923187668 | 0.049787068367863944 | 1 |
+--------------------+----------------------+--------+
1 row in set (0.00 sec)
1.10对数运算函数LOG(x)和LOG10(x)
PS:a.LOG(x)返回x的自然对数，x相对于基数e的对数。（什么是对数？答：如果a的x次方等于N（a>0，且a不等于1），那么数x叫做以a为底N的对数（logarithm），记作x=logaN。其中，a叫做对数的底数，N叫做真数。）
b.对数定义域不能为负数，因此LOG(-5)返回结果为NULL
mysql> SELECT LOG(5),LOG(-5); +--------------------+---------+ | LOG(5) | LOG(-5) | +--------------------+---------+ | 1.6094379124341003 | NULL | +--------------------+---------+ 1 row in set (0.00 sec)
mysql> SELECT LOG10(2),LOG10(100),LOG10(-100);
+--------------------+------------+-------------+
| LOG10(2) | LOG10(100) | LOG10(-100) |
+--------------------+------------+-------------+
| 0.3010299956639812 | 2 | NULL |
+--------------------+------------+-------------+
1 row in set (0.00 sec)
1.11角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)
mysql> SELECT RADIANS(90),RADIANS(180); +--------------------+-------------------+ | RADIANS(90) | RADIANS(180) | +--------------------+-------------------+ | 1.5707963267948966 | 3.141592653589793 | +--------------------+-------------------+ 1 row in set (0.00 sec)
mysql> SELECT DEGREES(PI()),DEGREES(PI()/2);
+---------------+-----------------+
| DEGREES(PI()) | DEGREES(PI()/2) |
+---------------+-----------------+
| 180 | 90 |
+---------------+-----------------+
1 row in set (0.00 sec)
1.12正弦函数SIN(x)和反正弦函数ASIN(x)
PS:a.余弦函数COS(x),反余弦函数ACOS(x),正切函数TAN(x),反正切函数ATAN(x),余切函数COT(x)情况类似
b.ASIN(x)返回x的反正弦，若x不在-1到1的范围之内，则返回NULL。
mysql> SELECT SIN(1),ROUND(SIN(PI())); +--------------------+------------------+ | SIN(1) | ROUND(SIN(PI())) | +--------------------+------------------+ | 0.8414709848078965 | 0 | +--------------------+------------------+ 1 row in set (2.68 sec)
mysql> SELECT ASIN(0.8414709848078965),ASIN(3);
+--------------------------+---------+
| ASIN(0.8414709848078965) | ASIN(3) |
+--------------------------+---------+
| 1 | NULL |
+--------------------------+---------+
1 row in set (0.01 sec)
二、字符串函数
2.1计算字符串字符数的函数CHAR_LENGTH(str)和字符串长度的函数LENGTH(str)
mysql> SELECT CHAR_LENGTH('zhangmeng'),CHAR_LENGTH(1234);
+--------------------------+-------------------+
| CHAR_LENGTH('zhangmeng') | CHAR_LENGTH(1234) |
+--------------------------+-------------------+
| 9 | 4 |
+--------------------------+-------------------+
1 row in set (0.01 sec)
mysql> SELECT LENGTH('zhangmeng'),LENGTH(1234);
+---------------------+--------------+
| LENGTH('zhangmeng') | LENGTH(1234) |
+---------------------+--------------+
| 9 | 4 |
+---------------------+--------------+
1 row in set (0.00 sec)
2.2合并字符串函数CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)
PS:a.CONCAT(s1,s2,...)如何有任何一个参数为NULL，则返回值为NULL。
b.CONCAT_WS(x,s1,s2,...)，使用分隔符x将两个字符串连接成一个字符串，同时忽略NULL值
mysql> SELECT CONCAT('MySQL',5.6),CONCAT('MySQL',NULL,5.6);
+---------------------+--------------------------+
| CONCAT('MySQL',5.6) | CONCAT('MySQL',NULL,5.6) |
+---------------------+--------------------------+
| MySQL5.6 | NULL |
+---------------------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT_WS('-','MySQL',5.6),CONCAT_WS('*','MySQL',NULL,5.6);
+----------------------------+---------------------------------+
| CONCAT_WS('-','MySQL',5.6) | CONCAT_WS('*','MySQL',NULL,5.6) |
+----------------------------+---------------------------------+
| MySQL-5.6 | MySQL*5.6 |
+----------------------------+---------------------------------+
1 row in set (0.00 sec)
2.3替换字符串的函数INSERT(s1,x,len,s2)
mysql> SELECT INSERT('15271937914',4,4,'****');
+----------------------------------+
| INSERT('15271937914',4,4,'****') |
+----------------------------------+
| 152****7914 |
+----------------------------------+
1 row in set (0.00 sec)
2.4字母大写转小写函数LOWER(str)、LCASE(str)
PS:LOWER(str)、LCASE(str)意义相同
mysql> SELECT LOWER('SELECT'),LCASE('DELETE');
+-----------------+-----------------+
| LOWER('SELECT') | LCASE('DELETE') |
+-----------------+-----------------+
| select | delete |
+-----------------+-----------------+
1 row in set (0.00 sec)
2.5字母小写转大写函数UPPER(str)、UCASE(str)
PS:UPPER(str)、UCASE(str)意义相同
mysql> SELECT UPPER('select'),UCASE('Delete');
+-----------------+-----------------+
| UPPER('select') | UCASE('delete') |
+-----------------+-----------------+
| SELECT | DELETE |
+-----------------+-----------------+
1 row in set (0.00 sec)
2.6获取制定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)
mysql> SELECT LEFT('zhangmeng',5);
+---------------------+
| LEFT('zhangmeng',5) |
+---------------------+
| zhang |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT RIGHT('zhangmeng',4);
+----------------------+
| RIGHT('zhangmeng',4) |
+----------------------+
| meng |
+----------------------+
1 row in set (0.00 sec)
2.7填充字符串函数LPAD(s1,len,s2)和RPAD(s1,len,s2)
mysql> SELECT LPAD('hello',4,'*'),LPAD('hello',9,'*');
+---------------------+---------------------+
| LPAD('hello',4,'*') | LPAD('hello',9,'*') |
+---------------------+---------------------+
| hell | ****hello |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT RPAD('hello',4,'*'),RPAD('hello',9,'*');
+---------------------+---------------------+
| RPAD('hello',4,'*') | RPAD('hello',9,'*') |
+---------------------+---------------------+
| hell | hello**** |
+---------------------+---------------------+
1 row in set (0.00 sec)
2.8删除空格的函数LTRIM(s)、RTRIM(s)、TRIM(s)
mysql> SELECT '( ball )',CONCAT('(',LTRIM(' ball '),')');
+----------+---------------------------------+
| ( ball ) | CONCAT('(',LTRIM(' ball '),')') |
+----------+---------------------------------+
| ( ball ) | (ball ) |
+----------+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT '( ball )',CONCAT('(',RTRIM(' ball '),')');
+----------+---------------------------------+
| ( ball ) | CONCAT('(',RTRIM(' ball '),')') |
+----------+---------------------------------+
| ( ball ) | ( ball) |
+----------+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT '( ball )',CONCAT('(',TRIM(' ball '),')');
+----------+--------------------------------+
| ( ball ) | CONCAT('(',TRIM(' ball '),')') |
+----------+--------------------------------+
| ( ball ) | (ball) |
+----------+--------------------------------+
1 row in set (0.00 sec)
2.9删除指定字符串的函数TRIM(s1 FROM s)
mysql> SELECT TRIM('he' FROM 'hello');
+-------------------------+
| TRIM('he' FROM 'hello') |
+-------------------------+
| llo |
+-------------------------+
1 row in set (0.00 sec)
2.10重复生成字符串的函数REPEAT(s,n)
mysql> SELECT REPEAT('hello',2);
+-------------------+
| REPEAT('hello',2) |
+-------------------+
| hellohello |
+-------------------+
1 row in set (0.00 sec)
2.11空格函数SPACE(n)
mysql> SELECT CONCAT('(',SPACE(8),')');
+--------------------------+
| CONCAT('(',SPACE(8),')') |
+--------------------------+
| ( ) |
+--------------------------+
1 row in set (0.00 sec)
2.12替换函数REPLACE(s,s1,s2)
mysql> SELECT REPLACE('xxx.mysql.com','x','w');
+----------------------------------+
| REPLACE('xxx.mysql.com','x','w') |
+----------------------------------+
| www.mysql.com |
+----------------------------------+
1 row in set (0.00 sec)
2.13比较字符串的大小函数STRCMP(s1,s2)
mysql> SELECT STRCMP('test','test'),STRCMP('test','test1'),STRCMP('test1','test');
+-----------------------+------------------------+------------------------+
| STRCMP('test','test') | STRCMP('test','test1') | STRCMP('test1','test') |
+-----------------------+------------------------+------------------------+
| 0 | -1 | 1 |
+-----------------------+------------------------+------------------------+
1 row in set (0.00 sec)
2.14获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)
PS:SUBSTRING(s,n,len)和MID(s,n,len)意义相同
mysql> SELECT SUBSTRING('breakfast',5) AS col1,SUBSTRING('breakfast',5,3) AS col2,SUBSTRING('lunch',-3),SUBSTRING('lunch',-5,3);
+-------+------+-----------------------+-------------------------+
| col1 | col2 | SUBSTRING('lunch',-3) | SUBSTRING('lunch',-5,3) |
+-------+------+-----------------------+-------------------------+
| kfast | kfa | nch | lun |
+-------+------+-----------------------+-------------------------+
1 row in set (0.00 sec)
2.15匹配子串开始位置的函数LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)
mysql> SELECT LOCATE('ball','football'),POSITION('ball'in'football'),INSTR('football','ball');
+---------------------------+------------------------------+--------------------------+
| LOCATE('ball','football') | POSITION('ball'in'football') | INSTR('football','ball') |
+---------------------------+------------------------------+--------------------------+
| 5 | 5 | 5 |
+---------------------------+------------------------------+--------------------------+
1 row in set (0.00 sec)
2.16字符串逆序的函数REVERSE(s)
mysql> SELECT REVERSE('12345');
+------------------+
| REVERSE('12345') |
+------------------+
| 54321 |
+------------------+
1 row in set (0.00 sec)
2.17返回制定位置的字符串的函数ELT(n,str1,str2,...)
mysql> SELECT ELT(2,'1st','2st','3st'),ELT(4,'1st','2st','3st'); +--------------------------+--------------------------+ | ELT(2,'1st','2st','3st') | ELT(4,'1st','2st','3st') | +--------------------------+--------------------------+ | 2st | NULL | +--------------------------+--------------------------+ 1 row in set (0.00 sec)
2.18返回制定字符串位置的函数FIELD(s,s1,s2,..)和FIND_IN_SET(s1,s2)
PS:FIELD(s,s1,s2,..)和FIND_IN_SET(s1,s2)意义相同
mysql> SELECT FIELD('hi','hello','hi','hey');
+--------------------------------+
| FIELD('hi','hello','hi','hey') |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FIND_IN_SET('hi','hello,hi,hey');
+----------------------------------+
| FIND_IN_SET('hi','hello,hi,hey') |
+----------------------------------+
| 2 |
+----------------------------------+
1 row in set (0.00 sec)
三、日期和时间函数
3.1获取当前日期的函数CURDATE()、CURRENT_DATE()
PS:a.CURDATE()、CURRENT_DATE()意义相同
b.CURDATE()+0将当前日期值转换为数值型
mysql> SELECT CURDATE(),CURRENT_DATE(),CURDATE()+0; +------------+----------------+-------------+ | CURDATE() | CURRENT_DATE() | CURDATE()+0 | +------------+----------------+-------------+ | 2017-07-13 | 2017-07-13 | 20170713 | +------------+----------------+-------------+ 1 row in set (0.02 sec)
3.2获取当前时间和函数CURTIME()和CURRENT_TIME()
PS:CURTIME()和CURRENT_TIME()意义相同
mysql> SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0; +-----------+----------------+-------------+ | CURTIME() | CURRENT_TIME() | CURTIME()+0 | +-----------+----------------+-------------+ | 17:45:39 | 17:45:39 | 174539 | +-----------+----------------+-------------+ 1 row in set (0.00 sec)
3.3获取当前日期和时间的函数CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE()
PS:CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE()意义相同
mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE(); +---------------------+---------------------+---------------------+---------------------+ | CURRENT_TIMESTAMP() | LOCALTIME() | NOW() | SYSDATE() | +---------------------+---------------------+---------------------+---------------------+ | 2017-07-13 17:49:54 | 2017-07-13 17:49:54 | 2017-07-13 17:49:54 | 2017-07-13 17:49:54 | +---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
3.4返回UTC日期的函数UTC_DATE()和返回UTC时间的函数UTC_TIME()
PS:UTC:世界标准时间
mysql> SELECT UTC_DATE(),UTC_TIME(),UTC_DATE()+0; +------------+------------+--------------+ | UTC_DATE() | UTC_TIME() | UTC_DATE()+0 | +------------+------------+--------------+ | 2017-07-13 | 09:58:22 | 20170713 | +------------+------------+--------------+ 1 row in set (0.00 sec)
3.5获取月份的函数MONTH(date)和MONTHNAME(date)
mysql> SELECT MONTH('2017-07-13'),MONTHNAME('2017-07-13');
+---------------------+-------------------------+
| MONTH('2017-07-13') | MONTHNAME('2017-07-13') |
+---------------------+-------------------------+
| 7 | July |
+---------------------+-------------------------+
1 row in set (0.00 sec)
3.6获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)
PS:DAYOFWEEK(d)和WEEKDAY(d)函数都是返回指定日期在某一周内的位置
mysql> SELECT DAYNAME('2017-07-13'),DAYOFWEEK('2017-07-13'),WEEKDAY('2017-07-13');
+-----------------------+-------------------------+-----------------------+
| DAYNAME('2017-07-13') | DAYOFWEEK('2017-07-13') | WEEKDAY('2017-07-13') |
+-----------------------+-------------------------+-----------------------+
| Thursday | 5 | 3 |
+-----------------------+-------------------------+-----------------------+
3.7获取星期数的函数WEEK(d)
PS:WEEK(d)函数查询指定日期是一年中的第几周
mysql> SELECT WEEK('2017-07-13');
+--------------------+
| WEEK('2017-07-13') |
+--------------------+
| 28 |
+--------------------+
1 row in set (0.00 sec)
3.8获取天数的函数DAYOFYEAR(d)
PS:DAYOFYEAR(d)函数查询指定日期是一年中的第几天
mysql> SELECT DAYOFYEAR('2017-07-13');
+-------------------------+
| DAYOFYEAR('2017-07-13') |
+-------------------------+
| 194 |
+-------------------------+
1 row in set (0.00 sec)
3.9获取年份的函数year(d)
mysql> SELECT YEAR('2017-07-13');
+--------------------+
| YEAR('2017-07-13') |
+--------------------+
| 2017 |
+--------------------+
1 row in set (0.00 sec)
3.10计算日期的函数(加)DATE_ADD（date,interval expr type）,ADDDATE（date,interval expr type）
mysql> SELECT DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 HOUR),
DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 DAY),
DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 MONTH),
DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 YEAR) G;
*************************** 1. row ***************************
DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 HOUR): 2017-07-13 20:04:30
DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 DAY): 2017-07-14 19:04:30
DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 MONTH): 2017-08-13 19:04:30
DATE_ADD('2017-07-13 19:04:30',INTERVAL 1 YEAR): 2018-07-13 19:04:30
1 row in set (0.00 sec)
mysql> SELECT ADDDATE('2017-07-13 19:04:30',INTERVAL 1 HOUR),
ADDDATE('2017-07-13 19:04:30',INTERVAL 1 DAY),
ADDDATE('2017-07-13 19:04:30',INTERVAL 1 MONTH),
ADDDATE('2017-07-13 19:04:30',INTERVAL 1 YEAR) G;
*************************** 1. row ***************************
ADDDATE('2017-07-13 19:04:30',INTERVAL 1 HOUR): 2017-07-13 20:04:30
ADDDATE('2017-07-13 19:04:30',INTERVAL 1 DAY): 2017-07-14 19:04:30
ADDDATE('2017-07-13 19:04:30',INTERVAL 1 MONTH): 2017-08-13 19:04:30
ADDDATE('2017-07-13 19:04:30',INTERVAL 1 YEAR): 2018-07-13 19:04:30
1 row in set (0.01 sec)
3.11计算日期的函数(减)DATE_SUB（date,interval expr type）,SUBDATE（date,interval expr type）
mysql> SELECT DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 HOUR),
DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 DAY),
DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 MONTH),
DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 YEAR) G;
*************************** 1. row ***************************
DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 HOUR): 2017-07-13 18:04:30
DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 DAY): 2017-07-12 19:04:30
DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 MONTH): 2017-06-13 19:04:30
DATE_SUB('2017-07-13 19:04:30',INTERVAL 1 YEAR): 2016-07-13 19:04:30
1 row in set (0.00 sec)
mysql> SELECT SUBDATE('2017-07-13 19:04:30',INTERVAL 1 HOUR),
SUBDATE('2017-07-13 19:04:30',INTERVAL 1 DAY),
SUBDATE('2017-07-13 19:04:30',INTERVAL 1 MONTH),
SUBDATE('2017-07-13 19:04:30',INTERVAL 1 YEAR) G;
*************************** 1. row ***************************
SUBDATE('2017-07-13 19:04:30',INTERVAL 1 HOUR): 2017-07-13 18:04:30
SUBDATE('2017-07-13 19:04:30',INTERVAL 1 DAY): 2017-07-12 19:04:30
SUBDATE('2017-07-13 19:04:30',INTERVAL 1 MONTH): 2017-06-13 19:04:30
SUBDATE('2017-07-13 19:04:30',INTERVAL 1 YEAR): 2016-07-13 19:04:30
1 row in set (0.00 sec)
3.12计算两个日期之间的间隔天数DATEDIFF(date1,date2)
PS:DATEDIFF(date1,date2)函数返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或
date-and-time表达式，计算中只用到这些值的日期部分。
mysql> SELECT DATEDIFF('2017-07-16 12:51:30','2017-07-13 18:30:30');
+-------------------------------------------------------+
| DATEDIFF('2017-07-16 12:51:30','2017-07-13 18:30:30') |
+-------------------------------------------------------+
| 3 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
3.13格式化输出日期和时间的函数DATE_FORMAT()
mysql> SELECT DATE_FORMAT('2017-07-16 12:39:30','%W %M %p %Y' );
+----------------------------------------------------+
| DATE_FORMAT('2017-07-16 12:39:30','%W %M %p %Y' ) |
+----------------------------------------------------+
| Sunday July PM 2017 |
+----------------------------------------------------+
1 row in set (0.00 sec)
四、条件判断函数
4.1条件判断函数IF(expr,v1,v2)
PS:IF(expr,v1,v2)如果表达式expr是TRUE（expr<>0 and expr<>NULL）,则IF()的返回值为v1，否则返回值v2.
mysql> SELECT IF(1>2,3,4),IF(1<2,'yes','no'),IF(STRCMP('test','test1'),'no','yes');
+-------------+--------------------+---------------------------------------+
| IF(1>2,3,4) | IF(1<2,'yes','no') | IF(STRCMP('test','test1'),'no','yes') |
+-------------+--------------------+---------------------------------------+
| 4 | yes | no |
+-------------+--------------------+---------------------------------------+
1 row in set (0.00 sec)
4.2条件判断函数IFNULL()
PS:a.IFNULL()加入v1不为NULL，则IFNULL()的返回值为v1，否则返回值为v2。
b.1/0的结果为NULL
mysql> SELECT IFNULL(1,2),IFNULL(NULL,0),IFNULL(1/0,'wrong'); +-------------+----------------+---------------------+ | IFNULL(1,2) | IFNULL(NULL,0) | IFNULL(1/0,'wrong') | +-------------+----------------+---------------------+ | 1 | 0 | wrong | +-------------+----------------+---------------------+ 1 row in set (0.00 sec)
mysql> SELECT 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
4.3CASE函数
PS：CASE expr WHEN v1 THEN r1 WHEN V2 THEN r2 ELSE rn END
该函数表示，如果expr值等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不相等，则返回ELSE后面的rn
CASE WHEN v1 THEN r1 WHEN V2 THEN r2 ELSE rn END
该函数表示，某个vn值为TRUE时，返回对应位置THEN后面的结果，如果所有值都不为TRUE，则返回ELSE后面的rn
mysql> SELECT CASE 'zm' WHEN 'lly' THEN 'chihuo' WHEN 'jzj' THEN 'haok' WHEN 'zm' THEN 'zuibangde' ELSE 'fw' END; +----------------------------------------------------------------------------------------------------+ | CASE 'zm' WHEN 'lly' THEN 'chihuo' WHEN 'jzj' THEN 'haok' WHEN 'zm' THEN 'zuibangde' ELSE 'fw' END | +----------------------------------------------------------------------------------------------------+ | zuibangde | +----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END ;
+--------------------------------------------+
| CASE WHEN 1<0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| false |
+--------------------------------------------+
1 row in set (0.00 sec)
五、系统信息函数
5.1获取MySQL版本号的函数VERSION();
mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.6.36 | +-----------+ 1 row in set (0.00 sec)
5.2获取MySQL服务器当前连接的次数CONNECTION_ID()
PS:SHOW PROCESSLIST;SHOW FULL PROCESSLIST;
processlist 命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的
连接状态，帮助识别出有问题的查询语句等。
如果是root账号，能看到所有用户的当前连接，如果是其他普通账号，则只能看到自己占用的连接。
show processlist;只列出前100条，如果想全部列出可使用show full processlist命令。
mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec)
5.3查看当前使用的数据库DATABASE(),SCHEMA();
PS:DATABASE(),SCHEMA();意义相同
mysql> SELECT DATABASE(),SCHEMA(); +------------+----------+ | DATABASE() | SCHEMA() | +------------+----------+ | mysql | mysql | +------------+----------+ 1 row in set (0.00 sec)
5.4获取用户名的函数USER(),CURRENT_USER,CURRENT_USER(),SYSTEM_USER(),SESSION_USER()
mysql> SELECT USER(),CURRENT_USER(),SYSTEM_USER(); +----------------+----------------+----------------+ | USER() | CURRENT_USER() | SYSTEM_USER() | +----------------+----------------+----------------+ | root@localhost | root@localhost | root@localhost | +----------------+----------------+----------------+ 1 row in set (0.00 sec)
5.5获取字符串的字符集的函数CHARSET(str)
mysql> SELECT CHARSET('abc'),CHARSET(CONVERT('abc' USING latin1)),CHARSET(VERSION());
+----------------+--------------------------------------+--------------------+
| CHARSET('abc') | CHARSET(CONVERT('abc' USING latin1)) | CHARSET(VERSION()) |
+----------------+--------------------------------------+--------------------+
| utf8 | latin1 | utf8 |
+----------------+--------------------------------------+--------------------+
1 row in set (0.00 sec)
5.6返回字符串的字符排列方式的函数COLLATION(str)
mysql> SELECT COLLATION(_latin2 'abc'),COLLATION(CONVERT('abc' USING utf8));
+--------------------------+--------------------------------------+
| COLLATION(_latin2 'abc') | COLLATION(CONVERT('abc' USING utf8)) |
+--------------------------+--------------------------------------+
| latin2_general_ci | utf8_general_ci |
+--------------------------+--------------------------------------+
1 row in set (0.00 sec)
5.7获取最后一个自动生成的ID值的函数LAST_INSERT_ID()
mysql> CREATE TABLE worker(Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,Name VARCHAR(30)); Query OK, 0 rows affected (0.40 sec) mysql> INSERT INTO worker VALUES(NULL,'zm'); Query OK, 1 row affected (0.04 sec) mysql> INSERT INTO worker VALUES(NULL,'jzj'); Query OK, 1 row affected (0.31 sec) mysql> INSERT INTO worker VALUES(NULL,'lly'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO worker VALUES(NULL,'fw'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM worker; +----+------+ | Id | Name | +----+------+ | 1 | zm | | 2 | jzj | | 3 | lly | | 4 | fw | +----+------+ 4 rows in set (0.00 sec) mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 4 | +------------------+ 1 row in set (0.27 sec)
六、加密函数
6.1加密函数PASSWORD(str)
PS:MySQL将PASSWORD函数加密后的密码保存到用户权限表中
mysql> SELECT PASSWORD('helloworld');
+-------------------------------------------+
| PASSWORD('helloworld') |
+-------------------------------------------+
| *D35DB127DB631E6E27C6B75E8D376B04F64FAF83 |
+-------------------------------------------+
1 row in set (0.00 sec)
6.2加密函数MD5(str)
mysql> SELECT MD5('helloworld');
+----------------------------------+
| MD5('helloworld') |
+----------------------------------+
| fc5e038d38a57032085441e7fe7010b0 |
+----------------------------------+
1 row in set (0.00 sec)
6.3加密函数ENCODE(str,pswd_str)
mysql> SELECT ENCODE('select','drop'),LENGTH(ENCODE('select','drop'));
+-------------------------+---------------------------------+
| ENCODE('select','drop') | LENGTH(ENCODE('select','drop')) |
+-------------------------+---------------------------------+
| ̒잠 | 6 |
+-------------------------+---------------------------------+
1 row in set (0.00 sec)
6.4解密函数DECODE(crypt_tr,pswd_str)
mysql> select decode(encode('select','drop'),'drop');
+----------------------------------------+
| decode(encode('select','drop'),'drop') |
+----------------------------------------+
| select |
+----------------------------------------+
1 row in set (0.00 sec)
七、其他函数
7.1格式化函数FORMAT(x,n)
mysql> SELECT FORMAT(123.123,2),FORMAT(1234.1,3),FORMAT(123,0); +-------------------+------------------+---------------+ | FORMAT(123.123,2) | FORMAT(1234.1,3) | FORMAT(123,0) | +-------------------+------------------+---------------+ | 123.12 | 1,234.100 | 123 | +-------------------+------------------+---------------+ 1 row in set (0.00 sec)
7.2不同进制的数字进行转换的函数CONV(N,from_base,to_base)
mysql> SELECT CONV('A',16,2),CONV(10,10,2),CONV(10,10,8),CONV(10,10,16);
+----------------+---------------+---------------+----------------+
| CONV('A',16,2) | CONV(10,10,2) | CONV(10,10,8) | CONV(10,10,16) |
+----------------+---------------+---------------+----------------+
| 1010 | 1010 | 12 | A |
+----------------+---------------+---------------+----------------+
1 row in set (0.00 sec)
7.3IP地址转换为数字的函数INET_ATON(expr)
mysql> SELECT INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+
1 row in set (0.27 sec)
7.3将数值网络地址转换成字符串网络地址的函数INET_NTOA()
mysql> SELECT INET_NTOA(3232235777); +-----------------------+ | INET_NTOA(3232235777) | +-----------------------+ | 192.168.1.1 | +-----------------------+ 1 row in set (0.00 sec)
7.4改变数据类型的函数CAST(x, AS type) 和CONVERT(x,type)
mysql> SELECT CAST(100 AS CHAR(2)),CONVERT('2017-07-16 22:11:30' ,TIME);
+----------------------+--------------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2017-07-16 22:11:30' ,TIME) |
+----------------------+--------------------------------------+
| 10 | 22:11:30 |
+----------------------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)
7.5改变字符集的函数CONVERT(...USING...)
mysql> SELECT CHARSET('string'),CHARSET(CONVERT('string' USING latin1));
+-------------------+-----------------------------------------+
| CHARSET('string') | CHARSET(CONVERT('string' USING latin1)) |
+-------------------+-----------------------------------------+
| utf8 | latin1 |
+-------------------+-----------------------------------------+
1 row in set (0.00 sec)
7.6重复执行指定操作的函数BENCHMARK(count,expr)
mysql> SELECT PASSWORD('helloworld');
+-------------------------------------------+
| PASSWORD('helloworld') |
+-------------------------------------------+
| *D35DB127DB631E6E27C6B75E8D376B04F64FAF83 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT BENCHMARK(500000,PASSWORD('helloworld'));
+------------------------------------------+
| BENCHMARK(500000,PASSWORD('helloworld')) |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row in set (0.08 sec)