与MSSQL对比学习MYSQL的心得（六）--函数

数学函数

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)直接截取值，并不进行四舍五入

3、求余函数HEX(X)和UNHEX（X）函数

有以下的代码可以演示HEX和UNHEX的功能：

SELECT HEX('this is a test str')

查询的结果为：746869732069732061207465737420737472

SELECT UNHEX('746869732069732061207465737420737472')

查询的结果为：this is a test str

字符串函数

计算字符串字符数的函数

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()函数的功能是一样的，这里不作介绍了

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

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"

6、删除空格的函数TRIM(S)

TRIM(S)删除字符串s两侧的空格

MYSQL这里比SQLSERVER方便，SQLSERVER要删除两边的空格，需要使用： SELECT LTRIM(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

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

这个函数跟SQLSERVER里的REPLICATE()函数是一样的，参数个数都是一样的，这里不作介绍了

9、比较字符串大小的函数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

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

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

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

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

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

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

13、返回子串位置的函数FIND_IN_SET(S1,S2)

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,...)两个函数格式不同，但作用类似，都可以返回指定字符串在字符串列表中的位置

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

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获取当前时间和日期用的

函数是： SELECT GETDATE()

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')