我的MYSQL学习心得（六）
- WBOY原创
- 2016-06-01 13:16:121188浏览
我的MYSQL学习心得（六）
我的MYSQL学习心得（一）
我的MYSQL学习心得（二）
我的MYSQL学习心得（三）
我的MYSQL学习心得（四）
我的MYSQL学习心得（五）
这一节主要介绍MYSQL里的函数，MYSQL里的函数很多，我这里主要介绍MYSQL里有而SQLSERVER没有的函数
数学函数
1、求余函数MOD(X,Y)
MOD(X,Y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，他返回除法运算后的精确余数
SELECT MOD(31,8)
2、四舍五入函数TRUNCATE(X,Y)
TRUNCATE(X,Y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。
若y设为负数，则截去（归零）x小数点左边起第y位开始后面所有低位的值。
SELECT TRUNCATE(1.32,1)
TRUNCATE(1.32,1)保留小数点后一位数字，返回值为1.3
TIPS：ROUND(X,Y)函数在截取值的时候会四舍五入，而TRUNCATE(x，y)直接截取值，并不进行四舍五入
字符串函数
计算字符串字符数的函数
1、CHAR_LENGTH(STR)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符
SELECT CHAR_LENGTH('DATE')
2、合并字符串函数CONCAT_WS（x,s1,s2,......）
CONCAT_WS（x,s1,s2,......）,CONCAT_WS代表CONCAT with Separator，是CONCAT（）函数的特殊形式。
第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是
其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。
SELECT CONCAT_WS('-','1st','2nd','3rd'),CONCAT_WS('-','1st',NULL,'3rd')
CONCAT_WS('-','1st','2nd','3rd')使用分隔符‘-’将3个字符串连接成一个字符串，结果为“1st-2nd-3rd”；
CONCAT_WS('-','1st',NULL,'3rd')因为第二个值为NULL，所以第二个值不会出现在结果里
3、替换字符串的函数INSERT(S1,X,LEN,S2)
INSERT(S1,X,LEN,S2)函数跟SQLSERVER里的STUFF()函数的功能是一样的，这里不作介绍了
填充字符串的函数LPAD(S1,LEN,S2)和RPAD(S1,LEN,S2)
4、LPAD(S1,LEN,S2)返回字符串s1，其左边由字符串s2填补到len字符串长度。假如s1的长度大于len，则返回值被缩短至len字符。
LPAD（）函数和RPAD（）函数功能跟SQLSERVER里的 REPLACE()相似，不过功能更加强大
SELECT LPAD('hello',4,'??'),LPAD('hello',10,'??')
字符串“hello”长度大于4，不需要填充，因此LPAD('hello',4,'??')只返回被缩短的长度为4的子串
字符串“hello”长度小于10，LPAD('hello',10,'??')返回结果为“?????hello”，左侧填充“？”，长度为10
5、RPAD(S1,LEN,S2)返回字符串s1，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到len字符长度
SELECT RPAD('hello',4,'?')
字符串“hello”长度大于4，不需要填充，因此RPAD('hello',4,'?')只返回被缩短的长度为4的子串"hell"
删除空格的函数TRIM(S)
6、TRIM(S)删除字符串s两侧的空格
MYSQL这里比SQLSERVER方便，SQLSERVER要删除两边的空格，需要使用： SELECTLTRIM(RTRIM('hello'))
SELECT TRIM(' book ')
删除指定字符串的函数TRIM(S1 FROM S)
7、TRIM(S1 FROM S)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格
SELECT TRIM('xy' FROM 'xyxboxyokxxyxy')
'xyxboxyokxxyxy'两端的重复字符串“xy”，而中间的“xy”并不删除，结果为
xboxyokx
重复生成字符串的函数REPEAT(S,N)
这个函数跟SQLSERVER里的REPLICATE()函数是一样的，参数个数都是一样的，这里不作介绍了
8、比较字符串大小的函数STRCMP(S1,S2)
STRCMP(S1,S2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1
SELECT STRCMP('txt','txt2') ,STRCMP('txt2','txt'),STRCMP('txt','txt')
“txt”小于“txt2”，因此 STRCMP('txt','txt2') 返回结果为-1
STRCMP('txt2','txt')返回结果为1
“txt”与“txt”相等，因此STRCMP('txt','txt')返回结果为0
匹配子串开始位置的函数
9、LOCATE(STR1,STR)、POSITION(STR1 IN STR)、INSTR(STR,STR1)3个函数作用相同，返回子字符串str1在字符串str中的开始位置
这三个函数跟SQLSERVER里的CHARINDEX()函数功能类似
SELECT LOCATE('ball','football'),POSITION('ball' IN 'football') ,INSTR('football','ball')
子字符串“ball”在字符串“football”中从第5个字母位置开始，因此3个函数返回结果都为5
返回指定位置的字符串的函数
10、ELT(N,字符串1,字符串2,字符串3,...,)，若N=1,则返回值为字符串1，若N=2，则返回值为字符串2，以此类推。
若N小于1或大于参数的数目，则返回值为NULL
SELECT ELT(3,'1st','2nd','3rd'),ELT(3,'net','os')
由结果可以看到,ELT(3,'1st','2nd','3rd')返回第3个位置的字符串“3rd”；指定返回字符串位置超出参数个数，返回NULL
返回指定字符串位置的函数FIELD(S,S1,S2,...)
11、FIELD(S,S1,S2,...)返回字符串s在列表s1，s2，......中第一次出现的位置，在找不到s的情况下，返回值为0。
如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。
SELECT FIELD('hi','hihi','hey','hi','bas') AS coll,FIELD('hi','hihi','lo','hilo','foo') AS col2
FIELD('hi','hihi','hey','hi','bas')函数中字符串hi出现在列表的第3个字符串位置，因此返回结果为3
FIELD('hi','hihi','lo','hilo','foo') 列表中没有字符串hi，因此返回结果为0
返回子串位置的函数FIND_IN_SET(S1,S2)
12、FIND_IN_SET(S1,S2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号
‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。
这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。
SELECT FIND_IN_SET('hi','hihi,hey,hi,bas')
虽然FIND_IN_SET(S1,S2)和FIELD(S,S1,S2,...)两个函数格式不同，但作用类似，都可以返回指定字符串在字符串列表中的位置
选取字符串的函数MAKE_SET(X,S1,S2,...)
13、MAKE_SET(X,S1,S2,...)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。
s1，s2...中的NULL值不会被添加到结果中。
SELECT MAKE_SET(1,'a','b','c') AS col1,MAKE_SET(1|4,'hello','nice','world') AS col2
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()
以上函数都是返回当前日期和时间值，MYSQL的函数数量的确比SQLSERVER多很多，SQLSERVER获取当前时间和日期用的
函数是： SELECTGETDATE()
SELECT NOW()
返回UTC日期的函数和返回UTC时间的函数
MYSQL里返回UTC日期和时间是分开的，而SQLSERVER里是一起的
SELECT GETUTCDATE()
MYSQL
SELECT UTC_DATE(),UTC_TIME()
2、获取月份的函数MONTHNAME(DATE)
MONTHNAME(DATE)函数返回日期date对应月份的英文全名
SELECT MONTHNAME('2013-8-2')
返回8月份的英文
获取季度、分钟和秒钟的函数
3、QUARTER(DATE)返回date对应的一年中的季度值，范围是从1~4
使用QUARTER(DATE)函数返回指定日期对应的季度
SELECT QUARTER('11-04-01')
4月份在第二季度，所以返回2
4、MINUTE(TIME)返回time对应的分钟数，范围是从0~59
SELECT MINUTE('11-02-03 10:10:06')
返回10分钟
5、SECOND(time) 返回time对应的秒数，范围是从0~59
SELECT SECOND('10:23:10')
返回10秒
6、获取日期的指定值的函数EXTRACT(type FROM date)
EXTRACT(type FROM date)这个函数跟SQLSERVER里的DATEPART()函数是一样的
获取日期中的年份
SQLSERVER
SELECT DATEPART(YEAR,'2013-2-3')
MYSQL
SELECT EXTRACT(YEAR FROM '2013-2-3')
时间和秒钟转换的函数
7、TIME_TO_SEC(time)返回已转化为秒的time参数，转换公式为：小时*3600+分钟*60+秒
SELECT TIME_TO_SEC('23:22:00')
8、SEC_TO_TIME(second)函数可以将秒转换为小时、分钟和秒数的second参数值
SELECT SEC_TO_TIME('84120')
SEC_TO_TIME(second)函数和TIME_TO_SEC(time)函数互为反函数
计算日期和时间的函数
MYSQL里计算日期和时间的函数比较多
增加日期：DATE_ADD(date,interval expr type)，ADDDATE(date,interval expr type)
减去日期：DATE_SUB(date,interval expr type)，SUBDATE(date,interval expr type)
增加时间：ADD_TIME(date,expr)
减去时间：SUBTIME(date,expr)
时间差：DATEDIFF()
日期和时间格式化：
DATE_FORMAT(date,format)
TIME_FORMAT(time,format)
返回日期时间字符串的显示格式：GET_FORMAT(val_type,format_type)
相对来说，SQLSERVER在时间日期方面的计算就没有那么多函数了
基本上SQLSERVER利用下面两个函数来通杀日期时间计算的场景
SELECT DATEADD(),DATEDIFF()
条件判断函数
条件判断函数也称为流程控制函数，根据满足的条件的不同，执行相应的流程。
MYSQL中进行条件判断的函数有IF、IFNULL、CASE
虽然SQLSERVER里也有IF和CASE，不过MYSQL里的IF语句的语法跟SQLERVER有很大出入
1、IF(expr,v1,v2)函数
IF(expr,v1,v2)如果表达式expr是TRUE（expr0 and exprNULL），则IF()的返回值为v1；
否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定
SELECT IF(1>2,2,3)
1>2的结果为FALSE，IF(1>2,2,3)返回第二个表达式的值3。
TIPS：如果v1或v2中只有一个明确是NULL，则IF()函数的结果类型为非NULL表达式的结果类型。
2、IFNULL(V1,V2)函数
IFNULL(V1,V2)假如v1不为NULL，则IFNULL(V1,V2)的返回值为v1；否则其返回值为v2。
IFNULL()的返回值是数字或是字符串，具体情况视语境而定
SELECT IFNULL(1,2),IFNULL(NULL,10)
IFNULL(1,2)虽然第二个值也不为空，但返回结果依然是第一个值；
IFNULL(NULL,10)第一个值为空，因此返回“10”
注意：IFNULL(V1,V2)函数跟SQLSERVER里的 SELECTNULLIF() 函数不一样
SQLSERVER里的NULLIF函数
需要两个参数,如果两个指定的表达式等价，则返回null
例子：NULLIF(a,b)
说明：如果a和b是相等的，那么返回NULL，如果不相等返回a
select NULLIF('eqeqweqwe','1') 结果是eqeqweqwe
select NULLIF(1,1) 结果是NULL
a和b的类型要一致
3、CASE函数
注意：一个CASE表达式的默认返回值类型是任何返回值的相容集合类型，但具体情况视其所在语境而定。
如果用字符串语境中，则返回结果为字符串。如果用在数字语境中，则返回结果为十进制、实数值或整数值
这个数据类型在拼接SQL语句的时候特别容易忽略，有时候会在拼接SQL语句的时候，case 后面的表达式或者when后面的表达式不一致报错！
参考文章：http://support.microsoft.com/kb/969467/zh-cn
修补程序： 错误消息，当您运行查询时，SQL Server 2008年中使用 CASE 函数："转换失败时转换为数据类型 值 "
在MSDN论坛也有一个相关的帖子，大概是因为拼接sql的时候case 后面的表达式值的数据类型和else后面的表达式值的数据类型不一致导致拼接sql失败
具体的帖子地址忘记了，也找不到，如果找到以后会补充上
系统信息函数
获取MYSQL版本号、连接数和数据库名的函数
1、VERSION()返回指示MYSQL服务器版本的字符串。这个字符串使用utf8字符集
SELECT VERSION()
2、CONNECTION_ID()返回MYSQL服务器当前连接的次数，每个连接都有各自唯一的ID
查看当前用户的连接数
SELECT CONNECTION_ID()
这里返回1，返回值根据登录的次数会有不同。
3、SHOW PROCESSLIST;
4、SHOW FULL PROCESSLIST;
processlist命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态
帮助识别出有问题的查询语句等。
如果是root帐号，能看到所有用户的当前连接。如果是其他普通帐号，则只能看到自己占用的连接。showprocesslist只能列出当前100条
如果想全部列出，可以使用SHOW FULL PROCESSLIST命令
SHOW PROCESSLIST
SHOW FULL PROCESSLIST
show full processlist会看到连接使用的内存
show processlist
show full processlist
各个列的含义
（1）id列，用户登录mysql时，系统分配的“connection_id”
（2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
（3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
（4）db列，显示这个进程目前连接的是哪个数据库
（5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）
（6）time列，显示这个状态持续的时间，单位是秒
（7）state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有状态的描述，state只是语句执行中的某一个状态。一个sql语句，
以查询为例
可能需要经过
copying to tmp table，
sorting result，
sending data
等状态才可以完成
（8）info列，显示这个sql语句，是判断问题语句的一个重要依据。
5、DATABASE()和SCHEMA()函数返回使用utf8字符集的默认（当前）数据库名
SELECT DATABASE(),SCHEMA()
可以看到，两个函数的作用相同
6、获取用户名的函数
USER()、CURRENT_USER()、CURRENT_USER、SYSTEM_USER()、SESSION_USER()
这几个函数返回当前被MYSQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户
存取权限的MYSQL帐户。一般情况下，这几个函数的返回值是相同的。
SELECTUSER(),CURRENT_USER(),SYSTEM_USER()
返回结果指示了当前帐户连接服务器的用户名以及所连接的客户主机，root为当前登录的用户名，localhost为登录的主机名
7、获取字符串的字符集和排序方式的函数
CHARSET(STR)返回字符串str自变量的字符集
SELECTCHARSET('abc') ,CHARSET(CONVERT('abc' USING latin1)),CHARSET(VERSION())
CHARSET('abc')返回系统默认的字符集utf8；
CHARSET(CONVERT('abc' USING latin1))返回的字符集为latin1；
VERSION()返回的字符串使用utf8字符集，因此CHARSET返回结果为utf8
8、COLLATION(str)返回字符串str的字符排列方式
SELECTCOLLATION(_latin2 'abc'),COLLATION(CONVERT('abc' USING utf8))
可以看到，使用不同字符集时字符串的排列方式不同
9、获取最后一个自动生成的ID值的函数
LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值
（1）一次插入一条记录
首先创建表worker，其ID字段带有AUTO_INCREMENT约束
CREATE TABLE worker(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,NAME VARCHAR(30))
分别单独向表worker插入2条记录
INSERT INTO worker VALUES(NULL,'jimmy');INSERT INTO worker VALUES(NULL,'tom')SELECT * FROM worker
查看已经插入的数据可以发现，最后一条插入的记录的ID字段值为2，使用LAST_INSERT_ID()查看最后自动生成的ID值
SELECT LAST_INSERT_ID()
可以看到，一次插入一条记录时，返回值为最后一条插入记录的ID值
（2）一次同时插入多条记录
接下来，向表中插入多条记录
INSERT INTO worker VALUES(NULL,'kevin'),(NULL,'michal'),(NULL,'nick')
查询已经插入的记录
SELECT * FROM worker
可以看到最后一条记录的ID字段值为5，使用LAST_INSERT_ID()查看最后自动生成的ID值
SELECT LAST_INSERT_ID()
结果显示，ID字段值不是5而是3，这是为什麽呢？
在向数据表插入一条记录时，LAST_INSERT_ID()返回带有AUTO_INCREMENT约束的字段最新生成的值2；继续向表
中同时添加3条记录，这时候因为当使用一条INSERT语句插入多个行时，LAST_INSERT_ID只返回插入的第一行数据
时产生的值，在这里为第3条记录。之所以这样，是因为这使依靠其他服务器复制同样的INSERT语句变得简单
TIPS:LAST_INSERT_ID是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID返回表b中的ID值
这里跟SQLSERVER不一样
使用下面脚本来测试，无论使用方式一还是方式二，当前的LAST_INSERT_ID都是最后一个值
CREATE TABLE [dbo].[aaa]([a] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,[name] [nvarchar](50) NOT NULL) ON [PRIMARY]
USE [sss]SELECT * FROM [dbo].[aaa]INSERT INTO [dbo].[aaa] ( [name] )VALUES( N'sdfsdf'-- name - nvarchar(50) )SELECT * FROM [dbo].[aaa]INSERT INTO [dbo].[aaa] ( [name] )VALUES( N'sdf969'-- name - nvarchar(50) ),('lkjj96'),('565656')SELECT IDENT_CURRENT('aaa')SELECT * FROM [dbo].[aaa]
加密函数
1、加密函数PASSWORD(STR)
PASSWORD(STR)从原文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL
SELECT PASSWORD('NEWPWD')
MYSQL将PASSWORD函数加密后的密码保存到用户权限表中
TIPS:PASSWOR()函数在MYSQL服务器的鉴定系统中使用；不应将他用在个人应用程序中，PASSWORD（）函数加密是单向的（不可逆）
PASSWORD执行密码加密与UNIX中密码加密方式不同
2、加密函数MD5(str)
MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL
SELECT MD5('123')
3、加密函数ENCODE(str,pswd_str)
ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。使用DECODE()解密结果，结果是一个和str长度相同的二进制字符串
SELECT ENCODE('nihao','123')
可以看到加密后的结果为乱码
4、解密函数DECODE(crypt_str,pswd_str)
DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE()返回的字符串
SELECT DECODE(ENCODE('nihao','123'),'123')
可以看到，解密出来的字符串
ENCODE()和DECODE互为反函数
其他函数
1、格式化函数FORMAT(x,n)
FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。
若n为0，则返回结果函数不含小数部分
SELECT FORMAT(12332.123465,4)
FORMAT(12332.123465,4)保留4位小数点值，并进行四舍五入，结果为12,332.1235
2、不同进制的数字转换的函数
CONV(N,from_base,to_base)函数进行不同进制数间的转换。
SELECT CONV('a',16,2)
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客户端内部报告语句执行的时间。
首先，使用PASSWORD函数加密密码
SELECT PASSWORD('nihao')
可以看到PASSWORD()函数执行花费的时间为0.00098秒
下面使用BENCHMARK函数重复执行PASSWORD操作500000次
SELECT BENCHMARK(500000,PASSWORD('nihao'))
由此可以看出，使用BENCHMARK执行500000次的时间为0.49690秒，明显比执行一次的时间延长了。
TIPS：BENCHMARK报告的时间是客户端经过的时间，而不是在服务器端的CPU时间，每次执行后报告的时间并不一定是相同的。
6、改变字符集的函数
CONVERT(...using...)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。
SELECTCHARSET('string'),CHARSET(CONVERT('string' USING latin1))
默认为utf8字符集，通过CONVERT()将字符串“string”的默认字符集改为latin1
7、改变数据类型的函数
CAST(x,AS type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型的值，可转换的type有：
BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED
在SQLSERVER里也是使用这两个函数进行数据类型转换的~
SELECTCAST(100 AS CHAR(2)),CONVERT('2013-8-9 12:12:12',TIME)
可以看到， CAST(100 AS CHAR(2))将整数数据100转换为带有2个显示宽度的字符串类型，结果为10
CONVERT('2013-8-9 12:12:12',TIME)将DATETIME类型的值，转换为TIME类型值，结果为“12:12:12”
总结
这一节简单介绍了MYSQL里的一些函数，并且比较了与SQLSERVER的区别
TIPS:
1、MYSQL中，日期时间以字符串形式存储在数据表中，因此可以使用字符串函数分别截取日期时间值的不同部分
2、修改默认的字符集，更改MYSQL默认的字符集，在Windows中，只需要修改my.ini，该文件在MYSQL安装目录下。
修改配置文件中的default-character-set和character-set-server参数值，将其改为想要的字符集名称，如：
gbk、gb2312、latin1等，修改完之后，重启MYSQL服务，即可生效。
如果不确定当前使用的字符集，可以使用下面的SQL语句来查看当前字符集进行对比
SHOW VARIABLES LIKE 'character_set_%'
如有不对的地方，欢迎大家拍砖o(∩_∩)o