MySQL练习题及答案

一 题目

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号，姓名，选课数，总成绩

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录
 
13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名

二 答案

#1、查询所有的课程的名称以及对应的任课老师姓名
select
    course.cname,
    teacher.tname
from
    course
inner join teacher on course.teacher_id = teacher.tid;




#2、查询学生表中男女生各有多少人
select
    gender 性别,
    count(1) 人数
from
    student
group by
    gender;




#3、查询物理成绩等于100的学生的姓名
select
    student.sname
from
    student
where
    sid in (
        select
            student_id
        from
            score
        inner join course on score.course_id = course.cid
        where
            course.cname = '物理'
        and score.num = 100
    );




#4、查询平均成绩大于八十分的同学的姓名和平均成绩
select
    student.sname,
    t1.avg_num
from
    student
inner join (
    select
        student_id,
        avg(num) as avg_num
    from
        score
    group by
        student_id
    having
        avg(num) > 80
) as t1 on student.sid = t1.student_id;




#5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
select
    student.sid,
    student.sname,
    t1.course_num,
    t1.total_num
from
    student
left join (
    select
        student_id,
        count(course_id) course_num,
        sum(num) total_num
    from
        score
    group by
        student_id
) as t1 on student.sid = t1.student_id;




#6、 查询姓李老师的个数
select
    count(tid)
from
    teacher
where
    tname like '李%';




#7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)
select
    student.sname
from
    student
where
    sid not in (
        select distinct
            student_id
        from
            score
        where
            course_id in (
                select
                    course.cid
                from
                    course
                inner join teacher on course.teacher_id = teacher.tid
                where
                    teacher.tname = '李平老师'
            )
    );




#8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)
select
    t1.student_id
from
    (
        select
            student_id,
            num
        from
            score
        where
            course_id = (
                select
                    cid
                from
                    course
                where
                    cname = '物理'
            )
    ) as t1
inner join (
    select
        student_id,
        num
    from
        score
    where
        course_id = (
            select
                cid
            from
                course
            where
                cname = '生物'
        )
) as t2 on t1.student_id = t2.student_id
where
    t1.num > t2.num;




#9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)
select
    student.sname
from
    student
where
    sid in (
        select
            student_id
        from
            score
        where
            course_id in (
                select
                    cid
                from
                    course
                where
                    cname = '物理'
                or cname = '体育'
            )
        group by
            student_id
        having
            count(course_id) = 1
    );




#10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表，然后对学生进行分组，统计课程数目>=2)
select
    student.sname,
    class.caption
from
    student
inner join (
    select
        student_id
    from
        score
    where
        num < 60
    group by
        student_id
    having
        count(course_id) >= 2
) as t1
inner join class on student.sid = t1.student_id
and student.class_id = class.cid;




#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)
select
    student.sname
from
    student
where
    sid in (
        select
            student_id
        from
            score
        group by
            student_id
        having
            count(course_id) = (select count(cid) from course)
    );




#12、查询李平老师教的课程的所有成绩记录
select
    *
from
    score
where
    course_id in (
        select
            cid
        from
            course
        inner join teacher on course.teacher_id = teacher.tid
        where
            teacher.tname = '李平老师'
    );




#13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)
select
    cid,
    cname
from
    course
where
    cid in (
        select
            course_id
        from
            score
        group by
            course_id
        having
            count(student_id) = (
                select
                    count(sid)
                from
                    student
            )
    );




#14、查询每门课程被选修的次数
select
    course_id,
    count(student_id)
from
    score
group by
    course_id;




#15、查询之选修了一门课程的学生姓名和学号
select
    sid,
    sname
from
    student
where
    sid in (
        select
            student_id
        from
            score
        group by
            student_id
        having
            count(course_id) = 1
    );




#16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select distinct
    num
from
    score
order by
    num desc;




#17、查询平均成绩大于85的学生姓名和平均成绩
select
    sname,
    t1.avg_num
from
    student
inner join (
    select
        student_id,
        avg(num) avg_num
    from
        score
    group by
        student_id
    having
        avg(num) > 85
) t1 on student.sid = t1.student_id;




#18、查询生物成绩不及格的学生姓名和对应生物分数
select
    sname 姓名,
    num 生物成绩
from
    score
left join course on score.course_id = course.cid
left join student on score.student_id = student.sid
where
    course.cname = '生物'
and score.num < 60;




#19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
select
    sname
from
    student
where
    sid = (
        select
            student_id
        from
            score
        where
            course_id in (
                select
                    course.cid
                from
                    course
                inner join teacher on course.teacher_id = teacher.tid
                where
                    teacher.tname = '李平老师'
            )
        group by
            student_id
        order by
            avg(num) desc
        limit 1
    );




#20、查询每门课程成绩最好的前两名学生姓名
#查看每门课程按照分数排序的信息，为下列查找正确与否提供依据
select
    *
from
    score
order by
    course_id,
    num desc;




#表1：求出每门课程的课程course_id,与最高分数first_num
select
    course_id,
    max(num) first_num
from
    score
group by
    course_id;




#表2：去掉最高分，再按照课程分组，取得的最高分，就是第二高的分数second_num
select
    score.course_id,
    max(num) second_num
from
    score
inner join (
    select
        course_id,
        max(num) first_num
    from
        score
    group by
        course_id
) as t on score.course_id = t.course_id
where
    score.num < t.first_num
group by
    course_id;




#将表1和表2联合到一起，得到一张表t3，包含课程course_id与该们课程的first_num与second_num
select
    t1.course_id,
    t1.first_num,
    t2.second_num
from
    (
        select
            course_id,
            max(num) first_num
        from
            score
        group by
            course_id
    ) as t1
inner join (
    select
        score.course_id,
        max(num) second_num
    from
        score
    inner join (
        select
            course_id,
            max(num) first_num
        from
            score
        group by
            course_id
    ) as t on score.course_id = t.course_id
    where
        score.num < t.first_num
    group by
        course_id
) as t2 on t1.course_id = t2.course_id;




#查询前两名的学生（有可能出现并列第一或者并列第二的情况）
select
    score.student_id,
    t3.course_id,
    t3.first_num,
    t3.second_num
from
    score
inner join (
    select
        t1.course_id,
        t1.first_num,
        t2.second_num
    from
        (
            select
                course_id,
                max(num) first_num
            from
                score
            group by
                course_id
        ) as t1
    inner join (
        select
            score.course_id,
            max(num) second_num
        from
            score
        inner join (
            select
                course_id,
                max(num) first_num
            from
                score
            group by
                course_id
        ) as t on score.course_id = t.course_id
        where
            score.num < t.first_num
        group by
            course_id
    ) as t2 on t1.course_id = t2.course_id
) as t3 on score.course_id = t3.course_id
where
    score.num >= t3.second_num
and score.num <= t3.first_num;




#排序后可以看的明显点
select
    score.student_id,
    t3.course_id,
    t3.first_num,
    t3.second_num
from
    score
inner join (
    select
        t1.course_id,
        t1.first_num,
        t2.second_num
    from
        (
            select
                course_id,
                max(num) first_num
            from
                score
            group by
                course_id
        ) as t1
    inner join (
        select
            score.course_id,
            max(num) second_num
        from
            score
        inner join (
            select
                course_id,
                max(num) first_num
            from
                score
            group by
                course_id
        ) as t on score.course_id = t.course_id
        where
            score.num < t.first_num
        group by
            course_id
    ) as t2 on t1.course_id = t2.course_id
) as t3 on score.course_id = t3.course_id
where
    score.num >= t3.second_num
and score.num <= t3.first_num
order by
    course_id;




#可以用以下命令验证上述查询的正确性
select
    *
from
    score
order by
    course_id,
    num desc;




-- 21、查询不同课程但成绩相同的学号，课程号，成绩
-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
-- 24、任课最多的老师中学生单科成绩最高的学生姓名