《MySQL 基礎篇》五：函式

ACatSmiling發表於2024-09-22
MySql函式

Author: ACatSmiling

Since: 2024-09-20

單行函式

函式概述

函式在計算機語言的使用中貫穿始終，它可以把我們經常使用的程式碼封裝起來，需要的時候直接呼叫即可。這樣既提高了程式碼效率，又提高了可維護性。在 SQL 中我們也可以使用函式對檢索出來的資料進行函式操作。使用這些函式，可以極大地提高使用者對資料庫的管理效率。

image-20230412151652405

從函式定義的角度出發，可以將函式分成內建函式和自定義函式。在 SQL 語言中，同樣也包括了內建函式和自定義函式。內建函式是系統內建的通用函式，而自定義函式是根據自己的需要編寫的。

在使用 SQL 語言的時候，不是直接和這門語言打交道，而是透過它使用不同的資料庫軟體，即 DBMS。DBMS 之間的差異性很大，遠大於同一個語言不同版本之間的差異。實際上，只有很少的函式是被 DBMS 同時支援的。比如，大多數 DBMS 使用（||）或者（+）來做拼接符，而在 MySQL 中的字串拼接函式為 CONCAT()。大部分 DBMS 會有自己特定的函式，這就意味著採用 SQL 函式的程式碼可移植性是很差的，因此在使用函式的時候需要特別注意。

MySQL 的內建函式及分類

MySQL 提供了豐富的內建函式，這些函式使得資料的維護與管理更加方便，能夠更好地提供資料的分析與統計功能，在一定程度上提高了開發人員進行資料分析與統計的效率。

MySQL 提供的內建函式從實現的功能角度可以分為數值函式、字串函式、日期和時間函式、流程控制函式、加密與解密函式、獲取 MySQL 資訊函式、聚合函式等。進一步，可以將這些豐富的內建函式再分為兩類：單行函式、聚合函式（或分組函式）。

image-20230412161007252

  • 單行函式：
    • 運算元據物件；
    • 接受引數返回一個結果；
    • 只對一行進行變換；
    • 每行返回一個結果；
    • 可以巢狀；
    • 引數可以是一列或一個值。

數值函式

基本函式

函式 用法
ABS(x) 返回 x 的絕對值
SIGN(x) 返回 x 的符號，正數返回 1，負數返回 -1，0 返回 0。
PI() 返回圓周率的值
CEIL(x)，CEILING(x) 返回大於或等於某個值的最小整數
FLOOR(x) 返回小於或等於某個值的最大整數
LEAST(e1, e2, e3…) 返回列表中的最小值
GREATEST(e1, e2, e3…) 返回列表中的最大值
MOD(x, y) 返回 x 除以 y 後的餘數
RAND() 返回 0 ~ 1 的隨機值
RAND(x) 返回 0 ~ 1 的隨機值，其中 x 的值用作種子值，相同的 x 值會產生相同的隨機數
ROUND(x) 返回一個對 x 的值進行四捨五入後，最接近於 x 的整數
ROUND(x, y) 返回一個對 x 的值進行四捨五入後最接近 x 的值，並保留到小數點後面 y 位
TRUNCATE(x, y) 返回數字 x 截斷為 y 位小數的結果
SQRT(x) 返回 x 的平方根，當 x 的值為負數時，返回 NULL

示例：

mysql> SELECT ABS(-123), ABS(32), SIGN(-23), SIGN(43), PI(), CEIL(32.32), CEILING(-43.23), FLOOR(32.32), FLOOR(-43.23), MOD(12,5) FROM DUAL;
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
| ABS(-123) | ABS(32) | SIGN(-23) | SIGN(43) | PI()     | CEIL(32.32) | CEILING(-43.23) | FLOOR(32.32) | FLOOR(-43.23) | MOD(12,5) |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
|       123 |      32 |        -1 |        1 | 3.141593 |          33 |             -43 |           32 |           -44 |         2 |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT RAND(), RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1) FROM DUAL;
+-------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| RAND()            | RAND()             | RAND(10)           | RAND(10)           | RAND(-1)           | RAND(-1)           |
+-------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| 0.759850322976569 | 0.3624563518561948 | 0.6570515219653505 | 0.6570515219653505 | 0.9050373219931845 | 0.9050373219931845 |
+-------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(12.33), ROUND(12.343,2), ROUND(12.324,-1), TRUNCATE(12.66,1), TRUNCATE(12.66,-1) FROM DUAL;
+--------------+-----------------+------------------+-------------------+--------------------+
| ROUND(12.33) | ROUND(12.343,2) | ROUND(12.324,-1) | TRUNCATE(12.66,1) | TRUNCATE(12.66,-1) |
+--------------+-----------------+------------------+-------------------+--------------------+
|           12 |           12.34 |               10 |              12.6 |                 10 |
+--------------+-----------------+------------------+-------------------+--------------------+
1 row in set (0.00 sec)

角度與弧度互換函式

函式 用法
RADIANS(x) 將角度轉化為弧度，其中，引數 x 為角度值
DEGREES(x) 將弧度轉化為角度，其中，引數 x 為弧度值

示例：

mysql> SELECT RADIANS(30), RADIANS(60), RADIANS(90), DEGREES(2 * PI()), DEGREES(RADIANS(90)) FROM DUAL;
+--------------------+--------------------+--------------------+-----------------+----------------------+
| RADIANS(30)        | RADIANS(60)        | RADIANS(90)        | DEGREES(2*PI()) | DEGREES(RADIANS(90)) |
+--------------------+--------------------+--------------------+-----------------+----------------------+
| 0.5235987755982988 | 1.0471975511965976 | 1.5707963267948966 |             360 |                   90 |
+--------------------+--------------------+--------------------+-----------------+----------------------+
1 row in set (0.00 sec)

三角函式

函式 用法
SIN(x) 返回 x 的正弦值，其中，引數 x 為弧度值
ASIN(x) 返回 x 的反正弦值，即獲取正弦為 x 的值。如果 x 的值不在 -1 到 1 之間，則返回 NULL
COS(x) 返回 x 的餘弦值，其中，引數 x 為弧度值
ACOS(x) 返回 x 的反餘弦值，即獲取餘弦為 x 的值。如果 x 的值不在 -1 到 1 之間，則返回 NULL
TAN(x) 返回 x 的正切值，其中，引數 x 為弧度值
ATAN(x) 返回 x 的反正切值，即返回正切值為 x 的值
ATAN2(m, n) 返回兩個引數的反正切值
COT(x) 返回 x 的餘切值，其中，x 為弧度值

ATAN2(m, n) 函式返回兩個引數的反正切值。與 ATAN(x) 函式相比，ATAN2(m, n) 需要兩個引數，例如有兩個點 point(x1, y1) 和 point(x2, y2)，使用 ATAN(x) 函式計算反正切值為 ATAN((y2 - y1) / (x2 - x1))，使用 ATAN2(m, n) 計算反正切值則為 ATAN2(y2 - y1, x2 - x1)。由使用方式可以看出，當 x2 - x1 等於0時，ATAN(x) 函式會報錯，而 ATAN2(m, n) 函式則仍然可以計算。

示例：

mysql> SELECT SIN(RADIANS(30)), DEGREES(ASIN(1)), TAN(RADIANS(45)), DEGREES(ATAN(1)), DEGREES(ATAN2(1,1) ) FROM DUAL;
+---------------------+------------------+--------------------+------------------+----------------------+
| SIN(RADIANS(30))    | DEGREES(ASIN(1)) | TAN(RADIANS(45))   | DEGREES(ATAN(1)) | DEGREES(ATAN2(1,1) ) |
+---------------------+------------------+--------------------+------------------+----------------------+
| 0.49999999999999994 |               90 | 0.9999999999999999 |               45 |                   45 |
+---------------------+------------------+--------------------+------------------+----------------------+
1 row in set (0.00 sec)

指數與對數

函式 用法
POW(x, y)，POWER(x, y) 返回 x 的 y 次方
EXP(x) 返回 e 的 x 次方，其中 e 是一個常數，2.718281828459045
LN(x)，LOG(x) 返回以 e 為底的 x 的對數，當 x <= 0 時，返回的結果為 NULL
LOG10(x) 返回以 10 為底的 x 的對數，當 x <= 0 時，返回的結果為 NULL
LOG2(x) 返回以 2 為底的 x 的對數，當 x <= 0 時，返回 NULL

示例：

mysql> SELECT POW(2, 5), POWER(2, 4), EXP(2), LN(10), LOG10(10), LOG2(4) FROM DUAL;
+-----------+-------------+------------------+-------------------+-----------+---------+
| POW(2, 5) | POWER(2, 4) | EXP(2)           | LN(10)            | LOG10(10) | LOG2(4) |
+-----------+-------------+------------------+-------------------+-----------+---------+
|        32 |          16 | 7.38905609893065 | 2.302585092994046 |         1 |       2 |
+-----------+-------------+------------------+-------------------+-----------+---------+
1 row in set (0.00 sec)

進位制間的轉換

函式 用法
BIN(x) 返回 x 的二進位制編碼
HEX(x) 返回 x 的十六進位制編碼
OCT(x) 返回 x 的八進位制編碼
CONV(x, f1, f2) 返回 f1 進位制數變成 f2 進位制數

示例：

mysql> SELECT BIN(10), HEX(10), OCT(10), CONV(10, 2, 8) FROM DUAL;
+---------+---------+---------+----------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10, 2, 8) |
+---------+---------+---------+----------------+
| 1010    | A       | 12      | 2              |
+---------+---------+---------+----------------+
1 row in set (0.00 sec)

字串函式

函式 用法
ASCII(s) 返回字串 s 中的第一個字元的 ASCII 碼值
CHAR_LENGTH(s) 返回字串 s 的字元數，作用與 CHARACTER_LENGTH(s) 相同
LENGTH(s) 返回字串 s 的位元組數，和字符集有關
CONCAT(s1, s2, ......, sn) 連線 s1, s2, ......, sn 為一個字串
CONCAT_WS(x, s1, s2, ......, sn) 同 CONCAT(s1, s2, ......, sn) 函式，但是每個字串之間要加上 x
INSERT(str, idx, len, replacestr) 將字串 str 從第 idx 位置開始，len 個字元長的子串替換為字串 replacestr
REPLACE(str, a, b) 用字串 b 替換字串 str 中所有出現的字串 a
UPPER(s) 或 UCASE(s) 將字串 s 的所有字母轉成大寫字母
LOWER(s) 或 LCASE(s) 將字串 s的所有字母轉成小寫字母
LEFT(str, n) 返回字串 str 最左邊的 n 個字元
RIGHT(str, n) 返回字串 str 最右邊的 n 個字元
LPAD(str, len, pad) 用字串 pad 對 str 最左邊進行填充，直到 str 的長度為 len 個字元
RPAD(str, len, pad) 用字串 pad 對 str 最右邊進行填充，直到 str 的長度為 len 個字元
LTRIM(s) 去掉字串 s 左側的空格
RTRIM(s) 去掉字串 s 右側的空格
TRIM(s) 去掉字串 s 開始與結尾的空格
TRIM(s1 FROM s) 去掉字串 s 開始與結尾的 s1
TRIM(LEADING s1 FROM s) 去掉字串 s 開始處的 s1
TRIM(TRAILING s1 FROM s) 去掉字串 s 結尾處的 s1
REPEAT(str, n) 返回 str 重複 n 次的結果
SPACE(n) 返回 n 個空格
STRCMP(s1, s2) 比較字串 s1 和 s2 的 ASCII 碼值的大小
SUBSTR(s, index, len) 返回從字串 s 的 index 位置起 len 個字元，作用與 SUBSTRING(s, n, len)、MID(s, n, len) 相同
LOCATE(substr, str) 返回字串 substr 在字串 str 中首次出現的位置，作用與 POSITION(substr IN str)、INSTR(str, substr) 相同，如果未找到，返回 0
ELT(m, s1, s2, ......, sn) 返回指定位置的字串，如果 m = 1，則返回 s1，如果 m = 2，則返回 s2，如果 m = n，則返回 sn
FIELD(s, s1, s2, ......, sn) 返回字串 s 在字串列表中第一次出現的位置
FIND_IN_SET(s1, s2) 返回字串 s1 在字串 s2 中出現的位置。其中，字串 s2 是一個以逗號分隔的字串
REVERSE(s) 返回 s 反轉後的字串
NULLIF(value1, value2) 比較兩個字串，如果 value1 與 value2 相等，則返回 NULL，否則返回 value1

注意：MySQL 中，字串的位置是從 1 開始的。

示例：

mysql> SELECT FIELD('mm', 'hello', 'msm', 'amma'), FIND_IN_SET('mm', 'hello,mm,amma') FROM DUAL;
+-------------------------------------+------------------------------------+
| FIELD('mm', 'hello', 'msm', 'amma') | FIND_IN_SET('mm', 'hello,mm,amma') |
+-------------------------------------+------------------------------------+
|                                   0 |                                  2 |
+-------------------------------------+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT NULLIF('mysql', 'mysql'), NULLIF('mysql', '') FROM DUAL;
+--------------------------+---------------------+
| NULLIF('mysql', 'mysql') | NULLIF('mysql', '') |
+--------------------------+---------------------+
| NULL                     | mysql               |
+--------------------------+---------------------+
1 row in set (0.00 sec)

日期和時間函式

獲取日期和時間

函式 用法
CURDATE() 或 CURRENT_DATE() 返回當前日期，只包含年、月、日
CURTIME() 或 CURRENT_TIME() 返回當前時間，只包含時、分、秒
NOW() 或 SYSDATE() 或 CURRENT_TIMESTAMP() 或 LOCALTIME() 或 LOCALTIMESTAMP() 返回當前系統日期和時間
UTC_DATE() 返回 UTC（世界標準時間）日期
UTC_TIME() 返回 UTC（世界標準時間）時間

示例：

mysql> SELECT CURDATE(), CURTIME(), NOW(), SYSDATE()+0, UTC_DATE(), UTC_DATE()+0, UTC_TIME(), UTC_TIME()+0 FROM DUAL;
+------------+-----------+---------------------+----------------+------------+--------------+------------+--------------+
| CURDATE()  | CURTIME() | NOW()               | SYSDATE()+0    | UTC_DATE() | UTC_DATE()+0 | UTC_TIME() | UTC_TIME()+0 |
+------------+-----------+---------------------+----------------+------------+--------------+------------+--------------+
| 2023-04-13 | 09:39:50  | 2023-04-13 09:39:50 | 20230413093950 | 2023-04-13 |     20230413 | 09:39:50   |        93950 |
+------------+-----------+---------------------+----------------+------------+--------------+------------+--------------+
1 row in set (0.00 sec)

日期與時間戳的轉換

函式 用法
UNIX_TIMESTAMP() 以 UNIX 時間戳的形式返回當前時間。SELECT UNIX_TIMESTAMP() ---> 1634348884
UNIX_TIMESTAMP(date) 將時間 date 以 UNIX 時間戳的形式返回
FROM_UNIXTIME(timestamp) 將 UNIX 時間戳的時間轉換為普通格式的時間

示例：

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
|            1681378975 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
|                1681344000 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
|                1681378991 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11');
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
|                            1321009871 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 03:35:10       |
+---------------------------+
1 row in set (0.01 sec)

獲取月份、星期、星期數、天數等函式

函式 用法
YEAR(date) / MONTH(date) / DAY(date) 返回具體的日期值
HOUR(time) / MINUTE(time) / SECOND(time) 返回具體的時間值
MONTHNAME(date) 返回月份：January，......
DAYNAME(date) 返回星期幾：MONDAY，TUESDAY，......，SUNDAY
WEEKDAY(date) 返回周幾，注意，週一是 0，週二是 1，......，週日是 6
QUARTER(date) 返回日期對應的季度，範圍為 1～4
WEEK(date) 或 WEEKOFYEAR(date) 返回一年中的第幾周
DAYOFYEAR(date) 返回日期是一年中的第幾天
DAYOFMONTH(date) 返回日期位於所在月份的第幾天
DAYOFWEEK(date) 返回周幾，注意：週日是 1，週一是 2，......，週六是 7

示例：

mysql> SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE()), HOUR(CURTIME()), MINUTE(NOW()), SECOND(SYSDATE()) FROM DUAL; 
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) | HOUR(CURTIME()) | MINUTE(NOW()) | SECOND(SYSDATE()) |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
|            2023 |                4 |             13 |               9 |            52 |                46 |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME('2023-04-13'), DAYNAME('2023-04-13'), WEEKDAY('2023-04-13'), QUARTER(CURDATE()), WEEK(CURDATE()), DAYOFYEAR(NOW()
), DAYOFMONTH(NOW()), DAYOFWEEK(NOW()) FROM DUAL;
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
| MONTHNAME('2023-04-13') | DAYNAME('2023-04-13') | WEEKDAY('2023-04-13') | QUARTER(CURDATE()) | WEEK(CURDATE()) | DAYOFYEAR(NOW()) | DAYOFMONTH(NOW()) | DAYOFWEEK(NOW()) |
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
| April                   | Thursday              |                     3 |                  2 |              15 |              103 |                13 |                5 |
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+-----------------

日期的操作函式

函式 用法
EXTRACT(type FROM date) 返回指定日期中特定的部分，type 指定返回的值

type 的取值與含義：

image-20230413184243322

示例：

mysql> SELECT EXTRACT(MINUTE FROM NOW()), EXTRACT(WEEK FROM NOW()), EXTRACT(QUARTER FROM NOW()), EXTRACT(MINUTE_SECOND FROM NOW()) FROM DUAL;
+----------------------------+--------------------------+-----------------------------+-----------------------------------+
| EXTRACT(MINUTE FROM NOW()) | EXTRACT(WEEK FROM NOW()) | EXTRACT(QUARTER FROM NOW()) | EXTRACT(MINUTE_SECOND FROM NOW()) |
+----------------------------+--------------------------+-----------------------------+-----------------------------------+
|                         43 |                       15 |                           2 |                              4324 |
+----------------------------+--------------------------+-----------------------------+-----------------------------------+
1 row in set (0.00 sec)

時間和秒鐘轉換的函式

函式 用法
TIME_TO_SEC(time) 將 time 轉化為秒並返回結果值，轉化的公式為：小時 * 3600 + 分鐘 * 60 + 秒
SEC_TO_TIME(seconds) 將 seconds 描述轉化為包含小時、分鐘和秒的時間

示例：

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
|              38801 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SEC_TO_TIME(38801);
+--------------------+
| SEC_TO_TIME(38801) |
+--------------------+
| 10:46:41           |
+--------------------+
1 row in set (0.00 sec)

計算日期和時間的函式

第 1 組：

函式 用法
DATE_ADD(date, INTERVAL expr type)或 ADDDATE(date, INTERVAL expr type) 返回與給定日期時間增加 INTERVAL 時間段的日期時間
DATE_SUB(date, INTERVAL expr type)或 SUBDATE(date, INTERVAL expr type) 返回與給定日期時間減少 INTERVAL 時間段的日期時間

type 的取值與含義：

image-20230413185003392

示例：

mysql> SELECT NOW() AS col1, DATE_SUB(NOW(), INTERVAL 1 HOUR) AS col2, DATE_ADD(NOW(), INTERVAL 1 HOUR) AS col3;
+---------------------+---------------------+---------------------+
| col1                | col2                | col3                |
+---------------------+---------------------+---------------------+
| 2023-05-12 16:32:16 | 2023-05-12 15:32:16 | 2023-05-12 17:32:16 |
+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1, DATE_ADD('2023-04-13 10:50:12', INTERVAL 1 SECOND) AS col2, ADDDATE('2023-04-13 10
:50:12', INTERVAL 1 SECOND) AS col3, DATE_ADD('2023-04-13 10:50:12', INTERVAL '1_1' MINUTE_SECOND) AS col4, DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 FROM DUAL;
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| col1                | col2                | col3                | col4                | col5                | col6                |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2023-04-14 10:52:31 | 2023-04-13 10:50:13 | 2023-04-13 10:50:13 | 2023-04-13 10:51:13 | 2022-04-13 10:52:31 | 2024-05-13 10:52:31 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2023-04-13', INTERVAL 31 DAY) AS col1, SUBDATE('2023-04-13', INTERVAL 31 DAY) AS col2, DATE_SUB('2023-04-13 02:01:01', INTERVAL '1 1' DAY_HOUR) AS col3 FROM DUAL;
+------------+------------+---------------------+
| col1       | col2       | col3                |
+------------+------------+---------------------+
| 2023-03-13 | 2023-03-13 | 2023-04-12 01:01:01 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

第 2 組：

函式 用法
ADDTIME(time1, time2) 返回 time1 加上 time2 的時間。當 time2 為一個數字時，代表的是秒，可以為負數
SUBTIME(time1, time2) 返回 time1 減去 time2 後的時間。當 time2 為一個數字時，代表的是秒，可以為負數
DATEDIFF(date1, date2) 返回 date1 - date2 的日期間隔天數
TIMEDIFF(time1, time2) 返回 time1 - time2 的時間間隔
FROM_DAYS(N) 返回從 0000 年 1 月 1 日起，N 天以後的日期
TO_DAYS(date) 返回日期 date 距離 0000 年 1 月 1 日的天數
LAST_DAY(date) 返回 date 所在月份的最後一天的日期
MAKEDATE(year, n) 針對給定年份與所在年份中的天數返回一個日期
MAKETIME(hour, minute, second) 將給定的小時、分鐘和秒組合成時間並返回
PERIOD_ADD(time, n) 返回 time 加上 n 後的時間

示例：

mysql> SELECT ADDTIME(NOW(), 20), SUBTIME(NOW(), 30), SUBTIME(NOW(), '1:1:3'), DATEDIFF(NOW(), '2031-04-13'), TIMEDIFF(NOW(), '2023-04-25
 22:10:10'), FROM_DAYS(366), TO_DAYS('0000-12-25'), LAST_DAY(NOW()), MAKEDATE(YEAR(NOW()), 12), MAKETIME(10, 21, 23), PERIOD_ADD(20200101
010101, 10) FROM DUAL;
+---------------------+---------------------+-------------------------+-------------------------------+----------------------------------------+----------------+-----------------------+-----------------+---------------------------+----------------------+--------------------------------+
| ADDTIME(NOW(), 20)  | SUBTIME(NOW(), 30)  | SUBTIME(NOW(), '1:1:3') | DATEDIFF(NOW(), '2031-04-13') | TIMEDIFF(NOW(), '2023-04-25 22:10:10') | FROM_DAYS(366) | TO_DAYS('0000-12-25') | LAST_DAY(NOW()) | MAKEDATE(YEAR(NOW()), 12) | MAKETIME(10, 21, 23) | PERIOD_ADD(20200101010101, 10) |
+---------------------+---------------------+-------------------------+-------------------------------+----------------------------------------+----------------+-----------------------+-----------------+---------------------------+----------------------+--------------------------------+
| 2023-04-14 09:51:31 | 2023-04-14 09:50:41 | 2023-04-14 08:50:08     |                         -2921 | -276:18:59                             | 0001-01-01     |                   359 | 2023-04-30      | 2023-01-12                | 10:21:23             |                 20200101010111 |
+---------------------+---------------------+-------------------------+-------------------------------+----------------------------------------+----------------+-----------------------+-----------------+---------------------------+----------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50)  |
+---------------------+
| 2023-04-14 09:52:29 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2023-04-14 10:52:52     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2023-04-14 08:50:58     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '-1:-1:-1');
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2023-04-14 09:52:08        |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01     |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020, 1);
+-------------------+
| MAKEDATE(2020, 1) |
+-------------------+
| 2020-01-01        |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020, 32);
+--------------------+
| MAKEDATE(2020, 32) |
+--------------------+
| 2020-02-01         |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKETIME(1, 1, 1);
+-------------------+
| MAKETIME(1, 1, 1) |
+-------------------+
| 01:01:01          |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(20200101010101, 1);
+-------------------------------+
| PERIOD_ADD(20200101010101, 1) |
+-------------------------------+
|                20200101010102 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
|         738989 |
+----------------+
1 row in set (0.00 sec)

# 查詢 7 天內的新增使用者數
mysql> SELECT COUNT(1) AS num FROM new_user WHERE TO_DAYS(NOW()) - TO_DAYS(regist_time) <= 7;

日期的格式化與解析

函式 用法
DATE_FORMAT(date, fmt) 按照字串 fmt 格式化日期 date 值
TIME_FORMAT(time, fmt) 按照字串 fmt 格式化時間 time 值
GET_FORMAT(date_type, format_type) 返回日期字串的顯示格式
STR_TO_DATE(str, fmt) 按照字串 fmt 對 str 進行解析，解析為一個日期

上述非 GET_FORMAT 函式中 fmt 引數常用的格式符：

格式符 說明 格式符 說明
%Y 用 4 位數字表示年份 %y 用 2 位數字表示年份
%M 用月名錶示月份，January 等 %m 用 2 位數字表示月份，01、02、03 等
%b 縮寫的月名，Jan.、Feb. 等 %c 用數字表示月份，1、2、3 等
%D 用英文字尾表示月中的天數，1st、2nd、3rd 等 %d 用兩位數字表示月中的天數，01、02 等
%e 用數字形式表示月中的天數，1、2、3、4、5 等
%H 兩位數字表示小數，24小時制，01、02 等 %h 和 %I 兩位數字表示小時，12 小時制，01、02 等
%k 數字形式表示小時，24小時制，1、2、3 等 %l 數字形式表示小時，12 小時制，1、2、3 等
%i 兩位數字表示分鐘，00、01、02 等 %S 和 %s 兩位數字表示秒，00、01、02 等
%W 一週中的星期名稱，Sunday 等 %a 一週中的星期縮寫，Sun.、Mon. 等
%w 以數字表示週中的天數，0 = Sunday、1 = Monday 等
%j 以三位數字表示年中的天數，001、002 等 %U 以數字表示年中的第幾周，1、2、3 等，其中 Sunday 為週中第一天
%u 以數字表示年中的第幾周，1、2、3 等，其中 Monday 為週中第一天
%T 24 小時制 %r 12 小時制
%p AM 或 PM %% 表示 %

上述GET_FORMAT函式中 date_type 和 format_type 引數取值如下：

image-20230421125159673

示例：

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 14:27:46                       |
+--------------------------------+
1 row in set (0.01 sec)

mysql> SELECT STR_TO_DATE('09/01/2009', '%m/%d/%Y') FROM DUAL;
+---------------------------------------+
| STR_TO_DATE('09/01/2009', '%m/%d/%Y') |
+---------------------------------------+
| 2009-09-01                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('20140422154706', '%Y%m%d%H%i%s') FROM DUAL;
+-----------------------------------------------+
| STR_TO_DATE('20140422154706', '%Y%m%d%H%i%s') |
+-----------------------------------------------+
| 2014-04-22 15:47:06                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('2014-04-22 15:47:06', '%Y-%m-%d %H:%i:%s') FROM DUAL;
+---------------------------------------------------------+
| STR_TO_DATE('2014-04-22 15:47:06', '%Y-%m-%d %H:%i:%s') |
+---------------------------------------------------------+
| 2014-04-22 15:47:06                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y                |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(), GET_FORMAT(DATE, 'USA')) FROM DUAL;
+---------------------------------------------+
| DATE_FORMAT(NOW(), GET_FORMAT(DATE, 'USA')) |
+---------------------------------------------+
| 04.23.2023                                  |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('2023-01-01 00:00:00', '%Y-%m-%d');
+------------------------------------------------+
| STR_TO_DATE('2023-01-01 00:00:00', '%Y-%m-%d') |
+------------------------------------------------+
| 2023-01-01                                     |
+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

常用的時間示例

-- 本日起止時間
mysql> SELECT NOW(), DATE_FORMAT(NOW(), '%Y-%m-%d %00:%00:%00') today_start, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') today_current, DATE_FORMAT(NOW(), '%Y-%m-%d %23:%59:%59') today_end;
+---------------------+---------------------+---------------------+---------------------+
| NOW()               | today_start         | today_current       | today_end           |
+---------------------+---------------------+---------------------+---------------------+
| 2023-05-29 16:33:00 | 2023-05-29 00:00:00 | 2023-05-29 16:33:00 | 2023-05-29 23:59:59 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

-- 本週起止時間
mysql> SELECT NOW(), DATE_FORMAT(SUBDATE(NOW(), DATE_FORMAT(NOW(), '%w') - 1), '%Y-%m-%d %00:%00:%00') week_start, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') week_current, DATE_FORMAT(SUBDATE(NOW(), DATE_FORMAT(NOW(), '%w') - 7), '%Y-%m-%d %23:%59:%59') week_end;
+---------------------+---------------------+---------------------+---------------------+
| NOW()               | week_start          | week_current        | week_end            |
+---------------------+---------------------+---------------------+---------------------+
| 2023-05-29 16:39:49 | 2023-05-29 00:00:00 | 2023-05-29 16:39:49 | 2023-06-04 23:59:59 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

-- 本月起止時間
mysql> SELECT NOW(), DATE_FORMAT(NOW(), '%Y-%m-01 %00:%00:%00') month_start, DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') month_current, DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d %23:%59:%59') month_end;
+---------------------+---------------------+---------------------+---------------------+
| NOW()               | month_start         | month_current       | month_end           |
+---------------------+---------------------+---------------------+---------------------+
| 2023-05-29 16:44:15 | 2023-05-01 00:00:00 | 2023-05-29 16:44:15 | 2023-05-31 23:59:59 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

-- 當前時間, 當前時間前推7天, 當前時間前推7天的0點, 當前時間前推7天的24點
mysql> SELECT NOW(), DATE_SUB(NOW(), INTERVAL 6 DAY) server_day_ago, DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 DAY), '%Y-%m-%d %00:%00:%00') server_day_ago_start, DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 DAY), '%Y-%m-%d %23:%59:%59') server_day_ago_end;
+---------------------+---------------------+----------------------+---------------------+
| NOW()               | server_day_ago      | server_day_ago_start | server_day_ago_end  |
+---------------------+---------------------+----------------------+---------------------+
| 2023-06-08 09:55:04 | 2023-06-02 09:55:04 | 2023-06-02 00:00:00  | 2023-06-02 23:59:59 |
+---------------------+---------------------+----------------------+---------------------+
1 row in set (0.00 sec)

-- 當前時間, 當前時間前推12個月, 當前時間前推12個月的月初的0點, 當前時間前推12個月的月末的24點
mysql> SELECT NOW(), DATE_SUB(NOW(), INTERVAL 11 MONTH) twelve_month_ago, DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 11 MONTH), '%Y-%m-01 %00:%00:%00') twelve_month_ago_start, DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 11 MONTH)), '%Y-%m-%d %23:%59:%59') twelve_month_ago_end;
+---------------------+---------------------+------------------------+----------------------+
| NOW()               | twelve_month_ago    | twelve_month_ago_start | twelve_month_ago_end |
+---------------------+---------------------+------------------------+----------------------+
| 2023-06-08 09:53:03 | 2022-07-08 09:53:03 | 2022-07-01 00:00:00    | 2022-07-31 23:59:59  |
+---------------------+---------------------+------------------------+----------------------+
1 row in set (0.00 sec)

對應的 Java 時間：

import cn.hutool.core.date.DateUtil;
import lombok.extern.slf4j.Slf4j;

import java.time.LocalDateTime;

@Slf4j
public class DateTimeUtil {
    public static void main(String[] args) {
        LocalDateTime now = LocalDateTime.now();
        log.info("當前時間: {}", now);

        LocalDateTime todayStartTime = DateUtil.toLocalDateTime(DateUtil.beginOfDay(new Date()));
        LocalDateTime todayEndTime = DateUtil.toLocalDateTime(DateUtil.endOfDay(new Date()));
        log.info("本日開始時間: {}, 本日結束時間: {}", todayStartTime, todayEndTime);

        LocalDateTime weekStartTime = DateUtil.toLocalDateTime(DateUtil.beginOfWeek(new Date()));
        LocalDateTime weekEndTime = DateUtil.toLocalDateTime(DateUtil.endOfWeek(new Date()));
        log.info("本週開始時間: {}, 本週結束時間: {}", weekStartTime, weekEndTime);

        LocalDateTime monthStartTime = DateUtil.toLocalDateTime(DateUtil.beginOfMonth(new Date()));
        LocalDateTime monthEndTime = DateUtil.toLocalDateTime(DateUtil.endOfMonth(new Date()));
        log.info("本月開始時間: {}, 本月結束時間: {}", monthStartTime, monthEndTime);

        LocalDateTime serverDayAgoStartTime = DateUtil.toLocalDateTime(DateUtil.beginOfDay(DateUtil.offsetDay(new Date(), -6)));
        LocalDateTime serverDayAgoEndTime = DateUtil.toLocalDateTime(DateUtil.endOfDay(DateUtil.offsetDay(new Date(), -6)));
        log.info("當前時間前推7天的0點: {}, 當前時間前推7天的24點: {}", serverDayAgoStartTime, serverDayAgoEndTime);

        LocalDateTime twelveStartTime = DateUtil.toLocalDateTime(DateUtil.beginOfMonth(DateUtil.offsetMonth(new Date(), -11)));
        LocalDateTime twelveEndTime = DateUtil.toLocalDateTime(DateUtil.endOfMonth(DateUtil.offsetMonth(new Date(), -11)));
        log.info("當前時間前推12個月的月初的0點: {}, 當前時間前推12個月的月初的24點: {}", twelveStartTime, twelveEndTime);
	}
}

結果:
  當前時間: 2023-06-08T09:58:41.425010100
  本日開始時間: 2023-06-08T00:00, 本日結束時間: 2023-06-08T23:59:59.999
  本週開始時間: 2023-06-05T00:00, 本週結束時間: 2023-06-11T23:59:59.999
  本月開始時間: 2023-06-01T00:00, 本月結束時間: 2023-06-30T23:59:59.999
  當前時間前推7天的0點: 2023-06-02T00:00, 當前時間前推7天的24點: 2023-06-02T23:59:59.999
  當前時間前推12個月的月初的0點: 2022-07-01T00:00, 當前時間前推12個月的月初的24點: 2022-07-31T23:59:59.999

Java 中 LocalDateTime 對應 MySQL 中的 datetime。

流程控制函式

流程處理函式可以根據不同的條件，執行不同的處理流程，可以在 SQL 語句中實現不同的條件選擇。MySQL 中的流程處理函式主要包括 IF()、IFNULL() 和 CASE() 函式。

函式 用法
IF(value, value1, value2) 如果 value 的值為 TRUE，返回 value1，否則返回 value2
IFNULL(value1, value2) 如果 value1 不為 NULL，返回 value1，否則返回 value2
CASE WHEN 條件 1 THEN 結果 1 WHEN 條件 2 THEN 結果 2 ...... [ELSE 結果n] END 相當於 Java 的 if...else if...else...
CASE expr WHEN 常量值 1 THEN 值 1 WHEN 常量值 2 THEN 值 2 ...... [ELSE 值 n] END 相當於 Java 的 switch...case...

示例：

mysql> SELECT IF(1 > 0, 'true', 'false');
+----------------------------+
| IF(1 > 0, 'true', 'false') |
+----------------------------+
| true                       |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(null, 'Hello Word');
+----------------------------+
| IFNULL(null, 'Hello Word') |
+----------------------------+
| Hello Word                 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END;
+-----------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END |
+-----------------------------------------------------------------------+
| 1 > 0                                                                 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 1 WHEN 1 THEN 'is 1' WHEN 2 THEN 'is 2' ELSE 'other' END;
+---------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'is 1' WHEN 2 THEN 'is 2' ELSE 'other' END |
+---------------------------------------------------------------+
| is 1                                                          |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT employee_id, salary, CASE WHEN salary >= 15000 THEN '1' WHEN salary >= 10000 THEN '2' WHEN salary >= 8000 THEN '3' ELSE '4' END "position" FROM employees;
+-------------+----------+----------+
| employee_id | salary   | position |
+-------------+----------+----------+
|         100 | 24000.00 | 1        |
|         101 | 17000.00 | 1        |
|         102 | 17000.00 | 1        |
|         103 |  9000.00 | 3        |
|         104 |  6000.00 | 4        |
|         105 |  4800.00 | 4        |
|         106 |  4800.00 | 4        |
|         107 |  4200.00 | 4        |
|         108 | 12000.00 | 2        |
|         205 | 12000.00 | 2        |
|         206 |  8300.00 | 3        |
+-------------+----------+----------+
107 rows in set (0.00 sec)

mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT employee_id, 12 * salary * (1 + IFNULL(commission_pct, 0)) FROM employees;
+-------------+-----------------------------------------------+
| employee_id | 12 * salary * (1 + IFNULL(commission_pct, 0)) |
+-------------+-----------------------------------------------+
|         100 |                                     288000.00 |
|         101 |                                     204000.00 |
|         102 |                                     204000.00 |
|         103 |                                     108000.00 |
|         104 |                                      72000.00 |
|         105 |                                      57600.00 |
|         106 |                                      57600.00 |
|         107 |                                      50400.00 |
|         108 |                                     144000.00 |
|         204 |                                     120000.00 |
|         205 |                                     144000.00 |
|         206 |                                      99600.00 |
+-------------+-----------------------------------------------+
107 rows in set (0.00 sec)

mysql> SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10 * salary WHEN 'ST_CLERK' THEN 1.15 * salary WHEN 'SA_REP' THEN 1.20 * salary ELSE salary END "REVISED_SALARY" FROM employees;
+-------------+------------+----------+----------------+
| last_name   | job_id     | salary   | REVISED_SALARY |
+-------------+------------+----------+----------------+
| King        | AD_PRES    | 24000.00 |       24000.00 |
| Kochhar     | AD_VP      | 17000.00 |       17000.00 |
| De Haan     | AD_VP      | 17000.00 |       17000.00 |
| Hunold      | IT_PROG    |  9000.00 |        9900.00 |
| Ernst       | IT_PROG    |  6000.00 |        6600.00 |
| Austin      | IT_PROG    |  4800.00 |        5280.00 |
| Baer        | PR_REP     | 10000.00 |       10000.00 |
| Higgins     | AC_MGR     | 12000.00 |       12000.00 |
| Gietz       | AC_ACCOUNT |  8300.00 |        8300.00 |
+-------------+------------+----------+----------------+
107 rows in set (0.00 sec)

加密與解密函式

加密與解密函式主要用於對資料庫中的資料進行加密和解密處理，以防止資料被他人竊取，這些函式在保證資料庫安全時非常有用。

函式 用法
PASSWORD(str) 返回字串 str 的加密版本，41 位長的字串。加密結果不可逆 ，常用於使用者的密碼加密
MD5(str) 返回字串 str 的 md5 加密後的值，也是一種加密方式。若引數為 NULL，則會返回 NULL
SHA(str) 從原明文密碼 str 計算並返回加密後的密碼字串，當引數為 NULL 時，返回 NULL。 SHA 加密演算法比 MD5 更加安全
ENCODE(value, password_seed) 返回使用 password_seed 作為加密密碼加密 value
DECODE(value, password_seed) 返回使用 password_seed 作為加密密碼解密 value

可以看到，ENCODE(value, password_seed) 函式與 DECODE(value, password_seed) 函式互為反函式。

示例：

ysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('mysql'), PASSWORD(NULL)' at line 1

mysql> SELECT md5('123');
+----------------------------------+
| md5('123')                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SHA('Tom123');
+------------------------------------------+
| SHA('Tom123')                            |
+------------------------------------------+
| c7c506980abc31cc390a2438c90861d0f1216d50 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ENCODE('mysql', 'mysql');
ERROR 1305 (42000): FUNCTION atguigudb.ENCODE does not exist

mysql> SELECT DECODE(ENCODE('mysql','mysql'), 'mysql');
ERROR 1305 (42000): FUNCTION atguigudb.DECODE does not exist

MySQL 資訊函式

MySQL 中內建了一些可以查詢 MySQL 資訊的函式，這些函式主要用於幫助資料庫開發或運維人員更好地對資料庫進行維護工作。

函式 用法
VERSION() 返回當前 MySQL 的版本號
CONNECTION_ID() 返回當前 MySQL 伺服器的連線數
DATABASE()，SCHEMA() 返回 MySQL 命令列當前所在的資料庫
USER()，CURRENT_USER()，SYSTEM_USER()，SESSION_USER() 返回當前連線 MySQL 的使用者名稱，返回結果格式為：主機名@使用者名稱
CHARSET(value) 返回字串 value 自變數的字符集
COLLATION(value) 返回字串 value 的比較規則

示例：

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| atguigudb  |
+------------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| atguigudb  |
+------------+
1 row in set (0.00 sec)

mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@127.0.0.1 | root@%         | root@127.0.0.1 | root@127.0.0.1 |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb3        |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT COLLATION('ABC');
+------------------+
| COLLATION('ABC') |
+------------------+
| utf8_general_ci  |
+------------------+
1 row in set (0.01 sec)

其他函式

MySQL 中有些函式無法對其進行具體的分類，但是這些函式在 MySQL 的開發和運維過程中也是不容忽視的。

函式 用法
FORMAT(value, n) 返回對數字 value 進行格式化後的結果資料。n 表示四捨五入後保留到小數點後 n 位
CONV(value, from, to) 將 value 的值進行不同進位制之間的轉換
INET_ATON(ipvalue) 將以點分隔的 IP 地址轉化為一個數字
INET_NTOA(value) 將數字形式的 IP 地址轉化為以點分隔的 IP 地址
BENCHMARK(n, expr) 將表示式 expr 重複執行 n 次。用於測試 MySQL 處理 expr 表示式所耗費的時間
CONVERT(value USING char_code) 將 value 所使用的字元編碼修改為 char_code

示例：

# 如果 n 的值小於或者等於 0，則只保留整數部分
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12             | 124                | 123                 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONV(16, 10, 2), CONV(8888, 10, 16), CONV(NULL, 10, 2);
+-----------------+--------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888, 10, 16) | CONV(NULL, 10, 2) |
+-----------------+--------------------+-------------------+
| 10000           | 22B8               | NULL              |
+-----------------+--------------------+-------------------+
1 row in set (0.00 sec)

# 以 192.168.1.100 為例，計算方式為 192 乘以 256 的 3 次方，加上 168 乘以 256 的 2 次方，加上 1 乘以 256，再加上 100
mysql> SELECT INET_ATON('192.168.1.100');
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
|                 3232235876 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(3232235876);
+-----------------------+
| INET_NTOA(3232235876) |
+-----------------------+
| 192.168.1.100         |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(1, MD5('mysql'));
+----------------------------+
| BENCHMARK(1, MD5('mysql')) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(1000000, MD5('mysql'));
+----------------------------------+
| BENCHMARK(1000000, MD5('mysql')) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.11 sec)

mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb3          | utf8mb3                                |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

聚合函式

概述

除了 SQL 單行函式，實際上 SQL 函式還有一類，叫做聚合 (或聚集、分組) 函式，它是對一組資料進行彙總的函式，輸入的是一組資料的集合，輸出的是單個值。

聚合函式作用於一組資料，並對一組資料返回一個值：

image-20230424010313346

聚合函式型別：

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

聚合函式用法：

image-20230424132056267

聚合函式不能巢狀呼叫，比如不能出現類似 "AVG(SUM(欄位名稱))" 形式的呼叫。

AVG 和 SUM 函式

可以對數值型資料使用 AVG 和 SUM 函式。

mysql> SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
+-------------+-------------+-------------+-------------+
| AVG(salary) | MAX(salary) | MIN(salary) | SUM(salary) |
+-------------+-------------+-------------+-------------+
| 8272.727273 |    11500.00 |     6000.00 |   273000.00 |
+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)

MIN 和 MAX 函式

可以對任意資料型別的資料使用 MIN 和 MAX 函式。

mysql> SELECT MIN(hire_date), MAX(hire_date) FROM employees;
+----------------+----------------+
| MIN(hire_date) | MAX(hire_date) |
+----------------+----------------+
| 1987-06-17     | 2000-04-21     |
+----------------+----------------+
1 row in set (0.00 sec)

COUNT 函式

COUNT(*)返回表中記錄總數，適用於任意資料型別：

mysql> SELECT COUNT(*) FROM employees WHERE department_id = 50;
+----------+
| COUNT(*) |
+----------+
|       45 |
+----------+
1 row in set (0.00 sec)

COUNT(expr)返回 expr 不為空的記錄總數：

mysql> SELECT COUNT(commission_pct) FROM employees WHERE department_id = 50;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

問題：用 count(*)，count(1)，count(列名) 誰好呢？

  • 其實，對於 MyISAM 引擎的表是沒有區別的，這種引擎內部有一計數器在維護著行數。InnoDB 引擎的表用 count(*)，count(1) 直接讀行數，複雜度是 O(n)，因為 InnoDB 真的要去數一遍，但好於具體的 count(列名)。

問題：能不能使用 count(列名) 替換 count(*)？

  • 不要使用 count(列名) 來替代 count(*)，count(*) 是 SQL-92 定義的標準統計行數的語法，跟資料庫無關，跟 NULL 和非 NULL 無關。

count(*) 會統計值為 NULL 的行，而 count(列名) 不會統計此列為 NULL 值的行。

GROUP BY

基本使用

image-20230425124448094

可以使用 GROUP BY 子句將表中的資料分成若干組：

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

WHERE 一定放在 FROM 後面。

在 SELECT 列表中所有未包含在組函式中的列，都應該包含在 GROUP BY 子句中：

mysql> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|          NULL |  7000.000000 |
|            10 |  4400.000000 |
|            20 |  9500.000000 |
|            30 |  4150.000000 |
|            40 |  6500.000000 |
|            50 |  3475.555556 |
|            60 |  5760.000000 |
|            70 | 10000.000000 |
|            80 |  8955.882353 |
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
+---------------+--------------+
12 rows in set (0.00 sec)

反過來，包含在 GROUP BY 子句中的列不必包含在 SELECT 列表中：

mysql> SELECT AVG(salary) FROM employees GROUP BY department_id;
+--------------+
| AVG(salary)  |
+--------------+
|  7000.000000 |
|  4400.000000 |
|  9500.000000 |
|  4150.000000 |
|  6500.000000 |
|  3475.555556 |
|  5760.000000 |
| 10000.000000 |
|  8955.882353 |
| 19333.333333 |
|  8600.000000 |
| 10150.000000 |
+--------------+
12 rows in set (0.00 sec)

使用多個列分組

image-20230425125045633

示例：

mysql> SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id;
+---------+------------+-------------+
| dept_id | job_id     | SUM(salary) |
+---------+------------+-------------+
|      90 | AD_PRES    |    24000.00 |
|      90 | AD_VP      |    34000.00 |
|      60 | IT_PROG    |    28800.00 |
|     100 | FI_MGR     |    12000.00 |
|     100 | FI_ACCOUNT |    39600.00 |
|      30 | PU_MAN     |    11000.00 |
|      30 | PU_CLERK   |    13900.00 |
|      50 | ST_MAN     |    36400.00 |
|      50 | ST_CLERK   |    55700.00 |
|      80 | SA_MAN     |    61000.00 |
|      80 | SA_REP     |   243500.00 |
|    NULL | SA_REP     |     7000.00 |
|      50 | SH_CLERK   |    64300.00 |
|      10 | AD_ASST    |     4400.00 |
|      20 | MK_MAN     |    13000.00 |
|      20 | MK_REP     |     6000.00 |
|      40 | HR_REP     |     6500.00 |
|      70 | PR_REP     |    10000.00 |
|     110 | AC_MGR     |    12000.00 |
|     110 | AC_ACCOUNT |     8300.00 |
+---------+------------+-------------+
20 rows in set (0.00 sec)

使用 WITH ROLLUP

使用WITH ROLLUP關鍵字之後，在所有查詢出的分組記錄之後增加一條記錄，該記錄計算查詢出的所有記錄的總和，即統計記錄數量。

mysql> SELECT department_id, AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
|          NULL | 11809.090909 |
+---------------+--------------+
4 rows in set (0.00 sec)

當使用 ROLLUP 時，不能同時使用 ORDER BY 子句進行結果排序，即 ROLLUP 和 ORDER BY 是互相排斥的。

HAVING

基本使用

image-20230426123459633

過濾分組：HAVING 子句。

  • 行已經被分組。
  • 使用了聚合函式。
  • 滿足 HAVING 子句中條件的分組將被顯示。
  • HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用。

HAVING 用法：

image-20230426123850274

示例：

mysql> SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
|            80 |    14000.00 |
|            90 |    24000.00 |
|           100 |    12000.00 |
|           110 |    12000.00 |
+---------------+-------------+
6 rows in set (0.00 sec)

非法使用聚合函式：不能在 WHERE 子句中使用聚合函式。如下：

mysql> SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;
ERROR 1111 (HY000): Invalid use of group function

WHERE 和 HAVING 的對比

區別 1：WHERE 可以直接使用表中的欄位作為篩選條件，但不能使用分組中的計算函式作為篩選條件；HAVING 必須要與 GROUP BY 配合使用，可以把分組計算的函式和分組欄位作為篩選條件。

  • 這決定了，在需要對資料進行分組統計的時候，HAVING 可以完成 WHERE 不能完成的任務。這是因為，在查詢語法結構中，WHERE 在 GROUP BY 之前，所以無法對分組結果進行篩選。HAVING 在 GROUP BY 之後，可以使用分組欄位和分組中的計算函式，對分組的結果集進行篩選，這個功能是 WHERE 無法完成的。另外，WHERE 排除的記錄不再包括在分組中。

區別 2：如果需要透過連線從關聯表中獲取需要的資料，WHERE 是先篩選後連線，而 HAVING 是先連線後篩選。

  • 這決定了，在關聯查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一個篩選後的較小資料集和關聯表進行連線，這樣佔用的資源比較少，執行效率也比較高。HAVING 則需要先把結果集準備好，也就是用未被篩選的資料集進行關聯，然後對這個大的資料集進行篩選，這樣佔用的資源就比較多，執行效率也較低。

小結：

優點 缺點
WHERE 先篩選資料再關聯，執行效率高 不能使用分組中的計算函式進行篩選
HAVING 可以使用分組中的計算函式 在最後的結果集中進行篩選，執行效率較低

開發中的選擇：
WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢裡面同時使用 WHERE 和 HAVING。包含分組統計函式的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發揮了 HAVING 可以使用包含分組統計函式的查詢條件的優點。當資料量特別大的時候，執行效率會有很大的差別。

SELECT 的執行過程

查詢的結構

方式一：

SELECT ..., ..., ...
FROM ..., ..., ...
WHERE 多表的連線條件
AND 不包含組函式的過濾條件
GROUP BY ..., ...
HAVING 包含組函式的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...;

方式二：

SELECT ..., ..., ...
FROM ...
JOIN ... ON 多表的連線條件
JOIN ... ON 多表的連線條件
...
WHERE 不包含組函式的過濾條件
AND/OR 不包含組函式的過濾條件
GROUP BY ..., ...
HAVING 包含組函式的過濾條件
ORDER BY ... ASC/DESC
LIMIT ..., ...;

其中：

  • FROM：從哪些表中篩選。
  • ON：關聯多表查詢時，去除笛卡爾積。
  • WHERE：從表中篩選的條件。
  • GROUP BY：分組依據。
  • HAVING：在統計結果中再次篩選。
  • ORDER BY：排序。
  • LIMIT：分頁。

SELECT 執行順序

SELECT 查詢時的兩個順序：

  • 關鍵字的順序是不能顛倒的：

    image-20230426185026255

  • SELECT 語句的執行順序（在 MySQL 和 Oracle 中，SELECT 執行順序基本相同）：

    image-20230426185138493

比如如下 SQL 語句，它的關鍵字順序和執行順序是下面這樣的：

SELECT DISTINCT player_id, player_name, COUNT(*) AS num # 順序 5
FROM player JOIN team ON player.team_id = team.team_id # 順序 1
WHERE height > 1.80 # 順序 2
GROUP BY player.team_id # 順序 3
HAVING num > 2 # 順序 4
ORDER BY num DESC # 順序 6
LIMIT 2 # 順序 7

在 SELECT 語句執行這些步驟的時候，每個步驟都會產生一個虛擬表，然後將這個虛擬表傳入下一個步驟中作為輸入。需要注意的是，這些步驟隱含在 SQL 的執行過程中，對於使用者來說是不可見的。

SQL 的執行原理

  1. SELECT 是先執行FROM 階段的。在這個階段，如果是多張表聯查，還會經歷下面的幾個步驟：
    1. 首先先透過 CROSS JOIN 求笛卡爾積，相當於得到虛擬表 vt1-1（virtual table）；
    2. 透過 ON 進行篩選，在虛擬表 vt1-1 的基礎上進行篩選，得到虛擬表 vt1-2；
    3. 新增外部行。如果我們使用的是左連線、右連結或者全連線，就會涉及到外部行，也就是在虛擬表 vt1-2 的基礎上增加外部行，得到虛擬表 vt1-3。
  2. 如果操作的是兩張以上的表，還會重複上面的步驟，直到所有表都被處理完為止，這個過程得到的是原始資料。
  3. 當拿到了查詢資料表的原始資料，也就是最終的虛擬表 vt1，就可以在此基礎上再進行WHERE 階段。在這個階段中，會根據 vt1 表的結果進行篩選過濾，得到虛擬表 vt2。
  4. 然後進入第三步和第四步，也就是GROUP BY 和 HAVING 階段。在這個階段中，實際上是在虛擬表 vt2 的基礎上進行分組和分組過濾，得到中間的虛擬表 vt3 和 vt4。
  5. 當完成了條件篩選部分之後，就可以篩選表中提取的欄位，也就是進入到 SELECT 和 DISTINCT 階段。
  6. 之後，在SELECT 階段會提取想要的欄位，然後在DISTINCT 階段過濾掉重複的行，分別得到中間的虛擬表 vt5-1 和 vt5-2。
  7. 當提取了想要的欄位資料之後，就可以按照指定的欄位進行排序，也就是ORDER BY 階段，得到虛擬表 vt6。
  8. 最後在 vt6 的基礎上，取出指定行的記錄，也就是LIMIT 階段，得到最終的結果，對應的是虛擬表 vt7。

當然，在寫 SELECT 語句的時候，不一定存在所有的關鍵字，相應的階段就會省略。

同時因為 SQL 是一門類似英語的結構化查詢語言，所以在寫 SELECT 語句的時候，還要注意相應的關鍵字順序，所謂底層執行的原理，就是上面講到的執行順序。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md

相關文章