阅读目录

• 一 题目
• 二 答案

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、任课最多的老师中学生单科成绩最高的学生姓名