mysql考试信息表_Mysql学习---SQL测试题之表结构

创建表结果和数据准备[直接执行即可]

1 /*

2 Navicat MySQL Data Transfer3

4 Source Server : ftl10125 Source Server Version : 506176 Source Host : localhost:33067 Source Database : test_python8

9 Target Server Type : MYSQL10 Target Server Version : 5061711 File Encoding : 6500112

13 Date: 2017-12-30 13:12:5714 */

15

16 SET FOREIGN_KEY_CHECKS=0;17

18 ------------------------------

19 --Table structure for class

20 ------------------------------

21 DROP TABLE IF EXISTS`class`;22 CREATE TABLE`class` (23 `cid` int(11) NOT NULLAUTO_INCREMENT,24 `caption` varchar(32) NOT NULL,25 PRIMARY KEY(`cid`)26 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;27

28 ------------------------------

29 --Records of class

30 ------------------------------

31 INSERT INTO `class` VALUES ('1', '三年二班');32 INSERT INTO `class` VALUES ('2', '三年三班');33 INSERT INTO `class` VALUES ('3', '一年二班');34 INSERT INTO `class` VALUES ('4', '二年九班');35

36 ------------------------------

37 --Table structure for course

38 ------------------------------

39 DROP TABLE IF EXISTS`course`;40 CREATE TABLE`course` (41 `cid` int(11) NOT NULLAUTO_INCREMENT,42 `cname` varchar(32) NOT NULL,43 `teacher_id` int(11) NOT NULL,44 PRIMARY KEY(`cid`),45 KEY`fk_course_teacher` (`teacher_id`),46 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES`teacher` (`tid`)47 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;48

49 ------------------------------

50 --Records of course

51 ------------------------------

52 INSERT INTO `course` VALUES ('1', '生物', '1');53 INSERT INTO `course` VALUES ('2', '物理', '2');54 INSERT INTO `course` VALUES ('3', '体育', '3');55 INSERT INTO `course` VALUES ('4', '美术', '2');56

57 ------------------------------

58 --Table structure for score

59 ------------------------------

60 DROP TABLE IF EXISTS`score`;61 CREATE TABLE`score` (62 `sid` int(11) NOT NULLAUTO_INCREMENT,63 `student_id` int(11) NOT NULL,64 `course_id` int(11) NOT NULL,65 `num` int(11) NOT NULL,66 PRIMARY KEY(`sid`),67 KEY`fk_score_student` (`student_id`),68 KEY`fk_score_course` (`course_id`),69 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES`course` (`cid`),70 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES`student` (`sid`)71 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;72

73 ------------------------------

74 --Records of score

75 ------------------------------

76 INSERT INTO `score` VALUES ('1', '1', '1', '10');77 INSERT INTO `score` VALUES ('2', '1', '2', '9');78 INSERT INTO `score` VALUES ('5', '1', '4', '66');79 INSERT INTO `score` VALUES ('6', '2', '1', '8');80 INSERT INTO `score` VALUES ('8', '2', '3', '68')