切換語言為:簡體

MySQL中常用的函式總結

  • 爱糖宝
  • 2024-07-10
  • 2070
  • 0
  • 0

前言

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")


MySQL中常用的函式總結

LOWER(str)

所有大寫字母都被轉爲了小寫

SELECT LOWER("TEst")


MySQL中常用的函式總結

UPPER(str)

所有的小寫字母都被轉爲了大寫

SELECT UPPER("test")


MySQL中常用的函式總結

LPAD(str,n,pad)

左補位了3個'-',以此達到長度5

SELECT LPAD("01",5,"-")


MySQL中常用的函式總結


RPAD(str,n,pad)

右補位了3個'-',以此達到長度5

SELECT RPAD("01",5,"-")


MySQL中常用的函式總結

TRIM(str)

字串的頭尾的空格被去掉

SELECT TRIM(' hello  mysql ')


MySQL中常用的函式總結

SUBSTRING(str,Start,len)

從第一個字元擷取到第五個字元

SELECT SUBSTRING('helloMysql',1,5)


MySQL中常用的函式總結

注意:此處擷取的索引是從1開始的


練習

由於業務需求變更,企業員工的工號,統一為5位數,目前不足5位數的全部在前面補0。比如:1號員工的工號應該為00001。

UPDATE tb_employee set WORK_NO=LPAD(WORK_NO,5,"0")


MySQL中常用的函式總結

MySQL中常用的函式總結

二、數值函式

函式 功能
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)


MySQL中常用的函式總結

FLOOR(x)

傳入該函式的值都被==向上取整==

SELECT FLOOR(1.3),FLOOR(1.6)


MySQL中常用的函式總結

MOD(x,y)

取模

SELECT MOD(3,4),MOD(7,2)


MySQL中常用的函式總結

RAND()

返回從0-1的隨機數

SELECT RAND()


MySQL中常用的函式總結

ROUND(x,y)

保留傳入引數的兩位小數(四捨五入)

SELECT ROUND(RAND(),2),ROUND(3.167,2)


MySQL中常用的函式總結

練習

透過資料庫中的函式,生成一個6位數的隨機驗證碼

SELECT LPAD(ROUND(RAND(),6)*1000000,6,"0")


MySQL中常用的函式總結

透過隨機函式生成一個數並且保留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()


MySQL中常用的函式總結

CURTIME()

獲取當前==時間==

SELECT CURRENT_TIME()


MySQL中常用的函式總結

NOW()

獲取當前的==日期和時間==

SELECT NOW()


MySQL中常用的函式總結

YEAR(date)

獲取日期中的年份

SELECT YEAR(CURDATE())


MySQL中常用的函式總結

MONTH(date)

獲取日期中的月份

SELECT CURDATE(),MONTH(CURDATE())


MySQL中常用的函式總結

DAY(date)

獲取日期中的日份

SELECT CURDATE(),DAY(CURDATE())


MySQL中常用的函式總結

DATE_ADD(date, INTERVAL expr type)

獲取間隔日期

SELECT  CURDATE(),DATE_ADD(CURDATE(), INTERVAL 5 YEAR),DATE_ADD(CURDATE(), INTERVAL 5 MONTH)


MySQL中常用的函式總結

DATEDIFF(date1,date2)

獲取兩個日期的間隔天數

SELECT  CURDATE(),DATE_ADD(CURDATE(), INTERVAL 5 YEAR),DATEDIFF(DATE_ADD(CURDATE(), INTERVAL 5 YEAR),CURDATE())


MySQL中常用的函式總結

練習

查詢所有員工的入職天數,並根據入職天數倒序排序

SELECT NAME ,DATEDIFF(CURDATE(),ENTRY_DATE) AS ENTRYDAY FROM tb_employee ORDER BY ENTRYDAY DESC


MySQL中常用的函式總結

四、流程函式

函式 功能
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')


MySQL中常用的函式總結

IFNULL(value1 , value2)

如果第一個引數不為空則返回第一個引數,如果第一個引數為空則返回第二個引數

SELECT IFNULL('a','b'),IFNULL(NULL,'c')


MySQL中常用的函式總結

CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END

查詢emp表的員工姓名和工作地址(北京/上海---->一線城市,其他---->二線城市)

SELECT NAME,
CASE WORK_ADDRESS  
WHEN '北京' THEN '一線城市' 
WHEN '上海' THEN '一線城市' 
ELSE '二線城市' END AS '城市'
FROM tb_employee


MySQL中常用的函式總結

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


MySQL中常用的函式總結

0則評論

您的電子郵件等資訊不會被公開,以下所有項目均必填

OK! You can skip this field.