运维丨MySQL数据库分组查询group by语句详解

1

2

3

4

5

6

7

8

9

10

11

12

1 SELECT

 ...

2 FROM

...

3 WHERE

...

4 GROUP BY

...

5 HAVING

...

6 ORDER BY

...

1

2

3

4

5

                数据源          位置            关键字

WHERE           原始表      ORDER BY语句之前     WHERE

HAVING      分组后的结果集   ORDER BY语句之后    HAVING

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

#1.查询每个班学生的最大年龄

 SELECT

       MAX(age),class

  FROM

       STU_CLASS

  GROUP BY CLASS;

# 2.查询每个班中男女生的最大年龄

 SELECT

         MAX(age)，class,sex

  FROM

        STU_CLASS

   GROUP BY

        class,sex;

#3.查询每个班中男女生的平局年龄，且平均年龄大于15岁

SELECT

       AVG（age）,class,sex

FROM

       STU_CLASS

GROUP BY

       class,sex

 HAVING

        avg(age) > 15;