1 数学函数
1.1 绝对值、平方根、求余函数
(1)ABS(X),返回X的绝对值;
(2)SQRT(X),返回非负数X的二次方根;
(3)MOD(X,Y),返回X被Y除后的余数,MOD()对于带有小数部分的数值也起作用,它返回除法运算后的精确余数。
mysql> SELECT abs(-3.3),sqrt(9),sqrt(-49),mod(31,8);
+-----------+---------+-----------+-----------+
| abs(-3.3) | sqrt(9) | sqrt(-49) | mod(31,8) |
+-----------+---------+-----------+-----------+
| 3.3 | 3 | NULL | 7 |
+-----------+---------+-----------+-----------+
1 row in set
1.2 进一取整、舍一取整函数
(1)CEIL(X)和CEILING(X),返回不小于x的最小整数值,返回值转化为一个BIGINT。
(2)FLOOR(X)返回不大于X的最大整数数值,返回值转化为一个BIGINT。
mysql> SELECT CEIL(3.35),CEIL(-3.35),FLOOR(-3.35),FLOOR(3.35);
+------------+-------------+--------------+-------------+
| CEIL(3.35) | CEIL(-3.35) | FLOOR(-3.35) | FLOOR(3.35) |
+------------+-------------+--------------+-------------+
| 4 | -3 | -4 | 3 |
+------------+-------------+--------------+-------------+
1 row in set
1.3 四舍五入、截断函数
(1)ROUND(X),对X的值进行四舍五入。
mysql> SELECT ROUND(-1.14),ROUND(-1.76),ROUND(1.66);
+--------------+--------------+-------------+
| ROUND(-1.14) | ROUND(-1.76) | ROUND(1.66) |
+--------------+--------------+-------------+
| -1 | -2 | 2 |
+--------------+--------------+-------------+
1 row in set
(2)ROUND(X,Y),返回最接近于参数X的数,其值保留到小数点后面Y位,若Y为负值,则将保留X值到小数点左边Y位。
mysql> SELECT ROUND(1.38,1),ROUND(1.38,0),ROUND(232.38,-1),ROUND(232.38,-2);
+---------------+---------------+------------------+------------------+
| ROUND(1.38,1) | ROUND(1.38,0) | ROUND(232.38,-1) | ROUND(232.38,-2) |
+---------------+---------------+------------------+------------------+
| 1.4 | 1 | 230 | 200 |
+---------------+---------------+------------------+------------------+
1 row in set
(3)TRUNCATE(X,Y),返回被舍去至小数点后Y位的数字X。若Y的值为0,则结果不带有小数点或不带有小数部分。若Y为负数,则截去(归零)X小数点坐起第Y位开始后面所有低位的值。
mysql> SELECT TRUNCATE(1.31,1),TRUNCATE(1.99,1),TRUNCATE(1.99,0),TRUNCATE(19.99,-1);
+------------------+------------------+------------------+--------------------+
| TRUNCATE(1.31,1) | TRUNCATE(1.99,1) | TRUNCATE(1.99,0) | TRUNCATE(19.99,-1) |
+------------------+------------------+------------------+--------------------+
| 1.3 | 1.9 | 1 | 10 |
+------------------+------------------+------------------+--------------------+
1 row in set
1.4 其他函数
1.4.1 获取随机数
RAND()和RAND(X)
当RAND(X)的参数相同时,将产生相同的随机数;如果不带参数X,则产生的随机数值不同。
1.4.2 幂运算
(1)POW(X,Y)或者POWER(X,Y)函数返回X的Y次乘方的结果值;
(2)EXP(X),返回e的X乘方后的值。
1.4.3 其他函数
(1)对数运算函数LOG(X)和LOG10(X)
(2)角度与弧度转换函数RANDIANS(X)和DEGREES(X)
(3)正弦函数SIN(X)和反正弦函数ASIN(X)
(4)余弦函数COS(X)和反余弦函数ACOS(X)
(5)正切TAN(X),反正切ATAN(X)和余切COT(X)
2 字符串函数
2.1 计算字符数和字符串长度
(1)CHAR_LENGTH(str),返回字符串str包含的字符个数。一个多字节字符算作一个单字符。
(2)LENGTH(str),返回字符串字节长度,使用utf8编码字符集时,一个汉字是3个字节,一个数字或字母算一个字节。
mysql> select char_length('JTZen9'),char_length('好好学习');
+-----------------------+-------------------------+
| char_length('JTZen9') | char_length('好好学习') |
+-----------------------+-------------------------+
| 6 | 4 |
+-----------------------+-------------------------+
1 row in set
mysql> select length('JTZen9'),length('好好学习');
+------------------+--------------------+
| length('JTZen9') | length('好好学习') |
+------------------+--------------------+
| 6 | 12 |
+------------------+--------------------+
1 row in set
2.2 合并字符串
(1)CONCAT(str1,str2,str3,……),合并括号内的多个字符串。如果有任何一个参数为NULL,则返回值为NULL。如果所有参数均为非二进制字符串,则结果为非二进制字符串。如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。
mysql> select concat('I','am','JTZen9'),concat('I','am',NULL,'JTZen9');
+---------------------------+--------------------------------+
| concat('I','am','JTZen9') | concat('I','am',NULL,'JTZen9') |
+---------------------------+--------------------------------+
| IamJTZen9 | NULL |
+---------------------------+--------------------------------+
1 row in set
(2)CONCAT_WS(x,str1,str2,……),第一个参数x是其他参数的分隔符,分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串或其他参数。如果分隔符为NULL,则结果为NULL;如果分割符后面参数有NULL,则会被忽略。
mysql> select concat_ws('-','I','am','JTZen9'),concat_ws('_','I','am',NULL,'JTZen9');
+----------------------------------+---------------------------------------+
| concat_ws('-','I','am','JTZen9') | concat_ws('_','I','am',NULL,'JTZen9') |
+----------------------------------+---------------------------------------+
| I-am-JTZen9 | I_am_JTZen9 |
+----------------------------------+---------------------------------------+
1 row in set
2.3 字母大小写转换
(1)LOWER(str)或LCASE(str)将字符串str的字母字符全部转换成小写字母。
(2)UPPER(str)或UCASE(str)将字符串str的字母字符全部转换成大写字母。
mysql> select lower('JTZEN9'),lcase('JTZEN9');
+-----------------+-----------------+
| lower('JTZEN9') | lcase('JTZEN9') |
+-----------------+-----------------+
| jtzen9 | jtzen9 |
+-----------------+-----------------+
1 row in set
mysql> select upper('jtzen9'),ucase('jtzen9');
+-----------------+-----------------+
| upper('jtzen9') | ucase('jtzen9') |
+-----------------+-----------------+
| JTZEN9 | JTZEN9 |
+-----------------+-----------------+
1 row in set
2.4 获取最左最右字符串
(1)LEFT(s,n),返回字符串s开始的最左边n个字符。
(2)RIGHT(s,n),返回字符串s最右边n个字符。
mysql> select left('JTZen9',3),right('JTZen9',4);
+------------------+-------------------+
| left('JTZen9',3) | right('JTZen9',4) |
+------------------+-------------------+
| JTZ | Zen9 |
+------------------+-------------------+
1 row in set
2.5 删除空格
(1)LTRIM(str)删除字符串左侧空格字符;
(2)RTRIM(str)删除字符串右侧空格字符;
(3)TRIM(str)删除字符串左右两侧空格字符,不会删除字符串内部中间的空格字符;
mysql> select length(' JTZen9 ') as col1,
-> length(ltrim(' JTZen9 ')) as col2,
-> length(rtrim(' JTZen9 ')) as col3,
-> length(trim(' JTZen9 ')) as col4;
+------+------+------+------+------+
| col1 | col2 | col3 | col3 | col4 |
+------+------+------+------+------+
| 10 | 8 | 8 | 8 | 6 |
+------+------+------+------+------+
1 row in set
(4)TRIM(s1 FROM s)删除字符串s左右两侧的子字符串s1。
mysql> select trim('hei' from 'heiJTZen9hei');
+---------------------------------+
| trim('hei' from 'heiJTZen9hei') |
+---------------------------------+
| JTZen9 |
+---------------------------------+
1 row in set
2.6 重复生成字符串
REPEAT(s,n),返回一个由重复n次字符串s组成的字符串,若n<=0,则返回一个空字符串。若s或n为NULL,则返回NULL。
mysql> select repeat('JTZen9',3);
+--------------------+
| repeat('JTZen9',3) |
+--------------------+
| JTZen9JTZen9JTZen9 |
+--------------------+
1 row in set
2.7 替换函数
(1)REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。
mysql> select replace('JTZen999','9','g') as col1,
-> replace('JTZen999','999','g') as col2;
+----------+--------+
| col1 | col2 |
+----------+--------+
| JTZenggg | JTZeng |
+----------+--------+
1 row in set
(2)INSERT(s1,x,len,s2),返回字符串s1,其子字符串起始于x位置和被字符串s2取代的len字符。
mysql> select insert('JTxxxx',3,4,'Zen9');
+-----------------------------+
| insert('JTxxxx',3,4,'Zen9') |
+-----------------------------+
| JTZen9 |
+-----------------------------+
1 row in set
2.8 获取子串函数
SUBSTRING(str,n,len),从字符串str返回一个长度同len字符相同的子字符串,起始于位置n。
mysql> select substring('JTZen9',3) as col1,
-> substring('JTZen9',3,3) as col2,
-> substring('JTZen9',-4) as col3,
-> substring('JTZen9',-6,3) as col4;
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| Zen9 | Zen | Zen9 | JTZ |
+------+------+------+------+
1 row in set
2.9 其他函数
2.9.1 匹配子串开始位置
LOCATE(str1,str)、POSITION(str1 IN str)和INSERT(str,str1)3个函数作用相同,返回子字符串str1在字符串str中的开始位置。
2.9.2 字符串逆序
REVERSE(s),将字符串s反转。
2.9.3 比较字符串大小
STRCMP(s1,s2),所有字符串相等,返回0;第一个参数小于第二个,则返回-1,其他情况返回1。
2.9.4 空格函数
SPACE(n),返回由n个空格组成的字符串。
2.9.5 填充字符串
LPAD(s1,len,s2)返回字符串s1,其左边由字符串s2填补到len字符长度。假如s1的长度大于len,则返回值被缩短至len字符。