SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cno` char(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tno` int(3) DEFAULT NULL,
PRIMARY KEY (`cno`) USING BTREE,
INDEX `tno`(`tno`) USING BTREE,
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3-105', '计算机导论', 825);
INSERT INTO `course` VALUES ('3-245', '操作系统', 804);
INSERT INTO `course` VALUES ('6-166', '数字电路', 856);
INSERT INTO `course` VALUES ('9-888', '高等数学', 831);
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`low` int(11) DEFAULT NULL,
`upp` int(11) DEFAULT NULL,
`rank` char(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci COMMENT = '等级表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (90, 100, 'A');
INSERT INTO `grade` VALUES (80, 89, 'B');
INSERT INTO `grade` VALUES (70, 79, 'C');
INSERT INTO `grade` VALUES (60, 69, 'D');
INSERT INTO `grade` VALUES (0, 59, 'E');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sno` int(3) DEFAULT NULL,
`cno` char(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`degree` decimal(4, 1) DEFAULT NULL,
INDEX `sno`(`sno`) USING BTREE,
INDEX `cno`(`cno`) USING BTREE,
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (103, '3-245', 86.0);
INSERT INTO `score` VALUES (105, '3-245', 75.0);
INSERT INTO `score` VALUES (109, '3-245', 68.0);
INSERT INTO `score` VALUES (103, '3-105', 92.0);
INSERT INTO `score` VALUES (105, '3-105', 88.0);
INSERT INTO `score` VALUES (109, '3-105', 76.0);
INSERT INTO `score` VALUES (101, '3-105', 64.0);
INSERT INTO `score` VALUES (107, '3-105', 91.0);
INSERT INTO `score` VALUES (108, '3-105', 78.0);
INSERT INTO `score` VALUES (101, '6-166', 85.0);
INSERT INTO `score` VALUES (107, '6-166', 79.0);
INSERT INTO `score` VALUES (108, '6-166', 81.0);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` int(3) NOT NULL,
`sname` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`ssex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sbirthday` datetime(0) DEFAULT NULL,
`class` int(5) DEFAULT NULL,
PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '李军', '男', '1976-02-20 00:00:00', 95033);
INSERT INTO `student` VALUES (103, '陆君', '男', '1974-06-03 00:00:00', 95031);
INSERT INTO `student` VALUES (105, '匡明', '男', '1975-10-02 00:00:00', 95031);
INSERT INTO `student` VALUES (107, '王丽', '女', '1976-01-23 00:00:00', 95033);
INSERT INTO `student` VALUES (108, '曾华', '男', '1977-09-01 00:00:00', 95033);
INSERT INTO `student` VALUES (109, '王芳', '女', '1975-02-10 00:00:00', 95031);
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tno` int(3) NOT NULL,
`tname` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tsex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tbirthday` datetime(0) DEFAULT NULL,
`prof` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`depart` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '教师表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (804, '李成', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `teacher` VALUES (825, '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `teacher` VALUES (831, '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
INSERT INTO `teacher` VALUES (856, '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');
SET FOREIGN_KEY_CHECKS = 1;