博客园 首页 新随笔 订阅 管理 随笔 - 62 文章 - 0 评论 - 36
这是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 |
+------------------+-----------------------+---------------------+-------------------------------