MySQL數(shù)據(jù)庫中的函數(shù)怎樣使用？

函數(shù) 是指一段可以直接被另一段程序調(diào)用的程序或代碼。 也就意味著，這一段程序或代碼在MySQL中已經(jīng)給我們提供了，我們要做的就是在合適的業(yè)務(wù)場景調(diào)用對應(yīng)的函數(shù)完成對應(yīng)的業(yè)務(wù)需求即可。 那么，函數(shù)到底在哪兒使用呢?

我們先來看兩個(gè)場景：

1). 在企業(yè)的OA或其他的人力系統(tǒng)中，經(jīng)常會(huì)提供的有這樣一個(gè)功能，每一個(gè)員工登錄上來之后都能夠看到當(dāng)前員工入職的天數(shù)。而在數(shù)據(jù)庫中，存儲(chǔ)的都是入職日期，如2000-11-12，那如果快速計(jì)算出天數(shù)呢?

2). 在做報(bào)表這類的業(yè)務(wù)需求中,我們要展示出學(xué)員的分?jǐn)?shù)等級分布。而在數(shù)據(jù)庫中，存儲(chǔ)的是學(xué)生的分?jǐn)?shù)值，如98/75，如何快速判定分?jǐn)?shù)的等級呢?其實(shí)，上述的這一類的需求呢，我們通過MySQL中的函數(shù)都可以很方便的實(shí)現(xiàn)。

MySQL中的函數(shù)主要分為以下四類：字符串函數(shù)、數(shù)值函數(shù)、日期函數(shù)、流程函數(shù)。

字符串函數(shù)

MySQL中內(nèi)置了很多字符串函數(shù)，常用的幾個(gè)如下：

演示如下：

A. concat : 字符串拼接

select concat('Hello' , ' MySQL');

B. lower : 全部轉(zhuǎn)小寫

select lower('Hello');

C. upper : 全部轉(zhuǎn)大寫

select upper('Hello');

D. lpad : 左填充

select lpad('01', 5, '-');

E. rpad : 右填充

select rpad('01', 5, '-');

F. trim : 去除空格

select trim(' Hello MySQL ');

G. substring : 截取子字符串

select substring('Hello MySQL',1,5);

案例:

由于業(yè)務(wù)需求變更，企業(yè)員工的工號，統(tǒng)一為5位數(shù)，目前不足5位數(shù)的全部在前面補(bǔ)0。比如： 1號員工的工號應(yīng)該為00001。

update emp set workno = lpad(workno, 5, '0');

處理完畢后, 具體的數(shù)據(jù)為:

數(shù)值函數(shù)

常見的數(shù)值函數(shù)如下：

演示如下：

A. ceil：向上取整

select ceil(1.1);

B. floor：向下取整

select floor(1.9);

C. mod：取模

select mod(7,4); 1

D. rand：獲取隨機(jī)數(shù)

select rand();

E. round：四舍五入

select round(2.344,2);

案例：

通過數(shù)據(jù)庫的函數(shù)，生成一個(gè)六位數(shù)的隨機(jī)驗(yàn)證碼。

思路： 獲取隨機(jī)數(shù)可以通過rand()函數(shù)，但是獲取出來的隨機(jī)數(shù)是在0-1之間的，所以可以在其基礎(chǔ)上乘以1000000，然后舍棄小數(shù)部分，如果長度不足6位，補(bǔ)0。

select lpad(round(rand()*1000000 , 0), 6, '0');

日期函數(shù)

常見的日期函數(shù)如下：

演示如下：

A. curdate：當(dāng)前日期

select curdate();

B. curtime：當(dāng)前時(shí)間

select curtime();

C. now：當(dāng)前日期和時(shí)間

select now();

D. YEAR , MONTH , DAY：當(dāng)前年、月、日

select YEAR(now()); 
select MONTH(now()); 
select DAY(now());

E. date_add：增加指定的時(shí)間間隔

select date_add(now(), INTERVAL 70 YEAR );

F. datediff：獲取兩個(gè)日期相差的天數(shù)

select datediff('2021-10-01', '2021-12-01');

案例：

查詢所有員工的入職天數(shù)，并根據(jù)入職天數(shù)倒序排序。入職天數(shù)，就是通過當(dāng)前日期 - 入職日期，所以需要使用datediff函數(shù)來完成。

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by 
entrydays desc;

流程函數(shù)

流程函數(shù)也是很常用的一類函數(shù)，可以在SQL語句中實(shí)現(xiàn)條件篩選，從而提高語句的效率。

演示如下：

A. if

select if(false, 'Ok', 'Error');

B. ifnull

select ifnull('Ok','Default'); 
select ifnull('','Default'); 
select ifnull(null,'Default');

C. case when then else end

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

select
  name,
  ( case workaddress when '北京' then '一線城市' when '上海' then '一線城市' else
'二線城市' end ) as '工作地址'
from emp;

案例:

create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '數(shù)學(xué)',
english int comment '英語',
chinese int comment '語文'
) comment '學(xué)員成績表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95
), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

具體的SQL語句如下:

select
id,
name,
(case when math >= 85 then '優(yōu)秀' when math >=60 then '及格' else '不及格' end )
'數(shù)學(xué)',
(case when english >= 85 then '優(yōu)秀' when english >=60 then '及格' else '不及格'
end ) '英語',
(case when chinese >= 85 then '優(yōu)秀' when chinese >=60 then '及格' else '不及格'
end ) '語文'
from score;

MySQL的常見函數(shù)我們學(xué)習(xí)完了，那接下來，我們就來分析一下，在前面講到的兩個(gè)函數(shù)的案例場景，思考一下需要用到什么樣的函數(shù)來實(shí)現(xiàn)?

1). 數(shù)據(jù)庫中，存儲(chǔ)的是入職日期，如 2000-01-01，如何快速計(jì)算出入職天數(shù)呢? -------->

答案: datediff

2). 數(shù)據(jù)庫中，存儲(chǔ)的是學(xué)生的分?jǐn)?shù)值，如98、75，如何快速判定分?jǐn)?shù)的等級呢? ---------->

答案: case ... when ...