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表达式

    引用和评论

    推荐阅读
    头像
    面试

    阿南阅读 889

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

    huan1993赞 7阅读 1k

    头像
    Mysql数据库sql优化案例

    杜若赞 4阅读 1.9k

    头像
    MySQL 亿级数据平滑迁移实战

    vivo互联网技术赞 2阅读 812

    头像
    MySQL间隙锁，next-key锁

    杜若赞 2阅读 9.8k评论 1

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

    Bytebase赞 3阅读 368

    头像
    sqlmap注入语句学习，帮助你排查SQL注入漏洞

    TANKING赞 2阅读 431

    0 条评论
    得票最新