MySQL聚合查詢案例講解
1、前言
前面的內容已經(jīng)把基礎的增刪改查介紹的差不多了，也介紹了表的相關約束， 從本期開始往后的內容，就更加復雜了，更多的是一些復雜的查詢 SQL.
本期雖然是講述聚合查詢相關知識，但是這里補充一個知識點，如何將查詢結果插入到另一個表中呢？
2、插入查詢結果
查詢還是用的比較多的，對于查詢到的數(shù)據(jù)，能不能也給保存下來呢？也就是把查詢的結果插入到另一張表中。
案例：創(chuàng)建一張學生表，表中有 id，name，sex，java，python 這些字段，現(xiàn)需要把 java 成績超過 90 的學生復制進 java_result 表，復制的字段為 name，java。
進行上述操作之前，我們需要創(chuàng)建一個學生表并準備好相關的數(shù)據(jù)：
create table student (
id int primary key,
name varchar(20),
sex varchar(1),
java float(5, 2)
);
insert into student value
(1, '張三', '男', 92.1),
(2, '小紅', '女', 88.2),
(3, '趙六', '男', 83.4),
(4, '王五', '男', 93.3),
(5, '小美', '女', 96.0);有了學生表之后，我們要把 name，java 這兩個字段的查詢結果復制到 java_result 這個表中，這里我們注意，要求查詢結果的臨時表的列數(shù)和列的類型，要和 java_result 這里匹配，所以接下來我們就來創(chuàng)建 java_result 這張表：
create table java_result (
name varchar(20),
java float(5, 2)
);創(chuàng)建好 java_result 這張表之后，就要查詢 student 表中 name java 兩個字段，并且 java > 90，將滿足上述條件的查詢結果，插入到 java_result 表中?。?/p>
insert into java_result select name, java from student where java > 90; -- Query OK, 3 rows affected (0.00 sec) -- Records: 3 Duplicates: 0 Warnings: 0
select * from java_result; +--------+-------+ | name | java | +--------+-------+ | 張三 | 92.10 | | 王五 | 93.30 | | 小美 | 96.00 | +--------+-------+ -- 3 rows in set (0.00 sec)
這樣我們就發(fā)現(xiàn)，已經(jīng)將 student 表中 name 和 java 字段滿足 > 90 的數(shù)據(jù)已經(jīng)全部插入成功了！
3、聚合查詢
前面我們接觸過的 帶表達式查詢 都是列和列之間進行運算的，看哪一列滿足了這個條件。
而現(xiàn)在要介紹的聚合查詢，就是針對 行和行 之間進行運算的！
3.1 聚合函數(shù)
進行聚合查詢，需要搭配聚合函數(shù)，下面介紹的函數(shù)都是 SQL 中內置的一組函數(shù)，我們先來簡單的認識下：
| 函數(shù) | 解釋 |
|---|---|
| COUNT([DISTINCT] expr) | 返回查詢到的數(shù)據(jù)的數(shù)量 |
| SUM([DISTINCT] expr) | 返回查詢到的數(shù)據(jù)的總和，不是數(shù)字無意義 |
| AVG([DISTINCT] expr) | 返回查詢到的數(shù)據(jù)的平均值，不是數(shù)字無意義 |
| MAX([DISTINCT] expr) | 返回查詢到的數(shù)據(jù)的最大值，不是數(shù)字無意義 |
| MIN([DISTINCT] expr) | 返回查詢到的數(shù)據(jù)的最小值，不是數(shù)字沒有意義 |
下面我們就來演示一下上述的聚合函數(shù)的簡單使用，在使用之前，我們需要有一張表，并且有相應的數(shù)據(jù)：
select * from student; +----+--------+------+-------+ | id | name | sex | java | +----+--------+------+-------+ | 1 | 張三 | 男 | 92.10 | | 2 | 小紅 | 女 | 88.20 | | 3 | 趙六 | 男 | 83.40 | | 4 | 王五 | 男 | 93.30 | | 5 | 小美 | 女 | 96.00 | | 6 | 李四 | 男 | NULL | +----+--------+------+-------+ -- 6 rows in set (0.00 sec)
下面我們就針對上述這張表，來使用下上述的聚合函數(shù)。
3.1.1 count
● 求出 student 表中有多少同學
select count(*) from student; +----------+ | count(*) | +----------+ | 6 | +----------+ -- 1 row in set (0.00 sec)
這個操作就相當于先進行 select * ，然后針對返回的結果，在進行 count 運算，求結果集合的行數(shù). 注意：此處如果有一列的數(shù)據(jù)全是 null，也會算進去！(因為是針對 *)
此處這里的 count() 括號中，不一定寫 *，可以寫成任意的列明/表達式，所以我們可以針對 name 來統(tǒng)計人數(shù)：
select count(name) from student; +-------------+ | count(name) | +-------------+ | 6 | +-------------+ -- 1 row in set (0.00 sec)
● 統(tǒng)計有多少人有 java 考試成績
select count(java) from student; +-------------+ | count(java) | +-------------+ | 5 | +-------------+ -- 1 row in set (0.00 sec)
這里我們看到了，由于 count 是針對 java 字段進行統(tǒng)計，而 李四 那一條數(shù)據(jù)中，java 為 null，前面我們學習過，null 與任何值計算都是 null，所以統(tǒng)計的時候，就把 null 給去掉了。
● 統(tǒng)計 java 成績大于90分的人數(shù)
select count(java) from student where java > 90; +-------------+ | count(java) | +-------------+ | 3 | +-------------+ -- 1 row in set (0.00 sec)
這里我們要弄清楚，count() 這個括號中，是針對你要針對的那一列，針對不同列，不同的條件，就會有不同的結果，對于 count 的演示就到這里。
注意：count 和 () 之間不能有空格，必須緊挨著，在 Java 中函數(shù)名和() 之間是可以有空格的，但很少人會這樣寫。
3.1.2 sum
這個聚合函數(shù)，就是把指定列的所有行進行相加得到的結果，要求這個列得是數(shù)字，不能是字符串/日期。
● 求出學生表中 java 考試分數(shù)總和
select sum(java) from student; +-----------+ | sum(java) | +-----------+ | 453.00 | +-----------+ -- 1 row in set (0.01 sec)
雖然我們表中有 java 字段這列中有 null 值，前面了解到 null 與任何值運算都是 null，但是這里的 sum 函數(shù)會避免這種情況發(fā)生。
當然在后面也可也帶上 where 條件，這里就不做過多演示了。
3.1.3 avg
● 求班級中 java 的平均分
select avg(java) from student; +-----------+ | avg(java) | +-----------+ | 90.600000 | +-----------+ -- 1 row in set (0.00 sec)
當前只是針對某一列進行平均運算，如果有兩門課程，求每個學生總分的平均分呢？
select avg(java + python) from student;
這里每次查詢結果都只有一列，能否把兩個聚合函數(shù)一起使用呢？
select sum(java), avg(java) as '平均分' from student; +-----------+-----------+ | sum(java) | 平均分 | +-----------+-----------+ | 453.00 | 90.600000 | +-----------+-----------+ -- 1 row in set (0.00 sec)
這里我們能發(fā)現(xiàn)一個細節(jié)，使用聚合函數(shù)查詢，字段也是可以取別名的。
3.1.4 max 和 min
● 求出 java 考試分數(shù)的最高分和最低分
select max(java) as '最高分', min(java) as '最低分' from student; +-----------+-----------+ | 最高分 | 最低分 | +-----------+-----------+ | 96.00 | 83.40 | +-----------+-----------+ -- 1 row in set (0.00 sec)
上述就是聚合函數(shù)最基礎的用法了， 但是在實際中也可能會有更復雜的情況，比如需要按照某某進行分組查詢，這就需要搭配 GROUP BY 字句了。
4、GROUP BY 子句
select 中使用 group by 自居可以對指定列進行分組查詢，但是需要滿足指定分組的字段必須是 "分組依據(jù)字段"，其他字段若想出現(xiàn)在 select 中，則必須包含在聚合函數(shù)中。
這里我們構造出一張薪水表 salary：
create table salary (
id int primary key,
name varchar(20),
role varchar(20),
income int
);
insert into salary value
(1, '麻花疼', '老板', 5000000),
(2, '籃球哥', '程序猿', 3000),
(3, '歪嘴猴', '經(jīng)理', 20000),
(4, '多嘴鳥', '經(jīng)理', 25000),
(5, '雷小君', '老板', 3000000),
(6, '阿紫姐', '程序猿', 5000);像上述的情況，如果要查平均工資，那公平嗎？？？
select avg(income) from salary; +--------------+ | avg(income) | +--------------+ | 1342166.6667 | +--------------+ -- 1 row in set (0.00 sec)
那籃球哥的月薪連平均下來的零頭都不到，所以這樣去求平均工資是毫無意義的，真正有意義的是啥呢？求老板這個職位的平均工資，以及經(jīng)理這個職位的平均工資，及程序猿這個職位的平均工資，通俗來說，就是按照 role 這個字段進行分組。每一組求平均工資：
select role, avg(income) from salary group by role; +-----------+--------------+ | role | avg(income) | +-----------+--------------+ | 程序猿 | 4000.0000 | | 經(jīng)理 | 22500.0000 | | 老板 | 4000000.0000 | +-----------+--------------+ -- 3 rows in set (0.00 sec)
這就也就是把 role 這一列，值相同的行給分成了一組，然后計算平均值，也是針對每個分組，分別計算。
在 MySQL 中，這里得到的查詢結果臨時表，如果沒有 order by 指定列排序，這里的順序是不可預期的，當然也可以手動指定排序，比如最終結果按照平均工資降序排序：
select role, avg(income) from salary group by role order by avg(income) desc; +-----------+--------------+ | role | avg(income) | +-----------+--------------+ | 老板 | 4000000.0000 | | 經(jīng)理 | 22500.0000 | | 程序猿 | 4000.0000 | +-----------+--------------+ -- 3 rows in set (0.00 sec)
如果不帶聚合函數(shù)的普通查詢，能否可行呢？這里如果你沒有修改任何配置文件，是不可行的，記住千萬不能把前面的 order by 與 group by 弄混！
5、HAVING 關鍵字
分組查詢也是可以指定條件的，具體三種情況：
- 先篩選，再分組(where)
- 先分組，再篩選(having)
- 分組前分組后都指定條件篩選(where 和 having 結合使用)
如何理解上述三條的含義呢？ 這里我們舉幾個例子就很好理解了：
● 籃球哥月薪 3000 實在是太低了，簡直給程序猿崗位拖后腿，干脆求平均工資時去掉籃球哥的月薪數(shù)據(jù)。
select role, avg(income) from salary where name != '籃球哥' group by role; +-----------+--------------+ | role | avg(income) | +-----------+--------------+ | 程序猿 | 5000.0000 | | 經(jīng)理 | 22500.0000 | | 老板 | 4000000.0000 | +-----------+--------------+ -- 3 rows in set (0.00 sec)
這樣求出來的平均值就不包含籃球哥的月薪數(shù)據(jù)了，這就是先篩選，再分組。
● 還是查詢每個崗位的平均工資，但是除去平均月薪在 10w 以上的崗位，不能讓籃球哥眼紅！
select role, avg(income) from salary group by role having avg(income) < 100000; +-----------+-------------+ | role | avg(income) | +-----------+-------------+ | 程序猿 | 4000.0000 | | 經(jīng)理 | 22500.0000 | +-----------+-------------+ -- 2 rows in set (0.00 sec)
這樣一來就只保留了平均月薪小于 10w 的崗位了，很明顯這個平均值是在分組之后才算出來的，這也就是先分組，再篩選。
這里 having 也能加上邏輯運算符，具體感興趣的小伙伴可以自行下來嘗試一下，好比如你想要拿好 offer，就得技術過關，還能加班！
至于第三種分組前后都需要篩選，就是把上述倆例子結合起來，這里就不多贅述了！
到此這篇關于MySQL聚合查詢的文章就介紹到這了,更多相關mysql聚合查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了mysql如何導出服務器內所有的數(shù)據(jù)庫問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
這篇文章主要介紹了mysql 修改密碼和設置允許遠程登錄的相關資料,需要的朋友可以參考下2015-07-07
本文主要介紹了MySQL?配置主從復制實踐，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-03-03
MySQL易學易用，且附帶豐富的技術文檔，這二個因素使之被廣泛應用。然而，隨著MySQL發(fā)展之迅速，即使一個MySQL老手有時也會為該軟件出其不意的功能感嘆。2011-01-01
什么是事務處理呢？？事務處理是用來維護數(shù)據(jù)庫的完整性的，它保證成批的MySQL操作要么完全執(zhí)行，要么完成不執(zhí)行。2017-08-08
MySQL依據(jù)一些規(guī)則，竭盡全力的把這些很糟糕的語句轉換成某種可以比較高效執(zhí)行的形式，這個過程也可以被稱作查詢重寫，本章主要就是詳細講解下這些比較重要的重寫規(guī)則，感興趣的小伙伴可跟著小編一起來學習2023-05-05
早打算寫些關于數(shù)據(jù)庫操作方面的知識了，現(xiàn)在終于完成了第一篇，以下記錄了關于mysql操作方面的基礎知識2012-08-08
本文主要介紹了如何在Windows系統(tǒng)中添加MySQL的環(huán)境變量以及如何初始化MySQL,通過添加環(huán)境變量,可以在任意一個命令提示符中直接調用MySQL的相關程序,大大簡化了操作,初始化MySQL時,需要新建一個配置文件并指定默認編碼集和存儲引擎,如果初始化過程中出現(xiàn)錯誤2024-11-11
這篇文章主要介紹了mysql的常用函數(shù)與視圖索引的特點與作用，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習吧2022-10-10
本文主要介紹了MySQL查看所有用戶的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-03-03