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* from (select sid from student where sid not in (select student_id from score where course_id = 2)) t1,(select avg(num) from score where course_id = 2group by course_id) t2;#向SC表中插入记录

insert into score (course_id,student_id,num) select2,t1.sid,t2.avg_num from (select sid from student where sid not in (select student_id from score where course_id = 2)) t1,(select avg(num) avg_num from score where course_id = 2group by course_id) t2;5、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；#查看每个学生的数学成绩

select student_id,numfrom score where course_id = (select cid from course where cname = '数学');#查看每个学生的语文成绩

select student_id,numfrom score where course_id = (select cid from course where cname = '语文');#查看每个学生的英语成绩

select student_id,numfrom score where course_id = (select cid from course where cname = '英语');#查看每个学生的平均成绩

select student_id,avg(num),count(num)fromscore group by student_id;#将上面的几张表拼接起来,为了生成所有学生的信息，用student表作为左连接的第一张表

select sid 学生ID,t2.num 语文,t1.num 数学, t3.num 英语,t4.count_course 有效课程数,t4.avg_num 有效平均分fromstudent

left join (select student_id,numfrom score where course_id = (select cid from course where cname = '数学')) t1

on student.sid=t1.student_id

left join (select student_id,numfrom score where course_id = (select cid from course where cname = '语文')) t2

on student.sid=t2.student_id

left join (select student_id,numfrom score where course_id = (select cid from course where cname = '英语')) t3

on student.sid=t3.student_id

left join (select student_id,avg(num) avg_num,count(num) count_coursefromscore group by student_id) t4

on student.sid=t4.student_id6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select course_id 课程ID,max(num) 最高分,min(num) 最低分fromscore group by course_id;7、按各科平均成绩从低到高和及格率的百分数从高到低顺序；#方法1:

#先求平均成绩

select course_id,avg(num)fromscore group by course_id;#解决计算各科及格率的问题

所有及格的人/所有人数

select t1.course_id,t1.count1/t2.count2 from(select course_id,count(course_id) count1from score where num>60group by course_id) t1

left join

(select course_id,count(course_id) count2fromscore group by course_id) t2

on t1.course_id=t2.course_id;#根据上述内容进行表的拼接

select t_out1.course_id,t_out1.avgnum, t_out2.pass_perfrom(select course_id,avg(num) avgnumfromscore group by course_id ) t_out1

left join

(select t1.course_id,t1.count1/t2.count2 pass_per from(select course_id,count(course_id) count1from score where num>60group by course_id) t1

left join

(select course_id,count(course_id) count2fromscore group by course_id) t2

on t1.course_id=t2.course_id) t_out2

on t_out1.course_id=t_out2.course_id#加上排序

select t_out1.course_id,t_out1.avgnum, t_out2.pass_perfrom (select course_id,avg(num) avgnum from score group by course_id ) t_out1 left join (select t1.course_id,t1.count1/t2.count2 pass_per from (select course_id,count(course_id) count1 from score where num>60 group by course_id) t1 left join (select course_id,count(course_id) count2 from score group by course_id) t2 on t1.course_id = t2.course_id) t_out2 on t_out1.course_id =t_out2.course_id order by avgnum ,pass_per desc;#方法2

#使用case when直接计算合格率

select

sum(case when num>60 then 1 else 0 end)/count(course_id)fromscore group by course_id#加上课程id和平均值

select course_id,avg(num),

sum(case when num>60 then 1 else 0 end)/count(course_id)fromscore group by course_id#加上排序

select course_id,avg(num) avgnum,

sum(case when num>60 then 1 else 0 end)/count(course_id) pass_perfromscore group by course_id

order by avgnum ,pass_per desc;8、查询各科成绩前三名的记录:(不考虑成绩并列情况)

select

t1.sid,t1.student_id,t1.course_id,t1.numfromscore t1

left join

(

select sid,course_id,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_num,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 2, 1) as third_numfromscore as s1

) t2

on t1.sid=t2.sid

where t1.num= t2.first_num or t1.num = t2.second_num or t1.num =t2.third_num;9、查询每门课程被选修的学生数；

select course_id,count(course_id)fromscore group by course_id;10、查询同名同姓学生名单，并统计同名人数；

select sname,count(1) as count fromstudent group by sname;11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

select course_id,avg(if(isnull(num), 0 ,num)) as avg fromscore group by course_id order by avg asc,course_id desc;12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

select student_id,sname, avg(if(isnull(num), 0 ,num)) from score left join student on score.student_id =student.sid group by student_id;13、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

select student.sname,score.numfromscore

left join course on score.course_id=course.cid

left join student on score.student_id=student.sid

where score.num< 60 and course.cname = '数学'

14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

select* from score where score.student_id = 3 and score.num > 80

15、求选了课程的学生人数

select sid,snamefrom student where sid not in (select student_id fromscore group by student_id);16、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；#先找到“杨艳”老师的教师id

select tidfrom teacher where tname = '杨艳';#再找到杨艳老师教的所有课程

select cidfrom course where teacher_id in (select tid from teacher where tname = '杨艳');#再找到杨艳老师教的所有课程的最高分

select max(num)from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));#再找到杨艳老师教的所有课程的最高分对应的学生

select distinct student_id,numfromscore

where num= (select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')))and course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));#找到学生的姓名

select student.sname,t1.numfrom(

select distinct student_id,numfromscore

where num= (select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')))and course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'))

) t1

left join

student

on

t1.student_id=student.sid;17、查询各个课程及相应的选修人数；

select course.cname,count(1) fromscore

left join course on score.course_id=course.cid

group by course_id;18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select DISTINCT s1.course_id,s2.course_id,s1.num,s2.numfrom score as s1, score as s2 where s1.num = s2.num and s1.course_id !=s2.course_id;19、查询每门课程成绩最好的前两名；

先查询每条数据对应学科成绩的第一名和第二名，这里必须要保留所有的s1，以便后续进行连表查询

select sid,course_id,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_numfromscore as s1

按照sid连表，把学生的成绩和对应的第一名、第二名成绩连起来

select* fromscore t1

left join

(

select sid,course_id,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_numfromscore as s1

) t2

on t1.sid=t2.sid

判断如果学生的成绩是第一名、第二名的成绩，那么就符合条件，显示学生的id、学科和成绩

select

t1.sid,t1.student_id,t1.course_id,t1.numfromscore t1

left join

(

select sid,course_id,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num,

(select numfrom score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_numfromscore as s1

) t2

on t1.sid=t2.sid

where t1.num= t2.first_num or t1.num =t2.second_num;20、检索至少选修两门课程的学生学号；

select student_idfrom score group by student_id having count(student_id) > 1;21、查询全部学生都选修的课程的课程号和课程名；#先查看一共有多少学生

select count(sid)fromstudent;#查看哪一门课选秀的学生个数和学生的总个数相等

select course_idfrom score group by course_id having count(student_id) = (select count(sid) fromstudent);22、查询没学过“叶平”老师讲授的任一门课程的学生姓名；#先查看要查找老师的id

select tidfrom teacher where tname = '李平老师';#查看该老师交了哪些课程

select cidfrom course where teacher_id in (select tid from teacher where tname = '李平老师')#看看有多少学生学习了该老师的课程

select distinct student_idfrom score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));#把不在上表中的学生姓名查出来

select snamefrom student where sid not in (select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')));23、查询两门以上不及格课程的同学的学号及其平均成绩；

select student_id,avg(num)from score where num<60 group by student_id having count(num)>=2;24、检索“004”课程分数小于60，按分数降序排列的同学学号；

select student_idfrom score where num< 60 and course_id = 4order by num desc;25、删除“002”同学的“001”课程的成绩；

deletefrom score where course_id = 1 and student_id = 2