问题3：生成SQL文件

2024/8/1 13:32:45

请根据上面的功能书，设计对应的数据库、编写对应的SQL语言，每个表都需要填充5条以上的样例数据

2024/8/1 13:32:45

好的，下面是基于你的功能说明书设计的数据库结构和相应的SQL语句。为了便于理解和实施，假设我们使用的是MySQL数据库。

### 数据库设计

#### 1. 用户表 (users)
```sql
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    role ENUM('admin', 'teacher', 'student') NOT NULL,
    avatar VARCHAR(255)
);

-- 插入样例数据
INSERT INTO users (username, password, email, role, avatar) VALUES 
('admin', 'password123', 'admin@example.com', 'admin', 'avatar1.png'),
('teacher1', 'password123', 'teacher1@example.com', 'teacher', 'avatar2.png'),
('teacher2', 'password123', 'teacher2@example.com', 'teacher', 'avatar3.png'),
('student1', 'password123', 'student1@example.com', 'student', 'avatar4.png'),
('student2', 'password123', 'student2@example.com', 'student', 'avatar5.png');
```

#### 2. 班级表 (classes)
```sql
CREATE TABLE classes (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,
    description TEXT,
    semester VARCHAR(20)
);

-- 插入样例数据
INSERT INTO classes (class_name, description, semester) VALUES 
('Math 101', 'Basic Math Class', 'Fall 2023'),
('Physics 101', 'Basic Physics Class', 'Fall 2023'),
('Chemistry 101', 'Basic Chemistry Class', 'Fall 2023'),
('Biology 101', 'Basic Biology Class', 'Fall 2023'),
('History 101', 'Basic History Class', 'Fall 2023');
```

#### 3. 用户班级关系表 (user_classes)
```sql
CREATE TABLE user_classes (
    user_class_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    class_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

-- 插入样例数据
INSERT INTO user_classes (user_id, class_id) VALUES 
(2, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);
```

#### 4. 步程表 (courses)
```sql
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    syllabus TEXT
);

-- 插入样例数据
INSERT INTO courses (course_name, description, syllabus) VALUES 
('Algebra', 'Algebra Course', 'Algebra Syllabus'),
('Mechanics', 'Mechanics Course', 'Mechanics Syllabus'),
('Organic Chemistry', 'Organic Chemistry Course', 'Organic Chemistry Syllabus'),
('Genetics', 'Genetics Course', 'Genetics Syllabus'),
('World History', 'World History Course', 'World History Syllabus');
```

#### 5. 班级步程关系表 (class_courses)
```sql
CREATE TABLE class_courses (
    class_course_id INT AUTO_INCREMENT PRIMARY KEY,
    class_id INT,
    course_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 插入样例数据
INSERT INTO class_courses (class_id, course_id) VALUES 
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);
```

#### 6. 视频表 (videos)
```sql
CREATE TABLE videos (
    video_id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    url VARCHAR(255) NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 插入样例数据
INSERT INTO videos (course_id, title, description, url) VALUES 
(1, 'Algebra Basics', 'Introduction to Algebra', 'video1.mp4'),
(2, 'Newton\'s Laws', 'Introduction to Mechanics', 'video2.mp4'),
(3, 'Organic Molecules', 'Introduction to Organic Chemistry', 'video3.mp4'),
(4, 'DNA Structure', 'Introduction to Genetics', 'video4.mp4'),
(5, 'Ancient Civilizations', 'Introduction to World History', 'video5.mp4');
```

#### 7. 测试题表 (tests)
```sql
CREATE TABLE tests (
    test_id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    type ENUM('multiple_choice', 'short_answer', 'fill_in_the_blank') NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 插入样例数据
INSERT INTO tests (course_id, question, answer, type) VALUES 
(1, 'What is 2+2?', '4', 'multiple_choice'),
(2, 'State Newton\'s first law of motion.', 'An object in motion stays in motion...', 'short_answer'),
(3, 'Name a common organic molecule.', 'Methane', 'fill_in_the_blank'),
(4, 'What is the basic unit of heredity?', 'Gene', 'short_answer'),
(5, 'What year did the Roman Empire fall?', '476 AD', 'fill_in_the_blank');
```

#### 8. 用户评论表 (comments)
```sql
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT,
    user_id INT,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 插入样例数据
INSERT INTO comments (course_id, user_id, content) VALUES 
(1, 4, 'Great introduction to Algebra!'),
(2, 5, 'Found this lecture on Newton\'s laws very helpful.'),
(3, 4, 'Organic molecules are fascinating!'),
(4, 5, 'DNA structure lecture was very clear.'),
(5, 4, 'Loved learning about ancient civilizations.');
```

以上设计包括了用户管理、班级管理、步程管理、学习步程、视频上传、步程发布、测试题管理和用户评论的各个功能模块。每个表都包含了5条样例数据，便于后续功能的测试和验证。希望这能帮助你更好地实现你的项目。