Mysql中常用函数的使用示例

场景

基础知识回顾：mysql中常用函数的使用示例。

注：

博客：
https://blog.csdn.net/badao_liumang_qizhi

实现

数学函数

-- ABS(x)返回x的绝对值
SELECT ABS(-1),ABS(2);
-- PI()返回圆周率
SELECT PI();
-- SQRT(x)返回非负数x的二次方根
SELECT SQRT(4);
-- MOD(x,y)返回x被y除之后的余数
SELECT MOD(10,3);
-- CEIL(X) CEILING(X)返回不小于x的最小整数值，返回值转化为一个BIGINT
SELECT CEIL(3.5),CEILING(2.22);
-- FLOOR(X)返回不大于x的最大整数值，返回值转化为一个BIGINT
SELECT FLOOR(3.5);
-- RAND()返回一个随机浮点数v,0<=v<=1
SELECT RAND();
-- RAND(X)返回一个随机浮点值v,0<=v<=1。参数x为整数，被用作种子值，用来产生重复序列
SELECT RAND(1);
-- ROUND(X)返回最接近于参数x的整数，对x值进行四舍五入
SELECT ROUND(3.4);
-- ROUND(X,Y)返回最接近于参数x的值，此值保留到小数点的后面的y位
SELECT ROUND(3.564,2);
-- TRUNCATE(X,Y)返回截去小数点后y位的数值x
SELECT TRUNCATE(3.2565,3);
-- SIGN(X)返回参数x的符号
SELECT SIGN(10),SIGN(-10);
-- POW(X,Y) POWER(X,Y)返回x的y次方
SELECT POW(2,3),POWER(3,2);
-- EXP(X)返回e的x次方
SELECT EXP(2);
-- LOG(X)返回x的自然对数，x相对于基数e的对数
SELECT LOG(8);
-- LOG10(X)返回x的基数为10的对数
SELECT LOG10(10);
-- RADIANS(X)返回参数x由角度转化为弧度的值
SELECT RADIANS(90);
-- DEGREES(X)返回参数x由弧度转化为角度的值
SELECT DEGREES(180);
-- SIN(X)返回x的正弦值
SELECT SIN(3);
-- ASIN(X)返回x的反正弦，即正弦为x的值
SELECT ASIN(0.1411);
-- COS(X)返回x的余弦值
SELECT COS(5);
-- ACOS(X)返回参数x的反余弦，即余弦为x的值
SELECT ACOS(0.14);
-- TAN(X)返回x的正切值
SELECT TAN(10);
-- COT(X)返回x的余切值
SELECT COT(10);

字符串函数

-- CHAR_LENGTH(str)计算字符串字符数函数，返回str中包含的字符个数
SELECT CHAR_LENGTH('BADAO')
-- LENGTH(str)计算字符串长度函数，返回字符串的字节长度
SELECT LENGTH('BADAO')
-- CONCAT(str1,str2,...)合并字符串函数，参数可以是一个或多个
SELECT CONCAT('BADAO','DE','CHENGXVYUAN');
-- CONCAT_WS(separator,str1,str2,...)此函数代表CONCAT With Separator,是CONCAT()的特殊形式
SELECT CONCAT_WS('-','BADAO','DE');
-- INSERT(str,pos,len,newstr)替换字符串函数，返回字符串str，在位置pos起始的len个字符长的子串由字符串newstr代替
SELECT INSERT('BADAODECHENGXVYUAN',3,3,'BA');
-- LOWER(str) LCASE(str)将字符串中的字母转换为小写
SELECT LOWER('BADAO'),LCASE('DE');
-- UPPER(str) UCASE(str)将字符串中的字母转换成大写
SELECT UPPER('badao'),UCASE('de');
-- LEFT(str,len)截取左侧字符串函数，返回str的最左边len个字符
SELECT LEFT('BADAODE',5);
-- RIGHT(str,len)截取右侧字符串函数，返回str的最右边len个字符
SELECT RIGHT('BADAODE',2);
-- LPAD(str,len,padstr)填充左侧字符串函数，返回字符串str的左边由字符串padstr填补到满足len个字符长度
SELECT LPAD('BADAO',7,'-');
-- RPAD(str,len,padstr)填充右侧字符串函数，返回字符串str的右边由字符串padstr填补到满足len个字符长度
SELECT RPAD('BADAO',7,'-');
-- LTRIM(str)删除字符串左侧空格函数
SELECT LTRIM(' BADAO ');
-- RTRIM(str)删除字符串右侧空格函数
SELECT RTRIM(' BADAO ');
-- TRIM(str)删除字符串左右两侧空格函数
SELECT TRIM(' BADAO ');
-- TRIM(s1 from str)删除指定字符串函数，用于删除字符串str中两端包含的子字符串s1
SELECT TRIM('A' FROM 'ABADAOAA');
-- REPEAT(str,count)重复生成字符串函数，返回一个由重复的字符串str组成的字符串，该字符串中str的重复次数是count
SELECT REPEAT('BA',2);
-- SPACE(N)空格函数，返回一个由n个空格组成的字符串
SELECT SPACE(2);
-- REPLACE(str,from_str,to_str)替换函数，使用字符串to_str替换字符串str中所有的子字符串from_str
SELECT REPLACE('BADAODECHENGXVYUANBABA','BA','BB');
-- STRCMP(expr1,expr2)比较字符串大小函数
SELECT STRCMP('12','123'),STRCMP('123','12');
-- SUBSTRING(str,pos,len) MID(str,pos,len)都是获取子字符串的函数，从字符串str中获取一个长度为len的子字符串，起始位置是pos,此函数的len可以省略
SELECT SUBSTR('BADAODE',3,3);
-- LOCATE(substr,str)匹配子字符串中开始位置的函数，返回子字符串substr在字符串str中第一次出现的位置
SELECT LOCATE('DAO','BADAO');
-- POSITION(substr IN str)匹配子字符串开始位置的函数，功能同LOCATE函数，返回子字符串substr在str中的开始位置
SELECT POSITION('DAO' IN 'BADAO');
-- INSTR(str,substr)匹配子字符串开始位置的函数，功能同上面两个函数
SELECT INSTR('BADAO','DAO');
-- REVERSE(str)字符串逆序函数
SELECT REVERSE('BADAO');
-- ELT(N,str1,str2,str3,...)返回指定位置的字符串函数，根据n的取值返回指定的字符串sn
SELECT ELT(2,'BADAO','DEE','CHENGXV');
-- FIELD(str,str1,str2,str3,...)返回指定字符串位置的函数，用于返回字符串str在列表str1、str2等中第一次出现的位置，如果找不到则返回0
SELECT FIELD('A','BDO','DEA','CHENGXVYUAN','A');
-- FIND_IN_SET(str,strlist)返回子字符串位置的函数，用于返回字符串str在字符串列表strlist中出现的位置
SELECT FIND_IN_SET('A','A,B,C');
-- MAKE_SET(bits,str1,str2,...)选取字符串的函数，用于返回一个设定值(一个包含被逗号分开的子字符串的字符串)，由在bits组中具有相应位的字符串组成
SELECT MAKE_SET(2,'A','B','C');

日期和时间函数

-- CURDATE() CURRENT_DATE()返回当前系统的日期值
SELECT CURDATE(),CURRENT_DATE();
-- CURTIME() CURRENT_TIME()返回当前系统的时间值
SELECT CURTIME(),CURRENT_TIME();
-- CURRENT_TIMESTAMP() LOCALTIME() NOW() SYSDATE()这四个函数作用相同，都是返回当前系统的日期和时间值
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
-- UNIX_TIMESTAMP(date)UNIX时间戳函数，返回一个以UNIX时间戳为基础的无符号整数
SELECT UNIX_TIMESTAMP();
-- FROM_UNIXTIME(unix_timestamp)把UNIX时间戳转换为时间格式的函数，与UNIX_TIMESTAMP互为反函数
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
-- UTC_DATE()返回UTC日期函数，用于返回当前UTC(世界标准时间)的日期值。由于时差关系，UTC不一定是当前计算机系统显示的日期值。
SELECT UTC_DATE();
-- UTC_TIME()返回UTC时间函数，用于返回当前UTC(世界标准时间)的时间值。由于时差关系，UTC不一定是当前计算机系统显示的时间值。
SELECT UTC_TIME();
-- MONTH(date) MONTHNAME(date)获取日期参数date中的月份的函数，MONTH(date)返回数值类型，MONTHNAME(date返回字符串类型
SELECT MONTH(CURRENT_DATE()),MONTHNAME(CURRENT_DATE());
-- DAYNAME(date)获取星期的函数，返回日期date对应的星期几的英文名称
SELECT DAYNAME(CURRENT_DATE());
-- DAYOFWEEK(date)获取星期的函数，返回日期date对应的一周的索引位置值
SELECT DAYOFWEEK(CURRENT_DATE());
-- WEEK(date)获取星期的函数，返回日期date对应的工作日索引
SELECT WEEK(CURRENT_DATE());
-- WEEK(date,mode)获取星期数的函数，返回日期参数date在一年中位于第几周。该函数允许指定星期是否起始于周日或者周一，以及返回值的范围是否为0-53或1到53，如果mode参数别省略，
-- 则使用系统自变量default_week_format,默认情况下，default_week_format的默认值为0
SELECT WEEK(CURRENT_DATE(),1),WEEK(CURRENT_DATE(),2);
-- WEEKOFYEAR(date)计算日期参数date是一年中的第几个星期，范围是1-53，相当于WEEK(date,53)
SELECT WEEKOFYEAR(CURRENT_DATE());
-- DAYOFYEAR(date)获取天数的函数，返回日期参数date是一年中的第几天，范围是1-366
SELECT DAYOFYEAR(CURRENT_DATE());
-- DAYOFMONTH(date)获取天数的函数，返回日期参数date是一个月中的第几天，范围是1-31
SELECT DAYOFMONTH(CURRENT_DATE());
-- YEAR(date)获取年份的函数，返回日期参数date对应的年份，范围是1970-2069
SELECT YEAR(CURRENT_DATE());
-- QUARTER(date)返回日期参数对应一年中的季度值，范围1-4
SELECT QUARTER(CURRENT_DATE());
-- MINUTE(time)返回时间参数对应的分钟数，范围0-59
SELECT MINUTE(CURRENT_TIME());
-- SECOND(time)返回时间参数对应的秒数，范围0-59
SELECT SECOND(CURRENT_TIME);
-- EXTRACT(unit FROM date)获取日期时间参数对应的指定类型的函数
SELECT EXTRACT(YEAR FROM CURRENT_DATE),EXTRACT(MONTH FROM CURRENT_DATE);
-- TIME_TO_SEC(time)时间和秒数转换的函数，返回将时间参数time转换为秒数的时间值
SELECT TIME_TO_SEC(CURRENT_TIME);
-- SEC_TO_TIME(seconds)秒数和时间转换的函数，返回将seconds转换为小时、分钟和秒数的时间值
SELECT SEC_TO_TIME(6400);
-- DATE_ADD(date,INTERVAL expr unit) ADDDATE(date,INTERVAL expr unit)加法计算日期函数，返回一个以参数date为起始日期加上时间间隔值之后的日期值，
-- 其中expr是一个字符串，可以是以负号开头的负值时间间隔，type指出了expr被解释的方式
SELECT DATE_ADD(CURRENT_DATE,INTERVAL 7 DAY);
-- DATE_SUB(date,INTERVAL expr unit) SUBDATE(date,INTERVAL expr unit)减法计算日期函数，返回一个以参数date为起始日期减去时间间隔值
-- 之后的日期值
SELECT DATE_SUB(CURRENT_DATE,INTERVAL 7 DAY);
-- ADDTIME(time,expr)加法计算时间值函数，返回将expr值加上原始时间time之后的值
SELECT ADDTIME(CURRENT_TIME,'00:30:00');
-- SUBTIME(time,expr2)减法计算时间值函数，返回将原始时间time减去expr值之后的值
SELECT SUBTIME(CURRENT_TIME,'00:30:00');
-- DATEDIFF(expr1,expr2)计算两个日期时间间隔的函数，返回参数expr1减去expr2之后的值
SELECT DATEDIFF(DATE_ADD(CURRENT_DATE,INTERVAL 7 DAY),CURRENT_DATE);
-- DATE_FORMAT(date,format)将日期和时间格式化的函数，返回根据参数format指定的格式显示的date值
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
-- TIME_FORMAT(time,format)将时间格式化的函数
SELECT TIME_FORMAT(CURRENT_TIME,'%H:%i:%s')
-- GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
-- 获得国家地区时间格式函数，返回值是一个格式字符串,传参选项从上面选择
SELECT GET_FORMAT(DATE,'USA');
SELECT GET_FORMAT(DATETIME,'JIS');

条件判断函数

-- IF(expr1,expr2,expr3)返回表达式expr1得到不用运算结果时对应的值。若expr是TRUE,则返回expr2,否则返回expr3
SELECT IF(1>=0,1,0);
-- IFNULL(expr1,expr2)返回参数expr1或expr2的值，如果expr1不为NULL,则返回expr1,否则返回expr2
SELECT IFNULL(NULL,'AAA'),IFNULL('BBB','CCC');
-- CASE 函数，根据expr的取值返回相应值
 SELECT
 CASE
   WEEKDAY(
   NOW())
   WHEN 0 THEN
   '星期一'
   WHEN 1 THEN
   '星期二'
   WHEN 2 THEN
   '星期三'
   WHEN 3 THEN
   '星期四'
   WHEN 4 THEN
   '星期五'
   WHEN 5 THEN
   '星期六' ELSE '星期天'
  END AS column1;

系统信息函数

-- VERSION()返回当前Mysql版本号的字符串
SELECT VERSION();
-- CONNECTION_ID()返回mysql服务器当前用户的连接次数
SELECT CONNECTION_ID();
-- PROCESSLIST 使用"SHOW PROCESSLIST"显示正在运行的线程，不仅可以查看当前所有的连接数，还可以查看当前的连接状态，帮助用户
-- 识别出有问题的查询语句等。如果是root账户，能看到所有用户的当前连接，如果是普通账号，只能看到自己占用的连接
SHOW PROCESSLIST;
-- DATABASE() SCHEMA()显示目前正在使用的数据库名称
SELECT DATABASE(),SCHEMA();
-- USER() CURRENT_USER() SYSTEM_USER() SESSION_USER()都是获取当前登录用户名的函数。
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
-- CHARSET(str)获取字符串的字符集函数，返回参数字符串str使用的字符集
SELECT CHARSET('AAA');
-- COLLATION(str)返回参数字符串str的排列方式
SELECT COLLATION('ABCD');
-- LAST_INSERT_ID()获取最后一个自动生成的ID值的函数，将自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值
SELECT LAST_INSERT_ID();

数据加密与解密函数

-- PASSWORD(str)加密函数，该函数计算原明文密码str，并返回加密后的密码字符串
-- PASSWORD()在mysql 8.0版本已弃用
SELECT PASSWORD('AAA');
-- MD5(str)加密函数，该函数为参数字符串str计算出一个MD5128位校验和，该值以32位十六进制数字的二进制字符串形式返回
SELECT MD5('BADAO');
-- ENCODE(str,pass_str)加密函数，该函数使用参数pass_str作为密钥，加密参数str
-- 在mysql 8.0版本已弃用
SELECT ENCODE('AAA','BADAO');
-- 在mysql 8.0版本已弃用
-- DECODE(crypt_str,pass_str)解密函数，该函数使用参数pass_str作为密钥，解密参数加密字符串crypt_str

其它函数

-- FORMAT(X,D)格式化函数，该函数将数值参数x格式化，并以四舍五入的方式保留小数点后d位，结果以字符串形式返回
SELECT FORMAT('2.123456',2);
-- CONV(N,from_base,to_base)不用进制的数字进行转换的函数，该函数将数字n从form_base转换到to_base,并以字符串形式返回。
-- 其中，参数n被解释为一个整数，但是也可以被指定为一个字符串。其最小基为2，最大为36
SELECT CONV(10,10,2);
-- INET_ATON(expr)IP地址与数字相互转换的函数，该函数将参数expr(作为字符串的网络地址的点地址)转换成一个代表该地址数值的整数
-- 数字网络地址可以是4位或8位
SELECT INET_ATON('192.168.1.1');
-- INET_NTOA(expr)数字网络地址转换成字符串网络地址函数，该函数将参数expr(数字网络地址，4位或8位)转换成字符串类型的该地址的点地址表示
SELECT INET_NTOA('3232235777');
-- GET_LOCK(str,timeout)加锁函数，该函数使用参数字符串str给定的名字得到一个锁，超时时间位timeout秒。若成功得到锁，返回1；若超时操作，返回0；若发生错误，返回NULL
SELECT GET_LOCK('BADAO',3);
-- RELEASE_LOCK(str)该函数解开被GET_LOCK()获取的用字符串str命名的锁。若锁被解开，返回1；若该线程尚未创建锁，返回0（此时锁没有被解开）
-- 若命名的锁不存在，返回NULL
SELECT RELEASE_LOCK('BADAO');
-- IS_FREE_LOCK(str)该函数检查名为str的锁是否可以使用(也就是说没有被锁)。若锁可以用，返回1；若锁正在被使用，返回0；如出现参数错误，返回NULL
SELECT IS_FREE_LOCK('BADAO');
-- IS_USED_LOCK(str)该函数检查名为str的锁是否正在被使用(也就是说被锁)。若锁正在被锁，返回使用该锁的客户端的连接标识符，否则返回NULL
SELECT IS_USED_LOCK('BADAO');
-- BENCHMARK(count,expr)重复执行指定操作的函数，该函数重复count次执行表达式expr。该函数可以用于计算Mysql处理表达式的速度，
-- 结果值通常为0（0只是表示处理过程很快，并不是没有花费时间）。该函数的另一个作用是在Mysql客户端内部报告语句执行的时间。
SELECT BENCHMARK(1000000,1+1);
-- CONVERT(expr USING transcoding_name)改变字符集函数，该函数可以改变字符串默认的字符集
SELECT CHARSET('BADAO');
SELECT CHARSET(CONVERT('BADAO' USING GBK));
-- CAST(expr AS type) CONVERT(expr,type)改变数据类型的函数，将参数expr由一个类型转换为另外一个类型
SELECT CAST(3.14 AS SIGNED);