非常感谢先生!
看图吧
班级表 建立及添加数据 在此省略...
建立student表
create table student(
sid int not null auto_increment primary key,
sname varchar(30),
gender enum('男','女')
)engine = innodb default charset = utf8
添加外键:
alter table student add constraint fk_c_s foreign key student(class_id) references class(cid);
student 添加数据
insert into student(sname,gender,class_id) values ('钢蛋','女',1),('铁锤','女',1),('山炮','男',2);
建立teacher表
create table teacher(
tid int not null auto_increment primary key,
tname varchar(30)
)engine = innodb default charset = utf8
添加先生数据:
insert into teacher(tname) values ('波多'),('苍空'),('饭岛');
建立course课程表
create table course(
cid int not null auto_increment primary key,
cname varchar(30),
tearch_id int
)engine = innodb default charset = utf8
添加课程表外键:
alter table course add constraint fk_t_c foreign key course(tearch_id) references teacher(tid);
添加课程表内容:
insert into course(cname,tearch_id) values ('生物',1),('体育',1),('物理',2);
建立score成就表:
create table score(
sid int not null auto_increment primary key,
student_id int,
corse_id int,
number int
)engine = innodb default charset = utf8
添加score外键:
alter table score add constraint fk_s_s foreign key score(student_id) references student(sid);
alter table score add constraint fkk_c_s foreign key score(corse_id) references course(cid);
添加score 数据
insert into score(student_id,corse_id,number) values (1,1,60),(1,2,59),(2,2,100);
数据成就表:
select * from (score left join student on score.student_id=student.sid) left join course on score.corse_id=course.cid;
精简查询:
select score.sid as "序号" , student.sname as "姓名",course.cname as "课程",score.number as "成就" from (score left join student on score.student_id=student.sid) left join course on score.corse_id=course.cid;
精简查询效果:
原文链接:https://www.cnblogs.com/bdua/p/12382036.html
本站声明:网站内容来源于网络,若有侵权,请联系我们,我们将及时处理。
mysql 一对多数据表 join 查询后结果集的处理