MySql基礎(chǔ)（四）查詢之聚合

  • 數(shù)據(jù)分組 GROUP BY

user表

mysql> SELECT name,age,salary,createtime FROM user;
+-----------------+------+---------+---------------------+
| name            | age  | salary  | createtime          |
+-----------------+------+---------+---------------------+
| 燕虹            |   21 | 7000.14 | 2017-08-31 10:53:52 |
| 鬼王            |  180 |    8000 | 2017-08-31 10:54:51 |
| 毒神            |  200 |    7000 | 2017-08-31 10:55:19 |
| 陸小琪          |   20 |    1000 | 2017-08-31 10:55:59 |
| 蒼松好道人      |  170 |    7000 | 2017-08-31 10:57:14 |
| 周一仙          |  500 |    5000 | 2017-09-01 16:18:20 |
| 小環(huán)            |   18 |    3000 | 2017-09-01 16:19:57 |
| 笨蛋            |    1 |       1 | 2017-09-07 16:24:43 |
| 笨笨            |    1 |       1 | 2017-09-07 16:25:03 |
| 小本            |   12 |    1000 | 2017-09-08 09:29:52 |
| 小歡1           |   32 |    1000 | 2017-09-08 09:59:54 |
| 小歡2           |   22 |    1000 | 2017-09-08 15:41:19 |
| 小歡3           |   17 |    1000 | 2017-09-08 15:43:11 |
| 小歡4           |   26 |    1000 | 2017-09-08 15:48:03 |
| 1               |    1 |       1 | 2017-09-09 14:49:54 |
| xiaoming        |   22 |   15555 | 2017-09-09 15:04:24 |
| 1               |    1 |       1 | 2017-09-15 15:00:02 |
+-----------------+------+---------+---------------------+
17 rows in set (0.00 sec)

查詢各個(gè)年齡段的具體人數(shù)

mysql> SELECT age ,COUNT(*) AS Total FROM user  GROUP BY age;
+------+-------+
| age  | Total |
+------+-------+
|    1 |     4 |
|   12 |     1 |
|   17 |     1 |
|   18 |     1 |
|   20 |     1 |
|   21 |     1 |
|   22 |     2 |
|   26 |     1 |
|   32 |     1 |
|  170 |     1 |
|  180 |     1 |
|  200 |     1 |
|  500 |     1 |
+------+-------+
13 rows in set (0.00 sec)

查詢各個(gè)年齡的具體人數(shù)，并把對(duì)應(yīng)的人員的姓名顯示出來(lái)

mysql> SELECT age ,GROUP_CONCAT(name), COUNT(*) AS Total FROM user  GROUP BY age;
+------+--------------------+-------+
| age  | GROUP_CONCAT(name) | Total |
+------+--------------------+-------+
|    1 | 笨蛋,笨笨,1,1      |     4 |
|   12 | 小本               |     1 |
|   17 | 小歡3              |     1 |
|   18 | 小環(huán)               |     1 |
|   20 | 陸小琪             |     1 |
|   21 | 燕虹               |     1 |
|   22 | 小歡2,xiaoming     |     2 |
|   26 | 小歡4              |     1 |
|   32 | 小歡1              |     1 |
|  170 | 蒼松好道人         |     1 |
|  180 | 鬼王               |     1 |
|  200 | 毒神               |     1 |
|  500 | 周一仙             |     1 |
+------+--------------------+-------+
13 rows in set (0.00 sec)
  • 數(shù)據(jù)分組 過(guò)濾

數(shù)據(jù)分組后，往往通過(guò)HAVING 關(guān)鍵字，取最大值，最小值，平均值（MAX(),MIN(),AVG(),SUM()）等對(duì)分組后的數(shù)據(jù)進(jìn)行過(guò)濾。
按年齡進(jìn)行分組，查找分組后工資最大值大于7000并且最小值小于20 的數(shù)據(jù)

mysql>  SELECT age, GROUP_CONCAT(salary) as "工資范圍" , GROUP_CONCAT(name) as "姓名范圍" ,  COUNT(*) AS Total FROM user GROUP BY age HAVING MAX(salary)>3000 AND MIN(salary)<20;
+------+--------------+---------------+-------+
| age  | 工資范圍     | 姓名范圍      | Total |
+------+--------------+---------------+-------+
|   21 | 7000,14      | 燕虹,小鬼     |     2 |
+------+--------------+---------------+-------+
1 row in set (0.00 sec)


mysql> SELECT * FROM fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| o2   |  103 | cocount    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
9 rows in set (0.00 sec)

mysql> SELECT
    -> s_id,
    -> GROUP_CONCAT(f_name) as '包含種類',
    -> GROUP_CONCAT(f_price) as '各個(gè)價(jià)格',
    -> COUNT(*) AS Total,
    -> SUM(f_price) AS '總價(jià)',
    -> AVG(f_price) AS '平均價(jià)格'
    -> FROM
    -> fruits
    -> GROUP BY s_id;
+------+-------------------------+------------------+-------+--------+--------------+
| s_id | 包含種類                | 各個(gè)價(jià)格         | Total | 總價(jià)   | 平均價(jià)格     |
+------+-------------------------+------------------+-------+--------+--------------+
|  101 | apple,blackberry,cherry | 5.20,10.20,3.20  |     3 |  18.60 |     6.200000 |
|  102 | orange,banana,grape     | 11.20,10.30,5.30 |     3 |  26.80 |     8.933333 |
|  103 | cocount                 | 9.20             |     1 |   9.20 |     9.200000 |
|  105 | melon                   | 8.20             |     1 |   8.20 |     8.200000 |
|  107 | xbababa                 | 3.60             |     1 |   3.60 |     3.600000 |
+------+-------------------------+------------------+-------+--------+--------------+
5 rows in set (0.00 sec)

GROUP BY一般常與用HAVING 對(duì)分組后的數(shù)據(jù)進(jìn)行過(guò)濾 ，滿足條件的分組才會(huì)顯示

查詢水果表按種類s_id進(jìn)行分組，并顯示每個(gè)種類分組中水果名稱大于1的數(shù)據(jù)
mysql> SELECT
    -> s_id,
    -> GROUP_CONCAT(f_name) as '包含種類',
    -> GROUP_CONCAT(f_price) as '各個(gè)價(jià)格',
    -> COUNT(*) AS Total,
    -> SUM(f_price) AS '總價(jià)',
    -> AVG(f_price) AS '平均價(jià)格'
    -> FROM
    -> fruits
    -> GROUP BY s_id
    -> HAVING COUNT(f_name)>1;
+------+-------------------------+------------------+-------+--------+--------------+
| s_id | 包含種類                | 各個(gè)價(jià)格         | Total | 總價(jià)   | 平均價(jià)格     |
+------+-------------------------+------------------+-------+--------+--------------+
|  101 | apple,blackberry,cherry | 5.20,10.20,3.20  |     3 |  18.60 |     6.200000 |
|  102 | orange,banana,grape     | 11.20,10.30,5.30 |     3 |  26.80 |     8.933333 |
+------+-------------------------+------------------+-------+--------+--------------+
2 rows in set (0.00 sec)

GROUP BY后使用WITH ROLLUP

使用WITH ROLLUP,會(huì)增加一行，顯示所有的統(tǒng)計(jì)數(shù)量
mysql> SELECT
    -> s_id,
    -> COUNT(*) AS Total
    -> FROM
    -> fruits
    -> GROUP BY s_id
    -> WITH ROLLUP
    -> ;
+------+-------+
| s_id | Total |
+------+-------+
|  101 |     3 |
|  102 |     3 |
|  103 |     1 |
|  105 |     1 |
|  107 |     1 |
| NULL |     9 |
+------+-------+
6 rows in set (0.00 sec)
  • COUNT()函數(shù)
  • count(*):計(jì)算表中總的行數(shù)，不忽略某列有數(shù)據(jù)或者是null;

  • count(字段名):計(jì)算表中總的行數(shù)，忽略為null值的行
    如下兩個(gè)查詢的不同結(jié)果：


    image.png

    image.png
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 轉(zhuǎn) # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    呂品?閱讀 10,133評(píng)論 0贊 44
  • （一）幾個(gè)數(shù)據(jù)庫(kù)相關(guān)的概念 1.數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù): 保存有組織數(shù)據(jù)的容器。 數(shù)據(jù)的所有存儲(chǔ)、檢索、管理和處理實(shí)際上是...
    快樂(lè)的小飛熊閱讀 627評(píng)論 0贊 1
  • 時(shí)間一久！再回頭看看！我們總是再過(guò)明天！好像從來(lái)沒(méi)有過(guò)過(guò)今天！我們總是覺(jué)得時(shí)間不夠～許多事情沒(méi)有做！
    54c54675dd48閱讀 169評(píng)論 0贊 0
  • 我的發(fā)尾燃起火 玫瑰燒出一朵朵 腳掌風(fēng)蝕成大地的骨骼 唇齒輕合 不知深夜的灣口 扯長(zhǎng)的白帆可否戰(zhàn)勝風(fēng)波 我的眸子幾...
    半角花鹿閱讀 276評(píng)論 0贊 0

友情鏈接更多精彩內(nèi)容