mysql函数用法 mysql函数作用

文章目录

• MySQL——单行函数

• 1、MySQL 中函数的分类
• 2、单行函数

• 2.1、数值型函数
• 2.2、字符串函数
• 2.3、日期和时间函数
• 2.4、流程控制函数

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)  -- 绝对值    9
select pi(); -- 圆周率 3.141593
select mod(4,3); -- 求余数 1
SELECT CEILING(9.8) -- 向上取整   10
SELECT FLOOR(9.8) -- 向下取整  9
SELECT RAND() -- 生成随机数0-1之间
SELECT SIGN(7) -- 判断一个数的正负   正数为1
SELECT SIGN(-7)--   负数为-1
select round(146.647); -- 四舍五入 146
select round(146.647,2); -- 146.65，保留两位小数
select truncate(146.647,2); -- 返回数字x截断为y位小数的结果 146.64
select truncate(round(146.647,2),1); -- 函数嵌套使用 146.6
select bin(4); -- 返回x的二进制编码 100
select hex(100); -- 返回x的十六进制编码 64
select 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码值 67
SELECT CHAR_LENGTH('万，里顾一程');  -- 返回字符串长度, 6
SELECT CONCAT('万里','顾','一','程');-- 拼接字符串 ,万里顾一程
SELECT CONCAT_WS('a','万里','顾','一','程');-- 拼接字符串,同CONCAT(s1,s2,…,sn)函数，但是每个字符串之间要加上a ,万里a顾a一a程
SELECT INSERT('helloworld',2,4,'b');-- 2:被替换字符的起始位置 4:被替换字符的长度  'b':替换字符 ,hbworld
SELECT LOWER('HELLo'); -- 转换成小写字母  hello
SELECT UPPER('hello'); -- 转换成大写字母  HELLO
SELECT left('hello',3); -- 从左侧字截取符串str，返回字符串左边的n干个字符 , hel
SELECT right('hello',3); -- 从左侧字截取符串str，返回字符串左边的n干个字符,  llo
SELECT lpad('hello',10,'a'); -- 用字符串pad对str最左边进行填充，直到str的长度为len个字符,  aaaaahello
SELECT rpad('hello',10,'a'); -- 用字符串pad对str最右边进行填充，直到str的长度为len个字符,  helloaaaaa
SELECT trim(' hello  '); -- 删除字符串两边的空格,  hello
SELECT REPEAT('hello',3); -- 返回str重复n次的结果,  hellohellohello
SELECT STRCMP('hello','world'); -- 比较字符串s1,s2的ASCIl码值的大小,  -1,表示后面的数大;1,表示前面的数大
SELECT INSTR('hellworld','wo'); -- 返回第一次出现的字符串的索引 ,5
SELECT REPLACE('java高级工程师','高级','究极');-- 替换出现的指定字符串  java究极工程师
SELECT SUBSTR('java高级工程师',1,4);-- 截取指定的字符串 1：开始截取的位置 4：截取字符串的长度, java
SELECT REVERSE('赵兄托我办点事');-- 反转字符串, 事点办我托兄赵
SELECT FIELD('ab','bc','cd','ab','dg','ab');-- 返回字符串s在字符串列表中第一次出现的位置,3
SELECT FIND_IN_SET('ab','bc,cd,ab,dg,ab');-- 返回字符串s1在字符串s2中出现的位置,3
SELECT NULLIF('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-12
SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME(); -- 返回当前系统的时间值 16:02:20，16:02:20，16:02:20
SELECT NOW(),SYSDATE(); -- 返回当前系统的日期和时间值 2022-02-12 16:03:33，2022-02-12 16:03:33
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW(); -- 获取UNIX时间戳函数 1644653034，1644653034，2022-02-12 16:03:54
SELECT FROM_UNIXTIME(1644653034); -- 将 UNIX 时间戳转换为普通格式时间 2022-02-12 16:03:54
SELECT MONTH('2017-12-15'); -- 返回指定日期中的月份 12
SELECT MONTHNAME('2017-12-15'); -- 返回日期 date 对应月份的英文全名 December
SELECT DAYNAME('2022-02-12'); -- 返回 date 对应的工作日英文名称 Saturday
SELECT DAYOFWEEK('2022-02-12'); -- 返回日期对应的周索引 7 ，1表示周日，2表示周一，……，7 表示周六

/*
WEEK() 函数计算日期 date 是一年中的第几周
WEEK函数接受两个参数：
 date是要获取周数的日期。
 mode是一个可选参数，用于确定周数计算的逻辑,如果忽略 mode 参数，默认情况下 WEEK 函数将使用 default_week_format 系统变量的值。
*/
SHOW VARIABLES LIKE 'default_week_format'; -- 获取 default_week_format 变量的当前值 0
SELECT WEEK('2022-02-12',1); -- 6

SELECT DAYOFYEAR('2022-02-12'); -- 返回指定日期在一年中是第几天 43
SELECT DAYOFMONTH('2022-02-12'); -- 返回指定日期在一月中是第几天 12
SELECT YEAR(NOW()); -- 获取当前时间的年份 2022
SELECT TIME_TO_SEC('15:15:15'); -- 将时间值转换为秒值 54915
SELECT SEC_TO_TIME('54925'); -- 将秒值转换为时间格式 15:15:25
SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) 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',INTERVAL 1 SECOND) AS T3; -- 在原始时间上加1 秒 2018-11-01 00:00:00
SELECT DATE_SUB('2018-01-02',INTERVAL 31 DAY) AS T1, -- 在原始时间上减去一个月 2017-12-02
       SUBDATE('2018-01-02',INTERVAL 31 DAY) 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:36
SELECT 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:22
SELECT 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 2017

SELECT 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同时使用
%Y	4 位数形式表示年份
%y	2 位数形式表示年份
%%	%一个文字字符

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
*/
SELECT IF(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,null
SELECT first_name ,salary,(salary + salary * IFNULL(commission_pct,0)) * 12 '年薪'

/*CASE 语句来检查表达式的值与一组唯一值的匹配.
将 <表达式> 的值与每个 WHEN 子句中的值进行比较，例如 <值1>，<值2> 等。如果 <表达式> 和 <值n> 的值相等，则执行相应的 WHEN 分支中的命令 <操作>。
如果 WHEN 子句中的 <值n> 没有与 <表达式> 的值匹配，则 ELSE 子句中的命令将被执行。ELSE 子句是可选的。 
如果省略 ELSE 子句，并且找不到匹配项，MySQL 将引发错误。
*/
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,
NOW(),WEEKDAY(NOW()),DAYNAME(NOW());

/*
MySQL 分别计算 WHEN 子句中的每个条件，直到找到一个值为 TRUE 的条件，然后执行 THEN 子句中的相应 <命令>。
如果没有一个条件为 TRUE，则执行 ELSE 子句中的 <命令>。如果不指定 ELSE 子句，并且没有一个条件为 TRUE，MySQL 将发出错误消息。
*/
SELECT CASE 
WHEN WEEKDAY(NOW())=0 THEN '星期一' 
WHEN WEEKDAY(NOW())=1 THEN '星期二'  
WHEN WEEKDAY(NOW())=2 THEN '星期三' 
WHEN WEEKDAY(NOW())=3 THEN '星期四' 
WHEN WEEKDAY(NOW())=4 THEN '星期五' 
WHEN WEEKDAY(NOW())=5 THEN '星期六' 
WHEN WEEKDAY(NOW())=6 THEN '星期天' END AS COLUMN1,
NOW(),WEEKDAY(NOW()),DAYNAME(NOW());