MySQL 联合查询实战：多表关联与子查询详解

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 班';