1 /*
2 Navicat MySQL Data Transfer
3
4 Source Server : lizebo's MYSQL
5 Source Server Type : MySQL
6 Source Server Version : 50720
7 Source Host : localhost:3306
8 Source Schema : sql练习
9
10 Target Server Type : MySQL
11 Target Server Version : 50720
12 File Encoding : 65001
13
14 Date: 07/11/2017 20:09:05
15 */
16
17 SET NAMES utf8mb4;
18 SET FOREIGN_KEY_CHECKS = 0;
19
20 -- ----------------------------
21 -- Table structure for class
22 -- ----------------------------
23 DROP TABLE IF EXISTS `class`;
24 CREATE TABLE `class` (
25 `cid` int(11) NOT NULL AUTO_INCREMENT,
26 `caption` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
27 PRIMARY KEY (`cid`) USING BTREE
28 ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
29
30 -- ----------------------------
31 -- Records of class
32 -- ----------------------------
33 INSERT INTO `class` VALUES (1, '三年二班');
34 INSERT INTO `class` VALUES (2, '三年三班');
35 INSERT INTO `class` VALUES (3, '一年二班');
36 INSERT INTO `class` VALUES (4, '二年九班');
37
38 -- ----------------------------
39 -- Table structure for course
40 -- ----------------------------
41 DROP TABLE IF EXISTS `course`;
42 CREATE TABLE `course` (
43 `cid` int(11) NOT NULL AUTO_INCREMENT,
44 `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
45 `teacher_id` int(11) NOT NULL,
46 PRIMARY KEY (`cid`) USING BTREE,
47 INDEX `fk_course_teacher`(`teacher_id`) USING BTREE,
48 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
49 ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
50
51 -- ----------------------------
52 -- Records of course
53 -- ----------------------------
54 INSERT INTO `course` VALUES (1, '生物', 1);
55 INSERT INTO `course` VALUES (2, '物理', 2);
56 INSERT INTO `course` VALUES (3, '体育', 3);
57 INSERT INTO `course` VALUES (4, '美术', 2);
58
59 -- ----------------------------
60 -- Table structure for score
61 -- ----------------------------
62 DROP TABLE IF EXISTS `score`;
63 CREATE TABLE `score` (
64 `sid` int(11) NOT NULL AUTO_INCREMENT,
65 `student_id` int(11) NOT NULL,
66 `course_id` int(11) NOT NULL,
67 `num` int(11) NOT NULL,
68 PRIMARY KEY (`sid`) USING BTREE,
69 INDEX `fk_score_student`(`student_id`) USING BTREE,
70 INDEX `fk_score_course`(`course_id`) USING BTREE,
71 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
72 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT
73 ) ENGINE = InnoDB AUTO_INCREMENT = 53 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
74
75 -- ----------------------------
76 -- Records of score
77 -- ----------------------------
78 INSERT INTO `score` VALUES (1, 1, 1, 10);
79 INSERT INTO `score` VALUES (2, 1, 2, 9);
80 INSERT INTO `score` VALUES (5, 1, 4, 66);
81 INSERT INTO `score` VALUES (6, 2, 1, 8);
82 INSERT INTO `score` VALUES (8, 2, 3, 68);
83 INSERT INTO `score` VALUES (9, 2, 4, 99);
84 INSERT INTO `score` VALUES (10, 3, 1, 77);
85 INSERT INTO `score` VALUES (11, 3, 2, 66);
86 INSERT INTO `score` VALUES (12, 3, 3, 87);
87 INSERT INTO `score` VALUES (13, 3, 4, 99);
88 INSERT INTO `score` VALUES (14, 4, 1, 79);
89 INSERT INTO `score` VALUES (15, 4, 2, 11);
90 INSERT INTO `score` VALUES (16, 4, 3, 67);
91 INSERT INTO `score` VALUES (17, 4, 4, 100);
92 INSERT INTO `score` VALUES (18, 5, 1, 79);
93 INSERT INTO `score` VALUES (19, 5, 2, 11);
94 INSERT INTO `score` VALUES (20, 5, 3, 67);
95 INSERT INTO `score` VALUES (21, 5, 4, 100);
96 INSERT INTO `score` VALUES (22, 6, 1, 9);
97 INSERT INTO `score` VALUES (23, 6, 2, 100);
98 INSERT INTO `score` VALUES (24, 6, 3, 67);
99 INSERT INTO `score` VALUES (25, 6, 4, 100);
100 INSERT INTO `score` VALUES (26, 7, 1, 9);
101 INSERT INTO `score` VALUES (27, 7, 2, 100);
102 INSERT INTO `score` VALUES (28, 7, 3, 67);
103 INSERT INTO `score` VALUES (29, 7, 4, 88);
104 INSERT INTO `score` VALUES (30, 8, 1, 9);
105 INSERT INTO `score` VALUES (31, 8, 2, 100);
106 INSERT INTO `score` VALUES (32, 8, 3, 67);
107 INSERT INTO `score` VALUES (33, 8, 4, 88);
108 INSERT INTO `score` VALUES (34, 9, 1, 91);
109 INSERT INTO `score` VALUES (35, 9, 2, 88);
110 INSERT INTO `score` VALUES (36, 9, 3, 67);
111 INSERT INTO `score` VALUES (37, 9, 4, 22);
112 INSERT INTO `score` VALUES (38, 10, 1, 90);
113 INSERT INTO `score` VALUES (39, 10, 2, 77);
114 INSERT INTO `score` VALUES (40, 10, 3, 43);
115 INSERT INTO `score` VALUES (41, 10, 4, 87);
116 INSERT INTO `score` VALUES (42, 11, 1, 90);
117 INSERT INTO `score` VALUES (43, 11, 2, 77);
118 INSERT INTO `score` VALUES (44, 11, 3, 43);
119 INSERT INTO `score` VALUES (45, 11, 4, 87);
120 INSERT INTO `score` VALUES (46, 12, 1, 90);
121 INSERT INTO `score` VALUES (47, 12, 2, 77);
122 INSERT INTO `score` VALUES (48, 12, 3, 43);
123 INSERT INTO `score` VALUES (49, 12, 4, 87);
124 INSERT INTO `score` VALUES (52, 13, 3, 87);
125
126 -- ----------------------------
127 -- Table structure for student
128 -- ----------------------------
129 DROP TABLE IF EXISTS `student`;
130 CREATE TABLE `student` (
131 `sid` int(11) NOT NULL AUTO_INCREMENT,
132 `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `class_id` int(11) NOT NULL,
134 `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 PRIMARY KEY (`sid`) USING BTREE,
136 INDEX `fk_class`(`class_id`) USING BTREE,
137 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
138 ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
139
140 -- ----------------------------
141 -- Records of student
142 -- ----------------------------
143 INSERT INTO `student` VALUES (1, '男', 1, '理解');
144 INSERT INTO `student` VALUES (2, '女', 1, '钢蛋');
145 INSERT INTO `student` VALUES (3, '男', 1, '张三');
146 INSERT INTO `student` VALUES (4, '男', 1, '张一');
147 INSERT INTO `student` VALUES (5, '女', 1, '张二');
148 INSERT INTO `student` VALUES (6, '男', 1, '张四');
149 INSERT INTO `student` VALUES (7, '女', 2, '铁锤');
150 INSERT INTO `student` VALUES (8, '男', 2, '李三');
151 INSERT INTO `student` VALUES (9, '男', 2, '李一');
152 INSERT INTO `student` VALUES (10, '女', 2, '李二');
153 INSERT INTO `student` VALUES (11, '男', 2, '李四');
154 INSERT INTO `student` VALUES (12, '女', 3, '如花');
155 INSERT INTO `student` VALUES (13, '男', 3, '刘三');
156 INSERT INTO `student` VALUES (14, '男', 3, '刘一');
157 INSERT INTO `student` VALUES (15, '女', 3, '刘二');
158 INSERT INTO `student` VALUES (16, '男', 3, '刘四');
159
160 -- ----------------------------
161 -- Table structure for teacher
162 -- ----------------------------
163 DROP TABLE IF EXISTS `teacher`;
164 CREATE TABLE `teacher` (
165 `tid` int(11) NOT NULL AUTO_INCREMENT,
166 `tname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 PRIMARY KEY (`tid`) USING BTREE
168 ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
169
170 -- ----------------------------
171 -- Records of teacher
172 -- ----------------------------
173 INSERT INTO `teacher` VALUES (1, '张磊老师');
174 INSERT INTO `teacher` VALUES (2, '李平老师');
175 INSERT INTO `teacher` VALUES (3, '刘海燕老师');
176 INSERT INTO `teacher` VALUES (4, '朱云海老师');
177 INSERT INTO `teacher` VALUES (5, '李杰老师');
178
179 SET FOREIGN_KEY_CHECKS = 1;
