MySQL 之 - MySQL函数

头像
阿南
    阅读 10 分钟
    1
    MySQL提供了众多功能强大、方便易用的函数。使用这些函数，可以极大的提高用户对数据库的管理效率。MySQL中的函数包括：数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数等其他函数。下面介绍MySQL中的这些函数的功能和用法。

    一. MySQL函数简介

    函数表示对输入参数值返回一个具体特定关系的值，MySQL提供了大量丰富的函数，在进行数据库的管理以及数据的查询操作时将会经常用到各种函数。通过对数据的处理，数据库功能变得更加强大，更加灵活的满足不同用户的需求。各类函数从功能方面主要分为以下几类：数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数等其他函数。

    1.1 数学函数

    数学函数主要用来处理数值数据，主要的数学函数有：绝对值函数、三角函数（包括正弦函数、余弦函数，正切函数、余切函数等）、对数函数、随机数函数等。再有错误产生时，数学函数会返回空值NULL。下面介绍各种数学函数的功能和用法。
    ABS（x）返回X的绝对值

    求2，-2.3，-34的绝对值，SQL语句：

    SELECT ABS(2)，ABS(-2.2)，ABS(-34);

    clipboard.png

    求圆周率的值：(结果保留了7位有效数字)

    SELECT PI();

    clipboard.png

    1.2 平方根函数SQRT(x)和求余函数MOD(x,y)

    SQRT(x)返回非负数x的二次方根

    求9,40，-49的二次方根，SQL如下：

    SELECT SQRT(9),SQRT(40),SQRT(-49);

    clipboard.png

    MOD(x,y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，他返回除法运算后的精确余数。

    对MOD(31,8),MOD(234,10),MOD(45.5,6)进行求余预算，SQL如下：

    SELECT MOD(31,8),MOD(234,10),MOD(45.5,6);

    clipboard.png

    1.3 获取整数的函数CEIL(x),CEILING(x)和FLOOR(x)

    CEIL(x)和CEILING(x)返回不小于x的最小整数值，返回值转化为一个BIGINT。

    使用CEILING函数返回最小整数值，SQL如下：

    SELECT CEIL(-3.35),CEILING(3.35);

    clipboard.png

    FLOOR(x)返回不大于x的最大正整数，转化为一个BIGINT。

    使用FLOOR(x)返回不大于x的最大整数，SQL如下：

    SELECT FLOOR(-3.35),FLOOR(3.35);

    clipboard.png

    1.4 获取随机数的函数RAND()和RAND(x)

    RAND(x)返回一个随机浮点值v，范围在0-1之间（即0<=v<=1.0）,若已经指定一个整数参数x，则它被用作种子值，用来产生重复序列。

    使用RAND()函数产生随机数，SQL如下：

    SELECT RAND(),RAND(),RAND();

    clipboard.png

    
可以看到，不带参数的RAND()每次产生的随机数是不同的。

    使用RAND(x)函数产生随机数，SQL如下：

    SELECT 
    RAND(10),RAND(10),RAND(11),RAND(11);

    clipboard.png

    可以看到，当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数不同。

    1.5 四舍五入函数ROUND(x)、ROUND(x,y)、TRUNCATE(x,y)

    ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。
    使用ROUND(X)函数对操作数进行四舍五入，输入语句如下：
     SELECT ROUND(-1.14),ROUND(-1.67),ROUND(1.14),ROUND(1.66);

    image.png

    可以看到，四舍五入处理之后，只保留了各个值得整数部分
ROUND(X,Y)返回最接近于X的数，其值保留到小数点后Y位，若Y为负值，则将保留X值到小数点左边Y位。


     SELECT ROUND(1.38,1),ROUND(1.38,0),ROUND(232.38,-1),ROUND(232.38,-2);

    image.png

    ROUND(1.38,1)保留小数点后面1位，四舍五入后的结果为1.4；
ROUND(1.38,0)保留小数点后0位，即返回四舍五入之后的整数值；
ROUND(232.38，-1)和ROUND(232.38，-2)分别保留小数点后1位和2位。
注释：Y为负值时，保留的小数点左边的相应位数直接保存为0，不进行四舍五入。

    TRUNCATE(X,Y)

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

     SELECT TRUNCATE(1.31,1),TRUNCATE(1.99,1),TRUNCATE(1.99,0),TRUNCATE(19.99,-1)

    image.png

    round(x,y)会进行四舍五入，TRUNCATE(x,y)不会进行四舍五入。

    1.6符号函数SIGN(x)

    SIGN(x)返回参数的符号，x的值为负，0或正时返回的结果依次是-1,0,1。


    SELECT SIGN(-21),SIGN(0),SIGN(21);

    image.png

    1.7幂运算函数POW(x,y)，POWER(x,y)，EXP(x)

    POW(x,y)或者POWER(x,y)函数依次返回x的y次方结果值。


    SELECT POW(2,2),POWER(2,2),POW(2,-2),POWER(2,-2);

    image.png

    二.字符串函数

    2.1 计算字符串字符数的函数，和字符串长度的函数

    CHAR_LENGTH(str)返回的是字符串str所包含的字符个数。一个多字节字符算一个单字符。
        SELECT CHAR_LENGTH('date'),CHAR_LENGTH('egg');

    image.png

    2.2 合并字符串函数CONCAT(s1,s2,...)，CONCAT_WS(x,s1,s2,...)

    CONCAT(s1,s2,...)返回结果为连接参数产生的字符串，或许有一个或多个参数。如果有任何一个参数为NULL，则返回NULL值。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任何一个二进制字符串，则结果为一个二进制字符串。
    CONCAT(s1,s2,...)，
    SELECT CONCAT('mysql','5.6'),CONCAT('MY',NULL,'SQL');

    image.png

    CONCAT('MYSQL',NULL,'SQL')中有一个参数为NULL，因此返回结果为NULL。
    CONCAT_WS(x,s1,s2,...)
    SELECT 
CONCAT_WS('-','1st','2nd','3rd'),CONCAT_WS('*','1st',NULL,'3rd');

    image.png

    `CONCAT('-','1st','2nd','3rd')使用分隔符'-'将3个字符串连接成一个字符串。CONCAT_WS('*','1st',NULL,'3rd')使用分隔符‘*’将字符串连接成一个字符串，同时忽略NULL值。`

    2.3替换字符串的函数INSERT(s1,x,len,s2)

    INSERT(s1,x,len,s2)返回字符串s1，其字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，则返回原始字符串。假如len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。

    SELECT 
 INSERT('Quest',2,3,'What') as col1,
 INSERT('Quest',-1,4,'What') col2,
 INSERT('Quest',3,100,'What') col3;

    image.png

    第一个函数INSERT('Quest',2,3,'What')将'Quest'第2个字符开始长度为4的字符串替换为'What',结果为'QWhat';第二个函数INSERT('Quest',-1,4,'What')中起始位置为-1超出了字符串长度，直接返回原始字符；第三个函数INSERT('Quest',3,100,'What')，替换长度超出了原始字符串长度，则从第3个字符开始截取后面所有的字符，并替换为指定字符What，结果为'QuWhat'。

    2.4 字母大小写转换LOWER(str)，LCASE(str)

    LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换为小写字母。
    SELECT LOWER('HELLO'),LCASE('HELLOWORLD');
SELECT UPPPER('hello'),UCASE('HElloWorld');

    image.png
    image.png

    2.5 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

    LEFT(s,n)返回字符串s，开始的最左边n个字符。
    SELECT LEFT('helloworld', 3);
SELECT RIGHT('helloworld', 3);

    image.png
    image.png

    2.6 删除空格函数 LTRIM(S)，RTRIM(S),TRIM(S)

    2.7 删除指定字符串的函数TRIM(s1 FROM s)

    SELECT TRIM('XY' FROM 'XYXBOXYXXYXY');

    image.png

    删除字符串'XYXBOXYXXYXY'两端的重复字符串'XY'，而中间'XY'并不删除，结果为'XBOXYX'

    2.8 重复生成字符串的函数REPEAT(S,N)

    REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n，若n<=0，则返回一个空字符串。若s或n为NULL，则返回NULL。
    SELECT REPEAT('Mysql', 3);

    image.png

    2.9 空格函数SPACE(n)，替换函数replace(s,s1,s2)

    SELECT REPLACE('xxx,baidu.com','x','w')

    image.png

    2.10 比较字符串大小（长度）的函数STRCMP(s1,s2)

    STRCMP(s1,s2)若所有的字符串均相同，则返回0。若根据当前分类次序，第一个参数长度小于第二个，则返回-1，其他情况返回1.
SELECT STRCMP('test','test2'),STRCMP('test1','test'),STRCMP('test','test')

    image.png

    2.11 获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len),二者作用相同

    SELECT
SUBSTRING('breakfast',5),
SUBSTRING('breakfast',5,3),
SUBSTRING('breakfast',-3),
SUBSTRING('breakfast',-5,3);

    image.png

    2.12 匹配子串开始位置的函数LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)三个函数作用相同。

    SELECT
LOCATE('ball','football'),
POSITION('ball' IN 'football'),
INSTR('football','ball');

    image.png

    2.13 字符串逆序的函数REVERSE(s)

    SELECT
REVERSE('abc');

    image.png

    2.14 返回指定位置的字符串函数ELT(N,str1,str2,str3)

    SELECT
ELT(3,'1st','2nd','3rd'),
ELT(3,'1st','2nd');

    image.png

    2.15 返回指定字符串位置的函数FIELD(s,s1,s2,...)

    SELECT
FIELD('aa','bb','cc','aa','dd');

    image.png

    2.16 返回子串位置的函数FIND_IN_SET(s1,s2)

    SELECT
FIND_IN_SET('Hi','hihi,Hi,Hey,bas');

    image.png

    三.日期和时间函数

    3.0 CAST函数

     Cast(字段名 as 转换的类型 )，其中类型可以为：
 CHAR\[(N)\] 字符型   
DATE  日期型  
DATETIME  日期和时间型  
DECIMAL  float型  
SIGNED  int  
TIME  时间型
```
  SELECT CAST('2012-02-21 12:21:30' as DATE) 
  SELECT CAST('2012-02-21 12:21:30' as DATETIME);
   SELECT CAST('2012-02-21 12:21:30' as TIME);
```
![image.png](/img/bVbCmMY)
![image.png](/img/bVbCmNe)
![image.png](/img/bVbCmNk)

    3.1 获取当前日期的函数和获取当前时间的函数CURDATE()、CURRENT_DATE()

    CURDATE()和CURRENT_DATE()函数作用相同，将当前日期按照YYYY-MM-DD或YYYYMMDD返回。
SELECT CURDATE(),CURRENT_DATE(),CURDATE()+0;

    image.png

    SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0;

    image.png

    3.2 获取当前日期和时间的函数CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

    SELECT
    CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
    image.png

    3.3 时间戳函数UNIX_TIMESTAMP('yyyy-mm-dd 00:00:00')

    SELECT 
UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2019-12-12 12:23:12');

    image.png

    3.4 获取月份的函数MONTH(date)和MONTHNAME(date)

    SELECT
MONTH(NOW()),
MONTHNAME(NOW()),
MONTH('2019-1-22'),
MONTHNAME('2019-1-22');

    image.png

    3.5 获取星期的函数DAYWEEK(d),DAYOFWEEK(d),WEEKDAY(d)

    SELECT 
DAYNAME(NOW()),DAYOFWEEK('2019-12-20') as '周索引',WEEKDAY('2019-12-20') as '日索引';

    image.png

    3.6 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

    3.7 获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)

    3.8 获取年份，嫉妒，小时，分钟，秒的函数YEAR(d),QUARTER(d)，MINUTE(time)，SECOND(time)

    SELECT 
YEAR('19-12-12') '年份',
QUARTER('2019-12-12') '第几个季度',
MINUTE('12:21:20') '分钟',
SECOND('12:21:20') '秒';

    image.png

    3.9 获取日期的指定值函数EXTRACT(type FROM date)

    SELECT
EXTRACT(YEAR FROM '2019-12-20 09:21:12') '年',
EXTRACT(YEAR_MONTH FROM '2019-12-20 09:21:12') '年月',
EXTRACT(DAY_MINUTE FROM '2019-12-20 09:21:12') '日-分钟';

    image.png

    3.10 时间和秒钟转换的函数TIME_TO_SEC(time)

    SELECT TIME_TO_SEC('23:23:00');

    image.png

    SEC_TO_TIME(seconds)返回被转化为小时，分钟，秒数的seconds参数值，其格式为HH:MM:SS或HHMMSS。
SELECT 
SEC_TO_TIME(84180),
SEC_TO_TIME(84180)+0;

    image.png

    3.11 计算日期和时间的函数

    DATE_ADD(),ADDDATE(),DATE_SUB(),SUBDATE(),ADDTIME(),SUBTIME(),DATE_DIFF()
SELECT DATE_ADD('2010-12-31 23:59:59', interval 1 SECOND) '当前时间+1秒';

    image.png
    mysql中计算日期和时间格式

    type值 预期的exp格式
    SECOND SECONDS
    MINUTE MINUTES
    HOUR HOURS
    MONTH MONTHS
    YEAR YEARS
    QUARTER(季度) QUARTERS
    ... ...

    四、条件判断函数

    4.1 IF(expr,v1,v2)函数

    mysql
    阿南
    20 声望7 粉丝
    « 上一篇
    maven - 阿里云镜像 - 提升依赖下载速度
    下一篇 »
    Java8 Lambda表达式

    引用和评论

    推荐阅读
    头像
    面试

    阿南阅读 913

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 2k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.1k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 312

    头像
    高效工程师的七个习惯

    Bytebase赞 3阅读 618

    头像
    Mysql梳理1

    cashew赞 1阅读 2.1k

    头像
    mysql梳理2

    cashew赞 1阅读 1.5k

    0 条评论
    得票最新