用sql编写学生选课系统

在构建一个简单的学生选课系统，SQL主要用于存储和管理数据。这里是一个基本的数据库设计以及几个相关的SQL操作示例：

1. **创建表（Tables）**:
- 学生表 (Students): 可能包含字段如 `ID`, `姓名`, `学号`, `邮箱` 等。
- 课程表 (Courses): 包含 `ID`, `名称`, `教师`, 等信息。
- 选课表 (Enrollments): 存储学生与课程的关系，通常有 `学生ID`, `课程ID`, 和 `成绩` 字段。

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    StudentNo VARCHAR(10),
    Email VARCHAR(50)
);

CREATE TABLE Courses (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Teacher VARCHAR(50)
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    Score DECIMAL(3, 1),
    FOREIGN KEY (StudentID) REFERENCES Students(ID),
    FOREIGN KEY (CourseID) REFERENCES Courses(ID)
);

2. **插入数据**:

   INSERT INTO Students (ID, Name, StudentNo, Email) VALUES (1, '张三', '001', 'zhangsan@example.com');
   INSERT INTO Courses (ID, Name, Teacher) VALUES (1, '数学', '李四');

3. **查询选课信息**:

   SELECT s.Name AS '学生姓名', c.Name AS '课程名称', e.Score
   FROM Students s
   JOIN Enrollments e ON s.ID = e.StudentID
   JOIN Courses c ON e.CourseID = c.ID;

4. **添加或删除选课记录**:

   -- 添加选课
   INSERT INTO Enrollments (StudentID, CourseID, Score) VALUES (1, 1, 90);

   -- 删除选课
   DELETE FROM Enrollments WHERE StudentID = 1 AND CourseID = 1;