7.1 分组子查询

查询 teachers 表中，各个国家所有教师的平均年龄大于所有国家教师的平均年龄的教师信息。

select *
from teachers
where country in (
    select country
    from teachers
    group by country
    having avg(age) > (
        select avg(age)
        from teachers
    )
)

7.2 update子查询

从 teachers 表中查询教师名字为 Eastern Heretic 的信息，并根据教师 id 将教师 Eastern Heretic 创建的课程名称全部改为 PHP，并将学生总数设为 300 人。

update courses
set name = 'PHP', student_count = 300
where teacher_id = (
    select id
    from teachers
    where name = 'Eastern Heretic'
)

7.3 内联视图子查询

将查询的结果集作为一个查询表，继续进行查询操作。使用内联视图，连接 teachers 表和 courses 表，并将查询的课程名称（起别名为 course_name）、学生总数、讲师姓名（起别名为 teacher_name）信息作为查询表，并从该查询表中选出学生总数最多的信息。

select *
from (
    select c.name as course_name, student_count, t.name as teacher_name
    from teachers t join courses c on t.id = c.teacher_id
) s
where student_count = (
    select max(student_count)
    from courses
)

7.4 多行子查询

7.4.1 IN、NOT IN

联合教师表和课程表，查询课程表 courses 中所有年龄大于 20 岁的教师所教的所有课程的课程名。

select c.name
from teachers t join courses c on t.id = c.teacher_id
where t.age in (
    select age
    from teachers
    where age > 20
)

7.4.2 ANY

操作符 ANY 属于逻辑运算符的一种，与 IN 运算符不同，ANY 必须和其它的比较运算符共同使用，其表示查询结果中的任意一个。在子查询中使用 ANY ，表示与子查询返回的任何值比较为真，则返回真。
从 courses 表和 teachers 表中查询课程创建时间晚于 ‘Southern Emperor’ 教师任意一门课程创建时间的课程名称。

select c.name
from courses c
    join teachers t on t.id = c.teacher_id
where c.created_at > any(
        select created_at
        from courses c
            join teachers t on c.teacher_id = t.id
        where t.name = 'Southern Emperor'
    )
    and c.teacher_id <> (
        select id
        from teachers
        where name = 'Southern Emperor'
    )

7.4.3 ALL

子查询中使用 ALL ，表示与子查询返回的所有值比较为真，则返回真。
查询教师表 teachers 和课程表 courses，查询最年长的老师所开课程的学生数，最后返回学生数均超过这些课程的课程信息。

select *
from courses
where student_count  > all(
    select student_count
    from courses c
        join teachers t on t.id = c.teacher_id
    where age = (
        select max(age)
        from teachers
    )
)

7.5 多列子查询

当是单行多列的子查询时，主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝，>，<，>=，<=， <> 等）来进行比较；
当是多行多列子查询时，主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ANY，ALL 等）来进行比较。

从课程表 courses 中查询每个教师授课学生人数最高的课程名称 name 和上课人数 student_count 。

select name, student_count
from courses
where (teacher_id, student_count) in (
    select teacher_id, max(student_count)
    from courses
    group by teacher_id
)
-- 根据教师分组，每个教师，得到最高的课程人数，然后再查课程名称

MySQL

7. 分组、子查询