MySQL为我们提供了丰富的函数，所谓函数是对输入值返回一个特定运算结果的值，在数据管理和数据操作过程中不同场景下通常要使用不同的函数，例如：计算一个班级的平均成绩，统计电商平台的总金额等等，通过使用这些函数使得数据库的功能变的更加强大，MySQL提供了不同功能的函数，大致有以下几类：数学函数、字符串函数、日期时间函数、条件判断函数、系统信息函数、加密函数等其他函数。

数学函数

在本小节我们将学习数学函数。

获取绝对值

ABS(X):返回X的绝对值

SELECT ABS(-3.14);

运行SQL语句后我们可以发现结果输出了3.14，获取了-3.14的绝对值。

我们也可以在数据表中的字段上使用此函数。我们创建表测试该函数。

#创建表并添加测试数据
CREATE TABLE TEMP1(ID INT);
INSERT INTO TEMP1 (ID) VALUES (10);
INSERT INTO TEMP2 (ID) VALUES (-20);
#测试ABS函数
SELECT ABS(ID) FROM TEMP1;

三角函数和获取圆周率

PI():返回圆周率

SIN(X):返回X的正弦值，其中X为弧度制

COS(X):返回X的余弦值，其中X为弧度制

TAN(X):返回X的正切，其中X为弧度制

COT(X):返回X的余切，其中X为弧度制

SELECT PI(),SIN(PI()),COS(PI()),TAN(PI()),COT(PI());

平方根函数和求余函数

SQRT(X):返回非负数X的二次方根，如果X为负数则会返回NULL

MOD(X,Y):返回X被Y除后的余数，MOD()对于带小数部分的数值也起作用，返回除法运算后的余数

SELECT SQRT(4),SQRT(-4),MOD(5,3),MOD(5.3,3);

向上取整和向下取整

CEIL(X):返回不小于X(或者说大于X)的最小整数值，即向上取整

CEILING(X):作用同上

FLOOR(X):返回不大于X(或者说小于)的最大整数值，即向下取整

SELECT CEIL(3.14),CEIL(-3.14),FLOOR(3.14);

获取随机数

RAND(x)返回一个随机浮点值v，范围在0到1之间（0 ≤ v ≤ 1.0）。若已指定一个整数参数x，则它被用作种子值，用来产生重复序列。

select rand(),rand(),rand();

使用RAND()获取随机数时，可以发现每次获得的随机数都不相同

下面我们使用RAND(x)来获取随机数，从结果可以发现当参数相同时，产生的随机数是相同的。不同的参数产生的随机数不同。

select rand(10),rand(10),rand(10);

近似值函数

ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。

使用ROUND(x)对浮点数进行四舍五入取整

SELECT ROUND(-3.14),ROUND(2.56),ROUND(1.14);

从结果可以看出，四舍五入处理后，只保留了各个值的整数部分。

ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位，并且会四舍五入。

SELECT ROUND(1.28,1),ROUND(1.38,0),ROUND(223.3,-1),ROUND(-1118.56,-2);

TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。

SELECT TRUNCATE(1.31,1),TRUNCATE(1.99,1),TRUNCATE(1.99,0),TRUNCATE(19.99,-1);

TRUNCATE(1.31,1)和TRUNCATE(1.99,1)都保留小数点后1位数字，返回值分别为1.3和1.9；TRUNCATE(1.99,0)返回整数部分值1；TRUNCATE(19.99,-1)截去小数点左边第1位后面的值，并将整数部分的1位数字置0，结果为10。

需要注意的是：y为负数时，保留小数点左边相应位数直接保存为0，不会四舍五入。

幂运算函数

POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值。

SELECT POW(2,5);

弧度函数与角度函数

RADIANS(x)将参数x由角度转化为弧度。

SELECT RADIANS(90);

DEGREES(x)将参数x由弧度转化为角度。

SELECT DEGREES(3.14);3.141592653589793238462643

字符串函数

字符串函数主要用来处理数据库中的字符串数据。MySQL中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。本节将介绍各种字符串函数的功能和用法。

获取字符串字符数

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。LENGTH(str)返回值为字符串的字节长度，使用utf8（UNICODE的一种变长字符编码，又称万国码）编码字符集时，一个汉字是2字节，一个数字或字母算1字节。

SELECT CHAR_LENGTH('张'),CHAR_LENGTH('DATE');

拼接字符串

CONCAT(s1,s2,…)返回结果为连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。

SELECT CONCAT('Hello','World'),CONCAT('MY',NULL,'SQL');

在CONCAT_WS(x,s1,s2,…)中，CONCAT_WS代表CONCAT With Separator，是CONCAT()的特殊形式。第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

SELECT CONCAT_WS("-","HELLO","WORLD"),CONCAT_WS(NULL,"HELLO","WORLD"),CONCAT_WS(",","HELLO",NULL,"WORLD");

大小写字母转换

LOWER (str)或者LCASE (str)可以将字符串str中的字母字符全部转换成小写字母。

SELECT LOWER('HELLO');

使用UPPER函数或者UCASE函数将字符串中所有字母字符转换为大写

删除空格

LTRIM(s)返回字符串s，字符串左侧空格字符被删除。

SELECT CONCAT('(',LTRIM('   BOOK    '),')');

RTRIM(s)返回字符串s，字符串右侧空格字符被删除。

SELECT CONCAT('(',RTRIM('   BOOK    '),')');

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。

SELECT CONCAT('(',TRIM('  BOOK  '),')');

从结果中可以看出当未指定参数s时，删除了 两端的空格。

SELECT TRIM('123' FROM '1234512345123');

从结果可以看出函数删除了两端的重复字符串123，并没有删除中间的重复字符串

替换函数

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

SELECT REPLACE('xxx.bytecollege.cn','x','w');

REPLACE()函数将字符串中的"x"，全部替换成了"w";

截取字符串

SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度与len字符相同的子字符串，起始于位置n。也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。

SELECT SUBSTRING('HELLOWORLD',5),SUBSTRING('HELLOWORLD',2,5),SUBSTRING('HELLO',-3),SUBSTRING('HELLOWORLD',-3,2);

MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。

如果对len使用的是一个小于1的值，则结果始终为空字符串。

字符串逆序

REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相反。

SELECT REVERSE('HELLO');

日期和时间函数

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者TIMESTAMP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但会忽略日期部分，许多日期函数可以同时接受数字和字符串类型的两种参数，本节将介绍各种日期和时间函数的功能和用法。

获取当前日期

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回

获取当前时间

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE() 4个函数的作用相同，均返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS

获取月份

MONTH(date)函数返回date对应的月份，范围值为1~12。

SELECT MONTH('2020-02-13');

获取星期

WEEK(d)计算日期d是一年中的第几周，取值范围是0-52；按周日为第一天。

SELECT WEEK('2018-02-11');

WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是1~53，周一为第一天。

SELECT  WEEKOFYEAR('2018-02-11');

获取天数

DAYOFYEAR(d)函数返回d是一年中的第几天，范围是1~366。

SELECT DAYOFYEAR('2018-02-11');

DAYOFMONTH(d)函数返回是d时间月份中的第几天。

获取年份、小时、分钟和秒钟的函数

YEAR(date)返回date对应的年份，范围是1970~2069。

HOUR(time)返回time对应的小时，范围是0-23；

MINUTE(time)返回time对应的分钟数，范围是0~59。

SECOND(time)返回time对应的秒数，范围是0~59。

SELECT YEAR('2018-02-11 10:11:11'),MINUTE('2018-02-11 10:11:11'),SECOND('2018-02-11 10:11:11');

计算日期和时间的函数

计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()

在DATE_ADD(date,INTERVAL expr type)和DATE_SUB(date,INTERVAL exprtype)中，date是一个DATETIME或DATE值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。对于负值的时间间隔，expr可以以一个负号‘-’开头。type为关键词，指示了表达式被解释的方式。

将日期和时间格式化的函数

DATE_FORMAT(date,format)根据format指定的格式显示date值，主要format格式如表

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');

条件判断函数

条件判断函数也称为控制流程函数，根据满足的不同条件，执行相应的流程。MySQL中进行条件判断的函数有IF、IFNULL和CASE。本节将分别介绍各个函数的用法。

IF(expr,v1,v2)函数

IF(expr, v1, v2)：如果表达式expr是TRUE(expr <> 0 and expr <> NULL)，则返回值为v1；否则返回值为v2。IF()的返回值为数字值或字符串值

SELECT IF(1>2,2,3),IF(1<2,'YES','NO'),IF('TEST'='TEST1','YES','NO');

IFNULL(v1,v2)函数

IFNULL(v1,v2)：假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2。IFNULL()的返回值是数字或者字符串根据实际情况决定

SELECT IFNULL(1,10),IFNULL(NULL,10),IF(1/0,'WRONG');

IFNULL(1,2)虽然第二个值也不为空，但返回结果依然是第一个值；IFNULL(NULL,10)第一个值为空，因此返回10；“1/0”的结果为空，因此IFNULL(1/0, 'wrong')返回字符串“wrong”。

CASE函数

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]…[ELSE rn+1] END：如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1。

SELECT CASE 2 WHEN 1 THEN '一' WHEN 2 THEN '二' ELSE 'WRONG' END;

CASE后面的值为2，与第二条分支语句WHEN后面的值相等，因此返回结果为“二”。CASE　WHEN v1 THEN r1 [WHEN v2 THEN r2]… ELSE rn+1] END：某个vn值为TRUE时，返回对应位置THEN后面的结果；如果所有值都不为TRUE，则返回ELSE后的rn+1

系统信息函数

获取MySQL版本号、连接数和数据库名的函数

VERSION()返回指示MySQL服务器版本的字符串

CONNECTION_ID()返回MySQL服务器当前连接的次数，每个连接都有各自唯一的ID。

SELECT VERSION(),CONNECTION_ID();

获取用户名的函数

USER()、CURRENT_USER()、SYSTEM_USER()和SESSION_USER()这几个函数返回当前被MySQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的MySQL账户。一般情况下，这几个函数的返回值是相同的。

SELECT USER(),CURRENT_USER(),SYSTEM_USER();

获取字符串的字符集

CHARSET(str)返回字符串str自变量的字符集。

SELECT CHARSET('ABC'),CHARSET(VERSION());

获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。

CREATE TABLE STUDENT(
	ID INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)
INSERT INTO STUDENT (NAME) VALUES ('张三');
SELECT LAST_INSERT_ID();

查看已经插入的数据可以发现，最后一条插入的记录的Id字段值为2，使用LAST_INSERT_ID()查看最后自动生成的Id值：

加密函数

加密函数主要用来对数据进行加密和界面处理，以保证某些重要数据不被别人获取。这些函数在保证数据库安全时非常有用。本节将介绍各种加密函数的作用和使用方法。

加密函数MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL。

SELECT MD5('ADMIN');

加密函数SHA(str)

SHA(str)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。

SELECT SHA('ADMIN');