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

╰半夏微凉° 2022-08-07 03:57 344阅读 0赞

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

MOD(X,Y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，他返回除法运算后的精确余数  
![这里写图片描述][20150308005214434]  
2、函数TRUNCATE(X,Y)

TRUNCATE(X,Y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。  
![这里写图片描述][20150308005513461]

若y设为负数，则截去（归零）x小数点左边起第y位开始后面所有低位的值。  
![这里写图片描述][20150308005615955]  
TIPS：ROUND(X,Y)函数在截取值的时候会四舍五入，而TRUNCATE(x，y)直接截取值，并不进行四舍五入  
3、HEX(N)  
　把N转为十六进制并以字串返回(N是BIGINT数字,等价于CONV  
(N,10,16))  
![这里写图片描述][20150308010801653]  
UNHEX是HEX的反转函数  
![这里写图片描述][20150308011012834]

**字符函数**  
1、CHAR\_LENGTH(STR)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符  
![这里写图片描述][20150308011151161]  
2、合并字符串函数CONCAT\_WS（x,s1,s2,……）

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

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

其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。  
![这里写图片描述][20150308011932999]  
3、INSERT(str,pos,len,newstr)  
返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替  
![这里写图片描述][20150308012525982]  
4、填充字符串的函数LPAD(S1,LEN,S2)和RPAD(S1,LEN,S2)

LPAD(S1,LEN,S2)返回字符串s1，其左边由字符串s2填补到len字符串长度。假如s1的长度大于len，则返回值被缩短至len字符。  
![这里写图片描述][20150310233812758]  
![这里写图片描述][20150310233911975]  
注意上面两幅图的区别  
5、删除空格的函数TRIM(S)  
TRIM(S)删除字符串s两侧的空格  
![这里写图片描述][20150316230508663]  
6、TRIM(S1 FROM S)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。这个函数时删除字符串两端相应匹配的字符，字符串中间出现的响应字符是不会被去除的。  
![这里写图片描述][20150316232537384]  
7、重复生成字符串的函数REPEAT(S,N)  
![这里写图片描述][20150316233659666]  
8、比较字符串大小的函数STRCMP(S1,S2)  
STRCMP(S1,S2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1  
![这里写图片描述][20150316233928994]  
9、匹配子串开始位置的函数

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

![这里写图片描述][20150317073627174]  
10、返回指定位置的字符串的函数

ELT(N,字符串1,字符串2,字符串3,…,)，若N=1,则返回值为字符串1，若N=2，则返回值为字符串2，以此类推。指定返回字符串位置超出参数个数，返回NULL  
![这里写图片描述][20150317221554131]  
11、返回指定字符串位置的函数FIELD(S,S1,S2,…)

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

如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。  
![这里写图片描述][20150317222723495]  
12、返回子串位置的函数FIND\_IN\_SET(S1,S2)  
FIND\_IN\_SET(S1,S2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。  
![这里写图片描述][20150317223813198]  
13、选取字符串的函数MAKE\_SET(X,S1,S2,…)

MAKE\_SET(X,S1,S2,…)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。  
![这里写图片描述][20150317230829781]  
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()  
![这里写图片描述][20150319222402744]  
2、获取月份的函数MONTHNAME(DATE)

MONTHNAME(DATE)函数返回日期date对应月份的英文全名  
![这里写图片描述][20150319222725903]  
3、获取季度、分钟和秒钟的函数

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

使用QUARTER(DATE)函数返回指定日期对应的季度  
![这里写图片描述][20150319223009641]  
MINUTE(TIME)返回time对应的分钟数，范围是从0~59  
![这里写图片描述][20150319223201587]  
SECOND(time) 返回time对应的秒数，范围是从0~59  
![这里写图片描述][20150319223327855]  
4、获取日期的指定值的函数EXTRACT(type FROM date)

EXTRACT(type FROM date)这个函数跟SQLSERVER里的DATEPART()函数是一样的  
![这里写图片描述][20150319224012694]  
5、时间和秒钟转换的函数  
TIME\_TO\_SEC(time)返回已转化为秒的time参数，转换公式为：小时\*3600+分钟\*60+秒  
SEC\_TO\_TIME(second)函数可以将秒转换为小时、分钟和秒数的second参数值  
![这里写图片描述][20150322180853598]  
6、计算日期和时间的函数

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

增加日期：DATE\_ADD(date,interval expr type)，ADDDATE(date,interval expr type)注意interval关键字  
![这里写图片描述][20150322181113953]

减去日期：DATE\_SUB(date,interval expr type)，SUBDATE(date,interval expr type)  
![这里写图片描述][20150322181755143]  
增加时间：ADD\_TIME(date,expr)

减去时间：SUBTIME(date,expr)  
![这里写图片描述][20150322221230908]  
日期差：DATEDIFF()  
函数返回两个日期之间的天数。  
![这里写图片描述][20150401101202949]  
时间差：timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值  
![这里写图片描述][20150401101900518]  
日期格式：DATE\_FORMAT(date,format)  
[日期格式输出参数][Link 1]  
![这里写图片描述][20150401102806862]

条件判断函数

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

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

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

否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定  
![这里写图片描述][20150401104838001]  
TIPS：如果v1或v2中只有一个明确是NULL，则IF()函数的结果类型为非NULL表达式的结果类型。  
![这里写图片描述][20150401105922607]

2、IFNULL(V1,V2)函数  
IFNULL(V1,V2)假如v1不为NULL，则IFNULL(V1,V2)的返回值为v1；否则其返回值为v2。  
IFNULL()的返回值是数字或是字符串，具体情况视语境而定  
![这里写图片描述][20150401114108520]

3、CASE函数

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

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

系统信息函数  
1、获取MYSQL版本号、连接数和数据库名的函数  
VERSION()返回指示MYSQL服务器版本的字符串。这个字符串使用utf8字符集  
![这里写图片描述][20150401114828145]  
2、CONNECTION\_ID()返回MYSQL服务器当前连接的次数，每个连接都有各自唯一的ID  
查看当前用户的连接数  
![这里写图片描述][20150401115123255]  
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自变量的字符集  
![这里写图片描述][20150401152543200]

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

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

PASSWORD(STR)从原文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL  
![这里写图片描述][20150401161729583]  
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，则返回结果函数不含小数部分  
![这里写图片描述][20150401172600362]

2、不同进制的数字转换的函数  
CONV(N,from\_base,to\_base)函数进行不同进制数间的转换。  
![这里写图片描述][20150401174512880]  
CONV(‘a’,16,2)将十六进制的a转换为二进制表示的数值。  
3、IP地址与数字相互转换的函数  
INET\_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。  
地址可以是4或8比特地址  
![这里写图片描述][20150401175159978]  
INET\_NTOA(expr)给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示。  
![这里写图片描述][20150401175502963]  
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客户端内部报告语句执行的时间。  
![这里写图片描述][20150402105802928]  
6、改变字符集的函数  
CONVERT(…using…)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。  
![这里写图片描述][20150402110322382]

分组函数  
1、分组拼接函数  
GROUP BY中使用GROUP\_CONCAT()函数  
![这里写图片描述][20150402115136811]

[20150308005214434]: /images/20220805/e5603458babb400e95c7420179c8a787.png
[20150308005513461]: /images/20220805/0efb955435084190af728349291e026f.png
[20150308005615955]: /images/20220805/0f58d17a0f804787964ba9388635fbc0.png
[20150308010801653]: /images/20220805/1d2b61cc9f874831a403cc9a088da2c2.png
[20150308011012834]: /images/20220805/5c3a47c1cc744b8aba0ebe07496cc1aa.png
[20150308011151161]: /images/20220805/a6fa49afe36e44aa99fadeb29f86537d.png
[20150308011932999]: /images/20220805/fc0cbce419f644c2a93abaf101ca3f3b.png
[20150308012525982]: /images/20220805/cca382fff92d49238b4b31ff1df5ff4f.png
[20150310233812758]: /images/20220805/f45c4d31690744c2bfcbcb3169995f5b.png
[20150310233911975]: /images/20220805/0b66dd89cda74ae8b2501067bfcfc23c.png
[20150316230508663]: /images/20220805/cd24ee9dc6da4b10b2032dd5e9077549.png
[20150316232537384]: /images/20220805/c3b158ac968c4f5fb692a602ce43e512.png
[20150316233659666]: /images/20220805/567900d1e4ce419c83ced50b6b038a66.png
[20150316233928994]: /images/20220805/755d9d73d42f4618ae61ba45d323e417.png
[20150317073627174]: /images/20220805/c15006ee8baa474390466763421489df.png
[20150317221554131]: /images/20220805/2d44165d1c884b20af51c7fb1cad9d38.png
[20150317222723495]: /images/20220805/40af494101e74a7cba025fc02ef1f474.png
[20150317223813198]: /images/20220805/75871d3de7154b6799c21e38fad02d8a.png
[20150317230829781]: /images/20220805/21fad7f8265e4e119f5d422bbd8571f6.png
[20150319222402744]: /images/20220805/acdae8ee82d84db790e5ec9517645220.png
[20150319222725903]: /images/20220805/3832f8c03e5c4791adbf1b9b0dfa6c3d.png
[20150319223009641]: /images/20220805/b7b1d4445c3e43348ce56d1bac27c6e6.png
[20150319223201587]: /images/20220805/1d3e4e01522a4ac2812b0886b7ba568a.png
[20150319223327855]: /images/20220805/ad45b3bc86ea45e79213c338c39bb99d.png
[20150319224012694]: /images/20220805/ff3d9d22d045403f8784bbbdfa8d04b8.png
[20150322180853598]: /images/20220805/ed1d8126d5894824abc8e0af7a8ed1f6.png
[20150322181113953]: /images/20220805/aac16054f3be45e68110754c4962dda0.png
[20150322181755143]: /images/20220805/8f055f76092f4ec18fe426e0fd55de86.png
[20150322221230908]: /images/20220805/35f053f48c884b8eacf59df80f0c1247.png
[20150401101202949]: /images/20220805/35e8bad1ce2345e9ae5e854a958bdd68.png
[20150401101900518]: /images/20220805/564090e940684e58a08ab03eefcc6728.png
[Link 1]: http://www.w3cschool.cn/func_date_format.html
[20150401102806862]: /images/20220805/6c72c36ae2c84803bd3ce1b0c7382a42.png
[20150401104838001]: /images/20220805/23777b14ea984ea89d2e7858674d779a.png
[20150401105922607]: /images/20220805/fe353e8b9bd041e8bcfe06991987df4a.png
[20150401114108520]: /images/20220805/0ee162ad154d4688a8ec4f63d518d427.png
[20150401114828145]: /images/20220805/b6e95c076e4b4671b4294a2f21eccdcf.png
[20150401115123255]: /images/20220805/68271c79558f4be79ec9f70aa2d5e211.png
[20150401152543200]: /images/20220805/88ce277211aa4a44822d8fd6f46bf30f.png
[20150401161729583]: /images/20220805/253d57c33956437dbaa379091a656254.png
[20150401172600362]: /images/20220805/6a6f446871274ab7ac9161cf6cb713d9.png
[20150401174512880]: /images/20220805/4ba740d7d3d4456d946175b0fe0e9fbe.png
[20150401175159978]: /images/20220805/3349ff07ee02475a835d240dd73bda09.png
[20150401175502963]: /images/20220805/85da1193fb644335acc91bef0d0ad388.png
[20150402105802928]: /images/20220805/20f810f5610b4eef9bd6496e3a322d5e.png
[20150402110322382]: /images/20220805/e2e435d411e84966810381d94d7adb5c.png
[20150402115136811]: /images/20220805/1fd188b23bfd41d38f0e02050874347d.png