MySQL 试题

了解SQL语句中关键字的书写顺序与执行顺序十分重要，这十分有利于我们编写与理解SQL语句。

关键字书写顺序（由上至下）：

select
distinct
from
join
on
where
group by
having
union （all）
order by
limit

关键字执行顺序（由上至下）：

from
on
join
where
group by
having
select
distinct
union （all）
order by
limit

笔试一

创建表数据

/* 学生表 */
CREATE TABLE Student(
    s_id VARCHAR(20),                        /* 学生ID */
    s_name VARCHAR(20) NOT NULL DEFAULT '',  /* 学生姓名 */
    s_birth VARCHAR(20) NOT NULL DEFAULT '', /* 出生日期 */
    s_sex VARCHAR(10) NOT NULL DEFAULT '',   /* 学生性别 */
    PRIMARY KEY(s_id) 
);
/* 课程表 */
CREATE TABLE Course(
    c_id VARCHAR(20),                        /* 课程ID */
    c_name VARCHAR(20) NOT NULL DEFAULT '',  /* 课程名称 */
    t_id VARCHAR(20) NOT NULL,               /* 教师ID */
    PRIMARY KEY(c_id) 
);
/* 教师表 */
CREATE TABLE Teacher(
    t_id VARCHAR(20),                        /* 教师ID */
    t_name VARCHAR(20) NOT NULL DEFAULT '',  /* 教师姓名*/
    PRIMARY KEY(t_id)
);
/* 成绩表 */
CREATE TABLE `Score`(
    s_id VARCHAR(20),                        /* 学生ID */
    c_id VARCHAR(20),                        /* 课程ID */
    s_score INT(3),                          /* 成绩值 */
    PRIMARY KEY(s_id,c_id)
);
/* 以下为测试数据 */

truncate table teacher ;
truncate table student;
truncate table course;
truncate table score ;

insert into teacher values(1, '苍老师');
insert into teacher values(2, '罗老师');
insert into teacher values(3, '武老师');

insert into student values(1, '李磊', '2001-01-01', '男');
insert into student values(2, '韩梅梅', '2001-01-01', '女');
insert into student values(3, '吉姆', '2001-01-01', '男');
insert into student values(4, '莉莉', '2001-01-01', '女');
insert into student values(5, '露西', '2001-01-01', '女');
insert into student values(6, '李明', '2001-01-01', '男');
insert into student values(7, '王大叔', '2001-01-01', '男');
insert into student values(8, '苏珊', '2001-01-01', '女');

insert into course values(1, '生理卫生', 1);
insert into course values(2, '法外狂徒', 2);
insert into course values(3, '维修电脑', 3);

insert into score values(4, 2, 55);
insert into score values(5, 1, 83);
insert into score values(3, 2, 38);
insert into score values(1, 2, 23);
insert into score values(8, 2, 75);
insert into score values(8, 1, 90);
insert into score values(3, 1, 42);
insert into score values(4, 3, 80);
insert into score values(6, 1, 60);
insert into score values(1, 3, 98);
insert into score values(2, 2, 100);
insert into score values(6, 3, 26);
insert into score values(4, 1, 91);
insert into score values(7, 1, 33);
insert into score values(5, 3, 50);
insert into score values(7, 3, 83);
insert into score values(7, 2, 64);
insert into score values(3, 3, 97);
insert into score values(5, 2, 33);
insert into score values(6, 2, 79);
insert into score values(2, 1, 99);
insert into score values(2, 3, 24);
insert into score values(8, 3, 78);

题目一：

查询两门以上不及格课程的同学的学号，姓名及其平均成绩。

思路：

首先通过子查询在成绩表（score）中过滤出有两门以上不及格课程的同学的学号（s_id），之后通过学号（s_id）完成成绩表（score）与子查询和学生表（student）的关联，再根据每个学号（s_id）进行分组展示。

SQL语句：

SELECT
    score.s_id AS '学号',
    student.s_name AS '姓名',
    AVG( score.s_score ) AS '平均成绩' 
FROM
    score
    JOIN ( SELECT score.s_id FROM score WHERE score.s_score < 60 GROUP BY score.s_id HAVING COUNT( score.s_id ) >= 2 ) AS querySID ON score.s_id = querySID.s_id
    JOIN student ON score.s_id = student.s_id 
GROUP BY
    score.s_id 
ORDER BY
    score.s_id DESC;

结果：

截屏2023-10-06 20.25.03.png

题目二：

统计每门课程的学生选修人数（超过2人的课程才统计），输出课程号、课程名和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序。

思路：

通过课程号（c_id）关联成绩表（score）与课程表（course），成绩表（score）中同一课程号（c_id）出现的次数代表了盖课程的选修人数，通过HAVING过滤超过2人的课程并分组展示排序后的结果。

SQL语句：

SELECT
    course.c_id AS '课程号',
    course.c_name AS '课程名', 
    COUNT( score.c_id ) AS '选修人数' 
FROM
    course
    JOIN score ON course.c_id = score.c_id 
GROUP BY
    score.c_id 
HAVING
    COUNT( score.c_id ) > 2 
ORDER BY
    COUNT( score.c_id ) DESC,
    course.c_id ASC;

截屏2023-10-06 20.54.10.png

题目三：

使用分段[100-85],[85-70],[70-60],[不及格]来统计各科成绩，分别统计各分数段人数、课程号和课程名称。

思路：

首先在成绩表（score）中，通过课程号（c_id）分组查询出该课程每个分数段的成绩数量（其中通过IF函数，以成绩区间作为判断条件，并返回指定值0或1，通过SUM求和），并将该查询结果与课程表（course）通过课程号（c_id）关联。

SQL语句：

SELECT
    course.c_id AS '课程号',
    course.c_name AS '课程名称',
    score_result.`100-85`,
    score_result.`85-70`,
    score_result.`70-60`,
    score_result.`不及格` 
FROM
    course
    JOIN (
    SELECT
        score.c_id,
        SUM(
        IF
        ( score.s_score BETWEEN 85 AND 100, 1, 0 )) AS '100-85',
        SUM(
        IF
        ( score.s_score BETWEEN 70 AND 85, 1, 0 )) AS '85-70',
        SUM(
        IF
        ( score.s_score BETWEEN 60 AND 70, 1, 0 )) AS '70-60',
        SUM(
        IF
        ( score.s_score < 60, 1, 0 )) AS '不及格' 
    FROM
        score 
    GROUP BY
        score.c_id 
    ) AS score_result ON course.c_id = score_result.c_id;

截屏2023-10-06 21.15.42.png

题目四:

查询不同老师所教不同课程平均分从高到低显示。

思路：

通过教师号（t_id）关联教师表（teacher）与课程表（course），再通过课课程号（c_id）关联成绩表（score），之后根据不同老师所教不同课程分组展示平均成绩并排序。

SQL语句：

SELECT
    teacher.t_name AS '教师',
    course.c_name AS '课程',
    AVG( score.s_score ) AS '平均值' 
FROM
    teacher
    JOIN course ON teacher.t_id = course.t_id
    JOIN score ON course.c_id = score.c_id 
GROUP BY
    teacher.t_name,
    course.c_name
Order BY
    AVG( score.s_score ) DESC;

截屏2023-10-06 21.43.56.png

题目五：

查询平均成绩大于60的所有学生的学号、姓名和平均成绩。

思路：

通过学号（s_id）关联学生表（student）与成绩表（score），并通过学号分组，通过HAVING过滤后，降序展示查询的数据。

SQL语句：

SELECT
    student.s_id AS '学号',
    student.s_name AS '姓名',
    AVG( score.s_score ) AS '平均成绩' 
FROM
    student
    JOIN score ON student.s_id = score.s_id 
GROUP BY
    student.s_id 
HAVING
    AVG( score.s_score ) > 60 
ORDER BY
    student.s_id DESC;

截屏2023-10-06 21.53.50.png