Python之路day10-数据库基本操作

Eva_J 博客链接: https://www.cnblogs.com/Eva-J/articles/10544358.html

表结构如下: 

创建表结构及插入数据sql: 

-- ----------------------------
-- 创建wlj_db库
-- ----------------------------
DROP DATABASE IF EXISTS `wlj_db`;
CREATE DATABASE IF NOT EXISTS wlj_db default charset utf8 COLLATE utf8_general_ci;
USE wlj_db;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- 创建class表 cid为主键
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (`cid` int(11) NOT NULL AUTO_INCREMENT,`caption` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY (`cid`)
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- 批量插入class表数据
-- ----------------------------
INSERT INTO `class` VALUES (1, '三年二班');
INSERT INTO `class` VALUES (2, '三年三班');
INSERT INTO `class` VALUES (3, '一年二班');-- ----------------------------
-- 创建course表 cid为主键
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (`cid` int(11) NOT NULL AUTO_INCREMENT,`cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`teacher_id` int(11) NOT NULL,PRIMARY KEY (`cid`) USING BTREE,INDEX `fk_course_teacher`(`teacher_id`) USING BTREE,CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- 批量插入course表数据
-- ----------------------------
INSERT INTO `course` VALUES (1, '生物', 1);
INSERT INTO `course` VALUES (2, '物理', 1);
INSERT INTO `course` VALUES (3, '体育', 3);-- ----------------------------
-- 创建score表 sid为主键
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (`sid` int(11) NOT NULL AUTO_INCREMENT,`student_id` int(11) NOT NULL,`course_id` int(11) NOT NULL,`num` int(11) NOT NULL,PRIMARY KEY (`sid`) USING BTREE,INDEX `fk_score_student`(`student_id`) USING BTREE,INDEX `fk_score_course`(`course_id`) USING BTREE,CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 53 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- 批量插入score表数据
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 2, 60);
INSERT INTO `score` VALUES (2, 2, 3, 100);
INSERT INTO `score` VALUES (3, 3, 1, 59);-- ----------------------------
-- 创建student学生表，sid 为主键
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (`sid` int(11) NOT NULL AUTO_INCREMENT,`gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`class_id` int(11) NOT NULL,`sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY (`sid`) USING BTREE,INDEX `fk_class`(`class_id`) USING BTREE,CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- 批量插入学生数据
-- ----------------------------
INSERT INTO `student` VALUES (1, '女', 1, '钢蛋');
INSERT INTO `student` VALUES (2, '女', 1, '铁锤');
INSERT INTO `student` VALUES (3, '男', 2, '山炮');-- ----------------------------
-- 创建teacher教师表， tid 为主键
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (`tid` int(11) NOT NULL AUTO_INCREMENT,`tname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- 批量插入教师数据
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '波多');
INSERT INTO `teacher` VALUES (2, '苍空');
INSERT INTO `teacher` VALUES (3, '饭岛');SET FOREIGN_KEY_CHECKS = 1;COMMIT;

转载于:https://www.cnblogs.com/wanglj/p/11547497.html

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！