Mysql系列第十講常用的幾十個函式詳解-有解無憂

Mysql系列第十講

- MySQL 數值型函式
- - abs:求絕對值
  - sqrt:求二次方跟（開方）
  - mod:求余數
  - ceil和ceiling:向上取整
  - floor:向下取整
  - rand:生成一個亂數
  - round:四舍五入函式
  - sign:回傳引數的符號
  - pow 和 power:次方函式
  - sin:正弦函式
- MySQL 字串函式
- - length:回傳字串直接長度
  - concat:合并字串
  - insert:替換字串
  - lower:將字母轉換成小寫
  - upper:將字母轉換成大寫
  - left:從左側截取字串
  - right:從右側截取字串
  - trim:洗掉字串兩側空格
  - replace:字串替換
  - substr 和 substring:截取字串
  - reverse:反轉字串
- MySQL 日期和時間函式
- - curdate 和 current_date:兩個函式作用相同，回傳當前系統的日期值
  - curtime 和 current_time:獲取系統當前時間
  - now 和 sysdate:獲取當前時間日期
  - unix_timestamp:獲取UNIX時間戳
  - from_unixtime:時間戳轉日期
  - month:獲取指定日期的月份
  - monthname:獲取指定日期月份的英文名稱
  - dayname:獲取指定日期的星期名稱
  - dayofweek:獲取日期對應的周索引
  - week:獲取指定日期是一年中的第幾周
  - dayofyear:獲取指定日期在一年中的位置
  - dayofmonth:獲取指定日期在一個月的位置
  - year:獲取年份
  - time_to_sec:將時間轉換為秒值
  - sec_to_time:將秒值轉換為時間格式
  - date_add和adddate:向日期添加指定時間間隔
  - date_sub和subdate:日期減法運算
  - addtime:時間加法運算
  - subtime:時間減法運算
  - datediff:獲取兩個日期的時間間隔
  - date_format:格式化指定的日期
  - weekday:獲取指定日期在一周內的索引位置
- MySQL 聚合函式
- MySQL 流程控制函式
- - if:判斷
  - ifnull:判斷是否為空
  - case:搜索陳述句,類似于java中的if..else if..else
- 其他函式

MySQL 數值型函式

函式名稱	作用
abs	求絕對值
sqrt	求二次方根
mod	求余數
ceil 和 ceiling	兩個函式功能相同，都是回傳不小于引數的最小整數，即向上取整
floor	向下取整，回傳值轉化為一個BIGINT
rand	生成一個0~1之間的亂數，傳入整數引數是，用來產生重復序列
round	對所傳引數進行四舍五入
sign	回傳引數的符號
pow 和 power	兩個函式的功能相同，都是所傳引數的次方的結果值
sin	求正弦值
asin	求反正弦值，與函式 SIN 互為反函式
cos	求余弦值
acos	求反余弦值，與函式 COS 互為反函式
tan	求正切值
atan	求反正切值，與函式 TAN 互為反函式
cot	求余切值

abs:求絕對值

函式 ABS(x) 回傳 x 的絕對值，正數的絕對值是其本身，負數的絕對值為其相反數，0 的絕對值是 0，

mysql> select abs(5),abs(-2.4),abs(-24),abs(0);
+--------+-----------+----------+--------+
| abs(5) | abs(-2.4) | abs(-24) | abs(0) |
+--------+-----------+----------+--------+
|      5 |       2.4 |       24 |      0 |
+--------+-----------+----------+--------+
1 row in set (0.00 sec)

sqrt:求二次方跟（開方）

函式 SQRT(x) 回傳非負數 x 的二次方根，負數沒有平方根，回傳結果為 NULL，

mysql> select sqrt(25),sqrt(120),sqrt(-9);
+----------+--------------------+----------+
| sqrt(25) | sqrt(120)          | sqrt(-9) |
+----------+--------------------+----------+
|        5 | 10.954451150103322 |     NULL |
+----------+--------------------+----------+
1 row in set (0.00 sec)

mod:求余數

函式 MOD(x,y) 回傳 x 被 y 除后的余數，MOD() 對于帶有小數部分的數值也起作用，它回傳除法運算后的余數，

mysql> select mod(63,8),mod(120,10),mod(15.5,3);
+-----------+-------------+-------------+
| mod(63,8) | mod(120,10) | mod(15.5,3) |
+-----------+-------------+-------------+
|         7 |           0 |         0.5 |
+-----------+-------------+-------------+
1 row in set (0.00 sec)

ceil和ceiling:向上取整

函式 CEIL(x) 和 CEILING(x) 的意義相同，回傳不小于 x 的最小整數值，回傳值轉化為一個 BIGINT，

mysql> select ceil(-2.5),ceiling(2.5);
+------------+--------------+
| ceil(-2.5) | ceiling(2.5) |
+------------+--------------+
|         -2 |            3 |
+------------+--------------+
1 row in set (0.00 sec)

floor:向下取整

floor(x) 函式回傳小于 x 的最大整數值，

mysql> select floor(5),floor(5.66),floor(-4),floor(-4.66);
+----------+-------------+-----------+--------------+
| floor(5) | floor(5.66) | floor(-4) | floor(-4.66) |
+----------+-------------+-----------+--------------+
|        5 |           5 |        -4 |           -5 |
+----------+-------------+-----------+--------------+
1 row in set (0.00 sec)

rand:生成一個亂數

生成一個0~1之間的亂數，傳入整數引數是，用來產生重復序列

mysql> select rand(), rand(), rand();
+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand()             |
+--------------------+--------------------+--------------------+
| 0.5224735778965741 | 0.3678060549942833 | 0.2716095720153391 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> select rand(1),rand(2),rand(1);
+---------------------+--------------------+---------------------+
| rand(1)             | rand(2)            | rand(1)             |
+---------------------+--------------------+---------------------+
| 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 |
+---------------------+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select rand(1),rand(2),rand(1);
+---------------------+--------------------+---------------------+
| rand(1)             | rand(2)            | rand(1)             |
+---------------------+--------------------+---------------------+
| 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 |
+---------------------+--------------------+---------------------+
1 row in set (0.00 sec)

round:四舍五入函式

回傳最接近于引數 x 的整數；ROUND(x,y) 函式對引數x進行四舍五入的操作，回傳值保留小數點后面指定的y位，

mysql> select round(-6.6),round(-8.44),round(3.44);
+-------------+--------------+-------------+
| round(-6.6) | round(-8.44) | round(3.44) |
+-------------+--------------+-------------+
|          -7 |           -8 |           3 |
+-------------+--------------+-------------+
1 row in set (0.00 sec)

mysql> select round(-6.66,1),round(3.33,3),round(88.66,-1),round(88.46,-2);
+----------------+---------------+-----------------+-----------------+
| round(-6.66,1) | round(3.33,3) | round(88.66,-1) | round(88.46,-2) |
+----------------+---------------+-----------------+-----------------+
|           -6.7 |         3.330 |              90 |             100 |
+----------------+---------------+-----------------+-----------------+
1 row in set (0.00 sec)

sign:回傳引數的符號

回傳引數的符號，x 的值為負、零和正時回傳結果依次為 -1、0 和 1，

mysql> select sign(-6),sign(0),sign(34);
+----------+---------+----------+
| sign(-6) | sign(0) | sign(34) |
+----------+---------+----------+
|       -1 |       0 |        1 |
+----------+---------+----------+
1 row in set (0.00 sec)

pow 和 power:次方函式

POW(x,y) 函式和 POWER(x,y) 函式用于計算 x 的 y 次方，

mysql> select pow(5,-2),pow(10,3),pow(100,0),power(4,3),power(6,-3);
+-----------+-----------+------------+------------+----------------------+
| pow(5,-2) | pow(10,3) | pow(100,0) | power(4,3) | power(6,-3)          |
+-----------+-----------+------------+------------+----------------------+
|      0.04 |      1000 |          1 |         64 | 0.004629629629629629 |
+-----------+-----------+------------+------------+----------------------+
1 row in set (0.00 sec)

sin:正弦函式

SIN(x) 回傳 x 的正弦值，其中 x 為弧度值，

mysql> select sin(1),sin(0.5*pi()),pi();
+--------------------+---------------+----------+
| sin(1)             | sin(0.5*pi()) | pi()     |
+--------------------+---------------+----------+
| 0.8414709848078965 |             1 | 3.141593 |
+--------------------+---------------+----------+
1 row in set (0.00 sec)

注：PI() 函式回傳圓周率（3.141593）

其他幾個三角函式在此就不說了，有興趣的可以自己去練習一下，

MySQL 字串函式

函式名稱	作用
length	計算字串長度函式，回傳字串的位元組長度
concat	合并字串函式，回傳結果為連接引數產生的字串，引數可以使一個或多個
insert	替換字串函式
lower	將字串中的字母轉換為小寫
upper	將字串中的字母轉換為大寫
left	從左側字截取符串，回傳字串左邊的若干個字符
right	從右側字截取符串，回傳字串右邊的若干個字符
trim	洗掉字串左右兩側的空格
replace	字串替換函式，回傳替換后的新字串
substr 和 substring	截取字串，回傳從指定位置開始的指定長度的字符換
reverse	字串反轉（逆序）函式，回傳與原始字串順序相反的字串

length:回傳字串直接長度

回傳值為字串的位元組長度，使用 uft8（UNICODE 的一種變長字符編碼，又稱萬國碼）編碼字符集時，一個漢字是 3 個位元組，一個數字或字母是一個位元組，

mysql> select length('javacode2018'),length('路人甲Java'),length('路人');
+------------------------+-------------------------+------------------+
| length('javacode2018') | length('路人甲Java')    | length('路人')   |
+------------------------+-------------------------+------------------+
|                     12 |                      13 |                6 |
+------------------------+-------------------------+------------------+
1 row in set (0.00 sec)

concat:合并字串

CONCAT(sl，s2，…) 函式回傳結果為連接引數產生的字串，或許有一個或多個引數，

若有任何一個引數為 NULL，則回傳值為 NULL，若所有引數均為非二進制字串，則結果為非二進制字串，若自變數中含有任一二進制字串，則結果為一個二進制字串，

mysql> select concat('路人甲','java'),concat('路人甲',null,'java');
+----------------------------+---------------------------------+
| concat('路人甲','java')    | concat('路人甲',null,'java')    |
+----------------------------+---------------------------------+
| 路人甲java                 | NULL                            |
+----------------------------+---------------------------------+
1 row in set (0.00 sec)

insert:替換字串

INSERT(s1，x，len，s2) 回傳字串 s1，子字串起始于 x 位置，并且用 len 個字符長的字串代替 s2，

x的值從1開始，第一個字符的x=1，若 x 超過字串長度，則回傳值為原始字串，

假如 len 的長度大于其他字串的長度，則從位置 x 開始替換，

若任何一個引數為 NULL，則回傳值為 NULL，

mysql> select
    ->   insert('路人甲Java', 2, 4, '**') AS col1,
    ->   insert('路人甲Java', -1, 4,'**') AS col2,
    ->   insert('路人甲Java', 3, 20,'**') AS col3;
+---------+---------------+----------+
| col1    | col2          | col3     |
+---------+---------------+----------+
| 路**va  | 路人甲Java    | 路人**   |
+---------+---------------+----------+
1 row in set (0.00 sec)

lower:將字母轉換成小寫

LOWER(str) 可以將字串 str 中的字母字符全部轉換成小寫，

mysql> select lower('路人甲JAVA');
+------------------------+
| lower('路人甲JAVA')    |
+------------------------+
| 路人甲java             |
+------------------------+
1 row in set (0.00 sec)

upper:將字母轉換成大寫

UPPER(str) 可以將字串 str 中的字母字符全部轉換成大寫，

mysql> select upper('路人甲java');
+------------------------+
| upper('路人甲java')    |
+------------------------+
| 路人甲JAVA             |
+------------------------+
1 row in set (0.00 sec)

left:從左側截取字串

LEFT(s，n) 函式回傳字串 s 最左邊的 n 個字符，s=1表示第一個字符，

mysql> select left('路人甲JAVA',2),left('路人甲JAVA',10),left('路人甲JAVA',-1);
+-------------------------+--------------------------+--------------------------+
| left('路人甲JAVA',2)    | left('路人甲JAVA',10)    | left('路人甲JAVA',-1)    |
+-------------------------+--------------------------+--------------------------+
| 路人                    | 路人甲JAVA               |                          |
+-------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)

right:從右側截取字串

RIGHT(s，n) 函式回傳字串 s 最右邊的 n 個字符，

mysql> select right('路人甲JAVA',1),right('路人甲JAVA',10),right('路人甲JAVA',-1);
+--------------------------+---------------------------+---------------------------+
| right('路人甲JAVA',1)    | right('路人甲JAVA',10)    | right('路人甲JAVA',-1)    |
+--------------------------+---------------------------+---------------------------+
| A                        | 路人甲JAVA                |                           |
+--------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

trim:洗掉字串兩側空格

TRIM(s) 洗掉字串 s 兩側的空格，

mysql> select '[   路人甲Java   ]',concat('[',trim('   路人甲Java   '),']');
+-----------------------+---------------------------------------------+
| [   路人甲Java   ]    | concat('[',trim('   路人甲Java   '),']')    |
+-----------------------+---------------------------------------------+
| [   路人甲Java   ]    | [路人甲Java]                                |
+-----------------------+---------------------------------------------+
1 row in set (0.00 sec)

replace:字串替換

REPLACE(s，s1，s2) 使用字串 s2 替換字串 s 中所有的字串 s1，

substr 和 substring:截取字串

substr(str,pos)
substr(str from pos)
substr(str,pos,len)
substr(str from pos for len)
substr()是substring()的同義詞，
沒有len引數的形式是字串str從位置pos開始回傳一個子字串，
帶有len引數的形式是字串str從位置pos開始回傳長度為len的子字串，
使用FROM的形式是標準的SQL語法，
也可以對pos使用負值，在這種情況下，子字串的開頭是字串末尾的pos字符，而不是開頭，在這個函式的任何形式中pos可以使用負值，
對于所有形式的substring()，從中提取子串的字串中第一個字符的位置被認為是1，

/** 第三個字符之后的子字串：inese **/
SELECT substring('chinese', 3);
/** 倒數第三個字符之后的子字串：ese **/
SELECT substring('chinese', -3);
/** 第三個字符之后的兩個字符：in **/
SELECT substring('chinese', 3, 2);
/** 倒數第三個字符之后的兩個字符：es **/
SELECT substring('chinese', -3, 2);
/** 第三個字符之后的子字串：inese **/
SELECT substring('chinese' FROM 3);
/** 倒數第三個字符之后的子字串：ese **/
SELECT substring('chinese' FROM -3);
/** 第三個字符之后的兩個字符：in **/
SELECT substring('chinese' FROM 3 FOR 2);
/** 倒數第三個字符之后的兩個字符：es **/
SELECT substring('chinese' FROM -3 FOR 2);

reverse:反轉字串

REVERSE(s) 可以將字串 s 反轉，回傳的字串的順序和 s 字串的順序相反，

mysql> select reverse('路人甲Java');
+--------------------------+
| reverse('路人甲Java')    |
+--------------------------+
| avaJ甲人路               |
+--------------------------+
1 row in set (0.00 sec)

MySQL 日期和時間函式

函式名稱	作用
curdate 和 current_date	兩個函式作用相同，回傳當前系統的日期值
curtime 和 current_time	兩個函式作用相同，回傳當前系統的時間值
now 和 sysdate	兩個函式作用相同，回傳當前系統的日期和時間值
unix_timestamp	獲取UNIX時間戳函式，回傳一個以 UNIX 時間戳為基礎的無符號整數
from_unixtime	將 UNIX 時間戳轉換為時間格式，與UNIX_TIMESTAMP互為反函式
month	獲取指定日期中的月份
monthname	獲取指定日期中的月份英文名稱
dayname	獲取指定曰期對應的星期幾的英文名稱
dayofweek	獲取指定日期是一周中是第幾天，回傳值范圍是1~7,1=周日
week	獲取指定日期是一年中的第幾周，回傳值的范圍是否為 0?52 或 1?53
dayofyear	獲取指定曰期是一年中的第幾天，回傳值范圍是1~366
dayofmonth	獲取指定日期是一個月中是第幾天，回傳值范圍是1~31
year	獲取年份，回傳值范圍是 1970?2069
time_to_sec	將時間引數轉換為秒數
sec_to_time	將秒數轉換為時間，與TIME_TO_SEC 互為反函式
date_add 和 adddate	兩個函式功能相同，都是向日期添加指定的時間間隔
date_sub 和 subdate	兩個函式功能相同，都是向日期減去指定的時間間隔
addtime	時間加法運算，在原始時間上添加指定的時間
subtime	時間減法運算，在原始時間上減去指定的時間
datediff	獲取兩個日期之間間隔，回傳引數 1 減去引數 2 的值
date_format	格式化指定的日期，根據引數回傳指定格式的值
weekday	獲取指定日期在一周內的對應的作業日索引

curdate 和 current_date:兩個函式作用相同，回傳當前系統的日期值

CURDATE() 和 CURRENT_DATE() 函式的作用相同，將當前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值回傳，具體格式根據函式用在字串或數字語境中而定，回傳的date型別，

mysql> select curdate(),current_date(),current_date()+1;
+------------+----------------+------------------+
| curdate()  | current_date() | current_date()+1 |
+------------+----------------+------------------+
| 2019-09-17 | 2019-09-17     |         20190918 |
+------------+----------------+------------------+
1 row in set (0.00 sec)

curtime 和 current_time:獲取系統當前時間

CURTIME() 和 CURRENT_TIME() 函式的作用相同，將當前時間以“HH：MM：SS”或“HHMMSS”格式回傳，具體格式根據函式用在字串或數字語境中而定，回傳time型別，

mysql> select curtime(),current_time(),current_time()+1;
+-----------+----------------+------------------+
| curtime() | current_time() | current_time()+1 |
+-----------+----------------+------------------+
| 16:11:25  | 16:11:25       |           161126 |
+-----------+----------------+------------------+
1 row in set (0.00 sec)

now 和 sysdate:獲取當前時間日期

NOW() 和 SYSDATE() 函式的作用相同，都是回傳當前日期和時間值，格式為“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具體格式根據函式用在字串或數字語境中而定，回傳datetime型別，

mysql> select now(),sysdate();
+---------------------+---------------------+
| now()               | sysdate()           |
+---------------------+---------------------+
| 2019-09-17 16:13:28 | 2019-09-17 16:13:28 |
+---------------------+---------------------+
1 row in set (0.00 sec)

unix_timestamp:獲取UNIX時間戳

UNIX_TIMESTAMP(date) 若無引數呼叫，回傳一個無符號整數型別的 UNIX 時間戳（'1970-01-01 00:00:00’GMT之后的秒數），

mysql> select unix_timestamp(),unix_timestamp(now()),now(),unix_timestamp('2019-09-17 12:00:00');
+------------------+-----------------------+---------------------+---------------------------------------+
| unix_timestamp() | unix_timestamp(now()) | now()               | unix_timestamp('2019-09-17 12:00:00') |
+------------------+-----------------------+---------------------+---------------------------------------+
|       1568710893 |            1568710893 | 2019-09-17 17:01:33 |                            1568692800 |
+------------------+-----------------------+---------------------+---------------------------------------+
1 row in set (0.00 sec)

from_unixtime:時間戳轉日期

FROM_UNIXTIME(unix_timestamp[,format]) 函式把 UNIX 時間戳轉換為普通格式的日期時間值，與 UNIX_TIMESTAMP () 函式互為反函式，

有2個引數：

unix_timestamp：時間戳（秒）

format：要轉化的格式比如“”%Y-%m-%d“” 這樣格式化之后的時間就是 2017-11-30

可以有的形式：

格式	說明
%M	月名字(January～December)
%W	星期名字(Sunday～Saturday)
%D	有英語前綴的月份的日期(1st, 2nd, 3rd, 等等）
%Y	年, 數字, 4 位
%y	年, 數字, 2 位
%a	縮寫的星期名字(Sun～Sat)
%d	月份中的天數, 數字(00～31)
%e	月份中的天數, 數字(0～31)
%m	月, 數字(01～12)
%c	月, 數字(1～12)
%b	縮寫的月份名字(Jan～Dec)
%j	一年中的天數(001～366)
%H	小時(00～23)
%k	小時(0～23)
%h	小時(01～12)
%I	（i的大寫）小時(01～12)
%l（	L的小寫）小時(1～12)
%i	分鐘, 數字(00～59)
%r	時間,12 小時(hh:mm:ss [AP]M)
%T	時間,24 小時(hh:mm:ss)
%S	秒(00～59)
%s	秒(00～59)
%p	AM或PM
%W	一個星期中的天數英文名稱(Sunday~Saturday）
%w	一個星期中的天數(0=Sunday ～6=Saturday）
%U	星期(0～52), 這里星期天是星期的第一天
%u	星期(0～52), 這里星期一是星期的第一天
%%	輸出%

mysql> select from_unixtime(1568710866),from_unixtime(1568710866,'%Y-%m-%d %H:%h:%s');
+---------------------------+-----------------------------------------------+
| from_unixtime(1568710866) | from_unixtime(1568710866,'%Y-%m-%d %H:%h:%s') |
+---------------------------+-----------------------------------------------+
| 2019-09-17 17:01:06       | 2019-09-17 17:05:06                           |
+---------------------------+-----------------------------------------------+
1 row in set (0.00 sec)

month:獲取指定日期的月份

MONTH(date) 函式回傳指定 date 對應的月份，范圍為 1～12，

mysql> select month('2017-12-15'),month(now());
+---------------------+--------------+
| month('2017-12-15') | month(now()) |
+---------------------+--------------+
|                  12 |            9 |
+---------------------+--------------+
1 row in set (0.00 sec)

monthname:獲取指定日期月份的英文名稱

MONTHNAME(date) 函式回傳日期 date 對應月份的英文全名，

mysql> select monthname('2017-12-15'),monthname(now());
+-------------------------+------------------+
| monthname('2017-12-15') | monthname(now()) |
+-------------------------+------------------+
| December                | September        |
+-------------------------+------------------+
1 row in set (0.00 sec)

dayname:獲取指定日期的星期名稱

DAYNAME(date) 函式回傳 date 對應的作業日英文名稱，例如 Sunday、Monday 等，

mysql> select now(),dayname(now());
+---------------------+----------------+
| now()               | dayname(now()) |
+---------------------+----------------+
| 2019-09-17 17:13:08 | Tuesday        |
+---------------------+----------------+
1 row in set (0.00 sec)

dayofweek:獲取日期對應的周索引

DAYOFWEEK(d) 函式回傳 d 對應的一周中的索引（位置），1 表示周日，2 表示周一，……，7 表示周六，這些索引值對應于ODBC標準，

mysql> select now(),dayofweek(now());
+---------------------+------------------+
| now()               | dayofweek(now()) |
+---------------------+------------------+
| 2019-09-17 17:14:21 |                3 |
+---------------------+------------------+
1 row in set (0.00 sec)

week:獲取指定日期是一年中的第幾周

WEEK(date[,mode]) 函式計算日期 date 是一年中的第幾周，WEEK(date,mode) 函式允許指定星期是否起始于周榷訓周一，以及回傳值的范圍是否為 0～52 或 1～53，

WEEK函式接受兩個引數：

date是要獲取周數的日期，

mode是一個可選引數，用于確定周數計算的邏輯，它允許您指定本周是從星期一還是星期日開始，回傳的周數應在0到52之間或0到53之間，

如果忽略mode引數，默認情況下WEEK函式將使用default_week_format系統變數的值，

要獲取default_week_format變數的當前值，請使用SHOW VARIABLES陳述句如下：

mysql> SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set, 1 warning (0.00 sec)

在我們的服務器中，default_week_format的默認值為0，下表格說明了mode引數如何影響WEEK函式：

模式	一周的第一天	范圍
0	星期日	0-53
1	星期一	0-53
2	星期日	1-53
3	星期一	1-53
4	星期日	0-53
5	星期一	0-53
6	星期日	1-53
7	星期一	1-53

上表中“今年有4天以上”表示：

如果星期包含1月1日，并且在新的一年中有4天或更多天，那么這周是第1周，
否則，這一周的數字是前一年的最后一周，下周是第1周，

mysql> select now(),week(now());
+---------------------+-------------+
| now()               | week(now()) |
+---------------------+-------------+
| 2019-09-17 17:20:28 |          37 |
+---------------------+-------------+
1 row in set (0.00 sec)

dayofyear:獲取指定日期在一年中的位置

DAYOFYEAR(d) 函式回傳 d 是一年中的第幾天，范圍為 1～366，

mysql> select now(),dayofyear(now()),dayofyear('2019-01-01');
+---------------------+------------------+-------------------------+
| now()               | dayofyear(now()) | dayofyear('2019-01-01') |
+---------------------+------------------+-------------------------+
| 2019-09-17 17:22:00 |              260 |                       1 |
+---------------------+------------------+-------------------------+
1 row in set (0.00 sec)

dayofmonth:獲取指定日期在一個月的位置

DAYOFMONTH(d) 函式回傳 d 是一個月中的第幾天，范圍為 1～31，

mysql> select now(),dayofmonth(now()),dayofmonth('2019-01-01');
+---------------------+-------------------+--------------------------+
| now()               | dayofmonth(now()) | dayofmonth('2019-01-01') |
+---------------------+-------------------+--------------------------+
| 2019-09-17 17:23:09 |                17 |                        1 |
+---------------------+-------------------+--------------------------+
1 row in set (0.00 sec)

year:獲取年份

YEAR() 函式可以從指定日期值中來獲取年份值，

mysql> select now(),year(now()),year('2019-01-02');
+---------------------+-------------+--------------------+
| now()               | year(now()) | year('2019-01-02') |
+---------------------+-------------+--------------------+
| 2019-09-17 17:28:10 |        2019 |               2019 |
+---------------------+-------------+--------------------+
1 row in set (0.00 sec)

time_to_sec:將時間轉換為秒值

TIME_TO_SEC(time) 函式回傳將引數 time 轉換為秒數的時間值，轉換公式為“小時 ×3600+ 分鐘 ×60+ 秒”，

mysql> select time_to_sec('15:15:15'),now(),time_to_sec(now());
+-------------------------+---------------------+--------------------+
| time_to_sec('15:15:15') | now()               | time_to_sec(now()) |
+-------------------------+---------------------+--------------------+
|                   54915 | 2019-09-17 17:30:44 |              63044 |
+-------------------------+---------------------+--------------------+
1 row in set (0.00 sec)

sec_to_time:將秒值轉換為時間格式

SEC_TO_TIME(seconds) 函式回傳將引數 seconds 轉換為小時、分鐘和秒數的時間值，

mysql> select sec_to_time(100),sec_to_time(10000);
+------------------+--------------------+
| sec_to_time(100) | sec_to_time(10000) |
+------------------+--------------------+
| 00:01:40         | 02:46:40           |
+------------------+--------------------+
1 row in set (0.00 sec)

date_add和adddate:向日期添加指定時間間隔

DATE_ADD(date,INTERVAL expr type)

date：引數是合法的日期運算式，expr 引數是您希望添加的時間間隔，

type：引數可以是下列值
Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

mysql> select date_add('2019-01-01',INTERVAL 10 day),adddate('2019-01-01 16:00:00',interval 100 SECOND);
+----------------------------------------+----------------------------------------------------+
| date_add('2019-01-01',INTERVAL 10 day) | adddate('2019-01-01 16:00:00',interval 100 SECOND) |
+----------------------------------------+----------------------------------------------------+
| 2019-01-11                             | 2019-01-01 16:01:40                                |
+----------------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2019-01-01',INTERVAL -10 day),adddate('2019-01-01 16:00:00',interval -100 SECOND);
+-----------------------------------------+-----------------------------------------------------+
| date_add('2019-01-01',INTERVAL -10 day) | adddate('2019-01-01 16:00:00',interval -100 SECOND) |
+-----------------------------------------+-----------------------------------------------------+
| 2018-12-22                              | 2019-01-01 15:58:20                                 |
+-----------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)

date_sub和subdate:日期減法運算

DATE_SUB(date,INTERVAL expr type)

date：引數是合法的日期運算式，expr 引數是您希望添加的時間間隔，

type的型別和date_add中的type一樣，

mysql> select date_sub('2019-01-01',INTERVAL 10 day),subdate('2019-01-01 16:00:00',interval 100 SECOND);
+----------------------------------------+----------------------------------------------------+
| date_sub('2019-01-01',INTERVAL 10 day) | subdate('2019-01-01 16:00:00',interval 100 SECOND) |
+----------------------------------------+----------------------------------------------------+
| 2018-12-22                             | 2019-01-01 15:58:20                                |
+----------------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub('2019-01-01',INTERVAL -10 day),subdate('2019-01-01 16:00:00',interval -100 SECOND);
+-----------------------------------------+-----------------------------------------------------+
| date_sub('2019-01-01',INTERVAL -10 day) | subdate('2019-01-01 16:00:00',interval -100 SECOND) |
+-----------------------------------------+-----------------------------------------------------+
| 2019-01-11                              | 2019-01-01 16:01:40                                 |
+-----------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)

addtime:時間加法運算

ADDTIME(time,expr) 函式用于執行時間的加法運算，添加 expr 到 time 并回傳結果，

其中：time 是一個時間或日期時間運算式，expr 是一個時間運算式，

mysql> select addtime('2019-09-18 23:59:59','0:1:1'), addtime('10:30:59','5:10:37');
+----------------------------------------+-------------------------------+
| addtime('2019-09-18 23:59:59','0:1:1') | addtime('10:30:59','5:10:37') |
+----------------------------------------+-------------------------------+
| 2019-09-19 00:01:00                    | 15:41:36                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)

subtime:時間減法運算

SUBTIME(time,expr) 函式用于執行時間的減法運算，

函式回傳 time，expr 表示的值和格式 time 相同，time 是一個時間或日期時間運算式， expr 是一個時間，

mysql> select subtime('2019-09-18 23:59:59','0:1:1'),subtime('10:30:59','5:12:37');
+----------------------------------------+-------------------------------+
| subtime('2019-09-18 23:59:59','0:1:1') | subtime('10:30:59','5:12:37') |
+----------------------------------------+-------------------------------+
| 2019-09-18 23:58:58                    | 05:18:22                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)

datediff:獲取兩個日期的時間間隔

DATEDIFF(date1，date2) 回傳起始時間 date1 和結束時間 date2 之間的天數，date1 和 date2 為日期或 date-and-time 運算式，計算時只用到這些值的日期部分，

mysql> select datediff('2017-11-30','2017-11-29') as col1, datediff('2017-11-30','2017-12-15') as col2;
+------+------+
| col1 | col2 |
+------+------+
|    1 |  -15 |
+------+------+
1 row in set (0.00 sec)

date_format:格式化指定的日期

DATE_FORMAT(date，format) 函式是根據 format 指定的格式顯示 date 值，

DATE_FORMAT() 函式接受兩個引數：

date：是要格式化的有效日期值format：是由預定義的說明符組成的格式字串，每個說明符前面都有一個百分比字符(%)，

format：格式和上面的函式from_unixtime中的format一樣，可以參考上面的，

mysql> select date_format('2017-11-30','%Y%m%d') as col0,now() as col1, date_format(now(),'%Y%m%d%H%i%s') as col2;
+----------+---------------------+----------------+
| col0     | col1                | col2           |
+----------+---------------------+----------------+
| 20171130 | 2019-09-17 17:56:12 | 20190917175612 |
+----------+---------------------+----------------+
1 row in set (0.00 sec)

weekday:獲取指定日期在一周內的索引位置

WEEKDAY(date) 回傳date的星期索引(0=星期一，1=星期二, ……6= 星期天)，

mysql> select now(),weekday(now());
+---------------------+----------------+
| now()               | weekday(now()) |
+---------------------+----------------+
| 2019-09-17 18:01:34 |              1 |
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select now(),dayofweek(now());
+---------------------+------------------+
| now()               | dayofweek(now()) |
+---------------------+------------------+
| 2019-09-17 18:01:34 |                3 |
+---------------------+------------------+
1 row in set (0.00 sec)

MySQL 聚合函式

函式名稱	作用
max	查詢指定列的最大值
min	查詢指定列的最小值
count	統計查詢結果的行數
sum	求和，回傳指定列的總和
avg	求平均值，回傳指定列資料的平均值

MySQL 流程控制函式

函式名稱	作用
if	判斷，流程控制
ifnull	判斷是否為空
case	搜索陳述句

if:判斷

IF(expr,v1,v2)

當 expr 為真是回傳 v1 的值，否則回傳 v2

mysql> select if(1<2,1,0) c1,if(1>5,'√','×') c2,if(strcmp('abc','ab'),'yes','no') c3;
+----+----+-----+
| c1 | c2 | c3  |
+----+----+-----+
|  1 | ×  | yes |
+----+----+-----+
1 row in set (0.00 sec)

ifnull:判斷是否為空

IFNULL(v1,v2)：v1為慷訓傳v2，否則回傳v1，

mysql> select ifnull(null,'路人甲Java'),ifnull('非空','為空');
+------------------------------+---------------------------+
| ifnull(null,'路人甲Java')    | ifnull('非空','為空')     |
+------------------------------+---------------------------+
| 路人甲Java                   | 非空                      |
+------------------------------+---------------------------+
1 row in set (0.00 sec)

case:搜索陳述句,類似于java中的if…else if…else

類似于java中的if…else if…else

有2種寫法

方式1：

CASE  <運算式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;

方式2：

CASE
    WHEN <條件1> THEN <命令>
    WHEN <條件2> THEN <命令>
    ...
    ELSE commands
END CASE;

示例：

準備資料：

CREATE TABLE t_stu (
  id   INT AUTO_INCREMENT  COMMENT '編號',
  name VARCHAR(10) COMMENT '姓名',
  sex  TINYINT COMMENT '性別,0:未知,1:男,2:女',
  PRIMARY KEY (id)
) COMMENT '學生表';

insert into t_stu (name,sex) VALUES 
  ('張學友',1),
  ('劉德華',1),
  ('郭富城',1),
  ('蔡依林',2),
  ('xxx',0);
mysql> select * from t_stu;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 張學友    |    1 |
|  2 | 劉德華    |    1 |
|  3 | 郭富城    |    1 |
|  4 | 蔡依林    |    2 |
|  5 | xxx       |    0 |
+----+-----------+------+
5 rows in set (0.00 sec)

需求：查詢所有學生資訊，輸出：姓名，性別（男、女、未知），如下：

mysql> SELECT
      t.name 姓名,
      (CASE t.sex
       WHEN 1
         THEN '男'
       WHEN 2
         THEN '女'
       ELSE '未知' END) 性別
    FROM t_stu t;
+-----------+--------+
| 姓名      | 性別   |
+-----------+--------+
| 張學友    | 男     |
| 劉德華    | 男     |
| 郭富城    | 男     |
| 蔡依林    | 女     |
| xxx       | 未知   |
+-----------+--------+
5 rows in set (0.00 sec)

mysql> SELECT
    t.name          姓名,
    (CASE
    WHEN t.sex = 1
    THEN '男'
    WHEN t.sex = 2
    THEN '女'
    ELSE '未知' END) 性別
    FROM t_stu t;
+-----------+--------+
| 姓名      | 性別   |
+-----------+--------+
| 張學友    | 男     |
| 劉德華    | 男     |
| 郭富城    | 男     |
| 蔡依林    | 女     |
| xxx       | 未知   |
+-----------+--------+
5 rows in set (0.00 sec)

其他函式

函式名稱	作用
version	資料庫版本號
database	當前的資料庫
user	當前連接用戶
password	回傳字串密碼形式
md5	回傳字串的md5資料

mysql> SELECT version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.00 sec)

mysql> SELECT database();
+--------------+
| database()   |
+--------------+
| javacode2018 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT  password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

易兮科技

Java

18級大連海事大學在校生
網路工程 & 國際經濟與貿易雙學位
中山市易兮軟體開發有限公司創始人
易兮科技團隊負責人
Java語言愛好者

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/86099.html

標籤：AI

上一篇：SAP PO 訊息報文存盤詳解(SAP PO 訊息報文存盤詳解二)

下一篇：MySQL1045錯誤解決方法（ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using passwor:yes)）

Mysql系列第十講 常用的幾十個函式詳解