计算字段及数据处理函数-常用函数
一、数值函数
数值函数是MySQL中一种很重要的函数，主要用于处理数值方面的运算。如果没有这些函数，用户在编写有关数值运算方面的代码时将会复杂很多。例如，如果没有ABS求绝对值函数，要取一个数的绝对值，就需要进行多次判断，直接使用该函数可以大大提高用户的工作效率。
MySQL中常用的数值函数及其功能
1.1 求绝对值函数
函数ABS(x)的返回值是数值x的绝对值。正数的绝对值是其本身，负数的绝对值是其相反数。
实例1：执行SQL语句，求5，-5和-5.5的绝对值，执行结果如下
mysql> SELECT ABS(5),ABS(-5),ABS(-5.5); +--------------+--------------+----------------+ | ABS(5) | ABS(-5) | ABS(-5.5) | +--------------+--------------+----------------+ | 5 | 5 | 5.5 | +--------------+--------------+----------------+ 1 row in set (0.03 sec)
1.2 求余函数
函数MOD(x,y)的返回值是数值x除以数值y后的余数。与x%y的结果相同，除数和被除数任何一个为NULL，返回结果都将为NULL；除数为0将是非法运算，返回结果为NULL。
实例2：执行SQL语句，求6除以4，6除以-4，NULL除以6，6除以0，及0除以2.5的余数，执行结果如下
mysql> SELECT MOD(6,4),MOD(6,-4), MOD(NULL,6), MOD(6,0),MOD(0,2.5); +--------------+---------------+----------------------------+----------------+----------------+ | MOD(6,4) | MOD(6,-4) | MOD(NULL,6) | MOD(6,0) | MOD(0,2.5) | +--------------+---------------+----------------------------+----------------+----------------+ | 2 | 2 | NULL | NULL | 0.0 | +--------------+---------------+----------------------------+----------------+----------------+ 1 row in set (0.00 sec)
1.3 用于获取整数的函数
MySQL中用于获取整数的函数主要有CEIL(x)和FLOOR(x)，下面分别介绍。
1.3.1 CEIL(x)
函数CEIL(x)的返回值是大于数值x的最小整数值，下面通过实例介绍。
实例3：执行SQL语句，分别求大于数值2.45和-2.45的最小整数值，执行结果如下
mysql> SELECT CEIL(2.45),CEIL(-2.45); +------------------------------+-----------------------------+ | CEIL(2.45) | CEIL(-2.45) | +------------------------------+-----------------------------+ | 3 | -2 | +------------------------------+-----------------------------+ 1 row in set (0.00 sec)
1.3.2 FLOOR(x)
函数FLOOR(x)的返回值是小于数值x的最大整数值，下面通过实例介绍。
实例4：执行SQL语句，求小于数值2.45和-2.45的最大整数值，执行结果如下
mysql> SELECT FLOOR(2.45),FLOOR(-2.45); +------------------------------+-----------------------------+ | FLOOR(2.45) | FLOOR(-2.45) | +------------------------------+-----------------------------+ | 2 | -3 | +------------------------------+-----------------------------+ 1 row in set (0.03 sec)
1.4 获取随机数的函数
函数RAND()的返回值是0～1内的小数，并且每次的运行结果都不同，下面通过实例介绍。
实例5：执行SQL语句，使用函数RAND()获取随机数，执行结果如下
mysql> SELECT RAND(),RAND(),RAND(); +----------------------------------------------+--------------------------------------------+---------------------------------------------+ | RAND() | RAND() | RAND() | +----------------------------------------------+--------------------------------------------+---------------------------------------------+ | 0.32945327863977597 | 0.017887058684497196 | 0.10107548823680308 | +----------------------------------------------+--------------------------------------------+---------------------------------------------+ 1 row in set (0.04 sec)
1.5 四舍五入函数
函数ROUND()的作用是对数值执行四舍五入操作，当函数格式为ROUND(x)时，返回值为整数；当函数格式为ROUND(x,y)时，对数值x进行四舍五入并保留小数点后y位，下面通过实例进行介绍。
实例6：执行SQL语句，使用函数ROUND(x)和ROUND(x,y)对数值进行四舍五入操作，执行结果如下
mysql> SELECT ROUND(100.144),ROUND(100.568),ROUND(100.144,2),ROUND(100.568,2); +------------------------------+-----------------------------+------------------------------+-----------------------------+ | ROUND(100.144) | ROUND(100.568) | ROUND(100.144,2) | ROUND(100.568,2) | +------------------------------+-----------------------------+------------------------------+-----------------------------+ | 100 | 101 | 100.14 | 100.57 | +------------------------------+-----------------------------+------------------------------+-----------------------------+ 1 row in set (0.03 sec)
1.6 截取小数函数
函数TRUNCATE(x,y)的作用是对数值x进行截取，保留小数点后y位。其与ROUND()函数的区别是，ROUND()函数在截取值时会四舍五入；而TRUNCATE(x,y)函数直接截取值，并不进行四舍五入。
实例7：执行SQL语句，使用函数TRUNCATE(x,y)和ROUND(x,y)分别截取数值，执行结果如下
mysql> SELECT TRUNCATE(1.42,1),TRUNCATE(1.58,1),ROUND(1.58,1); +------------------------------+-----------------------------+------------------------------+ | TRUNCATE(1.42,1) | TRUNCATE(1.58,1) | ROUND(1.58,1) | +------------------------------+-----------------------------+------------------------------+ | 1.4 | 1.5 | 1.6 | +------------------------------+-----------------------------+------------------------------+ 1 row in set (0.03 sec)
二、字符串函数
字符串函数是MySQL中使用最频繁的函数，主要用于处理数据库中字符串类型的数据。表10-2列出了MySQL中常用的字符串函数及其功能。
MySQL中常用的字符串函数及其功能
2.1 返回字符串长度和字符串中字符个数的函数
函数LENGTH(str)用于返回字符串的长度，一个汉字占用2个字节，一个英文字符和数字占用1个字节。
实例8：执行SQL语句，使用函数LENGTH(str)返回字符串长度，执行结果如下
mysql> SELECT LENGTH('abcdef'),LENGTH('字符长度');
+-----------------------------+------------------------------+
| LENGTH('abcdef') | LENGTH('字符长度') |
+-----------------------------+------------------------------+
| 6 | 8 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)函数CHAR_LENGTH(str)用于返回字符串中的字符个数。
实例9：执行SQL语句，使用函数CHAR_LENGTH(str)返回字符串中的字符个数，执行结果如下
mysql> SELECT CHAR_LENGTH('abcdef'),CHAR_LENGTH('字符个数');
+---------------------------------------------+---------------------------------------------+
| CHAR_LENGTH('abcdef') | CHAR_LENGTH('字符个数') |
+---------------------------------------------+---------------------------------------------+
| 6 | 4 |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)2.2 合并字符串的函数
函数CONCAT(str1,str2…strn)可以将多个字符串拼接成为一个字符串，但如果参数中有一个NULL值，则返回结果都将为NULL。
实例10：执行SQL语句，使用函数CONCAT(str1,str2…strn)拼接字符串，执行结果如下
mysql> SELECT CONCAT('abcd','efg'),CONCAT('abcd',NULL,'efg');
+------------------------------+---------------------------------------------+
| CONCAT('abcd','efg') | CONCAT('abcd',NULL,'efg') |
+------------------------------+---------------------------------------------+
| abcdefg | NULL |
+------------------------------+---------------------------------------------+
1 row in set (0.02 sec)函数CONCAT_WS(x,str1,str2…strn)是函数CONCAT(str1,str2…strn)的特殊形式，作用是以第一个参数为分隔符，连接后面的多个字符串。
实例11：执行SQL语句，使用函数CONCAT_WS(x,str1,str2…strn)拼接字符串，执行结果如下
mysql> SELECT CONCAT_WS('_','ab','cd','ef'),CONCAT_WS('_','gh',NULL,'ij');
+---------------------------------------------+----------------------------------------------+
| CONCAT_WS('_','ab','cd','ef') | CONCAT_WS('_','gh',NULL,'ij') |
+---------------------------------------------+----------------------------------------------+
| ab_cd_ef | gh_ij |
+---------------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)提示：由执行结果可以看出，函数CONCAT_WS(x,str1,str2…strn)会忽略分隔符后的NULL值，但如果分隔符为NULL，则返回结果为NULL。
2.3 替换字符串的函数
函数INSERT(str,x,y,instr)的作用是将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。
实例12：执行SQL语句，使用函数INSERT(str,x,y,instr)把字符串“beijinglvyoushichang”从第13个字符开始后面的8个字符替换为字符串“gonglue”，执行结果如下
mysql> SELECT INSERT('beijinglvyoushichang',13,8,'gonglue');
+----------------------------------------------------------------------------+
| INSERT('beijinglvyoushichang',13,8,'gonglue') |
+----------------------------------------------------------------------------+
| beijinglvyougonglue |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)函数REPLACE(str,a,b)也可以替换字符串，作用是使用字符串b替换字符串str中的子串a。
实例13：执行SQL语句，使用函数REPLACE(str,a,b)把字符串“abcabc”中的子串“abc”替换为字符串“you”，执行结果如下
mysql> SELECT REPLACE('abcabc','abc','you');
+---------------------------------------------+
| REPLACE('abcabc','abc','you') |
+---------------------------------------------+
| youyou |
+---------------------------------------------+
1 row in set (0.00 sec)2.4 字母大小写转换函数
函数LOWER(str)用于将字符串str中的字母全部转换为小写字母，函数UPPER(str)用于将字符串str中的字母全部转换为大写字母。
实例14：执行SQL语句，分别使用函数LOWER(str)和UPPER(str)把字符串“aBcD”转换为小写字母和大写字母，执行结果如下
mysql> SELECT LOWER('aBcD'),UPPER('aBcD');
+------------------------------+-----------------------------+
| LOWER('aBcD') | UPPER('aBcD') |
+------------------------------+-----------------------------+
| abcd | ABCD |
+------------------------------+-----------------------------+
1 row in set (0.07 sec)2.5 获取指定长度字符串的函数
函数LEFT(str,x)用于获取字符串str中最左边的x个字符，函数RIGHT(str,x)用于获取字符串str中最右边的x个字符。
实例15：执行SQL语句，分别使用函数LEFT(str,x)和RIGHT(str,x)获取字符串“beijinglvyougonglue”左边和右边的7个字符，执行结果如下
mysql> SELECT LEFT('beijinglvyougonglue',7),RIGHT('beijinglvyougonglue',7);
+---------------------------------------------+---------------------------------------------+
| LEFT('beijinglvyougonglue',7) | RIGHT('beijinglvyougonglue',7) |
+---------------------------------------------+---------------------------------------------+
| beijing | gonglue |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.02 sec)函数SUBSTRING(str,x,y)用于获取字符串str中从x位置开始，后面y个字符长度的子串。该函数常用于在给定字符串中提取子串。
实例16：执行SQL语句，使用函数SUBSTRING(str,x,y)获取字符串“beijinglvyougonglue”从第8个字符开始，后面的5个字符，执行结果如下
mysql> SELECT SUBSTRING('beijinglvyougonglue',8,5);
+-------------------------------------------------------------+
| SUBSTRING('beijinglvyougonglue',8,5) |
+-------------------------------------------------------------+
| lvyou |
+-------------------------------------------------------------+
1 row in set (0.00 sec)2.6 填充字符串的函数
函数LPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最左边进行填充，直到字符串str1总长度达到n个字符长度。如果str1的字符长度大于或等于n，则不填充。
实例17：执行SQL语句，使用字符串km对字符串“abcdefg”最左边进行填充，直到字符串达到5个或10个字符长度。执行结果如下
mysql> SELECT LPAD('abcdefg',5,'km'),LPAD('abcdefg',10,'km');
+---------------------------------------------+---------------------------------------------+
| LPAD('abcdefg',5,'km') | LPAD('abcdefg',10,'km') |
+---------------------------------------------+---------------------------------------------+
| abcde | kmkabcdefg |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)函数RPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最右边进行填充，直到字符串str1总长度达到n个字符长度。
实例18：执行SQL语句，使用字符串km对字符串“abcdefg”最右边进行填充，直到字符串达到5个或10个字符长度。执行结果如下
mysql> SELECT RPAD('abcdefg',5,'km'),RPAD('abcdefg',10,'km');
+---------------------------------------------+---------------------------------------------+
| RPAD('abcdefg',5,'km') | RPAD('abcdefg',10,'km') |
+---------------------------------------------+---------------------------------------------+
| abcde | abcdefgkmk |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.02 sec)2.7 删除字符串中空格的函数
函数LTRIM(str)用于删除字符串左侧的空格字符，函数RTRIM(str)用于删除字符串右侧的空格字符。
实例19：执行SQL语句，验证函数LTRIM(str)和RTRIM(str)的应用。执行结果如下
mysql> SELECT CONCAT('ab',' cd ','ef') AS str1,
-> CONCAT('ab',LTRIM(' cd '),'ef') AS str2,
-> CONCAT('ab',' cd ','ef') AS str3,
-> CONCAT('ab',RTRIM(' cd '),'ef') AS str4;
+--------------+--------------+--------------+-------------+
| str1 | str2 | str3 | str4 |
+--------------+--------------+--------------+-------------+
| ab cd ef | abcd ef | ab cd ef | ab cdef |
+--------------+--------------+--------------+-------------+
1 row in set (0.03 sec)提示：上述语句中，字符串“cd”两侧各有一个空格。在语句CONCAT('ab',LTRIM(' cd '),'ef') AS str2中，函数LTRIM(' cd ')将cd左侧的空格删除了，所以语句执行结果为abcd ef；在语句CONCAT('ab',RTRIM(' cd '),'ef') AS str4中，函数RTRIM(' cd ')将cd右侧的空格删除了，所以语句执行结果为ab cdef。
函数TRIM(str)用于删除字符串开头和结尾的空格，另外，它还可以删除字符串两侧的指定字符。
实例20：执行SQL语句，验证函数TRIM(str)的应用。执行结果如下
mysql> SELECT CONCAT('ab',' cd ','ef') AS str1,
-> CONCAT('ab',TRIM(' cd '),'ef') AS str2,
-> TRIM('a' from 'aabacaa') AS str3;
+--------------+--------------+--------------+
| str1 | str2 | str3 |
+--------------+--------------+--------------+
| ab cd ef | abcdef | bac |
+--------------+--------------+--------------+
1 row in set (0.00 sec)可以看到，在语句CONCAT('ab',TRIM(' cd '),'ef') AS str2中，函数TRIM(' cd ')将cd两侧的空格全部删除了，所以合并字符串的结果为abcdef；在语句TRIM('a' from 'aabacaa') AS str3中，函数TRIM('a' from 'aabacaa')将字符串aabacaa两侧的指定字符a全部删除了。
2.8 重复生成字符串的函数
函数REPEAT(str,n)返回字符串str重复n次的结果。
实例21：执行SQL语句，验证函数REPEAT(str,n)的应用。执行结果如下
mysql> SELECT REPEAT('abc ',3);
+------------------------------+
| REPEAT('abc ',3) |
+------------------------------+
| abc abc abc |
+------------------------------+
1 row in set (0.01 sec)提示：此处c后面有一个空格。
2.9 获取字符串中子串开始位置的函数
函数LOCATE(str1,str)返回子串str1在字符串str中的开始位置，返回值的最小值为1，如果字符串str中不包含字符串str1，则返回0。
实例22：执行SQL语句，验证函数LOCATE(str1,str)的应用。执行结果如下
mysql> SELECT LOCATE('abc','ababcabd'),LOCATE('efg','ababcabd');
+---------------------------------------------+---------------------------------------------+
| LOCATE('abc','ababcabd') | LOCATE('efg','ababcabd') |
+---------------------------------------------+---------------------------------------------+
| 3 | 0 |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.04 sec)2.10 反转字符串的函数
函数REVERSE(str)返回将字符串str中字符倒序排列后的结果。
实例23：执行SQL语句，验证函数REVERSE(str)的应用。执行结果如下
mysql> SELECT REVERSE('abcdefg');
+------------------------------+
| REVERSE('abcdefg') |
+------------------------------+
| gfedcba |
+------------------------------+
1 row in set (0.00 sec)