【6】查询练习：Group by 分组

1.查询每门课的平均成绩：

mysql> select * from course;
+----------+-----------+---------+
| cour_num | cour_name | tea_num |
+----------+-----------+---------+
| 1-245    | Math      | 0438    |
| 2-271    | Circuit   | 0435    |
| 3-105    | OS        | 0435    |
| 4-321    | Bio       | 0436    |
+----------+-----------+---------+

mysql> select * from score;
+---------+----------+--------+
| stu_num | cour_num | degree |
+---------+----------+--------+
| 11422   | 3-105    |     92 |
| 11423   | 1-245    |     84 |
| 11423   | 2-271    |     75 |
| 11424   | 4-321    |     75 |
| 11425   | 2-271    |     89 |
| 11426   | 1-245    |     61 |
| 11426   | 2-271    |     82 |
| 11427   | 1-245    |     78 |
+---------+----------+--------+

mysql> select avg(degree) from score where cour_num='1-245';
+-------------+
| avg(degree) |
+-------------+
|     74.3333 |
+-------------+

求平均：avg（）；

mysql> select avg(degree) from score where cour_num='2-271';
+-------------+
| avg(degree) |
+-------------+
|     82.0000 |
+-------------+

mysql> select avg(degree) from score where cour_num='3-105';
+-------------+
| avg(degree) |
+-------------+
|     92.0000 |
+-------------+

mysql> select avg(degree) from score where cour_num='4-321';
+-------------+
| avg(degree) |
+-------------+
|     75.0000 |
+-------------+

在同一条语句中计算4门课程平均值？

mysql> select avg(degree) from score group by cour_num;
+-------------+
| avg(degree) |
+-------------+
|     74.3333 |
|     82.0000 |
|     92.0000 |
|     75.0000 |
+-------------+

mysql> select cour_num,avg(degree) from score group by cour_num;
+----------+-------------+
| cour_num | avg(degree) |
+----------+-------------+
| 1-245    |     74.3333 |
| 2-271    |     82.0000 |
| 3-105    |     92.0000 |
| 4-321    |     75.0000 |
+----------+-------------+

group by：先按课程号分组，分组之后计算平均值。

2.计算score表中至少有2名学生选修的并以2开头的课程的平均分数：

按课程号分组显示：

mysql> select cour_num from score group by cour_num;
+----------+
| cour_num |
+----------+
| 1-245    |
| 2-271    |
| 3-105    |
| 4-321    |
+----------+

至少有2名同学选修：group by 后跟 having

mysql> select cour_num from score group by cour_num
    -> having count(cour_num)>=2;
+----------+
| cour_num |
+----------+
| 1-245    |
| 2-271    |
+----------+

并且要以2开头：like模糊查询

mysql> select cour_num from score group by cour_num
    -> having count(cour_num)>=2 and cour_num like '2%';
+----------+
| cour_num |
+----------+
| 2-271    |
+----------+

计算出平均值：avg

mysql> select cour_num,avg(degree) from score group by cour_num
    -> having count(cour_num)>=2 and cour_num like '2%';
+----------+-------------+
| cour_num | avg(degree) |
+----------+-------------+
| 2-271    |     82.0000 |
+----------+-------------+

也可知道选修的学生数：count

mysql> select cour_num,avg(degree),count(*) from score group by cour_num
    -> having count(cour_num)>=2 and cour_num like '2%';
+----------+-------------+----------+
| cour_num | avg(degree) | count(*) |
+----------+-------------+----------+
| 2-271    |     82.0000 |        3 |
+----------+-------------+----------+

 3.查询分数大于70，小于90的stu_num列：

mysql> select stu_num,degree from score where degree>70 and degree<90;
+---------+--------+
| stu_num | degree |
+---------+--------+
| 11423   |     84 |
| 11423   |     75 |
| 11424   |     75 |
| 11425   |     89 |
| 11426   |     82 |
| 11427   |     78 |
+---------+--------+

mysql> select stu_num,degree from score where degree between 70 and 90;
+---------+--------+
| stu_num | degree |
+---------+--------+
| 11423   |     84 |
| 11423   |     75 |
| 11424   |     75 |
| 11425   |     89 |
| 11426   |     82 |
| 11427   |     78 |
+---------+--------+