第6章 数据操作2 多表操作 完整落地教程（医院场景·表对照版）

本教程全程基于医院真实业务场景，所有示例统一使用4张固定基准表，先完整展示基准表的建表语句、初始全量数据，后续所有查询操作均基于此初始状态。每个知识点都配套「需求说明→执行SQL→原始表依据→查询结果表→实操理解」，前后对照直观易懂，所有代码可直接复制到数据库执行，和教程结果完全一致。

一、教程通用基准表（全量初始状态）

所有章节的示例均基于以下4张医院核心业务表，先执行建表和数据插入语句，即可复现教程所有结果。

1. 建表&插入初始数据SQL（可直接复制执行）

-- 1. 科室表 his_dpt（主表，存储科室基础信息）
CREATE TABLE his_dpt (
    dpt_code VARCHAR(10) PRIMARY KEY,  -- 科室编码，主键唯一标识
    dpt_name VARCHAR(20) NOT NULL,     -- 科室名称
    location VARCHAR(30)                -- 科室所在位置
);
-- 插入科室初始数据
INSERT INTO his_dpt VALUES
('NEI01', '心内科', '门诊楼2层'),
('WAI01', '普外科', '门诊楼3层'),
('ER01', '急诊科', '门诊楼1层'),
('PED01', '儿科', '门诊楼4层');

-- 2. 医生表 his_emp（从表，存储医生信息，关联科室表）
CREATE TABLE his_emp (
    emp_id INT PRIMARY KEY,      -- 医生编号，主键唯一标识
    emp_name VARCHAR(20) NOT NULL, -- 医生姓名
    dpt_code VARCHAR(10),        -- 所属科室编码，关联科室表主键
    title VARCHAR(10),           -- 医生职称
    sal INT                       -- 薪资
);
-- 插入医生初始数据
INSERT INTO his_emp VALUES
(1, '张医生', 'NEI01', '主任医师', 12000),
(2, '李医生', 'WAI01', '副主任医师', 10000),
(3, '王医生', 'NEI01', '主治医师', 8000),
(4, '赵医生', 'ER01', '主治医师', 9000),
(5, '刘医生', 'PED01', '主任医师', 11000),
(6, '孙医生', NULL, '主任医师', 11000); -- 暂未分配科室的医生

-- 3. 挂号表 his_reg（存储患者挂号记录，关联医生表）
CREATE TABLE his_reg (
    reg_id INT PRIMARY KEY,        -- 挂号单号，主键唯一标识
    pat_name VARCHAR(20) NOT NULL, -- 患者姓名
    emp_id INT,                     -- 接诊医生编号，关联医生表主键
    reg_date DATETIME,              -- 挂号时间
    reg_fee DECIMAL(5,2)            -- 挂号费
);
-- 插入挂号初始数据
INSERT INTO his_reg VALUES
(1, '患者甲', 1, '2025-04-20 08:00:00', 50.00),
(2, '患者乙', 1, '2025-04-20 08:10:00', 50.00),
(3, '患者丙', 2, '2025-04-20 08:20:00', 40.00),
(4, '患者丁', 3, '2025-04-20 08:30:00', 30.00),
(5, '患者戊', 4, '2025-04-20 08:40:00', 30.00);

-- 4. 药品表 his_drug（存储医院药品信息）
CREATE TABLE his_drug (
    drug_id INT PRIMARY KEY,        -- 药品编号，主键唯一标识
    drug_name VARCHAR(30) NOT NULL, -- 药品名称
    drug_price DECIMAL(6,2),        -- 药品单价
    drug_manu VARCHAR(50)            -- 生产厂家
);
-- 插入药品初始数据
INSERT INTO his_drug VALUES
(1, '硝苯地平缓释片', 28.50, '拜耳医药'),
(2, '阿莫西林胶囊', 12.80, '华北制药'),
(3, '布洛芬缓释胶囊', 18.60, '中美史克'),
(4, '小儿感冒颗粒', 25.90, '三九医药'),
(5, '急救肾上腺素', 58.00, '天津金耀药业');

2. 4张基准表完整原始数据（所有操作的起点）

表1：科室表 his_dpt（4条数据）

表2：医生表 his_emp（6条数据）

表3：挂号表 his_reg（5条数据）

表4：药品表 his_drug（5条数据）

6.1 联合查询（UNION）

核心大白话理解

联合查询是纵向合并多个独立SELECT查询的结果，就像把两个结构相同的Excel表格，上下复制粘贴到一起。核心是「行合并」，不会新增列，只做结果的上下拼接。

核心语法&硬性规则

SELECT 字段1,字段2... FROM 表1 [WHERE 条件]
UNION [ALL | DISTINCT]
SELECT 字段1,字段2... FROM 表2 [WHERE 条件];

1. 硬性规则：每个SELECT语句的字段数量必须完全一致，对应字段的类型必须兼容；

2. 最终结果的字段名，完全由第一个SELECT语句决定；

3. 关键字区别：

   • UNION（默认）：自动去除结果中完全重复的行，有去重计算开销；

   • UNION ALL：保留所有结果（包括重复行），无去重开销，大数据量下速度远快于UNION。

实操示例1：基础联合查询

查询需求

查询心内科和儿科的医生编号、姓名、职称，合并成一个结果展示。

执行SQL

-- 第一个查询：心内科医生
SELECT emp_id, emp_name, title FROM his_emp WHERE dpt_code='NEI01'
UNION
-- 第二个查询：儿科医生
SELECT emp_id, emp_name, title FROM his_emp WHERE dpt_code='PED01';

原始表依据

数据来自【医生表his_emp】，心内科对应dpt_code=\&\#39;NEI01\&\#39;（1号、3号医生），儿科对应dpt_code=\&\#39;PED01\&\#39;（5号医生）。

查询结果表

实操示例2：带排序的联合查询

查询需求

心内科的医生按薪资升序取前2名，其他科室按薪资降序取前2名，合并展示。

执行SQL

(SELECT emp_id, emp_name, title, sal, dpt_code FROM his_emp
 WHERE dpt_code='NEI01' ORDER BY sal ASC LIMIT 2)
UNION
(SELECT emp_id, emp_name, title, sal, dpt_code FROM his_emp
 WHERE dpt_code<>'NEI01' ORDER BY sal DESC LIMIT 2);

原始表依据

数据来自【医生表his_emp】，子查询1筛选心内科医生，子查询2筛选非心内科医生。

查询结果表

实操理解&避坑指南

1. 适用场景：同结构分表数据汇总（如2024年和2025年挂号表年度数据合并）、不同表的同类型数据合并（如医生和护士名单合并为全院医护清单）；

2. 避坑点：排序必须给每个子查询加括号，且配合LIMIT才能保证排序生效；无需去重时优先用UNION ALL，避免不必要的性能开销。

6.2 连接查询

核心大白话理解

连接查询是横向拼接多张表的数据，就像把两个Excel表，按指定的关联条件，左右拼成一个宽表。核心是「列合并」，是多表查询最核心的用法，解决了“关联数据分散在不同表”的问题。

6.2.1 内连接（INNER JOIN）—— 最常用

核心大白话

只保留两张表中完全匹配上关联条件的行，两边有任何一边匹配不上，这行数据就会被丢弃。比如没有科室的孙医生，和科室表匹配不上，内连接结果里就不会出现。

核心语法

-- 显式内连接（推荐，速度更快、可读性更好）
SELECT 表1.字段, 表2.字段...
FROM 表1 [INNER] JOIN 表2
ON 两张表的关联条件;

-- 隐式内连接（简写格式，新手易踩坑）
SELECT 表1.字段, 表2.字段...
FROM 表1, 表2
WHERE 两张表的关联条件;

实操示例

查询需求

查询所有医生的姓名、职称，以及对应的科室名称、科室位置。

执行SQL

SELECT 
  a.emp_name 医生姓名, 
  a.title 职称, 
  b.dpt_name 科室名称, 
  b.location 科室位置
FROM his_emp a INNER JOIN his_dpt b
ON a.dpt_code = b.dpt_code; -- 关联条件：医生表的科室编码=科室表的科室编码

原始表依据

左表【医生表his_emp】、右表【科室表his_dpt】，通过dpt_code字段关联，仅保留两边都有匹配值的行。

查询结果表

6.2.2 外连接（OUTER JOIN）—— 次常用

核心大白话

外连接会强制保留某一张表的所有行，另一张表匹配不上的字段，自动填充NULL，解决了内连接会丢失数据的问题。日常开发99%使用左外连接。

核心分类

1. 左外连接（LEFT JOIN）：LEFT JOIN左边的表为「左表」，左表的所有行都会被完整保留，右表匹配不上的字段填充NULL；

2. 右外连接（RIGHT JOIN）：RIGHT JOIN右边的表为「右表」，右表的所有行都会被完整保留，左表匹配不上的字段填充NULL。

实操示例1：左外连接（和PPT示例完全对应）

查询需求

查询所有医生的挂号记录，哪怕这个医生没有接诊过任何患者，也要显示医生的基本信息。

执行SQL

SELECT 
  a.emp_id 医生编号, 
  a.emp_name 医生姓名, 
  b.reg_id 挂号单号, 
  b.pat_name 患者姓名, 
  b.reg_fee 挂号费
FROM his_emp a LEFT JOIN his_reg b
ON a.emp_id = b.emp_id; -- 关联条件：医生编号=接诊医生编号

原始表依据

左表【医生表his_emp】6行数据全部保留，右表【挂号表his_reg】仅匹配有挂号记录的医生。

查询结果表

实操示例2：右外连接

查询需求

查询所有科室的医生信息，哪怕这个科室没有医生，也要显示科室信息。

执行SQL

SELECT 
  b.dpt_name 科室名称, 
  a.emp_name 医生姓名, 
  a.title 职称
FROM his_emp a RIGHT JOIN his_dpt b
ON a.dpt_code = b.dpt_code;

原始表依据

右表【科室表his_dpt】4行数据全部保留，左表【医生表his_emp】匹配对应科室的医生。

查询结果表

6.2.3 交叉连接（CROSS JOIN）—— 避坑专用

核心大白话

交叉连接就是数学里的「笛卡尔积」，第一张表的每一行，都会和第二张表的每一行强行拼接。比如4个科室、6个医生，交叉连接结果是4×6=24行，完全没有业务意义，99%的场景仅用于避坑。

核心语法

-- 完整语法
SELECT * FROM 表1 CROSS JOIN 表2;
-- 简写语法（新手最易踩坑：隐式内连接忘记写关联条件，就会变成交叉连接）
SELECT * FROM 表1,表2;

实操理解&避坑指南

新手写隐式内连接时，忘记写WHERE关联条件，就会生成交叉连接，产生大量无效脏数据。这也是推荐使用显式内连接的核心原因——必须写ON关联条件，不会误写成交叉连接。

6.3 子查询

核心大白话理解

子查询就是SQL套娃，在一个大的主查询里，嵌套一个小的、完整的SELECT查询。先执行里面的子查询拿到结果，再把结果给主查询当筛选条件/数据源，子查询必须用小括号\(\)包裹，且能独立执行。

6.3.1 标量子查询（单值子查询）—— 最简单、最常用

核心大白话

子查询执行后，只返回1个值（1行1列），通常和=、\>、\<、\>=、\<=这些比较运算符搭配使用。

实操示例

查询需求

查询药品单价低于「3号药品（布洛芬缓释胶囊）」的药品编号、名称、单价。

执行SQL

SELECT 
  drug_id 药品编号, 
  drug_name 药品名称, 
  drug_price 药品单价
FROM his_drug
WHERE drug_price < (SELECT drug_price FROM his_drug WHERE drug_id=3);

执行逻辑

子查询先执行，拿到3号药品的单价18\.60，主查询再用这个值做筛选。

原始表依据

数据来自【药品表his_drug】，3号药品单价18.60。

查询结果表

6.3.2 列子查询（多值子查询）

核心大白话

子查询执行后，返回1列N行的结果集，必须搭配IN、NOT IN、ANY、ALL这些关键字使用，不能直接用比较运算符。

核心关键字说明

实操示例1：IN关键字基础用法

查询需求

查询有接诊过患者的医生信息（有挂号记录的医生）。

执行SQL

SELECT 
  emp_id 医生编号, 
  emp_name 医生姓名, 
  title 职称, 
  sal 薪资
FROM his_emp
WHERE emp_id IN (SELECT emp_id FROM his_reg);

执行逻辑

子查询先执行，拿到挂号表里所有接诊医生编号\[1,1,2,3,4\]，主查询匹配编号保留对应行。

原始表依据

左表【医生表his_emp】，子查询数据来自【挂号表his_reg】。

查询结果表

实操示例2：ALL关键字用法（和PPT示例完全对应）

查询需求

查询药品单价高于「华北制药生产的所有药品」的药品信息。

执行SQL

SELECT 
  drug_id 药品编号, 
  drug_name 药品名称, 
  drug_price 药品单价, 
  drug_manu 生产厂家
FROM his_drug
WHERE drug_price > ALL (SELECT drug_price FROM his_drug WHERE drug_manu='华北制药');

执行逻辑

子查询拿到华北制药所有药品的单价\[12\.80\]，主查询要求比所有值都大，也就是比最大值还大。

查询结果表

6.3.3 EXISTS 子查询

核心大白话

EXISTS子查询不关心子查询返回什么内容，只判断子查询有没有结果返回：

• 子查询有结果返回 → 返回TRUE，主查询保留当前行；

• 子查询无结果返回 → 返回FALSE，主查询丢弃当前行。
  大数据量下，EXISTS的性能远优于IN子查询，因为它找到一条匹配数据就会停止，不用遍历全表。

实操示例

查询需求

查询从来没有接诊过患者的医生信息（无挂号记录的医生）。

执行SQL

SELECT 
  emp_id 医生编号, 
  emp_name 医生姓名, 
  title 职称, 
  sal 薪资
FROM his_emp a
WHERE NOT EXISTS (SELECT * FROM his_reg b WHERE a.emp_id = b.emp_id);

执行逻辑

遍历医生表的每一行，去挂号表查有没有这个医生的挂号记录，没有匹配结果就保留该行。

原始表依据

左表【医生表his_emp】，子查询数据来自【挂号表his_reg】。

查询结果表

6.4 外键约束

核心大白话理解

外键约束就是给两张关联表上的「数据安全锁」，保证关联数据的一致性和完整性，杜绝脏数据。
举个例子：医生表的dpt_code关联科室表的dpt_code，加了外键约束后：

1. 不能在医生表里，添加一个科室表里不存在的科室编码；

2. 不能先删除科室表里的心内科，再删除医生表里的心内科医生（科室还有关联医生，直接报错）。

核心概念

• 主表（父表）：被引用的表，比如科室表his_dpt；

• 从表（子表）：引用外键的表，比如医生表his_emp。

6.4.1 添加外键约束

1. 创建表时直接添加外键

CREATE TABLE his_emp (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(20) NOT NULL,
    dpt_code VARCHAR(10),
    title VARCHAR(10),
    sal INT,
    -- 添加外键约束
    CONSTRAINT fk_emp_dpt FOREIGN KEY(dpt_code) 
    REFERENCES his_dpt(dpt_code)
    [ON DELETE 级联规则]
    [ON UPDATE 级联规则]
);

2. 已存在的表添加外键

ALTER TABLE 从表名
ADD [CONSTRAINT 外键名称] FOREIGN KEY(从表外键字段) 
REFERENCES 主表名(主表主键字段)
[ON DELETE 级联规则]
[ON UPDATE 级联规则];

医院场景实操示例

给已存在的医生表，添加和科室表的外键约束

ALTER TABLE his_emp
ADD CONSTRAINT fk_emp_dpt FOREIGN KEY(dpt_code) 
REFERENCES his_dpt(dpt_code);

级联规则详解（ON DELETE/ON UPDATE）

这是主表数据被删除/修改时，从表关联数据的处理规则，核心常用3个：

⚠️ 实操警告：生产环境尽量不要用CASCADE级联删除，万一误删主表的一行数据，从表所有关联数据都会被自动删除，无法挽回。

6.4.2 删除外键约束

核心语法

ALTER TABLE 从表名 DROP FOREIGN KEY 外键名称;

实操示例

删除医生表的外键约束fk_emp_dpt

ALTER TABLE his_emp DROP FOREIGN KEY fk_emp_dpt;

实操理解&避坑指南

1. 外键约束是保证数据一致性的利器，但会降低插入、删除、更新的性能，高并发场景要权衡使用；

2. 外键字段和主表的主键字段，数据类型必须完全一致（比如都是VARCHAR(10)），否则无法创建外键；

3. 主表的被引用字段，必须有主键约束或唯一约束，否则无法创建外键。

6.5 综合动手实践（医院场景）

练习1：查询每个科室的名称，以及科室下的医生人数

执行SQL

SELECT b.dpt_name 科室名称, COUNT(a.emp_id) 医生人数
FROM his_dpt b LEFT JOIN his_emp a
ON b.dpt_code = a.dpt_code
GROUP BY b.dpt_code, b.dpt_name;

查询结果表

练习2：查询挂号费最高的挂号记录，对应的患者姓名、接诊医生姓名、科室名称

执行SQL

SELECT 
  r.pat_name 患者姓名, 
  e.emp_name 接诊医生, 
  d.dpt_name 科室名称, 
  r.reg_fee 挂号费
FROM his_reg r
LEFT JOIN his_emp e ON r.emp_id = e.emp_id
LEFT JOIN his_dpt d ON e.dpt_code = d.dpt_code
WHERE r.reg_fee = (SELECT MAX(reg_fee) FROM his_reg);

查询结果表

本章核心总结

1. 联合查询UNION：纵向合并多个查询结果，适合同结构数据汇总，优先用UNION ALL提升性能；

2. 连接查询：横向拼接多张表，内连接只保留匹配数据，左外连接保留左表全量数据，是多表查询的核心；

3. 子查询：嵌套查询解决复杂条件，标量子查询处理单值条件，EXISTS子查询大数据量下性能更优；

4. 外键约束：给关联数据上锁，保证数据一致性，生产环境优先用默认RESTRICT规则，慎用级联删除。

（）