MYSQL函數用法

MYSQL函數用法

#以下函數均已MYSQL版本號8.0.16為主，其他版本可能有兼容性的問題

8.0.11以後不在支持password函數，點擊以下連結查看8.0手冊

在MySQL 8.0.11中刪除了此功能。PASSWORD(*str*)

---

MD5加密函數

#計算字符串的MD5 128位校驗和。該值以32個十六進制數字的字符串形式返回
SELECT MD5('ohohhotdog');
>>>
+----------------------------------+
| MD5('ohohhotdog')                |
+----------------------------------+
| 3095b0c7a613c1bf544062e8ebd6e18a |
+----------------------------------+
#對數據表內容進行加密
#例如在原有表中:
>>>
emp:
+----+--------+-----------+------+------------+-------+------+--------+
| id | name   | job       | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+-----------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理      | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理      |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員      |    2 | 2011-06-17 | 22000 |  100 |      1 |
|  4 | 未央   | 職員      |    2 | 2013-06-02 | 21000 |   50 |      2 |
|  5 | 蔡八   | 實習生    |    3 | 2018-06-02 | 11000 |    0 |      3 |
|  6 | 閃哥   | 實習生    |    3 | 2018-08-02 | 11000 |    0 |      4 |
+----+--------+-----------+------+------------+-------+------+--------+
#以表的name為對象
SELECT MD5 (name) FROM emp;
>>>
+----------------------------------+
| MD5 (name)                       |
+----------------------------------+
| fb8c5b003eaf0ccb754dcc6d080719e1 |<<<伊澤
| f596ce85d5cc0ef121a9f2c210ad10bb |
| 5f38afb6af2106cff058bf3c32ec940d |
| ac4ad8b098b90cd306ce1f29afd31daa |
| 407ab4b5b488c395c8a202f47e926bc6 |
| fb937116e6851d6e9affb0187b50b22a |
+----------------------------------+

聚合函數

#AVG求表中sal的平均值
SELECT AVG(sal) FROM emp;
>>>
+----------+
| AVG(sal) |
+----------+
|    26500 |
+----------+
#COUNT求表中人數
SELECT COUNT(*) FROM emp;
>>>
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
#MIN查找表中最低工資 //改成MAX為最大工資
SELECT MIN(sal) FROM emp;
>>>
+----------+
| MIN(sal) |
+----------+
|    11000 |
+----------+
#查總和
SELECT SUM(sal) FROM emp;
>>>
+----------+
| SUM(sal) |
+----------+
|   159000 |
+----------+

數學函數

#0~1隨機數
SELECT RAND();
>>>
+---------------------+
| RAND()              |
+---------------------+
| 0.13509207790260397 |
+---------------------+
#絕對值
SELECT ABS(-1);
>>>
+---------+
| ABS(-1) |
+---------+
|       1 |
+---------+
#取餘
| MOD(11,5) |
+-----------+
|         1 |
+-----------+
#向上取整
SELECT CEILING(11.5);
>>>
+---------------+
| CEILING(11.5) |
+---------------+
|            12 |
+---------------+
#向下取整
SELECT FLOOR(11.5);
+-------------+
| FLOOR(11.5) |
+-------------+
|          11 |
+-------------+
#四捨五入
SELECT ROUND(11.7);
>>>
+-------------+
| ROUND(11.7) |
+-------------+
|          12 |
+-------------+
#保留小數第2位
SELECT ROUND(11.21355,2);
>>>
+-------------------+
| ROUND(11.21355,2) |
+-------------------+
|             11.21 |
+-------------------+
#求()內最小值
SELECT LEAST(22,33,111,1,223);
+------------------------+
| LEAST(22,33,111,1,223) |
+------------------------+
|                      1 |
+------------------------+

字符函數

#字串長度
SELECT LENGTH('ohohhotdog');
>>>
+----------------------+
| LENGTH('ohohhotdog') |
+----------------------+
|                   10 |
+----------------------+
#返回字串dog的位置
SELECT INSTR('ohohhotdog','dog');
>>>
+---------------------------+
| INSTR('ohohhotdog','dog') |
+---------------------------+
|                         8 |
+---------------------------+
#1為起始位置3為終點位置替換成111
 SELECT INSERT('ohohhotdog',1,3,'111');
 >>>
+--------------------------------+
| INSERT('ohohhotdog',1,3,'111') |
+--------------------------------+
| 111hhotdog                     |
+--------------------------------+
#欲替換對象dog,新對象cat
SELECT REPLACE('ohohhotdog','dog','cat');
+-----------------------------------+
| REPLACE('ohohhotdog','dog','cat') |
+-----------------------------------+
| ohohhotcat                        |
+-----------------------------------+
#左邊過來6位
SELECT LEFT('ohohhotdog',6);
>>>
+----------------------+
| LEFT('ohohhotdog',6) |
+----------------------+
| ohohho               |
+----------------------+
#右邊過來6位
SELECT RIGHT('ohohhotdog',6);
>>>
+-----------------------+
| RIGHT('ohohhotdog',6) |
+-----------------------+
| hotdog                |
+-----------------------+

日期函數

#當前日期跟時間
SELECT NOW();
>>>
+---------------------+
| NOW()               |
+---------------------+
| 2019-06-01 16:11:52 |
+---------------------+
#當前日期
SELECT CURDATE();
>>>
+------------+
| CURDATE()  |
+------------+
| 2019-06-01 |
+------------+
#當前時間
SELECT CURTIME();
>>>
+-----------+
| CURTIME() |
+-----------+
| 16:13:43  |
+-----------+
#獲取年
SELECT YEAR('1832-08-10 11:22:33');
>>>
+-----------------------------+
| YEAR('1832-08-10 11:22:33') |
+-----------------------------+
|                        1832 |
+-----------------------------+
 SELECT YEAR(NOW());
 >>>
+-------------+
| YEAR(NOW()) |
+-------------+
|        2019 |
+-------------+

#流程控制函數

#100是否等於10*2
SELECT IF(100=10*2,'等於','不等於');
>>>
+-----------------------------------+
| IF(100=10*2,'等於','不等於')      |
+-----------------------------------+
| 不等於                            |
+-----------------------------------+
#3是否為null式的話返回為null,不是返回3
SELECT IFNULL(3,'為null');
+---------------------+
| IFNULL(3,'為null')  |
+---------------------+
| 3                   |
+---------------------+
#相等返回NULL不相等返回第一個值
 SELECT NULLIF(100,100);
 >>>
+-----------------+
| NULLIF(100,100) |
+-----------------+
|            NULL |
+-----------------+