mysql常用function
前言： 本文主要是参考mysql官方文档，并加上自己的测试用例，来加深自己对mysql函数的理解，也借此平台与大家分享，如有不妥和错误之处欢迎读者评论，我会及时改正，愿与大家一起学习进步。
概述： 所谓function(特指mysql函数)就是mysql服务器预先编写的函数，来供我们使用。这些函数可用在where，ORDER BY ,HAVING用于查询条件，也可用于select之后用于显示给我们....比较难概述呵呵。
注意：默认情况下，函数名称和后面的括号之间不应有空格 ，例如CONCAT('My', 'S', 'QL')。这有助于MySQL解析器区分函数调用和对恰好与函数同名的表或列的引用。是否可以使用和--sql-mode=IGNORE_SPACE有关。
零：1（TRUE）或 0（FALSE）
一：流程控制
1.1 CASE
语法一： CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END 语法二： CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
eg1(语法1)：SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; -> 'one'
eg2(语法2)：SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true'
1.2 IF(expr1,expr2,expr3) 如果表达式1成立，则输出2，否则输出2
eg1: mysql> SELECT IF(1>2,2,3); -> 3
eg2: select if(count(a)=22,'yes','no') from test where a='a';
1.3 IFNULL(expr1,expr2) NULLIF(expr1,expr2)
二：字符串比较like
语法：expr LIKE pat [ESCAPE 'escape_char'] //可选参数escape 是指定转义字符
eg：SELECT 'a' = 'a ', 'a' LIKE 'a '; // 注意空格 like和=的区别
like：统配符
-
%匹配0个或多个字符。 -
_只匹配一个字符。
eg2 :mysql> SELECT 'David!' LIKE 'David_'; -> 1
mysql> SELECT 'David!' LIKE '%D%v%'; -> 1:
转义字符：
eg3：mysql> SELECT 'David!' LIKE 'David\_'; -> 0
mysql> SELECT 'David_' LIKE 'David\_'; -> 1
指定转义字符‘’
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|'; -> 1
三：正则表达式 参考文献
^ ： 匹配开头
mysql> SELECT 'fofo' REGEXP '^fo$'; -> 0 mysql> SELECT 'fofo' REGEXP '^fo'; -> 1
$ ：匹配尾部
mysql> SELECT 'fofo' REGEXP '^fofo$'; -> 1
. ：匹配任意字符
SELECT 'fofo' REGEXP '^f.*$'; +-----------------------+ | 'fofo' REGEXP '^f.*$' | +-----------------------+ | 1 | +-----------------------+
a* ：匹配零个或多个a 字符(*修饰前一个符号)
mysql> SELECT 'Ban' REGEXP '^Ba*n'; -> 1
mysql> SELECT 'Baaan' REGEXP '^Ba*n'; -> 1
SELECT 'Bn' REGEXP '^B.*n$';
+----------------------+
| 'Bn' REGEXP '^B.*n$' |
+----------------------+
| 1 |
+----------------------+
a? ：匹配零个或一个a字符 （？修饰前一个符号）
mysql> SELECT 'Bn' REGEXP '^Ba?n'; -> 1 mysql> SELECT 'Ban' REGEXP '^Ba?n'; -> 1 mysql> SELECT 'Baan' REGEXP '^Ba?n'; -> 0
de|abc ：或
mysql> SELECT 'pi' REGEXP 'pi|apa'; -> 1 mysql> SELECT 'axe' REGEXP 'pi|apa'; -> 0 mysql> SELECT 'apa' REGEXP 'pi|apa'; -> 1 mysql> SELECT 'apa' REGEXP '^(pi|apa)$'; -> 1 mysql> SELECT 'pi' REGEXP '^(pi|apa)$'; -> 1 mysql> SELECT 'pix' REGEXP '^(pi|apa)$'; -> 0
(abc)* ：()里面是个整体 匹配0个或多个()中的内容
mysql> SELECT 'pi' REGEXP '^(pi)*$'; -> 1 mysql> SELECT 'pip' REGEXP '^(pi)*$'; -> 0 mysql> SELECT 'pipi' REGEXP '^(pi)*$'; -> 1
{1}, {2,3}： 和其他正则表达式规则一样
[a-dX], [^a-dX] ：
四：数据库加密AES(可实现加解密功能,)
AES_ENCRYPT()和AES_DECRYPT()函数使用官方的AES算法实现数据的加密和解密，该算法以前称为 “ Rijndael”，
加密：AES_ENCRYPT(str,key_str[,init_vector]) 通过秘钥key_str对str进行加密，并返回二进制字符串（crypt_str）
解密：AES_DECRYPT(crypt_str,key_str[,init_vector]) 通过秘钥key_str对加密后的字符串解密（默认使用128，MySQL 5.7.4以上才支持192和256）
eg:
CREATE TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARBINARY(100) NULL ,
`address` VARBINARY(200) NOT NULL ,
PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
步骤二：插入加密数据
mysql> insert into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT('Obama', 'usa2010'));
Query OK, 1 row affected (0.03 sec)
步骤三：查看源数据
mysql> select * from user;
+----+------------------+------------------+
| id | first_name | address |
+----+------------------+------------------+
| 1 | b▒▒▒H1bT▒▒&▒m▒ | b▒▒▒H1bT▒▒&▒m▒ |
+----+------------------+------------------+
1 row in set (0.00 sec)
步骤四：查看解密数据
mysql> SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user;
+------------------------------------+---------------------------------+
| AES_DECRYPT(first_name, 'usa2010') | AES_DECRYPT(address, 'usa2010') |
+------------------------------------+---------------------------------+
| Obama | Obama |
+------------------------------------+---------------------------------+
五：数据库加密MD5 全称是Message-Digest Algorithm 5（信息-摘要算法）故只是信息的摘要算法其并不算加密算法
MD5(str) ：计算'str'字符串的MD5值，并返回32位的字符串 （所有的MD5算法都是一样的）
eg : mysql> SELECT MD5('testing'); -> 'ae2b1fca515949e5d54fb22b8ed95575'
eg ：java:MD5
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
public class MD5_Test {
public static void main(String[] args) throws NoSuchAlgorithmException {
// 拿到一个MD5转换器（如果想要SHA1加密参数换成"SHA1"）
MessageDigest messageDigest = MessageDigest.getInstance("MD5");
// 输入的字符串转换成字节数组
byte[] inputByteArray = "testing".getBytes();
// inputByteArray是输入字符串转换得到的字节数组
messageDigest.update(inputByteArray);
// 转换并返回结果，也是字节数组，包含16个元素
byte[] resultByteArray = messageDigest.digest();
// 字符数组转换成字符串返回
System.out.println(byteArrayToHex(resultByteArray));
}
public static String byteArrayToHex(byte[] byteArray) {
// 首先初始化一个字符数组，用来存放每个16进制字符
char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' };
// new一个字符数组，这个就是用来组成结果字符串的（解释一下：一个byte是八位二进制，也就是2位十六进制字符）
char[] resultCharArray = new char[byteArray.length * 2];
// 遍历字节数组，通过位运算（位运算效率高），转换成字符放到字符数组中去
int index = 0;
for (byte b : byteArray) {
resultCharArray[index++] = hexDigits[b >>> 4 & 0xf];
resultCharArray[index++] = hexDigits[b & 0xf];
}
// 字符数组组合成字符串返回
return new String(resultCharArray);
}
}
六：日期和时间函数
表12。13日期和时间函数（摘录官方文档）
| 名称 | 描述 |
|---|---|
ADDDATE() |
将时间值（间隔）添加到日期值 |
ADDTIME() |
添加时间 |
CONVERT_TZ() |
从一个时区转换为另一个时区 |
CURDATE() |
返回当前日期 |
CURRENT_DATE()， CURRENT_DATE |
CURDATE（）的同义词 |
CURRENT_TIME()， CURRENT_TIME |
CURTIME（）的同义词 |
CURRENT_TIMESTAMP()， CURRENT_TIMESTAMP |
同义词NOW（） |
CURTIME() |
返回当前时间 |
DATE() |
提取日期或日期时间表达式的日期部分 |
DATE_ADD() |
将时间值（间隔）添加到日期值 |
DATE_FORMAT() |
格式化日期指定 |
DATE_SUB() |
从日期中减去时间值（间隔） |
DATEDIFF() |
减去两个日期 |
DAY() |
DAYOFMONTH（）的同义词 |
DAYNAME() |
返回工作日的名称 |
DAYOFMONTH() |
返回月中的某一天（0-31） |
DAYOFWEEK() |
返回参数的工作日索引 |
DAYOFYEAR() |
返回一年中的某一天（1-366） |
EXTRACT() |
提取部分日期 |
FROM_DAYS() |
将日期号码转换为日期 |
FROM_UNIXTIME() |
将Unix时间戳格式化为日期 |
GET_FORMAT() |
返回日期格式字符串 |
HOUR() |
提取小时 |
LAST_DAY |
返回参数的月份的最后一天 |
LOCALTIME()， LOCALTIME |
NOW（）的同义词 |
LOCALTIMESTAMP， LOCALTIMESTAMP() |
NOW（）的同义词 |
MAKEDATE() |
创建年份和年中的日期 |
MAKETIME() |
从小时，分钟，秒创建时间 |
MICROSECOND() |
从参数返回微秒 |
MINUTE() |
从论证中返回分钟 |
MONTH() |
从过去的日期返回月份 |
MONTHNAME() |
返回月份名称 |
NOW() |
返回当前日期和时间 |
PERIOD_ADD() |
将期间添加到年 - 月 |
PERIOD_DIFF() |
返回句点之间的月数 |
QUARTER() |
从日期参数返回季度 |
SEC_TO_TIME() |
将秒转换为'HH：MM：SS'格式 |
SECOND() |
返回秒（0-59） |
STR_TO_DATE() |
将字符串转换为日期 |
SUBDATE() |
使用三个参数调用时DATE_SUB（）的同义词 |
SUBTIME() |
减去时间 |
SYSDATE() |
返回函数执行的时间 |
TIME() |
提取传递的表达式的时间部分 |
TIME_FORMAT() |
格式化为时间 |
TIME_TO_SEC() |
返回转换为秒的参数 |
TIMEDIFF() |
减去时间 |
TIMESTAMP() |
使用单个参数，此函数返回日期或日期时间表达式; 有两个参数，参数的总和 |
TIMESTAMPADD() |
在datetime表达式中添加间隔 |
TIMESTAMPDIFF() |
从日期时间表达式中减去间隔 |
TO_DAYS() |
返回转换为days的日期参数 |
TO_SECONDS() |
返回自0年以来转换为秒的日期或日期时间参数 |
UNIX_TIMESTAMP() |
返回Unix时间戳 |
UTC_DATE() |
返回当前的UTC日期 |
UTC_TIME() |
返回当前的UTC时间 |
UTC_TIMESTAMP() |
返回当前的UTC日期和时间 |
WEEK() |
返回周数 |
WEEKDAY() |
返回工作日索引 |
WEEKOFYEAR() |
返回日期的日历周（1-53） |
YEAR() |
回归年份 |
YEARWEEK() |
返回年份和星期 |
eg1:TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 返回 datetime_expr2-datetime_expr1单位是unit,常用单位有 SECOND，MINUTE，HOUR，DAY，WEEK， MONTH，QUARTER或YEAR。
View Codeeg2:DATE_FORMAT(date,format) 根据date格式化字符串为指定形式
form参数
View Code+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT(curdate(), '%W %M %Y');
+------------------------------------+
| DATE_FORMAT(curdate(), '%W %M %Y') |
+------------------------------------+
| Tuesday February 2019 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT('2009-10-04', '%W %M %Y');
+---------------------------------------+
| DATE_FORMAT('2009-10-04', '%W %M %Y') |
+---------------------------------------+
| Sunday October 2009 |
+---------------------------------------+
1 row in set (0.00 sec)
七：其他字符串函数
View Codeeg1:CONCAT(str1,str2,.....) 连接多个字符串。
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT('My', NULL, 'QL');
+--------------------------+
| CONCAT('My', NULL, 'QL') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set (0.00 sec)
mysql> select concat(name,' 分数:',score) from students;
+-------------------------------+
| concat(name,' 分数:',score) |
+-------------------------------+
| 小白 分数:81 |
| 小兵 分数:55 |
| 小林 分数:85 |
| 小新 分数:91 |
| 小王 分数:89 |
| 小丽 分数:85 |
| 小路 分数:81 |
| NULL |
+-------------------------------+
8 rows in set (0.01 sec)
备注：如果有字符串为null时，则返回的数据也是null
eg2:LOWER(str) 返回字符串的小写形式
mysql> SELECT LOWER('QUADRATICALLY'); -> 'quadratically'
eg3:RIGHT(str,len) 返回字符串右边 可类比LEFT（str,len）
mysql> SELECT RIGHT('foobarbar', 4); -> 'rbar'
浙公网安备 33010602011771号