快速回顧MySQL:彙總和分組

来源:https://www.cnblogs.com/flunggg/archive/2020/01/16/12203639.html
-Advertisement-
Play Games

10.3 彙總數據 我們經常需要彙總數據而不用把它們實際檢索處出來,為此MySQL提供了專門的函數。使用這些函數,MySQL查詢可用於檢索數據,以便分析和報表的生成。這種類型的檢索例子有以下幾種: 確定表中的行數(或者滿足某個條件或包含某個特定值的行數)。 獲得表中行組的和。 找出表列(或所有行或某 ...


10.3 彙總數據

我們經常需要彙總數據而不用把它們實際檢索處出來,為此MySQL提供了專門的函數。使用這些函數,MySQL查詢可用於檢索數據,以便分析和報表的生成。這種類型的檢索例子有以下幾種:

  • 確定表中的行數(或者滿足某個條件或包含某個特定值的行數)。
  • 獲得表中行組的和。
  • 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。

上述的例子都需要對錶中數據(而不是實際數據本身)彙總。因此,返回實際表數據是對時間和處理資源的一種浪費(更不用說帶寬了)。

MySQL提供了5個聚集函數。

聚集函數(aggregate function)運行在行組上,計算和返回單個值的函數。

函數 說明
AVG() 返回某列的平均值
COUNT() 返回某列的行數
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

下麵簡單介紹這些函數

先創建下麵的表:

CREATE TABLE student(
    sid INT PRIMARY KEY,
    sname VARCHAR(20),
    ssex  CAHR(1)
);
CREATE TABLE score(
    sid INT PRIMARY KEY,
    sname VARCHAR(20),
    smath INT,
    senglish INT
);

10.3.1 AVG()函數

AVG()通過對錶中行數計數並計算特定列值之和,求得該列的平均值。AVG()可用來返回所有列的平均值,也可以用來返回特定列平均值。

AVG()只能用來確定特定數值列的平均值,而且列名必須作為函數參數給出。為了獲得多個列的平均值,必須使用多個AVG()函數。
AVG()函數忽略列值為NULL的行。

來使用AVG()函數來查詢成績表score中數學成績的平均分(自己隨便加上幾個數):

SELECT AVG(smath) AS avg_smath FROM score;

AVG()也可用用來確定特定列平均值,配合WHERE子句。

10.3.2 COUNT()函數

COUNT()函數確定表中行的數目或符合特定條件的行的數目。

COUNT()函數有兩種使用的方式:

  • 使用COUNT(*)對錶中行的數目進行計數,不管表列中包含的是空值(NULL)還是非空值。
  • 使用COUNT(column)對特定列中具有值的行進行計數,忽略NULL值。

比如:返回學生總數:

SELECT COUNT(*) FROM student;

返回女生總數(特定列):

SELECT COUNT(ssex) FROM student WHERE ssex='女';

如果指定列名,則指定列的值為空的行被COUNT()函數忽略,但如果COUNT()函數中用的是星號(*),則不忽略。

10.3.3 MAX()函數

MAX() 返回指定列中的最大值。MAX() 要求指定列名。

比如:返回成績表中數學成績的最高分:

SELECT MAX(smath) FROM score;

雖然MAX()一般用來找出最大的數值或日期值,但MySQL允許將它用來返回任意列中的最大值,包括返迴文本列中的最大值。在用於文本數據時,如果數據按相應的列排序,則MAX()返回最後一行。MIN()函數也是,但是相反,返回最前面的行。

MAX()、MIN()都是忽略列值為NULL的行。

10.3.4 MIN()函數

MIN() 返回指定列中的最小值。MIN() 要求指定列名。

比如:返回成績表中數學成績的最低分:

SELECT MIN(smath) FROM score;

10.3.5 SUM()函數

SUM()用來返回指定列值的和(總計)。

比如返回一個班中的數學成績的總分:

SELECT SUM(smath) FROM score;

利用標準的算術操作符,所有的聚集函數都可以用來執行多個列上的計算。

SUM()函數忽略列值為NULL的行。

10.3.6 聚集不同值

以上5個聚集函數都可以如下使用:

  • 對所有的行執行計算,指定參數或不給參數(MAX和MIN函數);
  • 只包含不同的值,指定DISTINCT參數。(去重)

下麵舉例指定DISTINCT參數的用法:比如成績表中數學成績的平均分,平均分只考慮各個不同的分數:

SELECT AVG(DISTINCT smath) AS avg_smath FROM score;

如果指定列名,則DISTINCT只能用於COUNT(),DISTINCT不能用於COUNT(*),因此不允許使用COUNT(DISTINCT),否則會產生錯誤。類似地,DISTINCT必須使用列名,不能用於計算或表達式。

DISTINCT可用於MAX()和MIN(),但是沒有意義。

10.3.7 組合聚集函數

實際上SELECT語句可根據需要包含多個聚集函數

使用聚集函數,最好使用別名。


小結:聚集函數用來彙總數據,這些函數都是高效設計,它們返回的結果一般比你在自己的客戶機應用程式中計算要快得多。

10.4 分組數據

前面已經學習了SQL聚集函數來彙總數據。但是,比如:要求出學生表中男生和女生各有多少人。可用使用兩條語句分別列印。下麵介紹另一種方式:分組。

分組:把數據分為多個邏輯組,以便能對每個組進行聚集計算。

10.4.1 創建分組

分組時在SELECT語句的GROUP BY子句中建立的。按照上面的例子,對學生表的男女生分組:

SELECT ssex, COUNT(ssex) AS total FROM student GROUP BY ssex;

輸出:

+---------+----------------+
| stu_sex |      total     |
+---------+----------------+
| 女      |              6 |
| 男      |             11 |
+---------+----------------+
3 rows in set (0.05 sec)

但是也可這樣使用:

SELECT ssex, COUNT(*) AS total FROM student GROUP BY ssex;

輸出結果同上。

為什麼會這樣??因為使用了GROUP BY,就不必指定要計算和估值得每個組了。系統會自動完成。GROUP BY子句指示MySQL分組數據,然後對每個組而不是整個結果集進行聚集。

在具體使用GROUP BY子句前,需要知道一些重要的規定:

  • GROUP BY子句可以包括任意數目的列。這使得能對分組進行嵌套,為數據分組提供更細緻的控制。
  • 如果在GROUP BY子句中嵌套了分組,數據將在最後規定的分組上進行彙總。換句話說,在建立分組時,指定的所有列都一起計算(所以不能從個別的列取回數據)。
  • GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式(但不能是聚集函數)。如果在SELECT中使用表達式,則必須在GROUP BY子句中指定相同的表達式。不能使用別名。
  • 除聚集計算語句外,SELECT語句中的每個列都必須在GROUP BY子句中給出。
  • 如果分組列中具有NULL值,則NULL將作為一個分組返回。如果列中有多行NULL值,它們將分為一組。
  • GROUP BY子句必須出現在WHERE子句之後,ORDER BY子句之前。

使用WITH ROLLUP關鍵字,可以得到每個分組以及每個分組彙總級別(針對每個分組)的值
比如:

SELECT ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex WITH ROLLUP;

輸出:

+---------+-------+
| stu_sex | total |
+---------+-------+
| 女      |    10 |
| 男      |    16 |
| NULL    |    26 |
+---------+-------+
3 rows in set (0.05 sec)

引入一個函數:IFNULL(expression, alt_value):IFNULL() 函數用於判斷第一個表達式是否為 NULL,如果為 NULL 則返回第二個參數的值,如果不為 NULL 則返回第一個參數的值。

SELECT IFNULL(ssex,'總計')  AS ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex WITH ROLLUP;

輸出:

+---------+-------+
| stu_sex | total |
+---------+-------+
| 女      |    10 |
| 男      |    16 |
| 總計    |    26 |
+---------+-------+
3 rows in set (0.05 sec)

10.4.2 過濾分組

MySQL允許過濾分組,規定包括哪些分組,排除哪些分組。但是WHERE子句過濾指定的是行而不是分組。所以MySQL提供了HAVING子句,HAVING子句類似於WHERE,至今為止的所有類型的WHERE子句都可以用HAVING子句來替代。唯一的差別是WHERE子句過濾行,而HAVING子句過濾分組(即是先分組再過濾)

比如:顯示學生表的男生或者女生人數大於15個的。

SELECT ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex HAVING COUNT(*)>=15;

註意:HAVING子句在GROUP BY之後,因為是對分組的結果進行過濾。而WHERE子句是對行進行過濾,所以必須在GROUP BY之前。所以WHERE子句過濾的行可能會影響到GROUP BY的分組結果

那麼可以同時使用WHERE子句和HAVING子句嗎??答案是可以的。

10.4.3 分組和排序

說一說排序(ORDER BY)和分組(GROUP BY)的區別:

排序 分組
排序產生的輸出 輸出可能不是分組的順序
任意列都可以使用(甚至非選擇的列也可以使用) 只能使用選擇列或表達式列,而且必須使用每個選擇列表達式
不一定需要 如果與聚集函數一起使用列(或表達式),則必須使用

上面表格的第一行,可以經常發現GROUP BY分組的數據確實是以分組順序輸出的,但情況並不總這樣,它並不是SQL規範所要求的。此外,用戶可能會要求以不同於分組的順序排序。僅因為你以某種方式分組數據,並不表示你需要以相同的方式排序輸出。應該提供明確的BRDER BY子句,即使其效果等同於GROUP BY子句的輸出結果。

所以一般使用GROUP BY子句最好跟上ORDER BY子句。

那麼在位置如何放置:ORDER BY子句一定是放在sQL語句最後的。

所以修改上面的語句:例子舉得不是很好,就看看怎麼寫。

SELECT ssex, COUNT(*) AS total 
FROM student 
GROUP BY ssex 
HAVING COUNT(*)>=15
ORDER BY ssex;

小結:學習瞭如何用SQL聚集函數對數據進行彙總計算。也介紹如何使用GROUP UP子句對數據組進行這些彙總金酸,返回每個組的結果。而且還學了HAVING子句過濾特定的組,WHERE和HAVING之間以及ORDER BY和GROUP BY之間的差異


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 1.典型固定式偏置電路 圖1-97所示是經典的固定式偏置電路。電路中的VT1是NPN型三極體,採用正極性電源+V供電。 圖1-97 經典的固定式偏置電路 (1)固定式偏置電阻。在直流工作電壓+V和電阻R1的阻值大小確定後,流入三極體的基極電流就是確定的,所以R1稱為固定式偏置電阻。 (2)基極電流回 ...
  • 爬蟲項目需要用到lxml包,解析html文件,但是linux伺服器沒有lxml包, 伺服器中python版本是3.8. 直接使用命令安裝: pip install lxml 中途會報錯,錯誤提示我沒有保存,懶得再現了。以為是python版本問題,再運行: pip3 install lxml 註意此命 ...
  • # PC:Ubuntu18.04# GCC: arm-linux-gnueabihf- 7.4.0# 開發板:# imx6ull+debian10(文件系統為野火提供的) # 此處需要編譯的是帶gstreamer的qt 靜態庫,僅著重說明安裝gstreamer的庫,忽略其他工具的安裝# 參考:太多, ...
  • 一份新市場報告預計,從2018年到2029年,獨立MRAM和STT-MRAM的收入將增長170倍,達到近40億美元的收入。下一代記憶體技術的增長將主要由取代效率較低的記憶體技術(例如NOR快閃記憶體和SRAM)推動。 MRAM容量出貨量預測(2017-2029,Coughlin)分析人士預計,包括DRAM,3 ...
  • 1.cd /Library/LaunchDaemons 2.sudo vim com.redis.plist 3.sudo launchctl load /Library/LaunchDaemons/com.redis.plist 4.sudo launchctl start com.redis ...
  • Linux伺服器下運行同時包含CPU和GPU版本TensorFlow的項目,對於GPU版本,首先使用Anaconda建立GPU環境,首先將本地環境同步到服務上,再卸載cpu版本的pytorch和TensorFlow,下載GPU版對應版本的pytorch和TensorFlow。執行不同版本項目時,只需... ...
  • Centos下PHP,Apache,Mysql 的安裝 安裝Apache yum -y install httpd systemctl start httpd 添加防火牆 firewall-cmd --permanent --add-port=80/tcp systemctl restart fir ...
  • 隨著業務的越發複雜,對軟體系統的要求越來越高,這意味著我們需要隨時掌控系統的運行情況。因此,對系統的實時監控以及可視化展示,就成了基礎架構的必須能力。 Grafana官方網站 Grafana介紹 Grafana是一個跨平臺的開源的度量分析和可視化工具,可以通過將採集的數據查詢然後可視化的展示,並及時 ...
一周排行
    -Advertisement-
    Play Games
  • .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
  • 目錄前言PostgreSql安裝測試額外Nuget安裝Person.cs模擬運行Navicate連postgresql解決方案Garnet為什麼要選擇Garnet而不是RedisRedis不再開源Windows版的Redis是由微軟維護的Windows Redis版本老舊,後續可能不再更新Garne ...
  • C#TMS系統代碼-聯表報表學習 領導被裁了之後很快就有人上任了,幾乎是無縫銜接,很難讓我不想到這早就決定好了。我的職責沒有任何變化。感受下來這個系統封裝程度很高,我只要會調用方法就行。這個系統交付之後不會有太多問題,更多應該是做小需求,有大的開發任務應該也是第二期的事,嗯?怎麼感覺我變成運維了?而 ...
  • 我在隨筆《EAV模型(實體-屬性-值)的設計和低代碼的處理方案(1)》中介紹了一些基本的EAV模型設計知識和基於Winform場景下低代碼(或者說無代碼)的一些實現思路,在本篇隨筆中,我們來分析一下這種針對通用業務,且只需定義就能構建業務模塊存儲和界面的解決方案,其中的數據查詢處理的操作。 ...
  • 對某個遠程伺服器啟用和設置NTP服務(Windows系統) 打開註冊表 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpServer 將 Enabled 的值設置為 1,這將啟用NTP伺服器功 ...
  • title: Django信號與擴展:深入理解與實踐 date: 2024/5/15 22:40:52 updated: 2024/5/15 22:40:52 categories: 後端開發 tags: Django 信號 松耦合 觀察者 擴展 安全 性能 第一部分:Django信號基礎 Djan ...
  • 使用xadmin2遇到的問題&解決 環境配置: 使用的模塊版本: 關聯的包 Django 3.2.15 mysqlclient 2.2.4 xadmin 2.0.1 django-crispy-forms >= 1.6.0 django-import-export >= 0.5.1 django-r ...
  • 今天我打算整點兒不一樣的內容,通過之前學習的TransformerMap和LazyMap鏈,想搞點不一樣的,所以我關註了另外一條鏈DefaultedMap鏈,主要調用鏈為: 調用鏈詳細描述: ObjectInputStream.readObject() DefaultedMap.readObject ...
  • 後端應用級開發者該如何擁抱 AI GC?就是在這樣的一個大的浪潮下,我們的傳統的應用級開發者。我們該如何選擇職業或者是如何去快速轉型,跟上這樣的一個行業的一個浪潮? 0 AI金字塔模型 越往上它的整個難度就是職業機會也好,或者說是整個的這個運作也好,它的難度會越大,然後越往下機會就會越多,所以這是一 ...
  • @Autowired是Spring框架提供的註解,@Resource是Java EE 5規範提供的註解。 @Autowired預設按照類型自動裝配,而@Resource預設按照名稱自動裝配。 @Autowired支持@Qualifier註解來指定裝配哪一個具有相同類型的bean,而@Resourc... ...