mysql lift join 简单的三联表查询 -清零世界

bubuko 2022/1/25 20:09:18 mysql 字数 2999 阅读 996 0 来源 http://www.bubuko.com/infolist-5-1.html

非常感谢老师！

看图吧

技术分享图片




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


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

 精简查询结果：

mysql lift join 简单的三联表查询

原文：https://www.cnblogs.com/bdua/p/12382036.html

如果您也喜欢它,动动您的小指点个赞吧

mysql lift join 简单的三联表查询

分类汇总

您的鼓励是对我最大的支持