打算提升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系列目录
mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!
来源：https://itsoku.blog.csdn.net/article/details/100985736
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
