SQL学生成绩管理系统代码
时间: 2023-09-11 10:12:32 浏览: 45
以下是一个简单的学生成绩管理系统的 SQL 代码:
创建学生表:
```
CREATE TABLE students(
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
gender VARCHAR(10),
class_id INT
);
```
创建课程表:
```
CREATE TABLE courses(
id INT PRIMARY KEY,
name VARCHAR(50),
teacher VARCHAR(50),
credit INT
);
```
创建成绩表:
```
CREATE TABLE grades(
id INT PRIMARY KEY,
student_id INT,
course_id INT,
grade FLOAT,
FOREIGN KEY(student_id) REFERENCES students(id),
FOREIGN KEY(course_id) REFERENCES courses(id)
);
```
插入学生数据:
```
INSERT INTO students(id, name, age, gender, class_id)
VALUES
(1, '张三', 18, '男', 1),
(2, '李四', 19, '女', 1),
(3, '王五', 20, '男', 2),
(4, '赵六', 18, '女', 2);
```
插入课程数据:
```
INSERT INTO courses(id, name, teacher, credit)
VALUES
(1, '数学', '张老师', 4),
(2, '英语', '李老师', 3),
(3, '计算机', '王老师', 2);
```
插入成绩数据:
```
INSERT INTO grades(id, student_id, course_id, grade)
VALUES
(1, 1, 1, 90),
(2, 1, 2, 85),
(3, 2, 1, 92),
(4, 2, 2, 88),
(5, 3, 2, 78),
(6, 3, 3, 85),
(7, 4, 1, 88),
(8, 4, 3, 80);
```
查询每个学生的成绩:
```
SELECT s.name, c.name, g.grade
FROM students s
JOIN grades g ON s.id = g.student_id
JOIN courses c ON g.course_id = c.id;
```
查询每个学生的平均成绩:
```
SELECT s.name, AVG(g.grade) AS avg_grade
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id;
```
查询每门课程的平均成绩:
```
SELECT c.name, AVG(g.grade) AS avg_grade
FROM courses c
JOIN grades g ON c.id = g.course_id
GROUP BY c.id;
```