📄 教务系统数据库设计.sql
字号:
UNIQUE(studentId),
FOREIGN KEY(classId) REFERENCES Class(classId)
)
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020101', '赵刚','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020102', '王新','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020103', '刘必','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020104', '李力','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020105', '苏田','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020106', '李力','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020107', '吴刚','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020108', '李良新','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020109', '刘必天','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020110', '李如力','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020111', '苏东田','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010201','200601020112', '李公力','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010202','200601020201', '赵刚','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010202','200601020202', '王新','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010202','200601020203', '钱丽','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010202','200601020204', '孙玉','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010202','200601020205', '罗山','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006010202','200601020206', '孙玉','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020101','200602010101', '赵小刚','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020101','200602010102', '王小新','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020101','200602010103', '刘小必','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020101','200602010104', '李小力','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020101','200602010105', '苏小田','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020101','200602010106', '李小力','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020201', '赵大刚','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020202', '王大新','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020203', '钱大丽','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020204', '孙大玉','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020205', '罗大山','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020206', '孙大玉','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020207', '赵中刚','1')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020208', '王中新','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020209', '钱中丽','2')
INSERT INTO Student( classId, studentId,studentName,sex)
VALUES('2006020202','200602020210', '孙中玉','1')
CREATE TABLE TeachingAssignment( /* 14.教学任务 */
year1 VARCHAR(4) NOT NULL, /* 学年1 */
year2 VARCHAR(4) NOT NULL, /* 学年2 */
term VARCHAR(1) NOT NULL, /* 学期 */
classId VARCHAR(10) NOT NULL, /* 班级代码 */
courseId VARCHAR(5) NOT NULL, /* 课程代码 */
numberOfStudy NUMERIC DEFAULT 0 /* 修读人数 */
CHECK(numberOfStudy>=0),
missionNo VARCHAR(4), /* 课堂编号 */
deptNoOfOfferCourse VARCHAR(2) NOT NULL, /* 开课单位代码 */
teacherId VARCHAR(5), /* 任课教师代码 */
UNIQUE(year1,year2,term,classId,courseId),
FOREIGN KEY(classId) REFERENCES Class(classId),
FOREIGN KEY(courseId) REFERENCES Course(courseId),
);
CREATE TABLE Academic( /* 15.成绩(学生修读表) */
classId VARCHAR(10) NOT NULL, /* 班级代码 */
studentId VARCHAR(12) NOT NULL, /* 学号 */
termNo VARCHAR(2) NOT NULL, /* 学期序号 */
year1 VARCHAR(4) NOT NULL, /* 学年1 */
year2 VARCHAR(4) NOT NULL, /* 学年2 */
term VARCHAR(1) NOT NULL, /* 学期 */
courseId VARCHAR(5) NOT NULL, /* 课程代码 */
mark NUMERIC /* 成绩 */
CHECK(mark>=0 AND mark<=100),
UNIQUE(studentId,termNo,courseId),
FOREIGN KEY(studentId) REFERENCES Student(studentId),
FOREIGN KEY(courseId) REFERENCES Course(courseId)
)
INSERT INTO Academic VALUES('2006010201','200601020101','1','2006','2007','1','01001',80)
INSERT INTO Academic VALUES('2006010201','200601020101','1','2006','2007','1','01002',75)
INSERT INTO Academic VALUES('2006010201','200601020101','2','2006','2007','2','01003',60)
INSERT INTO Academic VALUES('2006010201','200601020101','2','2006','2007','2','01004',55)
INSERT INTO Academic VALUES('2006010201','200601020101','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010201','200601020101','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010201','200601020102','1','2006','2007','1','01001',70)
INSERT INTO Academic VALUES('2006010201','200601020102','1','2006','2007','1','01002',95)
INSERT INTO Academic VALUES('2006010201','200601020102','2','2006','2007','2','01003',80)
INSERT INTO Academic VALUES('2006010201','200601020102','2','2006','2007','2','01004',55)
INSERT INTO Academic VALUES('2006010201','200601020102','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010201','200601020102','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010201','200601020103','1','2006','2007','1','01001',50)
INSERT INTO Academic VALUES('2006010201','200601020103','1','2006','2007','1','01002',65)
INSERT INTO Academic VALUES('2006010201','200601020103','2','2006','2007','2','01003',70)
INSERT INTO Academic VALUES('2006010201','200601020103','2','2006','2007','2','01004',95)
INSERT INTO Academic VALUES('2006010201','200601020103','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010201','200601020103','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010201','200601020104','1','2006','2007','1','01001',100)
INSERT INTO Academic VALUES('2006010201','200601020104','1','2006','2007','1','01002',75)
INSERT INTO Academic VALUES('2006010201','200601020104','2','2006','2007','2','01003',50)
INSERT INTO Academic VALUES('2006010201','200601020104','2','2006','2007','2','01004',85)
INSERT INTO Academic VALUES('2006010201','200601020104','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010201','200601020104','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010201','200601020105','1','2006','2007','1','01001',90)
INSERT INTO Academic VALUES('2006010201','200601020105','1','2006','2007','1','01002',65)
INSERT INTO Academic VALUES('2006010201','200601020105','2','2006','2007','2','01003',90)
INSERT INTO Academic VALUES('2006010201','200601020105','2','2006','2007','2','01004',95)
INSERT INTO Academic VALUES('2006010201','200601020105','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010201','200601020105','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010201','200601020106','1','2006','2007','1','01001',50)
INSERT INTO Academic VALUES('2006010201','200601020106','1','2006','2007','1','01002',65)
INSERT INTO Academic VALUES('2006010201','200601020106','2','2006','2007','2','01003',90)
INSERT INTO Academic VALUES('2006010201','200601020106','2','2006','2007','2','01004',55)
INSERT INTO Academic VALUES('2006010201','200601020106','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010201','200601020106','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010202','200601020201','1','2006','2007','1','01001',80)
INSERT INTO Academic VALUES('2006010202','200601020201','1','2006','2007','1','01002',75)
INSERT INTO Academic VALUES('2006010202','200601020201','2','2006','2007','2','01003',60)
INSERT INTO Academic VALUES('2006010202','200601020201','2','2006','2007','2','01004',55)
INSERT INTO Academic VALUES('2006010202','200601020201','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010202','200601020201','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010202','200601020202','1','2006','2007','1','01001',70)
INSERT INTO Academic VALUES('2006010202','200601020202','1','2006','2007','1','01002',95)
INSERT INTO Academic VALUES('2006010202','200601020202','2','2006','2007','2','01003',80)
INSERT INTO Academic VALUES('2006010202','200601020202','2','2006','2007','2','01004',55)
INSERT INTO Academic VALUES('2006010202','200601020202','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010202','200601020202','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010202','200601020203','1','2006','2007','1','01001',50)
INSERT INTO Academic VALUES('2006010202','200601020203','1','2006','2007','1','01002',65)
INSERT INTO Academic VALUES('2006010202','200601020203','2','2006','2007','2','01003',70)
INSERT INTO Academic VALUES('2006010202','200601020203','2','2006','2007','2','01004',95)
INSERT INTO Academic VALUES('2006010202','200601020203','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010202','200601020203','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010202','200601020204','1','2006','2007','1','01001',70)
INSERT INTO Academic VALUES('2006010202','200601020204','1','2006','2007','1','01002',95)
INSERT INTO Academic VALUES('2006010202','200601020204','2','2006','2007','2','01003',80)
INSERT INTO Academic VALUES('2006010202','200601020204','2','2006','2007','2','01004',55)
INSERT INTO Academic VALUES('2006010202','200601020204','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010202','200601020204','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010202','200601020205','1','2006','2007','1','01001',50)
INSERT INTO Academic VALUES('2006010202','200601020205','1','2006','2007','1','01002',65)
INSERT INTO Academic VALUES('2006010202','200601020205','2','2006','2007','2','01003',70)
INSERT INTO Academic VALUES('2006010202','200601020205','2','2006','2007','2','01004',95)
INSERT INTO Academic VALUES('2006010202','200601020205','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010202','200601020205','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006010202','200601020206','1','2006','2007','1','01001',50)
INSERT INTO Academic VALUES('2006010202','200601020206','1','2006','2007','1','01002',65)
INSERT INTO Academic VALUES('2006010202','200601020206','2','2006','2007','2','01003',40)
INSERT INTO Academic VALUES('2006010202','200601020206','2','2006','2007','2','01004',95)
INSERT INTO Academic VALUES('2006010202','200601020206','3','2007','2008','1','01005',0)
INSERT INTO Academic VALUES('2006010202','200601020206','3','2007','2008','1','01006',0)
INSERT INTO Academic VALUES('2006020101','200602010101','1','2006','2007','1','01001',80)
INSERT INTO Academic VALUES('2006020101','200602010101','1','2006','2007','1','01002',75)
INSERT INTO Academic VALUES('2006020101','200602010101','2','2006','2007','2','02001',60)
INSERT INTO Academic VALUES('2006020101','200602010101','3','2007','2008','1','02002',0)
INSERT INTO Academic VALUES('2006020101','200602010102','1','2006','2007','1','01001',50)
INSERT INTO Academic VALUES('2006020101','200602010102','1','2006','2007','1','01002',75)
INSERT INTO Academic VALUES('2006020101','200602010102','2','2006','2007','2','02001',60)
INSERT INTO Academic VALUES('2006020101','200602010102','3','2007','2008','1','02002',0)
INSERT INTO Academic VALUES('2006020101','200602010103','1','2006','2007','1','01001',90)
INSERT INTO Academic VALUES('2006020101','200602010103','1','2006','2007','1','01002',55)
INSERT INTO Academic VALUES('2006020101','200602010103','2','2006','2007','2','02001',60)
INSERT INTO Academic VALUES('2006020101','200602010103','3','2007','2008','1','02002',0)
INSERT INTO Academic VALUES('2006020101','200602010104','1','2006','2007','1','01001',90)
INSERT INTO Academic VALUES('2006020101','200602010104','1','2006','2007','1','01002',55)
INSERT INTO Academic VALUES('2006020101','200602010104','2','2006','2007','2','02001',55)
INSERT INTO Academic VALUES('2006020101','200602010104','3','2007','2008','1','02002',0)⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -