一、表关系年级表：class_gradecreate table class_grade(    gid int primary key auto_increment,    gname varchar(20) not null    );insert into class_grade(gname) values    ('一年级'),    ('二年级'),    ('三年级');班级表：classcreate table class(    cid int primary key auto_increment,    caption varchar(30) not null,    grade_id int not null,    constraint class_name foreign key(grade_id)    references class_grade(gid)    on delete cascade    on update cascade    );insert into class(caption,grade_id) values    ('一年一班',1),    ('二年一班',2),    ('三年二班',3);    学生表：studentcreate table student(    sid int primary key auto_increment,    sname varchar(20) not null,    gender enum('女','男'),    class_id int not null,    constraint student_name foreign key(class_id)    references class(cid)    on delete cascade    on update cascade    );insert into student(sname,gender,class_id) values    ('乔丹','女',1),    ('艾弗森','女',1),    ('科比','女',2);    老师表：teachercreate table teacher(    tid int primary key auto_increment,    tname varchar(30) not null    );insert into teacher(tname) values    ('张三'),    ('李四'),    ('王五');    课程表：coursecreate table course(    cid int primary key auto_increment,    cname varchar(30) not null,    teacher_id int not null,    constraint teacher_name foreign key(teacher_id)    references teacher(tid)    on delete cascade    on update cascade);insert into course(cname,teacher_id) values    ('生物',1),    ('体育',1),    ('物理',2);    成绩表：scorecreate table score(    sid int primary key auto_increment,    student_id int not null,    course_id int not null,    score int not null,    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);insert into score(student_id,course_id,score) values    (1,1,60),    (1,2,59),    (2,2,99);    班级任职表：teach2clscreate table teach2cls(    tcid int primary key auto_increment,    tid int not null,    cid int not null,    foreign key(tid) references teacher(tid)    on delete cascade    on update cascade,    foreign key(cid) references class(cid)    on delete cascade    on update cascade);insert into teach2cls(tid,cid) values    (1,1),    (1,2),    (2,1),    (3,2);    2、查询学生总人数select count(sname) 总人数 from student;3、查询’生物‘课程和’物理‘课程成绩都及格的学生id和姓名select     sid,    snamefrom    studentinner join    (        select             student_id        from             score        where            course_id in (                select                    cid                from                    course                where                    cname in ('生物','物理')            ) and  score >= 60    ) as t1 on t1.student_id = student.sid;    4、查询每个年级的班级数，取出班级数最多的前三个年级;select    class_grade.gid,    class_grade.gnamefrom    class_gradeinner join(    select         grade_id,        count(cid)    from        class    group by         grade_id    order by         grade_id desc    limit 3) as t1 on t1.grade_id = class_grade.gid;    5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩;select    student.sid,    student.sname,    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_score            from                score            group by                 student_id            order by                avg(score) desc            limit 1            ),            (            select                avg(score) as min_score            from                score            group by                student_id            order by                avg(score)            limit 1            )        )    ) as t1 on t1.student_id = student.sid;6、查询每个年级的学生人数select     class_grade.gname,    count_cidfrom     class_gradeinner join    (    select        grade_id,        count(cid) as count_cid    from        class,student    where        class.cid = class_id    group by grade_id    ) as t1 on t1.grade_id = class_grade.gid;7、查询每位学生的学号、姓名、选课数、平均成绩;select    student.sid,    student.sname,    course_count,    avg_scorefrom    studentleft join    (    select        student_id,        count(course_id) as course_count,        avg(score) as avg_score    from        score    group by         student_id    ) as t1 on t1.student_id = student.sid;8、查询学生编号为‘2’的学生的姓名，该学生成绩最高的课程名、成绩最低的课程名及分数;select    student.sname,    student.sid,    t1.scorefrom(    select        student_id,        course_id,        score    from         score    where student_id = 2 and score in    (        (        select            max(score)        from            score        where student_id = 2),        (        select             min(score)        from            score        where student_id =2)    ) )as t1inner join student on t1.student_id = student.sidinner join course on t1.course_id = course.cid;9、查询‘李’的老师的个数和所带班级数;select    count(teacher.tname) as '李%个数',    count(teach2cls.cid) as '班级数量'from teacher left join teach2cls on teach2cls.tid = teacher.tidwhere teacher.tname like '李%';10、查询班级数小于5年级的id和年级名;select    gid,    gname,    count(cid)from    class_gradeinner join class on gid = grade_idgroup by    gidhaving    count(cid) < 511、查询班级信息，包括班级id、班级名称、年级、年级级别（12为低年级，34为中年级，56为高年级）select    class.cid as '班级id',    class.caption as '班级名称',    class_grade.gid as '年级',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    classleft join class_grade on class_grade.gid=class.grade_id;12、查询学过“张三”老师2门课以上的同学的学号、姓名；select     sid,    snamefrom    studentwhere sid in (select    student_idfrom    scoreleft join course on course_id = course.cidwhere course.teacher_id in     (            select             tid        from             teacher        where tname = '张三'    )group by    student_idhaving count(course.cid)>=2)13、查询教授课程超过2门的老师的id和姓名;select    tid,    tnamefrom    teacherinner join     (    select        teacher_id,        count(cid)    from        course    group by        teacher_id    having count(cid) >=2) as t1 on t1.teacher_id = teacher.tid;14、查询学过编号‘1’课程和编号2课程的同学的学号、姓名;select    sid,    snamefrom    studentwhere sid in (select     course_idfrom    scoregroup by    course_idhaving    course_id in (1,2))15、查询没有带过高年级的老师id和姓名;select    tid,    tnamefrom    teacherwhere tid  in(select     tidfrom    teach2clswhere tid in (select    cidfrom    classwhere grade_id in (5,6)));16、查询学过'张三'老师所教的所有课的同学的学号、姓名;select    sid,    snamefrom    studentwhere sid in     (    select        student_id    from        score    where course_id in         (            select            cid        from            course        inner join teacher on teacher_id = teacher.tid        where teacher.tname ='张三'));17、查询带过超过2个班级的老师的id和姓名;select    tid,    tnamefrom    teacherwhere tid in (select    tidfrom    teach2clsgroup by    tidhaving    count(cid) >2);18、查询课程编号’2‘的成绩比课程’1‘课程低的所有同学的学号、姓名select     sid,    snamefrom    studentwhere sid in (select     t1.student_idfrom(    select        student_id,        score    from        score    where         course_id =2) as t1inner join(    select        student_id,        score    from        score    where         course_id =1) as t2 on t1.student_id = t2.student_idwhere t1.score < t2.score);19、查询所带班级数最多的老师id和姓名;select    tid,    tnamefrom    teacherwhere tid =(select    tidfrom    teach2clsgroup by    tidorder by     count(cid) desclimit 1);20、查询有课程成绩小于60分的同学的学号、姓名;select    sid,    snamefrom    studentwhere sid in (select    student_idfrom    scorewhere score <60);21、查询没有学全所有课的同学的学号、姓名;select    sid,    snamefrom    studentwhere sid not in (select    student_idfrom    scoregroup by    student_idhaving    count(course_id) = (select count(cid) from course));22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；select    sid,    snamefrom    studentwhere sid in (select    student_idfrom    scorewhere course_id in (select    course_idfrom    scorewhere student_id = 1));23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；select    sid,    snamefrom    studentwhere sid in (select    student_idfrom    scorewhere course_id in (select    course_idfrom    scorewhere student_id = 1)having    student_id !=1);24、查询和‘2’号同学学习的课程完全相同的其它同学的学号和姓名select    sid,    snamefrom    studentwhere sid in (    select        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表记录;deletefrom    scorewhere course_id in (select     cidfrom    coursewhere course.teacher_id = (select     tidfrom     teacherwhere teacher.tname = '张三'));26、向score表中插入一些记录，这些记录要求符合以下条件：1、没有上过编号‘2’课程的同学学号2、插入’2‘号课程的平均成绩    insert into score(student_id,course_id,score)      select t1.sid,2,t2.avg from (     select sid from student where sid not in (     select student_id from score where course_id = 2)) as t1,     (select avg(score) as avg from score group by course_id having course_id =2) as t2;27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，    按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；      select sc.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 = sc.student_id) as 生物,        (select score.score from score left join course on score.course_id = course.cid           where course.cname = '体育' and score.student_id = sc.student_id) as 体育,        (select score.score from score left join course on score.course_id = course.cid           where course.cname = '物理' and score.student_id = sc.student_id) as 物理,        count(sc.course_id) as '有效课程数',avg(sc.score) as '有效平均分'    from score as sc group by sc.student_id order by avg(sc.score);28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，高低分;    select course_id as "课程ID",max(score) as "最高分",min(score) as "最低分" from score    group by course_id29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；    SELECT course_id as '课程ID',AVG(score) as '平均成绩',    sum(CASE WHEN score > 60 then 1 ELSE 0 END)/COUNT(1)*100 as '及格率'    from score    GROUP BY course_id ORDER BY '平均成绩' ASC,'及格率' desc;30、课程平均分从高到低显示（现实任课老师）；SELECT score.course_id as '课程ID',avg(score) as '平均分' from scoreinner join course on score.course_id = course.cidGROUP BY course_idORDER BY avg(score) DESC31、查询各科成绩前三名的记录(不考虑成绩并列情况)SELECT score.sid,score.course_id,score.score,t1.first_score,t1.sencond_score,t1.third_score from score LEFT JOIN(SELECT sid,    (select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 0,1) as first_score,    (select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 1,1) as sencond_score,    (select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 2,1) as third_scorefrom score as s1)as t1 on score.sid = t1.sidWHERE score.score in (t1.first_score,t1.sencond_score,t1.third_score)32、查询每门课程被选修的学生数；SELECT score.course_id as '课程ID',count(student_id) as '学生数' from scoreGROUP BY course_id33、查询选修了2门以上课程的全部学生的学号和姓名；SELECT student.sid,student.sname from student WHERE sid in (SELECT score.student_id from scoreGROUP BY student_idHAVING count(course_id) >=2);34、查询男生、女生的人数，按倒序排列；select gender,count(sid) as sum from studentgroup by genderorder by sum desc35、查询姓“张”的学生名单；SELECT sname from student WHERE sname like '张%'36、查询同名同姓学生名单，并统计同名人数；SELECT sname as '名字',count(sname) as '同名人数' from studentGROUP BY snameHAVING count(sname) >137、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；select score.course_id,avg(score) as avg from scoreINNER JOIN course on course.cid = course_idGROUP BY course_idORDER BY avg ASC,course_id DESC;38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；SELECT sid,sname from studentwhere sid in (SELECT score.student_id  from scoreINNER JOIN course on course.cid = score.course_idWHERE course.cname = '体育' and score.score <60);39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；SELECT sid,sname from  studentWHERE sid in (SELECT score.student_id  from scoreINNER JOIN course on course.cid = course_idWHERE course_id = 3 and score.score > 80);40、求选修了课程的学生人数select course_id as '课程ID',count(student_id) as '学生人数' from score group by course_id;41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；SELECT student.sname,max(score),min(score) from scoreINNER JOIN student on score.student_id = student.sidWHERE course_id in (SELECT cid from courseWHERE teacher_id in (SELECT tid FROM teacherWHERE tname = '王五'))GROUP BY student_idORDER BY max(score) DESC,MIN(score) ASCLIMIT 242、查询各个课程及相应的选修人数；SELECT score.course_id as 'ID',course.cname as '课程',count(student_id) as '人数' from scoreLEFT JOIN course on score.course_id = course.cidGROUP BY course_id43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；select DISTINCT s1.course_id,s2.course_id,s1.score,s2.score from score as s1, score as s2 where s1.score = s2.score and s1.course_id != s2.course_id;44、查询每门课程成绩最好的前两名学生id和姓名；SELECT score.sid,score.course_id,score.score,t1.first_score,t1.sencond_score from score LEFT JOIN(SELECT sid,    (select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 0,1) as first_score,    (select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 1,1) as sencond_scorefrom score as s1)as t1 on score.sid = t1.sidWHERE score.score <= t1.first_score and score.score >= t1.sencond_score45、检索至少选修两门课程的学生学号；SELECT score.student_id as '学生ID',count(course_id) as '课程ID' from scoreGROUP BY student_idHAVING count(course_id)>=246、查询没有学生选修的课程的课程号和课程名；SELECT cid,cname from courseWHERE cid not IN(SELECT score.course_id from scoreGROUP BY score.course_id)47、查询没带过任何班级的老师id和姓名；SELECT tid,tname from teacherWHERE tid not in(SELECT teach2cls.cid from teach2clsGROUP BY teach2cls.cid)48、查询有两门以上课程超过80分的学生id及其平均成绩；SELECT score.student_id as '学生ID',avg(score) as '平均成绩' from scoreWHERE score > 30 GROUP BY student_idHAVING count(course_id) >=249、检索“3”课程分数小于60，按分数降序排列的同学学号；select score.student_id from scoreWHERE score < 60 and course_id = 3ORDER BY score DESC50、删除编号为“2”的同学的“1”课程的成绩delete from score where score.student_id = 2 and score.course_id = 1;51、查询同时选修了物理课和生物课的学生id和姓名；SELECT sid,sname from studentWHERE sid in(SELECT score.student_id from scoreWHERE course_id in (SELECT cid from courseWHERE course.cname in ('生物','物理'))GROUP BY student_idHAVING count(course_id) =2)