MYSQL基础之函数：数值函数和字符串函数

函数是所有计算机编程语言中几乎都有的。一般都有内置和自定义函数，而现在聊内置函数，就是DBMS中直接可以用的函数。但是有一一点需要说，就是DBMS本身就有很多差异，比版本的禅意更大。简单的说就是很少有函数是所有DBMS都支持的，比如DBMS中的+连接字符串，但是在MYSQL中却无法使用其拼接函数是concat()。也就意味着DBMS各自有各自的函数，意味着SQL函数的代码可移植性很差。

而MYSQL中的种类大体分为数值函数，字符串函数，日期和时间函数，流程控制函数，加密和解密函数，以及其它函数。内置函数有根据其作用我们又反单行函数，聚合函数（或分组函数）

数值，字符这样的函数很容易通过字面就可以理解，现在简单说一下单行函数和聚合函数。

• 单行函数
  • 操作数据对象接受参数返回一个结果，支队一行进行交换，每行返回一个结果，可以嵌套使用，参数可以是一列或者一个值。
• 聚合函数
  • 聚合函数对一组值执行计算，并返回单个值，也被称为组函数。

数值函数

还是老规矩先看常用的函数，然后再用代码演示。

基本函数

函数	用户
ABS(x)	返回x的绝对值
SIGN(x)	返回x数值的符号，正数返回1，负数返回-1，0返回0；
PI()	返回圆周率
CEIL(x),CEILING(x)	返回一个大于或者等于某个值的最小正数.
FLOOR(x)	返回一个小于或者等于某个值的最大正数
LEAST(a,b,c,d…)	返回列表中最小的值
GREATEST(a,b,c…)	返回列表中最大的值
MOD(x,y)	返回x处于y的余数
RAND()	返回0-1随机值
RAND(x)	返回0-1随机值，其中x值用户种子值，相同的x值会产生相同的随机数
ROUND(x)	返回x的四舍五入后的小数后的整数值
ROUND(x,d)	返回x的四舍五入后的小数后面d尾的值
SQRT(x)	返回x的平方根。当x的值为负数时，返回null
TRUNCATE(x,y)	截断x为y位小数的值

为了方便演示，直接用伪表和实数进行演示了。

SELECT ABS(-9.2),SIGN(3.5),SIGN(-1),PI(),CEIL(3.4),FLOOR(3.9)   FROM DUAL;

SELECT LEAST(3,5,2,4),GREATEST(3,1,4,6,7),RAND(),RAND(),RAND(100),RAND(100),ROUND(1.01265,3)   FROM DUAL;

可以看出RAND的参数如果是相同的值，那么其返回的随机数就是一样的。

SELECT MOD(2,5),SQRT(9)   FROM DUAL;

SELECT TRUNCATE(3.129,2),TRUNCATE(3,2)   FROM DUAL;

这个可以看出其值不会四舍五入，同样如果后面不足定义的位数就会补充0；

其实最常用的就是基本函数，下面的就是看需求进行了解，知道有这些函数，不行到时候就查询以下即可。虽然不用记，但至少还要知道有这些函数。

三角函数

不过再了解三角函数之前，还需要聊了一个角度和弧度值。

角度很简单，这个无序解释。而弧度值，这个可能需要解释以下，说白就是角度对应的圆弧，然后除以半径的值，比如1rad（弧度值的单位）如下图

因为三角函数的参数对应的是弧度制，所以这个这个需要先看角度与弧度值转换的函数。

函数	作用
RADIANS(x)	将角度值转换位为弧度值
DEGREES(x)	将弧度值转换位为角度值

SELECT RADIANS(60),DEGREES(1),DEGREES(PI()) FROM  DUAL

先看下面

函数	作用
SIN(x)	返回弧度值x正弦值
ASIN(x)	返回正弦值x的弧度值下，如果x的值再-1和1之间返回NULL
COS(x)	返回弧度值x余弦值
ACOS(x)	返回反余弦值x的弧度值，如果x的值再-1和1之间返回NULL
TAN(x)	返回弧度值x正切值
ATAN(x)	返回弧度值x反正切值，返回正切值为x的弧度值
ATAN2(x,y)	返回两个参数的反正切值
COT(x)	返回弧度值为x的余切值

好在这些参数还是采用了数学种三角种的命名，不然又得记很多方法名字。从这也可以看出还是英语学习好的人学编程很有优势。

还是用伪表进行演示。

SELECT SIN(RADIANS(30)),COS(RADIANS(60)),COT(RADIANS(45)),RADIANS(30),ASIN(SIN(RADIANS(30))) FROM DUAL;

其它的函数也类似的转换，所以不在展示了。

指数和对数

指数：指数是幂运算aⁿ(a≠0)中的一个参数，a为底数，n为指数，指数位于底数的右上角，幂运算表示指数个底数相乘。

对数：对数是对求幂的逆运算，正如除法是乘法的逆运算，如果a的x次方等于N（a>0，且a≠1），那么数x叫做以a为底N的对数（logarithm），记作x=log_a N。其中，a叫做对数的底数，N叫做真数。

还是老规矩先看函数，然后具体演示。

函数	作用
POW(x,y) 或者 POWER(x,y)	返回x的次方
EXP(x)	返回一个常量的x次方，而这个常量是2.718281828459
LOG(x) 或者LN(x)	返回以常量（2.718281828459）为底数真数是为X的对数，如果x<=0的时候返回NULL.
LOG(x,y) 或者LN(x,y)	返回以x为底数真数是y的对数，如果x<=0的时候返回NULL.
LOG10(x)或LN10(x)	返回以10为底数真数是x的对数，如果x<=0的时候返回NULL. (MYSQL8.0 才支持)
LOG2(x)或LN2(x)	返回以2为底数真数是x的对数，如果x<=0的时候返回NULL.(MYSQL8.0 才支持)

SELECT POWER(3,2),POW(4,2),EXP(1),LOG(2.718281828459),LOG(3,9),LOG(3,-1) FROM DUAL;

进制转换

进制转换，这个也是编程种不可少的，MYSQL自然也不缺。

函数	作用
BIN(x)	将10进制的x变成2进制
OCT(x)	将10进制的x变成8进制
HEX(x)	将10进制的x变成16进制
CONV(x,f1,f2)	将f1进制的x变成f2进制

SELECT BIN(2),OCT(9),HEX(10) FROM DUAL;

SELECT CONV(10,2,8) FROM DUAL;

字符串函数

字符串的操作，在任何编程语言种都是很重要的一种操作，所以这个部分就聊以下MYSQL种字符串的函数。

函数	作用
ASCII(x)	返回字符串x的ASCII码的值
CHAR_LENGTH(x)或者CHARACTER_LENGTH(x)	返回字符串x的字符数，如果是NULL，返回的还是NULL.
LENGTH(x)	返回字符串x的字节数，和字符集有关，如果是NULL，返回的还是NULL.
CONCAT(x,y,…)	连接符，连接x,y,…作为一个字符串
CONCAT_WS(A, x,y,…)	也是一种连接符，不过连接x,y,…的时候在x后面的字符串例如y,…前面都加上一个A
INSERT(x,start,len,str)	将字符串x从第start位置开始，len个长度的字符串替换为str
REPLACE(x,a,b)	将字符串x种的a替换为b
UPPSER(x) 或者UCASE(x)	将字符串x都转换为大写
LOWER(x)或者LCASE(x)	将字符串x都转换为小写
LEFT(x,n)	返回字符串最左边n个字符
RIGHT(x,n)	返回字符串最右边n个字符
LPAD(x,len,pad)	用字符串pad对str最左边的进行填充，知道str的长度为len个字符
RPAD(x,len,pad)	用字符串pad对str最右边的进行填充，知道str的长度为len个字符
LTRIM(x)	去掉字符串x开始位置的空格
RTRIM(x)	去掉字符串x结束位置的空格
TRIM(x)	去掉字符串x开始和结束位置的的空格
TRIM(s FROM x)	去掉字符串x开始和结束位置的的字符s
TRIM(LEADING s FROM x)	去掉字符串x开始位置的的字符s
TRIM(TRAILING s FROM x)	去掉字符串x结束位置的的字符s
SPACE(n)	返回n个空格
STRCMP(x,y)	比较x,yd ASCII码值,x,大于y返回1，小于返回-1.等于返回0
SUBSTR(x,index,len)或SUBSTING(x,index,len)或者MID(s,index,len)	返回从字符串s的index位置其len个字符串
LOCATE(x,str)或POSITION(x,str)或者INSTR(x,str)	返回从字符串s种第一次出现str的位置，没有返回0
ELT(a,x,y…)	指定返回某个位置的字符串，如果a=1,就返回x的值依次类推
FIELD(a,x,y…)	返回字符串a,在就后面的位置。
FIND_IN_SET(x,y)	返回字符串x在字符串y种出现的位置。y需要用逗号分开，既然后面类似set，所以这个字符串需要右逗号隔开。

还是老规矩用伪表演示。

SELECT ASCII("a"),CHARACTER_LENGTH("我是中国人"),CHAR_LENGTH("abcde"),LENGTH("我是中国人"),LENGTH("abcde")   FROM DUAL; 

SELECT CONCAT("aa","bb"),CONCAT_WS("A","aa","bb","cc"),INSERT("abcd",2,1,"BB"),REPLACE("abcd","a","A"),UCASE("Abcd"),LCASE("Abcd") FROM DUAL;

-- 虽然会用UCASE和LCASE 修改大小写，因为在条件语句种怕错过什么 WHERE UCASE(name)=LILI' 但是mysql对大小写不是很敏感。

SELECT LEFT("abacd",3),RIGHT("abcd",3),LPAD("abcd","10","#"),RPAD("abcd","10","#") FROM  DUAL;

SELECT LTRIM("  abcd"),RTRIM("abcd  "),TRIM(" abcd "),TRIM(LEADING "a" FROM "abcda" ),CHAR_LENGTH( SPACE(3)) FROM DUAL; 

SELECT STRCMP("a","b"),MID("abcd",2,2),LOCATE("ad","abcd"),ELT(2,"a","b","c","d"),FIELD("a","a","b")   FROM DUAL;

SELECT FIND_IN_SET("a","abcd"),FIND_IN_SET("ab","abcd"),FIND_IN_SET("b","abcd"),FIND_IN_SET("b","a,b,c,d"),FIND_IN_SET("a","a,b,c,d"),FIND_IN_SET("a","a,")  FROM DUAL;
-- 从这里就可以看出逗号的意义了，当然如果没逗号

补充

其实在字符串函数种，很多时候操作NULL,是一个异类，比如求长度：

SELECT LENGTH(NULL),LOWER(NULL),CONCAT("a",NULL),CONCAT_WS("A","a",NULL),CONCAT_WS(NULL,"a","b")  FROM DUAL;

所以可以看出，NULL参与了字符串的连接时候，一般的时候会返回NULL值，但是个别是例外，所以在实际使用过程中，其实NULL值一直会影响最后使用者想要的值。
当然上面所写的函数也不一定是最全的，只能说是相对常用的。