根据上一篇文章建立的表，我们来做一些多表练习：

没建立表的可以点击此链接去建立练习用的表：

目录

1.查询“1”号学生的姓名和各科成绩：

2.查询各个学科的平均成绩和最高成绩：

3.查询所有姓张的同学的各科成绩：

4.查询每个同学的最高成绩和科目名称

5.查询每个课程的最高分的学生信息

6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。

7.查询平均成绩大于70的同学的信息。（子查询）

8.将学生按照总分数进行排名。（从高到低）

9.查询数学成绩的最高分、最低分、平均分。

10.将各科目按照平均分排序。

11.查询老师的信息和他所带的科目的平均分

12.查询被"Tom"和"Jerry"教的课程的最高分和最低分

13.查询每个学生的最好成绩的科目名称（子查询）

14.查询所有学生的课程及分数

15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）

16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩

17.查询有不及格课程的学生信息

18.查询每门课程有成绩的学生人数

19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列

20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩

21.查询有且仅有一门课程成绩在80分以上的学生信息

22.查询出只有三门课程的学生的学号和姓名

23.查询有不及格课程的课程信息

24.查询至少选择4门课程的学生信息

25.查询没有选全所有课程的同学的信息

26.查询选全所有课程的同学的信息 

27.查询各学生都选了多少门课

28.查询课程名称为"java"，且分数低于60分的学生姓名和分数

29.查询学过"Tony"老师授课的同学的信息

30.查询没学过"Tony"老师授课的学生信息

---

1.查询“1”号学生的姓名和各科成绩：

进行student表和scores表的id相连接，course表和scores表的id相连接

SELECTs.id sid,s.`name` sname,c.`name` cname,sc.score FROMstudent sLEFT JOIN scores sc ON s.id = sc.s_idLEFT JOIN course c ON c.id = sc.c_id WHEREs.id = 1;

2.查询各个学科的平均成绩和最高成绩：

SELECTc.id,c.`name`,AVG( sc.score ),max( sc.score ) FROMcourse cLEFT JOIN scores sc ON c.id = sc.c_id GROUP BYc.id,c.`name`;

3.查询所有姓张的同学的各科成绩：

SELECTs.id,s.`name`,c.`name` cname,sc.score FROMstudent sLEFT JOIN scores sc ON sc.s_id = s.idLEFT JOIN course c ON c.id = sc.c_id WHEREs.`name` LIKE '张%';

4.查询每个同学的最高成绩和科目名称

SELECTt.id,t.NAME,c.id,c.NAME,r.score FROM(SELECTs.id,s.NAME,(SELECTmax( score ) FROMscores r WHEREr.s_id = s.id ) score FROMstudent s ) tLEFT JOIN scores r ON r.s_id = t.id AND r.score = t.scoreLEFT JOIN course c ON r.c_id = c.id;

5.查询每个课程的最高分的学生信息

SELECT* FROMstudent s WHEREid IN (SELECT DISTINCTr.s_id FROM(SELECTc.id,c.NAME,max( score ) score FROMstudent sLEFT JOIN scores r ON r.s_id = s.idLEFT JOIN course c ON c.id = r.c_id GROUP BYc.id,c.NAME ) tLEFT JOIN scores r ON r.c_id = t.id AND t.score = r.score );

6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。

SELECTs.id,s.NAME sname,sc.score,c.NAME FROMstudent sLEFT JOIN scores sc ON s.id = sc.s_idLEFT JOIN course c ON sc.c_id = c.id WHEREs.NAME LIKE '%张%' OR s.NAME LIKE '%李%';

7.查询平均成绩大于70的同学的信息。（子查询）

SELECT* FROMstudent WHEREid IN (SELECTsc.s_id FROMscores sc GROUP BYsc.s_id HAVINGavg( sc.score ) >= 70 );

8.将学生按照总分数进行排名。（从高到低）

SELECTs.id,s.NAME,sum( sc.score ) score FROMstudent sLEFT JOIN scores sc ON s.id = sc.s_id GROUP BYs.id,s.NAME ORDER BYscore DESC,s.id ASC;

9.查询数学成绩的最高分、最低分、平均分。

SELECTc.NAME,max( sc.score ),min( sc.score ),avg( sc.score ) FROMcourse cLEFT JOIN scores sc ON c.id = sc.c_id WHEREc.NAME = '数学';

10.将各科目按照平均分排序。

SELECTc.id,c.NAME,avg( sc.score ) score FROMcourse cLEFT JOIN scores sc ON c.id = sc.c_id GROUP BYc.id,c.NAME ORDER BYscore DESC;

11.查询老师的信息和他所带的科目的平均分

SELECTt.id,t.NAME,c.id cid,c.NAME cname,avg( r.score ) FROMteacher tLEFT JOIN course c ON t.id = c.t_idLEFT JOIN scores r ON r.c_id = c.id GROUP BYt.id,t.NAME,c.id,c.NAME;

12.查询被"Tom"和"Jerry"教的课程的最高分和最低分

SELECTt.id,t.NAME,c.id cid,c.NAME cname,max( r.score ),min( r.score ) FROMteacher tLEFT JOIN course c ON t.id = c.t_idLEFT JOIN scores r ON r.c_id = c.id GROUP BYt.id,t.NAME,c.id,c.NAME HAVINGt.NAME IN ( 'Tom', 'Jerry' );

13.查询每个学生的最好成绩的科目名称（子查询）

SELECTt.id,t.sname,r.c_id,c.NAME,t.score FROM(SELECTs.id,s.NAME sname,max( r.score ) score FROMstudent sLEFT JOIN scores r ON r.s_id = s.id GROUP BYs.id,s.NAME ) tLEFT JOIN scores r ON r.s_id = t.id AND r.score = t.scoreLEFT JOIN course c ON r.c_id = c.id;

 

14.查询所有学生的课程及分数

SELECTs.id,s.NAME,c.id,c.NAME,r.score FROMstudent sLEFT JOIN scores r ON s.id = r.s_idLEFT JOIN course c ON c.id = r.c_id;

15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）

SELECTs.*,r.* FROMstudent sLEFT JOIN scores r ON s.id = r.s_id WHEREr.c_id = 1 AND r.score > 60

16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩

SELECTs.id,s.NAME,t.score FROMstudent sLEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id WHEREt.score >= 70;

17.查询有不及格课程的学生信息

SELECT* FROMstudent s WHEREid IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );

18.查询每门课程有成绩的学生人数

SELECTc.id,c.NAME,count(*) FROMcourse cLEFT JOIN scores r ON c.id = r.c_id GROUP BYc.id,c.NAME;

19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列

SELECTc.id,c.NAME,avg( score ) score FROMcourse cLEFT JOIN scores r ON c.id = r.c_id GROUP BYc.id,c.NAME ORDER BYscore DESC,c.id ASC;

20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩

SELECTs.id,s.NAME sname,avg( r.score ) score FROMstudent sLEFT JOIN scores r ON r.s_id = s.idLEFT JOIN course c ON c.id = r.c_id GROUP BYs.id,s.NAME HAVINGscore > 65;

21.查询有且仅有一门课程成绩在80分以上的学生信息

SELECTs.id,s.NAME,s.gender FROMstudent sLEFT JOIN scores r ON s.id = r.s_id WHEREr.score > 80 GROUP BYs.id,s.NAME,s.gender HAVINGcount(*) = 1;

22.查询出只有三门课程的学生的学号和姓名

SELECTs.id,s.NAME,s.gender FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME,s.gender HAVINGcount(*) = 3;

23.查询有不及格课程的课程信息

SELECT* FROMcourse c WHEREid IN (SELECTr.c_id FROMscores r GROUP BYr.c_id HAVINGmin( r.score ) < 60 );

24.查询至少选择4门课程的学生信息

SELECTs.id,s.NAME FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME HAVINGcount(*) >= 4;

25.查询没有选全所有课程的同学的信息

SELECT* FROMstudent WHEREid IN (SELECTr.s_id FROMscores r GROUP BYr.s_id HAVINGcount(*) != 5);

26.查询选全所有课程的同学的信息 

SELECTs.id,s.NAME,count(*) number FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME HAVINGnumber = ( SELECT count(*) FROM course );

27.查询各学生都选了多少门课

SELECTs.id,s.NAME,count(*) number FROMstudent sLEFT JOIN scores r ON s.id = r.s_id GROUP BYs.id,s.NAME;

28.查询课程名称为"java"，且分数低于60分的学生姓名和分数

SELECTs.id,s.NAME,r.score FROMstudent sLEFT JOIN scores r ON s.id = r.s_idLEFT JOIN course c ON r.c_id = c.id WHEREc.NAME = 'java' AND r.score < 60;

29.查询学过"Tony"老师授课的同学的信息

SELECTs.id,s.NAME FROMstudent sLEFT JOIN scores r ON r.s_id = s.idLEFT JOIN course c ON c.id = r.c_idLEFT JOIN teacher t ON t.id = c.t_id WHEREt.NAME = 'Tom';

30.查询没学过"Tony"老师授课的学生信息

SELECT* FROMstudent WHEREid NOT IN (SELECT DISTINCTs.id FROMstudent sLEFT JOIN scores r ON r.s_id = s.idLEFT JOIN course c ON c.id = r.c_idLEFT JOIN teacher t ON t.id = c.t_id WHEREt.NAME = 'Tom' )

 

来源地址：https://blog.csdn.net/weixin_49627122/article/details/126380916