非常實(shí)用的MySQL函數(shù)全面總結(jié)詳解示例分析教程
1.MySQL中關(guān)于函數(shù)的說(shuō)明
"概念":類(lèi)似java、python中的方法,將一組邏輯語(yǔ)句封裝在方法體中,對(duì)外暴露方法名;
"好處":Ⅰ隱藏了實(shí)現(xiàn)細(xì)節(jié);Ⅱ提高代碼的重用性;
"調(diào)用":select 函數(shù)名(實(shí)參列表) [from 表];
"特點(diǎn)":Ⅰ叫什么(函數(shù)名);Ⅱ干什么(函數(shù)功能);
"分類(lèi)":Ⅰ單行函數(shù);Ⅱ分組函數(shù);
"什么是單行函數(shù):":作用于表中的每一行記錄,一條記錄出來(lái)一個(gè)結(jié)果;
"什么是聚合函數(shù):":作用于一行或者多行,最終返回一個(gè)結(jié)果;
2.單行函數(shù)分類(lèi)
字符函數(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)啵?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)全面。希望大家一定要好好研究這幾種用法,都是很有用的。
等值判斷:類(lèi)似于java中switch case的效果;
區(qū)間判斷:類(lèi)似于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、查詢(xún)各科成績(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ù)的功能和分類(lèi);
① 聚合函數(shù)的功能;
用作統(tǒng)計(jì)使用,又稱(chēng)為聚合函數(shù)或統(tǒng)計(jì)函數(shù)或組函數(shù)。
② 聚合函數(shù)的分類(lèi); sum 求和avg 平均值max 最大值min 最小值count 計(jì)算個(gè)數(shù)
2)聚合函數(shù)的簡(jiǎn)單使用
3)五個(gè)聚合函數(shù)中傳入的參數(shù),所支持的數(shù)據(jù)類(lèi)型有哪些?
mysql不是強(qiáng)類(lèi)型的編程語(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ù)類(lèi)型才有意義;
結(jié)論如下:
sum()函數(shù)和avg()函數(shù)對(duì)于字符串類(lèi)型、日期/時(shí)間類(lèi)型的計(jì)算都沒(méi)有太大意義。因此,sum()函數(shù)和avg()函數(shù),我們只用來(lái)對(duì)小數(shù)類(lèi)型和整型進(jìn)行求和。
③ max()函數(shù)和min()函數(shù):傳入整型/小數(shù)類(lèi)型、日期/時(shí)間類(lèi)型意義較大;
結(jié)論如下:
max()和min()中傳入的是"整型/小數(shù)類(lèi)型",計(jì)算的是數(shù)值的最大值
和最小值
。max()和min()中傳入的是"日期類(lèi)型",max()計(jì)算的最大值是離我們最近的那個(gè)日期
,min()計(jì)算的最小值是離我們最遠(yuǎn)的那個(gè)日期
,這個(gè)可以記一下。max()和min()中傳入的是字符串類(lèi)型
,max()計(jì)算的最大值是按照英文字母順序顯示的,min()計(jì)算的最小值也是按照英文字母順序顯示的,意義不太大。
④ count()函數(shù):可以傳入任何數(shù)據(jù)類(lèi)型,但是碰到null要注意;
結(jié)論如下:
count()函數(shù)可以傳入任何數(shù)據(jù)類(lèi)型,表示對(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(列字段)<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的使用“最重要”;
關(guān)于這個(gè)知識(shí)點(diǎn),我們將會(huì)在后面的知識(shí)點(diǎn)中進(jìn)行講述。在這里我們只需要記住一句話(huà):當(dāng)SQL語(yǔ)句中使用了group by分組函數(shù)后,select后面的字段必須是group by后面的字段 + 聚合函數(shù)的使用。
以上就是非常實(shí)用的MySQL函數(shù)全面總結(jié)詳解示例分析的詳細(xì)內(nèi)容,更多關(guān)于MySQL函數(shù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
windows下mysql?8.0.27?安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了windows下mysql?8.0.27?安裝配置方法圖文教程,文中安裝步驟介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2022-04-04使用SQL語(yǔ)句統(tǒng)計(jì)數(shù)據(jù)時(shí)sum和count函數(shù)中使用if判斷條件的講解
今天小編就為大家分享一篇關(guān)于使用SQL語(yǔ)句統(tǒng)計(jì)數(shù)據(jù)時(shí)sum和count函數(shù)中使用if判斷條件的講解,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-02-02淺談Mysql、SqlServer、Oracle三大數(shù)據(jù)庫(kù)的區(qū)別
這篇文章主要介紹了Mysql、SqlServer、Oracle三大數(shù)據(jù)庫(kù)的區(qū)別,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04mysql8.0.23 linux(centos7)安裝完整超詳細(xì)教程
這篇文章主要介紹了mysql8.0.23 linux(centos7)安裝完整超詳細(xì)教程,本文給介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01Mac OS系統(tǒng)下mysql 5.7.20安裝教程圖文詳解
這篇文章主要介紹了Mac OS系統(tǒng)下mysql 5.7.20安裝教程圖文詳解,本文給大家介紹的非常詳細(xì),具有參考借鑒價(jià)值,需要的朋友可以參考下2017-11-11QT連接MYSQL數(shù)據(jù)庫(kù)的詳細(xì)步驟
這篇文章主要介紹了QT連接MYSQL數(shù)據(jù)庫(kù)的詳細(xì)步驟,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-07-07MySQL數(shù)據(jù)庫(kù)如何開(kāi)啟遠(yuǎn)程連接(多備份)
多備份服務(wù)器在備份你的數(shù)據(jù)庫(kù)時(shí),必須能夠遠(yuǎn)程連接上你的數(shù)據(jù)庫(kù)。但是一般來(lái)說(shuō)mysql安裝時(shí)都是關(guān)閉遠(yuǎn)程連接的,因此,需要你開(kāi)通mysql數(shù)據(jù)庫(kù)的遠(yuǎn)程訪(fǎng)問(wèn)權(quán)限。那么如何開(kāi)啟呢2015-01-01MySQL觸發(fā)器之判斷更新操作前后數(shù)據(jù)是否改變
這篇文章主要介紹了MySQL觸發(fā)器之判斷更新操作前后數(shù)據(jù)是否改變方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08