五.單行函數

單行函數

1.函數的理解
- 1.1什么是函數
- 1.2不同DBMS函數的差異
- 1.3MySQL的內置函數分類
2.數值函數
- 2.1基本函數
- 2.2角度與弧度互換函數
- 2.3三角函數
- 2.4指數與對數
- 2.5進制間的轉換
3.字符串函數
4.日期和時間函數
- 4.1獲取日期、時間
- 4.2日期與時間戳的轉換
- 4.3獲取月份、星期、星期數、天數等函數
- 4.4日期的操作函數
- 4.5時間和秒鐘轉換的函數
- 4.6計算日期和時間的函數
- 4.7日期的格式化與解析
5.流程控制函數
6.加密與解密函數
7.MySQL信息函數
8.其他函數

1.函數的理解

1.1什么是函數

函數在計算機語言的使用中貫穿始終，函數的作用是什么呢？它可以把我們經常使用的代碼封裝起來，需要的時候直接調用即可。這樣既 提高了代碼效率 ，又 提高了可維護性 。在 SQL 中我們也可以使用函數對檢索出來的數據進行函數操作。使用這些函數，可以極大地 提高用戶對數據庫的管理效率 。
在這里插入圖片描述
從函數定義的角度出發，我們可以將函數分成 內置函數 和 自定義函數 。在 SQL 語言中，同樣也包括了
內置函數和自定義函數。內置函數是系統內置的通用函數，而自定義函數是我們根據自己的需要編寫的。

1.2不同DBMS函數的差異

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

1.3MySQL的內置函數分類

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

MySQL提供的內置函數從實現的功能角度可以分為數值函數、字符串函數、日期和時間函數、流程控制函數、加密與解密函數、獲取MySQL信息函數、聚合函數等。這里，我將這些豐富的內置函數再分為兩類：單行函數、聚合函數（或分組函數）。
兩種SQL函數
在這里插入圖片描述
單行函數

操作數據對象
接受參數返回一個結果
只對一行進行變換
每行返回一個結果
可以嵌套
參數可以是一列或一個值

2.數值函數

2.1基本函數

函數	用法
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> mysql> SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.32),FLOOR(32.32),FLOOR(-43.32),MOD(12,5) FROM DUAL;
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
| ABS(-123) | ABS(32) | SIGN(-23) | SIGN(43) | PI()     | CEIL(32.32) | CEILING(-43.32) | FLOOR(32.32) | FLOOR(-43.32) | MOD(12,5) |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
|       123 |      32 |        -1 |        1 | 3.141593 |          33 |             -43 |           32 |           -44 |         2 |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
1 row in set (0.00 sec)

在這里插入圖片描述

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL;

在這里插入圖片描述

SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1) FROM DUAL;

在這里插入圖片描述

2.2角度與弧度互換函數

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

SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM DUAL;

在這里插入圖片描述

2.3三角函數

函數	用法
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)函數則仍然可以計算。

ATAN2(M,N)函數的使用示例如下：

SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1) ) FROM DUAL;

在這里插入圖片描述

2.4指數與對數

函數	用法
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

SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)  FROM DUAL;

在這里插入圖片描述

2.5進制間的轉換

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

SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)  FROM DUAL;

在這里插入圖片描述

3.字符串函數

函數	用法
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,…)函數，但是每個字符串之間要加上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 s1FROM s)	去掉字符串s開始處的s1
TRIM(TRAILING s1FROM 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開始的。

SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma') FROM DUAL;
SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');

在這里插入圖片描述

4.日期和時間函數

4.1獲取日期、時間

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

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0 FROM DUAL;

在這里插入圖片描述

4.2日期與時間戳的轉換

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

舉例

mysql> SELECT UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1701975404 |
+-----------------------+
1 row in set (0.00 sec)mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
|                1701925200 |
+---------------------------+
1 row in set (0.00 sec)mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
|                1701975409 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2023-12-07 14:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2023-12-07 14:00:00') |
+---------------------------------------+
|                            1701975600 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP(1701975600);
+----------------------------+
| UNIX_TIMESTAMP(1701975600) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

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

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

舉例

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(SYSDATE()) FROM DUAL;

在這里插入圖片描述
舉例

SELECT MONTHNAME('2023-12-07'),DAYNAME('2023-12-07'),WEEKDAY('2023-12-07'),QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),DAYOFMONTH(NOW()),DAYOFWEEK(NOW()) FROM DUAL;

在這里插入圖片描述

4.4日期的操作函數

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

EXTRACT(type FROM date)函數中type的取值與含義：
在這里插入圖片描述

舉例

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()) |
+----------------------------+---------------------------+------------------------------+------------------------------------+
|                         34 |                        49 |                            4 |                               3453 |
+----------------------------+---------------------------+------------------------------+------------------------------------+
1 row in set (0.00 sec)

4.5時間和秒鐘轉換的函數

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

舉例

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
|              56286 |
+--------------------+
1 row in set (0.00 sec)mysql> SELECT SEC_TO_TIME(56286);
+--------------------+
| SEC_TO_TIME(56286) |
+--------------------+
| 15:38:06           |
+--------------------+
1 row in set (0.00 sec)

4.6計算日期和時間的函數

函數	用法
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	返回與給定日期時間相差INTERVAL時間段的日期時間
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	返回與date相差INTERVAL時間間隔的日期

上述函數中type的取值：
在這里插入圖片描述

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY) AS col1,DATE_ADD('2023-12-07 15:45:00',INTERVAL 1 SECOND) AS col2,ADDDATE('2023-12-07 15:45:00',INTERVAL 1 SECOND) AS col3,DATE_ADD('2023-12-07 15:45:00',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;

在這里插入圖片描述

SELECT DATE_SUB('2023-12-07',INTERVAL 31 DAY) AS col1, SUBDATE('2023-12-07',INTERVAL 31 DAY) AS col2, DATE_SUB('2023-12-07 15:55:00',INTERVAL '1_1' DAY_HOUR) AS col3 FROM DUAL;

在這里插入圖片描述

函數	用法
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后的時間

舉例

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2023-12-06'),TIMEDIFF(NOW(),'2023-12-06 12:00:00'),FROM_DAYS(366),TO_DAYS('0000-12-25'),LAST_DAY(NOW()),MAKEDATE(NOW(),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10) FROM DUAL;

在這里插入圖片描述

mysql> SELECT ADDTIME(NOW(),50);
+---------------------+
| ADDTIME(NOW(),50)   |
+---------------------+
| 2023-12-07 17:49:32 |
+---------------------+
1 row in set (0.00 sec)mysql> SELECT ADDTIME(NOW(),'1:1:1');
+------------------------+
| ADDTIME(NOW(),'1:1:1') |
+------------------------+
| 2023-12-07 18:51:17    |
+------------------------+
1 row in set (0.00 sec)mysql> SELECT SUBTIME(NOW(),'-1:-1:-1');
+---------------------------+
| SUBTIME(NOW(),'-1:-1:-1') |
+---------------------------+
| 2023-12-07 17:51:11       |
+---------------------------+
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,33);
+-------------------+
| MAKEDATE(2020,33) |
+-------------------+
| 2020-02-02        |
+-------------------+
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) |
+------------------------------+
|                  13754718902 |
+------------------------------+
1 row in set (0.00 sec)mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
|         739226 |
+----------------+
1 row in set (0.00 sec)

4.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	表示兩位數字表示年份
%M	月名表示月份	（January,…）	%m兩位數字表示月份
%b	縮寫的月名	（Jan.，Feb.，…）	%c 數字表示月份
%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,4…）
%i	兩位數字表示分鐘（00,01,02）	%S和%s	兩位數字表示秒(00,01,02…)
%W	一周中的星期名稱（Sunday…）	%a	一周中的星期縮寫（Sun.，Mon.,Tues.，…）
%w	以數字表示周中的天數(0=Sunday,1=Monday…)
%j	以3位數字表示年中的天數(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參數取值如下：
在這里插入圖片描述
舉例

mysql> SELECT DATE_FORMAT(NOW(),'%H:%i:%s');
+-------------------------------+
| DATE_FORMAT(NOW(),'%H:%i:%s') |
+-------------------------------+
| 19:29:06                      |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('12/07/2023','%m/%d/%Y') FROM DUAL;
+--------------------------------------+
| STR_TO_DATE('12/07/2023','%m/%d/%Y') |
+--------------------------------------+
| 2023-12-07                           |
+--------------------------------------+
1 row in set (0.00 sec)mysql> SELECT STR_TO_DATE('12/07/2023','%m/%d/%Y');
+--------------------------------------+
| STR_TO_DATE('12/07/2023','%m/%d/%Y') |
+--------------------------------------+
| 2023-12-07                           |
+--------------------------------------+
1 row in set (0.00 sec)mysql> SELECT STR_TO_DATE('20231207180000','%Y%m%d%H%i%s');
+----------------------------------------------+
| STR_TO_DATE('20231207180000','%Y%m%d%H%i%s') |
+----------------------------------------------+
| 2023-12-07 18:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)mysql> SELECT STR_TO_DATE('2023-12-07 18:00:00','%Y-%m-%d %H:%i:%s');
+--------------------------------------------------------+
| STR_TO_DATE('2023-12-07 18:00:00','%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------+
| 2023-12-07 18:00:00                                    |
+--------------------------------------------------------+
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')) |
+-------------------------------------------+
| 12.07.2023                                |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('2023-12-07 18:00:00','%Y-%m-%d');
+-----------------------------------------------+
| STR_TO_DATE('2023-12-07 18:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2023-12-07                                    |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5.流程控制函數

流程處理函數可以根據不同的條件，執行不同的處理流程，可以在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 resultn]END	相當于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 … [ELSE 值n] END	相當于Java的switch…case…

mysql> SELECT IF (1>0,'正確','錯誤');
+----------------------------+
| IF (1>0,'正確','錯誤')     |
+----------------------------+
| 正確                       |
+----------------------------+
1 row in set (0.00 sec)mysql> SELECT IFNULL (null,'HelloWorld');
+----------------------------+
| IFNULL (null,'HelloWorld') |
+----------------------------+
| HelloWorld                 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
+---------------------------------------------------------------------+
| yes                                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown                                                            |
+--------------------------------------------------------------------+
1 row 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 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)

6.加密與解密函數

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

函數	用法
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）函數互為反函數
舉例

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql')                         | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT MD5('123');
+----------------------------------+
| MD5('123')                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set (0.08 sec)mysql> SELECT SHA('Tom123');
+------------------------------------------+
| SHA('Tom123')                            |
+------------------------------------------+
| c7c506980abc31cc390a2438c90861d0f1216d50 |
+------------------------------------------+
1 row in set (0.10 sec)
mysql> SELECT ENCODE('mysql','mysql');
+-------------------------+
| ENCODE('mysql','mysql') |
+-------------------------+
| �g���                       |
+-------------------------+
1 row in set, 1 warning (0.00 sec)mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql                                   |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

7.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 DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
mysql> SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@%         | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4        |
+----------------+
1 row in set (0.00 sec)mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC')   |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.01 sec)

8.其他函數

函數	用法
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 USINGchar_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)
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)
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.22 sec)
mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb4          | utf8                                   |
+------------------+----------------------------------------+
1 row in set (0.00 sec)

練習

顯示系統時間(注：日期+時間)
查詢員工號，姓名，工資，以及工資提高百分之20%后的結果（new salary）
將員工的姓名按首字母排序，并寫出姓名的長度（length）
查詢員工id,last_name,salary，并作為一個列輸出，別名為OUT_PUT
查詢公司各員工工作的年數、工作的天數，并按工作年數的降序排序
查詢員工姓名，hire_date , department_id，滿足以下條件：雇用時間在1997之后，department_id為80 或 90 或110, commission_pct不為空
查詢公司中入職超過10000天的員工姓名、入職時間
做一個查詢，產生下面的結果
使用case-when，按照下面的條件

1.顯示系統時間(注：日期+時間)
SELECT NOW()
FROM DUAL;2.查詢員工號，姓名，工資，以及工資提高百分之20%后的結果（new salary）
SELECT employee_id, last_name, salary, salary * 1.2 "new salary"FROM employees;3.將員工的姓名按首字母排序，并寫出姓名的長度（length）
SELECT last_name, LENGTH(last_name)
FROM employees 
ORDER BY last_name DESC;4.查詢員工id,last_name,salary，并作為一個列輸出，別名為OUT_PUT
SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT
FROM employees;5.查詢公司各員工工作的年數、工作的天數，并按工作年數的降序排序
SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(),
hire_date) worked_days
FROM employees
ORDER BY worked_years DESC6.查詢員工姓名，hire_date , department_id，滿足以下條件：雇用時間在1997之后，department_id為80 或 90 或110, commission_pct不為空
SELECT last_name, hire_date, department_id
FROM employees
#WHERE hire_date >= '1997-01-01'
#WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d')
WHERE DATE_FORMAT(hire_date,'%Y') >= '1997'
AND department_id IN (80, 90, 110)
AND commission_pct IS NOT NULL7.查詢公司中入職超過10000天的員工姓名、入職時間
SELECT last_name,hire_date
FROM employees
#WHERE TO_DAYS(NOW()) - to_days(hire_date) > 10000;
WHERE DATEDIFF(NOW(),hire_date) > 10000;8.做一個查詢，產生下面的結果
-- <last_name> earns `<salary>` monthly but wants <salary*3>
-- Dream Salary
-- King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ',
TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees;9.使用case-when，按照下面的條件
-- job       grade
-- AD_PRES     A
-- ST_MAN      B
-- IT_PROG     C
-- SA_REP      D
-- ST_CLERK    E
-- 產生下面的結果
-- Last_name Job_id  Grade
-- king      AD_PRES   A
SELECT last_name Last_name, job_id Job_id, 
CASE job_id WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F' END "grade"
FROM employees;