MySQL資料庫— 匯總和分組資料

標籤：core   ble   彙總函式   time   left join   直接   平均值   倒序   bsp   

一 匯總和分組資料

查詢語句 ---> 結果集（多條資料） ---> 彙總函式  ----> 單行記錄

1.常用的彙總函式：

sum()         數字                          對指定列中的所有非空值求總和

avg()          數字                          對指定列中的所有非空值求平均值

min()    數字，字元，datetime        返回指定列中的最小數字，最早的日期或者最小的字串

max()   數字，字元，datetime        返回指定列中的最大數字，最近的日期或者最大的字元集

count()   任意基於行的資料類型       統計結果集合眾全部記錄行的數量

 

例：查詢玩家表中一共有多少名玩家資訊

select count (user_qq) from users

select count(*) from users

例：查詢QQ號是12301的玩家遊戲的總分數

select sum(score) as ‘總分數‘ from scores where user_qq=‘12301‘

例：查詢QQ號是12302玩家的評價分數

select avg(score) as ‘平均分數’ from scores where user_qq=‘12302‘

例：查詢遊戲編號的1的最高分數

select max(score) as ‘最高分數‘ from score where gno=1

例： 查詢QQ號是12302的玩家的總分數，平均分數和最高分數

select sum(score) as ‘總分‘,avg(score) as ‘平均分‘,max(score) as ‘最高分‘ from scores where user_qq =‘12302‘

 

2. 使用GROUP BY分組

例：查詢每個玩家的總分數，平均分數，最高分數

select sum(score) as ‘總分‘,avg(score) as ‘平均分‘,max(score) as ‘最高分‘ from scores group by user_qq

例： 查詢每個玩家的平均分數，並顯示玩家QQ號和平均分數

select user_qq, avg(score) as ‘平均分數‘ from scores group by user_qq

 

3. 篩選分組結果

  在使用GROUP BY子句時，可用HAVING子句為分組統計進一步設定統計條件，HAVING子句與GROUP BY 子句的關係相當於WHERE子句與SELECT子句之間的關係

 與WHERE子句的區別是，在HAVING子句中是以彙總函式的統計結果為篩選條件。

 例：查詢平均分數大於4000的玩家QQ號，總分數，平均分數

select user_qq, sum(score) as‘總分數‘, avg(score) as ‘平均分數‘ from scores group by user_qq having avg(score) > 4000

例：查詢所有使用者的平均分數，和總分數，並按平均分數倒序排列

select user_qq,avg(score) as ‘平均分數‘ , sun(score) as ‘總分數‘ from scores group by user_qq orde by avg(score) desc

 

4.SELECT 語句的執行順序

     from 子句指定資料來源

     where 子句基於指定的條件對記錄進行篩選

     group by 子句將資料劃分為多個分組

     使用彙總函式進行計算

     使用having子句篩選分組

     使用order by 子句對結果集進行排序

 

二 串連查詢

1. 多表串連

例：查詢分數資訊，顯示玩家暱稱，遊戲名稱和分數

select user_name as ‘暱稱‘, game as ‘遊戲名稱‘ , score as ‘分數‘ from users.user_qq = scores.user_qq and game.gno= scores.gno

串連查詢分為內串連和外串連兩種

內串連特點：相串連的兩張表地位平等

                 如果一張表中在另一張表中不存在對應資料，則不做串連

                 from 子句後面直接出現多個表名，這種串連方式即屬於內串連，是隱式內串連

                 顯示內串連格式：select col_list from table1[inner] join table2 on table1.col=table2.clo1

                 例：查詢分數資訊，顯示玩家暱稱，遊戲名稱和分數

                 select user_name as ‘暱稱‘, g_name as ‘遊戲名稱‘, score as ‘分數‘ from games inner join scores on games.gno =scores.gno

                 inner join users on score.user_qq=user.user_qq

                 例：查詢每個玩家的暱稱，總分和平均分

                    select user_name as ‘暱稱‘,sum(score) as ‘總分‘,avg(score) as ‘平均分‘ from users U inner join scores S on S.user_qq = U.user_qq group by                         U.user_qq,user_name

                 例：查詢平均分數大於3500的分數資訊，顯示玩家暱稱，總分數，平均分數，並按照平均分數降序排列

                  select user_name as ‘暱稱‘,sum(score) as ‘總分‘,avg(score) as ‘平均分‘ from users U inner join scores S on S.user_qq = U.user_qq group by                         U.user_qq,user_name having avg(score)>3500 order by avg(score) desc

外串連分為左串連和右外串連

外串連特點：做串連的兩個表地位不平等，其中有一張的基礎資料表

                 基礎資料表中的每條資料必須出現，即使另一張表中沒有資料與之匹配，也要用NULL補齊

                 左外串連時左表是基礎資料表，右表外串連時右表是基礎資料表

                 語句中先出現的表為‘左表‘, 後出現的表為‘右表‘

外串連格式： SELECT COL_LIST FROM TABLE1 LEFT/RIGHT[OUTER] JOIN TABLE2 ON TABLE1.COL=TABLE2.COL

 例：查詢所有玩家關於5號遊戲的分數資訊

   select user_name as‘暱稱‘ gno as ‘遊戲編號‘, score as ‘分數‘ from users U left join scores S on U.user_qq=S.user_qq and S.gno=5

MySQL資料庫— 匯總和分組資料