MySql函数

@[MySql函数]

MySQL 函数

本博客是为了记录和测试MySQL中函数的使用,并且经常更新,方便以后查询使用,如有问题,欢迎指正!!

版权声明：本文为博主原创文章，欢迎转载，转载请注明作者、原文超链接 ，博主地址：https://blog.csdn.net/weixin_43867075/article/details/88755951

MySQL函数，是一种控制流程函数，属于数据库用语言。
接下来,将从以下几个方面,分别介绍和讲解每个函数的用法

表格支持点击跳转!!!

字符串函数	时间函数	流程控制函数
数学函数	聚合函数	加密函数
信息函数	搜索函数	其他函数

1.字符串函数

ASCII()：返回值为字符串 str 的最左字符的数值。假如 str 为空字符串，则返回值为 0 。假如 str 为 NULL ，则返回值为 NULL 。 ASCII() 用于带有从 0 到 255 的数值的字符

SELECT ASCII('A');			//结果65
SELECT ASCII('a');				//结果97
SELECT ASCII('');				//结果0
SELECT ASCII(null);			//结果null

CONV(N, from_base, to_base)
不同数基间转换数字。 返回值为数字的 N 字符串表示，由 from_base 基转化为 to_base 基。如有任意一个参数为 NULL ，则返回值为 NULL。
自变量 N 被理解为一个整数，但是可以被指定为一个整数或字符串。最小基数为 2 ，而最大基数则为 36 。
If to_base 是一个负数，则 N 被看作一个带符号数。否则， N 被看作无符号数。

SELECT CONV(15,10,2);			//结果1111
SELECT CONV(15,10,8);			//结果17
SELECT CONV(15,10,16);			//结果 F
SELECT CONV(1111,2,8);			//结果17
SELECT CONV(1111,2,10);		//结果15
SELECT CONV(1111,2,16);		//结果F
注:只有当to_base为负数,N才会被理解为一个带符号的数
SELECT CONV(3,10,-2);			//结果11
SELECT CONV(-7,10,-2);			//结果-111

BIN(N)：返回值为 N 的二进制值的字符串表示,，其中 N 为一个 longlong (BIGINT) 数字。这等同于 CONV(N ,10,2) 。 假如 N 为 NULL ，则返回值为 NULL 。

SELECT BIN(15);			//结果1111
SELECT BIN(0);				//结果0
SELECT BIN(null);			//结果null

CONCAT(str1 ,str2 ,…)
1.返回结果为连接参数产生的字符串。
2.如有任何一个参数为 NULL ，则返回值为 NULL 。
3. 只有所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

SELECT CONCAT('Good',' Morning',' !');			//Good Morning !
SELECT CONCAT('Nice',' to',' meet',' you');			//Nice to meet you
SELECT CONCAT('大','家','好');				//大家好
SELECT CONCAT('大',null,'家','好');				//null

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

SELECT CONCAT_WS('-','1999','9','9');				//1999-9-9
SELECT CONCAT_WS('-','1999','9',null);				//1999-9
SELECT CONCAT_WS(null,'1999','9','9');				//null

LENGTH(str)
返回值为字符串 str 的长度，单位为字节。一个多字节字符算作多字节。这意味着 对于一个包含 5 个 3 字节字符的字符串， LENGTH() 的返回值为 15, 而 CHAR_LENGTH() 的返回值则为5 。

SELECT LENGTH('12345');			//5
SELECT LENGTH('你好陌生人');			//15(本人编码格式utf-8)
SELECT LENGTH('大家好');				//9
SELECT LENGTH('');				//0
SELECT LENGTH(null);				//null

LOCATE(substr ,str )
第一个语法返回字符串 str 中子字符串substr 的第一个出现位置。第二个语法返回字符串 str 中子字符串substr 的第一个出现位置, 起始位置在pos 。如若substr 不在str 中，则返回值为0 。
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。这和LOCATE() 的双参数形式相同，参数的顺序被颠倒。

SELECT LOCATE('de','abcdefg');			//4
SELECT LOCATE('dd','abcdefg');			//0
SELECT INSTR('abcdefg','de');				//4
SELECT INSTR('abcdefg','dd');				//0

注:经测试 任何一个地方为null,则结果为null
SELECT LOCATE('de',null);
SELECT LOCATE(null,'abcdefg');
SELECT INSTR(null,'de');
SELECT INSTR('abcdefg',null);

LEFT(str,number):
返回从字符串str 开始的number最左字符
RIGHT(str ,number )
从字符串str 开始，返回最右number字符。

SELECT LEFT('123456789',4);			//1234
SELECT RIGHT('123456789',4);			//6789
SELECT RIGHT(null,4);				//null

SUBSTRING(str ,pos )
SUBSTRING(str FROM pos )
SUBSTRING(str ,pos ,len )
SUBSTRING(str FROM pos FOR len )

不带有len 参数的格式从字符串str 返回一个子字符串，起始于位置 pos 。
使用 FROM 的格式为标准 SQL 语法。也可能对pos 使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。
带有len 参数的格式从字符串str 返回一个长度是len 字符长度的子字符串，起始于位置 pos 。

SELECT SUBSTRING('abcdefg',4);				//defg
SELECT SUBSTRING('abcdefg' FROM 4);			//defg
SELECT SUBSTRING('abcdefg',4,2);				//de
SELECT SUBSTRING('abcdefg' FROM 4 FOR 2);			//de
SELECT SUBSTRING('abcdefg',4,10);				//defg
SELECT SUBSTRING('abcdefg',8,2);			//空字符串

TRIM(str)
TRIM([{BOTH | LEADING | TRAILING} [remstr ] FROM] str ) TRIM(remstr FROM] str )
返回字符串 str ， 其中所有remstr 前缀和/ 或后缀都已被删除,remstr 为可选项，在未指定情况下，可删除空格
分类符BOTH 、LEADING 或TRAILING 中没有一个是给定的, 则假设为BOTH 。

SELECT TRIM('   sdaf  ');			//结果sdaf	删除两侧空格
SELECT LTRIM('   sdaf  ');			//结果sdaf   删除左侧空格
SELECT RTRIM('   sdaf  ');			//结果   sdaf    删除右侧空格
SELECT TRIM('abc' FROM 'abcdfdfdfabc');			//结果dfdfdf		删除两侧abc
SELECT TRIM(BOTH 'abc' FROM 'abcdfdfdfabc');			//结果dfdfdf		删除两侧abc
SELECT TRIM(LEADING  'abc' FROM 'abcdfdfdfabc');			//结果dfdfdfabc	删除左侧abc
SELECT TRIM(TRAILING  'abc' FROM 'abcdfdfdfabc');			//结果abcdfdfdf	删除右侧abc

LOWER(str) / LCASE(str)
返回字符串 str 以及所有根据最新的字符集映射表变为小写字母的字符
UPPER(str) / UCASE(str)
返回字符串 str ， 以及根据最新字符集映射转化为大写字母的字符

SELECT LOWER('ABC');				//abc
SELECT LCASE('ABC');				//abc
SELECT UPPER('abc');				//ABC
SELECT UCASE('abc');				//ABC

REPLACE(str ,from_str ,to_str )
返回字符串str 以及所有被字符串to_str 替代的字符串from_str 。

SELECT REPLACE('abababcabc','abc','M');				//ababMM

REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str 的数目等于count 。 若 count <= 0, 则返回一个空字符串。若str 或 count 为 NULL ，则返回 NULL 。

SELECT REPEAT('ab',5);				//ababababab
SELECT REPEAT('ab',-1);				//空字符串
SELECT REPEAT('',5);					//空字符串
SELECT REPEAT(null,5);				//null

REVERSE(str )
返回字符串 str ，顺序和字符顺序相反。

SELECT REVERSE('abc');				//cba
SELECT REVERSE('你好');			//好你
SELECT REVERSE('');					//空字符串
SELECT REVERSE(null);				//null

HEX(N_or_S )
如果N_OR_S 是一个数字，则返回一个 十六进制值 N 的 字符串表示， N 是一个longlong (BIGINT) 数。这相当于 CONV(N,10,16) 。
如果N_OR_S 是一个字符串，则返回值为一个N_OR_S 的十六进制字符串表示，其中每个N_OR_S 里的每个字符被转化为两个十六进制数字。
UNHEX(str)
执行从 HEX(str) 的反向操作。结果字符以二进制字符串的形式返回。

SELECT HEX(17);				//11
SELECT HEX('你好');			//17
SELECT UNHEX(HEX('你好'));			//你好

2.时间函数

ADDDATE(date ,INTERVAL n type ) :时间加n 类型为type如day/second/…
ADDDATE(date,days ):时间加days天
ADDTIME(date,second):时间加second秒
SUBDATE(date ,INTERVAL n type ) :时间减n 类型为type如day/second/…
SUBDATE(date ,days ):时间加days天
SUBTIME(date ,second):时间加second秒

由于加减的方法形式一样,本处只进行时间加的操作

SELECT ADDDATE('2019-3-23 13:30:30',INTERVAL 10 DAY);		//2019-04-02 13:30:30   加了十天
SELECT ADDDATE('2019-3-23 13:30:30',10);				//2019-04-02 13:30:30  加了十天
SELECT ADDDATE('2019-3-23 13:30:30',INTERVAL 10 SECOND);	//2019-03-23 13:30:40  加了10s
SELECT ADDTIME('2019-3-23 13:30:30',10);				//2019-03-23 13:30:40		加了10s
SELECT ADDTIME('2019-3-23 13:30:30:683',10);				//2019-03-23 13:30:40		加了10s

CURDATE() 当前年月日
CURRENT_DATE(); 当前年月日
CURTIME() 当前时分秒
CURRENT_TIME(); 当前时分秒
NOW() 当前年月日时分秒
CURRENT_TIMESTAMP 当前年月日时分秒
将当前日期按照 ‘YYYY-MM-DD’ 或 YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。

SELECT CURDATE();					//2019-03-23
SELECT CURRENT_DATE();			//2019-03-23
SELECT CURTIME();						//17:20:11
SELECT CURRENT_TIME();			//17:20:11
SELECT NOW();								//2019-03-23 17:44:31
SELECT CURRENT_TIMESTAMP();			//2019-03-23 17:20:11

DATEDIFF(expr ,expr2 )
DATEDIFF() 返回起始时间(expr-expr2)的天数,计算中只用到这些值的日期部分。

SELECT DATEDIFF('2001-02-02','2001-01-01');			//32
SELECT DATEDIFF('2001-01-01','2001-02-02');			//-32

DATE(date) //提取年月日
TIME(date) //提取时分秒
YEAR(date) //提取年
MONTH(date) //提取月
DAY(date) //提取日
HOUR(date) //提取小时
MINUTE(date) //提取分钟
SENCOND(date) //提取秒
EXTRACT(type FROM date) 类型可以是以上任何类型 结果对应
提取日期或时间日期表达式中的年月日时分秒部分,或者指定内容.

SELECT DATE(NOW());				//2019-03-23
SELECT TIME(NOW());				//17:48:47
SELECT YEAR(NOW());				//2019
SELECT MONTH(NOW());			//3
SELECT DAY(NOW());				//23
SELECT HOUR(NOW());			//17
SELECT MINUTE(NOW());			//48
SELECT SECOND(NOW());		//47

DAYNAME(date) //获取星期几
MONTHNAME(date) //获取月份
WEEK(date) //获取取第几周
DAYOFWEEK(date) //获取这周的第几天
DAYOFMONTH(date) //获取这个月的第几天
DAYOFYEAR(date) //获取今天第几天
LAST_DAY(date) //获取本月最后一天

SELECT DAYNAME(NOW());		//Saturday
SELECT MONTHNAME(NOW());	//March
SELECT WEEK(NOW());			//11
SELECT DAYOFWEEK(NOW());	//7 	注:周末属于第一天
SELECT DAYOFMONTH(NOW());	//23
SELECT DAYOFYEAR(NOW());	//82
SELECT LAST_DAY(NOW());	//2019-03-31

SEC_TO_TIME(s)
将以秒为单位的时间 s 转换为时分秒的格式
TIME_TO_SEC(time)
将时分秒转换为秒

SELECT SEC_TO_TIME(3221);			//00:53:41
SELECT TIME_TO_SEC('19:20:00');			//69600

3. 流程控制函数

CASE value
WHEN [compare-value]
THEN result [WHEN [compare-value] THEN result ……]
[ELSE result ]
END
CASE 表示函数开始，END 表示函数结束。
当满足条件value =compare-value 时，返回对应的result，否则返回ELSE后的result。
CASE
WHEN [condition]
THEN
result [WHEN[condition] THEN result ……] [ELSE result]
END
当满足条件condition时，返回对应的result，否则返回ELSE后的result。
两种方案中，如果都不满足而且如果没有ELSE 部分，则返回值为NULL

SELECT CASE  m    
WHEN  2-1 
THEN 'a' 
WHEN 3-1
THEN 'b'
ELSE 'c'
END
将m的值改为分别置为,1,2,3,对应结果分别是 a,b,c

SELECT  CASE
WHEN 2-2= 1 THEN 'a' 
WHEN 3-2 = 2 THEN 'b' 
ELSE 'c' 
END;
若第一个when成立,返回a, 第二个when成立,返回b,都不成立则返回c

IF(expr,v1,v2)
若表达式expr成立,返回值v1 否则,返回v2;

SELECT IF(1>0,'a','b');			//a
SELECT IF(1>0,'a','b');			//b
SELECT IF(null,'a','b');			//b

IFNULL(expression, value)
函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

SELECT IFNULL(5<4,'a');			//返回0 
SELECT IFNULL(5>4,'a');			//返回1 
SELECT IFNULL('m','a');			//返回m
SELECT IFNULL('m',null);			//返回m
SELECT IFNULL(null,'a');			//返回a

NULLIF(expr1, expr2)
比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1

SELECT NULLIF(10,10);			//null
SELECT NULLIF(10,20);			//10
SELECT NULLIF(10,null);			//10
SELECT NULLIF(null,10);			//null
SELECT NULLIF(null,null);			//null

4. 数学函数

数学函数还是有很多的,其中有正弦,余弦之类的,类似于java中Math的Api文档,但是这些并不经常使用,这里只添加了一些常用方法

ABS(x)
返回 x 的绝对值

SELECT ABS(1);			//1
SELECT ABS(-1);			//1

MOD(a, b)
取余,等价于a%b

SELECT MOD(5,3);			//2

PI()
返回 (pi) 的值。默认的显示小数位数是 7 位 , 然而 MySQL 内部会使用完全双精度值。

SELECT PI();				//3.141593

DEGREES(x)
将弧度转换为角度
RADIANS(x)
将角度转换为弧度

SELECT DEGREES(PI()/2);				//90
SELECT RADIANS(90);						//PI/2

SIN(x) //参数为弧度,求x的正弦值
ASIN(x) //参数为值,求值为x的反正弦
COS(x) //参数为弧度,求x的余弦值
ACOS(x) //参数为值,求值为x的反余弦
TAN(x) //参数为弧度,求x的正切值
ATAN(x) //参数为值,求值为x的反切弦
本处只进行正弦演示,余弦,正切道理相同

SELECT SIN(0.5*PI());			//1
SELECT ASIN(1);					//PI/2

CEILING(x) / CEIL(x)
简单理解为向上取整

SELECT CEILING(3.4);			//4
SELECT CEILING(-3.4);			//-3
SELECT CEIL(3.4);					//4	
SELECT CEIL(-3.4);				//-3

FLOOR(x)
返回不大于 X 的最大整数值 。即向下取整

SELECT FLOOR(3.4);			//3
SELECT FLOOR(-3.4);			//-4

ROUND(x) 四舍五入
ROUND(x,D) 四舍五入,并保留d位小数等
其值保留到小数点后 D 位，而第 D 位的保留方式为四舍五入。
若要接保留 X 值小数点左边的 D 位，可将 D 设为负值。

SELECT ROUND(3.4);			//3
SELECT ROUND(3.5);			//4
SELECT ROUND(5555.5555,3);			//5555.556
SELECT ROUND(5555.5555,-3);			//6000

RAND()
返回 0 到 1 的随机数

SELECT RAND();			//0.41592828781896

POW(x, y) / POWER(x, y)
返回 X 的 Y 乘方的结果值。

SELECT POW(3,2);			//9
SELECT POWER(3,2);		//9
SELECT POW(4,0.5);			//2
SELECT POW(2,-2);			//0.25

log(x) 返回x 的自然对数。
ln(x) 返回x的自然对数
log2(x) 返回x的基数为2的对数
log10(x) 返回x的基数为10的对数

SELECT LOG(2,2);				//1
SELECT LOG(10);				//2.302585092994
SELECT LN(10);					//2.302585092994
SELECT LOG2(2);				//1
SELECT LOG10(10);			//1

SIGN(x)
返回参数作为 -1 、 0 或 1 的符号，该符号取决于 X 的值为负、零或正。

SELECT SIGN(-5);					//-1
SELECT SIGN(5);					//1
SELECT SIGN(0);					//0

SQRT(x)
返回非负数的二次方根

SELECT SQRT(4);					//2
SELECT SQRT(-4);				//null

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

SELECT TRUNCATE(5555.5555,3);			//5555.555
SELECT TRUNCATE(5555.5555,0);			//5555
SELECT TRUNCATE(5555.5555,-3);			//5000

FORMAT(x, D)
将数字 x 的格式写成 ‘#,###,###.##’ 格式 , 即保留小数点后 D 位，而第 D 位的保留方式为四舍五入，然后将结果以字符串的形式返回

SELECT FORMAT(55555555.5555,3)				//55,555,555.556

least(x,y,…) 返回最小值
greatest(x,y,…) 返回最大值

SELECT LEAST(1,2,3,4,5,6);				//1
SELECT LEAST('a','b','c');					//a
SELECT GREATEST(1,2,3,4,5,6);			//6
SELECT GREATEST('a','b','c');				//c

5. 聚合函数

COUNT(x) 返回非NULL 的字段x数量
MAX(x) 返回字段x的最大值
MIN(x) 返回字段x的最小值
SUM(x) 返回字段x的总和
AVG(x) 返回字段x的平均值

SELECT COUNT(age) FROM student;
SELECT MAX(age) FROM student;
SELECT MIN(age) FROM student;
SELECT SUM(age) FROM student;
SELECT AVG(age) FROM student;

6. 加密函数

AES_ENCRYPT(str ,key_str ) 加密
AES_DECRYPT(crypt_str ,key_str ) 解密

首先,在AES加密法之前,应对加密方式有一定了解
AES加密要求一般有两种方式:

其一:字段属性为varbinary/binary/四种blob类型等二进制字段属性,然后可以直接进行加密和读取解密

其二:将密文十六进制化，再存入varchar列。(可以使用字符串函数HEX和UNHEX),然后可以将密文十六进制化的内容存入数据库,解密只需现将查询内容UNHEX操作,然后进行AES解密

这个测试使用第二种方式,创建一个表只有一个字段pass,varchar类型
INSERT INTO password_test VALUES  (HEX(AES_ENCRYPT('你好','key')));
//40A7DA926402215ECD3D877F5902F322
SELECT AES_DECRYPT(UNHEX(pass),'key') FROM password_test;	//你好

ENCODE(str ,key_str ) 加密
DECODE(crypt_str ,key_str ) 解密

SELECT ENCODE('hello','123456');			//加密hello
SELECT DECODE(ENCODE('hello','123456'),'123456');		//解密出hello

MD5(x)
该值以 32 位十六进制数字的二进制字符串的形式返回 , 若参数为 NULL 则会返回 NULL

SELECT MD5('123456');			//e10adc3949ba59abbe56e057f20f883e

SHA(x) 系列算法

SELECT SHA('123456');			//7c4a8d09ca3762af61e59520943dc26494f8941b
SELECT SHA1('123456');			//7c4a8d09ca3762af61e59520943dc26494f8941b

7. 信息函数

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

SELECT CHARSET('123');			//utf8

COLLATION(str)

SELECT COLLATION('123456');			//utf8_general_ci  查询速度快,准确度稍差,相对于utf8_unicode_ci 准确度高，但校对速度稍慢。

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

SELECT CONNECTION_ID();	//1

CURRENT_USER() 返回程序的创建者

SELECT USER();					//root@localhost 
SELECT SYSTEM_USER();			//root@localhost 
SELECT SESSION_USER();			//root@localhost 
SELECT CURRENT_USER();			//root@localhost 

DATABASE() 返回当前数据库名
VERSION() 返回Mysql版本

SELECT DATABASE()			//mysql_function_test
SELECT VERSION();			//5.0.18-nt

LAST_INSERT_ID()
返回的值是该客户端产生对影响自动增长列的最新语句第一个 自动增长的值;
假如你使用单INSERT语句插入多个行， LAST_INSERT_ID() 只返回插入的第一行产生的值。

8. 搜索函数

MATCH (col1,col2,…) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION]) MySQL5.6新引入的特性
SELECT 表字段 FROM 表名 WHERE MATCH (全文搜索表字段) AGAINST (‘搜索字符串’);
MATCH 相当于要找的列， 而 AGAINST 就是要找的内容。

比起 like 有点不一样

而且 match … against 还提供很多 操作， 对数据 进一步过滤，

一般可以作为 比较精确的搜索，

9. 其他函数

UUID();
返回一个通用唯一标识符 (UUID) ， UUID 被设计成一个在时间和空间上都独一无二的数字。

SELECT UUID();				//f65225f2-a042-1037-a47b-49f6f6ebb9db