文章目录
- 1、建表
-
- 1.1 学生表和插入数据
- 1.2 教师表和数据
- 1.3 课程表和数据
- 1.4 成绩表和数据
- 2、数据库数据
-
- 2.1 学生表
- 2.2 教师表
- 2.3 课程表
- 2.4 得分表
1、建表
1.1 学生表和插入数据
|学号| 姓名|生日|性别|
|–|–|–|–|–|
|s_id |s_name |s_birth|s_sex|
sql脚本
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) NOT NULL COMMENT '学号',
`s_name` varchar(20) NOT NULL COMMENT '姓名',
`s_birth` varchar(20) NOT NULL COMMENT '生日',
`s_sex` varchar(10) NOT NULL COMMENT '性别',
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');
1.2 教师表和数据
| t_id | t_name |
|---|---|
| j教师编号 | 教师姓名 |
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` varchar(20) NOT NULL COMMENT '教师主键',
`t_name` varchar(20) NOT NULL COMMENT '教师姓名',
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');
1.3 课程表和数据
| 课程号 | 课程名称 | 学号 |
|---|---|---|
| c_id | c_name | t_id |
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` varchar(20) NOT NULL COMMENT '课程号',
`c_name` varchar(20) NOT NULL COMMENT '课程名称',
`t_id` varchar(20) NOT NULL COMMENT '学号',
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
1.4 成绩表和数据
| s_id | c_id | s_score |
|---|---|---|
| 学号 | 课程号 | 得分 |
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`s_id` varchar(20) NOT NULL COMMENT '学号',
`c_id` varchar(20) NOT NULL COMMENT '课程号',
`s_score` int(3) DEFAULT NULL COMMENT '得分',
PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='联合主键';
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', '80');
INSERT INTO `score` VALUES ('01', '02', '90');
INSERT INTO `score` VALUES ('01', '03', '99');
INSERT INTO `score` VALUES ('02', '01', '70');
INSERT INTO `score` VALUES ('02', '02', '60');
INSERT INTO `score` VALUES ('02', '03', '80');
INSERT INTO `score` VALUES ('03', '01', '80');
INSERT INTO `score` VALUES ('03', '02', '80');
INSERT INTO `score` VALUES ('03', '03', '80');
INSERT INTO `score` VALUES ('04', '01', '50');
INSERT INTO `score` VALUES ('04', '02', '30');
INSERT INTO `score` VALUES ('04', '03', '20');
INSERT INTO `score` VALUES ('05', '01', '76');
INSERT INTO `score` VALUES ('05', '02', '87');
INSERT INTO `score` VALUES ('06', '01', '31');
INSERT INTO `score` VALUES ('06', '03', '34');
INSERT INTO `score` VALUES ('07', '02', '89');
INSERT INTO `score` VALUES ('07', '03', '98');
2、数据库数据
2.1 学生表
2.2 教师表
2.3 课程表
2.4 得分表
（1-10）：https://blog.csdn.net/weixin_43304253/article/details/122378005
