MySQL（二） 常用函数

1. 数学函数

ABS(X)
返回X的绝对值。
示例：SELECT ABS(-3.5)  结果：3.5

PI()  
返回圆周率π的值，默认的显示小数位数是6位。
示例：SELECT PI()  结果：3.141593

MOD(x,y)
返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，它返回除法运算后的精确余数。 -- 
示例：SELECT MOD(3.5,2)  结果：1.5

CEIL(x)和CEILING(x)
返回不小于x的最小整数值，返回值转化为一个BIGINT。 -- SELECT CEILING(3.5) 结果：4

FLOOR(x)
返回不大于x的最大整数值，返回值转化为一个BIGINT。
示例：SELECT FLOOR(3.5)  结果：3

RAND()
返回一个随机浮点值v，范围在0到1之间（0 ≤ v ≤ 1.0），每次产生不同的的随机数值。
RAND(x)
参数x为整数，当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。

ROUND(x)
返回最接近于参数x的整数，对x值进行四舍五入。
ROUND(x,y)
返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

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

2. 字符串函数

CHAR_LENGTH(str)
返回值为字符串str所包含的字符个数，一个多字节字符算作一个单字符。
LENGTH(str)
返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3字节，一个数字或字母算1字节。

CONCAT(s1,s2,…)
返回结果为连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
CONCAT_WS(x,s1,s2,…)
参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

INSERT(s1,x,len,s2)
返回字符串s1，将字符串s1从第x（第一个位置x=1）位置开始，len个字符长的子串替换为字符串s2。如果x超过字符串长度，则返回值为原始字符串。假如len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。
示例：SELECT INSERT('abcde',1,2,'123') 结果：123cde

UPPER(str)或者UCASE(str)
可以将字符串str中的字母字符全部转换成大写字母。
LOWER(str)或者LCASE(str)
可以将字符串str中的字母字符全部转换成小写字母。

RIGHT(s,n)
返回字符串str最右边的n个字符。
LEFT(s,n)
返回字符串s开始的最左边n个字符（第一个位置为1）。

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

LTRIM(s)
返回字符串s，字符串左侧空格字符被删除。
RTRIM(s)
返回字符串s，字符串右侧空格字符被删除。
TRIM(s)
删除字符串s两侧的空格。
TRIM(s1 FROM s)
删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。

REPEAT(s,n)
返回一个字符串s重复n次的字符串。若n<=0，则返回一个空字符串。若s或n为NULL，则返回NULL。
SPACE(n)
返回一个由n个空格组成的字符串。

REPLACE(s,s1,s2)
使用字符串s2替代字符串s中所有的字符串s1。

REVERSE(s)
将字符串s反转，返回的字符串的顺序和s字符串顺序相反。

SUBSTRING(s,n)
返回字符串s从第n个字符开始到末尾的子字符串，如果n是负值，就从倒数第n个字符开始。
SUBSTRING(s,n,len)
返回字符串s从第n个字符开始，长度为len的子字符串，如果n是负值，就从倒数第n个字符开始。如果len为负值，返回空字符串。

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)
返回子字符串str1在字符串str中的开始位置（第一个位置为1）。

3. 聚合函数

COUNT(*)
计算结果返回列中包含的数据行数，不管某列是否有数值或者为NULL。
COUNT(字段名)
计算结果返回列中包含的数据行数，计算时将忽略NULL值的行。

SUM()
是一个求总和的函数，返回指定列值的总和。

AVG()
函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

MAX()
返回指定列中的最大值。
MIN()
返回查询列中的最小值。

SELECT GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep)
返回由属于一组的列值连接而成的结果。

4. 日期和时间函数

1、日期时间格式化
DATE_FORMAT(dt, fmt)  根据format指定的格式显示date值。
fmt值有：
%Y 年，4 位
%m 月，数值(00-12)
%d 月的天，数值(00-31)
%H 小时 (00-23)
%i 分钟，数值(00-59)
%s 秒(00-59)


2、当前日期时间
CURDATE()和CURRENT_DATE()  返回当前的日期。
CURTIME()和CURRENT_TIME()  返回当前的时间。
LOCALTIME()、NOW()、SYSDATE()、CURRENT_TIMESTAMP()4个函数的作用相同，均返回当前日期和时间值。


3、
YEAR(dt)  返回dt对应的年份，范围是1970~2069。
MONTH(dt)  返回dt对应的月份，范围值为1~12。
DAYOFMONTH(dt)  返回dt是一个月中的第几天，范围是1~31。
HOUR(dt)  返回dt对应的小时数，范围是0~23。
MINUTE(dt)  返回dt对应的分钟数，范围是0~59。
SECOND(dt)  返回dt对应的秒数，范围是0~59。
WEEKDAY(dt)+1  返回dt是周几：（加1后）1表示周一，2表示周二，...，7表示周日。

DAYOFYEAR(dt)  返回dt是一年中的第几天，范围是1~366。
WEEK(dt, 7) 查询指定日期是一年中的第几周（1-53），7表示从本年的第一个周一开始计算。


4、日期时间加减
DATE_ADD(dt, INTERVAL expr type) 日期加，dt是一个DATE或DATETIME表达式。
DATE_SUB(dt, INTERVAL expr type) 日期减，dt是一个DATE或DATETIME表达式。
其中type的值有：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEK
示例：
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);


5、日期时间比较
TIMESTAMPDIFF(type, dt1, dt2) 返回dt2减dt1的结果，其中dt1和dt2是DATE或DATETIME表达式。
其中type的值有：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEK
示例：
SELECT TIMESTAMPDIFF(DAY, '2015-01-01 00:00:01', '2015-01-02 00:00:00');
结果：0
SELECT TIMESTAMPDIFF(DAY, '2015-01-01 00:00:01', '2015-01-02 00:00:01');
结果：1


6、秒数时间戳
UNIX_TIMESTAMP()  返回一个UNIX时间戳（即“1970-01-01 00:00:00”之后的秒数）。
UNIX_TIMESTAMP(dt)  返回一个UNIX时间戳（即“1970-01-01 00:00:00”之后的秒数）。
示例：
SELECT UNIX_TIMESTAMP();
结果：1442818062
SELECT UNIX_TIMESTAMP(NOW());
结果：1442818062
SELECT UNIX_TIMESTAMP('2015-01-01');
结果：1420041600
SELECT UNIX_TIMESTAMP('2015-01-01 00:00:00');
结果：1420041600

FROM_UNIXTIME(unix_timestamp)  把UNIX时间戳转换为普通格式的时间。
FROM_UNIXTIME(unix_timestamp, fmt)  把UNIX时间戳转换为普通格式的时间。
示例：
SELECT FROM_UNIXTIME(1420041600);
结果：2015-01-01 00:00:00
SELECT FROM_UNIXTIME(1420041600,'%Y%m%d');
结果：20150101

5. 条件判断函数

IF(expr, v1, v2)  如果表达式expr为true，返回值为v1，否则返回值为v2。
IFNULL(v1,v2) 如果v1不为NULL，返回值为v1，否则其返回值为v2。

CASE expr WHEN v1 THEN r1 ELSE r2 END 
如果expr值等于v1，返回值为r1，否则返回值为r2，其中WHEN和THEN可多次判断。

6. 系统相关函数

SELECT VERSION();  显示MySQL版本。

SELECT USER();  返回当前连接MySQL的客户端IP和端口。

SHOW [FULL] PROCESSLIST;  显示当前[所有]正在连接的线程。
（1）Id：显示客户端登录MySQL时，系统分配的唯一Id。
（2）User：显示客户端使用的哪个用户登录。只有使用root用户登录的才会将所有登录的用户显示出来。
（3）Host：显示这个语句是哪个客户端的IP的哪个端口登录的，可以用来追踪出现问题语句的用户。
（4）db：显示这个线程目前连接的是哪个数据库。
（5）Command：显示当前连接执行的命令，通常取值为休眠（Sleep）、查询（Query）、连接（Connect）。
（6）Time：显示这个状态持续的时间，单位是秒。
（7）State：显示使用当前连接的SQL语句的状态，很重要的列。
（8）Info：显示这个SQL语句，是判断问题语句的一个重要依据。

KILL 89791;  杀掉线程。