作业45

mysql> create table class (id int primary key auto_increment,caption char(10));

mysql> insert into class(caption) values ("三年级二班"),("一年级三班"),("三年级一班");

mysql> select * from class;
+----+------------+
| id | caption |
+----+------------+
| 1 | 三年级二班 |
| 2 | 一年级三班 |
| 3 | 三年级一班 |
+----+------------+
3 rows in set (0.04 sec)

mysql> create table student (sid int primary key auto_increment,sname char(10),gender enum("男","女"),class_id int,
-> foreign key (class_id) references class(id));

mysql> desc student;
+----------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | char(10) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-----------------+------+-----+---------+----------------+

insert into student(sname,gender,class_id) values ("钢蛋","女",1),("铁锤","女",1),("山炮","男",2);

mysql> select * from student;
+-----+-------+--------+----------+
| sid | sname | gender | class_id |
+-----+-------+--------+----------+
| 1 | 钢蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+-------+--------+----------+

mysql> create table teacher (tid int primary key auto_increment,tname char(10));

mysql> desc teacher;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| tname | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+

insert into teacher(tname) values ("波多"),("苍井"),("饭岛");

mysql> select * from teacher ;
+-----+-------+
| tid | tname |
+-----+-------+
| 1 | 波多 |
| 2 | 苍井 |
| 3 | 饭岛 |
+-----+-------+

mysql> create table course (cid int primary key auto_increment,cname char(10),teacher_id int,
-> foreign key (teacher_id) references teacher(tid));

insert into course(cname,teacher_id) values("生物",1),("体育",1),("物理",2);

mysql> select * from course;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 1 | 生物 | 1 |
| 2 | 体育 | 1 |
| 3 | 物理 | 2 |
+-----+-------+------------+

mysql> create table score (sid int primary key auto_increment,student_id int,corse_id int,number int,
-> foreign key (student_id) references student(sid),
-> foreign key (corse_id) references course(cid));

 insert into score(student_id,corse_id,number) values (1,1,60),(1,2,59),(2,2,100);

mysql> select * from score;
+-----+------------+----------+--------+
| sid | student_id | corse_id | number |
+-----+------------+----------+--------+
| 1 | 1 | 1 | 60 |
| 2 | 1 | 2 | 59 |
| 3 | 2 | 2 | 100 |
+-----+------------+----------+--------+