MySQL基础- mysql常用函数汇总

geminigoth

已于 2023-09-16 00:03:19 修改

阅读量641

点赞数 1

分类专栏： MySQL系列文章标签： mysql 数据库

于 2023-08-31 22:38:35 首次发布

本文链接：https://blog.csdn.net/geminigoth/article/details/132611802

版权

MySQL系列专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文使用MySQL版本为8.0.11

1.数学函数

数学函数主要用来处理数值数据，主要的数学函数有绝对值函数、三角函数、对数函数、随机函数等。在有错误产生时，数据函数将会返回空值NULL。

1.1 绝对值函数ABS(x)和返回圆周率的函数PI()

ABS(x)返回x的绝对值

【例1.1.1】求2、-3.3和-33的绝对值，输入语句如下：

SELECT ABS(2),ABS(-3.3),ABS(-33);

输出结果为：

【例1.1.2】PI()返回圆周率π的值。默认的显示小数数位是6位。

SELECT PI();

输出为：

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

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

【例1.2.1】求9、 40 、和 -49的二次方根：

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

输出为：

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

【例1.2.2】对(31,8),(234,10),(45.5,6)进行求余运算：

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

输出为：

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

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

【例1.3.1】使用CEILING函数返回最小整数：

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

输出：

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

【例1.3.2】使用FLOOR函数返回最大整数：

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

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

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

【例1.4.1】使用RAND()函数产生随机数：

SELECT RAND() as '随机数1',RAND()  as '随机数2',RAND() as '随机数3';

【例1.4.2】使用RAND(x)产生随机数：

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

输出：

1.5 ROUND(x)/ROUND(x,y)/TRUNCATE(x,y)

ROUND(x) 返回最接近于参数x的整数，对x值进行四舍五入。

【例1.5.1】使用ROUND(x)进行四舍五入：

SELECT ROUND(-1.14),ROUND(-1.67),ROUND(1.14),ROUND(1.66);

输出：

ROUND(x,y)返回最近接于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

【例1.5.1】使用ROUND(x,y)函数进行四舍五入：

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

输出：

【提示】y值为负数时，保留的小数点左边的相应位数直接保存为0，不进行四舍五入。

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

【例1.5.3】使用TURNCATE（x,y）函数对操作数进行操作：

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

输出：

1.6 符号函数SIGN(x)

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

【1.6.1】使用SIGN(x)函数返回参数的符号：

SELECT SIGN(-22),SIGN(0),SIGN(100);

输出：

1.7 幂运算POW(x,y)、POWER(x,y)、EXP(x)

pow(x,y)或者power(x,y)返回x的y次乘方的结果值

【例1.7.1】使用pow(x,y)和power(x,y)函数：

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

输出：

EXP(X)返回e的x乘方后的值

【例1.7.2】使用EXP函数计算e的乘方

SELECT EXP(3),EXP(-3),EXP(0);

输出：

1.8 对数运算运算LOG(x)和LOG10(x)

LOG(x)返回x的自然对数，x相对于基数e的对数

【例1.8.1】使用LOG(x)计算自然对数：

SELECT LOG(3),LOG(-3);

LOG10(x)返回x的基数为10的对数

【例1.8.2】使用LOG10计算以10为基数的对数：

SELECT LOG10(2),LOG10(100),LOG10(-100);

输出：

2.字符串函数

字符串函数主要用来处理数据库中字符串数据。

2.1 计算字符串字符数和字符串长度

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。

【例2.1.1】使用CHAR_LENGTH计算字符串字符个数：

SELECT CHAR_LENGTH('date'),char_length('birthday');

结果：

使用utf8编码时，一个汉字是3字节，一个数字或字母是1字节。

【例2.1.2】使用LENGTH函数计算字符串长度：

SELECT LENGTH('DATE'),LENGTH('abc');

结果：

2.2 合并字符串

CONCAT(s1,s2....)返回结果为连接参数产生的字符串，可能有一个或多个参数。如果有任何一个参数为null，则返回结果为null。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串，

【例2.2.1】使用CONCAT连接字符串：

SELECT CONCAT('MY SQL','8.0'),CONCAT('MY',NULL,'SQL');

结果：

CONCAT_WS(x,s1,s2..)中，CONCAT_WS是CONCAT()特殊形式。第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为null，则结果为null。函数会忽略任何分隔符参数后的null值。

【例2.2.2】使用concat_ws连接带分隔符的字符串：

SELECT CONCAT_WS('-','1st','2nd','3rd'),concat_ws('*','1st',null,'3rd');

结果是：

2.3 替换字符串函数

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

【例2.3.1】使用INSERT函数进行字符串替代操作：

SELECT INSERT('Quest',2,4,'what') as coll1,
       insert('Quest',-1,4,'what') as coll2,
       insert('Quest',3,100,'what') as coll3

输出：

2.4 字母大小写转换

LOWER(str)和LCASE(str)可以将字符串str中的最字符全部转换成小写字母。

【例2.4.1】使用LOWER和LCASE函数：

SELECT  LOWER('ADMIN'),LCASE('Well')

输出：

UPPER(str)和UCASE(str)可以将字符串中的字母全部转换成大写字母

【例2.4.2】使用UPPER和UCASE函数：

SELECT UPPER('abcdefg') , UCASE('ABCdef');

结果：

2.5 获取指定长度的字符串

LEFT(s,n)返回字符串s开始的最左边n个字符。

【例2.5.1】使用LEFT函数返回字符串中左边的字符：

SELECT LEFT('football',5);

结果：

RIGHT(s,n)返回字符串最右边的n个字符。

【例2.5.2】使用RIGHT返回字符串右边的字符：

SELECT RIGHT('football',4);

结果：

2.6 填充字符串LPAD和RPAD

LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符。

【例2.6.1】使用LPAD函数对字符串进行填充：

SELECT LPAD('hello',4,'??'),LPAD('hello',10,'?');

结果：

RPAD(s1,len,s2)返回字符串s1，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到len字符长度。

【例2.6.2】使用RPAD函数对字符串进行填充：

SELECT RPAD('hello',4,'?'),RPAD('hello',10,'?');

结果：

2.7 删除空格函数

LTRIM(s)返回字符串s，字符串左侧空格被删除。

【例2.7.1】使用LTRI函数删除字符串左边空格：

SELECT '( book )',CONCAT('(',LTRIM(' book '),')')

结果：

RTRIM(s)返回字符串s，字符串右边空格被删除

【例2.7.2】使用RTRIM函数删除字符串右边的空格：

SELECT '( book )',CONCAT('(',RTRIM(' book '),')')

结果：

TRIM(s)删除s两侧的空格

【例2.7.3】使用TRIM删除字符串两侧的空格：

SELECT '( book )',CONCAT('(',TRIM(' book '),')')

结果：

2.8 删除指定字符串

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，如果不填，则删除空格。

【例2.8.1】使用TRIM函数删除字符串两端指定的字符：

SELECT TRIM('ab' FROM 'abcdefabcdefab');

结果：

2.9 重复生成字符串函数

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

【例2.9.1】使用REPEAT函数重复生成相同的字符串：

SELECT REPEAT('mysql',3);

结果：

2.10 空格函数和替换函数

SPACE(n)返回一个由n个空格组成的字符串。

【例2.10.1】使用SPACE函数生成有空格组成的字符串：

SELECT CONCAT('(',SPACE(6), ')');

SPACE(6)返回的字符串由6个空格组成。

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

【例2.10.2】使用REPLACE函数进行字符串替代操作：

SELECT REPLACE('xxx.mysql.com','x','w');

2.11 比较字符串大小

STRCMP(s1,s2)：若所有的字符串均相同，则返回0；s1小于s2，则返回-1；s1大于s2,返回1。

【例2.11.1】使用STRCMP函数比较字符串大小：

SELECT STRCMP('TXT','TXT'),STRCMP('txt2','txt'),STRCMP('txt','txt2');

2.12 获取子串的函数

SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度与len字符相同的子字符串，起始于位置n。也可能对n使用一个负值。假若这样，则子字符串的位置起始与字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。

【2.12.1】使用SUBTRING函数获取指定位置处的子字符串：

SELECT SUBSTRING('breakfast',5) as coll1,
       SUBSTRING('breakfast',5,3) as coll2,
       SUBSTRING('lunch',-3) as coll3,
       SUBSTRING('lunch',-5,3) as coll4;

结果：

2.13 匹配子串开始位置

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str,str1)3个函数作用相同，返回子字符串str1在字符串str中的开始位置。

【例2.13.1】分别使用LOCATE,POSITION,INSTR函数查找字符串中指定子字符串的开始位置：

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

结果：

2.14 字符串逆序函数

REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相反。

【2.14.1】使用REVERSE函数反转字符串。

SELECT REVERSE('abcdefg');

结果：

2.15 返回指定位置的字符串

ELT(N,str1,str2,...,str N)：N =1,则返回值为字符串1；若N = 2,则返回值为字符串2；以此类推；若N小于或大于参数的数目，则返回null。

【例2.15.1】使用ELT函数返回指定位置字符串：

SELECT ELT(3,'1ST','2ND','3RD'),ELT(3,'NET','OS');

结果：

2.16 返回指定字符串位置

FIELD(s,s1,s2,...,sn)返回字符串s在列表s1,s2,...,sn中第一次出现的位置，在找不到s的情况下，返回值为0。如果s为null，则返回值是0，原因是null不能同任意值进行同等比较。

【例2.16.1】使用FIELD函数返回指定字符串第一次出现的位置：

SELECT FIELD('hi','hihi','hey','hi','bas') as coll1,
       FIELD('hi','hey','lo','hello') as coll2;

结果：

3.日期和时间函数

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者DATESTAMP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但会忽略日期部分，许多日期函数可以同时接受数字和字符串类型的两种参数。

3.1 获取当前日期和当前时间

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。

【例3.1.1】获取系统当前日期：

SELECT CURDATE(),CURRENT_DATE,CURDATE() + 0;

结果：

【例3.1.2】获取系统当前时间：

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

结果：

3.2获取当前日期和时间

CURRENT_TIMESTAMP、LOCALTIME、NOW和SYSDATE4个函数的作用相同，均返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’ 或 YYYYMMDDHHMMSS

【例3.2.1】获取当前系统的日期和时间：

SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();

结果：

3.3 UNIX时间戳函数

UNIX_TIMESTAMP(date)如果没有参数，则返回一个UNIX时间戳('1970-01-01 00:00:00' GMT 之后的秒数)作为无符号整数。其中， GMT为格林尼治标准时间。若用date来调用UNIX_TIMESTAMP(),它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYYYMMDD或YYYYMMDD格式的数字。

【3.3.1】使用UNIX_TIMESTAMP函数返回UNIX格式的时间戳：

SELECT UNIX_TIMESTAMP() AS  TIME1,
       UNIX_TIMESTAMP(NOW()) AS TIME2,
       NOW() AS TIME3;

结果：

FROM_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP(date)函数互为反函数。

【例3.3.2】使用FROM_UNIXTIME函数将UNIX时间戳转换为普通格式时间：

SELECT FROM_UNIXTIME('1693653527');

结果：

3.4 返回UTC日期和返回UTC时间

UTC_DATE()函数返回当前UTC日期值，格式为‘YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。

【3.4.1】使用UTC_DATE函数返回当前UTC日期值：

SELECT UTC_DATE(),UTC_DATE + 0;

结果：

UTC_TIME()返回当前UTC时间值，其格式为‘HH:MM:SS’或HHMMSS，具体格式取决于函数是否用在字符串或数字语境中。

【例3.4.2】使用UTC_TIME函数返回当前UTC时间值：

SELECT UTC_TIME,UTC_TIME + 0;

结果：

3.5 获取月份

MONTH(date)函数返回date对应的月份，范围值1~12.

【例3.5.1】使用MONTH函数返回指定日期中的月份：

SELECT MONTH('2023-09-02');

结果：

MONTHNAME(date)返回日期date对应月份的英文全称：

SELECT MONTHNAME('2023-09-02');

结果：

3.6 获取星期函数

DAYNAME(d)函数返回d对应的工作日英文名称。

【例3.6.1】使用DAYNAME函数返回指定日期的工作日名称：

SELECT DAYNAME('2023-09-02');

结果：

DAYOFWEEK(d)函数返回d对应的一周中的索引（1表示周日，.....，7表示周六）。

【例3.6.2】使用DAYOFWEEK返回日期对应的周索引：

SELECT DAYOFWEEK('2023-09-02');

结果：

3.7 获取星期数

WEEK(d)计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始与周日或周一，以及返回值的范围是否为0~53或1~53。

【例3.7.1】使用WEEK()函数查询指定日期是一年中的第几周：

SELECT WEEK('2023-09-02'),WEEK('2023-09-02',0),WEEK('2023-09-02',2);

结果：

3.8 获取年份、季度、小时、分钟、秒

YEAR(date)：返回date对应的年份，范围是1970~2069。

QUARTER(date)：返回date对应的一年中的季度，范围1~4。

HOUR(time)：返回对应的小时，范围0~23。

MINUTE(date)：返回time对应的分钟，范围0~59。

SECOND(time)：返回time对应的秒数。

【例3.8.1】分别使用year,quarter,hour,minute,seconde 获取对应的结果：

SELECT YEAR('2023-09-02') AS '年',
       quarter('2023-09-02') as '季度',
       hour('2023-09-02 18:12:30') as '小时',
       minute('2023-09-02 01:20:03') as '分钟',
       second('2023-09-02 02:02:10') as '秒';

结果：

4.条件判断函数

4.1 IF(expr,v1,v2)函数

IF(expr,v1,v2)：如果表达式expr是true(expr<> 0 and expr<> null)，则返v1；否则返回值v2。IF()的返回值为数字或者空字符串，具体情况视其所在语境而定。

【例4.1.1】使用IF()函数进行条件判断：

SELECT IF(1 > 2,2,3),
       IF(1<2,'yes ','no');

结果：

4.2 IFNULL(v1,v2)函数

IFNULL(v1,v2)：假如v1不为null，则ifnull()的返回值为v1；否则返回值为v2。

【例4.2.1】使用IFNULL()函数进行条件判断：

SELECT IFNULL(1,2),
       IFNULL(NULL,10),
       IFNULL(1/0,'wrong');

结果：

4.3 CASE函数

CASE expr WHEN v1 THEN r1

[WHEN v2 THEN r2]...

[ELSE rn+1] END:如果expr值等于某个vn，则返回对应位置THEN 后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1。

【例4.3.1】使用CASE value WHEN语句执行分支操作。

SELECT CASE 3 WHEN 1 THEN 'ONE'
              WHEN 2 THEN 'TWO'
        ELSE 'MORE' END;

结果：

5.MySQL 8.0新特性-加密函数

5.1 加密函数MD5

MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位16进制数组的二进制字符串形式返回。

【例5.1.1】使用MD5加密：

SELECT MD5('password');

输出：

5.2 加密函数SHA(str)

SHA(str)从原明文密码str计算并返回加密后的密码字符串。SHA加密算法比MD5更加安全。

【例5.2.1】使用SHA加密：

SELECT sha('password');

结果：

5.3 加密函数SHA2(str,hash_length)

SHA2()使用hash_length作为长度，加密str。hash_length支持的值为224,256,384,512,0。其中0等同于256。

【例5.3.1】使用SHA2加密

SELECT SHA2('PASSWORD',0) AS 'A',
       SHA2('PASSWORD',512) AS 'B' ;

输出：

geminigoth

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL基础- mysql常用函数汇总

本文使用MySQL版本为8.0.11。
复制链接

扫一扫

专栏目录

MySQL基础- mysql常用函数汇总

1.数学函数

1.1 绝对值函数ABS(x)和返回圆周率的函数PI()

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

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

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

1.5 ROUND(x)/ROUND(x,y)/TRUNCATE(x,y)

1.6 符号函数SIGN(x)

1.7 幂运算POW(x,y)、POWER(x,y)、EXP(x)

1.8 对数运算运算LOG(x)和LOG10(x)

2.字符串函数

2.1 计算字符串字符数和字符串长度

2.2 合并字符串

2.3 替换字符串函数

2.4 字母大小写转换

2.5 获取指定长度的字符串

2.6 填充字符串LPAD和RPAD

2.7 删除空格函数

2.8 删除指定字符串

2.9 重复生成字符串函数

2.10 空格函数和替换函数

2.11 比较字符串大小

2.12 获取子串的函数

2.13 匹配子串开始位置

2.14 字符串逆序函数

2.15 返回指定位置的字符串

2.16 返回指定字符串位置

3.日期和时间函数

3.1 获取当前日期和当前时间

3.2获取当前日期和时间

3.3 UNIX时间戳函数

3.4 返回UTC日期和返回UTC时间

3.5 获取月份

3.6 获取星期函数

3.7 获取星期数

3.8 获取年份、季度、小时、分钟、秒

4.条件判断函数

4.1 IF(expr,v1,v2)函数

4.2 IFNULL(v1,v2)函数

4.3 CASE函数

5.MySQL 8.0新特性-加密函数

5.1 加密函数MD5

5.2 加密函数SHA(str)

5.3 加密函数SHA2(str,hash_length)

“相关推荐”对你有帮助么？