MySQL 联合查询实战
在数据库设计的范式下，数据往往分散在多个表中。当我们需要同时获取这些表的信息时，就必须使用联合查询（Join）将多个表连接起来。
笛卡尔积的陷阱
如果直接对两个表进行查询而不加限制，会产生笛卡尔积。例如：
SELECT * FROM student, class;
这会返回两个表行数的乘积，数据量会非常庞大且包含大量无效结果。正确的做法是加上 WHERE 条件，确保关联字段相等：
SELECT * FROM student, class WHERE student.class_id = class.id;
此时查询结果才合理。为了避免歧义，建议显式指定列名并使用表别名，特别是当不同表存在同名列时：
SELECT student.id, student.name, sex, student.class_id, class.id, class.name
FROM student, class
WHERE student.class_id = class.id;
联合查询基本步骤：
- 确定查询表（隐含笛卡尔积）
- 确定连接条件
- 加入过滤条件
- 精简查询列，建议使用表别名
内连接 (Inner Join)
内连接只返回两个表中连接字段相匹配的行。除了传统的逗号分隔写法，推荐使用标准的 JOIN 语法：
-- 传统写法
SELECT 字段 FROM 表 1 别名 1, 表 2 别名 2 WHERE 连接条件 AND 其他条件;
-- 标准写法
SELECT 字段 FROM 表 1 别名 1 INNER JOIN 表 2 别名 2 ON 连接条件 WHERE 其他条件;
示例：查询学生成绩
假设有课程、班级、学生、分数四张表。查询'唐三藏'的所有学科成绩：
SELECT s.name, sc.score
FROM student AS s, score AS sc
WHERE s.id = sc.student_id AND s.name = '唐三藏';
-- 或者使用 JOIN
SELECT s.name, sc.score
FROM student AS s JOIN score sc ON s.id = sc.student_id AND s.name = '唐三藏';
若要查询所有学生的总成绩，需要配合 GROUP BY：
SELECT s.name, SUM(sc.score)
FROM student AS s, score AS sc
WHERE s.id = sc.student_id
GROUP BY s.id;
外连接 (Outer Join)
MySQL 支持左外连接和右外连接，但不支持全外连接。
- 左外连接 (LEFT JOIN)：返回左表所有记录，右表无匹配则显示 NULL。
- 右外连接 (RIGHT JOIN)：返回右表所有记录，左表无匹配则显示 NULL。
左外连接应用
查询没有参加考试的学生。让 student 左外连接 score，若成绩为空则说明未考试：
SELECT s.id, s.name, sc.*
FROM student s LEFT JOIN score sc ON s.id = sc.student_id;
-- 筛选出未考试的学生
SELECT s.id, s.name, s.class_id
FROM student s LEFT JOIN score sc ON s.id = sc.student_id
WHERE sc.score IS NULL;
右外连接应用
查询没有学生的班级。让 student 右外连接 class：
SELECT * FROM student s RIGHT JOIN class c ON s.class_id = c.id;
-- 筛选出没有学生的班级
SELECT * FROM student s RIGHT JOIN class c ON s.class_id = c.id
WHERE s.id IS NULL;
自连接 (Self Join)
自连接是将表与自己进行连接，通常用于比较同一表中的不同行。必须给表起不同的别名，否则无法区分。
例如，查询 MySQL 成绩大于 Java 成绩的学生：
SELECT s1.*, s2.*
FROM score s1, score s2, course c1, course c2
WHERE s1.student_id = s2.student_id
AND s1.course_id = c1.id
AND s2.course_id = c2.id
AND c1.NAME = 'MySQL'
AND c2.NAME = 'java'
AND s1.score > s2.score;
若想查看详细信息（含姓名、班级），需进一步关联 student 和 class 表：
SELECT stu.name AS 姓名, c.name AS 班级, s1.score AS MySQL, s2.score AS java
FROM score s1, score s2, course c1, course c2, student stu, class c
WHERE s1.student_id = s2.student_id
AND s1.course_id = c1.id
AND s2.course_id = c2.id
AND stu.id = s1.student_id
AND stu.class_id = c.id
AND c1.NAME = 'MySQL'
AND c2.NAME = 'java'
AND s1.score > s2.score;
子查询 (Subquery)
子查询是指在一个查询语句中嵌套另一个查询，其结果可作为条件或临时表使用。
单行子查询
WHERE 条件中的子查询只返回一行数据，常用 = 或 IN。
例如，查询与'不想毕业'同学同班的所有学生：
SELECT * FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '不想毕业');
多列子查询
子查询返回多行或多列数据，通常配合 IN 使用。
查询选修了 MySQL 或 Java 课程的成绩：
SELECT * FROM score
WHERE course_id IN (SELECT id FROM course WHERE name = 'MySQL' OR name = 'java');
FROM 子句中的子查询
MySQL 会自动为子查询创建临时表，可将其视为普通表进行连接或过滤。
例如，查询比'Java001 班'平均分高的成绩信息：
SELECT * FROM score s,
(SELECT AVG(sc.score) AS score
FROM score sc, class c, student s
WHERE c.id = s.class_id AND sc.student_id = s.id AND c.NAME = 'java001 班') tmp
WHERE s.score > tmp.score;
合并查询 (Union)
使用 UNION 操作符合并多个 SELECT 的结果集。
- UNION：自动去除重复行。
- UNION ALL：保留所有行，包括重复项，性能更高。
示例：合并 student 表中 ID<3 的同学与 student1 表中的所有同学：
SELECT * FROM student WHERE id < 3
UNION
SELECT * FROM student1;
-- 不去重
SELECT * FROM student WHERE id < 3
UNION ALL
SELECT * FROM student1;
插入查询结果
可以将一个查询的结果直接插入到另一个表中：
INSERT INTO table_name [(column, ...)] SELECT ...;
例如，将 student 表中'C++001 班'的学生复制到 student1 表：
INSERT INTO student1 (name, class_id)
SELECT s.name, s.class_id
FROM student s, class c
WHERE s.class_id = c.id AND c.name = 'C++001 班';
