快速回顧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
  • C#TMS系統代碼-基礎頁面BaseCity學習 本人純新手,剛進公司跟領導報道,我說我是java全棧,他問我會不會C#,我說大學學過,他說這個TMS系統就給你來管了。外包已經把代碼給我了,這幾天先把增刪改查的代碼背一下,說不定後面就要趕鴨子上架了 Service頁面 //using => impo ...
  • 委托與事件 委托 委托的定義 委托是C#中的一種類型,用於存儲對方法的引用。它允許將方法作為參數傳遞給其他方法,實現回調、事件處理和動態調用等功能。通俗來講,就是委托包含方法的記憶體地址,方法匹配與委托相同的簽名,因此通過使用正確的參數類型來調用方法。 委托的特性 引用方法:委托允許存儲對方法的引用, ...
  • 前言 這幾天閑來沒事看看ABP vNext的文檔和源碼,關於關於依賴註入(屬性註入)這塊兒產生了興趣。 我們都知道。Volo.ABP 依賴註入容器使用了第三方組件Autofac實現的。有三種註入方式,構造函數註入和方法註入和屬性註入。 ABP的屬性註入原則參考如下: 這時候我就開始疑惑了,因為我知道 ...
  • C#TMS系統代碼-業務頁面ShippingNotice學習 學一個業務頁面,ok,領導開完會就被裁掉了,很突然啊,他收拾東西的時候我還以為他要旅游提前請假了,還在尋思為什麼回家連自己買的幾箱飲料都要叫跑腿帶走,怕被偷嗎?還好我在他開會之前拿了兩瓶芬達 感覺感覺前面的BaseCity差不太多,這邊的 ...
  • 概述:在C#中,通過`Expression`類、`AndAlso`和`OrElse`方法可組合兩個`Expression<Func<T, bool>>`,實現多條件動態查詢。通過創建表達式樹,可輕鬆構建複雜的查詢條件。 在C#中,可以使用AndAlso和OrElse方法組合兩個Expression< ...
  • 閑來無聊在我的Biwen.QuickApi中實現一下極簡的事件匯流排,其實代碼還是蠻簡單的,對於初學者可能有些幫助 就貼出來,有什麼不足的地方也歡迎板磚交流~ 首先定義一個事件約定的空介面 public interface IEvent{} 然後定義事件訂閱者介面 public interface I ...
  • 1. 案例 成某三甲醫預約系統, 該項目在2024年初進行上線測試,在正常運行了兩天後,業務系統報錯:The connection pool has been exhausted, either raise MaxPoolSize (currently 800) or Timeout (curren ...
  • 背景 我們有些工具在 Web 版中已經有了很好的實踐,而在 WPF 中重新開發也是一種費時費力的操作,那麼直接集成則是最省事省力的方法了。 思路解釋 為什麼要使用 WPF?莫問為什麼,老 C# 開發的堅持,另外因為 Windows 上已經裝了 Webview2/edge 整體打包比 electron ...
  • EDP是一套集組織架構,許可權框架【功能許可權,操作許可權,數據訪問許可權,WebApi許可權】,自動化日誌,動態Interface,WebApi管理等基礎功能於一體的,基於.net的企業應用開發框架。通過友好的編碼方式實現數據行、列許可權的管控。 ...
  • .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...