MySQL函数
MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。例如，字符串连接函数可以很方便的将多个字符串连接在一起。
一、数学函数
ABS(X)：返回X的绝对值。
select ABS(-32);
MOD(N,M)或%：返回N被M除的余数。
select MOD(15,7); select 15 % 7;
CEILING(X)：返回不小于X的最小整数值。
select CEILING(1.23); select CEILING(-1.23);
ROUND(X) ：返回参数X的四舍五入的一个整数。
select ROUND(1.58); select ROUND(-1.58);
FLOOR(x) ：返回小于 x 的最大整数值。
SELECT FLOOR(5),FLOOR(5.66),FLOOR(-4),FLOOR(-4.66);
RAND() ：产生一个在 0 和 1 之间的随机数。
SELECT RAND(); SELECT RAND(1);
二、字符串函数
LENGTH(str)
返回字符串的字节长度，使用 uft8（UNICODE 的一种变长字符编码，又称万国码）编码字符集时，一个汉字是 3 个字节，一个数字或字母是一个字节。
SELECT LENGTH('name'),LENGTH('数据库');
CONCAT(sl，s2，...)
返回结果为连接参数产生的字符串，或许有一个或多个参数。
SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL);
INSERT(s1，x，len，s2)
替换字符串函数。返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2。
若 x 超过字符串长度，则返回值为原始字符串。假如 len 的长度大于其他字符串的长度，则从位置 x 开始替换。若任何一个参数为 NULL，则返回值为 NULL。
SELECT INSERT('Football',2,4,'Play') AS col1,INSERT('Football',-1,4,'Play') AS col2,INSERT('Football',3,20,'Play') AS col3;
LOWER(str)
将字符串中的字母转换为小写。
SELECT LOWER('BLUE'),LOWER('Blue');
UPPER(str)
将字符串中的字母转换为大写
SELECT UPPER('green'),UPPER('Green');
LEFT(s，n)
从左侧字截取符串，返回字符串左边的若干个字符。返回字符串 s 最左边的 n 个字符。
SELECT LEFT('MySQL',2);
RIGHT(s，n)
从右侧字截取符串，返回字符串右边的若干个字符。返回字符串 s 最右边的 n 个字符。
SELECT RIGHT('MySQL',3);
TRIM(s)
删除字符串左右两侧的空格
SELECT '[ mobile ]',CONCAT('[',TRIM(' mobile '),']');
REPLACE(s，s1，s2)
字符串替换函数，返回替换后的新字符串。使用字符串 s2 替换字符串 s 中所有的字符串 s1。
SELECT REPLACE('aaa.mysql.com','a','w');
SUBSTRING(s，n，len)
截取字符串，返回从指定位置开始的指定长度的字符串。
SELECT SUBSTRING('computer',3) AS col1,SUBSTRING('computer',3,4) AS col2,SUBSTRING('computer',-3) AS col3,SUBSTRING('computer',-5,3) AS col4;
REVERSE(s)
字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
SELECT REVERSE('hello');
三、日期和时间函数
CURDATE() 和 CURRENT_DATE()
返回当前系统的日期值。
将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回，具体格式根据函数用在字符串或数字语境中而定。
SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;
CURTIME() 和 CURRENT_TIME()
返回当前系统的时间值。
将当前时间以“HH：MM：SS”或“HHMMSS”格式返回，具体格式根据函数用在字符串或数字语境中而定。
SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0;
NOW() 和 SYSDATE()
返回当前系统的日期和时间值
返回当前日期和时间值，格式为“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数用在字符串或数字语境中而定。
SELECT NOW(),SYSDATE();
虽然在 MySQL 中 NOW() 和 SYSDATE() 都表示当前时间，但是 NOW() 取的是语句开始执行的时间，而 SYSDATE() 取的是语句执行过程中动态的实时时间。
UNIX_TIMESTAMP(date)
若无参数调用，返回一个无符号整数类型的 UNIX 时间戳（'1970-01-01 00:00:00'GMT之后的秒数）。
若用 date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00'GMT后的秒数的形式返回。
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();
FROM_UNIXTIME(date)
把 UNIX 时间戳转换为普通格式的日期时间值，与 UNIX_TIMESTAMP 函数互为反函数。
SELECT FROM_UNIXTIME(1150051270);
MONTH(date)
返回指定 date 对应的月份，范围为 1～12。
SELECT MONTH('2017-12-15');
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。
SELECT DAYOFWEEK('2017-12-15');
WEEK()
计算日期 date 是一年中的第几周。
WEEK(date,mode) 函数允许指定星期是否起始于周日或周一，以及返回值的范围是否为 0～52 或 1～53。
WEEK函数接受两个参数：
- date是要获取周数的日期。
- mode是一个可选参数，用于确定周数计算的逻辑。
如果忽略 mode 参数，默认情况下 WEEK 函数将使用 default_week_format 系统变量的值。要获取 default_week_format 变量的当前值，请使用 SHOW VARIABLES LIKE 'default_week_format';查看。
SELECT WEEK('2018-10-25',1);
DAYOFYEAR(d)
返回 d 是一年中的第几天，范围为 1～366。
SELECT DAYOFYEAR('2017-12-15');
DAYOFMONTH(d)
返回 d 是一个月中的第几天，范围为 1～31。
SELECT DAYOFMONTH('2017-12-15');
YEAR()
从指定日期值中来获取年份值。
YEAR(date)函数接受 date 参数，返回日期的年份。
SELECT YEAR(NOW());
HOUR(time)
返回time的小时，范围是0到23。
select HOUR('10:05:03');
MINUTE(time)
返回time的分钟，范围是0到59。
select MINUTE('98-02-03 10:05:03');
SECOND(time)
返回time的秒数，范围是0到59。
select SECOND('10:05:03');
QUARTER(date)
返回date一年中的季度，范围1到4。
select QUARTER('98-04-01');
TIME_TO_SEC(time)
将参数 time 转换为秒数的时间值，转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。
SELECT TIME_TO_SEC('15:15:15');
DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type)
用于执行日期的加运算。
DATE_ADD() 和 ADDDATE() 函数有两个参数：
- date 是 DATE 或 DATETIME 的起始值。
- INTERVAL expr type是要添加到起始日期值的间隔值。
SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1,DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS C2,ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3;
SELECT ADDDATE('2017-11-30 23:59:59', INTERVAL 1 SECOND) AS col1,ADDDATE('2017-11-30 23:59:59' ,INTERVAL '1:1' MINUTE_SECOND) AS col2;
DATE_SUB(date,INTERVAL expr type) 和 SUBDATE(date,INTERVAL expr type)
执行日期的减法运算。
DATE_SUB() 和 SUBDATE() 函数接受两个参数：
- date 是 DATE 或 DATETIME 的起始值。
- expr 是一个字符串，用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位，例如 DAY，HOUR 等
SELECT DATE_SUB('2018-01-02',INTERVAL 31 DAY) AS C1,SUBDATE('2018-01-02',INTERVAL 31 DAY) AS C2, DATE_SUB('2018-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS C3;
DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type) 函数在指定加减的时间段时也可以指定负值，加法的负值即返回原始时间之前的日期和时间，减法的负值即返回原始时间之后的日期和时间。
ADDTIME(time,expr)
时间的加法运算。添加 expr 到 time 并返回结果。其中：time 是一个时间或日期时间表达式，expr 是一个时间表达式。
SELECT ADDTIME('2018-10-31 23:59:59','0:1:1'),ADDTIME('10:30:59','5:10:37');
SUBTIME(time,expr)
时间的减法运算。其中：函数返回 time。expr 表示的值和格式 time 相同。time 是一个时间或日期时间表达式， expr 是一个时间。
SELECT SUBTIME('2018-10-31 23:59:59','0:1:1'),SUBTIME('10:30:59','5:12:37');
DATEDIFF(date1，date2)
返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。
SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1, DATEDIFF('2017-11-30','2017-12-15') AS col2;
DATE_FORMAT(date，format)
根据 format 指定的格式显示 date 值。
DATE_FORMAT() 函数接受两个参数：
- date：是要格式化的有效日期值
- format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。
主要的 format 格式如下表所示：
| 说明符 |
说明 |
|---|---|
| %a | 工作日的缩写名称（Sun~Sat) |
| %b | 月份的缩写名称（Jan…Dec) |
| %c | 月份，数字形式（0~12) |
| %D | 带有英语后缀的该月日期(0th, 2st, 3nd,…） |
| %d | 该月日期，数字形式（00~31) |
| %e | 该月日期，数字形式（(0~31) |
| %f | 微秒（000000 …999999) |
| %H | 以 2 位数表示 24 小时（00~23) |
| %h, %I | 以 2 位数表示 12 小时（01~12) |
| %i | 分钟，数字形式(00~59) |
| %j | —年中的天数（001~366) |
| %k | 以 24 小时（0~23)表示 |
| %l | 以12小时（1~12)表示 |
| %M | 月份名称（January~December) |
| %m | 月份，数字形式（00~12) |
| %p | 上午（AM) 或下午（PM) |
| %r | 时间，12小时制（小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM) |
| %S, %s | 以 2 位数形式表示秒（00~59) |
| %T | 时间，24 小时制（小时 (hh): 分钟 (mm): 秒数 (ss)) |
| %U | 周（00~53)，其中周日为每周的第一天 |
| %u | 周（00~53)，其中周一为每周的第一天 |
| %V | 周（01~53)，其中周日为每周的第一天，和％X同时使用 |
| %v | 周（01~53)，其中周一为每周的第一天，和%x同时使用 |
| %W | 星期标识（周日、周一、周二…周六） |
| %w | —周中的每日（0= 周日…6= 周六） |
| %X | 该周的年份，其中周日为每周的第一天，数字形式，4 位数，和％V同时使用 |
| %x | 该周的年份，其中周一为每周的第一天，数字形式，4位数，和%v同时使用 |
| %Y | 4 位数形式表示年份 |
| %y | 2 位数形式表示年份 |
| %% | %一个文字字符 |
SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1, DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2;
WEEKDAY(d)
返回 d 对应的工作日索引。0 表示周一，1 表示周二，……，6 表示周日。
SELECT WEEKDAY('2017-12-15');
四、控制流程函数
IF(expr,v1,v2)
根据表达式的某个条件或值结果来执行一组 SQL 语句。其中：表达式 expr 得到不同的结果，当 expr 为真是返回 v1 的值，否则返回 v2。
要在 MySQL 中形成一个表达式，可以结合文字，变量，运算符，甚至函数来组合。表达式可以返回 TRUE,FALSE 或 NULL，这三个值之一。
SELECT IF(1<2,1,0) c1,IF(1>5,'√','×') c2,IF(STRCMP('abc','ab'),'yes','no') c3;
IFNULL(v1,v2)
判断是否为空。它接受两个参数，如果不是 NULL，则返回第一个参数。 否则，IFNULL 函数返回第二个参数。两个参数可以是文字值或表达式。
SELECT IFNULL(5,8),IFNULL(NULL,'OK'),IFNULL(SQRT(-8),'FALSE'),SQRT(-8);
CASE
搜索语句。CASE 语句有两种形式：简单的和可搜索 CASE 语句。
#简单的 CASE 语句就是指使用简单 CASE 语句来检查表达式的值与一组唯一值的匹配。
简单的 CASE 语句的语法：
CASE <表达式> WHEN <值1> THEN <操作> WHEN <值2> THEN <操作> ... ELSE <操作> END CASE;
其中：<表达式> 可以是任何有效的表达式。我们将 <表达式> 的值与每个 WHEN 子句中的值进行比较，例如 <值1>，<值2> 等。如果 <表达式> 和 <值n> 的值相等，则执行相应的 WHEN 分支中的命令 <操作>。如果 WHEN 子句中的 <值n> 没有与 <表达式> 的值匹配，则 ELSE 子句中的命令将被执行。ELSE 子句是可选的。 如果省略 ELSE 子句，并且找不到匹配项，MySQL 将引发错误。
ELECT 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());
#可搜索的 CASE 语句
简单 CASE 语句仅允许将表达式的值与一组不同的值进行匹配。 为了执行更复杂的匹配，如范围，则可以使用可搜索 CASE 语句。 可搜索 CASE 语句等同于 IF 语句，但是它的构造更加可读。
可搜索CASE语句的语法：
CASE
WHEN <条件1> THEN <命令>
WHEN <条件2> THEN <命令>
...
ELSE commands
END CASE;
MySQL 分别计算 WHEN 子句中的每个条件，直到找到一个值为 TRUE 的条件，然后执行 THEN 子句中的相应 <命令>。如果没有一个条件为 TRUE，则执行 ELSE 子句中的 <命令>。如果不指定 ELSE 子句，并且没有一个条件为 TRUE，MySQL 将发出错误消息。MySQL 不允许在 THEN 或 ELSE 子句中使用空的命令。 如果您不想处理 ELSE 子句中的逻辑，同时又要防止 MySQL 引发错误，则可以在 ELSE 子句中放置一个空的 BEGIN END 块。
SELECT CASE WHEN WEEKDAY(NOW())=0 THEN '星期一' WHEN WEEKDAY(NOW())=1 THE N '星期二' WHEN WEEKDAY(NOW())=2 THEN '星期三' WHEN WEEKDAY(NOW())=3 THEN '星期 四' WHEN WEEKDAY(NOW())=4 THEN '星期五' WHEN WEEKDAY(NOW())=5 THEN '星期六' WHEN WEEKDAY(NOW())=6 THEN '星期天' END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW( ));
五、聚合函数
MAX()
查询指定列的最大值。
SELECT MAX(student_score)AS max_score FROM tb_students_score;
MAX() 函数不仅适用于查找数值类型，也可应用于字符类型。
SELECT MAX(student_name) AS max_name FROM tb_students_score;
注意：MAX() 函数还可以返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型的数据进行比较时，按照字符的 ASCII 码值大小进行比较，从 a～z，a 的 ASCII 码最小，z 的最大。在比较时，先比较第一个字符，如果相等，继续比较下一个字符，一直到两个字符不相等或者字符结束为止。例如，b 与 t 比较时，t 为最大值；bcd 与 bca 比较时，bcd 为最大值。
MIN()
返回查询列中的最小值。
SELECT MIN(student_score) AS min_score FROM tb_students_score;
MIN() 函数与 MAX() 函数类似，不仅适用于查找数值类型，也可应用于字符类型。
COUNT()
统计查询结果的行数。
- COUNT(*) 计算表中总的行数，无论某列有数值或者为空值。
- COUNT（字段名）计算指定列下总的行数，计算时将忽略空值的行
SELECT COUNT(student_name) AS students_number FROM tb_students_score;
在计算总数的时候对待 NULL 值的方式是，指定列的值为空的行被 COUNT() 函数忽略，但若不指定列，而在 COUNT() 函数中使用星号“*”，则所有记录都不忽略。
SUM()
求总和的函数，返回指定列值的总和。
SUM() 函数是如何工作的？
- 如果在没有返回匹配行 SELECT 语句中使用 SUM 函数，则 SUM 函数返回 NULL，而不是 0。
- DISTINCT 运算符允许计算集合中的不同值。
- SUM 函数忽略计算中的 NULL 值。
SELECT SUM(student_score) AS score_sum FROM tb_students_score;
SUM() 函数在计算时，忽略列值为 NULL 的行。
AVG()
求平均值，返回指定列数据的平均值。
SELECT AVG(student_score) AS score_avg FROM tb_students_score;
使用 AVG() 函数时，参数为要计算的列名称，若要得到多个列的平均值，则需要在每一列都使用 AVG() 函数。
六、系统信息函数
系统信息函数用来查询MySQL数据库的系统信息。例如，查询数据库的版本，查询数据库的当前用户等。本小节将详细讲解系统信息函数的作用和使用方法。
VERSION()
返回数据库的版本号
CONNECTION_ID()
返回服务器的连接数，也就是到现在为止MySQL服务的连接次数。
DATABASE()和SCHEMA()
返回当前数据库名。
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()
返回当前用户的名称。
CHARSET(str)
返回字符串str的字符集，一般情况这个字符集就是系统的默认字符集；COLLATION(str)函数返回字符串str的字符排列方式。
LAST_INSERT_ID()
返回最后生成的AUTO_INCREMENT值。
七、加密函数
加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，就应该通过加密方式来使这些数据变成看似乱码的数据。例如用户的密码，就应该经过加密。本小节将详细讲解加密函数的作用和使用方法。
PASSWORD(str)
对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。
MD5(str)
对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。
ENCODE(str,pswd_str)
ENCODE(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。
DECODE(crypt_str,pswd_str)
使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。
八、格式化函数
FORMAT(x,n)
将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。例如FORMAT(2.356,2)返回的结果将会是2.36；FORMAT(2.353,2)返回的结果将会是2.35。
IP地址与数字相互转换的函数
INET_ATON(IP)函数可以将IP地址转换为数字表示；INET_NTOA(n)函数可以将数字n转换成IP的形式。其中，INET_ATON(IP)函数中IP值需要加上引号。这两个函数互为反函数。
