mysql查询必练50题

摘要： -- 1、查询“01”课程比“02”课程成绩高的所有学生的学号； SELECT a.sid FROM (SELECT sid, score FROM sc WHERE cid=01) a, (SELECT sid ,score FROM sc WHERE cid=02) b WHERE a.score > b.score AND a.sid= b.sid; SELECT a.sid FROM student a LEFT JOIN SC b ON b.cid=01 AND a.sid=b.sid LEFT JOIN SC c ON c.cid=02 AND a.sid=c.sid WHERE b.score>c.score; -- 2、查询平均成绩大于60分的同学的学号和平均成绩； SELECT sid 学号, AVG(score) 平均成绩 FROM sc GROUP BY sid HAVING 平均成绩 > 60; SELECT sid, AVG(score) FROM sc GROUP BY sid HAVING AVG(score)>60; -- 3、查询所有同学的学号、姓名、选课数、总成绩； SELECT s.sid 学号, s.sname 姓名, COUNT(*) 选棵数, SUM(score) 总成绩 FROM student s, sc WHERE s.sid = sc.sid GROUP BY sc.sid; SELECT s.sid, sname, COUNT(*), SUM(score) FROM student s LEFT JOIN sc ON s.sid = sc.sid GROUP BY sc.sid;