mysql的四张表（学生表，教师表，课程表，分数表）

 

CREATE table student(
id INT(6) PRIMARY KEY,
s_num VARCHAR(20),
s_name VARCHAR(20),
s_sex VARCHAR(5),
s_birth datetime
)

create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);


create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar(10),
cno varchar(10),
score DOUBLE,
constraint pk_sc primary key (sno,cno)
);

insert into student values ('1','120311120','张三','男','1994-10-21');  
insert into student values ('2','120311121','赵玲','女','1995-01-12'); 
insert into student values ('3','120311123','冯凯','男','1996-02-16'); 
insert into student values ('4','120311124','朱茵','女','1997-03-24'); 
insert into student values ('5','120311125','徐丽','女','1998-04-23'); 
insert into student values ('6','120311126','姜山','男','1994-05-28'); 
insert into student values ('7','120311127','孔雀','女','1994-04-27'); 
insert into student values ('8','120311128','雷霆','男','1995-10-21'); 
insert into student values ('9','120311129','胡锦','女','1995-10-21'); 
insert into student values ('10','120311130','江水凌','女','1995-10-21'); 
insert into student values ('11','120311140','方海','男','1992-10-21'); 
insert into student values ('12','120311150','钱凤刚','男','1994-10-21');  
insert into student values ('13','120311160','孙琳','男','1994-10-21'); 
insert into student values ('14','120311170','蒋经国','女','1995-07-21'); 
insert into student values ('15','120311180','胡海峰','男','1995-11-21'); 
insert into student values ('16','120311190','贾胜男','女','1995-12-26'); 
insert into student values ('17','120311131','袁晓峰','女','1996-05-21'); 
insert into student values ('18','120311132','宫国','男','1996-10-17'); 
insert into student values ('19','120311133','郭靖','男','1996-10-09'); 
insert into student values ('20','120311134','黄蓉','女','1998-10-08'); 
insert into student values ('21','120311135','张婷','女','1992-10-23'); 
insert into student values ('22','120311136','袁文博','男','1994-08-19'); 

insert into student values ('1','120311120','张三','男','1994-10-21');  
insert into student values ('2','120311121','赵玲','女','1995-01-12'); 
insert into student values ('3','120311123','冯凯','男','1996-02-16'); 
insert into student values ('4','120311124','朱茵','女','1997-03-24'); 
insert into student values ('5','120311125','徐丽','女','1998-04-23'); 
insert into student values ('6','120311126','姜山','男','1994-05-28'); 
insert into student values ('7','120311127','孔雀','女','1994-04-27'); 
insert into student values ('8','120311128','雷霆','男','1995-10-21'); 
insert into student values ('9','120311129','胡锦','女','1995-10-21'); 
insert into student values ('10','120311130','江水凌','女','1995-10-21'); 
insert into student values ('11','120311140','方海','男','1992-10-21'); 
insert into student values ('12','120311150','钱凤刚','男','1994-10-21');  
insert into student values ('13','120311160','孙琳','男','1994-10-21'); 
insert into student values ('14','120311170','蒋经国','女','1995-07-21'); 
insert into student values ('15','120311180','胡海峰','男','1995-11-21'); 
insert into student values ('16','120311190','贾胜男','女','1995-12-26'); 
insert into student values ('17','120311131','袁晓峰','女','1996-05-21'); 
insert into student values ('18','120311132','宫国','男','1996-10-17'); 
insert into student values ('19','120311133','郭靖','男','1996-10-09'); 
insert into student values ('20','120311134','黄蓉','女','1998-10-08'); 
insert into student values ('21','120311135','张婷','女','1992-10-23'); 
insert into student values ('22','120311136','袁文博','男','1994-08-19'); 


insert into teacher values ('001','赵老师'); 
insert into teacher values ('002','何老师'); 
insert into teacher values ('003','文老师'); 
insert into teacher values ('004','蒋老师'); 
insert into teacher values ('005','黄老师'); 
insert into teacher values ('006','李老师'); 

insert into course values('100','语文','001'); 
insert into course values('200','数学','002'); 
insert into course values('300','英语','003'); 
insert into course values('400','物理','004'); 
insert into course values('500','生物','005'); 
insert into course values('600','化学','006'); 




insert into sc values('120311126','67','100'); 
insert into sc values('120311126','58','200'); 
insert into sc values('120311126','89','300'); 
insert into sc values('120311126','90','400'); 
insert into sc values('120311126','98','500'); 
insert into sc values('120311126','68','600'); 

insert into sc values('120311127','100','100'); 
insert into sc values('120311127','82','200'); 
insert into sc values('120311127','45.5','300'); 
insert into sc values('120311127','86','400'); 
insert into sc values('120311127','81','500'); 
insert into sc values('120311127','87','600'); 

insert into sc values('120311128','67','100'); 
insert into sc values('120311128','100','200'); 
insert into sc values('120311128','89','300'); 
insert into sc values('120311128','95','400'); 
insert into sc values('120311128','98','500'); 
insert into sc values('120311128','78','600'); 

insert into sc values('120311129','89','100'); 
insert into sc values('120311129','82','200'); 
insert into sc values('120311129','89.5','300'); 
insert into sc values('120311129','94','400'); 
insert into sc values('120311129','81','500'); 
insert into sc values('120311129','87','600'); 

insert into sc values('120311130','67','100'); 
insert into sc values('120311130','78','200'); 
insert into sc values('120311130','89','300'); 
insert into sc values('120311130','92','400'); 
insert into sc values('120311130','79','500'); 
insert into sc values('120311130','64','600'); 

insert into sc values('120311131','89.4','100'); 
insert into sc values('120311131','92','200'); 
insert into sc values('120311131','79.5','300'); 
insert into sc values('120311131','96','400'); 
insert into sc values('120311131','41','500'); 
insert into sc values('120311131','97','600'); 

insert into sc values('120311150','34','100'); 
insert into sc values('120311150','90','200'); 
insert into sc values('120311150','76','300'); 
insert into sc values('120311150','90','400'); 
insert into sc values('120311150','91','500'); 
insert into sc values('120311150','65','600'); 

insert into sc values('120311140','69','100'); 
insert into sc values('120311140','78','200'); 
insert into sc values('120311140','78.5','300'); 
insert into sc values('120311140','90','400'); 
insert into sc values('120311140','87','500'); 
insert into sc values('120311140','83','600');