MySQL 试题
2023-10-07 本文已影响0人
寻心_0a46
了解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
