mysql学生成绩数据库_MySQL数据库设计——以学生成绩数据库为例

出处：https://blog.csdn.net/sinat_35988648/article/details/74279681

-- ----------------------------

-- Table structure for course

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`course_num` int(11) NOT NULL COMMENT '课程号',

`course_name` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '课程名',

`course_hour` int(2) NOT NULL COMMENT '课程学时',

`course_score` varchar(10) CHARACTER SET utf8 NOT NULL COMMENT '课程学分',

PRIMARY KEY (`course_num`),

KEY `course_num` (`course_num`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='课程信息表';

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('1', '概率论', '5', '2');

INSERT INTO `course` VALUES ('2', 'MySQL', '5', '2');

INSERT INTO `course` VALUES ('3', '英语', '5', '3');

INSERT INTO `course` VALUES ('4', '毛概', '2', '5');

-- ----------------------------

-- Table structure for score

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

`score_id` int(11) NOT NULL,

`course_num` int(11) NOT NULL,

`student_num` int(11) NOT NULL,

`score` int(3) NOT NULL COMMENT '分数',

PRIMARY KEY (`score_id`),

KEY `course_num` (`course_num`),

KEY `student_num` (`student_num`),

CONSTRAINT `score_ibfk_1` FOREIGN KEY (`course_num`) REFERENCES `course` (`course_num`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `score_ibfk_2` FOREIGN KEY (`student_num`) REFERENCES `student` (`student_num`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='学生成绩表';

-- ----------------------------

-- Records of score

-- ----------------------------

INSERT INTO `score` VALUES ('1', '1', '15001', '89');

INSERT INTO `score` VALUES ('2', '1', '15002', '78');

INSERT INTO `score` VALUES ('3', '1', '15003', '80');

INSERT INTO `score` VALUES ('4', '1', '16004', '78');

INSERT INTO `score` VALUES ('5', '2', '15001', '85');

INSERT INTO `score` VALUES ('6', '2', '15002', '78');

INSERT INTO `score` VALUES ('7', '2', '15003', '75');

INSERT INTO `score` VALUES ('8', '2', '16004', '89');

INSERT INTO `score` VALUES ('9', '3', '15001', '87');

INSERT INTO `score` VALUES ('10', '3', '15002', '77');

INSERT INTO `score` VALUES ('11', '3', '15003', '88');

INSERT INTO `score` VALUES ('12', '3', '16004', '90');

INSERT INTO `score` VALUES ('13', '4', '15001', '90');

INSERT INTO `score` VALUES ('14', '4', '15002', '98');

INSERT INTO `score` VALUES ('15', '4', '15003', '89');

INSERT INTO `score` VALUES ('16', '4', '16004', '88');

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`student_num` int(11) NOT NULL COMMENT '学生学号',

`student_name` varchar(5) CHARACTER SET utf8 NOT NULL COMMENT '学生姓名',

`student_sex` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT '男' COMMENT '学生性别',

`student_birthday` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '学生生日',

PRIMARY KEY (`student_num`),

KEY `student_num` (`student_num`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='学生基本信息表';

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('15001', 'Mark', '男', '1997-02-19');

INSERT INTO `student` VALUES ('15002', 'Wen', '男', '1997-09-16');

INSERT INTO `student` VALUES ('15003', 'Lee', '女', '1997-03-12');

INSERT INTO `student` VALUES ('16004', 'Mary', '女', '1996-07-12');

---------------------

作者：dpqb

来源：CSDN

原文：https://blog.csdn.net/sinat_35988648/article/details/74279681

版权声明：本文为博主原创文章，转载请附上博文链接！