非常實用的MySQL函數全面總結詳解示例分析教程

1.MySQL中關於函數的說明

“概念”：類似java、python中的方法，將一組邏輯語句封裝在方法體中，對外暴露方法名；

“好處”：Ⅰ隱藏瞭實現細節；Ⅱ提高代碼的重用性；

“調用”：select 函數名(實參列表) [from 表]；

“特點”：Ⅰ叫什麼(函數名)；Ⅱ幹什麼(函數功能)；

“分類”：Ⅰ單行函數；Ⅱ分組函數；

“什麼是單行函數：”：作用於表中的每一行記錄，一條記錄出來一個結果；

“什麼是聚合函數：”：作用於一行或者多行，最終返回一個結果；

2.單行函數分類

字符函數；

數學函數；

日期函數；

其他函數；

流程控制函數；

3.字符函數

1）length(str)：獲取參數值的字節個數；

對於utf8字符集來說，一個英文占1個字節；一個中文占3個字節；

對於gbk字符集來說，一個英文占1個字節；一個中文占2個字節；

操作如下 ：

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

操作如下：

3）upper(str)：將字符中的所有字母變為大寫；

操作如下：

4）lower(str) ：將字符中所有字母變為小寫；

操作如下：

5）substr(str,start,len)：從start位置開始截取字符串，len表示要截取的長度； 沒有指定len長度：表示從start開始起，截取到字符串末尾。指定瞭len長度：表示從start開始起，截取len個長度。

操作如下：

6）instr(str,要查找的子串) ：返回子串第一次出現的索引，如果找不到，返回0； 當查找的子串存在於字符串中：返回該子串在字符串中【第一次】出現的索引。當查找的子串不在字符串中：返回0。

操作如下：

7）trim(str)：去掉字符串前後的空格； 該函數隻能去掉字符串前後的空格，不能去掉字符串中間的空格。

操作如下：

8）lpad(str,len,填充字符)：用指定的字符，實現對字符串左填充指定長度；

操作如下：

9）rpad(str,len,填充字符)：用指定的字符，實現對字符串右填充指定長度；

操作如下：

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

操作如下：

4.數學函數

1）round(x,[保留的位數]) ：四舍五入； 當對正數進行四舍五入：按照正常的計算方式，四舍五入即可。當對負數進行四舍五入：先把符號丟到一邊，對去掉負號後的正數進行四舍五入，完成以後，再把這個負號，補上即可。

操作如下：

2）ceil(x)：向上取整，返回>=該參數的最小整數。(天花板函數) 天花板函數，在excel，python中均存在這個函數。你就想象一下你傢的天花板，把這個數字丟到天花板上，求的是大於等於這個數字的最小整數。

操作如下：

3）floor(x)：向下取整，返回<=該參數的最大整數。（地板函數） 地板函數：在excel，python中均存在這個函數。你就想象一下你傢的地板，把這個數字丟到地板上，求的是小於等於這個數字的最大整數。

操作如下：

4）truncate(x,D) ：截斷； truncate函數，也是在excel，python中均存在，含義基本都是一致的。但是這個函數理解起來有點難，我這裡帶著大傢好好學習一下。

理解如下：

“參考下面的示例圖，體會如下文字”
1）D是正數，操作的是小數點右側的小數部分。 
      D=1，直接從第1個位置處，砍掉後面的部分。
      D=2，直接從第2個位置處，砍掉後面的部分。
 ……
  

2）D是0，直接去掉小數部分。

3）D是負數，操作的是小數點左側的整數部分。
   D=-1，直接從-1位置處，先砍掉後面的小數部分，
   並且”從當前位置起(包括當前位置)，後面整數部分替換為0″。
   D=-2，直接從-2位置處，先砍掉後面的小數部分，
   並且”從當前位置起(包括當前位置)，後面整數部分替換為0″。

示例圖：

操作如下：

5）mod(被除數,除數) ：取餘； 當被除數為正數，結果就是正數。當被除數為負數，結果就是負數。

操作如下：

5.日期時間函數

日期的含義：指的是我們常說的年、月、日。

時間的含義：指的是我們常說的時、分、秒。

在講述下面函數之前，我們先補充一個知識，不同時間格式符表示什麼含義呢？

1）now() ：返回系統當前的日期和時間；

操作如下：

2）curdate() ：隻返回系統當前的日期，不包含時間；

操作如下：

3）curtime() ：隻返回系統當前的時間，不包含日期；

操作如下：

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

 獲取年份：year()；

獲取月份：month()；

獲取日：day()；

獲取小時：hour()；

獲取分鐘：minute()；

獲取秒數：second()；

5）weekofyear() ：獲取當前時刻所屬的周數；

操作如下：

6）quarter() ：獲取當前時刻所屬的季度；

操作如下：

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

操作如下：

8）date_format() ：將日期轉換成日期字符串； %Y-%m-%d返回的月份是01,02…這樣的格式。%Y-%c-%d返回的月份是1,2…這樣的格式。

操作如下：

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

操作如下：

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

操作如下：

11）datediff(end_date,start_date) ：計算兩個時間相差的天數；

操作如下：

12）timestampdiff(unit,start_date,end_date) ：計算兩個時間返回的年/月/天數；

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

year：年份

month：月份

day：天

hour：小時

minute 分鐘

second：秒

microsecond：微秒

week：周數

quarter：季度

YEAR：年份

操作如下：

6.其它常用系統函數

7.流程控制函數

1）if函數：實現if-else的效果；

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

3）case…when函數的三種用法；

case … when共有三種用法，我相信自己的總結會相當全面。希望大傢一定要好好研究這幾種用法，都是很有用的。

等值判斷：類似於java中switch case的效果；

區間判斷：類似於python中if-elif-else的效果；

case … when和聚合函數聯用；

① case … when用作等值判斷的語法格式；

case 要判斷的字段或表達式
when 常量1 then 要顯示的值1或語句1
when 常量2 then 要顯示的值2或語句2
…
else 要顯示的值n或語句n
end

操作如下：

② case … when用作區間判斷的語法格式；

case
when 條件1 then 要顯示的值1或語句1
when 條件2 then 要顯示的值2或語句2
…
else 要顯示的值n或語句n
end

操作如下：

③ case…when與聚合函數的聯用

利用上述原始表，完成如下問題：

— 18、查詢各科成績最高分、最低分和平均分，以如下形式顯示：
— 課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
— 及格為：>=60，中等為：70-80，優良為：80-90，優秀為：>=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(*) 優良率,
sum(case when score>=90 then 1 else 0 end)/count(*) 優秀率
from sc left join course 
on sc.c = course.c
group by sc.c;

結果如下：

8.聚合函數

1）聚合函數的功能和分類；

① 聚合函數的功能；

用作統計使用，又稱為聚合函數或統計函數或組函數。

② 聚合函數的分類； sum 求和avg 平均值max 最大值min 最小值count 計算個數

2）聚合函數的簡單使用

3）五個聚合函數中傳入的參數，所支持的數據類型有哪些？

mysql不是強類型的編程語言。也就是說，有些語句執行結果可能不報錯，但是執行結果無實際意義，因此，我們也認為是不正確的。

① 測試數據；

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

"插入數據"
 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()函數和avg()函數：傳入整型/小數類型才有意義；

結論如下：

sum()函數和avg()函數對於字符串類型、日期/時間類型的計算都沒有太大意義。因此，sum()函數和avg()函數，我們隻用來對小數類型和整型進行求和。

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

結論如下：

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

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

結論如下：

    count()函數可以傳入任何數據類型，表示對行計數。
    “但是下面的知識點需要特別註意的”
    首先看看count(sal) ，count(birth)這兩句表示的是什麼意思？這兩句分別表示的是
對sal列字段、birth列字段的行數，進行統計。由於其中有一條記錄是null值，因此使用count()
函數計數的時候，會忽略掉null行。
    其次，對於count(*)表示的是統計【整個表】有多少行，這個肯定是對原始數據的行數的正確
統計，隻要整張表某一行有一個列字段的值不是null，count(*)就會認為該行為1行。當然要是一
整行都是null值，你也沒必要插入這條記錄。

總結:

   當某個字段列中沒有null值，則”count(列字段)=count(*)。”
   當某個字段列中有null值，則”count(列字段)<count(*)。”
   因此，假如你想統計的是整張表的行數，請用count(*)。

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

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

結論如下：

對於avg(sal)求平均值來說，(6500+4000+5500+10000)/4=6500。

對於後面這個sum()/count(*)求平均值來說，(6500+4000+5500+10000)/5=5200。

好好體會上述例子，有時候某人成績雖然記錄的是null，但是你仍然有5個人存在，所以你要考慮一下怎麼使用合適的函數，達到你想要的結果。

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

    無論是sum(1),sum(0),count(1),count(0),avg(1),avg(0)，原理都是一樣的，相當於在原表中新增一列。

    其次，我們知道where後面接的是【邏輯值】，當使用where 1和where 0原理也還是一樣，也相當於在原表中新增一列。

    我們隻需要記住在mysql中：”非0即為true，0為false”。也就是說，下面的所有是1的地方，你可以換成任何非0數字，都是可以的。

原理圖如下：

測試一下：

⑦ count(*)計數的效率問題；

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

4）聚合函數和group by的使用“最重要”；

關於這個知識點，我們將會在後面的知識點中進行講述。在這裡我們隻需要記住一句話：當SQL語句中使用瞭group by分組函數後，select後面的字段必須是group by後面的字段 + 聚合函數的使用。

以上就是非常實用的MySQL函數全面總結詳解示例分析的詳細內容，更多關於MySQL函數的資料請關註WalkonNet其它相關文章！

推薦閱讀：

• mysql中TIMESTAMPDIFF案例詳解
• MySQL中查詢當前時間間隔前1天的數據
• 詳解Mysql日期格式並聚合統計示例
• 教你用python從日期中獲取年、月、日和星期等30種信息
• Pandas數據分析固定時間點和時間差