多表查询与集合运算
7.12 多表查询
7.12.1 概述
flowchart TB
subgraph 多表查询分类
subgraph 连接查询
A1[内连接 INNER JOIN]
A2[外连接]
A3[交叉连接 CROSS JOIN]
A1 --> A11[等值连接]
A1 --> A12[非等值连接]
A1 --> A13[自然连接]
A2 --> A21[左外连接 LEFT JOIN]
A2 --> A22[右外连接 RIGHT JOIN]
A2 --> A23[全外连接]
end
subgraph 子查询
B1[单行单列子查询]
B2[多行单列子查询]
B3[多行多列子查询]
end
end
7.12.2 连接查询
连接条件
-- 公共列连接（通常是主键-外键关系）
ON 学院.学院编号 = 课程.学院编号
-- 两表的公共列形态：
-- 1. 都是主键
-- 2. 一个主键，一个外键
内连接 INNER JOIN
等值连接：
-- 隐式内连接（使用 WHERE）
SELECT * FROM stu, sc
WHERE stu.sno = sc.sno;
-- 显式内连接（使用 INNER JOIN）
SELECT * FROM stu
INNER JOIN sc ON stu.sno = sc.sno;
-- INNER 可以省略
SELECT * FROM stu
JOIN sc ON stu.sno = sc.sno;
内连接示意图：
flowchart LR
subgraph stu表
S1[S001]
S2[S002]
S3[S003]
end
subgraph sc表
C1[S001]
C2[S002]
C3[S005]
end
S1 <-->|✓匹配| C1
S2 <-->|✓匹配| C2
S3 -.-|✗无匹配| C3
💡 结果：只返回两表都有的 S001, S002
自然连接：
-- MySQL 支持 NATURAL JOIN（自动匹配同名列，去除重复列）
SELECT * FROM stu NATURAL JOIN sc;
-- 等价于手动指定列
SELECT stu.sno, stu.sname, sc.cno, sc.grade
FROM stu
JOIN sc ON stu.sno = sc.sno;
三表连接：
-- 查询学生选课成绩，显示学生姓名、课程名、成绩
SELECT 学生.姓名, 课程.课程名称, 选课成绩.成绩
FROM 学生
JOIN 选课成绩 ON 学生.学号 = 选课成绩.学号
JOIN 课程 ON 选课成绩.课程编号 = 课程.课程编号;
外连接
左外连接 LEFT JOIN：
-- 显示所有学生，包括没有选课的
SELECT stu.*, sc.*
FROM stu
LEFT JOIN sc ON stu.sno = sc.sno;
| sno | sname | cno | grade |
|---|---|---|---|
| S001 | 张三 | C001 | 85 |
| S002 | 李四 | C001 | 90 |
| S003 | 王五 | NULL | NULL |
💡 左表（stu）的所有记录都保留
右外连接 RIGHT JOIN：
-- 显示所有成绩，包括没有对应学生的
SELECT stu.*, sc.*
FROM stu
RIGHT JOIN sc ON stu.sno = sc.sno;
左右连接互换：
-- 以下两个查询结果相同
SELECT * FROM A LEFT JOIN B ON A.id = B.id;
SELECT * FROM B RIGHT JOIN A ON A.id = B.id;
-- 只是把表的位置交换了
全外连接：
-- MySQL 不直接支持 FULL OUTER JOIN
-- 需要使用 UNION 模拟
SELECT * FROM stu LEFT JOIN sc ON stu.sno = sc.sno
UNION
SELECT * FROM stu RIGHT JOIN sc ON stu.sno = sc.sno;
交叉连接 CROSS JOIN
-- 笛卡尔积：每行与每行组合
SELECT * FROM stu CROSS JOIN sc;
-- 等价于
SELECT * FROM stu, sc;
-- 如果 stu 有12行，sc 有12行，结果有 144 行
连接小结
| 连接类型 | 结果说明 |
|---|---|
INNER JOIN |
只返回匹配的行（结果最少） |
LEFT JOIN |
返回左表所有行 + 匹配的右表行 |
RIGHT JOIN |
返回右表所有行 + 匹配的左表行 |
FULL JOIN |
返回两表所有行（MySQL需用UNION模拟） |
CROSS JOIN |
返回笛卡尔积（结果最多） |
连接 + 其他子句
-- 查询教师欧阳淑芳所上的所有课堂
SELECT 课堂.课堂名称, 课堂.开课年份, 课堂.开课学期
FROM 教师
INNER JOIN 课堂 ON 教师.教师编号 = 课堂.教师编号
WHERE 教师.姓名 = '欧阳淑芳'
ORDER BY 课堂.开课年份, 课堂.开课学期;
-- 统计计算机学院每位教师的教学工作量
SELECT 教师.教师编号, SUM(课程.学时数) AS 学时数
FROM 教师
INNER JOIN 课堂 ON 教师.教师编号 = 课堂.教师编号
INNER JOIN 课程 ON 课堂.课程编号 = 课程.课程编号
INNER JOIN 学院 ON 教师.学院编号 = 学院.学院编号
WHERE 课堂.开课年份 = '2017-2018'
AND 课堂.开课学期 = '一'
AND 学院名称 = '计算机学院'
GROUP BY 教师.教师编号
ORDER BY 学时数 DESC;
7.12.3 子查询
概述
单行单列子查询
-- 查询成绩高于叶淑华的学生
SELECT * FROM stus
WHERE score > (
SELECT score FROM stus WHERE name = '叶淑华'
);
多行单列子查询
-- 查询财务部和销售部的所有员工
SELECT * FROM emp
WHERE dep_id IN (
SELECT id FROM dept
WHERE dept_name = '财务部' OR dept_name = '销售部'
);
-- 使用 ANY（满足任意一个）
SELECT * FROM 选课成绩
WHERE 成绩 > ANY (
SELECT 成绩 FROM 选课成绩 WHERE 学号 = 'S001'
);
-- 使用 ALL（满足所有）
SELECT * FROM 选课成绩
WHERE 成绩 > ALL (
SELECT 成绩 FROM 选课成绩 WHERE 学号 = 'S001'
);
多行多列子查询（派生表）
-- 查询年龄大于20的员工信息和部门信息
SELECT * FROM
(SELECT * FROM emp WHERE age > 20) AS t1
JOIN dept ON t1.dep_id = dept.id;
⚠️ 注意：MySQL 要求派生表必须有别名！
不相关子查询
-- 子查询可以独立执行
SELECT * FROM 学生
WHERE 学院编号 = (
SELECT 学院编号 FROM 学院 WHERE 学院名称 = '计算机学院'
);
-- 判断用 = 还是 IN：
-- 子查询返回单值 → 用 =
-- 子查询返回多值 → 用 IN
相关子查询
-- 子查询引用了外部表，无法独立执行
-- 查询每门课成绩高于该课平均分的记录
SELECT * FROM sc AS t1
WHERE grade > (
SELECT AVG(grade) FROM sc AS t2
WHERE t1.cno = t2.cno -- 引用了外部的 t1
);
💡 相关子查询执行过程
外部查询每处理一行，子查询就执行一次：
第1行：t1.cno = 'C001' → 计算C001的平均分 → 比较 第2行：t1.cno = 'C001' → 计算C001的平均分 → 比较 第3行：t1.cno = 'C002' → 计算C002的平均分 → 比较 ...
EXISTS 子查询
-- 查询有选课记录的学生
SELECT * FROM 学生
WHERE EXISTS (
SELECT 1 FROM 选课成绩
WHERE 选课成绩.学号 = 学生.学号
);
-- 查询没有选课记录的学生
SELECT * FROM 学生
WHERE NOT EXISTS (
SELECT 1 FROM 选课成绩
WHERE 选课成绩.学号 = 学生.学号
);
别名的重要性
-- 自关联查询：同一张表比较
-- 查询成绩高于自己平均分的记录
-- ✗ 错误写法：无法区分两个 sc
SELECT sno, cno
FROM sc
WHERE grade > (SELECT AVG(grade) FROM sc WHERE sc.sno = sc.sno);
-- ✓ 正确写法：使用别名区分
SELECT t1.sno, t1.cno
FROM sc AS t1
WHERE t1.grade > (
SELECT AVG(t2.grade)
FROM sc AS t2
WHERE t1.sno = t2.sno
);
7.13 集合运算
7.13.1 概述
-- 基本格式
查询1
<集合运算>
查询2
[ORDER BY ...]
7.13.2 UNION 并集
-- 合并两个查询结果，去除重复行
SELECT 姓名, 性别 FROM 教师
UNION
SELECT 姓名, 性别 FROM 学生;
-- 保留重复行
SELECT 姓名, 性别 FROM 教师
UNION ALL
SELECT 姓名, 性别 FROM 学生;
| 运算 | 说明 |
|---|---|
UNION |
去除重复行（较慢，需要排序去重） |
UNION ALL |
保留所有行（较快，直接合并） |
💡 如果确定没有重复，或需要保留重复，用
UNION ALL效率更高
7.13.3 INTERSECT 交集（MySQL 8.0.31+）
-- 返回两个查询都有的记录
SELECT 姓名 FROM 教师
INTERSECT
SELECT 姓名 FROM 学生;
-- MySQL 8.0.31 之前的替代方案
SELECT DISTINCT 教师.姓名
FROM 教师
INNER JOIN 学生 ON 教师.姓名 = 学生.姓名;
7.13.4 EXCEPT 差集（MySQL 8.0.31+）
-- 返回在第一个查询中但不在第二个查询中的记录
SELECT 姓名 FROM 教师
EXCEPT
SELECT 姓名 FROM 学生;
-- MySQL 8.0.31 之前的替代方案
SELECT 姓名 FROM 教师
WHERE 姓名 NOT IN (SELECT 姓名 FROM 学生);
-- 或使用 LEFT JOIN
SELECT 教师.姓名
FROM 教师
LEFT JOIN 学生 ON 教师.姓名 = 学生.姓名
WHERE 学生.姓名 IS NULL;
7.13.5 集合运算示意图
7.13.6 集合运算规则
| 规则 | 说明 |
|---|---|
| 列数相同 | 两个查询的列数必须相同 |
| 类型兼容 | 对应列的数据类型必须兼容 |
| ORDER BY 位置 | 只能出现在最后，对整个结果排序 |
| 列名 | 以第一个查询为准 |
| 优先级 | INTERSECT > UNION = EXCEPT |
7.14 快速参考
7.14.1 DML 语句汇总
-- 插入
INSERT INTO 表名(列1, 列2) VALUES (值1, 值2);
INSERT INTO 表名(列1, 列2) VALUES (值1, 值2), (值3, 值4);
-- 删除
DELETE FROM 表名 WHERE 条件;
TRUNCATE TABLE 表名;
-- 更新
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;
-- 查询
SELECT 列 FROM 表
WHERE 条件
GROUP BY 分组列
HAVING 分组条件
ORDER BY 排序列
LIMIT 偏移, 数量;
7.14.2 常用函数速查
| SQL Server | MySQL | 说明 |
|---|---|---|
LEN() |
CHAR_LENGTH() |
字符长度 |
GETDATE() |
NOW() |
当前时间 |
+ |
CONCAT() |
字符串连接 |
TOP(N) |
LIMIT N |
前N条 |
ISNULL() |
IFNULL() |
空值替换 |
CONVERT() |
CAST() / CONVERT() |
类型转换 |
7.14.3 SQL 执行顺序
| 顺序 | 子句 | 说明 |
|---|---|---|
| 1 | FROM | 确定数据来源 |
| 2 | JOIN | 表连接 |
| 3 | WHERE | 行过滤 |
| 4 | GROUP BY | 分组 |
| 5 | HAVING | 组过滤 |
| 6 | SELECT | 选择列 |
| 7 | DISTINCT | 去重 |
| 8 | ORDER BY | 排序 |
| 9 | LIMIT | 分页 |
💬 评论