SQL数据库查询操作：学生选课系统数据检索

---

SQL数据库操作：构建学生选课系统

将从创建数据库表开始，然后插入数据，并最终执行一系列查询操作来检索和分析数据。

创建数据库表

首先，需要创建三个表：课程表（C）、学生表（S）和学生选课表（SC）。

课程表（C）

CREATE TABLE C (
    C CHAR(10) PRIMARY KEY, -- 课程编号为10位字符
    CNAME VARCHAR(50), -- 课程名称
    T VARCHAR(50) -- 教师名称
);

学生表（S）

CREATE TABLE S (
    S CHAR(10) PRIMARY KEY, -- 学生编号为10位字符
    SNAME VARCHAR(50), -- 学生姓名
    AGE INT, -- 学生年龄
    SEX CHAR(1) -- 学生性别，'M'表示男，'F'表示女
);

学生选课表（SC）

CREATE TABLE SC (
    S CHAR(10), -- 学生编号
    C CHAR(10), -- 课程编号
    SCORE INT, -- 分数
    PRIMARY KEY (S, C), -- 学生编号和课程编号组合作为主键
    FOREIGN KEY (S) REFERENCES S(S), -- 外键约束，学生编号对应学生表
    FOREIGN KEY (C) REFERENCES C(C) -- 外键约束，课程编号对应课程表
);

插入数据

接下来，我们将向这些表中插入数据。

插入课程数据到C表

INSERT INTO C (C, CNAME, T) VALUES
('C001', '数学', '张老师'),
('C002', '物理', '李老师'),
('C003', '化学', '王老师'),
('C004', '生物', '赵老师'),
('C005', '历史', '钱老师'),
('C006', '地理', '孙老师'),
('C007', '政治', '周老师'),
('C008', '英语', '吴老师'),
('C009', '体育', '郑老师'),
('C010', '计算机', '冯老师');

插入学生数据到S表

INSERT INTO S (S, SNAME, AGE, SEX) VALUES
('S001', '张三', 20, 'M'),
('S002', '李四', 21, 'M'),
('S003', '王五', 20, 'F'),
('S004', '赵六', 22, 'F'),
('S005', '钱七', 21, 'M'),
('S006', '孙八', 20, 'F'),
('S007', '周九', 22, 'M'),
('S008', '吴十', 21, 'F'),
('S009', '郑十一', 20, 'M'),
('S010', '王十二', 22, 'F');

插入学生选课数据到SC表

INSERT INTO SC (S, C, SCORE) VALUES
('S001', 'C001', 85),
('S001', 'C002', 90),
('S002', 'C001', 75),
('S002', 'C003', 80),
('S003', 'C002', 88),
('S003', 'C004', 92),
('S004', 'C003', 70),
('S004', 'C005', 85),
('S005', 'C004', 90),
('S005', 'C006', 82),
('S006', 'C005', 78),
('S006', 'C007', 85),
('S007', 'C006', 88),
('S007', 'C008', 90),
('S008', 'C007', 75),
('S008', 'C009', 80),
('S009', 'C008', 85),
('S009', 'C010', 90),
('S010', 'C009', 80),
('S010', 'C001', 88);

确保学生S001选修了所有课程：

UPDATE SC SET SCORE = 90 WHERE S = 'S001' AND C = 'C001';
UPDATE SC SET SCORE = 85 WHERE S = 'S001' AND C = 'C002';
UPDATE SC SET SCORE = 88 WHERE S = 'S001' AND C = 'C003';
-- 继续更新S001的其余课程分数...

现在，已经成功创建了学生选课系统的基本结构，并插入了初始数据。接下来，将执行一些查询操作来检索和分析这些数据。

数据检索与分析

有了数据库表和数据，我们现在可以进行一些有用的查询来检索和分析学生选课系统的数据。

1. 检索选修课程号为C002课程的学生学号与成绩

要找出选修了物理课（课程号C002）的学生学号和成绩，可以使用以下SQL查询：

SELECT S.S, SC.SCORE
FROM SC
JOIN S ON SC.S = S.S
JOIN C ON SC.C = C.C
WHERE C.C = 'C002';

这个查询通过连接学生表（S）、课程表（C）和学生选课表（SC），筛选出所有选修了课程号为C002的学生的学号和成绩。

2. 检索选修课程号为C002课程的学生的学号和姓名

如果还想知道这些学生的姓名，可以稍微修改上面的查询：

SELECT S.S, S.SNAME
FROM SC
JOIN S ON SC.S = S.S
JOIN C ON SC.C = C.C
WHERE C.C = 'C002';

3. 检索选修课程名为“计算机”的学生的学号与姓名

要找出选修了“计算机”这门课程的学生学号与姓名，我们可以使用如下查询：

SELECT S.S, S.SNAME
FROM SC
JOIN S ON SC.S = S.S
JOIN C ON SC.C = C.C
WHERE C.CNAME = '计算机';

4. 检索选修课程号为C002或C004课程的学生的学号

对于想找出选修了物理（C002）或生物（C004）的学生学号的需求，我们可以这样查询：

SELECT S.S
FROM SC
JOIN S ON SC.S = S.S
JOIN C ON SC.C = C.C
WHERE C.C = 'C002' OR C.C = 'C004';

5. 检索至少选修了课程号为C002和C004课程的学生的学号

要找出至少选修了物理和生物两门课程的学生学号，我们可以使用分组和计数：

SELECT SC.S
FROM SC
WHERE SC.C IN ('C002', 'C004')
GROUP BY SC.S
HAVING COUNT(DISTINCT SC.C) = 2;

6. 检索没有选修C002课程的学生的姓名

如果我们想找出没有选修物理课的学生姓名，可以使用左连接和空值检查：

SELECT S.SNAME
FROM S
LEFT JOIN SC ON S.S = SC.S
WHERE SC.C IS NULL OR SC.C <> 'C002';

7. 检索选修了全部课程的学生的姓名

对于检索选修了全部课程的学生姓名，我们首先需要确保有一个学生选修了所有课程，然后进行如下查询：

SELECT S.SNAME
FROM S
JOIN SC ON S.S = SC.S
GROUP BY S.S, S.SNAME
HAVING COUNT(DISTINCT SC.C) = (SELECT COUNT(*) FROM C);

8. 检索选修课程包含学生S003所学的课程的学生的学号

最后，如果我们想找出选修了学生S003所学课程的学生学号，可以使用子查询：

SELECT DISTINCT SC.S
FROM SC
WHERE SC.C IN (
    SELECT SC.C
    FROM SC
    WHERE SC.S = 'S003'
);