· 7 years ago · Dec 14, 2018, 09:14 AM
1CREATE DATABASE IF NOT EXISTS `school` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
2use school;
3CREATE TABLE IF NOT EXISTS `student`
4(
5 `id` INT AUTO_INCREMENT,
6 `name` VARCHAR(20) NOT NULL DEFAULT '',
7 `birth` VARCHAR(20) NOT NULL DEFAULT '',
8 `sex` VARCHAR(10) NOT NULL DEFAULT '',
9 PRIMARY KEY (`id`)
10) ENGINE = innodb
11 CHARSET = utf8 COMMENT = 'å¦ç”Ÿè¡¨';
12
13CREATE TABLE IF NOT EXISTS `course`
14(
15 `id` INT AUTO_INCREMENT,
16 `name` VARCHAR(20) NOT NULL DEFAULT '',
17 `teacher_id` INT NOT NULL,
18 PRIMARY KEY (`id`)
19) ENGINE = innodb
20 CHARSET = utf8 COMMENT = '课程表';
21
22CREATE TABLE IF NOT EXISTS `teacher`
23(
24 `id` INT AUTO_INCREMENT,
25 `name` VARCHAR(20) NOT NULL DEFAULT '',
26 PRIMARY KEY (`id`)
27) ENGINE = innodb
28 CHARSET = utf8 COMMENT = '教师表';
29
30CREATE TABLE IF NOT EXISTS `grade`
31(
32 `id` INT AUTO_INCREMENT,
33 `student_id` INT,
34 `course_id` INT,
35 `score` INT(3),
36 PRIMARY KEY (`id`)
37) ENGINE = innodb
38 CHARSET = utf8 COMMENT = '分数表';
39
40insert into student
41values (NULL, 'å¼ ä¸‰', '1990-01-01', 'm'),
42 (NULL, 'æ¬ ç‚¹', '1990-12-21', 'f'),
43 (NULL, '顺丰', '1990-05-20', 'm'),
44 (NULL, 'æŽäº‘', '1990-08-06', 'f'),
45 (NULL, '周梅', '1991-12-01', 'f'),
46 (NULL, 'å´å…°', '1992-03-01', 'f'),
47 (NULL, '郑足', '1989-07-01', 'm'),
48 (NULL, '王èŠ', '1990-01-20', 'f');
49
50insert into course
51values (NULL, 'è¯æ–‡', 2),
52 (NULL, 'æ•°å¦', 1),
53 (NULL, '英è¯', 3);
54
55
56insert into teacher
57values (NULL, 'å¼ æ¡‘'),
58 (NULL, 'æŽæ€'),
59 (NULL, '王æ¦');
60
61
62insert into grade
63values (NULL, 1, 1, 80),
64 (NULL, 2, 1, 70),
65 (NULL, 2, 2, 60),
66 (NULL, 2, 3, 80),
67 (NULL, 3, 1, 80),
68 (NULL, 3, 2, 80),
69 (NULL, 3, 3, 80),
70 (NULL, 4, 1, 50),
71 (NULL, 4, 2, 30),
72 (NULL, 4, 3, 20),
73 (NULL, 5, 1, 76),
74 (NULL, 5, 2, 87),
75 (NULL, 6, 1, 31),
76 (NULL, 6, 3, 34),
77 (NULL, 7, 2, 89),
78 (NULL, 7, 3, 98);