mysql常用函数
MySql常用函数
目录
- MySql常用函数
- 1.数值型函数
- 2.字符串函数
- 3.日期和时间函数
- curdate 和 current_date：两个函数作用相同，返回当前系统的日期值
- curtime 和 current_time:获取系统当前时间
- now 和 sysdate：获取当前时间日期
- unix_timestamp:获取UNIX时间戳
- from_unixtime：时间戳转日期
- month：获取指定月份
- monthname：获取指定日期月份的英文全称
- dayname：获取指定日期的星期名称
- dayofweek：获取日期对应的周索引
- week：获取指定日期是一年中的第几周
- weekday：获取指定日期在一周内的索引
- dayofyear：获取指定日期在一年中的位置
- dayogmonth：获取指定日期在一个月中的位置
- year：获取年份
- time_to_sec：将时间转换为秒值
- sec_to_time：将秒值转换为时间格式
- date_add 和 adddate：向日期添加指定时间间隔
- date_sub 和 subdate：日期减法运算
- addtime：时间加法运算
- subtime：时间减法运算
- datediff：获取两个日期的时间间隔
- date_format：格式化指定的日期
- 4.聚合函数
- 5.流程控制函数
- 6.系统信息函数
- 7.加密函数
- 8.其他函数
1.数值型函数
| 函数名 | 作用 |
|---|---|
| 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(3),abs(-2.3),abs(-2.7),abs(-23),abs(0);
+--------+-----------+-----------+----------+--------+
| abs(3) | abs(-2.3) | abs(-2.7) | abs(-23) | abs(0) |
+--------+-----------+-----------+----------+--------+
| 3 | 2.3 | 2.7 | 23 | 0 |
+--------+-----------+-----------+----------+--------+
1 row in set (0.00 sec)
sqrt：求二次方根（开平方）
函数 SQRT(x) 返回非负数 x 的二次方根。负数没有平方根，返回结果为 NULL。
mysql> select sqrt(4),sqrt(9),sqrt(-9),-sqrt(abs(-9)),sqrt(3);
+---------+---------+----------+----------------+--------------------+
| sqrt(4) | sqrt(9) | sqrt(-9) | -sqrt(abs(-9)) | sqrt(3) |
+---------+---------+----------+----------------+--------------------+
| 2 | 3 | NULL | -3 | 1.7320508075688772 |
+---------+---------+----------+----------------+--------------------+
1 row in set (0.00 sec)
mod：求余数
函数 MOD(x,y) 返回 x 被 y 除后的余数，MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的余数。
mysql> select mod(10,3),mod(11.5,3),mod(10,-3);
+-----------+-------------+------------+
| mod(10,3) | mod(11.5,3) | mod(10,-3) |
+-----------+-------------+------------+
| 1 | 2.5 | 1 |
+-----------+-------------+------------+
1 row in set (0.00 sec)
ceil和ceiling：向上取整
函数 CEIL(x) 和 CEILING(x) 的意义相同，返回不小于 x 的最小整数值，返回值转化为一个BIGINT。
mysql> select ceil(-2.3),ceiling(2.3);
+------------+--------------+
| ceil(-2.3) | ceiling(2.3) |
+------------+--------------+
| -2 | 3 |
+------------+--------------+
1 row in set (0.00 sec)
floor:向下取整
floor(x) 函数返回小于 x 的最大整数值。
mysql> select floor(23),floor(23.33),floor(-23.33),floor(-23.66);
+-----------+--------------+---------------+---------------+
| floor(23) | floor(23.33) | floor(-23.33) | floor(-23.66) |
+-----------+--------------+---------------+---------------+
| 23 | 23 | -24 | -24 |
+-----------+--------------+---------------+---------------+
1 row in set (0.00 sec)
rand：生产随机数
生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
mysql> select rand(),rand(),rand();
+---------------------+--------------------+--------------------+
| rand() | rand() | rand() |
+---------------------+--------------------+--------------------+
| 0.39351383819572056 | 0.9848400773339346 | 0.7436589028161568 |
+---------------------+--------------------+--------------------+
1 row in set (0.00 sec)
mysql> select rand(1),rand(1),rand(2);
+---------------------+---------------------+--------------------+
| rand(1) | rand(1) | rand(2) |
+---------------------+---------------------+--------------------+
| 0.40540353712197724 | 0.40540353712197724 | 0.6555866465490187 |
+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)
mysql> select rand(2),rand(2),rand(1);
+--------------------+--------------------+---------------------+
| rand(2) | rand(2) | rand(1) |
+--------------------+--------------------+---------------------+
| 0.6555866465490187 | 0.6555866465490187 | 0.40540353712197724 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)
round：四舍五入函数
返回最接近于参数 x 的整数；ROUND(x,y) 函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。
mysql> select round(5.5),round(5.4),round(-5.5),round(-5.4);
+------------+------------+-------------+-------------+
| round(5.5) | round(5.4) | round(-5.5) | round(-5.4) |
+------------+------------+-------------+-------------+
| 6 | 5 | -6 | -5 |
+------------+------------+-------------+-------------+
1 row in set (0.00 sec)
mysql> select round(5.556,2),round(5.44,3),round(55.66,-1),round(55.66,-2);
+----------------+---------------+-----------------+-----------------+
| round(5.556,2) | round(5.44,3) | round(55.66,-1) | round(55.66,-2) |
+----------------+---------------+-----------------+-----------------+
| 5.56 | 5.440 | 60 | 100 |
+----------------+---------------+-----------------+-----------------+
1 row in set (0.00 sec)
sign：返回参数符号
这个函数返回X的符号(负数，零或正)对应-1，0或1
mysql> select sign(-23),sign(0),sign(23);
+-----------+---------+----------+
| sign(-23) | sign(0) | sign(23) |
+-----------+---------+----------+
| -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),power(5,2),pow(10,0),pow(10,-1);
+----------+------------+-----------+------------+
| pow(5,2) | power(5,2) | pow(10,0) | pow(10,-1) |
+----------+------------+-----------+------------+
| 25 | 25 | 1 | 0.1 |
+----------+------------+-----------+------------+
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.03 sec)
sin(1/2π)=1
sin(0.5*pi())=1
pi()函数返回π
三角函数并不常用，不做总结
2.字符串函数
| 函数名 | 作用 |
|---|---|
| length | 计算字符串长度函数，返回字符串的字节长度 |
| concat | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 |
| insert | 替换字符串函数 |
| lower | 将字符串中的字母转换为小写 |
| upper | 将字符串中的字母转换为大写 |
| left | 从左侧字截取符串，返回字符串左边的若干个字符 |
| right | 从右侧字截取符串，返回字符串右边的若干个字符 |
| trim | 删除字符串左右两侧的空格 |
| replace | 字符串替换函数，返回替换后的新字符串 |
| substr 和substring | 截取字符串，返回从指定位置开始的指定长度的字符换 |
| reverse | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
length：返回字符串直接长度
返回值为字符串的字节长度，使用 uft8（UNICODE 的一种变长字符编码，又称万国码）编码字>符集时，一个汉字是 3 个字节，一个数字或字母是一个字节。
mysql> select length('12345abcde'),length('字符串'),length('字符串123');
+----------------------+---------------------+------------------------+
| length('12345abcde') | length('字符串') | length('字符串123') |
+----------------------+---------------------+------------------------+
| 10 | 9 | 12 |
+----------------------+---------------------+------------------------+
1 row in set (0.27 sec)
concat：合并字符串
CONCAT(sl，s2，...) 函数返回结果为连接参数产生的字符串，或许有一个或多个参数。
若有任何一个参数为 NULL，则返回值为 NULL。若所有参数均为非二进制字符串，则结果为非二进制字符串。若自变量中含有任一二进制字符串，则结果为一个二进制字符串。
mysql> select concat('aaa','bbb'),concat(null,'aaa','bbb'),concat('abc','123');
+---------------------+--------------------------+---------------------+
| concat('aaa','bbb') | concat(null,'aaa','bbb') | concat('abc','123') |
+---------------------+--------------------------+---------------------+
| aaabbb | NULL | abc123 |
+---------------------+--------------------------+---------------------+
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('abcdefg',3,4,'test'),insert('abcdefg',-1,4,'test'),insert('abcdefg',3,100,'test');
+------------------------------+-------------------------------+--------------------------------+
| insert('abcdefg',3,4,'test') | insert('abcdefg',-1,4,'test') | insert('abcdefg',3,100,'test') |
+------------------------------+-------------------------------+--------------------------------+
| abtestg | abcdefg | abtest |
+------------------------------+-------------------------------+--------------------------------+
1 row in set (0.00 sec)
lower：将字母转成小写
LOWER(str) 可以将字符串 str 中的字母字符全部转换成小写。
mysql> select lower('大写ABC');
+--------------------+
| lower('大写ABC') |
+--------------------+
| 大写abc |
+--------------------+
1 row in set (0.01 sec)
upper：将字母转成大写
UPPER(str) 可以将字符串 str 中的字母字符全部转换成大写。
mysql> select upper('mysql');
+----------------+
| upper('mysql') |
+----------------+
| MYSQL |
+----------------+
1 row in set (0.04 sec)
left: 从左侧截取字符串
LEFT(s，n) 函数返回字符串 s 最左边的 n 个字符，s=1表示第一个字符。
mysql> select left('字符串Abc',2), left('字符串Abc',4) , left('字符串Abc',0), left('字符串Abc',-1);
+------------------------+------------------------+------------------------+-------------------------+
| left('字符串Abc',2) | left('字符串Abc',4) | left('字符串Abc',0) | left('字符串Abc',-1) |
+------------------------+------------------------+------------------------+-------------------------+
| 字符 | 字符串A | | |
+------------------------+------------------------+------------------------+-------------------------+
1 row in set (0.00 sec)
right：从右侧截取字符串
RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符。
mysql> select right('字符串Abc',2), right('字符串Abc',4) , right('字符串Abc',10), right('字符串Abc',-1);
+-------------------------+-------------------------+--------------------------+--------------------------+
| right('字符串Abc',2) | right('字符串Abc',4) | right('字符串Abc',10) | right('字符串Abc',-1) |
+-------------------------+-------------------------+--------------------------+--------------------------+
| bc | 串Abc | 字符串Abc | |
+-------------------------+-------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)
trim：删除字符串两侧空格
TRIM(s) 删除字符串 s 两侧的空格。
mysql> select '[ 字符串 ]',concat('[',trim(' 字符串 '),']');
+-----------------+---------------------------------------+
| [ 字符串 ] | concat('[',trim(' 字符串 '),']') |
+-----------------+---------------------------------------+
| [ 字符串 ] | [字符串] |
+-----------------+---------------------------------------+
1 row in set (0.00 sec)
replace：字符串替换
REPLACE(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1。
mysql> select replace('aabbcc','a','A');
+---------------------------+
| replace('aabbcc','a','A') |
+---------------------------+
| AAbbcc |
+---------------------------+
1 row in set (0.00 sec)
substr或substring：截取字符串
SUBSTR(str,pos)
截取从pos位置开始到最后的所有str字符串
SUBSTR (str, pos, len)
参数说明:
- str为列名/字符串；
- pos为起始位置；mysql中的起始位置pos是从1开始的；如果为正数，就表示从正数的位置往下截取字符串（起始坐标从1开始），反之如果起始位置pos为负数，那么 表示就从倒数第几个开始截取；
- len为截取字符个数/长度。
mysql> select substring('abcdefg',3);
+------------------------+
| substring('abcdefg',3) |
+------------------------+
| cdefg |
+------------------------+
1 row in set (0.00 sec)
mysql> select substring('abcdefg',-3);
+-------------------------+
| substring('abcdefg',-3) |
+-------------------------+
| efg |
+-------------------------+
1 row in set (0.00 sec)
mysql> select substring('abcdefg',-3,2);
+---------------------------+
| substring('abcdefg',-3,2) |
+---------------------------+
| ef |
+---------------------------+
1 row in set (0.00 sec)
reverse：反转字符串
REVERSE(s) 可以将字符串 s 反转，返回的字符串的顺序和 s 字符串的顺序相反。
mysql> select reverse('字符串abc123');
+----------------------------+
| reverse('字符串abc123') |
+----------------------------+
| 321cba串符字 |
+----------------------------+
1 row in set (0.00 sec)
3.日期和时间函数
| 函数名 | 作用 |
|---|---|
| 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 now(),curdate(),current_date();
+---------------------+------------+--------------+
| now() | curdate() | current_date |
+---------------------+------------+--------------+
| 2021-02-24 00:57:30 | 2021-02-24 | 2021-02-24 |
+---------------------+------------+--------------+
1 row in set (0.00 sec)
curtime 和 current_time:获取系统当前时间
CURTIME() 和 CURRENT_TIME() 函数的作用相同，将当前时间以“HH：MM：SS”或“HHMMSS”格式返回，具体格式根据函数用在字符串或数字语境中而定，返回 time 类型。
mysql> select now(),curdate(),curtime(),current_time(),current_time()+1;
+---------------------+------------+-----------+----------------+------------------+
| now() | curdate() | curtime() | current_time() | current_time()+1 |
+---------------------+------------+-----------+----------------+------------------+
| 2021-02-24 01:12:26 | 2021-02-24 | 01:12:26 | 01:12:26 | 11227 |
+---------------------+------------+-----------+----------------+------------------+
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() |
+---------------------+---------------------+
| 2021-02-24 01:14:06 | 2021-02-24 01:14:06 |
+---------------------+---------------------+
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('2021-02-23 00:00:00');
+------------------+-----------------------+---------------------+---------------------------------------+
| unix_timestamp() | unix_timestamp(now()) | now() | unix_timestamp('2021-02-23 00:00:00') |
+------------------+-----------------------+---------------------+---------------------------------------+
| 1614155346 | 1614155346 | 2021-02-24 03:29:06 | 1614056400 |
+------------------+-----------------------+---------------------+---------------------------------------+
1 row in set (0.00 sec)
from_unixtime：时间戳转日期
FROM_UNIXTIME(unix_timestamp[,format]) 函数把 UNIX 时间戳转换为普通格式的日期时间值，与 UNIX_TIMESTAMP () 函数互为反函数。
有2个参数：
unix_timestamp：时间戳（秒）
format：要转化的格式 比如“”%Y-%m-%d“” 这样格式化之后的时间就是 2020-11-11这样的格式
可以有的形式：
| 格式 | 说明 |
|---|---|
| %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(1614155346),from_unixtime(1614155346,'%Y-%m-%d %H:%i:%s');
+---------------------------+-----------------------------------------------+
| from_unixtime(1614155346) | from_unixtime(1614155346,'%Y-%m-%d %H:%i:%s') |
+---------------------------+-----------------------------------------------+
| 2021-02-24 03:29:06 | 2021-02-24 03:29:06 |
+---------------------------+-----------------------------------------------+
1 row in set (0.00 sec)
month：获取指定月份
MONTH(date) 函数返回指定 date 对应的月份，范围为 1～12。
mysql> select month('2021-02-24 03:29:06'),month(now());
+------------------------------+--------------+
| month('2021-02-24 03:29:06') | month(now()) |
+------------------------------+--------------+
| 2 | 2 |
+------------------------------+--------------+
1 row in set (0.00 sec)
monthname：获取指定日期月份的英文全称
MONTHNAME(date) 函数返回日期 date 对应月份的英文全名
mysql> select monthname('2021-01-24 03:29:06'),monthname(now());
+----------------------------------+------------------+
| monthname('2021-01-24 03:29:06') | monthname(now()) |
+----------------------------------+------------------+
| January | February |
+----------------------------------+------------------+
1 row in set (0.00 sec)
dayname：获取指定日期的星期名称
DAYNAME(date) 函数返回 date 对应的工作日英文名称，例如 Sunday、Monday 等
mysql> select now(),dayname(now());
+---------------------+----------------+
| now() | dayname(now()) |
+---------------------+----------------+
| 2021-02-24 22:25:40 | Wednesday |
+---------------------+----------------+
1 row in set (0.00 sec)
dayofweek：获取日期对应的周索引
DAYOFWEEK(d) 函数返回 d 对应的一周中的索引（位置）。1 表示周日，2 表示周一，……，7 表示周六。这些索引值对应于ODBC标准。
mysql> select now(),dayofweek(now());
+---------------------+------------------+
| now() | dayofweek(now()) |
+---------------------+------------------+
| 2021-02-25 00:34:38 | 5 |
+---------------------+------------------+
1 row in set (0.00 sec)
#5表示周四
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 (0.01 sec)
default_week_format 的默认值为 0 ，下表格说明了 mode 参数如何影响 WEEK 函数：
| 模式值 | 描述 |
|---|---|
| 0 | 星期的第一天是星期日 |
| 1 | 一周的第一天是星期一，一年的第一周超过3天 |
| 2 | 星期的第一天是星期日 |
| 3 | 一周的第一天是星期一，一年的第一周超过3天 |
| 4 | 一周的第一天是星期日，一年的第一周超过3天 |
| 5 | 一周的第一天是星期一 |
| 6 | 一周的第一天是星期日，一年的第一周超过3天 |
| 7 | 一周的第一天是星期一 |
一年的第一周超过三天指的是：
- 如果星期包含1月1日这天，并且在新的一年中有 4 天或更多天，那么这周是第 1 周
- 否则，这一周的数字是前一年的最后一周，下周是第1周。
mysql> select now(),week(now());
+---------------------+-------------+
| now() | week(now()) |
+---------------------+-------------+
| 2021-02-25 01:35:05 | 8 |
+---------------------+-------------+
1 row in set (0.00 sec)
weekday：获取指定日期在一周内的索引
WEEKDAY(date) 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
#3对应星期四
mysql> select now(),weekday(now());
+---------------------+----------------+
| now() | weekday(now()) |
+---------------------+----------------+
| 2021-02-25 03:42:12 | 3 |
+---------------------+----------------+
1 row in set (0.00 sec)
#ODBC标准，1周日.....7周六
mysql> select now(),dayofweek(now());
+---------------------+------------------+
| now() | dayofweek(now()) |
+---------------------+------------------+
| 2021-02-25 03:42:58 | 5 |
+---------------------+------------------+
1 row in set (0.00 sec)
dayofyear：获取指定日期在一年中的位置
DAYOFYEAR(d) 函数返回 d 是一年中的第几天，范围为 1～366。
mysql> select now(),dayofyear(now()),dayofyear('20210101');
+---------------------+------------------+-----------------------+
| now() | dayofyear(now()) | dayofyear('20210101') |
+---------------------+------------------+-----------------------+
| 2021-02-25 02:00:42 | 56 | 1 |
+---------------------+------------------+-----------------------+
1 row in set (0.00 sec)
dayogmonth：获取指定日期在一个月中的位置
DAYOFMONTH(d) 函数返回 d 是一个月中的第几天，范围为 1～31。
mysql> select now(),dayofmonth(now()),dayofmonth('20210101');
+---------------------+-------------------+------------------------+
| now() | dayofmonth(now()) | dayofmonth('20210101') |
+---------------------+-------------------+------------------------+
| 2021-02-25 02:06:26 | 25 | 1 |
+---------------------+-------------------+------------------------+
1 row in set (0.00 sec)
year：获取年份
YEAR() 函数可以从指定日期值中来获取年份值。
mysql> select now(),year(now()),year('20200202');
+---------------------+-------------+------------------+
| now() | year(now()) | year('20200202') |
+---------------------+-------------+------------------+
| 2021-02-25 02:10:51 | 2021 | 2020 |
+---------------------+-------------+------------------+
1 row in set (0.00 sec)
time_to_sec：将时间转换为秒值
TIME_TO_SEC(time) 函数返回将参数 time 转换为秒数的时间值，转换公式为“小时 ×3600+ 分钟×60+ 秒”。
mysql> mysql> select now(),time_to_sec(now()),time_to_sec('2021-02-25 02:20:00'),time_to_sec('02:20:00');
+---------------------+--------------------+------------------------------------+-------------------------+
| now() | time_to_sec(now()) | time_to_sec('2021-02-25 02:20:00') | time_to_sec('02:20:00') |
+---------------------+--------------------+------------------------------------+-------------------------+
| 2021-02-25 02:14:58 | 8098 | 8400 | 8400 |
+---------------------+--------------------+------------------------------------+-------------------------+
1 row in set (0.00 sec)
sec_to_time：将秒值转换为时间格式
SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为小时、分钟和秒数的时间值。
mysql> select sec_to_time(60),sec_to_time(1230);
+-----------------+-------------------+
| sec_to_time(60) | sec_to_time(1230) |
+-----------------+-------------------+
| 00:01:00 | 00:20:30 |
+-----------------+-------------------+
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('2021-01-01',INTERVAL 10 day),adddate('2021-01-01 16:00:00',interval 100
SECOND);
+----------------------------------------+----------------------------------------------------+
| date_add('2021-01-01',INTERVAL 10 day) | adddate('2021-01-01 16:00:00',interval 100 SECOND) |
+----------------------------------------+----------------------------------------------------+
| 2021-01-11 | 2021-01-01 16:01:40 |
+----------------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2021-01-01',INTERVAL -10 day),adddate('2021-01-01 16:00:00',interval -100
+-----------------------------------------+-----------------------------------------------------+
| date_add('2021-01-01',INTERVAL -10 day) | adddate('2021-01-01 16:00:00',interval -100 SECOND) |
+-----------------------------------------+-----------------------------------------------------+
| 2020-12-22 | 2021-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('2021-01-01',INTERVAL 10 day),subdate('2021-01-01 17:00:00',INTERVAL 100
SECOND);
+----------------------------------------+-----------------------------------------------------+
| date_sub('2021-01-01',INTERVAL 10 day) | subdate('2021-01-01 17:00:00',INTERVAL 100 SECOND) |
+----------------------------------------+-----------------------------------------------------+
| 2020-12-22 | 2021-01-01 16:58:20 |
+----------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('2021-01-01',INTERVAL -10 day),subdate('2021-01-01 17:00:00',INTERVAL -100 SECOND);
+-----------------------------------------+------------------------------------------------------+
| date_sub('2021-01-01',INTERVAL -10 day) | subdate('2021-01-01 17:00:00',INTERVAL -100 SECOND) |
+-----------------------------------------+------------------------------------------------------+
| 2021-01-11 | 2021-01-01 17:01:40 |
+-----------------------------------------+------------------------------------------------------+
1 row in set (0.00 sec)
addtime：时间加法运算
ADDTIME(time,expr) 函数用于执行时间的加法运算。添加 expr 到 time 并返回结果。
其中：time 是一个时间或日期时间表达式，expr 是一个时间表达式。
mysql> select addtime('2021-02-22 23:59:59','0:1:1'), addtime('10:30:59','5:10:37');
+----------------------------------------+-------------------------------+
| addtime('2021-02-22 23:59:59','0:1:1') | addtime('10:30:59','5:10:37') |
+----------------------------------------+-------------------------------+
| 2021-02-23 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('2021-02-22 23:59:59','0:1:1'),subtime('10:30:59','5:12:37');
+----------------------------------------+-------------------------------+
| subtime('2021-02-22 23:59:59','0:1:1') | subtime('10:30:59','5:12:37') |
+----------------------------------------+-------------------------------+
| 2021-02-22 23:58:58 | 05:18:22 |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)
datediff：获取两个日期的时间间隔
DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2为日期或 date-and-time 表达式。计算时只用到这些值的日期部分
#date1-date2
mysql> select datediff('2023-11-10','2022-11-10') as col1, datediff('2021-11-30','2021-12-15') as col2;
+------+------+
| col1 | col2 |
+------+------+
| 365 | -15 |
+------+------+
1 row in set (0.00 sec)
date_format：格式化指定的日期
DATE_FORMAT(date，format) 函数是根据 format 指定的格式显示 date 值。
DATE_FORMAT() 函数接受两个参数：
date：是要格式化的有效日期
format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。format：格式和上面的函数 from_unixtime 中的format一样，可以参考上面的。
4.聚合函数
| 函数 | 作用 |
|---|---|
| max | 查询指定列的最大值 |
| min | 查询指定列的最小值 |
| count | 统计查询结果的行数 |
| sum | 求和，返回指定列的总和 |
| agv | 求平均值，返回指定列数据的平均值 |
5.流程控制函数
| 函数名 | 作用 |
|---|---|
| if | 判断，流程控制 |
| ifnull | 判断是否非空 |
| case | 搜索语句 |
if
IF(expr,v1,v2)
当 expr 为真是返回 v1 的值，否则返回 v2
mysql> select if(1+1>2,'T','F') as colum1,if(1>2,'yes','no') as colum2;
+--------+--------+
| colum1 | colum2 |
+--------+--------+
| F | no |
+--------+--------+
1 row in set (0.00 sec)
ifnull：判断是否非空
IFNULL(v1,v2)：v1为空返回v2，否则返回v1。
mysql> select ifnull(null,'abc'),ifnull('acb','def'),ifnull(null,'abc');
+--------------------+---------------------+--------------------+
| ifnull(null,'abc') | ifnull('acb','def') | ifnull(null,'abc') |
+--------------------+---------------------+--------------------+
| abc | acb | abc |
+--------------------+---------------------+--------------------+
1 row in set (0.00 sec)
case：搜索语句
有两种写法
方式1：
CASE <表达式>
WHEN <值1> THEN <操作>
WHEN <值2> THEN <操作>
...
ELSE <操作>
END CASE;
方式2：
CASE
WHEN <条件1> THEN <命令>
WHEN <条件2> THEN <命令>
...
ELSE commands
END CASE;
示例：
准备数据如下:
mysql> 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);
Query OK, 0 rows affected (0.01 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 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 as 姓名,
(CASE t.sex
WHEN 1 THEN '男'
WHEN 2 THEN '女'
ELSE '未知' END) as 性别
FROM t_stu t;
+-----------+--------+
| 姓名 | 性别 |
+-----------+--------+
| 张学友 | 男 |
| 刘德华 | 男 |
| 郭富城 | 男 |
| 蔡依林 | 女 |
| xxx | 未知 |
+-----------+--------+
5 rows in set (0.00 sec)
mysql> SELECT t.name as 姓名,
(CASE
WHEN t.sex=1 THEN '男'
WHEN t.sex=2 THEN '女'
ELSE '未知' END) as 性别
FROM t_stu t;
+-----------+--------+
| 姓名 | 性别 |
+-----------+--------+
| 张学友 | 男 |
| 刘德华 | 男 |
| 郭富城 | 男 |
| 蔡依林 | 女 |
| xxx | 未知 |
+-----------+--------+
5 rows in set (0.00 sec)
6.系统信息函数
系统信息函数用来查询MySQL数据库的系统信息。
| 函数 | 说明 |
|---|---|
| VERSION() | 获取数据库的版本号。 |
| CONNECTION_ID() | 获取服务器的连接数。 |
| DATABASE()、SCHEMA() | 获取当前数据库名。 |
| USER()、SYSTEM_USER()、SESSION_USER() | 获取当前用户名。 |
| CURRENT_USER()、CURRENT_USER | 获取当前用户名。 |
| CHARSET(str) | 获取字符串str的字符集。 |
| COLLATION(str) | 获取字符串str的字符排序方法。 |
| LAST_INSERT_ID() | 获取最近生成的AUTO_INCREMENT值。 |
#获取版本号，连接数，当前数据库名，用户名
mysql> select version() '版本',connection_id() as '连接数',user() '当前用户';
+--------+-----------+----------------+
| 版本 | 连接数 | 当前用户 |
+--------+-----------+----------------+
| 5.7.30 | 2 | root@localhost |
+--------+-----------+----------------+
1 row in set (0.00 sec)
7.加密函数
加密函数是MySQL中用来对数据进行加密的函数。用来保护敏感数据。
| 函数 | 说明 |
|---|---|
| PASSWORD(str) | 对字符串str进行加密。经此函数加密后的数据是不可逆的。其经常用于对普通数据进行加密。 |
| MD5(str) | 对字符串str进行MD5加密。经常用于对普通数据进行加密。 |
| ENCODE(str,pass_str) | 使用字符串pass_str来加密字符串str。加密后的结果是一个二进制数，必须使用BLOB类型的字段来保存它。 |
| DECODE(crypt_str,pass_str) | 使用字符串pass_str来为crypt_str解密。 |
#例如使用password来更改密码
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456
mysql>
#
mysql> select user,host,authentication_string from mysql.user where user='root' and host='localhost';
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select password('123456');
+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
8.其他函数
MySQL中除了上述内置函数以外，还包含很多函数。例如，数字格式化函数FORMAT(x,n)，IP地址与数字的转换函数INET_ATON(ip)，还有加锁函数GET_LOCT(name,time)、解锁函数RELEASE_LOCK(name)等等。
| 函数 | 说明 |
|---|---|
| FORMAT(X,D) | 将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入。 |
| CONV(N,from_base,to_base) | 不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制。 |
| INET_ATON(expr) | 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特。 |
| INET_NTOA(expr) | 给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示。 |
| BENCHMARK(count,expr) | 重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间。 |
| CONVERT(str USING charset) | 使用字符集charset表示字符串str。 |
FORMAT(X,D)函数
将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入。
mysql> SELECT FORMAT(1.2323,3);
+------------------+
| FORMAT(1.2323,3) |
+------------------+
| 1.232 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT FORMAT(1.2325,3);
+------------------+
| FORMAT(1.2325,3) |
+------------------+
| 1.233 |
+------------------+
1 row in set (0.00 sec)
