先创建一个课程表,一个老师表,一个学生表
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开发 |
+----------+
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开发 |
+----------+