MySQL多表对多表的基本练习

– 1、查询平均成绩大于70分的同学的学号和平均成绩
– 查询每个学生的平均分
SELECT student_id,AVG(score) AS 平均分
FROM student_course GROUP BY student_id;

– 增加条件：平均成绩大于70
SELECT student_id,AVG(score) AS 平均分
FROM student_course GROUP BY student_id HAVING 平均分>70;

– 2、查询所有同学的学号、姓名、选课数、总成绩
SELECT s.id,s.name,COUNT(s.id),SUM(score)
FROM student s,student_course sc
WHERE s.id=sc.student_id GROUP BY id;

– 2.1从学生课程中间表 查询学号，课程id,成绩
SELECT student_id,course_id,score
FROM student_course;

– 2.2 根据学号分组 求出每个学生的选课数和总成绩
SELECT student_id,COUNT(course_id),SUM(score)
FROM student_course GROUP BY student_id;

– 2.3 将2.2作为临时表 和 学生表 关联查询
SELECT s.id,s.name,COUNT(course_id),SUM(score)
FROM student s LEFT OUTER JOIN student_course sc
ON s.id=sc.student_id GROUP BY s.id;

– 3、查询学过赵云老师所教课的同学的学号、姓名
SELECT s.id,s.name
FROM teacher t,course c,student s,student_course sc
WHERE t.id=c.teacher_id AND c.id=sc.course_id AND s.id=sc.student_id AND t.name=‘赵云’;

– 3.1 查询赵云老师教了那些课
SELECT course.name FROM course,teacher
WHERE teacher.name=‘赵云’ AND teacher.id=course.teacher_id;

– 3.2 查询那些学生上了赵云老师的课
SELECT student.name FROM student_course,student
WHERE student.id=student_id AND course_id IN
(SELECT course.id FROM course,teacher
WHERE teacher.name=‘赵云’ AND teacher.id=course.teacher_id);
– 3.3 根据学号查询学生信息
SELECT id,NAME FROM student_course,student
WHERE student.id=student_id AND course_id IN
(SELECT course.id FROM course,teacher
WHERE teacher.id=course.teacher_id AND teacher.name=‘赵云’);

– 4、查询没学过关羽老师课的同学的学号、姓名
– 在第三题的基础上，查询学过关羽老师教的课的学生
SELECT s.id,s.name
FROM teacher t,course c,student s,student_course sc
WHERE t.id = c.teacher_id AND c.id = sc.course_id AND s.id = sc.student_id AND t.name = “关羽”;

– 查询没学过关羽老师课的同学的学号、姓名
SELECT s.id,s.name
FROM student s
WHERE s.id NOT IN (SELECT s.id FROM teacher t,course c,student s,student_course sc WHERE t.id = c.teacher_id
AND c.id = sc.course_id AND s.id = sc.student_id AND t.name = “关羽”);

– 4.1 找到关羽的id
SELECT id FROM teacher WHERE NAME=‘关羽’;

– 4.2 查询关羽教授的课程
SELECT c.name FROM course c,teacher t WHERE t.NAME=‘关羽’ AND t.id=c.teacher_id;

– 4.3 查询那些学生上了关羽老师的课程
SELECT DISTINCT student_id FROM student_course
WHERE course_id IN (SELECT id FROM course
WHERE teacher_id=(SELECT id FROM teacher WHERE NAME=‘关羽’));

– 4.4 反向查询学号和姓名
SELECT id,NAME FROM student
WHERE id NOT IN(SELECT student_id FROM student_course
WHERE course_id IN (SELECT id FROM course
WHERE teacher_id=(SELECT id FROM teacher WHERE NAME=‘关羽’)));

– 5、查询没有学三门课以上的同学的学号、姓名
SELECT s.id,s.name FROM student s,student_course sc
WHERE s.id=sc.student_id GROUP BY student_id HAVING COUNT(course_id)<=3;

– 查询学了0,1,2,3门课的学生的成绩
SELECT SUM(score),COUNT(course_id) FROM student_course
GROUP BY student_id HAVING COUNT(course_id) IN(0,1,2,3);

– 查询学了三门课以上的同学的学号、姓名
SELECT id,NAME FROM student s,student_course sc
WHERE s.id=sc.student_id GROUP BY student_id HAVING COUNT(course_id)>3;

– 查询没有学三门课以上的同学的学号、姓名
SELECT id,NAME FROM student s,student_course sc
WHERE s.id=sc.student_id GROUP BY student_id HAVING COUNT(course_id)<=3;

– 5.1 查询每个学生学了几门课
SELECT id,COUNT(course_id) FROM student,student_course
WHERE id=student_id GROUP BY id;

– 5.2 查询每个学生学了几门课 条件1：小于等于三门
SELECT id,COUNT(course_id) FROM student,student_course
WHERE id=student_id GROUP BY id HAVING COUNT(course_id)<=3;

– 5.3 查询 学号和姓名， 将5.2 作为临时表
SELECT id,NAME FROM student, (SELECT student_id, COUNT(course_id)
FROM student_course GROUP BY student_id HAVING COUNT(course_id)<=3) temp
WHERE student.id=temp.student_id;

– 6、查询各科成绩最高分和最低分
– 根据学科分组 最高分和最低分
SELECT course_id,MAX(score),MIN(score)
FROM student_course
GROUP BY course_id;