/*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50720
Source Host           : localhost:3306
Source Database       : student-manager

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

Date: 2018-05-27 15:05:02
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`student_id`  int(11) UNSIGNED NOT NULL COMMENT '学生姓名' ,
`subject_id`  int(11) UNSIGNED NOT NULL COMMENT '课程ID' ,
`teacher_id`  int(11) UNSIGNED NOT NULL COMMENT '课程教师ID' ,
`score`  int(4) UNSIGNED NOT NULL COMMENT '课程分数' ,
FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX `student_uuid` (`student_id`) USING BTREE ,
INDEX `subject_uuid` (`subject_id`) USING BTREE ,
INDEX `teacher_uuid` (`teacher_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生学号' ,
`username`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名' ,
`password`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生密码' ,
`birth`  date NULL DEFAULT NULL COMMENT '学生出生日期' ,
`sex`  tinyint(1) UNSIGNED NULL DEFAULT NULL COMMENT '学生性别' ,
`address`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生家庭住址' ,
`credit`  varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生身份证号' ,
PRIMARY KEY (`id`),
UNIQUE INDEX `uuid` (`id`) USING BTREE ,
UNIQUE INDEX `credit` (`credit`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '曹可乐', '1234556', null, null, null, '123212345654323456');
COMMIT;

-- ----------------------------
-- Table structure for student_subject
-- ----------------------------
DROP TABLE IF EXISTS `student_subject`;
CREATE TABLE `student_subject` (
`student_id`  int(32) UNSIGNED NOT NULL ,
`subject_id`  int(11) UNSIGNED NULL DEFAULT NULL ,
PRIMARY KEY (`student_id`),
FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX `subject_uuid` (`subject_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of student_subject
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`id`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程ID' ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名' ,
PRIMARY KEY (`id`),
UNIQUE INDEX `name` (`name`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of subject
-- ----------------------------
BEGIN;
INSERT INTO `subject` VALUES ('3', 'CHINESE'), ('2', 'ENGLISH'), ('1', 'MATH');
COMMIT;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '教师唯一ID' ,
`username`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师姓名' ,
`password`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师密码' ,
PRIMARY KEY (`id`),
UNIQUE INDEX `uuid` (`id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for tearcher_subject
-- ----------------------------
DROP TABLE IF EXISTS `tearcher_subject`;
CREATE TABLE `tearcher_subject` (
`teacher_id`  int(11) UNSIGNED NOT NULL COMMENT '教师ID' ,
`subject_id`  int(11) UNSIGNED NOT NULL COMMENT '课程ID' ,
FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX `teacher_uuid` (`teacher_id`) USING BTREE ,
INDEX `subject_uuid` (`subject_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of tearcher_subject
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Auto increment value for student
-- ----------------------------
ALTER TABLE `student` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for subject
-- ----------------------------
ALTER TABLE `subject` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for teacher
-- ----------------------------
ALTER TABLE `teacher` AUTO_INCREMENT=1;