MySQL单行函数的介绍和使用

2022-08-21 11:07:50

MySQL——单行函数

1、MySQL 中函数的分类

在SQL语言中,包含了内置函数和自定义函数。不同的 DBMS 函数是存在差异的,大部分 DBMS 会有自己特定的函数。

MySQL 内置函数及分类

MySQL提供了丰富的内置函数,这些内置函数可以帮助用户更加方便地处理表中的数据。函数就像预定的公式一样存放在数据库里,每个用户都可以调用已经存在的函数来完成某些功能。

函数可以很方便的地实现业务逻辑的重用,并且 MySQL 数据库允许用户自己创建函数,以适应实际的业务操作。正确使用函数会让开发者在编写 SQL 语句时起到事半功倍的效果。

MySQL 函数用来对数据表中的数据进行相应的处理,以便得到用户希望得到的数据,使 MySQL 数据库的功能更加强大。

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、系统信息函数和加密函数、获取MyMSQL信息函数等。

SELECT、INSERT、UPDATE 和 DELETE 语句及其子句(例如 WHERE、ORDER BY、HAVING 等)中都可以使用 MySQL 函数。

下面介绍上面几类函数的使用范围:

  • 数学函数主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。
  • 字符串函数主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。
  • 日期和时间函数主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。
  • 流程控制函数主要用于在 SQL 语句中控制条件选择。其中包括 IF 语句、CASE 语句和 WHERE 语句等。
  • 系统信息函数主要用于获取 MySQL 数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。
  • 加密函数主要用于对字符串进行加密解密。其中包括字符串加密函数和字符串解密函数等。
  • 其他函数主要包括格式化函数和锁函数等。

MySQL函数官方参考文档:https://dev.mysql.com/doc/refman/5.7/en/

内置函数再分为两类:单行函数、聚合函数(或分组函数)。

2、单行函数

单行函数定义:

  • 操作数据对象
  • 接收参数返回一个结果
  • 只对一行进行转换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一字段或者是一个值

2.1、数值型函数

函数名称作 用
ABS()求绝对值
SQRT()求二次方根
MOD(x,y)求x除以y的余数
PI()返回圆周率
CEIL() 和 CEILING()两个函数功能相同,都是返回不小于参数的最小整数,即向上取整
FLOOR()向下取整,返回值转化为一个BIGINT
RAND()生成一个0~1之间的随机数,传入参数相同时,生成随机数相同
ROUND(x,y)对所传参数进行四舍五入
TRUNCARE(x,y)返回数字x截断为y位小数的结果
SIGN()返回参数的符号。正数返回1,负数返回-1,0返回0
POW(x,y) 和 POWER(x,y)两个函数的功能相同,都是返回x的y次方
EXP(x)返回e的x次方,其中e是一个常数,2.718281828459045
LN(x),LOG(x)返回以e为底的X的对数,当X<=0时,返回的结果为NULL
BIN(x)返回x的二进制编码
HEX(x)返回x的十六进制编码
OCT(x)返回x的八进制编码
SIN()求正弦值
ASIN()求反正弦值,与函数 SIN 互为反函数
COS()求余弦值
ACOS()求反余弦值,与函数 COS 互为反函数
TAN()求正切值
ATAN()求反正切值,与函数 TAN 互为反函数
COT()求余切值

使用实例:

SELECT ABS(-9)-- 绝对值    9select pi();-- 圆周率 3.141593selectmod(4,3);-- 求余数 1SELECT CEILING(9.8)-- 向上取整   10SELECT FLOOR(9.8)-- 向下取整  9SELECT RAND()-- 生成随机数0-1之间SELECT SIGN(7)-- 判断一个数的正负   正数为1SELECT SIGN(-7)--   负数为-1selectround(146.647);-- 四舍五入 146selectround(146.647,2);-- 146.65,保留两位小数selecttruncate(146.647,2);-- 返回数字x截断为y位小数的结果 146.64selecttruncate(round(146.647,2),1);-- 函数嵌套使用 146.6select bin(4);-- 返回x的二进制编码 100select hex(100);-- 返回x的十六进制编码 64select oct(100);-- 返回x的八进制编码 144

2.2、字符串函数

函数名称作 用
ASCII(s)返回字符串s中的第一个字符的ASCll码值
CHAR_LENGTHI(s)返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)返回字符串的字节长度,和字符集有关
CONCAT(s1,s2,…,sn)合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个
CONCAT_WS(x,s1,s2,…,sn)同CONCAT(s1,s2,…,sn)函数,但是每个字符串之间要加上x
INSERT(str,idx,len,replacestr)替换字符串函数,将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr,字符串索引从1开始的
LOWER(s)或LCASE(s)将字符串s中的字母转换为小写
UPPER(s)或UCASE(s)将字符串s中的字母转换为大写
LEFT(str,n)从左侧字截取符串str,返回字符串左边的n干个字符
RIGHT(str,n)从右侧字截取符串str,返回字符串右边的n干个字符
LPAD(str,len,pad)用字符串pad对str最左边进行填充,直到str的长度为len个字符
RPAD(str,len,pad)用字符串pad对str最右边进行填充,直到str的长度为len个字符
TRIM(s)删除字符串左右两侧的空格
LTRIM(s)去掉字符串s左侧的空格
RTRIM(s)去掉字符串s右侧的空格
REPEAT(str,n)返回str重复n次的结果
SPACE(n)返回n个空格
STRCMP(s1,s2)比较字符串s1,s2的ASCIl码值的大小
REPLACE(str,a,b)字符串替换函数,用字符串b替换宇符串str中所有出现的字符串a
SUBSTRING截取字符串,返回从指定位置开始的指定长度的字符换
REVERSE字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串
ELT(m,s1,s2,s3,…,sn)返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如果m=n,则返回sn
FIELD(s,s1,s2,s3,…,sn)返回字符串s在字符串列表中第一次出现的位置
FIELD_IN_SET(s1,s2)返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一个以逗号分隔的字符串
NULLIF(s1,s2)比较两个字符串,如果s1与s2相等,则返回NULL,否则返回s1

使用实例:

select ascii('CBcd')from dual;-- 返回字符串s中的第一个字符的ASCll码值 67SELECT CHAR_LENGTH('万,里顾一程');-- 返回字符串长度, 6SELECT CONCAT('万里','顾','一','程');-- 拼接字符串 ,万里顾一程SELECT CONCAT_WS('a','万里','顾','一','程');-- 拼接字符串,同CONCAT(s1,s2,…,sn)函数,但是每个字符串之间要加上a ,万里a顾a一a程SELECTINSERT('helloworld',2,4,'b');-- 2:被替换字符的起始位置 4:被替换字符的长度  'b':替换字符 ,hbworldSELECT LOWER('HELLo');-- 转换成小写字母  helloSELECT UPPER('hello');-- 转换成大写字母  HELLOSELECTleft('hello',3);-- 从左侧字截取符串str,返回字符串左边的n干个字符 , helSELECTright('hello',3);-- 从左侧字截取符串str,返回字符串左边的n干个字符,  lloSELECT lpad('hello',10,'a');-- 用字符串pad对str最左边进行填充,直到str的长度为len个字符,  aaaaahelloSELECT rpad('hello',10,'a');-- 用字符串pad对str最右边进行填充,直到str的长度为len个字符,  helloaaaaaSELECT trim(' hello  ');-- 删除字符串两边的空格,  helloSELECTREPEAT('hello',3);-- 返回str重复n次的结果,  hellohellohelloSELECT STRCMP('hello','world');-- 比较字符串s1,s2的ASCIl码值的大小,  -1,表示后面的数大;1,表示前面的数大SELECT INSTR('hellworld','wo');-- 返回第一次出现的字符串的索引 ,5SELECTREPLACE('java高级工程师','高级','究极');-- 替换出现的指定字符串  java究极工程师SELECT SUBSTR('java高级工程师',1,4);-- 截取指定的字符串 1:开始截取的位置 4:截取字符串的长度, javaSELECT REVERSE('赵兄托我办点事');-- 反转字符串, 事点办我托兄赵SELECT FIELD('ab','bc','cd','ab','dg','ab');-- 返回字符串s在字符串列表中第一次出现的位置,3SELECT FIND_IN_SET('ab','bc,cd,ab,dg,ab');-- 返回字符串s1在字符串s2中出现的位置,3SELECTNULLIF('ab','bc');-- 比较两个字符串,ab

2.3、日期和时间函数

函数名称作 用
CURDATE 和 CURRENT_DATE两个函数作用相同,返回当前系统的日期值
CURTIME 和 CURRENT_TIME两个函数作用相同,返回当前系统的时间值
NOW 和 SYSDATE两个函数作用相同,返回当前系统的日期和时间值
UNIX_TIMESTAMP获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数
MONTH获取指定日期中的月份
MONTHNAME获取指定日期中的月份英文名称
DAYNAME获取指定曰期对应的星期几的英文名称
DAYOFWEEK获取指定日期对应的一周的索引位置值
WEEK获取指定日期是一年中的第几周,返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR获取指定曰期是一年中的第几天,返回值范围是1~366
DAYOFMONTH获取指定日期是一个月中是第几天,返回值范围是1~31
YEAR获取年份,返回值范围是 1970〜2069
TIME_TO_SEC将时间参数转换为秒数
SEC_TO_TIME将秒数转换为时间,与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE两个函数功能相同,都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE两个函数功能相同,都是向日期减去指定的时间间隔
ADDTIME时间加法运算,在原始时间上添加指定的时间
SUBTIME时间减法运算,在原始时间上减去指定的时间
DATEDIFF获取两个日期之间间隔,返回参数 1 减去参数 2 的值
DATE_FORMAT格式化指定的日期,根据参数返回指定格式的值
WEEKDAY获取指定日期在一周内的对应的工作日索引

使用实例:

SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE();-- 返回当前系统的日期值 2022-02-12,2022-02-12,2022-02-12SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME();-- 返回当前系统的时间值 16:02:20,16:02:20,16:02:20SELECTNOW(),SYSDATE();-- 返回当前系统的日期和时间值 2022-02-12 16:03:33,2022-02-12 16:03:33SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();-- 获取UNIX时间戳函数 1644653034,1644653034,2022-02-12 16:03:54SELECT FROM_UNIXTIME(1644653034);-- 将 UNIX 时间戳转换为普通格式时间 2022-02-12 16:03:54SELECTMONTH('2017-12-15');-- 返回指定日期中的月份 12SELECT MONTHNAME('2017-12-15');-- 返回日期 date 对应月份的英文全名 DecemberSELECT DAYNAME('2022-02-12');-- 返回 date 对应的工作日英文名称 SaturdaySELECT DAYOFWEEK('2022-02-12');-- 返回日期对应的周索引 7 ,1表示周日,2表示周一,……,7 表示周六/*
WEEK() 函数计算日期 date 是一年中的第几周
WEEK函数接受两个参数:
 date是要获取周数的日期。
 mode是一个可选参数,用于确定周数计算的逻辑,如果忽略 mode 参数,默认情况下 WEEK 函数将使用 default_week_format 系统变量的值。
*/SHOW VARIABLESLIKE'default_week_format';-- 获取 default_week_format 变量的当前值 0SELECT WEEK('2022-02-12',1);-- 6SELECT DAYOFYEAR('2022-02-12');-- 返回指定日期在一年中是第几天 43SELECT DAYOFMONTH('2022-02-12');-- 返回指定日期在一月中是第几天 12SELECTYEAR(NOW());-- 获取当前时间的年份 2022SELECT TIME_TO_SEC('15:15:15');-- 将时间值转换为秒值 54915SELECT SEC_TO_TIME('54925');-- 将秒值转换为时间格式 15:15:25SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL1SECOND)AS T1,-- 在原始时间上加1 秒 2018-11-01 00:00:00
       DATE_ADD('2018-10-31 23:59:59',INTERVAL'1:1' MINUTE_SECOND)AS T2,-- 在原始时间上加 1 分钟 1 秒 2018-11-01 00:01:00
       ADDDATE('2018-10-31 23:59:59',INTERVAL1SECOND)AS T3;-- 在原始时间上加1 秒 2018-11-01 00:00:00SELECT DATE_SUB('2018-01-02',INTERVAL31DAY)AS T1,-- 在原始时间上减去一个月 2017-12-02
       SUBDATE('2018-01-02',INTERVAL31DAY)AS T2,-- 在原始时间上减去一个月 2017-12-02
       DATE_SUB('2018-01-01 00:01:00',INTERVAL'0 0:1:1' DAY_SECOND)AS T3;-- 在原始时间上减去1分1秒 2017-12-31 23:59:59-- DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type) 函数在指定加减的时间段时也可以指定负值,加法的负值即返回原始时间之前的日期和时间,减法的负值即返回原始时间之后的日期和时间。SELECT ADDTIME('2018-10-31 23:59:59','0:1:1'),-- 进行时间的加法运算 2018-11-01 00:01:00
       ADDTIME('10:30:59','5:10:37');-- 15:41:36SELECT SUBTIME('2018-10-31 23:59:59','0:1:1'),-- 进行时间的减法运算 2018-10-31 23:58:58
       SUBTIME('10:30:59','5:12:37');-- 05:18:22SELECT DATEDIFF('2017-11-30','2017-11-20')AS COL1,-- 返回起始时间 date1 和结束时间 date2 之间的天数,返回 date1-date2 后的值 10
       DATEDIFF('2017-11-30','2017-12-15')AS col2;-- -15/**
  DATE_FORMAT(date,format) 函数根据 format 指定的格式显示 date 值。
  DATE_FORMAT() 函数接受两个参数:
   date:是要格式化的有效日期值
   format:是由预定义的说明符组成的格式字符串,每个说明符前面都有一个百分比字符(%)。
 */SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y')AS col1,-- Wednesday November 15th 2017
       DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y')AS col2;-- 09:i PM November 15th 2017SELECT WEEKDAY('2022-02-12');-- 返回日期对应的工作日索引,0表示周一,1表示周二,……,6表示周日  5

DATE_FORMAT(date,format) 函数中 format 格式如下表所示:

说明符说明
%a工作日的缩写名称(Sun~Sat)
%b月份的缩写名称(Jan…Dec)
%c月份,数字形式(0~12)
%D带有英语后缀的该月日期(0th, 2st, 3nd,…)
%d该月日期,数字形式(00~31)
%e该月日期,数字形式(0~31)
%f微秒(000000 …999999)
%H以 2 位数表示 24 小时(00~23)
%h, %I以 2 位数表示 12 小时(01~12)
%i分钟,数字形式(00~59)
%j—年中的天数(001~366)
%k以 24 小时(0~23)表示
%l以12小时(1~12)表示
%M月份名称(January~December)
%m月份,数字形式(00~12)
%p上午(AM) 或下午(PM)
%r时间,12小时制(小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM)
%S, %s以 2 位数形式表示秒(00~59)
%T时间,24 小时制(小时 (hh): 分钟 (mm): 秒数 (ss))
%U周(00~53),其中周日为每周的第一天
%u周(00~53),其中周一为每周的第一天
%V周(01~53),其中周日为每周的第一天,和%X同时使用
%v周(01~53),其中周一为每周的第一天,和%x同时使用
%W星期标识(周日、周一、周二…周六)
%w—周中的每日(0= 周日…6= 周六)
%X该周的年份,其中周日为每周的第一天,数字形式,4 位数,和%V同时使用
%x该周的年份,其中周一为每周的第一天,数字形式,4位数,和%v同时使用
%Y4 位数形式表示年份
%y2 位数形式表示年份
%%%一个文字字符

2.4、流程控制函数

流程处理函数可以根据不同的条件,执行不同的处理流程,可以在sQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括lF()、IFNULL()和CASE()函数。

函数名称作用
lF(value,value1,value2)如果value的值为TRUE,返回value1,否则返回value2
IFNULL(value1, value2)如果value1不为NULL,返回value1,否则返回value2
CASE WHEN 条件1 THEN结果1 WHEN条件2 THEN结果2…[ELSE resultn] END相当于 Java的if…else if…else…
CASE expr WHEN 常量值1 THEN值1 WHEN常量值1 THEN 值1…[ELSE 值n] END相当于 Java的switch…case…

实用实例:

/*使用 IF(expr,v1,v2) 函数根据 expr 表达式结果返回相应值:
在 c3 中,先用 STRCMP(s1,s2) 函数比较两个字符串的大小,字符串 'abc' 和 'ab' 比较结果的返回值为 1,
也就是表达式 expr 的返回结果不等于 0 且不等于 NULL,则返回值为 v1,即字符串 'yes' 1,×,yes
*/SELECTIF(1<2,1,0) c1,IF(1>5,'√','×') c2,IF(STRCMP('abc','ab'),'yes','no') c3;SELECT first_name,salary,IF(salary>6000,'高薪','一般')'薪资水平'FROM employees-- 使用 IFNULL(v1,v2) 函数根据 v1 的取值返回相应值,如果 v1 不为 NULL,则 IFNULL 函数返回 v1; 否则返回 v2 的结果。SELECT IFNULL(5,8),IFNULL(NULL,'OK'),IFNULL(SQRT(-8),'FALSE'),SQRT(-8);-- 5,OK,FALSE,nullSELECT first_name,salary,(salary+ salary* IFNULL(commission_pct,0))*12'年薪'/*CASE 语句来检查表达式的值与一组唯一值的匹配.
将 <表达式> 的值与每个 WHEN 子句中的值进行比较,例如 <值1>,<值2> 等。如果 <表达式> 和 <值n> 的值相等,则执行相应的 WHEN 分支中的命令 <操作>。
如果 WHEN 子句中的 <值n> 没有与 <表达式> 的值匹配,则 ELSE 子句中的命令将被执行。ELSE 子句是可选的。 
如果省略 ELSE 子句,并且找不到匹配项,MySQL 将引发错误。
*/SELECTCASE WEEKDAY(NOW())WHEN0THEN'星期一'WHEN1THEN'星期二'WHEN2THEN'星期三'WHEN3THEN'星期四'WHEN4THEN'星期五'WHEN5THEN'星期六'ELSE'星期天'ENDAS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());/*
MySQL 分别计算 WHEN 子句中的每个条件,直到找到一个值为 TRUE 的条件,然后执行 THEN 子句中的相应 <命令>。
如果没有一个条件为 TRUE,则执行 ELSE 子句中的 <命令>。如果不指定 ELSE 子句,并且没有一个条件为 TRUE,MySQL 将发出错误消息。
*/SELECTCASEWHEN WEEKDAY(NOW())=0THEN'星期一'WHEN WEEKDAY(NOW())=1THEN'星期二'WHEN WEEKDAY(NOW())=2THEN'星期三'WHEN WEEKDAY(NOW())=3THEN'星期四'WHEN WEEKDAY(NOW())=4THEN'星期五'WHEN WEEKDAY(NOW())=5THEN'星期六'WHEN WEEKDAY(NOW())=6THEN'星期天'ENDAS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());
  • 作者:万里顾—程
  • 原文链接:https://wanli.blog.csdn.net/article/details/122909374
    更新时间:2022-08-21 11:07:50