前言
MySQL 中的函式是一種重用 SQL 語句邏輯的方法，可以讓開發者自定義並封裝一些常用的數據處理邏輯，以便在查詢、更新或刪除等操作過程中進行呼叫。MySQL 提供了多種型別的函式，包括標量函式（Scalar Functions）、聚合函式（Aggregate Functions）、表值函式（Table-Valued Functions）等。
測試資料
本文使用下面這張表進行測試
CREATE TABLE TB_EMPLOYEE ( ID INT COMMENT '編號', WORK_NO VARCHAR(10) COMMENT '工號', NAME VARCHAR(10) COMMENT '姓名', GENDER CHAR COMMENT '性別', AGE TINYINT UNSIGNED COMMENT '年齡', ID_CARD CHAR(18) COMMENT '身份證號', WORK_ADDRESS VARCHAR(50) COMMENT '工作地址', ENTRY_DATE DATE COMMENT '入職時間' ) COMMENT '員工表' [2024-07-09 11:15:45] 12ms 執行完畢 emps INSERT INTO TB_EMPLOYEE (ID, WORK_NO, NAME, GENDER, AGE, ID_CARD, WORK_ADDRESS, ENTRY_DATE) VALUES (1, '1', '柳巖', '女', 20, '123456789012345678', '北京', '2000-01-01'), (2, '2', '張無忌', '男', 18, '123456789012345670', '北京', '2005-09-01'), (3, '3', '韋一笑', '男', 38, '123456789012345670', '上海', '2005-08-01'), (4, '4', '趙敏', '女', 18, '123456789012345670', '北京', '2009-12-01'), (5, '5', '小昭', '女', 16, '123456789012345678', '上海', '2007-07-01'), (6, '6', '楊逍', '男', 28, '12345678901234567X', '北京', '2006-01-01'), (7, '7', '範瑤', '男', 40, '123456789012345670', '北京', '2005-05-01'), (8, '8', '黛綺絲', '女', 38, '123456789012345670', '天津', '2015-05-01'), (9, '9', '範涼涼', '女', 45, '123456789012345678', '北京', '2010-04-01'), (10, '10', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-01'), (11, '11', '張士誠', '男', 55, '123456789012345670', '江蘇', '2015-05-01'), (12, '12', '常遇春', '男', 32, '123456789012345670', '北京', '2004-02-01'), (13, '13', '張三丰', '男', 88, '123456789012345678', '江蘇', '2020-11-01'), (14, '14', '滅絕', '女', 65, '123456789012345670', '西安', '2019-05-01'), (15, '15', '胡青牛', '男', 70, '12345678901234567X', '西安', '2018-04-01'), (16, '16', '周芷若', '女', 18, NULL, '北京', '2012-06-01')
一、字串函式
| 函式 | 功能 |
|---|---|
| CONCAT(S1,S,..Sn) | 字串拼接，將S1，S2，..Sn拼接成一個字串 |
| LOWER(str) | 將字串str全部轉為小寫 |
| UPPER(str) | 將字串str全部轉為大寫 |
| LPAD(str,n,pad) | 左填充，用字串pad對str的左邊進行填充，達到n個字串長度 |
| RPAD(str,n,pad) | 右填充，用字串pad對str的右邊進行填充，達到n個字串長度 |
| TRIM(str) | 去掉字串頭部和尾部的空格 |
| SUBSTRING(str,Start,len) | 返回從字串str從start位置起的len個長度的字串 |
測試
CONCAT(S1,S,..Sn)
三個字串被拼接為一個字串
SELECT CONCAT("hello","mysql","test")
LOWER(str)
所有大寫字母都被轉爲了小寫
SELECT LOWER("TEst")
UPPER(str)
所有的小寫字母都被轉爲了大寫
SELECT UPPER("test")
LPAD(str,n,pad)
左補位了3個'-'，以此達到長度5
SELECT LPAD("01",5,"-")
RPAD(str,n,pad)
右補位了3個'-'，以此達到長度5
SELECT RPAD("01",5,"-")
TRIM(str)
字串的頭尾的空格被去掉
SELECT TRIM(' hello mysql ')
SUBSTRING(str,Start,len)
從第一個字元擷取到第五個字元
SELECT SUBSTRING('helloMysql',1,5)
注意：此處擷取的索引是從1開始的
練習
由於業務需求變更，企業員工的工號，統一為5位數，目前不足5位數的全部在前面補0。比如：1號員工的工號應該為00001。
UPDATE tb_employee set WORK_NO=LPAD(WORK_NO,5,"0")
二、數值函式
| 函式 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1內的隨機數 |
| ROUND(x,y) | 求引數x的四捨五入的值，保留y位小數 |
測試
CEIL(x)
傳入該函式的值都被==向上取整==
SELECT CEIL(1.3),CEIL(1.6)
FLOOR(x)
傳入該函式的值都被==向上取整==
SELECT FLOOR(1.3),FLOOR(1.6)
MOD(x,y)
取模
SELECT MOD(3,4),MOD(7,2)
RAND()
返回從0-1的隨機數
SELECT RAND()
ROUND(x,y)
保留傳入引數的兩位小數（四捨五入）
SELECT ROUND(RAND(),2),ROUND(3.167,2)
練習
透過資料庫中的函式，生成一個6位數的隨機驗證碼
SELECT LPAD(ROUND(RAND(),6)*1000000,6,"0")
透過隨機函式生成一個數並且保留6位小數，然後再乘以1000000，由於隨機會得到如0.012345的數，乘以1000000後仍然沒有6位數，所以再使用LPAD函式進行左補位，以此得到一個6位的隨機數。
三、日期函式
| 函式 | 功能 |
|---|---|
| CURDATE() | 返回當前日期 |
| CURTIME() | 返回當前時間 |
| NOW() | 返回當前日期和時間 |
| YEAR(date) | 獲取指定date的年份 |
| MONTH(date) | 獲取指定date的月份 |
| DAY(date) | 獲取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一個日期/時間值加上一個時間間隔expr後的時間值 |
| DATEDIFF(date1,date2) | 返回起始時間date1和結束時間date2之間的天數 |
測試
CURDATE()
獲取當前==日期==
SELECT CURDATE()
CURTIME()
獲取當前==時間==
SELECT CURRENT_TIME()
NOW()
獲取當前的==日期和時間==
SELECT NOW()
YEAR(date)
獲取日期中的年份
SELECT YEAR(CURDATE())
MONTH(date)
獲取日期中的月份
SELECT CURDATE(),MONTH(CURDATE())
DAY(date)
獲取日期中的日份
SELECT CURDATE(),DAY(CURDATE())
DATE_ADD(date, INTERVAL expr type)
獲取間隔日期
SELECT CURDATE(),DATE_ADD(CURDATE(), INTERVAL 5 YEAR),DATE_ADD(CURDATE(), INTERVAL 5 MONTH)
DATEDIFF(date1,date2)
獲取兩個日期的間隔天數
SELECT CURDATE(),DATE_ADD(CURDATE(), INTERVAL 5 YEAR),DATEDIFF(DATE_ADD(CURDATE(), INTERVAL 5 YEAR),CURDATE())
練習
查詢所有員工的入職天數，並根據入職天數倒序排序
SELECT NAME ,DATEDIFF(CURDATE(),ENTRY_DATE) AS ENTRYDAY FROM tb_employee ORDER BY ENTRYDAY DESC
四、流程函式
| 函式 | 功能 |
|---|---|
| IF(value ,t, f) | 如果value為true，則返回t，否則返回f |
| IFNULL(value1 , value2) | 如果value1不為空，返回value1，否則返回value |
| CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END | 如果val1為true，返回res1，...否則返回default預設值 |
| CASE [ expr ] WHEN [val1 ] THEN [res1] ... ELSE [ default ] END | 如果expr的值等於vall，返回res1，...否則返回default預設值 |
測試
IF(value ,t, f)
如果第一個引數為TRUE則返回第二個引數，如果第一個引數為FALSE則返回第三個引數
SELECT IF(TRUE,'a','b'),IF(FALSE,'a','b')
IFNULL(value1 , value2)
如果第一個引數不為空則返回第一個引數，如果第一個引數為空則返回第二個引數
SELECT IFNULL('a','b'),IFNULL(NULL,'c')
CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END
查詢emp表的員工姓名和工作地址（北京/上海---->一線城市，其他---->二線城市)
SELECT NAME, CASE WORK_ADDRESS WHEN '北京' THEN '一線城市' WHEN '上海' THEN '一線城市' ELSE '二線城市' END AS '城市' FROM tb_employee
CASE [ expr ] WHEN [val1 ] THEN [res1] ... ELSE [ default ] END
統計班級各個學員的成績，展示的規則如下：>=85，展示優秀>=60，展示及格否則，展示不及格
這個案例需要以下這張表
CREATE TABLE score( id int comment 'ID', name VARCHAR(20) comment '姓名', math int comment '數學', english int comment '英語', chinese int comment '語文' ) comment '學員成績表'; insert into score (id, name, math, english, chinese) values (1,'Tom',67,88,95), (2,'Rose',23,66,90), (3,'Jack',56,98,76);
SELECT name, (CASE WHEN math >=85 THEN '優秀' WHEN math>=60 THEN '及格' ELSE '不及格' END) AS '數學', (CASE WHEN english >=85 THEN '優秀' WHEN english>=60 THEN '及格' ELSE '不及格'END) AS '英語', (CASE WHEN chinese >=85 THEN '優秀' WHEN chinese>=60 THEN '及格' ELSE '不及格'END) AS '語文' FROM score
