5 分鐘快速掌握 MySQL 函數(shù)

目錄

1.MySQL中關(guān)于函數(shù)的說(shuō)明

• 概念：類似java、python中的方法，將一組邏輯語(yǔ)句封裝在方法體中，對(duì)外暴露方法名；
• 好處：Ⅰ隱藏了實(shí)現(xiàn)細(xì)節(jié)；Ⅱ提高代碼的重用性；
• 調(diào)用：select 函數(shù)名(實(shí)參列表) [from 表]；
• 特點(diǎn)：Ⅰ叫什么(函數(shù)名)；Ⅱ干什么(函數(shù)功能)；
• 分類：Ⅰ單行函數(shù)；Ⅱ分組函數(shù)；
• 什么是單行函數(shù)：作用于表中的每一行記錄，一條記錄出來(lái)一個(gè)結(jié)果；
• 什么是聚合函數(shù)：作用于一行或者多行，最終返回一個(gè)結(jié)果；

2.單行函數(shù)分類

• 字符函數(shù)；
• 數(shù)學(xué)函數(shù)；
• 日期函數(shù)；
• 其他函數(shù)；
• 流程控制函數(shù)；

3.字符函數(shù)

1）length(str)：獲取參數(shù)值的字節(jié)個(gè)數(shù)；

• 對(duì)于utf8字符集來(lái)說(shuō)，一個(gè)英文占1個(gè)字節(jié)；一個(gè)中文占3個(gè)字節(jié)；
• 對(duì)于gbk字符集來(lái)說(shuō)，一個(gè)英文占1個(gè)字節(jié)；一個(gè)中文占2個(gè)字節(jié)；

操作如下 ：

2） concat(str1,str2,...)：拼接字符串；

操作如下：

3）upper(str)：將字符中的所有字母變?yōu)榇髮?xiě)；

操作如下：

4）lower(str)：將字符中所有字母變?yōu)樾?xiě)；

操作如下：

5）substr(str,start,len)：從start位置開(kāi)始截取字符串，len表示要截取的長(zhǎng)度；

• 沒(méi)有指定len長(zhǎng)度：表示從start開(kāi)始起，截取到字符串末尾。
• 指定了len長(zhǎng)度：表示從start開(kāi)始起，截取len個(gè)長(zhǎng)度。

操作如下：

6）instr(str,要查找的子串)：返回子串第一次出現(xiàn)的索引，如果找不到，返回0；

• 當(dāng)查找的子串存在于字符串中：返回該子串在字符串中【第一次】出現(xiàn)的索引。
• 當(dāng)查找的子串不在字符串中：返回0。

操作如下：

7）trim(str)：去掉字符串前后的空格；

• 該函數(shù)只能去掉字符串前后的空格，不能去掉字符串中間的空格。

操作如下：

8）lpad(str,len,填充字符)：用指定的字符，實(shí)現(xiàn)對(duì)字符串左填充指定長(zhǎng)度；

操作如下：

9）rpad(str,len,填充字符)：用指定的字符，實(shí)現(xiàn)對(duì)字符串右填充指定長(zhǎng)度；

操作如下：

10） replace(str,子串,另一個(gè)字符串)：將字符串str中的字串，替換為另一個(gè)字符串；

操作如下：

4.數(shù)學(xué)函數(shù)

1）round(x,[保留的位數(shù)])：四舍五入；

• 當(dāng)對(duì)正數(shù)進(jìn)行四舍五入：按照正常的計(jì)算方式，四舍五入即可。
• 當(dāng)對(duì)負(fù)數(shù)進(jìn)行四舍五入：先把符號(hào)丟到一邊，對(duì)去掉負(fù)號(hào)后的正數(shù)進(jìn)行四舍五入，完成以后，再把這個(gè)負(fù)號(hào)，補(bǔ)上即可。

操作如下：

2）ceil(x)：向上取整，返回>=該參數(shù)的最小整數(shù)。(天花板函數(shù))

• 天花板函數(shù)，在excel，python中均存在這個(gè)函數(shù)。你就想象一下你家的天花板，把這個(gè)數(shù)字丟到天花板上，求的是大于等于這個(gè)數(shù)字的最小整數(shù)。

操作如下：

3）floor(x)：向下取整，返回<=該參數(shù)的最大整數(shù)。（地板函數(shù)）

• 地板函數(shù)：在excel，python中均存在這個(gè)函數(shù)。你就想象一下你家的地板，把這個(gè)數(shù)字丟到地板上，求的是小于等于這個(gè)數(shù)字的最大整數(shù)。

操作如下：

4）truncate(x,D)：截?cái)啵?/h4>

• truncate函數(shù)，也是在excel，python中均存在，含義基本都是一致的。但是這個(gè)函數(shù)理解起來(lái)有點(diǎn)難，我這里帶著大家好好學(xué)習(xí)一下。

理解如下：

"參考下面的示例圖，體會(huì)如下文字"
1）D是正數(shù)，操作的是小數(shù)點(diǎn)右側(cè)的小數(shù)部分。?
?? D=1，直接從第1個(gè)位置處，砍掉后面的部分。
?? D=2，直接從第2個(gè)位置處，砍掉后面的部分。
???......
???
2）D是0，直接去掉小數(shù)部分。?

3）D是負(fù)數(shù)，操作的是小數(shù)點(diǎn)左側(cè)的整數(shù)部分。
???D=-1，直接從-1位置處，先砍掉后面的小數(shù)部分，
???并且"從當(dāng)前位置起(包括當(dāng)前位置)，后面整數(shù)部分替換為0"。
???D=-2，直接從-2位置處，先砍掉后面的小數(shù)部分，
???并且"從當(dāng)前位置起(包括當(dāng)前位置)，后面整數(shù)部分替換為0"。

示例圖：

操作如下：

5）mod(被除數(shù),除數(shù))：取余；

• 當(dāng)被除數(shù)為正數(shù)，結(jié)果就是正數(shù)。
• 當(dāng)被除數(shù)為負(fù)數(shù)，結(jié)果就是負(fù)數(shù)。

操作如下：

5.日期時(shí)間函數(shù)

• 日期的含義：指的是我們常說(shuō)的年、月、日。
• 時(shí)間的含義：指的是我們常說(shuō)的時(shí)、分、秒。

在講述下面函數(shù)之前，我們先補(bǔ)充一個(gè)知識(shí)，不同時(shí)間格式符表示什么含義呢？

1）now()：返回系統(tǒng)當(dāng)前的日期和時(shí)間；

操作如下：

2）curdate()：只返回系統(tǒng)當(dāng)前的日期，不包含時(shí)間；

操作如下：

3）curtime()：只返回系統(tǒng)當(dāng)前的時(shí)間，不包含日期；

操作如下：

4）獲取日期和時(shí)間中年、月、日、時(shí)、分、秒；

• 獲取年份：year()；
• 獲取月份：month()；
• 獲取日：day()；
• 獲取小時(shí)：hour()；
• 獲取分鐘：minute()；
• 獲取秒數(shù)：second()；

5）weekofyear()：獲取當(dāng)前時(shí)刻所屬的周數(shù)；

操作如下：

6）quarter()：獲取當(dāng)前時(shí)刻所屬的季度；

操作如下：

7）str_to_date()：將日期格式的字符串，轉(zhuǎn)換成指定格式的日期；

操作如下：

8）date_format()：將日期轉(zhuǎn)換成日期字符串；

• %Y-%m-%d返回的月份是01,02...這樣的格式。
• %Y-%c-%d返回的月份是1,2...這樣的格式。

操作如下：

9）date_add() + interval：向前、向后偏移日期和時(shí)間；

操作如下：

10）last_day()：提取某個(gè)月最后一天的日期；

操作如下：

11）datediff(end_date,start_date)：計(jì)算兩個(gè)時(shí)間相差的天數(shù)；

操作如下：

12）timestampdiff(unit,start_date,end_date)：計(jì)算兩個(gè)時(shí)間返回的年/月/天數(shù)；

unit參數(shù)是確定(start_date,end_date)結(jié)果的單位，表示為整數(shù)，以下是有效單位：

• year：年份
• month：月份
• day：天
• hour：小時(shí)
• minute 分鐘
• second：秒
• microsecond：微秒
• week：周數(shù)
• quarter：季度
• YEAR：年份

操作如下：

6.其它常用系統(tǒng)函數(shù)

7.流程控制函數(shù)

1）if函數(shù)：實(shí)現(xiàn)if-else的效果；

2）ifnull函數(shù)：判斷值是否為null，是null用指定值填充；

3）case...when函數(shù)的三種用法；

case ... when共有三種用法，我相信自己的總結(jié)會(huì)相當(dāng)全面。希望大家一定要好好研究這幾種用法，都是很有用的。

• 等值判斷：類似于java中switch case的效果；
• 區(qū)間判斷：類似于python中if-elif-else的效果；
• case ... when和聚合函數(shù)聯(lián)用；

① case ... when用作等值判斷的語(yǔ)法格式；

case?要判斷的字段或表達(dá)式
when?常量1?then?要顯示的值1或語(yǔ)句1
when?常量2?then?要顯示的值2或語(yǔ)句2
...
else?要顯示的值n或語(yǔ)句n
end

操作如下：

② case ... when用作區(qū)間判斷的語(yǔ)法格式；

case?
when?條件1?then?要顯示的值1或語(yǔ)句1
when?條件2?then?要顯示的值2或語(yǔ)句2
...
else?要顯示的值n或語(yǔ)句n
end

操作如下：

③ ?case...when與聚合函數(shù)的聯(lián)用

利用上述原始表，完成如下問(wèn)題：

-- 18、查詢各科成績(jī)最高分、最低分和平均分，以如下形式顯示：
--?課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
--?及格為：>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90

操作如下：

select?sc.c,cname,
max(score)?最高分,min(score)?最低分,avg(score)?平均分,
sum(case?when?score>60?then?1?else?0?end)/count(*)?及格率,
sum(case?when?score>=70?and?score<80?then?1?else?0?end)/count(*)?中等率,
sum(case?when?score>=80?and?score<90?then?1?else?0?end)/count(*)?優(yōu)良率,
sum(case?when?score>=90?then?1?else?0?end)/count(*)?優(yōu)秀率
from?sc?left?join?course?
on?sc.c?=?course.c
group?by?sc.c;

結(jié)果如下：

8.聚合函數(shù)

1）聚合函數(shù)的功能和分類；

① 聚合函數(shù)的功能；

用作統(tǒng)計(jì)使用，又稱為聚合函數(shù)或統(tǒng)計(jì)函數(shù)或組函數(shù)。

② 聚合函數(shù)的分類；

• sum 求和
• avg 平均值
• max 最大值
• min 最小值
• count 計(jì)算個(gè)數(shù)

2）聚合函數(shù)的簡(jiǎn)單使用

3）五個(gè)聚合函數(shù)中傳入的參數(shù)，所支持的數(shù)據(jù)類型有哪些？

mysql不是強(qiáng)類型的編程語(yǔ)言。也就是說(shuō)，有些語(yǔ)句執(zhí)行結(jié)果可能不報(bào)錯(cuò)，但是執(zhí)行結(jié)果無(wú)實(shí)際意義，因此，我們也認(rèn)為是不正確的。

① 測(cè)試數(shù)據(jù)；

"建表語(yǔ)句"
create?table?test(
?id?int?primary?key?auto_increment,
?name?varchar(20)?not?null,
?sal?int,
?birth?date)charset=utf8;

"插入數(shù)據(jù)"
?insert?into?test(name,sal,birth)?values?
?("Zoo",6500,'1993.3.20'),
?("Hobby",4000,'1997.6.10'),
?("Aline",5500,'2000.5.1'),
?("Bob",10000,'2008.10.1');

② sum()函數(shù)和avg()函數(shù)：傳入整型/小數(shù)類型才有意義；

結(jié)論如下：

• sum()函數(shù)和avg()函數(shù)對(duì)于字符串類型、日期/時(shí)間類型的計(jì)算都沒(méi)有太大意義。因此，sum()函數(shù)和avg()函數(shù)，我們只用來(lái)對(duì)小數(shù)類型和整型進(jìn)行求和。

③ max()函數(shù)和min()函數(shù)：傳入整型/小數(shù)類型、日期/時(shí)間類型意義較大；

結(jié)論如下：

• max()和min()中傳入的是"整型/小數(shù)類型"，計(jì)算的是數(shù)值的最大值和最小值。
• max()和min()中傳入的是"日期類型"，max()計(jì)算的最大值是離我們最近的那個(gè)日期，min()計(jì)算的最小值是離我們最遠(yuǎn)的那個(gè)日期，這個(gè)可以記一下。
• max()和min()中傳入的是字符串類型，max()計(jì)算的最大值是按照英文字母順序顯示的，min()計(jì)算的最小值也是按照英文字母順序顯示的，意義不太大。

④ count()函數(shù)：可以傳入任何數(shù)據(jù)類型，但是碰到null要注意；

結(jié)論如下：

??? count()函數(shù)可以傳入任何數(shù)據(jù)類型，表示對(duì)行計(jì)數(shù)。
????"但是下面的知識(shí)點(diǎn)需要特別注意的"
????首先看看【count(sal),count(birth)】這兩句表示的是什么意思？這兩句分別表示的是
對(duì)sal列字段、birth列字段的行數(shù)，進(jìn)行統(tǒng)計(jì)。由于其中有一條記錄是null值，因此使用count()
函數(shù)計(jì)數(shù)的時(shí)候，會(huì)忽略掉null行。
????其次，對(duì)于count(*)表示的是統(tǒng)計(jì)【整個(gè)表】有多少行，這個(gè)肯定是對(duì)原始數(shù)據(jù)的行數(shù)的正確
統(tǒng)計(jì)，只要整張表某一行有一個(gè)列字段的值不是null，count(*)就會(huì)認(rèn)為該行為1行。當(dāng)然要是一
整行都是null值，你也沒(méi)必要插入這條記錄。
???總結(jié):
???當(dāng)某個(gè)字段列中沒(méi)有null值，則"count(列字段)=count(*)。"
???當(dāng)某個(gè)字段列中有null值，則"count(列字段)???因此，假如你想統(tǒng)計(jì)的是整張表的行數(shù)，請(qǐng)用count(*)。

其實(shí)所有的分組函數(shù)都忽略null值的，但上面那個(gè)count()函數(shù)碰到null值要特別注意。

⑤ count()函數(shù)碰到null值需要特別注意；

結(jié)論如下：

????對(duì)于avg(sal)求平均值來(lái)說(shuō)，(6500+4000+5500+10000)/4=6500。對(duì)于后面這個(gè)
sum()/count(*)求平均值來(lái)說(shuō)，(6500+4000+5500+10000)/5=5200。

????好好體會(huì)上述例子，有時(shí)候某人成績(jī)雖然記錄的是null，但是你仍然有5個(gè)人存在，所
以你要考慮一下怎么使用合適的函數(shù)，達(dá)到你想要的結(jié)果。

⑥ count(1),count(0)表示的是啥意思呢？

????無(wú)論是sum(1),sum(0),count(1),count(0),avg(1),avg(0)，原理都是一樣的，
相當(dāng)于在原表中新增一列。

????其次，我們知道where后面接的是【邏輯值】，當(dāng)使用where?1和where?0原理也還是
一樣，也相當(dāng)于在原表中新增一列。

????我們只需要記住在mysql中："非0即為true，0為false"。也就是說(shuō)，下面的所有是
1的地方，你可以換成任何非0數(shù)字，都是可以的。

原理圖如下：

測(cè)試一下：

⑦ count(*)計(jì)數(shù)的效率問(wèn)題；

• MYISAM存儲(chǔ)引擎下，count(*)的效率高。
• INNODB存儲(chǔ)引擎下，count(*)和count(1)的效率差不多，比count(字段)效率要高一些。
• 綜上所述：優(yōu)先使用count(*)。

4）聚合函數(shù)和group by的聯(lián)合使用最重要；

關(guān)于這個(gè)知識(shí)點(diǎn)，我們將會(huì)在后面的知識(shí)點(diǎn)中進(jìn)行講述。在這里我們只需要記住一句話：當(dāng)SQL語(yǔ)句中使用了group by分組函數(shù)后，select后面的字段必須是group by后面的字段 + 聚合函數(shù)的使用。

點(diǎn)擊下方閱讀原文加入社區(qū)會(huì)員

點(diǎn)贊鼓勵(lì)一下