表关系
查询练习
1、自行创建测试数据；2、查询学生总人数；3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；4、查询每个年级的班级数，取出班级数最多的前三个年级；5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；6、查询每个年级的学生人数；7、查询每位学生的学号，姓名，选课数，平均成绩；8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；9、查询姓“李”的老师的个数和所带班级数；10、查询班级数小于5的年级id和年级名；11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下； 班级id 班级名称 年级 年级级别 1 一年一班 一年级 低12、查询学过“张三”老师2门课以上的同学的学号、姓名；13、查询教授课程超过2门的老师的id和姓名；14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；15、查询没有带过高年级的老师id和姓名；16、查询学过“张三”老师所教的所有课的同学的学号、姓名；17、查询带过超过2个班级的老师的id和姓名；18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；19、查询所带班级数最多的老师id和姓名；20、查询有课程成绩小于60分的同学的学号、姓名；21、查询没有学全所有课的同学的学号、姓名；22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；25、删除学习“张三”老师课的score表记录；26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；30、课程平均分从高到低显示（显示任课老师）；31、查询各科成绩前三名的记录(不考虑成绩并列情况)32、查询每门课程被选修的学生数；33、查询选修了2门以上课程的全部学生的学号和姓名；34、查询男生、女生的人数，按倒序排列；35、查询姓“张”的学生名单；36、查询同名同姓学生名单，并统计同名人数；37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；40、求选修了课程的学生人数41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；42、查询各个课程及相应的选修人数；43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；44、查询每门课程成绩最好的前两名学生id和姓名；45、检索至少选修两门课程的学生学号；46、查询没有学生选修的课程的课程号和课程名；47、查询没带过任何班级的老师id和姓名；48、查询有两门以上课程超过80分的学生id及其平均成绩；49、检索“3”课程分数小于60，按分数降序排列的同学学号；50、删除编号为“2”的同学的“1”课程的成绩；51、查询同时选修了物理课和生物课的学生id和姓名；
建立相关表
create table class_grade( gid int primary key auto_increment, gname varchar(16) not null unique);create table class( cid int primary key auto_increment, caption varchar(16) not null, grade_id int not null, foreign key(grade_id) references class_grade(gid));create table student( sid int primary key auto_increment, sname varchar(16) not null, gender enum('女','男') not null default '女', class_id int not null, foreign key(class_id) references class(cid));create table teacher( tid int primary key auto_increment, tname varchar(16) not null);create table course( cid int primary key auto_increment, cname varchar(16) not null, teacher_id int not null, foreign key(teacher_id) references teacher(tid));create table score( sid int not null unique auto_increment, student_id int not null, course_id int not null, score int not null, primary key(student_id,course_id), foreign key(student_id) references student(sid) on delete cascade on update cascade, foreign key(course_id) references course(cid) on delete cascade on update cascade);create table teach2cls( tcid int not null unique auto_increment, tid int not null, cid int not null, primary key(tid,cid), foreign key(tid) references teacher(tid) on delete cascade on update cascade, foreign key(cid) references class(cid) on delete cascade on update cascade);建表 添加测试数据
# 5 个年级insert into class_grade(gname) values('一年级'),('二年级'),('三年级'),('四年级'),('五年级');# 5 个老师insert into teacher(tname) values('张三'),('李四'),('王五'),('李杰'),('赵六');# 12 个班级insert into class(caption,grade_id) values('一年一班',1),('一年二班',1),('一年三班',1),('一年四班',1),('一年五班',1),('二年一班',2),('二年二班',2),('三年一班',3),('三年二班',3),('四年一班',4),('四年二班',4),('五年一班',5);# 10 个课程insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2),('化学',3),('美术',4),('音乐',2),('语文',3),('数学',4),('地理',2),('研究',1);# 18 个学生insert into student(sname,gender,class_id) values # 12个学生('孙尚香','女',1),('貂蝉','女',1),('刘备','男',2),('孙二娘','女',2),('张飞','男',3),('关羽','男',4),('林黛玉','女',5),('薛宝钗','女',6),('宋江','男',6),('白骨精','女',7),('猪八戒','男',8),('王熙凤','女',1),('李师师','女',2),('金翠莲','女',9),('如花','女',1),('沙僧','男',2),('李美丽','女',3),('金角大王','男',4);insert into score(student_id,course_id,score) values(1,1,60),(1,2,59),(1,3,58),(1,4,22),(1,5,59),(1,6,60),(1,7,99),(1,8,100),(1,9,88),(2,1,99),(2,2,99),(2,3,89),(2,4,60),(2,5,59),(2,6,33),(2,7,56),(2,8,59),(2,9,60),(3,1,59),(3,3,30),(3,5,28),(3,7,70),(3,9,60),(4,2,59),(4,4,100),(4,6,90),(4,8,80),(4,10,88),(5,1,59),(5,2,33),(5,3,12),(5,4,88),(6,1,60),(6,3,99),(6,5,100),(6,6,60),(6,7,59),(6,8,100),(6,9,88),(7,9,20),(7,1,36),(7,3,57),(7,5,60),(7,8,60),(7,10,60),(8,2,61),(8,4,59),(8,6,62),(9,8,59),(9,1,60),(9,2,61),(9,3,21),(10,1,70),(10,3,88),(10,5,68),(10,9,99),(11,1,89),(11,7,99),(12,3,100),(12,8,60);insert into teach2cls(tid,cid) values(1,1),(1,2),(1,3),(1,5),(2,4),(2,6),(2,8),(2,9),(2,1),(2,5),(3,7),(3,1),(3,3),(3,5),(3,9),(4,7),(4,2),(4,4),(4,6),(4,8),(4,1),(1,12),(2,12); 参考SQL
1、自行创建测试数据；详见"db5_结构+数据.sql"2、查询学生总人数；select count(sid) from student ;3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；select sid, snamefrom studentwhere sid in( select score.student_id from score inner join course on score.course_id=course.cid where course.cname in( '生物', '物理' ) and score.score >= 60 group by score.student_id having count(course_id)=2 );4、查询每个年级的班级数，取出班级数最多的前三个年级；select gnamefrom class_gradewhere gid in ( select grade_id from class group by grade_id having count(cid) in ( select t1.count_cid from ( select distinct count(cid) as count_cid from class group by grade_id order by count_cid desc limit 3 ) as t1 ) );5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；select student.sid, student.sname, t1.avg_scorefrom studentinner join ( select student_id, avg(score) as avg_score from score group by student_id having avg(score) in ( ( select avg(score) as max_avg_score from score group by student_id order by avg(score) desc limit 1 ), ( select avg(score) as min_avg_score from score group by student_id order by avg(score) asc limit 1 ) )) as t1 on student.sid = t1.student_id;6、查询每个年级的学生人数；select t1.grade_id, count(t1.sid) as count_studentfrom ( select student.sid, class.grade_id from student inner join class on student.class_id=class.cid ) as t1group by t1.grade_id;7、查询每位学生的学号，姓名，选课数，平均成绩；select student.sid, student.sname, t1.count_course, t1.avg_scorefrom studentleft join ( select student_id, count(course_id) as count_course, avg(score) as avg_score from score group by student_id) as t1 on student.sid = t1.student_id;8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；select student.sname, course.cname, t1.scorefrom( select student_id, course_id, score from score where student_id=2 and score in( ( select min(score) from score where student_id=2 ), ( select max(score) from score where student_id=2 ) ) ) as t1 inner join student on t1.student_id=student.sid inner join course on t1.course_id=course.cid;9、查询姓“李”的老师的个数和所带班级数；select teacher.tid, teacher.tname, t1.count_cidfrom teacherleft join ( select tid, count(cid) as count_cid from teach2cls where tid in ( select tid from teacher where tname like '李%' ) group by tid) as t1 on teacher.tid = t1.tidwhere teacher.tname like '李%';10、查询班级数小于5的年级id和年级名；select gid, gnamefrom class_gradewhere gid in ( select grade_id from class group by grade_id having count(caption)<5 );11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下； 班级id 班级名称 年级 年级级别 1 一年一班 一年级 低# case when ... thenselect class.cid 班级id, class.caption 班级名称, class_grade.gname 年级, case when class_grade.gid between 1 and 2 then '低' when class_grade.gid between 3 and 4 then '中' when class_grade.gid between 5 and 6 then '高' else 0 end as '年级级别'from class, class_gradewhere class.grade_id = class_grade.gid;12、查询学过“张三”老师2门课以上的同学的学号、姓名；select sid, snamefrom studentwhere sid in ( select student_id from score where course_id in ( select course.cid from teacher, course where teacher.tid = course.teacher_id and teacher.tname = '张三' ) group by student_id having count(course_id) > 2 );13、查询教授课程超过2门的老师的id和姓名；select tid, tnamefrom teacherwhere tid in ( select teacher_id from course group by teacher_id having count(cid) > 2 );14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；select sid, snamefrom studentwhere sid in ( select distinct student_id from score where course_id in ( 1, 2 ) );15、查询没有带过高年级的老师id和姓名；select tid, tnamefrom teacherwhere tid not in ( select tid from teach2cls where cid in ( select cid from class where grade_id in ( 5, 6 ) ) );16、查询学过“张三”老师所教的所有课的同学的学号、姓名；select sid, snamefrom studentwhere sid in ( select student_id from score inner join course on score.course_id=course.cid where teacher_id in( select sid from teacher where sname='张三' ) )17、查询带过超过2个班级的老师的id和姓名；select tid, tnamefrom teacherwhere tid in ( select teacher_id from course group by teacher_id having count(cid) > 2 );18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；select sid, snamefrom studentwhere sid in ( select t1.student_id from ( select student_id, score from score where course_id = 2 group by student_id ) as t1, ( select student_id, score from score where course_id = 1 group by student_id ) as t2 where t1.student_id = t2.student_id and t1.score < t2.score );19、查询所带班级数最多的老师id和姓名；# 考虑班级数并列最多的情况select tid, tnamefrom teacherwhere tid in ( select tid from teach2cls group by tid having count(cid) = ( select count(cid) from teach2cls group by tid order by count(cid) desc limit 1 ) );20、查询有课程成绩小于60分的同学的学号、姓名；select sid, snamefrom studentwhere sid in ( select distinct student_id from score where score < 60 );21、查询没有学全所有课的同学的学号、姓名；select sid, snamefrom studentwhere sid not in ( select student_id from score group by student_id having count(course_id) = ( select count(cid) from course ) );22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；select sid, snamefrom studentwhere sid in ( select student_id from score where course_id in ( select course_id from score where student_id = 1 ) group by student_id );23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；select sid, snamefrom studentwhere sid in ( select student_id from score where course_id in ( select course_id from score where student_id = 1 ) group by student_id ) and sid != 1;24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；select sid, snamefrom studentwhere sid in ( select score.student_id from score, ( select course_id from score where student_id = 2 ) as t1 where score.course_id = t1.course_id and score.student_id != 2 group by score.student_id having count(score.course_id) = ( select count(course_id) from score where student_id = 2 ) );25、删除学习“张三”老师课的score表记录；delete from scorewhere course_id in ( select course.cid from course, teacher where course.teacher_id = teacher.tid and teacher.tname = '张三' );26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；# 实际结果会和上面一题有冲突，张三老师所教的2号课程insert into score(student_id,course_id,score)select t1.sid, 2, t2.avg_scorefrom ( select sid from student where sid not in ( select student_id from score where course_id = 2 ) ) as t1, ( select avg(score) as avg_score from score where course_id = 2 ) as t2;27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；# 有效成绩含所有课程，包括语数英select t1.student_id as 学生ID, (select score.score from score left join course on score.course_id = course.cid where course.cname = '语文' and score.student_id = t1.student_id) as 语文, (select score.score from score left join course on score.course_id = course.cid where course.cname = '数学' and score.student_id = t1.student_id) as 数学, (select score.score from score left join course on score.course_id = course.cid where course.cname = '音乐' and score.student_id = t1.student_id) as 英语, count(t1.course_id) as 有效课程数, avg(t1.score) as 有效平均分from score as t1group by t1.student_idorder by avg(t1.score) asc;28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；select course.cid as 课程ID, max(score.score) as 最高分, min(score.score) as 最低分from courseleft join score on course.cid = score.course_idgroup by score.course_id;29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；select course_id, avg(score) as avg_score, sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as 及格率from scoregroup by course_idorder by avg(score) desc, 及格率 asc;30、课程平均分从高到低显示（显示任课老师）；select course.cname, t1.avg_score, teacher.tnamefrom course, teacher, ( select course_id, avg(score) as avg_score from score group by course_id order by avg_score desc ) as t1where course.cid = t1.course_id and course.teacher_id = teacher.tidorder by t1.avg_score desc;31、查询各科成绩前三名的记录(不考虑成绩并列情况)select score.sid, score.student_id, score.course_id, score.sid, t1.first_score, t1.second_score, t1.third_scorefrom scoreinner join ( select s1.sid, (select score from score as s2 where s1.course_id = s2.course_id order by score desc limit 0,1) as first_score, (select score from score as s3 where s1.course_id = s3.course_id order by score desc limit 1,1) as second_score, (select score from score as s4 where s1.course_id = s4.course_id order by score desc limit 2,1) as third_score from score as s1) as t1 on score.sid = t1.sidwhere score.score in ( t1.first_score, t1.second_score, t1.third_score );32、查询每门课程被选修的学生数；# 包含了没有被选修的课程显示0select course.cid, ifnull(t1.count_students,0) as count_studentfrom courseleft join ( select course_id, count(student_id) as count_students from score group by course_id) as t1 on course.cid = t1.course_id;33、查询选修了2门以上课程的全部学生的学号和姓名；select sid, snamefrom studentwhere sid in ( select student_id from score group by student_id having count(course_id) > 2 );34、查询男生、女生的人数，按倒序排列；select gender, count(sid) as count_studentfrom studentgroup by genderorder by count_student desc;35、查询姓“张”的学生名单；select student.sid, student.sname, student.gender, class.captionfrom studentinner join class on student.class_id = class.cidwhere student.sname like '张%';36、查询同名同姓学生名单，并统计同名人数；select sname, count(sname) as count_snamefrom studentgroup by snamehaving count(sname) > 1;37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；select course_id, avg(score) as avg_scorefrom scoregroup by course_idorder by avg_score, course_id desc;38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；select student.sname, t1.scorefrom studentinner join ( select score.student_id, score.score from score inner join course on score.course_id = course.cid where course.cname = '数学' and score.score < 60) as t1 on student.sid = t1.student_id;39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；select sid, snamefrom studentwhere sid in ( select student_id from score where course_id = 3 and score > 80);40、求选修了课程的学生人数select course_id, count(student_id) as count_studentfrom scoregroup by course_id;41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；# 教的课可能包含多门，按课程id来分的！包含了最高，最低成绩相等的情况select student.sname, t2.course_id, t2.score, t2.max_score, t2.min_scorefrom studentinner join ( select score.student_id, score.course_id, score.score, t1.max_score, t1.min_score from score, ( select course_id, max(score) as max_score , min(score) as min_score from score where course_id in ( select cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = '王五' ) group by course_id ) as t1 where score.course_id = t1.course_id and score.score in ( max_score, min_score )) as t2 on student.sid = t2.student_id;42、查询各个课程及相应的选修人数；# 包含了没有被选修的课程显示0select course.cname, ifnull(t1.count_student,0) as count_studentfrom courseleft join ( select course_id, count(student_id) as count_student from score group by course_id) as t1 on course.cid = t1.course_id;43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；# 不同的学生之间，成绩相同 这里有问题！！select distinct s1.student_id, s2.student_id, s1.course_id as s1_course_id, s2.course_id as s2_course_id, s1.score, s2.scorefrom score as s1, score as s2where s1.course_id != s2.course_id and s1.score = s2.score;select distinct # 同一个学生，成绩相同 s1.student_id, s2.student_id, s1.course_id as s1_course_id, s2.course_id as s2_course_id, s1.score, s2.scorefrom score as s1, score as s2where s1.student_id = s2.student_id and s1.course_id != s2.course_id and s1.score = s2.score;44、查询每门课程成绩最好的前两名学生id和姓名；# 这个有排名 包括了成绩相同的！select c.sname, d.cname, a.scorefrom score ainner join ( select course_id, score, rank from ( select a.course_id, a.score, count(*) as rank from (select course_id, score from score group by course_id, score order by course_id, score desc) a inner join (select course_id, score from score group by course_id, score order by course_id, score desc) b on a.course_id = b.course_id and a.score <= b.score group by course_id, score ) t1 where rank in (1, 2) order by course_id, rank ) bon a.course_id = b.course_id and a.score = b.scoreinner join student c on a.student_id = c.sidinner join course d on a.course_id = d.cid;45、检索至少选修两门课程的学生学号；select student_idfrom scoregroup by student_idhaving count(course_id) >= 2;46、查询没有学生选修的课程的课程号和课程名；select cid, cnamefrom coursewhere cid not in ( select course_id from score group by course_id );47、查询没带过任何班级的老师id和姓名；select tid, tnamefrom teacherwhere tid not in ( select tid from teach2cls group by tid );48、查询有两门以上课程超过80分的学生id及其平均成绩；select student_id, avg(score) as avg_scorefrom scorewhere student_id in ( select student_id from score where score > 80 group by student_id having count(course_id) > 2 )group by student_id;49、检索“3”课程分数小于60，按分数降序排列的同学学号；select student_id, scorefrom scorewhere course_id = 3 and score < 60order by score desc;50、删除编号为“2”的同学的“1”课程的成绩；delete from scorewhere sid = ( select t1.sid from ( select sid from score where student_id = 2 and course_id = 1 ) as t1 );51、查询同时选修了物理课和生物课的学生id和姓名；select sid, snamefrom studentwhere sid in ( select student_id from score where course_id in ( select cid from course where course.cname in ( '物理', '生物' ) ) group by student_id having count(course_id) = 2 );