用sql写学生成绩查询管理系统
时间: 2023-07-20 22:12:03 浏览: 52
好的,以下是一个简单的学生成绩查询管理系统的 SQL 代码:
创建学生表:
```
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
gender VARCHAR(10),
age INT,
class VARCHAR(50)
);
```
创建成绩表:
```
CREATE TABLE scores (
id INT PRIMARY KEY,
student_id INT,
course VARCHAR(50),
score INT,
FOREIGN KEY (student_id) REFERENCES students(id)
);
```
插入学生数据:
```
INSERT INTO students (id, name, gender, age, class)
VALUES
(1, '张三', '男', 18, '一班'),
(2, '李四', '女', 19, '二班'),
(3, '王五', '男', 20, '三班'),
(4, '赵六', '女', 21, '四班');
```
插入成绩数据:
```
INSERT INTO scores (id, student_id, course, score)
VALUES
(1, 1, '语文', 80),
(2, 1, '数学', 90),
(3, 1, '英语', 85),
(4, 2, '语文', 85),
(5, 2, '数学', 95),
(6, 2, '英语', 90),
(7, 3, '语文', 70),
(8, 3, '数学', 80),
(9, 3, '英语', 75),
(10, 4, '语文', 75),
(11, 4, '数学', 85),
(12, 4, '英语', 80);
```
查询所有学生的成绩:
```
SELECT s.id, s.name, s.class, sc.course, sc.score
FROM students s
JOIN scores sc
ON s.id = sc.student_id;
```
查询某个学生的成绩:
```
SELECT s.id, s.name, s.class, sc.course, sc.score
FROM students s
JOIN scores sc
ON s.id = sc.student_id
WHERE s.name = '张三';
```
查询某门课程的成绩:
```
SELECT s.id, s.name, s.class, sc.course, sc.score
FROM students s
JOIN scores sc
ON s.id = sc.student_id
WHERE sc.course = '数学';
```
查询某个学生某门课程的成绩:
```
SELECT s.id, s.name, s.class, sc.course, sc.score
FROM students s
JOIN scores sc
ON s.id = sc.student_id
WHERE s.name = '张三'
AND sc.course = '数学';
```
以上是一个简单的学生成绩查询管理系统的 SQL 代码,希望能对您有所帮助。