You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
159 lines
5.7 KiB
159 lines
5.7 KiB
# Host: localhost (Version: 5.5.22)
|
|
# Date: 2024-06-13 18:04:47
|
|
# Generator: MySQL-Front 5.3 (Build 4.9)
|
|
|
|
/*!40101 SET NAMES utf8 */;
|
|
|
|
#
|
|
# Source for table "college"
|
|
#
|
|
|
|
DROP TABLE IF EXISTS `college`;
|
|
CREATE TABLE `college` (
|
|
`collegeID` int(11) NOT NULL,
|
|
`collegeName` varchar(200) NOT NULL COMMENT '课程名',
|
|
PRIMARY KEY (`collegeID`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
#
|
|
# Data for table "college"
|
|
#
|
|
|
|
INSERT INTO `college` VALUES (1,'计算机系'),(2,'设计系'),(3,'财经系');
|
|
|
|
#
|
|
# Source for table "role"
|
|
#
|
|
|
|
DROP TABLE IF EXISTS `role`;
|
|
CREATE TABLE `role` (
|
|
`roleID` int(11) NOT NULL,
|
|
`roleName` varchar(20) NOT NULL,
|
|
`permissions` varchar(255) DEFAULT NULL COMMENT '权限',
|
|
PRIMARY KEY (`roleID`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
#
|
|
# Data for table "role"
|
|
#
|
|
|
|
INSERT INTO `role` VALUES (0,'admin',NULL),(1,'teacher',NULL),(2,'student',NULL);
|
|
|
|
#
|
|
# Source for table "student"
|
|
#
|
|
|
|
DROP TABLE IF EXISTS `student`;
|
|
CREATE TABLE `student` (
|
|
`userID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`userName` varchar(200) NOT NULL,
|
|
`sex` varchar(20) DEFAULT NULL,
|
|
`birthYear` date DEFAULT NULL COMMENT '出生日期',
|
|
`grade` date DEFAULT NULL COMMENT '入学时间',
|
|
`collegeID` int(11) NOT NULL COMMENT '院系id',
|
|
PRIMARY KEY (`userID`),
|
|
KEY `collegeID` (`collegeID`),
|
|
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`collegeID`) REFERENCES `college` (`collegeID`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=10010 DEFAULT CHARSET=utf8;
|
|
|
|
#
|
|
# Data for table "student"
|
|
#
|
|
|
|
INSERT INTO `student` VALUES (10001,'小黄','女','1996-09-02','2015-09-02',1),(10002,'小米','女','1995-09-14','2015-09-02',3),(10003,'小陈','女','1996-09-02','2015-09-02',2),(10004,'小华','男','1996-09-02','2015-09-02',2),(10005,'小左','女','1996-09-02','2015-09-02',2),(10006,'小拉','女','1996-09-02','2015-09-02',1),(10007,'小红','男','1996-09-02','2015-09-02',1),(10008,'xiaohei','男','1996-09-02','2015-09-02',1),(10009,'xiaohong','男','1996-09-02','2015-09-02',1);
|
|
|
|
#
|
|
# Source for table "teacher"
|
|
#
|
|
|
|
DROP TABLE IF EXISTS `teacher`;
|
|
CREATE TABLE `teacher` (
|
|
`userID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`userName` varchar(200) NOT NULL,
|
|
`sex` varchar(20) DEFAULT NULL,
|
|
`birthYear` date NOT NULL,
|
|
`degree` varchar(20) DEFAULT NULL COMMENT '学历',
|
|
`title` varchar(255) DEFAULT NULL COMMENT '职称',
|
|
`grade` date DEFAULT NULL COMMENT '入职时间',
|
|
`collegeID` int(11) NOT NULL COMMENT '院系',
|
|
PRIMARY KEY (`userID`),
|
|
KEY `collegeID` (`collegeID`),
|
|
CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`collegeID`) REFERENCES `college` (`collegeID`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8;
|
|
|
|
#
|
|
# Data for table "teacher"
|
|
#
|
|
|
|
INSERT INTO `teacher` VALUES (1001,'刘老师','女','1990-03-08','硕士','副教授','2015-09-02',2),(1002,'张老师','男','1996-09-02','本科','普通教师','2015-09-02',1),(1003,'软老师','男','1996-09-02','硕士','助教','2017-07-07',1);
|
|
|
|
#
|
|
# Source for table "course"
|
|
#
|
|
|
|
DROP TABLE IF EXISTS `course`;
|
|
CREATE TABLE `course` (
|
|
`courseID` int(11) NOT NULL,
|
|
`courseName` varchar(200) NOT NULL COMMENT '课程名称',
|
|
`teacherID` int(11) NOT NULL,
|
|
`courseTime` varchar(200) DEFAULT NULL COMMENT '开课时间',
|
|
`classRoom` varchar(200) DEFAULT NULL COMMENT '开课地点',
|
|
`courseWeek` int(200) DEFAULT NULL COMMENT '学时',
|
|
`courseType` varchar(20) DEFAULT NULL COMMENT '课程类型',
|
|
`collegeID` int(11) NOT NULL COMMENT '所属院系',
|
|
`score` int(11) NOT NULL COMMENT '学分',
|
|
PRIMARY KEY (`courseID`),
|
|
KEY `collegeID` (`collegeID`),
|
|
KEY `teacherID` (`teacherID`),
|
|
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`collegeID`) REFERENCES `college` (`collegeID`),
|
|
CONSTRAINT `course_ibfk_2` FOREIGN KEY (`teacherID`) REFERENCES `teacher` (`userID`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
#
|
|
# Data for table "course"
|
|
#
|
|
|
|
INSERT INTO `course` VALUES (1,'C语言程序设计',1001,'周二','科401',18,'必修课',1,3),(2,'Python爬虫技巧',1001,'周四','X402',18,'必修课',1,3),(3,'数据结构',1001,'周四','科401',18,'必修课',1,2),(4,'Java程序设计',1002,'周五','科401',18,'必修课',1,2),(5,'英语',1002,'周四','X302',18,'必修课',2,2),(6,'服装设计',1003,'周一','科401',18,'选修课',2,2);
|
|
|
|
#
|
|
# Source for table "selectedcourse"
|
|
#
|
|
|
|
DROP TABLE IF EXISTS `selectedcourse`;
|
|
CREATE TABLE `selectedcourse` (
|
|
`courseID` int(11) NOT NULL,
|
|
`studentID` int(11) NOT NULL,
|
|
`mark` int(11) DEFAULT NULL COMMENT '成绩',
|
|
KEY `courseID` (`courseID`),
|
|
KEY `studentID` (`studentID`),
|
|
CONSTRAINT `selectedcourse_ibfk_1` FOREIGN KEY (`courseID`) REFERENCES `course` (`courseID`),
|
|
CONSTRAINT `selectedcourse_ibfk_2` FOREIGN KEY (`studentID`) REFERENCES `student` (`userID`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
#
|
|
# Data for table "selectedcourse"
|
|
#
|
|
|
|
INSERT INTO `selectedcourse` VALUES (2,10001,12),(1,10001,95),(1,10002,66),(1,10003,90),(2,10003,99),(5,10001,NULL),(3,10001,90),(6,10001,90);
|
|
|
|
#
|
|
# Source for table "userlogin"
|
|
#
|
|
|
|
DROP TABLE IF EXISTS `userlogin`;
|
|
CREATE TABLE `userlogin` (
|
|
`userID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`userName` varchar(200) NOT NULL,
|
|
`password` varchar(200) NOT NULL,
|
|
`role` int(11) NOT NULL DEFAULT '2' COMMENT '角色权限',
|
|
PRIMARY KEY (`userID`),
|
|
KEY `role` (`role`),
|
|
CONSTRAINT `userlogin_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`roleID`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
|
|
|
|
#
|
|
# Data for table "userlogin"
|
|
#
|
|
|
|
INSERT INTO `userlogin` VALUES (1,'admin','123',0),(8,'10001','123456',2),(9,'10002','123456',2),(10,'10003','123',2),(11,'10005','123',2),(12,'10004','123',2),(13,'10006','123',2),(14,'1001','123',1),(15,'1002','123',1),(16,'1003','123',1),(17,'10007','123',2),(18,'10008','123',2),(19,'10009','123',2);
|