大家好，我是anyux。数据库联表查询很重要，内联查询更是尤为重要。

需要将school.sql文件导入到数据库中

数据库多表联合查询，school库下表的逻辑结构关系都在下图中。可以通过驱动表加子表配合实现所有查询要求。

统计zhang3,学习了几门课

select STU.sname as 姓名, count(SC.sno) as 数量 from student as STU join sc as SC on STU.sno= SC.sno where STU.sname='zhang3';

查询zhang3,学习的课程名称有哪些?

方法1

select STU.sname as 姓名, COU.cname as 课程名  from student as STU join sc as SC on STU.sno = SC.sno join course as COU on SC.cno = COU.cno where STU.sname='zhang3';

方法2(推荐)

select STU.sname as 姓名, group_concat(COU.cname) as 课程名  from student as STU join sc as SC on STU.sno = SC.sno join course as COU on SC.cno = COU.cno where STU.sname='zhang3' group by STU.sname;

查询KING老师教的学生名.

select TEA.tname as 教师名称, group_concat(STU.sname) as 学生名称 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where TEA.tname='KING' group by TEA.tname;

查询KING所教课程的平均分数

select COU.cname as 课程名称,TEA.tname as 教师名称, avg(SC.score) as 课程成绩 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno = COU.cno where TEA.tname='KING' group by COU.cname,TEA.tname;

每位老师所教课程的平均分,并按平均分排序

select TEA.tname as 教师名称, COU.cname as 课程名称, avg(SC.score) as 平均成绩  from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno group by TEA.tname, COU.cname order by AVG(SC.score) DESC;

查询KING所教的不及格的学生姓名

select STU.sname as 学生姓名 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where TEA.tname='KING' and SC.score<60;

查询所有老师所教学生不及格的信息

方法1

select TEA.tname as 教师名称,STU.sname as 学生姓名,SC.score as 学生成绩 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where SC.score<60;

方法2(推荐,显示优化)

select TEA.tname as 教师名称,group_concat(concat(STU.sname,":",SC.score)) as 不及格的 from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno join student as STU on STU.sno=SC.sno where SC.score<60 group by TEA.tno;

查询平均成绩大于60分的同学的学号和平均成绩；

select STU.sno as 学号, avg(SC.score) as 平均成绩 from student as STU join sc as SC on STU.sno=SC.sno group by STU.sno having avg(SC.score)>60;

查询所有同学的学号、姓名、选课数、总成绩；

select STU.sno as 学号, STU.sname as 姓名, count(SC.cno) as 选课数, sum(SC.score) as 总成绩  from student as STU join sc as SC on STU.sno=SC.sno group by STU.sno ;

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select sc.cno as 课程ID, max(sc.score) as 最高分,min(sc.score) as 最低分 from sc group by sc.cno;

统计各位老师,所教课程的及格率

select TEA.tname as 教师名称,COU.cname as 课程名称,concat((select count(sc.cno) from sc where sc.score>60)/(select count(sc.cno) from sc)*100,'%') as 及格率  from teacher as TEA join course as COU on TEA.tno=COU.tno join sc as SC on SC.cno=COU.cno group by TEA.tno,COU.cname;

查询每门课程被选修的学生数

select COU.cno as 课程名称, count(STU.sno) as 学生数量 from student as STU join sc as SC on STU.sno=SC.sno join course as COU on SC.cno=COU.cno group by COU.cno;

查询出只选修了一门课程的全部学生的学号和姓名

select STU.sno as 学号,STU.sname as 学生名称,count(SC.cno) as 选修课程数量 from student as STU join sc as SC on STU.sno=SC.sno group by SC.sno having count(SC.sno)=1;

查询选修课程门数超过1门的学生信息

select STU.sno as 学号,STU.sname as 学生名称,count(SC.cno) as 选修课程数量 from student as STU join sc as SC on STU.sno=SC.sno group by SC.sno having count(SC.sno)!=1;

统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

select STU.sname as 学生名称,STU.sno as 学号,COU.cname as 课程名称, case when SC.score>85 then '优秀' when SC.score>70 and SC.score<=85 then '良好' when SC.score>=60 and SC.score<=70 then '一般' when SC.score<60 then '不及格' end as 成绩状态 from student as STU join sc as SC on STU.sno=SC.sno join course as COU on SC.cno=COU.cno group by COU.cno,STU.sno,STU.sname,SC.score;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select STU.sno as 学号,STU.sname as 学生名称, avg(SC.score) as 平均成绩 from student as STU join sc as SC on STU.sno=SC.sno group by SC.score,STU.sname,STU.sno having avg(SC.score)>85;