学生成绩管理系统
数据库设计
使用数据库为scoreDB
班级信息表class
字段名 |
描述 |
数据类型 |
约束 |
classNo |
班级编号 |
char(10) |
primary key |
className |
班级名称 |
varchar(20) |
not null |
institute |
所属学院 |
varchar(20) |
not null |
grade |
年级 |
int |
not null |
classNum |
班级人数 |
int |
not null |
学生信息表student
字段名 |
描述 |
数据类型 |
约束 |
stuNo |
学号 |
char(10) |
primary key |
stuName |
姓名 |
varchar(20) |
not null |
sex |
性别 |
int |
not null |
birthday |
出生日期 |
date |
not null |
nat |
民族 |
varchar(20) |
not null |
classNo |
所属班级 |
varchar(20) |
not null |
课程信息表course
字段名 |
属性 |
数据类型 |
约束 |
courseNo |
课程号 |
char(10) |
primary key |
courseName |
课程名 |
varchar(20) |
not null |
credit |
学分 |
int |
not null |
courseHour |
课时数 |
int |
not null |
priorCourse |
先修课程 |
varchar(20) |
not null |
成绩表score
字段名 |
属性 |
数据类型 |
约束 |
stuNo |
学号 |
char(10) |
primary key |
courseNo |
课程号 |
char(10) |
primary key |
term |
开课学期 |
char(10) |
not null |
score |
成绩 |
int |
not null |
账号表account
字段名 |
描述 |
数据类型 |
约束 |
id |
用户id |
int |
primary key ,auto_increament |
username |
用户名 |
varchar(20) |
not null |
password |
密码 |
varchar(20) |
not null |
authority |
用户权限 |
int |
not null |
详细建表sql
use scoreDB;
SELECT concat('DROP TABLE IF EXISTS' , table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'scoreDB';
drop table IF EXISTS class;
CREATE TABLE class(
classNo CHAR(10) PRIMARY KEY ,
className VARCHAR (20) NOT NULL,
institute VARCHAR (20) NOT NULL,
grade INT NOT NULL,
classNum INT NOT NULL
);
INSERT INTO class (classNo, className, institute, grade, classNum) VALUES
('20011', '通信201','通信学院',1, 1);
drop table IF EXISTS student;
CREATE TABLE student(
stuNo CHAR(10) PRIMARY KEY,
stuName VARCHAR (20) NOT NULL,
sex INT NOT NULL,
birthday DATE NOT NULL,
nat VARCHAR(20) NOT NULL,
classNo VARCHAR (20) NOT NULL
);
INSERT INTO student (stuNo, stuName, sex, birthday, nat, classNo) VALUES
('20066','王兰花',2,'2000-01-01','汉族','20011');
INSERT INTO student (stuNo, stuName, sex, birthday, nat, classNo) VALUES
('20068','李子豪',1,'2000-06-06','汉族','20011');
INSERT INTO student (stuNo, stuName, sex, birthday, nat, classNo) VALUES
('20088','苏梅',2,'2000-02-12','汉族','20011');
UPDATE class SET classNum = (select count(*) from student where classNo='20011')
where classNo='20011';
drop TABLE IF EXISTS course;
CREATE TABLE course(
courseNo CHAR(10) PRIMARY KEY,
courseName VARCHAR (20) NOT NULL,
credit INT NOT NULL,
courseHour INT NOT NULL,
priorCourse VARCHAR (20) NOT NULL
);
INSERT INTO course (courseNo, courseName, credit, courseHour, priorCourse) VALUES
('ke-001','高等数学',4, 60, 'null');
INSERT INTO course (courseNo, courseName, credit, courseHour, priorCourse) VALUES
('ke-002','线性代数',4, 60, 'null');
INSERT INTO course (courseNo, courseName, credit, courseHour, priorCourse) VALUES
('ke-003','通信原理',4, 60, '高等数学');
INSERT INTO course (courseNo, courseName, credit, courseHour, priorCourse) VALUES
('ke-004','电子技术基础',4, 60, '高等数学');
INSERT INTO course (courseNo, courseName, credit, courseHour, priorCourse) VALUES
('ke-005','电工学',4, 60, '高等数学');
INSERT INTO course (courseNo, courseName, credit, courseHour, priorCourse) VALUES
('ke-006','自动控制原理',4, 60, '高等数学');
drop TABLE IF EXISTS score;
CREATE TABLE score(
stuNo CHAR(10) ,
courseNo CHAR(10) ,
term VARCHAR (10) NOT NULL,
score INT NOT NULL,
PRIMARY KEY (stuNo, courseNo)
);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20066', 'ke-001', '2020年春季学期', 80);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20066', 'ke-002', '2020年春季学期', 88);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20066', 'ke-003', '2020年春季学期', 88);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20066', 'ke-004', '2020年春季学期', 88);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20066', 'ke-005', '2020年春季学期', 88);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20066', 'ke-006', '2020年春季学期', 88);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20068', 'ke-001', '2020年春季学期', 60);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20068', 'ke-002', '2020年春季学期', 68);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20068', 'ke-003', '2020年春季学期', 68);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20068', 'ke-004', '2020年春季学期', 68);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20068', 'ke-005', '2020年春季学期', 68);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20068', 'ke-006', '2020年春季学期', 68);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20088', 'ke-001', '2020年春季学期', 90);
INSERT INTO score (stuNo, courseNo, term, score) VALUES
('20088', 'ke-002', '2020年春季学期', 98