函數

日後有需要用到的函數大多可以在這裡查到

根據輸入的資訊，返回某種結果
函數在不同的DBMS(資料庫軟體)之間的差距很大，使用時要注意移植性，以下都是基於MySQL

常見分類

依照資料類型:數值、字符串、時間日期
依照功能:流程控制、加密解密、獲取信息
依照輸入資料的行數:單行、多行(聚合)

單行函數

對一行資料進行變換、每行返回一個結果
- 例如:想知道每個員工的工作年資，具體作法為現在時間減去員工入職時間，就可以調用時間相關的函數
單行函數可以嵌套使用

範例太多我就複製貼上了，先過眼了解，有用到時不熟悉再來查

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

2.1 角度

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

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

2.2 三角函數

函數	用法
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為弧度值

2.3 指數與對數

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

2.4 進制間的轉換

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

3. 字串函數

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

函數	用法
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 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> 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', '');
+-------------------------+---------------------+
| NULLIF('mysql','mysql') | NULLIF('mysql', '') |
+-------------------------+---------------------+
| NULL                    | mysql               |
+-------------------------+---------------------+
1 row in set (0.00 sec)

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()) |
+-----------------------+
|            1576380910 |
+-----------------------+
1 row in set (0.01 sec)

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

mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
|                1576380969 |
+---------------------------+
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') |
+---------------------------------------+
|                            1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10       |
+---------------------------+
1 row in set (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(NOW()),SECOND(SYSDATE())
FROM DUAL;

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

4.4 日期的操作函數

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

4.5 時間和秒鐘轉換的函數

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

舉例：

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

mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54            |
+--------------------+
1 row in set (0.12 sec)

4.6 計算日期和時間的函數

第1組：

函數	用法
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時間間隔的日期

舉例：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32: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;

SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

第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後的時間

舉例：

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-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)
FROM DUAL;

mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50)  |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '-1:-1:-1');
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25: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,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()) |
+----------------+
|          737773 |
+----------------+
1 row in set (0.00 sec)

舉例：查詢 7 天內的新增用戶數有多少？

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

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	兩位元數字表示月份（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,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	%%	表示%

舉例：

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34                        |
+--------------------------------+
1 row in set (0.00 sec)

SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;

SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;

SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;

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

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;

mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01                                    |
+-----------------------------------------------+
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…

SELECT IF(1 > 0,'正確','錯誤')
->正確

SELECT IFNULL(null,'Hello Word')
->Hello Word

SELECT CASE
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0

SELECT CASE 1
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是誰'

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
				  WHEN salary>=10000 THEN '潛力股'
				  WHEN salary>=8000 THEN '屌絲'
				  ELSE '草根' END  "描述"
FROM employees;

SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
								   WHEN 2 THEN '已付款'
								   WHEN 3 THEN '已發貨'
								   WHEN 4 THEN '確認收貨'
								   ELSE '無效訂單' END
FROM t_order;

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)

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;

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;

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)

SELECT md5('123')
->202cb962ac59075b964b07152d234b70

SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50

mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg　¼　ìÉ                  |
+--------------------------+
1 row in set, 1 warning (0.01 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 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@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+

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.00 sec)

8. 其他函數

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)

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

# 以"192.168.1.100"為例，計算方式為192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。

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.20 sec)

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

聚合(多行)函數

對一列資料(例如字段進行變換，返回一個結果
- 例如:想知道全部員工平均的薪水是多少
MySQL中聚合函數無法嵌套使用

常見的聚合函數

AVG:求平均，只能針對數值，會自動過濾null
SUM:求總和，只能針對數值，會自動過濾null

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees;

MAX:求最大，可用於數值、字串、時間類型或變量
MIN:求最小，可用於數值、字串、時間類型或變量

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

COUNT

返回表中或列中的資料個數，可用於任意數據類型

SELECT COUNT(*)
FROM	employees;

SELECT COUNT(*):包含null行
- SELECT COUNT(*)跟SELECT COUNT(1)、SELECT COUNT(23456)都是一樣的意思
SELECT COUNT(列名):會自動過濾null
結合上面知識點，求平均的時候要注意，如果資料有null的，用AVG會排除掉這些，可能導致錯誤的結果(例如求全國平均工資，結果有人工資是null，導致虛高)
- 正確寫法:sum(salary) / count(ifnull(salary,1)) – 1可以是任意
- 或:avg(ifnull(salary,0))
COUNT(參數)效率問題
- 如果是MyISAM引擎:*=1=字段，複雜度都是O(1)
- 如果是InnoDB引擎*=1>字段，不指定字段的情況下會調用二級索引優化效率，總之最好使用*

GROUP BY

跟ORDER BY用法類似

依照字段進行分組

select job_id,avg(salary)
from employees
group by job_id;

也可以多個條件，前後順序對於實質分組內容沒差，只是顯示順位會變
- 例如你分男/女與帥/醜，怎麼分就是那4種

select job_id,department_id ,avg(salary)
from employees
group by job_id,department_id;

但是注意select聲明的非聚合函數字段，必定要在group by中使用
- 不可能想看2種分類情形，卻只依靠1個分組條件
  - 例如我想select性別、長相，卻只group by性別，那分出來的數據無法確定長相，必定是錯的(但MySQL預設不報錯，可以自己調)
group by使用的字段，則不一定要select顯示出來
- 我可以分了但是不告訴你是怎分的
聲明位置:from ... where ... GROUP BY ... order by ... limit
group by ... WITH ROLLUP:在末尾增加一行，顯示總和
- 切記不要跟ORDER BY排序一起用

HAVING

用來過濾

聚合函數中不能使用WHERE，因為剛剛也提了WHERE順位在GROUP BY之前，車已經開走了
所以用HAVING取代WHERE來篩選
聲明位置:HAVING必須聲明在GROUP BY之後
開發中使用HAVING的前提就是在用了GROUP BY之後
- 如果沒有使用聚合函數，那用WHERE就好，效率更高，這個就涉及SQL語句的執行順序

SQL語句的執行順序

需要記住 SELECT 查詢時的兩個順序：

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

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

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

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的欄位 -> DISTINCT -> ORDER BY -> LIMIT

比如你寫了一個 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 的執行過程中，對於我們來說是不可見的。

上次修改於 2021-12-19