学籍管理系统数据库sql
时间: 2023-12-28 19:05:28 浏览: 113
学籍管理系统数据库的SQL设计通常包括对学生基本信息、班级、专业、院系、课程、学生成绩、奖惩信息等数据的管理。下面是一个简单的学籍管理系统数据库的SQL示例:
1. 创建表格:
```sql
CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(50),
gender VARCHAR(10),
birthday DATE,
class_id INT,
major_id INT,
department_id INT
);
CREATE TABLE class (
class_id INT PRIMARY KEY,
class_name VARCHAR(50)
);
CREATE TABLE major (
major_id INT PRIMARY KEY,
major_name VARCHAR(50)
);
CREATE TABLE department (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE course (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);
CREATE TABLE grade (
student_id INT,
course_id INT,
score INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (course_id) REFERENCES course(course_id)
);
CREATE TABLE punishment (
student_id INT,
punishment_reason VARCHAR(100),
PRIMARY KEY (student_id),
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
```
2. 插入数据:
```sql
INSERT INTO student (student_id, name, gender, birthday, class_id, major_id, department_id)
VALUES (1, '张三', '男', '1990-01-01',1, 1, 1);
INSERT INTO class (class_id, class_name)
VALUES (1, '计算机科学与技术1班');
INSERT INTO major (major_id, major_name)
VALUES (1, '计算机科学与技术');
INSERT INTO department (department_id, department_name)
VALUES (1, '计算机学院');
INSERT INTO course (course_id, course_name)
VALUES (1, '数据库');
INSERT INTO grade (student_id, course_id, score)
VALUES (1, 1, 90);
INSERT INTO punishment (student_id, punishment_reason)
VALUES (1, '违纪');
```
3. 查询数据:
```sql
-- 查询学生信息
SELECT * FROM student;
-- 查询某个班级的学生信息
SELECT * FROM student WHERE class_id = 1;
-- 查询某个学生的成绩
SELECT * FROM grade WHERE student_id = 1;
-- 查询某个学生的奖惩信息
SELECT * FROM punishment WHERE student_id = 1;
```
4. 更新数据:
```sql
-- 更新学生信息
UPDATE student SET name = '李四' WHERE student_id = 1;
-- 更新某个学生的成绩
UPDATE grade SET score = 95 WHERE student_id = 1 AND course_id = 1;
-- 更新某个学生的奖惩信息
UPDATE punishment SET punishment_reason = '违纪处分' WHERE student_id = 1;
```
5. 删除数据:
```sql
-- 删除学生信息
DELETE FROM student WHERE student_id = 1;
-- 删除某个学生的成绩
DELETE FROM grade WHERE student_id = 1 AND course_id = 1;
-- 删除某个学生的奖惩信息
DELETE FROM punishment WHERE student_id = 1;
```