mysql 三联表_mysql lift join 简单的三联表查询

非常感谢老师！

看图吧

班级表 创建及添加数据 在此省略...

创建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;

精简查询结果：