study/online_study_system.sql

/*
Navicat MySQL Data Transfer

Source Server         : aa
Source Server Version : 50022
Source Host           : localhost:3306
Source Database       : online_study_system

Target Server Type    : MYSQL
Target Server Version : 50022
File Encoding         : 65001

Date: 2020-08-04 19:51:02
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(26) NOT NULL COMMENT '课程名称',
  `teacher_id` int(11) NOT NULL COMMENT '教授老师工号',
  `period` int(11) default NULL COMMENT '学时',
  `price` int(11) NOT NULL COMMENT '售价',
  PRIMARY KEY  (`id`),
  KEY `teacherID` (`teacher_id`),
  CONSTRAINT `course_ibfk_2` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'C语言从入门到放弃', '25', '20', '10');
INSERT INTO `course` VALUES ('4', 'JAVA', '26', '18', '20');
INSERT INTO `course` VALUES ('7', '养猪入门', '38', '20', '10');
INSERT INTO `course` VALUES ('8', '从零开始入门炒股', '26', '50', '74');

-- ----------------------------
-- Table structure for `role`
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL COMMENT '类型名',
  `permissions` varchar(255) default NULL COMMENT '权限',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', 'admin', null);
INSERT INTO `role` VALUES ('2', 'teacher', null);
INSERT INTO `role` VALUES ('3', 'student', null);

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(16) NOT NULL COMMENT '姓名',
  `sex` varchar(1) default NULL COMMENT '性别',
  `birthday` date default NULL COMMENT '出生日期',
  `register_time` date default NULL COMMENT '注册日期',
  `balance` int(64) default '0' COMMENT '账户余额',
  PRIMARY KEY  (`id`),
  CONSTRAINT `student_userlogin_FK` FOREIGN KEY (`id`) REFERENCES `userlogin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('24', '小明', '男', '2017-10-03', '2017-11-03', '40');
INSERT INTO `student` VALUES ('39', '小红', '男', '1995-01-10', '2017-11-05', '0');
INSERT INTO `student` VALUES ('40', '小林', '女', '1990-06-01', '2017-11-05', '0');

-- ----------------------------
-- Table structure for `student_course`
-- ----------------------------
DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course` (
  `course_id` int(11) NOT NULL COMMENT '课程id',
  `student_id` int(11) NOT NULL COMMENT '学生id',
  `progress` int(11) NOT NULL default '1' COMMENT '当前学时',
  `mark` int(11) default NULL COMMENT '成绩',
  `has_exam` tinyint(1) NOT NULL default '0' COMMENT '是否完成考试（0：未完成；1：完成）',
  KEY `courseID` (`course_id`),
  KEY `studentID` (`student_id`),
  CONSTRAINT `selectedcourse_student_FK` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `student_course_course_FK` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student_course
-- ----------------------------
INSERT INTO `student_course` VALUES ('7', '24', '20', null, '1');
INSERT INTO `student_course` VALUES ('4', '24', '2', null, '0');
INSERT INTO `student_course` VALUES ('1', '24', '0', null, '0');

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(16) NOT NULL COMMENT '姓名',
  `sex` varchar(1) default NULL COMMENT '性别',
  `birthday` date default NULL COMMENT '出生日期',
  `degree` varchar(20) default NULL COMMENT '学历',
  `register_time` date default NULL COMMENT '注册日期',
  PRIMARY KEY  (`id`),
  CONSTRAINT `teacher_userlogin_FK` FOREIGN KEY (`id`) REFERENCES `userlogin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('25', '李老师', '男', '2000-11-03', '本科', '2017-11-03');
INSERT INTO `teacher` VALUES ('26', '张老师', '女', '2017-11-03', '研究生', '2017-11-03');
INSERT INTO `teacher` VALUES ('37', '刘老师', '男', '1997-11-13', '硕士', '2017-11-05');
INSERT INTO `teacher` VALUES ('38', '曹老师', '男', '1958-11-12', '博士', '2017-11-05');

-- ----------------------------
-- Table structure for `userlogin`
-- ----------------------------
DROP TABLE IF EXISTS `userlogin`;
CREATE TABLE `userlogin` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(16) NOT NULL COMMENT '姓名',
  `password` char(56) NOT NULL COMMENT '密码',
  `role` int(2) NOT NULL default '2' COMMENT '角色权限',
  PRIMARY KEY  (`id`),
  KEY `role` (`role`),
  CONSTRAINT `userLogin_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of userlogin
-- ----------------------------
INSERT INTO `userlogin` VALUES ('23', 'admin', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '1');
INSERT INTO `userlogin` VALUES ('24', '小明', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '3');
INSERT INTO `userlogin` VALUES ('25', '李老师', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '2');
INSERT INTO `userlogin` VALUES ('26', '张老师', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '2');
INSERT INTO `userlogin` VALUES ('37', '刘老师', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '2');
INSERT INTO `userlogin` VALUES ('38', '曹老师', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '2');
INSERT INTO `userlogin` VALUES ('39', '小红', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '3');
INSERT INTO `userlogin` VALUES ('40', '小林', 'b2d1f375db625e65a4a63ba0c7b29bdd738392cd83c38b8216c10c4d', '3');