我的MYSQL学习笔记（四）——函数

数字函数
1、求余函数MOD(X,Y)

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

2、函数TRUNCATE(X,Y)

TRUNCATE(X,Y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。

若y设为负数，则截去（归零）x小数点左边起第y位开始后面所有低位的值。

TIPS：ROUND(X,Y)函数在截取值的时候会四舍五入，而TRUNCATE(x，y)直接截取值，并不进行四舍五入
3、HEX(N)
　把N转为十六进制并以字串返回(N是BIGINT数字,等价于CONV
(N,10,16))

UNHEX是HEX的反转函数

字符函数
1、CHAR_LENGTH(STR)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符

2、合并字符串函数CONCAT_WS（x,s1,s2,……）

CONCAT_WS（x,s1,s2,……）,CONCAT_WS代表CONCAT with Separator，是CONCAT（）函数的特殊形式。

第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是

其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

3、INSERT(str,pos,len,newstr)
返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替

4、填充字符串的函数LPAD(S1,LEN,S2)和RPAD(S1,LEN,S2)

LPAD(S1,LEN,S2)返回字符串s1，其左边由字符串s2填补到len字符串长度。假如s1的长度大于len，则返回值被缩短至len字符。


注意上面两幅图的区别
5、删除空格的函数TRIM(S)
TRIM(S)删除字符串s两侧的空格

6、TRIM(S1 FROM S)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。这个函数时删除字符串两端相应匹配的字符，字符串中间出现的响应字符是不会被去除的。

7、重复生成字符串的函数REPEAT(S,N)

8、比较字符串大小的函数STRCMP(S1,S2)
STRCMP(S1,S2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1

9、匹配子串开始位置的函数

LOCATE(STR1,STR)、POSITION(STR1 IN STR)、INSTR(STR,STR1)3个函数作用相同，返回子字符串str1在字符串str中的开始位置

10、返回指定位置的字符串的函数

ELT(N,字符串1,字符串2,字符串3,…,)，若N=1,则返回值为字符串1，若N=2，则返回值为字符串2，以此类推。指定返回字符串位置超出参数个数，返回NULL

11、返回指定字符串位置的函数FIELD(S,S1,S2,…)

FIELD(S,S1,S2,…)返回字符串s在列表s1，s2，……中第一次出现的位置，在找不到s的情况下，返回值为0。

如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。

12、返回子串位置的函数FIND_IN_SET(S1,S2)
FIND_IN_SET(S1,S2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。

13、选取字符串的函数MAKE_SET(X,S1,S2,…)

MAKE_SET(X,S1,S2,…)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。

1的二进制值为0001，4的二进制值为0100，1与4进行异或操作之后的二进制值为0101，从右到左第一位和第三位为1。

MAKE_SET(1,’a’,’b’,’c’)返回第一个字符串

MAKE_SET(1|4,’hello’,’nice’,’world’) 返回从左端开始第一和第三个字符串组成的字符串

日期和时间函数
1、获取当前日期的函数和获取当前时间的函数
CURDATE()、CURRENT_DATE()、CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()、utc_time(),UTC_TIMESTAMP()

2、获取月份的函数MONTHNAME(DATE)

MONTHNAME(DATE)函数返回日期date对应月份的英文全名

3、获取季度、分钟和秒钟的函数

QUARTER(DATE)返回date对应的一年中的季度值，范围是从1~4

使用QUARTER(DATE)函数返回指定日期对应的季度

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

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

4、获取日期的指定值的函数EXTRACT(type FROM date)

EXTRACT(type FROM date)这个函数跟SQLSERVER里的DATEPART()函数是一样的

5、时间和秒钟转换的函数
TIME_TO_SEC(time)返回已转化为秒的time参数，转换公式为：小时*3600+分钟*60+秒
SEC_TO_TIME(second)函数可以将秒转换为小时、分钟和秒数的second参数值

6、计算日期和时间的函数

MYSQL里计算日期和时间的函数比较多

增加日期：DATE_ADD(date,interval expr type)，ADDDATE(date,interval expr type)注意interval关键字

减去日期：DATE_SUB(date,interval expr type)，SUBDATE(date,interval expr type)

增加时间：ADD_TIME(date,expr)

减去时间：SUBTIME(date,expr)

日期差：DATEDIFF()
函数返回两个日期之间的天数。

时间差：timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值

日期格式：DATE_FORMAT(date,format)
日期格式输出参数

条件判断函数

条件判断函数也称为流程控制函数，根据满足的条件的不同，执行相应的流程。

MYSQL中进行条件判断的函数有IF、IFNULL、CASE
1、IF(expr,v1,v2)函数

IF(expr,v1,v2)如果表达式expr是TRUE（expr<>0 and expr<>NULL），则IF()的返回值为v1；

否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定

TIPS：如果v1或v2中只有一个明确是NULL，则IF()函数的结果类型为非NULL表达式的结果类型。

2、IFNULL(V1,V2)函数
IFNULL(V1,V2)假如v1不为NULL，则IFNULL(V1,V2)的返回值为v1；否则其返回值为v2。
IFNULL()的返回值是数字或是字符串，具体情况视语境而定

3、CASE函数

注意：一个CASE表达式的默认返回值类型是任何返回值的相容集合类型，但具体情况视其所在语境而定。

如果用字符串语境中，则返回结果为字符串。如果用在数字语境中，则返回结果为十进制、实数值或整数值

系统信息函数
1、获取MYSQL版本号、连接数和数据库名的函数
VERSION()返回指示MYSQL服务器版本的字符串。这个字符串使用utf8字符集

2、CONNECTION_ID()返回MYSQL服务器当前连接的次数，每个连接都有各自唯一的ID
查看当前用户的连接数

3、SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;
processlist命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态
帮助识别出有问题的查询语句等。
4、DATABASE()和SCHEMA()函数返回使用utf8字符集的默认（当前）数据库名
5、获取用户名的函数
USER()、CURRENT_USER()、CURRENT_USER、SYSTEM_USER()、SESSION_USER()
这几个函数返回当前被MYSQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的MYSQL帐户。
6、CHARSET(STR)返回字符串str自变量的字符集

7、获取最后一个自动生成的ID值的函数
LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值
请注意：当一次性插入多个值时该函数返回的值会发生错误

加密函数
1、加密函数PASSWORD(STR)

PASSWORD(STR)从原文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL

MYSQL将PASSWORD函数加密后的密码保存到用户权限表中
TIPS:PASSWOR()函数在MYSQL服务器的鉴定系统中使用；不应将他用在个人应用程序中，PASSWORD（）函数加密是单向的（不可逆）
PASSWORD执行密码加密与UNIX中密码加密方式不同
2、加密函数MD5(str)
MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL
3、加密函数ENCODE(str,pswd_str)
ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。使用DECODE()解密结果，结果是一个和str长度相同的二进制字符串
4、解密函数DECODE(crypt_str,pswd_str)
DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE()返回的字符串

其他函数
1、格式化函数FORMAT(x,n)
FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。
若n为0，则返回结果函数不含小数部分

2、不同进制的数字转换的函数
CONV(N,from_base,to_base)函数进行不同进制数间的转换。

CONV(‘a’,16,2)将十六进制的a转换为二进制表示的数值。
3、IP地址与数字相互转换的函数
INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。
地址可以是4或8比特地址

INET_NTOA(expr)给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示。

4、加锁函数和解锁函数

GET_LOCK(str,timeout)设法使用字符串str给定的名字得到一个锁，超时为timeout秒。

RELEASE_LOCK(str)解开被GET_LOCK()获取的，用字符串str所命名的锁。

IS_FREE_LOCK(str)检查名为str的锁是否可以使用

IS_USED_LOCK(str)检查名为str的锁是否正在被使用
5、重复执行指定操作的函数
BENCHMARK(count,expr)函数重复count次执行表达式expr。他可以用于计算MYSQL处理表达式的速度。
结果值通常为0（0只是表示处理过程很快，并不是没有花费时间）
另一个作用是他可以在MYSQL客户端内部报告语句执行的时间。

6、改变字符集的函数
CONVERT(…using…)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。

分组函数
1、分组拼接函数
GROUP BY中使用GROUP_CONCAT()函数