mysql查询详细例子_MySQL -- 查询案例（一）

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号

SELECT A.sid FROM

(SELECT sid , score from sc WHERE cid = 001) A,

(SELECT sid , score from sc WHERE cid = 002) B

WHERE (A.score > B.score) AND (A.sid = B.sid);

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

SELECT sid , AVG(score) FROM sc

GROUP BY sid

HAVING AVG(score) > 60;

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

SELECT sc.sid , sname , sc.cid , SUM(score) from SCINNER JOIN student

ON sc.sid = student.sid

GROUP BY sid;

4、查询姓“刘”的老师的个数

SELECT COUNT(tName) FROM teacher

WHERE tName LIKE("刘%");

5、查询没学过“李老师”课的同学的学号、姓名

SELECT sid , sname from student

WHERE sid NOT IN

(SELECT sid from sc WHERE cid IN

(SELECT cid from course WHERE tid IN

(SELECT tid from teacher WHERE tName = '李老师')))；

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名