mysql多表操作例题_MySQL多表查询练习题

1、查询没有学全所有课的同学的学号、姓名；#先统计一共有多少门课程

select count(cid)fromcourse;#查看每个学生选择的课程书

select count(course_id)fromscore group by student_id;#查询所学课程数小于总课程数的学生学号

select student_idfrom (select count(course_id) c_course_id,student_id fromscore group by student_id) t1

where t1.c_course_id< (select count(cid) fromcourse) ;#查询没有学全所有课的同学的学号、姓名；

select sid,snamefrom student where sid in(

select student_idfrom (select count(course_id) c_course_id,student_id fromscore group by student_id

) t1 where t1.c_course_id< (select count(cid) fromcourse)

) ;2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；#先查询2号同学学了哪些课程

select* from score where student_id =2;#找到学习了2号同学没学习课程的所有同学(找到所有和2号同学学习的课程不一样的同学)

select student_idfrom score where course_id not in (select course_id from score where student_id=2)#找到score表中所有的学生并且把 2号同学 以及(和2号同学学习的课程不一样的同学)排除出去

select student_idfrom score where student_id not in (select student_id from score where course_id not in (select course_id from score where student_id=2)) and student_id !=2

#对剩余的和2号同学所选课程没有不同的同学所选课程数进行统计，如果和2号同学的课程数相同，就是选择了相同的课程

select student_idfrom score where student_id not in(

select student_idfrom score where course_id not in (select course_id from score where student_id=2)

)and student_id !=2group by student_id

having count(course_id)= (select count(course_id) from score where student_id=2);3、删除学习“叶平”老师课的SC(score)表记录；#先查出李平老师的id

select tidfrom teacher where tname = '李平老师';#查看李平老师所教授的课程

select cidfrom course where teacher_id = (select tid from teacher where tname = '李平老师’);

#查看李平老师所教课程的成绩数据

select* from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'));#执行删除命令

deletefrom score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'));4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；#先找寻上过2号课程的同学

select student_idfrom score where course_id = 2;#再找到没上过2号课程的所有同学

select* from student where sid not in (select student_id from score where course_id = 2);#计算出学习2号课程的同学的平均成绩

select avg(num)from score where course_id = 2group by course_id;#用笛卡尔积将上述两个表拼起来

select* f