如何使用MySQL构建高效的习题库系统？

MySQL是一种广泛使用的关系型数据库管理系统，其强大的功能和灵活性使其成为许多应用场景中的理想选择，下面将详细探讨如何使用MySQL来构建和管理一个习题库数据库：

创建数据库和表

1、创建数据库：首先需要创建一个数据库来存储所有的表和数据，可以创建一个名为mysql_test的数据库。

2、创建学生表：学生表用于存储学生的基本信息，包括学号、姓名、出生日期和性别，以下是创建学生表的SQL语句：

CREATE TABLE student (
    s_id INT PRIMARY KEY,
    s_name VARCHAR(8),
    s_birth DATE,
    s_sex VARCHAR(4)
);

3、插入学生数据：向学生表中插入一些示例数据：

INSERT INTO student VALUES
(1, '赵雷', '19900101', '男'),
(2, '钱电', '19901221', '男'),
(3, '孙风', '19900520', '男'),
...
(8, '王菊', '19900120', '女');

4、创建课程表：课程表用于存储课程的相关信息，包括课程编号、课程名称和教师编号，以下是创建课程表的SQL语句：

CREATE TABLE course (
    c_id VARCHAR(10) PRIMARY KEY,
    c_name VARCHAR(10),
    t_id VARCHAR(10)
);

5、插入课程数据：向课程表中插入一些示例数据：

INSERT INTO course VALUES
('01', '语文', '02'),
('02', '数学', '01'),
('03', '英语', '03');

6、创建教师表：教师表用于存储教师的基本信息，包括教师编号和教师姓名，以下是创建教师表的SQL语句：

CREATE TABLE teacher (
    t_id INT PRIMARY KEY,
    t_name VARCHAR(8)
);

7、插入教师数据：向教师表中插入一些示例数据：

INSERT INTO teacher VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');

8、创建成绩表：成绩表用于存储学生的课程成绩信息，包括学号、课程号和成绩，以下是创建成绩表的SQL语句：

CREATE TABLE score (
    s_id INT,
    c_id VARCHAR(10),
    s_score INT,
    PRIMARY KEY (s_id, c_id),
    FOREIGN KEY (s_id) REFERENCES student(s_id),
    FOREIGN KEY (c_id) REFERENCES course(c_id)
);

9、插入成绩数据：向成绩表中插入一些示例数据：

INSERT INTO score VALUES
(1, '01', 80),
(1, '02', 90),
(1, '03', 99),
...
(7, '03', 34);

练习题示例

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数：这个查询需要联接学生表、成绩表以及课程表，以筛选出在“01”课程中成绩高于“02”课程的学生信息及其课程分数。

SELECT s.*, sc1.s_score AS score_01, sc2.s_score AS score_02
FROM student s
JOIN score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE sc1.s_score > sc2.s_score;

2、查询学过“张三”老师授课的同学的信息：这个查询需要联接学生表、成绩表、课程表和教师表，以筛选出所有学习过“张三”老师授课课程的学生。

SELECT DISTINCT s.*
FROM student s
JOIN score sc ON s.s_id = sc.s_id
JOIN course c ON sc.c_id = c.c_id
JOIN teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三';

3、查询没学过“张三”老师授课的同学的信息：与上一个查询类似，但这次筛选的是未学习过“张三”老师授课课程的学生。

SELECT DISTINCT s.*
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
LEFT JOIN course c ON sc.c_id = c.c_id AND c.t_id = (SELECT t_id FROM teacher WHERE t_name = '张三')
WHERE c.c_id IS NULL;

4、查询至少有一门课与学号为“01”的同学所学相同的同学的信息：这个查询需要联接学生表和成绩表，以找出至少有一个相同课程的同学信息。

SELECT DISTINCT s1.*
FROM student s1
JOIN score sc1 ON s1.s_id = sc1.s_id
JOIN score sc2 ON sc1.c_id = sc2.c_id AND sc2.s_id = '01'
WHERE s1.s_id != '01';

5、统计每门课程的学生选修人数（超过5人的课程才统计）：这个查询需要对成绩表进行分组和计数，以统计每门课程的选修人数，并只显示选修人数超过5人的课程。

SELECT c_id, COUNT(DISTINCT s_id) AS student_count
FROM score
GROUP BY c_id
HAVING COUNT(DISTINCT s_id) > 5;

6、查询各科成绩最高分、最低分和平均分：这个查询需要对成绩表按课程编号进行分组，然后分别计算每个课程的最高分、最低分和平均分。

SELECT c_id, MAX(s_score) AS max_score, MIN(s_score) AS min_score, AVG(s_score) AS avg_score
FROM score
GROUP BY c_id;

7、按各科成绩进行排序，并显示排名：这个查询需要对成绩表按课程编号和成绩进行排序，然后添加一个排名列。

SELECT c_id, s_score, RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS rank
FROM score
ORDER BY c_id, rank;

8、查询学生的总成绩并进行排名：这个查询需要先计算每个学生的总成绩，然后按总成绩进行排名。

SELECT s_id, SUM(s_score) AS total_score, RANK() OVER (ORDER BY SUM(s_score) DESC) AS rank
FROM score
GROUP BY s_id;

9、查询不同老师所教不同课程平均分从高到低显示：这个查询需要联接成绩表、课程表和教师表，然后按教师编号和课程编号进行分组，计算平均分，并按平均分降序排列。

SELECT t.t_name, c.c_name, AVG(sc.s_score) AS avg_score
FROM score sc
JOIN course c ON sc.c_id = c.c_id
JOIN teacher t ON c.t_id = t.t_id
GROUP BY t.t_name, c.c_name
ORDER BY avg_score DESC;

10、查询名字中含有“风”字的学生信息：这个查询需要在学生表中筛选出名字中含有“风”字的学生信息。

SELECT *
FROM student
WHERE s_name LIKE '%风%';

相关问答FAQs

1、如何删除数据库？：要删除一个数据库，可以使用DROP DATABASE命令，要删除名为mysql_test的数据库，可以执行以下SQL语句：

DROP DATABASE IF EXISTS mysql_test CASCADE;

2、如何查看当前数据库中的所有表？：要查看当前数据库中的所有表，可以使用SHOW TABLES命令，要查看mysql_test数据库中的所有表，可以执行以下SQL语句：

USE mysql_test;
SHOW TABLES;