玩转Mysql系列 – 第10篇：常用的几十个函数详解
- 2019 年 10 月 5 日
- 笔记
打算提升sql技能的，可以加我微信itsoku，带你成为sql高手。
这是Mysql系列第10篇。
环境：mysql5.7.25，cmd命令中进行演示。
MySQL 数值型函数
|
函数名称 |
作 用 |
|---|---|
|
abs |
求绝对值 |
|
sqrt |
求二次方根 |
|
mod |
求余数 |
|
ceil 和 ceiling |
两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 |
|
floor |
向下取整，返回值转化为一个BIGINT |
|
rand |
生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列 |
|
round |
对所传参数进行四舍五入 |
|
sign |
返回参数的符号 |
|
pow 和 power |
两个函数的功能相同，都是所传参数的次方的结果值 |
|
sin |
求正弦值 |
|
asin |
求反正弦值，与函数 SIN 互为反函数 |
|
cos |
求余弦值 |
|
acos |
求反余弦值，与函数 COS 互为反函数 |
|
tan |
求正切值 |
|
atan |
求反正切值，与函数 TAN 互为反函数 |
|
cot |
求余切值 |
abs:求绝对值
函数 ABS(x) 返回 x 的绝对值。正数的绝对值是其本身，负数的绝对值为其相反数，0 的绝对值是 0。
mysql> select abs(5),abs(-2.4),abs(-24),abs(0); +--------+-----------+----------+--------+ | abs(5) | abs(-2.4) | abs(-24) | abs(0) | +--------+-----------+----------+--------+ | 5 | 2.4 | 24 | 0 | +--------+-----------+----------+--------+ 1 row in set (0.00 sec)
sqrt:求二次方跟（开方）
函数 SQRT(x) 返回非负数 x 的二次方根。负数没有平方根，返回结果为 NULL。
mysql> select sqrt(25),sqrt(120),sqrt(-9); +----------+--------------------+----------+ | sqrt(25) | sqrt(120) | sqrt(-9) | +----------+--------------------+----------+ | 5 | 10.954451150103322 | NULL | +----------+--------------------+----------+ 1 row in set (0.00 sec)
mod:求余数
函数 MOD(x,y) 返回 x 被 y 除后的余数，MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的余数。
mysql> select mod(63,8),mod(120,10),mod(15.5,3); +-----------+-------------+-------------+ | mod(63,8) | mod(120,10) | mod(15.5,3) | +-----------+-------------+-------------+ | 7 | 0 | 0.5 | +-----------+-------------+-------------+ 1 row in set (0.00 sec)
ceil和ceiling:向上取整
函数 CEIL(x) 和 CEILING(x) 的意义相同，返回不小于 x 的最小整数值，返回值转化为一个 BIGINT。
mysql> select ceil(-2.5),ceiling(2.5); +------------+--------------+ | ceil(-2.5) | ceiling(2.5) | +------------+--------------+ | -2 | 3 | +------------+--------------+ 1 row in set (0.00 sec)
floor:向下取整
floor(x) 函数返回小于 x 的最大整数值。
mysql> select floor(5),floor(5.66),floor(-4),floor(-4.66); +----------+-------------+-----------+--------------+ | floor(5) | floor(5.66) | floor(-4) | floor(-4.66) | +----------+-------------+-----------+--------------+ | 5 | 5 | -4 | -5 | +----------+-------------+-----------+--------------+ 1 row in set (0.00 sec)
rand:生成一个随机数
生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
mysql> select rand(), rand(), rand(); +--------------------+--------------------+--------------------+ | rand() | rand() | rand() | +--------------------+--------------------+--------------------+ | 0.5224735778965741 | 0.3678060549942833 | 0.2716095720153391 | +--------------------+--------------------+--------------------+ 1 row in set (0.00 sec) mysql> select rand(1),rand(2),rand(1); +---------------------+--------------------+---------------------+ | rand(1) | rand(2) | rand(1) | +---------------------+--------------------+---------------------+ | 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 | +---------------------+--------------------+---------------------+ 1 row in set (0.00 sec) mysql> select rand(1),rand(2),rand(1); +---------------------+--------------------+---------------------+ | rand(1) | rand(2) | rand(1) | +---------------------+--------------------+---------------------+ | 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 | +---------------------+--------------------+---------------------+ 1 row in set (0.00 sec)
round:四舍五入函数
返回最接近于参数 x 的整数；ROUND(x,y) 函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。
mysql> select round(-6.6),round(-8.44),round(3.44); +-------------+--------------+-------------+ | round(-6.6) | round(-8.44) | round(3.44) | +-------------+--------------+-------------+ | -7 | -8 | 3 | +-------------+--------------+-------------+ 1 row in set (0.00 sec) mysql> select round(-6.66,1),round(3.33,3),round(88.66,-1),round(88.46,-2); +----------------+---------------+-----------------+-----------------+ | round(-6.66,1) | round(3.33,3) | round(88.66,-1) | round(88.46,-2) | +----------------+---------------+-----------------+-----------------+ | -6.7 | 3.330 | 90 | 100 | +----------------+---------------+-----------------+-----------------+ 1 row in set (0.00 sec)
sign:返回参数的符号
返回参数的符号，x 的值为负、零和正时返回结果依次为 -1、0 和 1。
mysql> select sign(-6),sign(0),sign(34); +----------+---------+----------+ | sign(-6) | sign(0) | sign(34) | +----------+---------+----------+ | -1 | 0 | 1 | +----------+---------+----------+ 1 row in set (0.00 sec)
pow 和 power:次方函数
POW(x,y) 函数和 POWER(x,y) 函数用于计算 x 的 y 次方。
mysql> select pow(5,-2),pow(10,3),pow(100,0),power(4,3),power(6,-3); +-----------+-----------+------------+------------+----------------------+ | pow(5,-2) | pow(10,3) | pow(100,0) | power(4,3) | power(6,-3) | +-----------+-----------+------------+------------+----------------------+ | 0.04 | 1000 | 1 | 64 | 0.004629629629629629 | +-----------+-----------+------------+------------+----------------------+ 1 row in set (0.00 sec)
sin:正弦函数
SIN(x) 返回 x 的正弦值，其中 x 为弧度值。
mysql> select sin(1),sin(0.5*pi()),pi(); +--------------------+---------------+----------+ | sin(1) | sin(0.5*pi()) | pi() | +--------------------+---------------+----------+ | 0.8414709848078965 | 1 | 3.141593 | +--------------------+---------------+----------+ 1 row in set (0.00 sec)
注：PI() 函数返回圆周率（3.141593）
其他几个三角函数在此就不说了，有兴趣的可以自己去练习一下。
MySQL 字符串函数
|
函数名称 |
作 用 |
|---|---|
|
length |
计算字符串长度函数，返回字符串的字节长度 |
|
concat |
合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 |
|
insert |
替换字符串函数 |
|
lower |
将字符串中的字母转换为小写 |
|
upper |
将字符串中的字母转换为大写 |
|
left |
从左侧字截取符串，返回字符串左边的若干个字符 |
|
right |
从右侧字截取符串，返回字符串右边的若干个字符 |
|
trim |
删除字符串左右两侧的空格 |
|
replace |
字符串替换函数，返回替换后的新字符串 |
|
substr 和 substring |
截取字符串，返回从指定位置开始的指定长度的字符换 |
|
reverse |
字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
length:返回字符串直接长度
返回值为字符串的字节长度，使用 uft8（UNICODE 的一种变长字符编码，又称万国码）编码字符集时，一个汉字是 3 个字节，一个数字或字母是一个字节。
mysql> select length('javacode2018'),length('路人甲Java'),length('路人'); +------------------------+-------------------------+------------------+ | length('javacode2018') | length('路人甲Java') | length('路人') | +------------------------+-------------------------+------------------+ | 12 | 13 | 6 | +------------------------+-------------------------+------------------+ 1 row in set (0.00 sec)
concat:合并字符串
CONCAT(sl，s2，…) 函数返回结果为连接参数产生的字符串，或许有一个或多个参数。 若有任何一个参数为 NULL，则返回值为 NULL。若所有参数均为非二进制字符串，则结果为非二进制字符串。若自变量中含有任一二进制字符串，则结果为一个二进制字符串。
mysql> select concat('路人甲','java'),concat('路人甲',null,'java'); +----------------------------+---------------------------------+ | concat('路人甲','java') | concat('路人甲',null,'java') | +----------------------------+---------------------------------+ | 路人甲java | NULL | +----------------------------+---------------------------------+ 1 row in set (0.00 sec)
insert:替换字符串
INSERT(s1，x，len，s2) 返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2。 x的值从1开始，第一个字符的x=1，若 x 超过字符串长度，则返回值为原始字符串。 假如 len 的长度大于其他字符串的长度，则从位置 x 开始替换。 若任何一个参数为 NULL，则返回值为 NULL。
mysql> select -> insert('路人甲Java', 2, 4, '**') AS col1, -> insert('路人甲Java', -1, 4,'**') AS col2, -> insert('路人甲Java', 3, 20,'**') AS col3; +---------+---------------+----------+ | col1 | col2 | col3 | +---------+---------------+----------+ | 路**va | 路人甲Java | 路人** | +---------+---------------+----------+ 1 row in set (0.00 sec)
lower:将字母转换成小写
LOWER(str) 可以将字符串 str 中的字母字符全部转换成小写。
mysql> select lower('路人甲JAVA'); +------------------------+ | lower('路人甲JAVA') | +------------------------+ | 路人甲java | +------------------------+ 1 row in set (0.00 sec)
upper:将字母转换成大写
UPPER(str) 可以将字符串 str 中的字母字符全部转换成大写。
mysql> select upper('路人甲java'); +------------------------+ | upper('路人甲java') | +------------------------+ | 路人甲JAVA | +------------------------+ 1 row in set (0.00 sec)
left:从左侧截取字符串
LEFT(s，n) 函数返回字符串 s 最左边的 n 个字符，s=1表示第一个字符。
mysql> select left('路人甲JAVA',2),left('路人甲JAVA',10),left('路人甲JAVA',-1); +-------------------------+--------------------------+--------------------------+ | left('路人甲JAVA',2) | left('路人甲JAVA',10) | left('路人甲JAVA',-1) | +-------------------------+--------------------------+--------------------------+ | 路人 | 路人甲JAVA | | +-------------------------+--------------------------+--------------------------+ 1 row in set (0.00 sec)
right:从右侧截取字符串
RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符。
mysql> select right('路人甲JAVA',1),right('路人甲JAVA',10),right('路人甲JAVA',-1); +--------------------------+---------------------------+---------------------------+ | right('路人甲JAVA',1) | right('路人甲JAVA',10) | right('路人甲JAVA',-1) | +--------------------------+---------------------------+---------------------------+ | A | 路人甲JAVA | | +--------------------------+---------------------------+---------------------------+ 1 row in set (0.00 sec)
trim:删除字符串两侧空格
TRIM(s) 删除字符串 s 两侧的空格。
mysql> select '[ 路人甲Java ]',concat('[',trim(' 路人甲Java '),']'); +-----------------------+---------------------------------------------+ | [ 路人甲Java ] | concat('[',trim(' 路人甲Java '),']') | +-----------------------+---------------------------------------------+ | [ 路人甲Java ] | [路人甲Java] | +-----------------------+---------------------------------------------+ 1 row in set (0.00 sec)
replace:字符串替换
REPLACE(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1。
substr 和 substring:截取字符串
substr(str,pos) substr(str from pos) substr(str,pos,len) substr(str from pos for len) substr()是substring()的同义词。 没有len参数的形式是字符串str从位置pos开始返回一个子字符串。 带有len参数的形式是字符串str从位置pos开始返回长度为len的子字符串。 使用FROM的形式是标准的SQL语法。 也可以对pos使用负值，在这种情况下，子字符串的开头是字符串末尾的pos字符，而不是开头。在这个函数的任何形式中pos可以使用负值。 对于所有形式的substring()，从中提取子串的字符串中第一个字符的位置被认为是1。
/** 第三个字符之后的子字符串：inese **/ SELECT substring('chinese', 3); /** 倒数第三个字符之后的子字符串：ese **/ SELECT substring('chinese', -3); /** 第三个字符之后的两个字符：in **/ SELECT substring('chinese', 3, 2); /** 倒数第三个字符之后的两个字符：es **/ SELECT substring('chinese', -3, 2); /** 第三个字符之后的子字符串：inese **/ SELECT substring('chinese' FROM 3); /** 倒数第三个字符之后的子字符串：ese **/ SELECT substring('chinese' FROM -3); /** 第三个字符之后的两个字符：in **/ SELECT substring('chinese' FROM 3 FOR 2); /** 倒数第三个字符之后的两个字符：es **/ SELECT substring('chinese' FROM -3 FOR 2);
reverse:反转字符串
REVERSE(s) 可以将字符串 s 反转，返回的字符串的顺序和 s 字符串的顺序相反。
mysql> select reverse('路人甲Java'); +--------------------------+ | reverse('路人甲Java') | +--------------------------+ | avaJ甲人路 | +--------------------------+ 1 row in set (0.00 sec)
MySQL 日期和时间函数
|
函数名称 |
作 用 |
|---|---|
|
curdate 和 current_date |
两个函数作用相同，返回当前系统的日期值 |
|
curtime 和 current_time |
两个函数作用相同，返回当前系统的时间值 |
|
now 和 sysdate |
两个函数作用相同，返回当前系统的日期和时间值 |
|
unix_timestamp |
获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数 |
|
from_unixtime |
将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数 |
|
month |
获取指定日期中的月份 |
|
monthname |
获取指定日期中的月份英文名称 |
|
dayname |
获取指定曰期对应的星期几的英文名称 |
|
dayofweek |
获取指定日期是一周中是第几天，返回值范围是1~7,1=周日 |
|
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 |
获取指定日期在一周内的对应的工作日索引 |
curdate 和 current_date:两个函数作用相同，返回当前系统的日期值
CURDATE() 和 CURRENT_DATE() 函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回，具体格式根据函数用在字符串或数字语境中而定，返回的
date类型。
mysql> select curdate(),current_date(),current_date()+1; +------------+----------------+------------------+ | curdate() | current_date() | current_date()+1 | +------------+----------------+------------------+ | 2019-09-17 | 2019-09-17 | 20190918 | +------------+----------------+------------------+ 1 row in set (0.00 sec)
curtime 和 current_time:获取系统当前时间
CURTIME() 和 CURRENT_TIME() 函数的作用相同，将当前时间以“HH：MM：SS”或“HHMMSS”格式返回，具体格式根据函数用在字符串或数字语境中而定，返回
time类型。
mysql> select curtime(),current_time(),current_time()+1; +-----------+----------------+------------------+ | curtime() | current_time() | current_time()+1 | +-----------+----------------+------------------+ | 16:11:25 | 16:11:25 | 161126 | +-----------+----------------+------------------+ 1 row in set (0.00 sec)
now 和 sysdate:获取当前时间日期
NOW() 和 SYSDATE() 函数的作用相同，都是返回当前日期和时间值，格式为“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数用在字符串或数字语境中而定，返回
datetime类型。
mysql> select now(),sysdate(); +---------------------+---------------------+ | now() | sysdate() | +---------------------+---------------------+ | 2019-09-17 16:13:28 | 2019-09-17 16:13:28 | +---------------------+---------------------+ 1 row in set (0.00 sec)
unix_timestamp:获取UNIX时间戳
UNIX_TIMESTAMP(date) 若无参数调用，返回一个无符号整数类型的 UNIX 时间戳（'1970-01-01 00:00:00'GMT之后的秒数）。
mysql> select unix_timestamp(),unix_timestamp(now()),now(),unix_timestamp('2019-09-17 12:00:00'); +------------------+-----------------------+---------------------+---------------------------------------+ | unix_timestamp() | unix_timestamp(now()) | now() | unix_timestamp('2019-09-17 12:00:00') | +------------------+-----------------------+---------------------+---------------------------------------+ | 1568710893 | 1568710893 | 2019-09-17 17:01:33 | 1568692800 | +------------------+-----------------------+---------------------+---------------------------------------+ 1 row in set (0.00 sec)
from_unixtime:时间戳转日期
FROM_UNIXTIME(unix_timestamp[,format]) 函数把 UNIX 时间戳转换为普通格式的日期时间值，与
UNIX_TIMESTAMP ()函数互为反函数。 有2个参数： unix_timestamp：时间戳（秒） format：要转化的格式 比如“”%Y-%m-%d“” 这样格式化之后的时间就是 2017-11-30 可以有的形式：
|
格式 |
说明 |
|---|---|
|
%M |
月名字(January～December) |
|
%W |
星期名字(Sunday～Saturday) |
|
%D |
有英语前缀的月份的日期(1st, 2nd, 3rd, 等等） |
|
%Y |
年, 数字, 4 位 |
|
%y |
年, 数字, 2 位 |
|
%a |
缩写的星期名字(Sun～Sat) |
|
%d |
月份中的天数, 数字(00～31) |
|
%e |
月份中的天数, 数字(0～31) |
|
%m |
月, 数字(01～12) |
|
%c |
月, 数字(1～12) |
|
%b |
缩写的月份名字(Jan～Dec) |
|
%j |
一年中的天数(001～366) |
|
%H |
小时(00～23) |
|
%k |
小时(0～23) |
|
%h |
小时(01～12) |
|
%I（i的大写） |
小时(01～12) |
|
%l（L的小写） |
小时(1～12) |
|
%i |
分钟, 数字(00～59) |
|
%r |
时间,12 小时(hh:mm:ss [AP]M) |
|
%T |
时间,24 小时(hh:mm:ss) |
|
%S |
秒(00～59) |
|
%s |
秒(00～59) |
|
%p |
AM或PM |
|
%W |
一个星期中的天数英文名称(Sunday~Saturday） |
|
%w |
一个星期中的天数(0=Sunday ～6=Saturday） |
|
%U |
星期(0～52), 这里星期天是星期的第一天 |
|
%u |
星期(0～52), 这里星期一是星期的第一天 |
|
%% |
输出% |
mysql> select from_unixtime(1568710866),from_unixtime(1568710866,'%Y-%m-%d %H:%h:%s'); +---------------------------+-----------------------------------------------+ | from_unixtime(1568710866) | from_unixtime(1568710866,'%Y-%m-%d %H:%h:%s') | +---------------------------+-----------------------------------------------+ | 2019-09-17 17:01:06 | 2019-09-17 17:05:06 | +---------------------------+-----------------------------------------------+ 1 row in set (0.00 sec)
month:获取指定日期的月份
MONTH(date) 函数返回指定 date 对应的月份，范围为 1～12。
mysql> select month('2017-12-15'),month(now()); +---------------------+--------------+ | month('2017-12-15') | month(now()) | +---------------------+--------------+ | 12 | 9 | +---------------------+--------------+ 1 row in set (0.00 sec)
monthname:获取指定日期月份的英文名称
MONTHNAME(date) 函数返回日期 date 对应月份的英文全名。
mysql> select monthname('2017-12-15'),monthname(now()); +-------------------------+------------------+ | monthname('2017-12-15') | monthname(now()) | +-------------------------+------------------+ | December | September | +-------------------------+------------------+ 1 row in set (0.00 sec)
dayname:获取指定日期的星期名称
DAYNAME(date) 函数返回 date 对应的工作日英文名称，例如 Sunday、Monday 等。
mysql> select now(),dayname(now()); +---------------------+----------------+ | now() | dayname(now()) | +---------------------+----------------+ | 2019-09-17 17:13:08 | Tuesday | +---------------------+----------------+ 1 row in set (0.00 sec)
dayofweek:获取日期对应的周索引
DAYOFWEEK(d) 函数返回 d 对应的一周中的索引（位置）。1 表示周日，2 表示周一，……，7 表示周六。这些索引值对应于ODBC标准。
mysql> select now(),dayofweek(now()); +---------------------+------------------+ | now() | dayofweek(now()) | +---------------------+------------------+ | 2019-09-17 17:14:21 | 3 | +---------------------+------------------+ 1 row in set (0.00 sec)
week:获取指定日期是一年中的第几周
WEEK(date[,mode]) 函数计算日期 date 是一年中的第几周。WEEK(date,mode) 函数允许指定星期是否起始于周日或周一，以及返回值的范围是否为 0～52 或 1～53。 WEEK函数接受两个参数：
date是要获取周数的日期。mode是一个可选参数，用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始，返回的周数应在0到52之间或0到53之间。
如果忽略mode参数，默认情况下WEEK函数将使用default_week_format系统变量的值。 要获取default_week_format变量的当前值，请使用SHOW VARIABLES语句如下：
mysql> SHOW VARIABLES LIKE 'default_week_format'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | default_week_format | 0 | +---------------------+-------+ 1 row in set, 1 warning (0.00 sec)
在我们的服务器中，default_week_format的默认值为0，下表格说明了mode参数如何影响WEEK函数：
|
模式 |
一周的第一天 |
范围 |
|---|---|---|
|
0 |
星期日 |
0-53 |
|
1 |
星期一 |
0-53 |
|
2 |
星期日 |
1-53 |
|
3 |
星期一 |
1-53 |
|
4 |
星期日 |
0-53 |
|
5 |
星期一 |
0-53 |
|
6 |
星期日 |
1-53 |
|
7 |
星期一 |
1-53 |
上表中“今年有4天以上”表示：
- 如果星期包含1月1日，并且在新的一年中有
4天或更多天，那么这周是第1周。 - 否则，这一周的数字是前一年的最后一周，下周是第1周。
mysql> select now(),week(now()); +---------------------+-------------+ | now() | week(now()) | +---------------------+-------------+ | 2019-09-17 17:20:28 | 37 | +---------------------+-------------+ 1 row in set (0.00 sec)
dayofyear:获取指定日期在一年中的位置
DAYOFYEAR(d) 函数返回 d 是一年中的第几天，范围为 1～366。
mysql> select now(),dayofyear(now()),dayofyear('2019-01-01'); +---------------------+------------------+-------------------------+ | now() | dayofyear(now()) | dayofyear('2019-01-01') | +---------------------+------------------+-------------------------+ | 2019-09-17 17:22:00 | 260 | 1 | +---------------------+------------------+-------------------------+ 1 row in set (0.00 sec)
dayofmonth:获取指定日期在一个月的位置
DAYOFMONTH(d) 函数返回 d 是一个月中的第几天，范围为 1～31。
mysql> select now(),dayofmonth(now()),dayofmonth('2019-01-01'); +---------------------+-------------------+--------------------------+ | now() | dayofmonth(now()) | dayofmonth('2019-01-01') | +---------------------+-------------------+--------------------------+ | 2019-09-17 17:23:09 | 17 | 1 | +---------------------+-------------------+--------------------------+ 1 row in set (0.00 sec)
year:获取年份
YEAR() 函数可以从指定日期值中来获取年份值。
mysql> select now(),year(now()),year('2019-01-02'); +---------------------+-------------+--------------------+ | now() | year(now()) | year('2019-01-02') | +---------------------+-------------+--------------------+ | 2019-09-17 17:28:10 | 2019 | 2019 | +---------------------+-------------+--------------------+ 1 row in set (0.00 sec)
time_to_sec:将时间转换为秒值
TIME_TO_SEC(time) 函数返回将参数 time 转换为秒数的时间值，转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。
mysql> select time_to_sec('15:15:15'),now(),time_to_sec(now()); +-------------------------+---------------------+--------------------+ | time_to_sec('15:15:15') | now() | time_to_sec(now()) | +-------------------------+---------------------+--------------------+ | 54915 | 2019-09-17 17:30:44 | 63044 | +-------------------------+---------------------+--------------------+ 1 row in set (0.00 sec)
sec_to_time:将秒值转换为时间格式
SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为小时、分钟和秒数的时间值。
mysql> select sec_to_time(100),sec_to_time(10000); +------------------+--------------------+ | sec_to_time(100) | sec_to_time(10000) | +------------------+--------------------+ | 00:01:40 | 02:46:40 | +------------------+--------------------+ 1 row in set (0.00 sec)
date_add和adddate:向日期添加指定时间间隔
DATE_ADD(date,INTERVAL expr type) date：参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。 type：参数可以是下列值
|
Type 值 |
|---|
|
MICROSECOND |
|
SECOND |
|
MINUTE |
|
HOUR |
|
DAY |
|
WEEK |
|
MONTH |
|
QUARTER |
|
YEAR |
|
SECOND_MICROSECOND |
|
MINUTE_MICROSECOND |
|
MINUTE_SECOND |
|
HOUR_MICROSECOND |
|
HOUR_SECOND |
|
HOUR_MINUTE |
|
DAY_MICROSECOND |
|
DAY_SECOND |
|
DAY_MINUTE |
|
DAY_HOUR |
|
YEAR_MONTH |
mysql> select date_add('2019-01-01',INTERVAL 10 day),adddate('2019-01-01 16:00:00',interval 100 SECOND); +----------------------------------------+----------------------------------------------------+ | date_add('2019-01-01',INTERVAL 10 day) | adddate('2019-01-01 16:00:00',interval 100 SECOND) | +----------------------------------------+----------------------------------------------------+ | 2019-01-11 | 2019-01-01 16:01:40 | +----------------------------------------+----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2019-01-01',INTERVAL -10 day),adddate('2019-01-01 16:00:00',interval -100 SECOND); +-----------------------------------------+-----------------------------------------------------+ | date_add('2019-01-01',INTERVAL -10 day) | adddate('2019-01-01 16:00:00',interval -100 SECOND) | +-----------------------------------------+-----------------------------------------------------+ | 2018-12-22 | 2019-01-01 15:58:20 | +-----------------------------------------+-----------------------------------------------------+ 1 row in set (0.00 sec)
date_sub和subdate:日期减法运算
DATE_SUB(date,INTERVAL expr type) date：参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。 type的类型和date_add中的type一样。
mysql> select date_sub('2019-01-01',INTERVAL 10 day),subdate('2019-01-01 16:00:00',interval 100 SECOND); +----------------------------------------+----------------------------------------------------+ | date_sub('2019-01-01',INTERVAL 10 day) | subdate('2019-01-01 16:00:00',interval 100 SECOND) | +----------------------------------------+----------------------------------------------------+ | 2018-12-22 | 2019-01-01 15:58:20 | +----------------------------------------+----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_sub('2019-01-01',INTERVAL -10 day),subdate('2019-01-01 16:00:00',interval -100 SECOND); +-----------------------------------------+-----------------------------------------------------+ | date_sub('2019-01-01',INTERVAL -10 day) | subdate('2019-01-01 16:00:00',interval -100 SECOND) | +-----------------------------------------+-----------------------------------------------------+ | 2019-01-11 | 2019-01-01 16:01:40 | +-----------------------------------------+-----------------------------------------------------+ 1 row in set (0.00 sec)
addtime:时间加法运算
ADDTIME(time,expr) 函数用于执行时间的加法运算。添加 expr 到 time 并返回结果。 其中：time 是一个时间或日期时间表达式，expr 是一个时间表达式。
mysql> select addtime('2019-09-18 23:59:59','0:1:1'), addtime('10:30:59','5:10:37'); +----------------------------------------+-------------------------------+ | addtime('2019-09-18 23:59:59','0:1:1') | addtime('10:30:59','5:10:37') | +----------------------------------------+-------------------------------+ | 2019-09-19 00:01:00 | 15:41:36 | +----------------------------------------+-------------------------------+ 1 row in set (0.00 sec)
subtime:时间减法运算
SUBTIME(time,expr) 函数用于执行时间的减法运算。 函数返回 time。expr 表示的值和格式 time 相同。time 是一个时间或日期时间表达式， expr 是一个时间。
mysql> select subtime('2019-09-18 23:59:59','0:1:1'),subtime('10:30:59','5:12:37'); +----------------------------------------+-------------------------------+ | subtime('2019-09-18 23:59:59','0:1:1') | subtime('10:30:59','5:12:37') | +----------------------------------------+-------------------------------+ | 2019-09-18 23:58:58 | 05:18:22 | +----------------------------------------+-------------------------------+ 1 row in set (0.00 sec)
datediff:获取两个日期的时间间隔
DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。
mysql> select datediff('2017-11-30','2017-11-29') as col1, datediff('2017-11-30','2017-12-15') as col2; +------+------+ | col1 | col2 | +------+------+ | 1 | -15 | +------+------+ 1 row in set (0.00 sec)
date_format:格式化指定的日期
DATE_FORMAT(date，format) 函数是根据 format 指定的格式显示 date 值。 DATE_FORMAT() 函数接受两个参数： date：是要格式化的有效日期值format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。 format：格式和上面的函数
from_unixtime中的format一样，可以参考上面的。
mysql> select date_format('2017-11-30','%Y%m%d') as col0,now() as col1, date_format(now(),'%Y%m%d%H%i%s') as col2; +----------+---------------------+----------------+ | col0 | col1 | col2 | +----------+---------------------+----------------+ | 20171130 | 2019-09-17 17:56:12 | 20190917175612 | +----------+---------------------+----------------+ 1 row in set (0.00 sec)
weekday:获取指定日期在一周内的索引位置
WEEKDAY(date) 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
mysql> select now(),weekday(now()); +---------------------+----------------+ | now() | weekday(now()) | +---------------------+----------------+ | 2019-09-17 18:01:34 | 1 | +---------------------+----------------+ 1 row in set (0.00 sec) mysql> select now(),dayofweek(now()); +---------------------+------------------+ | now() | dayofweek(now()) | +---------------------+------------------+ | 2019-09-17 18:01:34 | 3 | +---------------------+------------------+ 1 row in set (0.00 sec)
MySQL 聚合函数
|
函数名称 |
作用 |
|---|---|
|
max |
查询指定列的最大值 |
|
min |
查询指定列的最小值 |
|
count |
统计查询结果的行数 |
|
sum |
求和，返回指定列的总和 |
|
avg |
求平均值，返回指定列数据的平均值 |
MySQL 流程控制函数
|
函数名称 |
作用 |
|---|---|
|
if |
判断，流程控制 |
|
ifnull |
判断是否为空 |
|
case |
搜索语句 |
if:判断
IF(expr,v1,v2) 当 expr 为真是返回 v1 的值，否则返回 v2
mysql> select if(1<2,1,0) c1,if(1>5,'√','×') c2,if(strcmp('abc','ab'),'yes','no') c3; +----+----+-----+ | c1 | c2 | c3 | +----+----+-----+ | 1 | × | yes | +----+----+-----+ 1 row in set (0.00 sec)
ifnull:判断是否为空
IFNULL(v1,v2)：v1为空返回v2，否则返回v1。
mysql> select ifnull(null,'路人甲Java'),ifnull('非空','为空'); +------------------------------+---------------------------+ | ifnull(null,'路人甲Java') | ifnull('非空','为空') | +------------------------------+---------------------------+ | 路人甲Java | 非空 | +------------------------------+---------------------------+ 1 row in set (0.00 sec)
case:搜索语句,类似于java中的if..else if..else
类似于java中的if..else if..else
有2种写法
方式1：
CASE <表达式> WHEN <值1> THEN <操作> WHEN <值2> THEN <操作> ... ELSE <操作> END CASE;
方式2：
CASE WHEN <条件1> THEN <命令> WHEN <条件2> THEN <命令> ... ELSE commands END CASE;
示例：
准备数据：
CREATE TABLE t_stu ( id INT AUTO_INCREMENT COMMENT '编号', name VARCHAR(10) COMMENT '姓名', sex TINYINT COMMENT '性别,0:未知,1:男,2:女', PRIMARY KEY (id) ) COMMENT '学生表'; insert into t_stu (name,sex) VALUES ('张学友',1), ('刘德华',1), ('郭富城',1), ('蔡依林',2), ('xxx',0);
mysql> select * from t_stu; +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 1 | 张学友 | 1 | | 2 | 刘德华 | 1 | | 3 | 郭富城 | 1 | | 4 | 蔡依林 | 2 | | 5 | xxx | 0 | +----+-----------+------+ 5 rows in set (0.00 sec)
需求：查询所有学生信息，输出：姓名，性别（男、女、未知），如下：
mysql> SELECT t.name 姓名, (CASE t.sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END) 性别 FROM t_stu t; +-----------+--------+ | 姓名 | 性别 | +-----------+--------+ | 张学友 | 男 | | 刘德华 | 男 | | 郭富城 | 男 | | 蔡依林 | 女 | | xxx | 未知 | +-----------+--------+ 5 rows in set (0.00 sec) mysql> SELECT t.name 姓名, (CASE WHEN t.sex = 1 THEN '男' WHEN t.sex = 2 THEN '女' ELSE '未知' END) 性别 FROM t_stu t; +-----------+--------+ | 姓名 | 性别 | +-----------+--------+ | 张学友 | 男 | | 刘德华 | 男 | | 郭富城 | 男 | | 蔡依林 | 女 | | xxx | 未知 | +-----------+--------+ 5 rows in set (0.00 sec)
其他函数
|
函数名称 |
作用 |
|---|---|
|
version |
数据库版本号 |
|
database |
当前的数据库 |
|
user |
当前连接用户 |
|
password |
返回字符串密码形式 |
|
md5 |
返回字符串的md5数据 |
mysql> SELECT version(); +------------+ | version() | +------------+ | 5.7.25-log | +------------+ 1 row in set (0.00 sec) mysql> SELECT database(); +--------------+ | database() | +--------------+ | javacode2018 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> SELECT password('123456'); +-------------------------------------------+ | password('123456') | +-------------------------------------------+ | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT md5('123456'); +----------------------------------+ | md5('123456') | +----------------------------------+ | e10adc3949ba59abbe56e057f20f883e | +----------------------------------+ 1 row in set (0.00 sec)
今天介绍函数比较多，大家搜藏一下，慢慢消化，喜欢的帮忙转发一下，谢谢。
mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!
