mysql 多表查询案例

先创建一个课程表,一个老师表,一个学生表


create table course(
id int primary key auto_increment,
cou_name varchar(20)
);


insert into course values(null,'java基础');
insert into course values(null,'j2e基础');
insert into course values(null,'hadoop课程');
insert into course values(null,'php开发');


+----+------------+
| id | cou_name   |
+----+------------+
|  1 | java基础   |
|  2 | j2e基础    |
|  3 | hadoop课程 |
|  4 | php开发    |
+----+------------+


再创建一个教师表
create table teacher(
id int primary key auto_increment,
tea_name varchar(20),
cou_id int,
foreign key (cou_id) references course(id)
);


insert into teacher values(null,'刘意',1);
insert into teacher values(null,'朴乾',2);
insert into teacher values(null,'吴超',3);
insert into teacher values(null,'付东',4);


+----+----------+--------+
| id | tea_name | cou_id |
+----+----------+--------+
|  1 | 刘意     |      1 |
|  2 | 朴乾     |      2 |
|  3 | 吴超     |      3 |
|  4 | 付东     |      4 |
+----+----------+--------+


再创建一个学生表


create table student(
id int primary key auto_increment,
stu_name varchar(20)
);


insert into student values(null,'星爷');
insert into student values(null,'马哥');
insert into student values(null,'超哥');
insert into student values(null,'豪哥');


+----+----------+
| id | stu_name |
+----+----------+
|  1 | 星爷     |
|  2 | 马哥     |
|  3 | 超哥     |
|  4 | 豪哥     |
+----+----------+


再创建一个学生 老师关系表


create table tea_stu (
id int primary key auto_increment,
tea_id int,
foreign key (tea_id) references teacher(id),
stu_id int,
foreign key (stu_id) references student(id)
);


星爷师从  刘意 朴乾 付东


insert into tea_stu values(null,1,1);
insert into tea_stu values(null,2,1);
insert into tea_stu values(null,4,1);


马哥师从 付东 吴超


insert into tea_stu values(null,3,2);
insert into tea_stu values(null,4,2);


朴乾 教过 星爷 马哥 豪哥 超哥


insert into tea_stu values(null,2,1);
insert into tea_stu values(null,2,2);
insert into tea_stu values(null,2,3);
insert into tea_stu values(null,2,4);


付东教过 星爷  马哥


insert into tea_stu values(null,4,1);
insert into tea_stu values(null,4,2);




1.查找所有教过星爷的老师
select tea_name  from teacher where teacher.id in (
select tea_id from tea_stu where tea_stu.stu_id=(
select id from student where stu_name='星爷'
)
);


+----------+
| tea_name |
+----------+
| 刘意     |
| 朴乾     |
| 付东     |
+----------+
2查找朴乾教过的所有学生: 学生的名字 当学生的id 在学生老师关系表中老师的名字是朴乾的
select stu_name from student where student.id in(
select stu_id from tea_stu where tea_stu.tea_id=(
select id from teacher where tea_name='朴乾')
);


+----------+
| stu_name |
+----------+
| 星爷     |
| 马哥     |
| 超哥     |
| 豪哥     |
+----------+
3 查找星爷学过的所有学科 查找学科的名字  学科的id在教师表中  教师的Id在学生老师关系表中 学生的名字是星爷
select cou_name from course where course.id  in(
select cou_id from teacher where teacher.id in (
select tea_id from tea_stu where tea_stu.stu_id=(
select id from student where stu_name='星爷')));




+----------+
| cou_name |
+----------+
| java基础 |
| j2e基础  |
| php开发  |
+----------+