2 0 0

计算字段及数据处理函数-常用函数

ZAESKY
2020-1-30 2339

一、数值函数

数值函数是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除以46除以-4NULL除以66除以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()的返回值是01内的小数,并且每次的运行结果都不同,下面通过实例介绍。

实例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)


请先登录后发表评论!

最新回复 (2)
  • admin_ 2020-3-18
    3

    0
  • ZAESKY 2020-2-10
    2

    0
返回
请先登录后发表评论!