mysql09-MySQL函数
1、数学函数
1、ABS(x)和PI()
- 绝对值函数ABS(x):返回X的绝对值
- 返回圆周率的函数PI():返回圆周率π的值。默认的显示小数位数是6位。
示例:
mysql> select ABS(-3.3),ABS(3.3),PI(); +-----------+----------+----------+ | ABS(-3.3) | ABS(3.3) | PI() | +-----------+----------+----------+ | 3.3 | 3.3 | 3.141593 | +-----------+----------+----------+
2、SQRT(x)和MOD(x,y)
- 平方根函数SQRT(x):返回非负数x的二次方根。
- 求余函数MOD(x,y):返回x除以y后的余数,MOD()对于带有小数部分的数值也起作用,它返回除法运算后的精确余数。
示例:
mysql> select SQRT(9),SQRT(41),SQRT(-9),MOD(32,6),MOD(5.25,2.005); +---------+--------------------+----------+-----------+-----------------+ | SQRT(9) | SQRT(41) | SQRT(-9) | MOD(32,6) | MOD(5.25,2.005) | +---------+--------------------+----------+-----------+-----------------+ | 3 | 6.4031242374328485 | NULL | 2 | 1.240 | +---------+--------------------+----------+-----------+-----------------+
3、CEIL(x),CEILING(x)和FLOOR(x)
- 获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)
- CELL(x)和CEILING(x)意义相同,返回不小于x的最小整数值,返回值转化为一个BIGINT。
- FLOOR(x)返回不大于x的最大整数值,返回值转化为一个BIGINT。
示例:
mysql> select CEIL(3.5),CEIL(-3.5),CEILING(3.5),CEILING(-3.5),FLOOR(3.5),FLOOR(-3.5); +-----------+------------+--------------+---------------+------------+-------------+ | CEIL(3.5) | CEIL(-3.5) | CEILING(3.5) | CEILING(-3.5) | FLOOR(3.5) | FLOOR(-3.5) | +-----------+------------+--------------+---------------+------------+-------------+ | 4 | -3 | 4 | -3 | 3 | -4 | +-----------+------------+--------------+---------------+------------+-------------+
4、RAND()和RAND(x)
- RAND(x)返回一个随机浮点值v,范围在0到1之间(即0<v<1.0)。若已指定一个整数参数x,则它被用作种子值,用来产生重复序列。
- 不带参数的RAND()每次产生的随机数值是不同的。
- 当RAND(x)的参数x相同时,将产生相同的随机数,不同的x产生的随机数值不同。
示例:
mysql> select RAND(),RAND(),RAND(),RAND(1),RAND(1),RAND(2),RAND(2); +--------------------+--------------------+--------------------+---------------------+---------------------+--------------------+--------------------+ | RAND() | RAND() | RAND() | RAND(1) | RAND(1) | RAND(2) | RAND(2) | +--------------------+--------------------+--------------------+---------------------+---------------------+--------------------+--------------------+ | 0.1621509791930681 | 0.8048944760159538 | 0.5380194732342096 | 0.40540353712197724 | 0.40540353712197724 | 0.6555866465490187 | 0.6555866465490187 | +--------------------+--------------------+--------------------+---------------------+---------------------+--------------------+--------------------+
5、ROUND(x),ROUND(x,y)和TRUNCATE(x,y)
- ROUND(x)返回最接近于参数x的整数,对x值进行四舍五入。
示例1:
mysql> select ROUND(1.23),ROUND(2.64),ROUND(-1.23),ROUND(-2.64); +-------------+-------------+--------------+--------------+ | ROUND(1.23) | ROUND(2.64) | ROUND(-1.23) | ROUND(-2.64) | +-------------+-------------+--------------+--------------+ | 1 | 3 | -1 | -3 | +-------------+-------------+--------------+--------------+
- ROUND(x, y)返回最接近于参数x的数,其值保留到小数点后面y位;若y为负值,则将保留x值到小数点左边y位。(进行四舍五入)
示例2:
mysql> select ROUND(111.25,1),ROUND(-111.25,1),ROUND(115.23,-1),ROUND(-115.23,-1); +-----------------+------------------+------------------+-------------------+ | ROUND(111.25,1) | ROUND(-111.25,1) | ROUND(115.23,-1) | ROUND(-115.23,-1) | +-----------------+------------------+------------------+-------------------+ | 111.3 | -111.3 | 120 | -120 | +-----------------+------------------+------------------+-------------------+
- TRUNCATE(x, y)返回被截取至小数点后y位的数字x;若y的值为0,则结果不带有小数点或不带有小数部分;若y设为负数,则截去(归零) x小数点左起第y位开始后面所有低位的值。(截取,不进行四舍五入)
示例3:
mysql> select TRUNCATE(111.25,1),TRUNCATE(-111.25,1),TRUNCATE(115.23,-1),TRUNCATE(-115.23,-1),TRUNCATE(-115.23,0),TRUNCATE(115.23,0); +--------------------+---------------------+---------------------+----------------------+---------------------+--------------------+ | TRUNCATE(111.25,1) | TRUNCATE(-111.25,1) | TRUNCATE(115.23,-1) | TRUNCATE(-115.23,-1) | TRUNCATE(-115.23,0) | TRUNCATE(115.23,0) | +--------------------+---------------------+---------------------+----------------------+---------------------+--------------------+ | 111.2 | -111.2 | 110 | -110 | -115 | 115 | +--------------------+---------------------+---------------------+----------------------+---------------------+--------------------+
6、符号函数SIGN(x)
- 符号函数SIGN(x):返回参数的符号,x的值为负、零或正时返回结果依次为-1、0或1。
示例:
mysql> select SIGN(-11),SIGN(0),SIGN(22); +-----------+---------+----------+ | SIGN(-11) | SIGN(0) | SIGN(22) | +-----------+---------+----------+ | -1 | 0 | 1 | +-----------+---------+----------+
7、POW(x.y),POWER(x,y)和EXP(x)
- 幂运算函数POW(x,y)或者POWER(x,y)函数:返回x的y次乘方的结果值。
示例1:
mysql> select POW(2,2),POW(2,-2),POWER(2,2),POWER(2,-2); +----------+-----------+------------+-------------+ | POW(2,2) | POW(2,-2) | POWER(2,2) | POWER(2,-2) | +----------+-----------+------------+-------------+ | 4 | 0.25 | 4 | 0.25 | +----------+-----------+------------+-------------+
- 幂运算函数EXP(x):返回e的x乘方后的值。
示例2:
mysql> select EXP(2),EXP(3),EXP(-2),EXP(-3); +------------------+--------------------+--------------------+----------------------+ | EXP(2) | EXP(3) | EXP(-2) | EXP(-3) | +------------------+--------------------+--------------------+----------------------+ | 7.38905609893065 | 20.085536923187668 | 0.1353352832366127 | 0.049787068367863944 | +------------------+--------------------+--------------------+----------------------+
2、字符串函数
1、LENGTH(str)和CHAR_LENGTH(str)
- CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单"字符。
- LENGTH(str)返回值为字符串的字节长度。使用utt8(UNICODE的一种变长字符编码,又称万国码)编码字符集时,一个汉字是3个字节,一个数字成字母算一个字节。
示例:
mysql> select CHAR_LENGTH('hh123'),CHAR_LENGTH('麦恒123'),LENGTH('hh123'),LENGTH('麦恒123'); +----------------------+------------------------+-----------------+-------------------+ | CHAR_LENGTH('hh123') | CHAR_LENGTH('麦恒123') | LENGTH('hh123') | LENGTH('麦恒123') | +----------------------+------------------------+-----------------+-------------------+ | 5 | 5 | 5 | 7 | +----------------------+------------------------+-----------------+-------------------+
2、合并字符串函数
- CONCAT(s1,s2,...)返回结果为连接参数产生的字符串,或许有一个或多个参数。
- 如果所有参数均为非二进制字符串,则结果为非二进制字符串。
- 如果有任何一个参数为NULL,则返回值为NULL。
- 如果有任何一个参数为二进制字符串,则结果为一个二进制字符串。
- CONCAT_WS(x,sl,s2...)是CONCATO的特殊形式(CONCAT WS代表CONCAT With Separator)。
- 第一个参数x是其他参数的分隔符,分隔符的位置放在要连接的两个字符串之间。
- 分隔符可以是一个字符串,也可以是其他参数。
- 如果分隔符为NULL,则结果为NULL。
- 函数会忽略任何分隔符参数后的NULL值。
示例1:
mysql> select CONCAT('mai','heng','123'),CONCAT('mai','heng',null); +----------------------------+---------------------------+ | CONCAT('mai','heng','123') | CONCAT('mai','heng',null) | +----------------------------+---------------------------+ | maiheng123 | NULL | +----------------------------+---------------------------+
示例2:
mysql> select CONCAT_WS('-','mai','heng','123'),CONCAT_WS('-','mai','heng',null),CONCAT_WS(null,'mai','heng','123'); +-----------------------------------+----------------------------------+------------------------------------+ | CONCAT_WS('-','mai','heng','123') | CONCAT_WS('-','mai','heng',null) | CONCAT_WS(null,'mai','heng','123') | +-----------------------------------+----------------------------------+------------------------------------+ | mai-heng-123 | mai-heng | NULL | +-----------------------------------+----------------------------------+------------------------------------+
3、替换字符串的函数
- INSERT(sl,x,len,s2)返回字符串s1的子串(从s1的x位置开始,长len的子串)被s2替换后的字符串。
- 如果x超过字符串长度,则返回值为原始字符串。
- 如果len大于s1的长度,则从位置x开始替换。
- 若任何一个参数为NULL,则返回值为NULL,
- REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串sl。
示例1:
mysql> select INSERT('Quest',2,4,'What'),INSERT('Quest',2,1,'What'),INSERT('Quest',2,100,'What'),INSERT('Quest',6,4,'What'),INSERT('Quest',-2,4,'What'); +----------------------------+----------------------------+------------------------------+----------------------------+-----------------------------+ | INSERT('Quest',2,4,'What') | INSERT('Quest',2,1,'What') | INSERT('Quest',2,100,'What') | INSERT('Quest',6,4,'What') | INSERT('Quest',-2,4,'What') | +----------------------------+----------------------------+------------------------------+----------------------------+-----------------------------+ | QWhat | QWhatest | QWhat | Quest | Quest | +----------------------------+----------------------------+------------------------------+----------------------------+-----------------------------+
示例2:
mysql> select REPLACE('ahhhahhha','a','c'),REPLACE('ahhhahhha','ha','c'); +------------------------------+-------------------------------+ | REPLACE('ahhhahhha','a','c') | REPLACE('ahhhahhha','ha','c') | +------------------------------+-------------------------------+ | chhhchhhc | ahhchhc | +------------------------------+-------------------------------+
4、字母大小写转换函数
- LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母。
- UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母。
示例:
mysql> select LOWER('Holle World!'),LCASE('Holle World!'),UPPER('Holle World!'),UCASE('Holle World!'); +-----------------------+-----------------------+-----------------------+-----------------------+ | LOWER('Holle World!') | LCASE('Holle World!') | UPPER('Holle World!') | UCASE('Holle World!') | +-----------------------+-----------------------+-----------------------+-----------------------+ | holle world! | holle world! | HOLLE WORLD! | HOLLE WORLD! | +-----------------------+-----------------------+-----------------------+-----------------------+
5、获取左右两端的子串的函数
- LEFT(s,n)返回字符串s最左边n个字符。
- RIGHT(s,n)返回字符串s最右边n个字符。
示例:
mysql> select LEFT('Holle World!',5),RIGHT('Holle World!',6); +------------------------+-------------------------+ | LEFT('Holle World!',5) | RIGHT('Holle World!',6) | +------------------------+-------------------------+ | Holle | World! | +------------------------+-------------------------+
6、获取子串的函数
- SUBSTRING(s,n,len)返回从字符串s的n位置开始的,长len个字符的子串。如果n是负值,则子字符串的位置起始于字符串结尾的n字符,即倒数第n个字符。
- SUBSTRING(s,n)返回从字符串s的n位置开始到结尾的子串。如果n是负值,则子字符串的位置起始于字符串结尾的n字符,即倒数第n个字符。
- MID(s,n,len)与SUBSTRING(s,n,len)的作用相同
- 如果对len使用的是一个小于1的值,则结果始终为空字符串。
示例:
mysql> select SUBSTRING('Holle World!',1,5),SUBSTRING('Holle World!',-6,6),SUBSTRING('Holle World!',1),SUBSTRING('Holle World!',-6),MID('Holle World!',1,5),MID('Holle World!',-6,6); +-------------------------------+--------------------------------+-----------------------------+------------------------------+-------------------------+--------------------------+ | SUBSTRING('Holle World!',1,5) | SUBSTRING('Holle World!',-6,6) | SUBSTRING('Holle World!',1) | SUBSTRING('Holle World!',-6) | MID('Holle World!',1,5) | MID('Holle World!',-6,6) | +-------------------------------+--------------------------------+-----------------------------+------------------------------+-------------------------+--------------------------+ | Holle | World! | Holle World! | World! | Holle | World! | +-------------------------------+--------------------------------+-----------------------------+------------------------------+-------------------------+--------------------------+
7、填充字符串的函数
- LPAD(s1,len,s2)返回字符串s1左边由字符串s2填补到len字符长度。假如s1的长度大于len,则返回值被缩短至len字符。
- RPAD(s1,len,s2)返回字符串s1右边被字符串s2填补到len字符长度。假如s1的长度大于len,则返回值被缩短至len字符。
示例:
mysql> select LPAD('Holle World!',15,'#'),LPAD('Holle World!',10,'#'),RPAD('Holle World!',15,'#'),RPAD('Holle World!',10,'#'); +-----------------------------+-----------------------------+-----------------------------+-----------------------------+ | LPAD('Holle World!',15,'#') | LPAD('Holle World!',10,'#') | RPAD('Holle World!',15,'#') | RPAD('Holle World!',10,'#') | +-----------------------------+-----------------------------+-----------------------------+-----------------------------+ | ###Holle World! | Holle Worl | Holle World!### | Holle Worl | +-----------------------------+-----------------------------+-----------------------------+-----------------------------+
8、删除空格的函数
- LTRIM(s):删除字符串s左侧的空格。
- RTRIM(s):删除字符串s右侧的空格。
- TRIM(s):删除字符串s两侧的空格。
示例:
mysql> select concat('(',LTRIM(' Holle World! '),')'),concat('(',RTRIM(' Holle World! '),')'),concat('(',TRIM(' Holle World! '),')'); +---------------------------------------------+---------------------------------------------+--------------------------------------------+ | concat('(',LTRIM(' Holle World! '),')') | concat('(',RTRIM(' Holle World! '),')') | concat('(',TRIM(' Holle World! '),')') | +---------------------------------------------+---------------------------------------------+--------------------------------------------+ | (Holle World! ) | ( Holle World!) | (Holle World!) | +---------------------------------------------+---------------------------------------------+--------------------------------------------+
9、删除指定字符串的函数
- TRIM(s1 FROM s)删除字符串s中两端所有的子串sl。s1为可选项,在未指定情况下,删除空格。
示例:
mysql> select concat('(',TRIM('ab' FROM 'ababcabc abcabab'),')'),concat('(',TRIM(' abcabc abcab '),')'); +-----------------------------------------------------+-------------------------------------------+ | concat('(',TRIM('ab' FROM 'ababcabc abcabab'),')') | concat('(',TRIM(' abcabc abcab '),')') | +-----------------------------------------------------+-------------------------------------------+ | (cabc abc) | (abcabc abcab) | +-----------------------------------------------------+-------------------------------------------+
10、生成重复字符串的函数
- REPEAT(s,n):返回一个由字符串s重复n次的字符串。
- 若n<=0则返回一个空字符串。
- 若s或n为NULL,则返回NULL。
示例:
mysql> select REPEAT('MySQL',3),REPEAT('MySQL',0),REPEAT('MySQL',null); +-------------------+-------------------+----------------------+ | REPEAT('MySQL',3) | REPEAT('MySQL',0) | REPEAT('MySQL',null) | +-------------------+-------------------+----------------------+ | MySQLMySQLMySQL | | NULL | +-------------------+-------------------+----------------------+
11、空格函数
- SPACE(n)返回一个由n个空格组成的字符串。
示例:
mysql> select concat('(',SPACE(5),')'); +--------------------------+ | concat('(',SPACE(5),')') | +--------------------------+ | ( ) | +--------------------------+
12、比较字符串大小的函数
- STRCMP(s1,s2)若所有的字符串均相同,则返回0;若根据当前分类次序,第一个参数小于第二个,则返回-1,其他情况返回1。
示例:
mysql> select STRCMP('test','test'),STRCMP('test1','test'),STRCMP('test','test1'); +-----------------------+------------------------+------------------------+ | STRCMP('test','test') | STRCMP('test1','test') | STRCMP('test','test1') | +-----------------------+------------------------+------------------------+ | 0 | 1 | -1 | +-----------------------+------------------------+------------------------+
13、匹配子串开始位置的函数
- LOCATE(str1,str),POSITION(strl IN str)和INSTR(str, strl)3个函数作用相同,返回子字符串strl在字符串str中的开始位置。
示例:
mysql> select LOCATE('lle','Holle, World!'),POSITION('lle' IN 'Holle, World!'),INSTR('Holle, World!', 'lle'); +-------------------------------+------------------------------------+-------------------------------+ | LOCATE('lle','Holle, World!') | POSITION('lle' IN 'Holle, World!') | INSTR('Holle, World!', 'lle') | +-------------------------------+------------------------------------+-------------------------------+ | 3 | 3 | 3 | +-------------------------------+------------------------------------+-------------------------------+
14、字符串逆序的函数
- REVERSE(s)将字符串s反转,返回的字符串的顺序和s字符串顺序相反。
示例:
mysql> select REVERSE('Holle World!'); +-------------------------+ | REVERSE('Holle World!') | +-------------------------+ | !dlroW elloH | +-------------------------+
15、返回指定位置的字符串的函数
- ELT(N,字符串1,字符串2,...,字符串N):若N=1,则返回值为字符串1;若N=2,则返回值为字符串2,以此类推。若N小于1或大于参数的数目,则返回值为NULL。
示例:
mysql> SELECT ELT(3,'1st','2nd','3rd'), ELT (3,'net','os'); +--------------------------+--------------------+ | ELT(3,'1st','2nd','3rd') | ELT (3,'net','os') | +--------------------------+--------------------+ | 3rd | NULL | +--------------------------+--------------------+
16、返回指定字符串位置的函数
- FIELD(s,s1,s2,...)返回字符串s在列表[sl,s2,...]中第一次出现的位置。
- 在找不到s的情况下,返回值为0。
- 如果s为NULL,则返回值为0,原因是NULL不能同任何值进行同等比较。
示例:
mysql> SELECT FIELD('Hi','thihi','Hey','Hi','bas') as col1,FIELD('Hi','Hey','Lo','Hilo' 'foo') as col2; +------+------+ | col1 | col2 | +------+------+ | 3 | 0 | +------+------+
17、返回子串位置的函数
- FIND_IN_SET(sl,s2)返回字符串s1在字符串列表s2中出现的位置。
- 字符串列表是一个由多个逗号","分开的字符串组成的列表。(注意,字符串列表中的空格是属于字符串的一部分)
- 如果s1不在s2,或s2为空字符串,则返回值为0。
- 如果任意一个参数为NULL,则返回值为NULL。
- 如果第一个参数中有逗号时,将无法正常运行。
示例:
mysql> SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas'); +-------------------------------------+ | FIND_IN_SET('Hi','hihi,Hey,Hi,bas') | +-------------------------------------+ | 3 | +-------------------------------------+
3、日期和时间函数
1、获取当前日期或时间的函数
- CURDATE()和CURRENT_DATE()函教作用相同,将当前日期按照'YYYY-MM-DD'或YYYYMMDD格式的值返回,具体格式根据函数在字符串或是数字语境中而定。
- CURTIME()和CURRENT_TIME()函数作用相,将当前时间以'HH:MM:SS'或HHMMSS的格式返回,具体格式根据函数在字符串或是数字语境中而定。
示例:
mysql> select CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(); +------------+----------------+-----------+----------------+ | CURDATE() | CURRENT_DATE() | CURTIME() | CURRENT_TIME() | +------------+----------------+-----------+----------------+ | 2021-08-17 | 2021-08-17 | 23:17:34 | 23:17:34 | +------------+----------------+-----------+----------------+ mysql> select CURDATE()+0,CURRENT_DATE()+0,CURTIME()+0,CURRENT_TIME()+0; +-------------+------------------+-------------+------------------+ | CURDATE()+0 | CURRENT_DATE()+0 | CURTIME()+0 | CURRENT_TIME()+0 | +-------------+------------------+-------------+------------------+ | 20210817 | 20210817 | 231907 | 231907 | +-------------+------------------+-------------+------------------+
2、获取当前日期和时间的函数
- CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()4个函数的作用相同,均返回当前日期和时间值。
- 格式为'YYYY-MM-DD HH:MM:SS',或YYYYMMDDHHMMSS。
- 具体格式根据函数在字符串或数字语境中而定。
示例:
mysql> select CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE(); +---------------------+---------------------+---------------------+---------------------+ | CURRENT_TIMESTAMP() | LOCALTIME() | NOW() | SYSDATE() | +---------------------+---------------------+---------------------+---------------------+ | 2021-08-17 23:24:22 | 2021-08-17 23:24:22 | 2021-08-17 23:24:22 | 2021-08-17 23:24:22 | +---------------------+---------------------+---------------------+---------------------+ mysql> select CURRENT_TIMESTAMP()+0,LOCALTIME()+0,NOW()+0,SYSDATE()+0; +-----------------------+----------------+----------------+----------------+ | CURRENT_TIMESTAMP()+0 | LOCALTIME()+0 | NOW()+0 | SYSDATE()+0 | +-----------------------+----------------+----------------+----------------+ | 20210817232445 | 20210817232445 | 20210817232445 | 20210817232445 | +-----------------------+----------------+----------------+----------------+
3、UNIX时间戳函数
- UNIX_TIMESTAMP()返回一个Unix时间戳('1970-01-01 00:00:00'GMT之后的秒数),GMT(Greenwich mean time),为格林尼治标准时间)。
- UNIX_TIMESTAMP(date):会将普通格式的时间(例如:1970-01-01 00:00:00)转换为UNIX时间戳(秒数)。
- date可以是:DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字。
- FROM_UNIXTIME(date):把UNIX时间戳转换为普通格式的时间,与UNIX_TIMESTAMP(date)函数互为反函数
示例:
mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(now()); +------------------+-----------------------+ | UNIX_TIMESTAMP() | UNIX_TIMESTAMP(now()) | +------------------+-----------------------+ | 1629214782 | 1629214782 | +------------------+-----------------------+ mysql> select FROM_UNIXTIME(1629214782); +---------------------------+ | FROM_UNIXTIME(1629214782) | +---------------------------+ | 2021-08-17 23:39:42 | +---------------------------+
4、返回UTC日期或时间的函数
- UTC_DATE()函数返回值为当前时区(UTC世界标准时间)的日期值,格式为'YYYY-MM-DD',或YYYYMMDD,具体格式取决于函数是否用在字符串或数字语境中。
- UTC_TIME()返回当前UTC时间值,其格式为'HH:MM:SS'或HHMMSS,具体格式取决于函数是否用在字符串或数字语境中。
示例:
mysql> select UTC_DATE(),UTC_TIME(),UTC_DATE()+0,UTC_TIME()+0; +------------+------------+--------------+--------------+ | UTC_DATE() | UTC_TIME() | UTC_DATE()+0 | UTC_TIME()+0 | +------------+------------+--------------+--------------+ | 2021-08-17 | 15:45:44 | 20210817 | 154544 | +------------+------------+--------------+--------------+
5、获取月份的函数
- MONTH(date)函数返回date对应的月份,范围值从1-12。
- MONTHNAME(date)函数返回日期date对应月份的英文全名。
示例:
mysql> select MONTH('2021-08-17'),MONTH(20210817),MONTH('2021-08-17 23:39:42'),MONTH(20210817233942); +---------------------+-----------------+------------------------------+-----------------------+ | MONTH('2021-08-17') | MONTH(20210817) | MONTH('2021-08-17 23:39:42') | MONTH(20210817233942) | +---------------------+-----------------+------------------------------+-----------------------+ | 8 | 8 | 8 | 8 | +---------------------+-----------------+------------------------------+-----------------------+ mysql> select MONTHNAME('2021-08-17'),MONTHNAME(20210817),MONTHNAME('2021-08-17 23:39:42'),MONTHNAME(20210817233942); +-------------------------+---------------------+----------------------------------+---------------------------+ | MONTHNAME('2021-08-17') | MONTHNAME(20210817) | MONTHNAME('2021-08-17 23:39:42') | MONTHNAME(20210817233942) | +-------------------------+---------------------+----------------------------------+---------------------------+ | August | August | August | August | +-------------------------+---------------------+----------------------------------+---------------------------+
6、获取星期的函数
- DAYNAME(date)函数返回date对应的工作日的英文名称,例如Sunday、Monday等
- DAYOFWEEK(date)函数返回date对应的一周中的索引(位置)。1表示周日、2表示周一...7表示周六)。
- WEEKDAY(date)返回date对应的工作日索引。0表示周一、 1表示周二、...、6表示周日。
示例:
mysql> select DAYNAME('2021-08-17'),DAYOFWEEK(20210817),WEEKDAY('2021-08-17 23:39:42'); +-----------------------+---------------------+--------------------------------+ | DAYNAME('2021-08-17') | DAYOFWEEK(20210817) | WEEKDAY('2021-08-17 23:39:42') | +-----------------------+---------------------+--------------------------------+ | Tuesday | 3 | 1 | +-----------------------+---------------------+--------------------------------+
7、获取星期数的函数
- WEEK(date)计算日期date是一年中的第几周。
- WEEK()的双参数形式允许指定该星期是否起始于周日或周一,以及返回值的范围是否为从0-53或从1-53。若Mode参数被省略,则使用default week format系统自变量的值。
- default week format的默认值,MySQL中为0,指定一周的第一天为周日。
- WEEKOFYEAR(d)计算某天位于一年中的第几周,范围是从1-53,相当于WEEK(d,3)。
示例:
mysql> select WEEK('2021-08-17',3),WEEKOFYEAR(20210817); +----------------------+----------------------+ | WEEK('2021-08-17',3) | WEEKOFYEAR(20210817) | +----------------------+----------------------+ | 33 | 33 | +----------------------+----------------------+
8、获取天数的函数
- DAYOFYEAR(date)函数返回date是一年中的第几天,范围是从1-366。
- DAYOFMONTH(date)函数返回date是一个月中的第几天,范围是从1-31。
示例:
mysql> select DAYOFYEAR('2021-08-17'),DAYOFMONTH(20210817); +-------------------------+----------------------+ | DAYOFYEAR('2021-08-17') | DAYOFMONTH(20210817) | +-------------------------+----------------------+ | 229 | 17 | +-------------------------+----------------------+
9、获取时间的函数
- YEAR(date)返回date对应的年份,范围是1970-2069。
- ‘00-69’转换为‘2000-2069’,‘70-99’转换为‘1970-1999’。
- Month(date)返回date对应的月
- Date(date)返回date对应的日
- Hour(date)返回date对应的小时
- Minute(date)返回date对应的分钟数,范围是从0-59。
- Second(date)返回date对应的秒数,范围是从0-59。
- Quarter(date)返回date对应的一年中的季度值,范围是从1-4。
示例:
mysql> select YEAR(now()),QUARTER(now()),Month(now()),Date(now()),Hour(now()),MINUTE(now()),SECOND(now()),now(); +-------------+----------------+--------------+-------------+-------------+---------------+---------------+---------------------+ | YEAR(now()) | QUARTER(now()) | Month(now()) | Date(now()) | Hour(now()) | MINUTE(now()) | SECOND(now()) | now() | +-------------+----------------+--------------+-------------+-------------+---------------+---------------+---------------------+ | 2021 | 3 | 8 | 2021-08-18 | 0 | 33 | 20 | 2021-08-18 00:33:20 | +-------------+----------------+--------------+-------------+-------------+---------------+---------------+---------------------+
10、获取日期的指定值的函数
- EXTRACT(type FROM date)函数所使用的时间间隔类型说明符同DATEADDO或DATE SUBO)的相同,但它从日期中提取一部分,而不是执行日期运算。
示例:
mysql> select EXTRACT(year_month FROM '2021-08-18 00:33:20'); +------------------------------------------------+ | EXTRACT(year_month FROM '2021-08-18 00:33:20') | +------------------------------------------------+ | 202108 | +------------------------------------------------+ mysql> select EXTRACT(day_minute FROM '2021-08-18 00:33:20'); +------------------------------------------------+ | EXTRACT(day_minute FROM '2021-08-18 00:33:20') | +------------------------------------------------+ | 180033 | +------------------------------------------------+
11、时间和秒钟转换的函数
- TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为;小时*3600+分钟*60+秒。
- SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的seconds参数值,其格式为"HH:MM:SS'或HHMMSS,具体格式根据该函数是否用在字符串或数字语境中而定。
示例:
mysql> SELECT TIME_TO_SEC('23:23:00'),SEC_TO_TIME (84180); +-------------------------+---------------------+ | TIME_TO_SEC('23:23:00') | SEC_TO_TIME (84180) | +-------------------------+---------------------+ | 84180 | 23:23:00 | +-------------------------+---------------------+
12、计算日期和时间的函数
- DATE_ADD(date,NTERVAL expr type)和DATE_SUB(date,INTERVAL expr type)
- date是一个DATETIME或DATE值,用来指定起始时间。
- expr是一个表达式,用来指定从起始日期添加或减去的时间间隔值。
- expr是一个字符串。对于负值的时间间隔,它可以以一个负号'-'开头。
- type为关键词,它指示了表达式被解释的方式。
- DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)两个函数作用相同,执行日期的加运算。
- DATE_SUB(date,INTERVAL expr type)或者SUBDATE(date,INTERVAL expr type)两个函数作用相同,执行日期的减运算。
示例1:
mysql> SELECT DATE_ADD('2010-12-31 23:59:59',INTERVAL 1 SECOND) AS col1,ADDDATE('2010-12-31 23:59:59',INTERVAL -1 SECOND) As col2,DATE_ADD('2010-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS co13,DATE_ADD('2010-12-31 23:59:59',INTERVAL '-1:1' MINUTE_SECOND) AS co14; +---------------------+---------------------+---------------------+---------------------+ | col1 | col2 | co13 | co14 | +---------------------+---------------------+---------------------+---------------------+ | 2011-01-01 00:00:00 | 2010-12-31 23:59:58 | 2011-01-01 00:01:00 | 2010-12-31 23:58:58 | +---------------------+---------------------+---------------------+---------------------+
- ADDTIME(date,expr)函数:将expr加到date上,并返回修改后的值。date是一个日期或者日期时间表达式,而expr是一个时间表达式。
- SUBTIME(date,expr)函数:将date减去expr值,并返回修改后的值。date是一个日期或者日期时间表达式,而expr是一个时间表达式。
示例2:
mysql> SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'), ADDTIME('02:02:02','02:00:00'); +----------------------------------------+--------------------------------+ | ADDTIME('2000-12-31 23:59:59','1:1:1') | ADDTIME('02:02:02','02:00:00') | +----------------------------------------+--------------------------------+ | 2001-01-01 01:01:00 | 04:02:02 | +----------------------------------------+--------------------------------+ mysql> SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'), SUBTIME('02:02:02','02:00:00'); +----------------------------------------+--------------------------------+ | SUBTIME('2000-12-31 23:59:59','1:1:1') | SUBTIME('02:02:02','02:00:00') | +----------------------------------------+--------------------------------+ | 2000-12-31 22:58:58 | 00:02:02 | +----------------------------------------+--------------------------------+
13、将日期和时间格式化的函数
- DATE_FORMAT(date,format)根据format指定的格式显示date值。
示例:
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2; +-----------------------+--------------------------+ | col1 | col2 | +-----------------------+--------------------------+ | Saturday October 1997 | 4th 97 Sat 04 10 Oct 277 | +-----------------------+--------------------------+
4、条件判断函数
1、IF(expr,v1,v2)函数
- IF(expr, v1, v2),如果表达式expr是TRUE(expr <> 0 and expr <> NULL),则IF()的返回值为vl;否则返回值为v2。IF()的返回值为数字值或字符串值,具体情况视其所在语境而定。
示例:
mysql> SELECT IF(1>2, 2, 3), IF(1<2, 'yes', 'no'), IF(STRCMP('test', 'testl'), 'no', 'yes'); +---------------+----------------------+------------------------------------------+ | IF(1>2, 2, 3) | IF(1<2, 'yes', 'no') | IF(STRCMP('test', 'testl'), 'no', 'yes') | +---------------+----------------------+------------------------------------------+ | 3 | yes | no | +---------------+----------------------+------------------------------------------+
2、IFNULL(v1,v2)函数
- IFNULL(v1,v2)假如v1不为NULL,则IFNULL()的返回值为v1;否则其返回值为v2
示例:
mysql> SELECT IFNULL(1, 2), IFNULL(NULL, 10), IFNULL(1/0, 'wrong'); +--------------+------------------+----------------------+ | IFNULL(1, 2) | IFNULL(NULL, 10) | IFNULL(1/0, 'wrong') | +--------------+------------------+----------------------+ | 1 | 10 | wrong | +--------------+------------------+----------------------+
3、CASE函数
- CASE expr WHEN v1 THEN rl [WHEN v2 THEN r2] (ELSE rn] END
- 如果expr值等于某个vn,则返回对应位置THEN后面的结果。
- 如果与所有值都不相等,则返回ELSE后面的rn。
- CASE WHEN v1 THEN rl [WHEN v2 THEN r21 ELSE rn END
- 某个vn值为TRUE时,返回对应位置THEN后面的结果。
- 如果所有值都不为TRUE,则返回ELSE后的m。
示例1:
mysql> SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; +------------------------------------------------------------+ | CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END | +------------------------------------------------------------+ | two | +------------------------------------------------------------+
示例2:
mysql> SELECT CASE WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; +----------------------------------------------------------+ | CASE WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END | +----------------------------------------------------------+ | one | +----------------------------------------------------------+