全面詳解MySQL單行函數(shù)分析
更新時(shí)間：2023年10月17日 15:11:13 作者：_GGBond_
MySQL常見(jiàn)的函數(shù)分為單行函數(shù)和分組函數(shù),單行函數(shù)包含字符函數(shù)、數(shù)學(xué)函數(shù)、日期函數(shù)、流程控制函數(shù)等,下面就詳細(xì)的來(lái)介紹一下MySQL單行函數(shù)
一、函數(shù)的理解
1、什么是函數(shù)
函數(shù)在計(jì)算機(jī)語(yǔ)言的使用中貫穿始終，函數(shù)的作用是什么呢？它可以把我們經(jīng)常使用的代碼封裝起來(lái)，需要的時(shí)候直接調(diào)用即可。這樣既提高了代碼效率，又提高了可維護(hù)性。在 SQL 中我們也可以使用函數(shù)對(duì)檢索出來(lái)的數(shù)據(jù)進(jìn)行函數(shù)操作。使用這些函數(shù)，可以極大地提高用戶(hù)對(duì)數(shù)據(jù)庫(kù)的管理效率。
從函數(shù)定義的角度出發(fā)，我們可以將函數(shù)分成內(nèi)置函數(shù)和自定義函數(shù)。在 SQL 語(yǔ)言中，同樣也包括了內(nèi)置函數(shù)和自定義函數(shù)。內(nèi)置函數(shù)是系統(tǒng)內(nèi)置的通用函數(shù)，而自定義函數(shù)是我們根據(jù)自己的需要編寫(xiě)的，本章及下一章講解的是 SQL 的內(nèi)置函數(shù)。
2、不同DBMS函數(shù)的差異
我們?cè)谑褂?SQL 語(yǔ)言的時(shí)候，不是直接和這門(mén)語(yǔ)言打交道，而是通過(guò)它使用不同的數(shù)據(jù)庫(kù)軟件，即 DBMS。DBMS 之間的差異性很大，遠(yuǎn)大于同一個(gè)語(yǔ)言不同版本之間的差異。實(shí)際上，只有很少的函數(shù)是被 DBMS 同時(shí)支持的。比如，大多數(shù) DBMS 使用（||）或者（+）來(lái)做拼接符，而在 MySQL 中的字符串拼接函數(shù)為concat()。大部分 DBMS 會(huì)有自己特定的函數(shù)，這就意味著采用 SQL 函數(shù)的代碼可移植性是很差的，因此在使用函數(shù)的時(shí)候需要特別注意。
3、MySQL的內(nèi)置函數(shù)及分類(lèi)
MySQL提供了豐富的內(nèi)置函數(shù)，這些函數(shù)使得數(shù)據(jù)的維護(hù)與管理更加方便，能夠更好地提供數(shù)據(jù)的分析與統(tǒng)計(jì)功能，在一定程度上提高了開(kāi)發(fā)人員進(jìn)行數(shù)據(jù)分析與統(tǒng)計(jì)的效率。
MySQL提供的內(nèi)置函數(shù)從實(shí)現(xiàn)的功能角度可以分為數(shù)值函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)、流程控制函數(shù)、加密與解密函數(shù)、獲取MySQL信息函數(shù)、聚合函數(shù)等。這里，我將這些豐富的內(nèi)置函數(shù)再分為兩類(lèi)：?jiǎn)涡泻瘮?shù)、聚合函數(shù)（或分組函數(shù)）。
兩種SQL函數(shù)
單行函數(shù)
- 操作數(shù)據(jù)對(duì)象
- 接受參數(shù)返回一個(gè)結(jié)果
- 只對(duì)一行進(jìn)行變換
- 每行返回一個(gè)結(jié)果
- 可以嵌套
- 參數(shù)可以是一列或一個(gè)值
二、數(shù)值函數(shù)
1、基本函數(shù)
| 函數(shù) | 用法 |
|---|---|
| ABS(x) | 返回x的絕對(duì)值 |
| SIGN(X) | 返回X的符號(hào)。正數(shù)返回1，負(fù)數(shù)返回-1，0返回0 |
| PI() | 返回圓周率的值 |
| CEIL(x)，CEILING(x) | 返回大于或等于某個(gè)值的最小整數(shù) |
| FLOOR(x) | 返回小于或等于某個(gè)值的最大整數(shù) |
| LEAST(e1,e2,e3…) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
| MOD(x,y) | 返回X除以Y后的余數(shù) |
| RAND() | 返回0~1的隨機(jī)值 |
| RAND(x) | 返回0~1的隨機(jī)值，其中x的值用作種子值，相同的X值會(huì)產(chǎn)生相同的隨機(jī)數(shù) |
| ROUND(x) | 返回一個(gè)對(duì)x的值進(jìn)行四舍五入后，最接近于X的整數(shù) |
| ROUND(x,y) | 返回一個(gè)對(duì)x的值進(jìn)行四舍五入后最接近X的值，并保留到小數(shù)點(diǎn)后面Y位 |
| TRUNCATE(x,y) | 返回?cái)?shù)字x截?cái)酁閥位小數(shù)的結(jié)果 |
| SQRT(x) | 返回x的平方根。當(dāng)X的值為負(fù)數(shù)時(shí)，返回NULL |
舉例：
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;
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、角度與弧度互換函數(shù)
| 函數(shù) | 用法 |
|---|---|
| RADIANS(x) | 將角度轉(zhuǎn)化為弧度，其中，參數(shù)x為角度值 |
| DEGREES(x) | 將弧度轉(zhuǎn)化為角度，其中，參數(shù)x為弧度值 |
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM DUAL;
3、三角函數(shù)
| 函數(shù) | 用法 |
|---|---|
| SIN(x) | 返回x的正弦值，其中，參數(shù)x為弧度值 |
| ASIN(x) | 返回x的反正弦值，即獲取正弦為x的值。如果x的值不在-1到1之間，則返回NULL |
| COS(x) | 返回x的余弦值，其中，參數(shù)x為弧度值 |
| ACOS(x) | 返回x的反余弦值，即獲取余弦為x的值。如果x的值不在-1到1之間，則返回NULL |
| TAN(x) | 返回x的正切值，其中，參數(shù)x為弧度值 |
| ATAN(x) | 返回x的反正切值，即返回正切值為x的值 |
| ATAN2(m,n) | 返回兩個(gè)參數(shù)的反正切值 |
| COT(x) | 返回x的余切值，其中，X為弧度值 |
舉例：
- ATAN2(M,N)函數(shù)返回兩個(gè)參數(shù)的反正切值。
- 與ATAN(X)函數(shù)相比，
ATAN2(M,N)需要兩個(gè)參數(shù)，例如有兩個(gè)點(diǎn)point(x1,y1)和point(x2,y2)，使用ATAN(X)函數(shù)計(jì)算反正切值為ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)計(jì)算反正切值則為ATAN2(y2-y1,x2-x1)。由使用方式可以看出，當(dāng)x2-x1等于0時(shí)，ATAN(X)函數(shù)會(huì)報(bào)錯(cuò)，而ATAN2(M,N)函數(shù)則仍然可以計(jì)算。 - ATAN2(M,N)函數(shù)的使用示例如下：
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1)) FROM DUAL;
4、指數(shù)與對(duì)數(shù)
| 函數(shù) | 用法 |
|---|---|
| POW(x,y)，POWER(X,Y) | 返回x的y次方 |
| EXP(X) | 返回e的X次方，其中e是一個(gè)常數(shù)，2.718281828459045 |
| LN(X)，LOG(X) | 返回以e為底的X的對(duì)數(shù)，當(dāng)X <= 0 時(shí)，返回的結(jié)果為NULL |
| LOG10(X) | 返回以10為底的X的對(duì)數(shù)，當(dāng)X <= 0 時(shí)，返回的結(jié)果為NULL |
| LOG2(X) | 返回以2為底的X的對(duì)數(shù)，當(dāng)X <= 0 時(shí)，返回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)
5、進(jìn)制間的轉(zhuǎn)換
| 函數(shù) | 用法 |
|---|---|
| BIN(x) | 返回x的二進(jìn)制編碼 |
| HEX(x) | 返回x的十六進(jìn)制編碼 |
| OCT(x) | 返回x的八進(jìn)制編碼 |
| CONV(x,f1,f2) | 返回f1進(jìn)制數(shù)變成f2進(jìn)制數(shù) |
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)
三、字符串函數(shù)
| 函數(shù) | 用法 |
|---|---|
| ASCII(S) | 返回字符串S中的第一個(gè)字符的ASCII碼值 |
| CHAR_LENGTH(s) | 返回字符串s的字符數(shù)。作用與CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字節(jié)數(shù)，和字符集有關(guān) |
| CONCAT(s1,s2,…,sn) | 連接s1,s2,…,sn為一個(gè)字符串 |
| CONCAT_WS(x, s1,s2,…,sn) | 同CONCAT(s1,s2,…)函數(shù)，但是每個(gè)字符串之間要加上x(chóng) |
| INSERT(str, idx, len, replacestr) | 將字符串str從第idx位置開(kāi)始，len個(gè)字符長(zhǎng)的子串替換為字符串replacestr |
| REPLACE(str, a, b) | 用字符串b替換字符串str中所有出現(xiàn)的字符串a(chǎn) |
| UPPER(s) 或 UCASE(s) | 將字符串s的所有字母轉(zhuǎn)成大寫(xiě)字母 |
| LOWER(s) 或LCASE(s) | 將字符串s的所有字母轉(zhuǎn)成小寫(xiě)字母 |
| LEFT(str,n) | 返回字符串str最左邊的n個(gè)字符 |
| RIGHT(str,n) | 返回字符串str最右邊的n個(gè)字符 |
| LPAD(str, len, pad) | 用字符串pad對(duì)str最左邊進(jìn)行填充，直到str的長(zhǎng)度為len個(gè)字符 |
| RPAD(str ,len, pad) | 用字符串pad對(duì)str最右邊進(jìn)行填充，直到str的長(zhǎng)度為len個(gè)字符 |
| LTRIM(s) | 去掉字符串s左側(cè)的空格 |
| RTRIM(s) | 去掉字符串s右側(cè)的空格 |
| TRIM(s) | 去掉字符串s開(kāi)始與結(jié)尾的空格 |
| TRIM(s1 FROM s) | 去掉字符串s開(kāi)始與結(jié)尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s開(kāi)始處的s1 |
| TRIM(TRAILING s1 FROM s) | 去掉字符串s結(jié)尾處的s1 |
| REPEAT(str, n) | 返回str重復(fù)n次的結(jié)果 |
| SPACE(n) | 返回n個(gè)空格 |
| STRCMP(s1,s2) | 比較字符串s1,s2的ASCII碼值的大小 |
| SUBSTR(s,index,len) | 返回從字符串s的index位置其len個(gè)字符，作用與SUBSTRING(s,n,len)、MID(s,n,len)相同 |
| LOCATE(substr,str) | 返回字符串substr在字符串str中首次出現(xiàn)的位置，作用與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在字符串列表中第一次出現(xiàn)的位置 |
| FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出現(xiàn)的位置。其中，字符串s2是一個(gè)以逗號(hào)分隔的字符串 |
| REVERSE(s) | 返回s反轉(zhuǎn)后的字符串 |
| NULLIF(value1,value2) | 比較兩個(gè)字符串，如果value1與value2相等，則返回NULL，否則返回value1 |
注意：MySQL中，字符串的位置是從1開(kāi)始的。
舉例：
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)
四、日期和時(shí)間函數(shù)
1、獲取日期、時(shí)間
| 函數(shù) | 用法 |
|---|---|
| CURDATE() ，CURRENT_DATE() | 返回當(dāng)前日期，只包含年、月、日 |
| CURTIME() ， CURRENT_TIME() | 返回當(dāng)前時(shí)間，只包含時(shí)、分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回當(dāng)前系統(tǒng)日期和時(shí)間 |
| UTC_DATE() | 返回UTC（世界標(biāo)準(zhǔn)時(shí)間）日期 |
| UTC_TIME() | 返回UTC（世界標(biāo)準(zhǔn)時(shí)間）時(shí)間 |
舉例：
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0 FROM DUAL;
2、日期與時(shí)間戳的轉(zhuǎn)換
| 函數(shù) | 用法 |
|---|---|
| UNIX_TIMESTAMP() | 以UNIX時(shí)間戳的形式返回當(dāng)前時(shí)間。SELECT UNIX_TIMESTAMP() ->1634348884 |
| UNIX_TIMESTAMP(date) | 將時(shí)間date以UNIX時(shí)間戳的形式返回。 |
| FROM_UNIXTIME(timestamp) | 將UNIX時(shí)間戳的時(shí)間轉(zhuǎn)換為普通格式的時(shí)間 |
舉例：
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)
3、獲取月份、星期、星期數(shù)、天數(shù)等函數(shù)
| 函數(shù) | 用法 |
|---|---|
| YEAR(date) / MONTH(date) / DAY(date) | 返回具體的日期值 |
| HOUR(time) / MINUTE(time) / SECOND(time) | 返回具體的時(shí)間值 |
| MONTHNAME(date) | 返回月份：January，… |
| DAYNAME(date) | 返回星期幾：MONDAY，TUESDAY…SUNDAY |
| WEEKDAY(date) | 返回周幾，注意，周1是0，周2是1，。。。周日是6 |
| QUARTER(date) | 返回日期對(duì)應(yīng)的季度，范圍為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、日期的操作函數(shù)
| 函數(shù) | 用法 |
|---|---|
| EXTRACT(type FROM date) | 返回指定日期中特定的部分，type指定返回的值 |
EXTRACT(type FROM date)函數(shù)中type的取值與含義：
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()), EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()) FROM DUAL;
5、時(shí)間和秒鐘轉(zhuǎn)換的函數(shù)
| 函數(shù) | 用法 |
|---|---|
| TIME_TO_SEC(time) | 將 time 轉(zhuǎn)化為秒并返回結(jié)果值。轉(zhuǎn)化的公式為：小時(shí)3600+分鐘60+秒 |
| SEC_TO_TIME(seconds) | 將 seconds 描述轉(zhuǎn)化為包含小時(shí)、分鐘和秒的時(shí)間 |
舉例：
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)
6、計(jì)算日期和時(shí)間的函數(shù)
第1組
| 函數(shù) | 用法 |
|---|---|
| DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) | 返回與給定日期時(shí)間相差I(lǐng)NTERVAL時(shí)間段的日期時(shí)間 |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) | 返回與date相差I(lǐng)NTERVAL時(shí)間間隔的日期 |
上述函數(shù)中type的取值
舉例：
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, #可以是負(fù)數(shù)
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要單引號(hào)
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組
| 函數(shù) | 用法 |
|---|---|
| ADDTIME(time1,time2) | 返回time1加上time2的時(shí)間。當(dāng)time2為一個(gè)數(shù)字時(shí)，代表的是秒，可以為負(fù)數(shù) |
| SUBTIME(time1,time2) | 返回time1減去time2后的時(shí)間。當(dāng)time2為一個(gè)數(shù)字時(shí)，代表的是秒，可以為負(fù)數(shù) |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期間隔天數(shù) |
| TIMEDIFF(time1, time2) | 返回time1 - time2的時(shí)間間隔 |
| FROM_DAYS(N) | 返回從0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距離0000年1月1日的天數(shù) |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 針對(duì)給定年份與所在年份中的天數(shù)返回一個(gè)日期 |
| MAKETIME(hour,minute,second) | 將給定的小時(shí)、分鐘和秒組合成時(shí)間并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的時(shí)間 |
舉例：
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)
舉例：查詢(xún) 7 天內(nèi)的新增用戶(hù)數(shù)有多少？
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
7、日期的格式化與解析
| 函數(shù) | 用法 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化時(shí)間time值 |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的顯示格式 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt對(duì)str進(jìn)行解析，解析為一個(gè)日期 |
上述非GET_FORMAT函數(shù)中fmt參數(shù)常用的格式符：
GET_FORMAT函數(shù)中date_type和format_type參數(shù)取值如下：
舉例：
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)
五、流程控制函數(shù)
流程處理函數(shù)可以根據(jù)不同的條件，執(zhí)行不同的處理流程，可以在SQL語(yǔ)句中實(shí)現(xiàn)不同的條件選擇。MySQL中的流程處理函數(shù)主要包括IF()、IFNULL()和CASE()函數(shù)。
| 函數(shù) | 用法 |
|---|---|
| IF(value,value1,value2) | 如果value的值為T(mén)RUE，返回value1，否則返回value2 |
| IFNULL(value1, value2) | 如果value1不為NULL，返回value1，否則返回value2 |
| CASE WHEN 條件1 THEN 結(jié)果1 WHEN 條件2 THEN 結(jié)果2 … [ELSE resultn] END | 相當(dāng)于Java的if…else if…else… |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相當(dāng)于Java的switch…case… |
SELECT IF(1 > 0,'正確','錯(cuò)誤') ->正確
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 '你是誰(shuí)'
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 '已發(fā)貨' WHEN 4 THEN '確認(rèn)收貨' ELSE '無(wú)效訂單' 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;
六、加密與解密函數(shù)
加密與解密函數(shù)主要用于對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行加密和解密處理，以防止數(shù)據(jù)被他人竊取。這些函數(shù)在保證數(shù)據(jù)庫(kù)安全時(shí)非常有用。
| 函數(shù) | 用法 |
|---|---|
| PASSWORD(str) | 返回字符串str的加密版本，41位長(zhǎng)的字符串。加密結(jié)果不可逆，常用于用戶(hù)的密碼加密 |
| MD5(str) | 返回字符串str的md5加密后的值，也是一種加密方式。若參數(shù)為NULL，則會(huì)返回NULL |
| SHA(str) | 從原明文密碼str計(jì)算并返回加密后的密碼字符串，當(dāng)參數(shù)為NULL時(shí)，返回NULL。SHA加密算法比MD5更加安全。 |
| ENCODE(value,password_seed) | 返回使用password_seed作為加密密碼加密value |
| DECODE(value,password_seed) | 返回使用password_seed作為加密密碼解密value |
可以看到，ENCODE(value,password_seed)函數(shù)與DECODE(value,password_seed)函數(shù)互為反函數(shù)。
舉例：
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)
七、MySQL信息函數(shù)
MySQL中內(nèi)置了一些可以查詢(xún)MySQL信息的函數(shù)，這些函數(shù)主要用于幫助數(shù)據(jù)庫(kù)開(kāi)發(fā)或運(yùn)維人員更好地對(duì)數(shù)據(jù)庫(kù)進(jìn)行維護(hù)工作。
| 函數(shù) | 用法 |
|---|---|
| VERSION() | 返回當(dāng)前MySQL的版本號(hào) |
| CONNECTION_ID() | 返回當(dāng)前MySQL服務(wù)器的連接數(shù) |
| DATABASE()，SCHEMA() | 返回MySQL命令行當(dāng)前所在的數(shù)據(jù)庫(kù) |
| USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() | 返回當(dāng)前連接MySQL的用戶(hù)名，返回結(jié)果格式為“主機(jī)名@用戶(hù)名” |
| CHARSET(value) | 返回字符串value自變量的字符集 |
| COLLATION(value) | 返回字符串value的比較規(guī)則 |
舉例：
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)
八、其他函數(shù)
MySQL中有些函數(shù)無(wú)法對(duì)其進(jìn)行具體的分類(lèi)，但是這些函數(shù)在MySQL的開(kāi)發(fā)和運(yùn)維過(guò)程中也是不容忽視的。
| 函數(shù) | 用法 |
|---|---|
| FORMAT(value,n) | 返回對(duì)數(shù)字value進(jìn)行格式化后的結(jié)果數(shù)據(jù)。n表示四舍五入后保留到小數(shù)點(diǎn)后n位 |
| CONV(value,from,to) | 將value的值進(jìn)行不同進(jìn)制之間的轉(zhuǎn)換 |
| INET_ATON(ipvalue) | 將以點(diǎn)分隔的IP地址轉(zhuǎn)化為一個(gè)數(shù)字 |
| INET_NTOA(value) | 將數(shù)字形式的IP地址轉(zhuǎn)化為以點(diǎn)分隔的IP地址 |
| BENCHMARK(n,expr) | 將表達(dá)式expr重復(fù)執(zhí)行n次。用于測(cè)試MySQL處理expr表達(dá)式所耗費(fèi)的時(shí)間 |
| CONVERT(value USING char_code) | 將value所使用的字符編碼修改為char_code |
舉例：
# 如果n的值小于或者等于0，則只保留整數(shù)部分 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”為例，計(jì)算方式為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)到此這篇關(guān)于全面詳解MySQL單行函數(shù)分析的文章就介紹到這了,更多相關(guān)MySQL單行函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
DDL數(shù)據(jù)庫(kù)與表的創(chuàng)建和管理深入講解使用教程
這篇文章主要介紹了DDL數(shù)據(jù)庫(kù)與表的創(chuàng)建和管理，系統(tǒng)架構(gòu)的層面來(lái)看，數(shù)據(jù)庫(kù)從大到小依次是數(shù)據(jù)庫(kù)服務(wù)器(上面安裝了DBMS和數(shù)據(jù)庫(kù))、數(shù)據(jù)庫(kù)(也稱(chēng)database或者schema)、數(shù)據(jù)表、數(shù)據(jù)表的行與列2023-04-04
這篇文章主要介紹了MySQL的表級(jí)鎖,行級(jí)鎖,排它鎖和共享鎖，事務(wù)隔離級(jí)別越高，為避免沖突所花費(fèi)的性能也就越多，即效率低,更多相關(guān)內(nèi)容需要的小伙伴可以參考與一下2022-07-07
詳解MySQL實(shí)時(shí)同步到Oracle解決方案
這篇文章主要介紹了詳解MySQL實(shí)時(shí)同步到Oracle解決方案，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
MySQL關(guān)系型數(shù)據(jù)庫(kù)事務(wù)的ACID特性與實(shí)現(xiàn)
這篇文章主要介紹了MySQL關(guān)系型數(shù)據(jù)庫(kù)事務(wù)的ACID特性與實(shí)現(xiàn)，ACID?是為保證事務(wù)transaction是正確可靠的，具備原子性、一致性、隔離性、持久性等特性2022-07-07
本文詳細(xì)介紹了MySQL中常見(jiàn)的四種JOIN算法：嵌套循環(huán)連接（NLJ）、索引嵌套循環(huán)連接（INLJ）、塊嵌套循環(huán)連接（BNLJ）和基于哈希的連接（HashJoin）,每種算法都有其適用場(chǎng)景和性能特點(diǎn),感興趣的朋友一起看看吧2025-02-02
這篇文章主要介紹了MySQL處理JSON常見(jiàn)函數(shù)的使用，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
MySQL?alter命令修改表語(yǔ)法實(shí)例詳解
這篇文章主要給大家介紹了關(guān)于MySQL?alter命令修改表語(yǔ)法實(shí)例詳解的相關(guān)資料,在MySQL中ALTER指令的作用是修改已存在的數(shù)據(jù)庫(kù)表的結(jié)構(gòu),文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-01-01
MySQL查詢(xún)表中某列字段相同的重復(fù)數(shù)據(jù)的方法
在數(shù)據(jù)庫(kù)查詢(xún)中,我們經(jīng)常需要查找表中某列中重復(fù)的數(shù)據(jù),本文將介紹如何使用 SQL 查詢(xún)語(yǔ)句來(lái)查找表中某列字段相同的重復(fù)數(shù)據(jù),幫助你快速定位重復(fù)數(shù)據(jù)問(wèn)題并進(jìn)行處理2023-08-08
Mysql5.7.14安裝配置方法操作圖文教程(密碼問(wèn)題解決辦法)
本篇文章主要涉及mysql5.7.14用以往的安裝方法安裝存在的密碼登錄不上，密碼失效等問(wèn)題的解決辦法，需要的朋友參考下吧2017-01-01
MySQL單表查詢(xún)常見(jiàn)操作實(shí)例總結(jié)
這篇文章主要介紹了MySQL單表查詢(xún)常見(jiàn)操作,結(jié)合實(shí)例形式總結(jié)分析了MySQL創(chuàng)建表以及條件查詢(xún)常見(jiàn)操作技巧,需要的朋友可以參考下2018-06-06