MySQL多表查询实例

• 查询班级名称，和班级总人数

SELECT c.cname,COUNT(*) FROM classes c,student s WHERE c.cid = s.cno GROUP BY c.cname;

• 查询学生的姓名和学生所选的总课程平均成绩。

select s.sname,avg(sc.score) from student s,stu_cour sc where s.sid = sc.sno group by s.sname;

• 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。

select s.sname,count(*) from student s,stu_cour sc where s.sid = sc.sno group by s.sname having count(*) > 2;

• 查询平均成绩大于80分的学生的总数。

select count(*) from student s where s.sid in (SELECT sc.sno FROM stu_cour sc GROUP BY sc.sno HAVING AVG(sc.score) > 80);

• 查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的评价成绩。

SELECT s.sname,AVG(sc.score) FROM student s,stu_cour sc WHERE s.sid = sc.sno GROUP BY s.sname HAVING AVG(sc.score) > ANY(SELECT AVG(sc.score) FROM student s,stu_cour sc,classes c WHERE s.sid = sc.sno AND s.cno = c.cname AND c.cname ='01班' GROUP BY s.sname);