1 use around; 2
3 CREATE TABLE class ( cid TINYINT PRIMARY KEY auto_increment, caption VARCHAR ( 20 ) ); 4 CREATE TABLE student ( 5 sid TINYINT PRIMARY KEY auto_increment, 6 sname VARCHAR ( 20 ), 7 gender VARCHAR ( 10 ), 8 class_id TINYINT, 9 CONSTRAINT c_id FOREIGN KEY ( class_id ) REFERENCES class ( cid ) 10 ); 11 CREATE TABLE teacher ( tid TINYINT PRIMARY KEY auto_increment, tname VARCHAR ( 20 ) ); 12
13 CREATE TABLE course ( 14 cid TINYINT PRIMARY KEY auto_increment, 15 cname VARCHAR ( 20 ), 16 teacher_id TINYINT, 17 CONSTRAINT t_id FOREIGN KEY ( teacher_id ) REFERENCES teacher ( tid ) 18 ); 19
20 CREATE TABLE score ( 21 sid TINYINT PRIMARY KEY auto_increment, 22 student_id TINYINT, 23 course_id TINYINT, 24 number INT, 25 CONSTRAINT stu_id FOREIGN KEY ( student_id ) REFERENCES student (sid), 26 CONSTRAINT tea_id FOREIGN KEY ( course_id ) REFERENCES course ( cid ) 27 ); 28
29
30 INSERT INTO class 31 VALUES
32 ( ‘1‘, ‘三年二班‘ ), 33 ( ‘2‘, ‘三年三班‘ ), 34 ( ‘3‘, ‘一年二班‘ ), 35 ( ‘4‘, ‘二年九班‘ ); 36
37 INSERT INTO student ( sid, gender, class_id, sname ) 38 VALUES
39 ( ‘1‘, ‘男‘, ‘1‘, ‘理解‘ ), 40 ( ‘2‘, ‘女‘, ‘1‘, ‘钢蛋‘ ), 41 ( ‘3‘, ‘男‘, ‘1‘, ‘张三‘ ), 42 ( ‘4‘, ‘男‘, ‘1‘, ‘张一‘ ), 43 ( ‘5‘, ‘女‘, ‘1‘, ‘张二‘ ), 44 ( ‘6‘, ‘男‘, ‘1‘, ‘张四‘ ), 45 ( ‘7‘, ‘女‘, ‘2‘, ‘铁锤‘ ), 46 ( ‘8‘, ‘男‘, ‘2‘, ‘李三‘ ), 47 ( ‘9‘, ‘男‘, ‘2‘, ‘李一‘ ), 48 ( ‘10‘, ‘女‘, ‘2‘, ‘李二‘ ), 49 ( ‘11‘, ‘男‘, ‘2‘, ‘李四‘ ), 50 ( ‘12‘, ‘女‘, ‘3‘, ‘如花‘ ), 51 ( ‘13‘, ‘男‘, ‘3‘, ‘刘三‘ ), 52 ( ‘14‘, ‘男‘, ‘3‘, ‘刘一‘ ), 53 ( ‘15‘, ‘女‘, ‘3‘, ‘刘二‘ ), 54 ( ‘16‘, ‘男‘, ‘3‘, ‘刘四‘ ); 55
56 INSERT INTO teacher 57 VALUES
58 ( ‘1‘, ‘张磊老师‘ ), 59 ( ‘2‘, ‘李平老师‘ ), 60 ( ‘3‘, ‘刘海燕老师‘ ), 61 ( ‘4‘, ‘朱云海老师‘ ), 62 ( ‘5‘, ‘李杰老师‘ ); 63
64 INSERT INTO course 65 VALUES
66 ( ‘1‘, ‘生物‘, ‘1‘ ), 67 ( ‘2‘, ‘物理‘, ‘2‘ ), 68 ( ‘3‘, ‘体育‘, ‘3‘ ), 69 ( ‘4‘, ‘美术‘, ‘2‘ ); 70
71 INSERT INTO score 72 VALUES
73 ( ‘1‘, ‘1‘, ‘1‘, ‘10‘ ), 74 ( ‘2‘, ‘1‘, ‘2‘, ‘9‘ ), 75 ( ‘5‘, ‘1‘, ‘4‘, ‘66‘ ), 76 ( ‘6‘, ‘2‘, ‘1‘, ‘8‘ ), 77 ( ‘8‘, ‘2‘, ‘3‘, ‘68‘ ), 78 ( ‘9‘, ‘2‘, ‘4‘, ‘99‘ ), 79 ( ‘10‘, ‘3‘, ‘1‘, ‘77‘ ), 80 ( ‘11‘, ‘3‘, ‘2‘, ‘66‘ ), 81 ( ‘12‘, ‘3‘, ‘3‘, ‘87‘ ), 82 ( ‘13‘, ‘3‘, ‘4‘, ‘99‘ ), 83 ( ‘14‘, ‘4‘, ‘1‘, ‘79‘ ), 84 ( ‘15‘, ‘4‘, ‘2‘, ‘11‘ ), 85 ( ‘16‘, ‘4‘, ‘3‘, ‘67‘ ), 86 ( ‘17‘, ‘4‘, ‘4‘, ‘100‘ ), 87 ( ‘18‘, ‘5‘, ‘1‘, ‘79‘ ), 88 ( ‘19‘, ‘5‘, ‘2‘, ‘11‘ ), 89 ( ‘20‘, ‘5‘, ‘3‘, ‘67‘ ), 90 ( ‘21‘, ‘5‘, ‘4‘, ‘100‘ ), 91 ( ‘22‘, ‘6‘, ‘1‘, ‘9‘ ), 92 ( ‘23‘, ‘6‘, ‘2‘, ‘100‘ ), 93 ( ‘24‘, ‘6‘, ‘3‘, ‘67‘ ), 94 ( ‘25‘, ‘6‘, ‘4‘, ‘100‘ ), 95 ( ‘26‘, ‘7‘, ‘1‘, ‘9‘ ), 96 ( ‘27‘, ‘7‘, ‘2‘, ‘100‘ ), 97 ( ‘28‘, ‘7‘, ‘3‘, ‘67‘ ), 98 ( ‘29‘, ‘7‘, ‘4‘, ‘88‘ ), 99 ( ‘30‘, ‘8‘, ‘1‘, ‘9‘ ), 100 ( ‘31‘, ‘8‘, ‘2‘, ‘100‘ ), 101 ( ‘32‘, ‘8‘, ‘3‘, ‘67‘ ), 102 ( ‘33‘, ‘8‘, ‘4‘, ‘88‘ ), 103 ( ‘34‘, ‘9‘, ‘1‘, ‘91‘ ), 104 ( ‘35‘, ‘9‘, ‘2‘, ‘88‘ ), 105 ( ‘36‘, ‘9‘, ‘3‘, ‘67‘ ), 106 ( ‘37‘, ‘9‘, ‘4‘, ‘22‘ ), 107 ( ‘38‘, ‘10‘, ‘1‘, ‘90‘ ), 108 ( ‘39‘, ‘10‘, ‘2‘, ‘77‘ ), 109 ( ‘40‘, ‘10‘, ‘3‘, ‘43‘ ), 110 ( ‘41‘, ‘10‘, ‘4‘, ‘87‘ ), 111 ( ‘42‘, ‘11‘, ‘1‘, ‘90‘ ), 112 ( ‘43‘, ‘11‘, ‘2‘, ‘77‘ ), 113 ( ‘44‘, ‘11‘, ‘3‘, ‘43‘ ), 114 ( ‘45‘, ‘11‘, ‘4‘, ‘87‘ ), 115 ( ‘46‘, ‘12‘, ‘1‘, ‘90‘ ), 116 ( ‘47‘, ‘12‘, ‘2‘, ‘77‘ ), 117 ( ‘48‘, ‘12‘, ‘3‘, ‘43‘ ), 118 ( ‘49‘, ‘12‘, ‘4‘, ‘87‘ ), 119 ( ‘52‘, ‘13‘, ‘3‘, ‘87‘ ); 120
121
