MySQL第四章，SQL高级二子查询

子查询的定义

子查询（Subquery）是嵌套在其他SQL语句中的查询，也称为内查询（Inner Query）或嵌套查询（Nested Query）。子查询可以放在SELECT、INSERT、UPDATE、DELETE等语句中，并且支持多层嵌套，即子查询中还可以包含其他子查询。

子查询的作用

子查询的主要作用包括：

1. 结构化查询：子查询允许将查询分解为更小的部分，从而使查询更加结构化和易于理解。
2. 替代复杂连接：在某些情况下，子查询可以替代复杂的连接操作，使查询更加简洁。
3. 提供灵活的查询条件：子查询可以用于提供动态的查询条件，使查询更加灵活和强大。

子查询的类型

根据子查询返回的结果类型，子查询可以分为以下几类：

1. 标量子查询：返回单个值的子查询，通常用于比较操作。例如，(SELECT MAX(salary) FROM employees) 返回员工表中的最高工资。
2. 列级子查询：返回一列值的子查询，通常用于IN或NOT IN等操作符中。例如，(SELECT department_id FROM employees WHERE salary > 5000) 返回工资大于5000的员工的部门ID列表。
3. 行级子查询：返回一行多列值的子查询，通常用于与主查询中的行进行比较。例如，(SELECT department_id, MAX(salary) FROM employees GROUP BY department_id) 返回每个部门的最高工资及其对应的部门ID。
4. 表级子查询：返回多行多列值的子查询，通常用于FROM子句中将子查询结果作为临时表使用。例如，SELECT * FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS avg_salaries 将每个部门的平均工资作为临时表进行查询。

注意事项

1. 性能问题：子查询可能会影响查询性能，特别是在处理大数据集时。因此，在使用子查询时需要注意性能优化。
2. 可读性：虽然子查询可以使查询更加灵活和强大，但过多的嵌套和复杂的子查询可能会降低代码的可读性。因此，在使用子查询时需要权衡可读性和性能之间的关系。

综上所述，子查询是MySQL中一种强大的查询工具，可以帮助用户实现更加复杂和灵活的查询需求。在使用子查询时需要注意性能优化和可读性等问题。

IN 子查询

IN 子查询用于测试一个值是否存在于子查询的结果集中。如果子查询返回的结果集中包含该值，则条件为真。

这里是一些语法:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT statement);

示例：
假设有两个表 students 和 courses，students 表包含学生的信息，courses 表包含学生选修的课程信息。我们想要找出选修了课程ID为101或102的所有学生。

SELECT student_name
FROM students
WHERE student_id IN (SELECT student_id FROM courses WHERE course_id IN (101, 102));

在这个例子中，子查询 (SELECT student_id FROM courses WHERE course_id IN (101, 102)) 返回选修了课程ID为101或102的所有学生的ID，然后外层查询根据这些ID找出对应的学生姓名。

EXISTS 子查询

EXISTS 子查询用于测试子查询是否返回任何行。如果子查询返回至少一行，则条件为真。

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT statement);

示例：
假设我们想要找出至少选修了一门课程的所有学生。

SELECT student_name
FROM students s
WHERE EXISTS (SELECT 1 FROM courses c WHERE c.student_id = s.student_id);

在这个例子中，子查询 (SELECT 1 FROM courses c WHERE c.student_id = s.student_id) 检查是否存在与 students 表中的 student_id 相匹配的 courses 表中的行。如果存在，则 EXISTS 条件为真，外层查询返回该学生的姓名。

综合查询

综合查询通常涉及多个表之间的连接（JOIN）、子查询、聚合函数（如 SUM、COUNT、AVG 等）以及分组（GROUP BY）和排序（ORDER BY）。

示例：
假设我们想要找出每个学生选修的课程数量，并按课程数量降序排列。

SELECT s.student_name, COUNT(c.course_id) AS course_count
FROM students s
LEFT JOIN courses c ON s.student_id = c.student_id
GROUP BY s.student_id, s.student_name
ORDER BY course_count DESC;

在这个例子中，我们使用了 LEFT JOIN 来连接 students 和 courses 表，GROUP BY 来按学生分组，COUNT 来计算每个学生选修的课程数量，ORDER BY 来按课程数量降序排列结果。

子查询练习题

1. 找出选修了最多课程的学生：
   • 假设 courses 表包含 student_id 和 course_id 列。
   • 编写一个查询来找出选修了最多课程的学生ID和姓名（假设学生姓名在 students 表中）。
2. 找出没有选修任何课程的学生：
   • 使用 EXISTS 子查询来找出没有选修任何课程的学生姓名。
3. 计算每个课程的平均成绩，并找出平均成绩高于80分的课程：
   • 假设 grades 表包含 student_id、course_id 和 grade 列。
   • 编写一个查询来计算每个课程的平均成绩，并只返回平均成绩高于80分的课程ID和平均成绩。
4. 找出至少有两个学生选修的课程：
   • 使用 GROUP BY 和 HAVING 子句来找出至少有两个学生选修的课程ID。
5. 找出每个学生选修的课程中成绩最高的那门课程的成绩：
   • 使用子查询和 MAX 函数来找出每个学生选修的课程中成绩最高的那门课程的成绩。

这些练习题涵盖了 IN 子查询、EXISTS 子查询、连接、聚合函数、分组和排序等SQL综合查询的各个方面。希望这些示例和练习题能帮助您更好地理解和应用SQL子查询和综合查询。