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 |
+-----------------+