【MySQL】数据库系统常用功能函数（二）

0x04、控制流程函数

概述

序号	函数	功能
1	CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] END	返回结果中， value=compare-value。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。
2	CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END	返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。
3	IF(expr1,expr2,expr3)	如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
4	STRCMP(expr1,expr2)	如果str1>str2返回1,str1=str2反回0，str1<str2返回-1)

1、CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END

--返回compare-value=value对应的result
SELECT CASE 11 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;

2、CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

--返回满足condition的result
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

[外链图片转存失败(img-PtPv0y0j-1568038824483)(C:\Users\CHAO\AppData\Roaming\Typora\typora-user-images!1567597294548.png)]

3、IF(expr1,expr2,expr3)

--IF(expr1,expr2,expr3)：如果expr1是TRUE，则IF()的返回值为expr2；否则返回值则为expr3.IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。
SELECT IF(1>2,2,3);
SELECT IF(1<2,'yes ','no');
SELECT IF(STRCMP('test','test1'),'no','yes');

[外链图片转存失败(img-N770V8qn-1568038824483)(C:\Users\CHAO\AppData\Roaming\Typora\typora-user-images!1567597727398.png)]

4、STRCMP(expr1,expr2)

--Strcmp(str1,str2):如果str1>str2返回1,str1=str2反回0，str1<str2返回-1)
select STRCMP('text', 'text2'); 
select STRCMP('text2', 'text'); 
select STRCMP('text', 'text');

0x05、系统信息函数

概述

序号	函数名	功能
1	VERSION()	返回数据库的版本号。
2	CONNECTION_ID()	返回服务器的连接数，也就是到现在为止MySQL服务的连接次数。
3	DATABASE()和SCHEMA()	返回当前数据库名。
4	USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER()	返回当前用户的名称。
5	CHARSET(str)	函数返回字符串str的字符集，一般情况这个字符集就是系统的默认字符集。
6	COLLATION(str)	函数返回字符串str的字符排列方式。
7	LAST_INSERT_ID()	返回最后生成的AUTO_INCREMENT值。

1、VERSION()

--VERSION()函数返回数据库的版本号
SELECT VERSION();

2、CONNECTION_ID()

--CONNECTION_ID()函数返回服务器的连接数，也就是到现在为止MySQL服务的连接次数
SELECT CONNECTION_ID();

3、DATABASE()和SCHEMA()

--DATABASE()和SCHEMA()返回当前数据库名
SELECT DATABASE();
SELECT SCHEMA();

4、USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER

--USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER()这几个函数可以返回当前用户的名称。
SELECT USER();
SELECT SYSTEM_USER();
SELECT SESSION_USER();
SELECT CURRENT_USER();
SELECT CURRENT_USER();

5、CHARSET(str)

--CHARSET(str)函数返回字符串str的字符集，一般情况这个字符集就是系统的默认字符集
SELECT CHARSET('ad');

6、COLLATION(str)

--COLLATION(str)函数返回字符换str的字符排列方式
SELECT COLLATION('ad');

7、LAST_INSERT_ID()

--LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值
SELECT LAST_INSERT_ID();

0x06、加密函数

概述

加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，就应该通过加密方式来使这些数据变成看似乱码的数据。例如用户的密码，就应该经过加密。

序号	函数名	功能
1	PASSWORD(str)	可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。
2	MD5(str)	可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。
3	ENCODE(str,pswd_str)	可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。
4	DECODE(crypt_str,pswd_str)	可以使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。

1、PASSWORD(str)

--PASSWORD(str)函数可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。下面使用PASSWORD(str)函数为字符串“abcd”加密。
SELECT PASSWORD('abcd');

2、MD5(str)

--MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。下面使用MD5(str)函数为字符串“abcd”加密。
SELECT MD5('abcd');

3、ENCODE(str,pswd_str)

--ENCODE(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。
SELECT ENCODE('CHAO','Glen');

[外链图片转存失败(img-OqqlihbT-1568038824493)(C:\Users\CHAO\AppData\Roaming\Typora\typora-user-images!1567599334018.png)]

4、DECODE(crypt_str,pswd_str)

--DECODE(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。下面使用DECODE(crypt_str,pswd_str)为ENCODE(str,pswd_str)加密的数据解密。
SELECT DECODE(ENCODE('CHAO','Glen'),'Glen');

0x07、其他函数

概述

序号	函数名	功能
1	FORMAT(x,n)	FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。例如FORMAT(2.356,2)返回的结果将会是2.36；FORMAT(2.353,2)返回的结果将会是2.35。
2	ASCII(s)	返回字符串s的第一个字符的ASCII码。
3	BIN(x)	返回x的二进制编码。
4	HEX(x)	返回x的十六进制编码。
5	OCT(x)	返回x的八进制编码。
6	CONV(x,f1,f2)	将x从f1进制数变成f2进制数。
7	INET_ATON(IP)	可以将IP地址转换为数字表示。（IP值需要加上引号）
8	INET_NTOA(n)	可以将数字n转换成IP的形式。
9	GET_LOCT(name,time)	定义一个名称为name、持续时间长度为time秒的锁。如果锁定成功，返回1；如果尝试超时，返回0；如果遇到错误，返回NULL。
10	RELEASE_LOCK(name)	解除名称为name的锁。如果解锁成功，返回1；如果尝试超时，返回0；如果解锁失败，返回NULL。
11	IS_FREE_LOCK(name)	判断是否使用名为name的锁。如果使用，返回0；否则，返回1。
12	BENCHMARK(count,expr)	将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。
13	CONVERT(s USING cs)	将字符串s的字符集变成cs
14	CAST(x AS type)和CONVERT(x,type)	两个函数将x变成type类型。这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER这些类型起作用。但两种方法只是改变了输出值的数据类型，并没有改变表中字段的类型。

1、FORMAT(x,n)

--FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。例如FORMAT(2.356,2)返回的结果将会是2.36；FORMAT(2.353,2)返回的结果将会是2.35。下面使用FORMAT(x,n)函数来讲235.3456和235.3454进行格式化，都保留到小数点后3位。
SELECT FORMAT(235.3456,3),FORMAT(235.3454,3);

2、ASCII(s)

--ASCII(s)返回字符串s的第一个字符的ASCII码
SELECT ASCII(chao);

3、BIN(x)

--BIN(x)返回x的二进制编码
SELECT BIN(10);

4、HEX(x)

--HEX(x)返回x的十六进制编码
SELECT HEX(10);

5、OCT(x)

--OCT(x)返回x的八进制编码
SELECT OCT(10);

6、CONV(x,f1,f2)

--CONV(x,f1,f2)将x从f1进制数变成f2进制数
SELECT CONV(10,10,2);

7、INET_ATON(IP)

--INET_ATON(IP)函数可以将IP地址转换为数字表示,其中，IP值需要加上引号。
SELECT INET_ATON('10.75.4.22');

8、INET_ATON()

--INET_NTOA(n)函数可以将数字n转换成IP的形式。
SELECT INET_ATON(200000);

9、GET_LOCT(name,time)

--GET_LOCT(name,time)函数定义一个名称为name、持续时间长度为time秒的锁。如果锁定成功，返回1；如果尝试超时，返回0；如果遇到错误，返回NULL。
SELECT GET_LOCT(chao,60);

10、RELEASE_LOCK(name)

--RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，返回1；如果尝试超时，返回0；如果解锁失败，返回NULL。

11、IS_FREE_LOCK(name)

--IS_FREE_LOCK(name)函数判断是否使用名为name的锁。如果使用，返回0；否则，返回1。
重复执行指定操作的函数。

12、BENCHMARK(count,expr)

--BENCHMARK(count,expr)函数将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。
SELECT BENCHMARK(1000000,100*100%2);

13、CONVERT(s USING cs)

--CONVERT(s USING cs)函数将字符串s的字符集变成cs
SELECT CHARSET('ABC'),CHARSET(CONVERT('ABC' USING gbk));

14、CAST(x AS type)和CONVERT(x,type)

CAST(x AS type)和CONVERT(x,type)这两个函数将x变成type类型。这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER这些类型起作用。但两种方法只是改变了输出值的数据类型，并没有改变表中字段的类型。

参考链接

MySQL常用内置函数

MySql常用函数大全讲解