创建课本40页的学生数据库,并在该数据库下创建40页的3张表,并各添加前3条记录
时间: 2024-09-25 15:08:19 浏览: 14
创建课本40页的学生数据库并为其设计3张表格,分别是`Students`, `Courses`, 和 `Grades`,我们可以按照以下步骤进行:
首先,我们建立数据库和表格:
```sql
CREATE DATABASE StudentDatabase;
USE StudentDatabase;
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Gender VARCHAR(10),
Class INT
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Teacher VARCHAR(100),
CoursePages INT CHECK (CoursePages <= 40)
);
CREATE TABLE Grades (
GradeID INT AUTO_INCREMENT PRIMARY KEY,
StudentID INT,
CourseID INT,
PageNumber INT,
Score DECIMAL(3, 1),
FOREIGN KEY (StudentID) REFERENCES Students(ID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
```
接下来,我们分别向这3张表添加前3条记录:
对于`Students`表:
```sql
INSERT INTO Students (ID, Name, Age, Gender, Class)
VALUES (1, '张三', 18, '男', 1),
(2, '李四', 17, '女', 2),
(3, '王五', 19, '男', 1);
```
对于`Courses`表:
```sql
INSERT INTO Courses (CourseID, CourseName, Teacher, CoursePages)
VALUES (1, '数学基础', '刘老师', 10),
(2, '英语入门', '陈老师', 10),
(3, '计算机科学导论', '赵老师', 20);
```
对于`Grades`表,由于只给出了课程名和课程页数,这里暂无具体分数,我们先插入课程ID和对应的课程页数:
```sql
INSERT INTO Grades (StudentID, CourseID, PageNumber)
VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3),
(2, 2, 1), (2, 2, 2), (2, 2, 3),
(3, 3, 1), (3, 3, 2), (3, 3, 3); -- 示例,实际数据需根据情况填写
```