MySQL 45题作业

代码均为AI生成，保真不保对。

  1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    s.SId, 
    s.Sname, 
    s1.score AS '01_Score', 
    s2.score AS '02_Score'
FROM 
    Student s
JOIN 
    SC s1 ON s.SId = s1.SId AND s1.CId = '01'
JOIN 
    SC s2 ON s.SId = s2.SId AND s2.CId = '02'
WHERE 
    s1.score > s2.score;
  1. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    s.SId, 
    s.Sname, 
    AVG(sc.score) AS 'Avg_Score'
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
GROUP BY 
    s.SId, 
    s.Sname
HAVING 
    AVG(sc.score) >= 60;
  1. 查询在SC表存在成绩的学生信息：
1
2
3
4
5
6
7
8
9
SELECT 
    DISTINCT s.SId, 
    s.Sname, 
    s.Sage, 
    s.Ssex
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId;
  1. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为 null）：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    s.SId, 
    s.Sname, 
    COUNT(sc.CId) AS 'Total_Courses', 
    SUM(sc.score) AS 'Total_Score'
FROM 
    Student s
LEFT JOIN 
    SC sc ON s.SId = sc.SId
GROUP BY 
    s.SId, 
    s.Sname;
  1. 查询「李」姓老师的数量：
1
2
3
4
5
6
SELECT 
    COUNT(*) AS 'Number_of_Li_Teachers'
FROM 
    Teacher
WHERE 
    Tname LIKE '李%';
  1. 查询学过「张三」老师授课的同学的信息：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT 
    s.SId, 
    s.Sname, 
    s.Sage, 
    s.Ssex
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
JOIN 
    Course c ON sc.CId = c.CId
JOIN 
    Teacher t ON c.TId = t.TId
WHERE 
    t.Tname = '张三';
  1. 查询没有学全所有课程的同学的信息：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 
    s.SId, 
    s.Sname, 
    s.Sage, 
    s.Ssex
FROM 
    Student s
WHERE 
    s.SId NOT IN (
        SELECT 
            sc.SId
        FROM 
            SC sc
        GROUP BY 
            sc.SId
        HAVING 
            COUNT(DISTINCT sc.CId) = (SELECT COUNT(*) FROM Course)
    );
  1. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息：
1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT 
    s.SId, 
    s.Sname, 
    s.Sage, 
    s.Ssex
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
WHERE 
    sc.CId IN (SELECT CId FROM SC WHERE SId = '01');
  1. 查询和"01"号的同学学习的课程完全相同的其他同学的信息：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SELECT 
    s.SId, 
    s.Sname, 
    s.Sage, 
    s.Ssex
FROM 
    Student s
WHERE 
    s.SId != '01'
AND NOT EXISTS (
    SELECT 
        CId 
    FROM 
        SC 
    WHERE 
        SId = '01'
    AND CId NOT IN (
        SELECT 
            CId 
        FROM 
            SC 
        WHERE 
            SId = s.SId
    )
)
AND NOT EXISTS (
    SELECT 
        CId 
    FROM 
        SC 
    WHERE 
        SId = s.SId
    AND CId NOT IN (
        SELECT 
            CId 
        FROM 
            SC 
        WHERE 
            SId = '01'
    )
);
  1. 查询没学过"张三"老师讲授的任一门课程的学生姓名：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 
    s.Sname
FROM 
    Student s
WHERE NOT EXISTS (
    SELECT 
        *
    FROM 
        SC sc
    JOIN 
        Course c ON sc.CId = c.CId
    JOIN 
        Teacher t ON c.TId = t.TId
    WHERE 
        t.Tname = '张三'
    AND 
        s.SId = sc.SId
);
  1. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT 
    s.SId, 
    s.Sname, 
    AVG(sc.score) AS 'Avg_Score'
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
WHERE 
    sc.SId IN (
        SELECT 
            SId 
        FROM 
            SC 
        WHERE 
            score < 60
        GROUP BY 
            SId
        HAVING 
            COUNT(*) >= 2
    )
GROUP BY 
    s.SId, 
    s.Sname;
  1. 检索"01"课程分数小于60，按分数降序排列的学生信息：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 
    s.SId, 
    s.Sname, 
    s.Sage, 
    s.Ssex, 
    sc.score
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
WHERE 
    sc.CId = '01'
AND 
    sc.score < 60
ORDER BY 
    sc.score DESC;
  1. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    s.SId, 
    s.Sname, 
    AVG(sc.score) AS 'Avg_Score'
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
GROUP BY 
    s.SId, 
    s.Sname
ORDER BY 
    AVG(sc.score) DESC;
  1. 查询各科成绩最高分、最低分和平均分：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    c.CId, 
    c.Cname, 
    MAX(sc.score) AS 'Highest_Score', 
    MIN(sc.score) AS 'Lowest_Score', 
    AVG(sc.score) AS 'Average_Score'
FROM 
    Course c
JOIN 
    SC sc ON c.CId = sc.CId
GROUP BY 
    c.CId, 
    c.Cname;
  1. 按各科成绩进行排序，并显示排名，Score重复时保留名次空缺（这需要数据库支持窗口函数）：
1
2
3
4
5
6
7
8
9
10
SELECT 
    s.SId, 
    s.Sname, 
    sc.CId, 
    sc.score, 
    RANK() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS 'Rank'
FROM 
    SC sc
JOIN 
    Student s ON sc.SId = s.SId;
  1. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    s.SId, 
    s.Sname, 
    SUM(sc.score) AS 'Total_Score', 
    RANK() OVER (ORDER BY SUM(sc.score) DESC) AS 'Rank'
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
GROUP BY 
    s.SId, 
    s.Sname;
  1. 统计各科成绩各分数段人数：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
    c.CId, 
    c.Cname,
    SUM(CASE WHEN sc.score >= 85 THEN 1 ELSE 0 END) AS '[100-85]',
    SUM(CASE WHEN sc.score < 85 AND sc.score >= 70 THEN 1 ELSE 0 END) AS '[85-70]',
    SUM(CASE WHEN sc.score < 70 AND sc.score >= 60 THEN 1 ELSE 0 END) AS '[70-60]',
    SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS '[60-0]'
FROM 
    Course c
LEFT JOIN 
    SC sc ON c.CId = sc.CId
GROUP BY 
    c.CId, 
    c.Cname;
  1. 查询各科成绩前三名的记录：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT 
    s.SId, 
    s.Sname, 
    sc.CId, 
    sc.score
FROM 
    SC sc
JOIN 
    Student s ON sc.SId = s.SId
WHERE 
    (
        SELECT 
            COUNT(DISTINCT score)
        FROM 
            SC sc2
        WHERE 
            sc2.score > sc.score
        AND 
            sc2.CId = sc.CId
    ) < 3
ORDER BY 
    sc.CId, 
    sc.score DESC;
  1. 查询每门课程被选修的学生数：
1
2
3
4
5
6
7
8
9
10
11
SELECT 
    c.CId, 
    c.Cname, 
    COUNT(DISTINCT sc.SId) AS 'Number_of_Students'
FROM 
    Course c
LEFT JOIN 
    SC sc ON c.CId = sc.CId
GROUP BY 
    c.CId, 
    c.Cname;
  1. 查询出只选修两门课程的学生学号和姓名：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    s.SId, 
    s.Sname
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
GROUP BY 
    s.SId, 
    s.Sname
HAVING 
    COUNT(DISTINCT sc.CId) = 2;
  1. 查询男生、女生人数：
1
2
3
4
5
6
7
SELECT 
    Ssex, 
    COUNT(*) AS 'Number_of_Students'
FROM 
    Student
GROUP BY 
    Ssex;
  1. 查询名字中含有「风」字的学生信息：
1
2
3
4
5
6
SELECT 
    *
FROM 
    Student
WHERE 
    Sname LIKE '%风%';
  1. 查询同名同性学生名单，并统计同名人数：
1
2
3
4
5
6
7
8
9
10
11
SELECT 
    Sname, 
    Ssex, 
    COUNT(*) AS 'Number_of_Same_Name'
FROM 
    Student
GROUP BY 
    Sname, 
    Ssex
HAVING 
    COUNT(*) > 1;
  1. 查询1990年出生的学生名单：
1
2
3
4
5
6
SELECT 
    *
FROM 
    Student
WHERE 
    YEAR(Sage) = 1990;
  1. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
    c.CId, 
    c.Cname, 
    AVG(sc.score) AS 'Avg_Score'
FROM 
    Course c
LEFT JOIN 
    SC sc ON c.CId = sc.CId
GROUP BY 
    c.CId, 
    c.Cname
ORDER BY 
    AVG(sc.score) DESC, 
    c.CId ASC;
  1. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    s.SId, 
    s.Sname, 
    AVG(sc.score) AS 'Avg_Score'
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
GROUP BY 
    s.SId, 
    s.Sname
HAVING 
    AVG(sc.score) >= 85;
  1. 查询课程名称为「数学」，且分数低于60的学生姓名和分数：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    s.Sname, 
    sc.score
FROM 
    SC sc
JOIN 
    Student s ON sc.SId = s.SId
JOIN 
    Course c ON sc.CId = c.CId
WHERE 
    c.Cname = '数学'
AND 
    sc.score < 60;
  1. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    s.SId, 
    s.Sname, 
    c.CId, 
    c.Cname, 
    sc.score
FROM 
    Student s
LEFT JOIN 
    SC sc ON s.SId = sc.SId
LEFT JOIN 
    Course c ON sc.CId = c.CId;
  1. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    s.Sname, 
    c.Cname, 
    sc.score
FROM 
    SC sc
JOIN 
    Student s ON sc.SId = s.SId
JOIN 
    Course c ON sc.CId = c.CId
WHERE 
    sc.score > 70;
  1. 查询不及格的课程：
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    c.CId, 
    c.Cname
FROM 
    SC sc
JOIN 
    Course c ON sc.CId = c.CId
WHERE 
    sc.score < 60
GROUP BY 
    c.CId, 
    c.Cname;
  1. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名：
1
2
3
4
5
6
7
8
9
10
11
SELECT 
    s.SId, 
    s.Sname
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
WHERE 
    sc.CId = '01'
AND 
    sc.score > 80;
  1. 求每门课程的学生人数：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    c.CId, 
    c.Cname, 
    COUNT(DISTINCT s.SId) AS 'Number_of_Students'
FROM 
    Course c
LEFT JOIN 
    SC sc ON c.CId = sc.CId
LEFT JOIN 
    Student s ON sc.SId = s.SId
GROUP BY 
    c.CId, 
    c.Cname;
  1. 假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 
    s.SId, 
    s.Sname, 
    sc.CId, 
    sc.score
FROM 
    SC sc
JOIN 
    Student s ON sc.SId = s.SId
JOIN 
    Course c ON sc.CId = c.CId
JOIN 
    Teacher t ON c.TId = t.TId
WHERE 
    t.Tname = '张三'
ORDER BY 
    sc.score DESC
LIMIT 1;
  1. 假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT 
    s.SId, 
    s.Sname, 
    sc.CId, 
    sc.score
FROM 
    SC sc
JOIN 
    Student s ON sc.SId = s.SId
JOIN 
    Course c ON sc.CId = c.CId
JOIN 
    Teacher t ON c.TId = t.TId
WHERE 
    t.Tname = '张三'
AND 
    sc.score = (
        SELECT 
            MAX(score)
        FROM 
            SC
        JOIN 
            Course ON SC.CId = Course.CId
        JOIN 
            Teacher ON Course.TId = Teacher.TId
        WHERE 
            Teacher.Tname = '张三'
    );
  1. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩：
1
2
3
4
5
6
7
8
9
SELECT 
    sc1.SId, 
    sc1.CId AS 'CId_1', 
    sc2.CId AS 'CId_2', 
    sc1.score
FROM 
    SC sc1
JOIN 
    SC sc2 ON sc1.SId = sc2.SId AND sc1.CId != sc2.CId AND sc1.score = sc2.score;
  1. 查询每门功成绩最好的前两名：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT 
    s.SId, 
    s.Sname, 
    sc.CId, 
    sc.score
FROM 
    SC sc
JOIN 
    Student s ON sc.SId = s.SId
WHERE 
    (
        SELECT 
            COUNT(DISTINCT score)
        FROM 
            SC sc2
        WHERE 
            sc2.score > sc.score
        AND 
            sc2.CId = sc.CId
    ) < 2
ORDER BY 
    sc.CId, 
    sc.score DESC;
  1. 统计每门课程的学生选修人数（超过5人的课程才统计）：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    c.CId, 
    c.Cname, 
    COUNT(DISTINCT sc.SId) AS 'Number_of_Students'
FROM 
    Course c
JOIN 
    SC sc ON c.CId = sc.CId
GROUP BY 
    c.CId, 
    c.Cname
HAVING 
    COUNT(DISTINCT sc.SId) > 5;
  1. 检索至少选修两门课程的学生学号：
1
2
3
4
5
6
7
8
9
10
SELECT 
    s.SId
FROM 
    Student s
JOIN 
    SC sc ON s.SId = sc.SId
GROUP BY 
    s.SId
HAVING 
    COUNT(DISTINCT sc.CId) >= 2;
  1. 查询选修了全部课程的学生信息：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT 
    s.SId, 
    s.Sname, 
    s.Sage, 
    s.Ssex
FROM 
    Student s
WHERE 
    NOT EXISTS (
        SELECT 
            c.CId
        FROM 
            Course c
        WHERE 
            NOT EXISTS (
                SELECT 
                    sc.SId
                FROM 
                    SC sc
                WHERE 
                    sc.SId = s.SId
                AND 
                    sc.CId = c.CId
            )
    );
  1. 查询各学生的年龄，只按年份来算：
1
2
3
4
5
6
SELECT 
    SId, 
    Sname, 
    YEAR(CURDATE()) - YEAR(Sage) AS 'Age'
FROM 
    Student;
  1. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一：
1
2
3
4
5
6
7
8
9
10
SELECT 
    SId, 
    Sname, 
    CASE 
        WHEN DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(Sage, '%m%d') 
        THEN YEAR(CURDATE()) - YEAR(Sage) - 1
        ELSE YEAR(CURDATE()) - YEAR(Sage)
    END AS 'Age'
FROM 
    Student;
  1. 查询本周过生日的学生：
1
2
3
4
5
6
7
8
9
10
SELECT 
    SId, 
    Sname, 
    Sage
FROM 
    Student
WHERE 
    WEEK(Sage) = WEEK(CURDATE())
AND 
    YEAR(Sage) != YEAR(CURDATE());
  1. 查询下周过生日的学生：
1
2
3
4
5
6
7
8
9
10
SELECT 
    SId, 
    Sname, 
    Sage
FROM 
    Student
WHERE 
    WEEK(Sage) = WEEK(DATE_ADD(CURDATE(), INTERVAL 7 DAY))
AND 
    YEAR(Sage) != YEAR(CURDATE());
  1. 查询本月过生日的学生：
1
2
3
4
5
6
7
8
9
10
SELECT 
    SId, 
    Sname, 
    Sage
FROM 
    Student
WHERE 
    MONTH(Sage) = MONTH(CURDATE())
AND 
    YEAR(Sage) != YEAR(CURDATE());
  1. 查询下月过生日的学生：
1
2
3
4
5
6
7
8
9
10
SELECT 
    SId, 
    Sname, 
    Sage
FROM 
    Student
WHERE 
    MONTH(Sage) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))
AND 
    YEAR(Sage) != YEAR(CURDATE());