2020/11/18 刘一辰的JAVA随笔

今日目标：Mysql学习22

实验内容：

-- 17.查询班级是'95031'班学生每门课的平均分

select c_no,AVG(sc_degree) from score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no;

 

select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = '95031' GROUP BY sc.c_no;

 

SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC  WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ;

+-------+-------------------+

| c_no  | AVG(sc.sc_degree) |

+-------+-------------------+

| 3-105 |           82.0000 |

| 3-245 |           71.5000 |

| 6-166 |           80.0000 |

+-------+-------------------+

进阶,加入课程名称:

SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c  WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;

+-------+------------+-------------------+

| c_no  | c_name     | AVG(sc.sc_degree) |

+-------+------------+-------------------+

| 3-105 | 计算机导论 |           82.0000 |

| 3-245 | 操作系统   |           71.5000 |

| 6-166 | 数字电路   |           80.0000 |

+-------+------------+-------------------+