首先上一段测试数据

create table teacher (
id int(11) not null primary key auto_increment,
name varchar(20) not null unique
);
create table student (
id int(11) not null primary key auto_increment,
name varchar(20) NOT NULL unique,
city varchar(40) NOT NULL,
age int
) ;
create table course(
id int(11) not null primary key auto_increment,
name varchar(20) not null unique,
teacher_id int(11) not null,
foreign key(teacher_id) references teacher (id)
);

create table studentcourse (
student_id int NOT NULL,
course_id int NOT NULL,
score double NOT NULL,
foreign key (student_id) references student (id),
foreign key (course_id) references course (id)
);

insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');

insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','广州',20);

insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);

insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);

比较经典的mysql学习的子查询案例：

-- 需求1：查询获得最高分的学生信息。

-- 最高分数查询的是中间表，获取最高分数的学生的id
-- 然后根据id来获取这个学生的信息

select MAX(score) from studentcourse;

select student_id from studentcourse where score=95;

select * from student where id =4;

-- 子查询语句

select * from student where id=(select student_id from studentcourse where score=(select MAX(score) from studentcourse));

-- 查询编号是2的课程比编号是1的课程最高成绩高的学生信息。

-- 获取编号1的课程的最高成绩
select MAX(score) from studentcourse where course_id=1;
-- 获取编号2的比编号1课程还要高的的学生的id
select student_id from studentcourse where course_id=2 and score >80;
-- 然后根据id获取到这个学生的信息
select * from student where id in(1,4,6,9);

-- 拼接成子查询语句
select * from student where id in(select student_id from studentcourse where course_id=2 and score >(select MAX(score) from studentcourse where course_id=1)) ;

-- 查询编号是2的课程比编号是1的课程最高成绩高的学生姓名和成绩（借助于临时表）。

-- 编号为1的的最高的成绩
select MAX(score) from studentcourse where course_id=1;

-- 编号为2比编号为1的最高还要高的学生的id 和成绩
select student_id,score from studentcourse where course_id=2 and score >80;
-- 将以上查询出来的表格作为一个中间表格，然后在利用内连接的方式将这个中间表和学生表合并。
select s.name,ss.score from (select student_id,score from studentcourse
where course_id=2 and score >(select MAX(score) from studentcourse
where course_id=1))as ss INNER JOIN student as s on s.id=ss.student_id;

-- 1、查询每个平均成绩大于70分的同学的学号和平均成绩。

-- 不同的学生有不同的平均成绩，可以根据学生的学号来分组，然后获取到平均数
select student_id,AVG(score) from studentcourse GROUP BY student_id having avg(score)>70;

-- 2、查询每个同学的学号、姓名、选课数、总成绩。
-- 学生的学号，和姓名都是student这个表中的
select id,name from student;
-- 学生的选课数和总的成绩都是中间表的,所以需要分组查询出来这个选课数和总的成绩，这里用到了分组和函数
select student_id, count(course_id),SUM(score) from studentcourse GROUP BY student_id;
-- 然后使用内连接的方式将，这个虚拟的中间表和这个student进行内连接，内连接过程中使用到给表取名的动作
-- 这样做目的是简化书写
SELECT s.id,s.name,ss.count,ss.sum from (select student_id, count(course_id) as count,SUM(score) as sum from studentcourse GROUP BY student_id)
as ss INNER JOIN student s on s.id=ss.student_id;

-- 3、查询学过赵云老师所教课的同学的学号、姓名。

-- 根据赵老师的名称获取到赵老师的id

select id from teacher where name="赵云";

-- 然后在课程表中根据id获取赵老师所教课程的id

select id from course where teacher_id=3; -- 课程id为6

-- 然后根据所有的课程id获取到所有学生的id

select student_id from studentcourse where course_id=6;

-- 然后根据学生的id获取到这个学生的信息

select id,name from student where id in(3,4,5);

-- 拼接成一个子查询

select id,name from student where id in(select student_id from studentcourse

where course_id=(select id from course
where teacher_id=(select id from teacher where name="赵云")));

总结：子查询一般的做法就是将一个比较长的需求进行拆分，拆分成一段段的小的需求，然后进行拼接即可！！