CREATE DATABASE huafeng_db;
use
huafeng_db;
DROP TABLE IF EXISTS `huafeng_db`.`t_scores`;
DROP TABLE IF EXISTS `huafeng_db`.`t_students`;
DROP TABLE IF EXISTS `huafeng_db`.`t_class`;
CREATE TABLE `huafeng_db`.`t_class` ( `class_id` int(11) NOT NULL, `class_name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES (
'1'
,
'一年级'
);
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES (
'2'
,
'二年级'
);
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES (
'3'
,
'三年级'
);
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES (
'4'
,
'四年级'
);
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES (
'5'
,
'五年级'
);
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES (
'6'
,
'六年级'
);
CREATE TABLE `t_students` ( `student_id` int(11) NOT NULL AUTO_INCREMENT, `student_name` varchar(32) NOT NULL, `sex` int(1) DEFAULT NULL, `seq_no` int(11) DEFAULT NULL, `class_id` int(11) NOT NULL,
PRIMARY KEY (`student_id`),
KEY `class_id` (`class_id`),
CONSTRAINT `t_students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(
'小红'
,0,1,
'1'
);
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(
'小青'
,0,2,
'2'
);
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(
'小明'
,1,3,
'3'
);
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(
'小兰'
,0,4,
'4'
);
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(
'小米'
,1,5,
'5'
);
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(
'小白'
,1,6,
'6'
);
CREATE TABLE `huafeng_db`.`t_scores` ( `score_id` int(11) NOT NULL AUTO_INCREMENT, `course_name` varchar(64) DEFAULT NULL, `score` double(3,2) DEFAULT NULL, `student_id` int(11) DEFAULT NULL,
PRIMARY KEY (`score_id`),
KEY `student_id` (`student_id`),
CONSTRAINT `t_scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `t_students` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES (
'1'
,
'语文'
,
'90'
,
'1'
);
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES (
'2'
,
'数学'
,
'97'
,
'1'
);
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES (
'3'
,
'英语'
,
'95'
,
'1'
);
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES (
'4'
,
'语文'
,
'92'
,
'2'
);
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES (
'5'
,
'数学'
,
'100'
,
'2'
);
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES (
'6'
,
'英语'
,
'98'
,
'2'
);