Mysql高手系列 – 第10篇：常用的幾十個函數詳解，收藏慢慢看

• 2019 年 10 月 3 日
• 筆記

這是Mysql系列第10篇。

環境：mysql5.7.25，cmd命令中進行演示。

MySQL 數值型函數

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:返回字元串直接長度

返回值為字元串的位元組長度，使用 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:兩個函數作用相同，返回當前系統的日期值

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

可以有的形式：

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函數：

上表中「今年有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：參數可以是下列值

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 聚合函數

MySQL 流程式控制制函數

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)

其他函數

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系列目錄

1. 第1篇：mysql基礎知識
2. 第2篇：詳解mysql數據類型（重點）
3. 第3篇：管理員必備技能(必須掌握)
4. 第4篇：DDL常見操作
5. 第5篇：DML操作匯總（insert,update,delete）
6. 第6篇：select查詢基礎篇
7. 第7篇：玩轉select條件查詢，避免采坑
8. 第8篇：詳解排序和分頁(order by & limit)
9. 第9篇：分組查詢詳解（group by & having）

mysql系列大概有20多篇，喜歡的請關注一下，歡迎大家加我微信itsoku或者留言交流mysql相關技術!