mysql复习第五天：分组筛选

分手后的思念是犯贱 2023-08-17 16:39 172阅读 0赞

### 1、分组查询 group by ###

*  数据表：  
    ![在这里插入图片描述][20190929101420184.png]  
    ![在这里插入图片描述][2019092910144055.png]

> select 列名 from 表 group by 要分组的列名

SELECT MAX(score),s_sex,s_name FROM Student GROUP BY s_sex;

![在这里插入图片描述][20190929101345561.png]

> select 分组函数,列(要求出现在group by的后面) from 表 \[where 刷选条件\] group by 分组列表 \[order by 字句|别名|函数 \]

SELECT 
    ROUND(AVG(s.score),1) AS '平均分',
    c.classname 
    FROM Student s,class c 
    WHERE s.class_id = c.classid 
    GROUP BY s.class_id 
    ORDER BY '平均分';

![在这里插入图片描述][20190929101618315.png]

#### 1.1、按多个字段分组 ####

SELECT 
    ROUND(AVG(score),1) AS 'avgscore',
    Class_id,
    s_sex 
    FROM Student 
    GROUP BY Class_id,s_sex 
    ORDER BY avgscore;

![在这里插入图片描述][20190929102649589.png]

### 2、筛选 ###

> select 分组函数,列(要求出现在group by的后面) from 表 \[where 刷选条件\] group by 分组列表 \[having > 筛选条件字句|别名|函数\] \[order by 字句|别名|函数 \]

SELECT 
    ROUND(AVG(s.score),1) AS '平均分',
    c.classname 
    FROM 
    Student s,class c 
    WHERE s.class_id = c.classid 
    GROUP BY s.class_id 
    HAVING ROUND(AVG(s.score),1) > 80 
    ORDER BY ROUND(AVG(s.score),1) ;

![在这里插入图片描述][20190929102051452.png]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>数据源</th> 
   <th>位置</th> 
   <th>关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>分组前刷选</td> 
   <td>原始表</td> 
   <td>group by字句的前面</td> 
   <td>where</td> 
  </tr> 
  <tr> 
   <td>分组后刷选</td> 
   <td>分组后的结果集</td> 
   <td>group by字句的后面</td> 
   <td>having</td> 
  </tr> 
 </tbody> 
</table>

[20190929101420184.png]: /images/20230809/9016df2769e84de6a9bda72dc8ecbe30.png
[2019092910144055.png]: /images/20230809/4b5a136b01834616a1a41bc897fd6da0.png
[20190929101345561.png]: /images/20230809/212be1511c8142ed90ccf6e959f603e7.png
[20190929101618315.png]: /images/20230809/9258edeb01324d7c9f23ff051c704695.png
[20190929102649589.png]: /images/20230809/c60db38201714b518ae26aec067d2a10.png
[20190929102051452.png]: /images/20230809/a7337c06423243cd8f22f19f944c6671.png