学习地址：一天学会 MySQL 数据库【https://www.bilibili.com/video/BV1Vt411z7wy】 

 

MySQL安装教程：https://blog.csdn.net/weixin_44949135/article/details/106661080

1. 🚀01-03：【笔记01】https://blog.csdn.net/weixin_44949135/article/details/105505630
2. 🚀04-15：【笔记02】https://blog.csdn.net/weixin_44949135/article/details/106686808
3. 🚀16-21：【笔记03】https://blog.csdn.net/weixin_44949135/article/details/106694730
4. 🚀22-29：【笔记04】https://blog.csdn.net/weixin_44949135/article/details/106728980
5. 🚀30-54：【笔记05】https://blog.csdn.net/weixin_44949135/article/details/106731538
6. 🚀55-64：【笔记06】https://blog.csdn.net/weixin_44949135/article/details/106735501

MySQL专栏：https://blog.csdn.net/weixin_44949135/category_10101442.html

目   录

22-查询练习-分组计算平均成绩

11、查询每门课的平均成绩

23-查询练习-分组条件与模糊查询

12、查询score表中至少有2名学生选修的并以3开头的课程的平均分数。 

24-查询练习-范围查询的两种方式

13、查询分数大于70，小于90的sno列。

25-查询练习-多表查询

14、查询所有学生的sname、cno和degree列。

26-查询练习-多表查询

15、查询所有学生的cname、sno和degree列。

27-查询练习-三表关联查询

16、查询所有学生的sname、cname和degree列。

28-查询练习-子查询 加 分组求平均分

17、查询”95031″班学生每课的平均分。

29-查询练习-子查询

18、查询选修“3-105″课程的成绩高于“109”号同学“3-105″成绩的所有同学的记录。

22-查询练习-分组计算平均成绩

11、查询每门课的平均成绩

 select * from course;  select avg(degree) from score where cno= '3-105'; select avg(degree) from score where cno= '3-245'; select avg(degree) from score where cno= '6-166'; select avg(degree) from score where cno= '9-888';  select degree from score where cno= '3-105'; select degree from score where cno= '9-888';  select avg(degree) from score group by cno; select cno, avg(degree) from score group by cno;

23-查询练习-分组条件与模糊查询

12、查询score表中至少有2名学生选修的并以3开头的课程的平均分数。 

having 和 group by 配合使用，目的是过滤分组中的数据。

having子句，在聚合后 对组记录进行筛选。

 select cno from score group by cno;  select cno from score group by cno having count(cno) >= 2;  select cno from score group by cno having count(cno) >= 4;  select cno from score group by cno having count(cno) >= 2 and cno like '3%';  select cno, avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%';  select cno, avg(degree), count(*) from score group by cno having count(cno) >= 2 and cno like '3%';

24-查询练习-范围查询的两种方式

13、查询分数大于70，小于90的sno列。

 select sno, degree from score where degree > 70 and degree < 90; /*开区间：(70, 90) */  select sno, degree from score where degree between 70 and 90;    /*闭区间：[70, 90] */

25-查询练习-多表查询

14、查询所有学生的sname、cno和degree列。

 select sname from student;  select cno, degree from score;  /*----------------加上sno----------------*/  select sno, sname from student;  select sno, cno, degree from score;  /*----------------多表连接查询----------------*/  select sname, cno, degree from student, score where student.sno = score.sno;

26-查询练习-多表查询

15、查询所有学生的cname、sno和degree列。

 select cno, cname from course;  select cno, sno, degree from score;  select cname, sno, degree from course, score  where course.cno = score.cno;

27-查询练习-三表关联查询

16、查询所有学生的sname、cname和degree列。

sname -> student
cname -> course
degree -> score

共同字段

 select sname, cname, degree from student, course, score  where student.sno = score.sno  and course.cno = score.cno;   select sname, cname, degree, student.sno, course.cno from student, course, score  where student.sno = score.sno  and course.cno = score.cno;   select sname, cname, degree, student.sno as stu_sno,  course.cno as cou_cno from student, course, score  where student.sno = score.sno  and course.cno = score.cno;   select sname, cname, degree, student.sno as stu_sno, score.sno, score.cno  course.cno as cou_cno from student, course, score  where student.sno = score.sno  and course.cno = score.cno;

28-查询练习-子查询 加 分组求平均分

17、查询”95031″班学生每课的平均分。

 select * from student where class = '95031';  select sno from student where class = '95031';  select * from score where sno in ( select sno from student where class = '95031' );  select cno, avg(degree) from score where sno in (select sno from student where class='95031' ) group by cno; 

29-查询练习-子查询

18、查询选修“3-105″课程的成绩高于“109”号同学“3-105″成绩的所有同学的记录。

题意是：“3-105”这门课程里面，109号同学成绩为x，成绩>x的其他同学的记录。

 select degree from Score where sno = '109' and cno = '3-105' ;  select * from Score where degree > ( select degree from Score where sno = '109' and cno = '3-105' );  select * from Score where degree > ( select degree from Score where sno = '109' and cno = '3-105' ) and cno = '3-105';