mysql 多表查询案例_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开发  | +----------+