一.表信息
1.1 表结构
-- 学生表
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT, -- 学生ID
name VARCHAR(50) NOT NULL, -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(10), -- 学生性别
major VARCHAR(50) -- 学生专业
);
-- 老师表
CREATE TABLE teachers (
teacher_id INT PRIMARY KEY AUTO_INCREMENT, -- 老师ID
name VARCHAR(50) NOT NULL, -- 老师姓名
department VARCHAR(50) -- 所属部门
);
-- 课程表
CREATE TABLE courses (
course_id INT PRIMARY KEY AUTO_INCREMENT, -- 课程ID
course_name VARCHAR(100) NOT NULL, -- 课程名称
teacher_id INT, -- 教师ID（外键）
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
-- 学生选课表
CREATE TABLE student_courses (
student_id INT, -- 学生ID（外键）
course_id INT, -- 课程ID（外键）
grade DECIMAL(3, 1), -- 成绩
PRIMARY KEY (student_id, course_id), -- 组合主键
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
1.2 测试数据
-- 插入学生数据
INSERT INTO students (name, age, gender, major) VALUES
('张三', 22, '男', '计算机科学'),
('李四', 21, '女', '数学与统计'),
('王五', 23, '男', '物理学'),
('赵六', 24, '女', '化学工程'),
('孙七', 25, '男', '电子工程'),
('李八', 26, '女', '机械工程'),
('张九', 22, '男', '生物学'),
('王十', 23, '女', '化学工程');
-- 插入老师数据
INSERT INTO teachers (name, department) VALUES
('张教授', '计算机科学与技术学院'),
('李教授', '数学与统计学院'),
('王教授', '物理与光电工程学院'),
('赵教授', '化学与化工学院');
-- 插入课程数据
INSERT INTO courses (course_name, teacher_id) VALUES
('数据结构', 1),
('高等数学', 2),
('量子力学', 3),
('有机化学', 4),
('线性代数', 2),
('物理实验', 3),
('计算机网络', 1),
('化学实验', 4);
-- 插入学生选课数据
INSERT INTO student_courses (student_id, course_id, grade) VALUES
(1, 1, 90), -- 张三，数据结构
(1, 2, 85), -- 张三，高等数学
(1, 3, 92), -- 张三，量子力学
(2, 2, 78), -- 李四，高等数学
(2, 4, 95), -- 李四，有机化学
(3, 1, 88), -- 王五，数据结构
(3, 3, 80), -- 王五，量子力学
(3, 5, 91), -- 王五，线性代数
(4, 2, 75), -- 赵六，高等数学
(4, 4, 85), -- 赵六，有机化学
(4, 6, 90), -- 赵六，物理实验
(5, 7, 86), -- 孙七，计算机网络
(5, 8, 92), -- 孙七，化学实验
(6, 3, 95), -- 李八，量子力学
(6, 5, 84), -- 李八，线性代数
(7, 1, 90), -- 张九，数据结构
(7, 7, 88), -- 张九，计算机网络
(8, 6, 79), -- 王十，物理实验
(8, 8, 82); -- 王十，化学实验
题目：
基本查询（易）
- 查询所有学生的姓名和年龄。
- 查询所有学生的姓名、年龄和专业，按年龄升序排序。
- 查询所有教师的姓名和部门。
- 查询所有课程的名称和对应的教师姓名。
- 查询学生表中所有年龄大于22岁的学生姓名。
- 查询每位学生选修的课程数量。
- 查询选修了“数据结构”课程的学生姓名。
- 查询每门课程的学生数量。
- 查询成绩高于90分的学生姓名和他们的成绩。
- 查询所有学生选修的课程及成绩。
条件筛选与聚合（中等）
- 查询所有学生的姓名和他们所选课程的名称。
- 查询选修课程“量子力学”的学生姓名和他们的成绩。
- 查询每个学生的姓名以及他们在“高等数学”课程中的成绩。
- 查询选修了多于2门课程的学生姓名和所选课程数量。
- 查询成绩在80分以上的所有学生姓名和成绩。
- 查询每门课程的平均成绩。
- 查询成绩在“量子力学”课程中排名前3的学生。
- 查询所有学生在所有课程中的成绩排名。
- 查询某位教师教授的课程中，所有学生成绩的平均值。
- 查询每门课程中成绩最低的学生姓名和成绩。
复杂查询（高级）
- 查询成绩在90分以上的学生，并按成绩降序排列。
- 查询每门课程中，成绩大于课程平均成绩的学生姓名和成绩。
- 查询成绩低于60分的学生姓名和他们的成绩。
- 查询选修了“有机化学”课程的学生，并按成绩降序排序。
- 查询选修了至少3门课程的学生姓名。
- 查询每位学生在所有课程中的平均成绩。
- 查询课程“线性代数”中成绩排名前5的学生。
- 查询每位学生的最高成绩和最低成绩。
- 查询学生成绩的总和（按课程分组），并按总成绩降序排序。
- 查询每个学生的课程数、总成绩、平均成绩，并按平均成绩降序排列。
子查询与联合查询（更难）
- 查询所有成绩在某个课程中高于该课程平均成绩的学生姓名。
- 查询每门课程的最高成绩及该成绩对应的学生姓名。
- 查询每位学生选修的课程数量和成绩总和。
- 查询每门课程的学生数量，并显示出至少有3个学生选修的课程名称。
- 查询选修过“计算机网络”课程的学生，并显示他们在“物理实验”课程中的成绩。
- 查询成绩比所有“有机化学”课程选修学生的平均成绩低的学生姓名和成绩。
- 查询每位学生选修的课程名称及成绩，按学生姓名排序。
- 查询成绩最高的3门课程及其对应的教师姓名。
- 查询每门课程中，成绩低于班级平均成绩的学生姓名。
- 查询没有选修“数据结构”课程的学生姓名。
窗口函数与高级聚合（最难）
- 查询每位学生的成绩排名（按课程分组），并显示成绩和排名。
- 查询每门课程中成绩排名前10%的学生姓名。
- 查询成绩大于某门课程平均成绩的学生姓名，并按成绩降序排列。
- 查询每门课程的课程平均成绩，及每个学生与课程平均成绩的差异。
- 查询每位学生在所有课程中的累计成绩，按成绩总和降序排序。
- 查询成绩低于某个课程平均成绩的学生姓名和成绩，并按成绩升序排列。
- 查询每门课程中，成绩和课程平均成绩差异最大和最小的学生姓名和成绩。
- 查询选修最多课程的学生姓名和他们所选课程的数量。
- 查询每门课程选修人数最多的学生姓名。
- 查询每门课程的平均成绩和与之相关的最高成绩，按最高成绩排序。