第6章 數(shù)據(jù)操作2 多表操作 完整落地教程（醫(yī)院場景·表對照版）

本教程全程基于醫(yī)院真實(shí)業(yè)務(wù)場景，所有示例統(tǒng)一使用4張固定基準(zhǔn)表，先完整展示基準(zhǔn)表的建表語句、初始全量數(shù)據(jù)，后續(xù)所有查詢操作均基于此初始狀態(tài)。每個(gè)知識點(diǎn)都配套「需求說明→執(zhí)行SQL→原始表依據(jù)→查詢結(jié)果表→實(shí)操理解」，前后對照直觀易懂，所有代碼可直接復(fù)制到數(shù)據(jù)庫執(zhí)行，和教程結(jié)果完全一致。

一、教程通用基準(zhǔn)表（全量初始狀態(tài)）

所有章節(jié)的示例均基于以下4張醫(yī)院核心業(yè)務(wù)表，先執(zhí)行建表和數(shù)據(jù)插入語句，即可復(fù)現(xiàn)教程所有結(jié)果。

1. 建表&插入初始數(shù)據(jù)SQL（可直接復(fù)制執(zhí)行）

-- 1. 科室表 his_dpt（主表，存儲科室基礎(chǔ)信息）
CREATE TABLE his_dpt (
    dpt_code VARCHAR(10) PRIMARY KEY,  -- 科室編碼，主鍵唯一標(biāo)識
    dpt_name VARCHAR(20) NOT NULL,     -- 科室名稱
    location VARCHAR(30)                -- 科室所在位置
);
-- 插入科室初始數(shù)據(jù)
INSERT INTO his_dpt VALUES
('NEI01', '心內(nèi)科', '門診樓2層'),
('WAI01', '普外科', '門診樓3層'),
('ER01', '急診科', '門診樓1層'),
('PED01', '兒科', '門診樓4層');

-- 2. 醫(yī)生表 his_emp（從表，存儲醫(yī)生信息，關(guān)聯(lián)科室表）
CREATE TABLE his_emp (
    emp_id INT PRIMARY KEY,      -- 醫(yī)生編號，主鍵唯一標(biāo)識
    emp_name VARCHAR(20) NOT NULL, -- 醫(yī)生姓名
    dpt_code VARCHAR(10),        -- 所屬科室編碼，關(guān)聯(lián)科室表主鍵
    title VARCHAR(10),           -- 醫(yī)生職稱
    sal INT                       -- 薪資
);
-- 插入醫(yī)生初始數(shù)據(jù)
INSERT INTO his_emp VALUES
(1, '張醫(yī)生', 'NEI01', '主任醫(yī)師', 12000),
(2, '李醫(yī)生', 'WAI01', '副主任醫(yī)師', 10000),
(3, '王醫(yī)生', 'NEI01', '主治醫(yī)師', 8000),
(4, '趙醫(yī)生', 'ER01', '主治醫(yī)師', 9000),
(5, '劉醫(yī)生', 'PED01', '主任醫(yī)師', 11000),
(6, '孫醫(yī)生', NULL, '主任醫(yī)師', 11000); -- 暫未分配科室的醫(yī)生

-- 3. 掛號表 his_reg（存儲患者掛號記錄，關(guān)聯(lián)醫(yī)生表）
CREATE TABLE his_reg (
    reg_id INT PRIMARY KEY,        -- 掛號單號，主鍵唯一標(biāo)識
    pat_name VARCHAR(20) NOT NULL, -- 患者姓名
    emp_id INT,                     -- 接診醫(yī)生編號，關(guān)聯(lián)醫(yī)生表主鍵
    reg_date DATETIME,              -- 掛號時(shí)間
    reg_fee DECIMAL(5,2)            -- 掛號費(fèi)
);
-- 插入掛號初始數(shù)據(jù)
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（存儲醫(yī)院藥品信息）
CREATE TABLE his_drug (
    drug_id INT PRIMARY KEY,        -- 藥品編號，主鍵唯一標(biāo)識
    drug_name VARCHAR(30) NOT NULL, -- 藥品名稱
    drug_price DECIMAL(6,2),        -- 藥品單價(jià)
    drug_manu VARCHAR(50)            -- 生產(chǎn)廠家
);
-- 插入藥品初始數(shù)據(jù)
INSERT INTO his_drug VALUES
(1, '硝苯地平緩釋片', 28.50, '拜耳醫(yī)藥'),
(2, '阿莫西林膠囊', 12.80, '華北制藥'),
(3, '布洛芬緩釋膠囊', 18.60, '中美史克'),
(4, '小兒感冒顆粒', 25.90, '三九醫(yī)藥'),
(5, '急救腎上腺素', 58.00, '天津金耀藥業(yè)');

2. 4張基準(zhǔn)表完整原始數(shù)據(jù)（所有操作的起點(diǎn)）

表1：科室表 his_dpt（4條數(shù)據(jù)）

表2：醫(yī)生表 his_emp（6條數(shù)據(jù)）

表3：掛號表 his_reg（5條數(shù)據(jù)）

表4：藥品表 his_drug（5條數(shù)據(jù)）

6.1 聯(lián)合查詢（UNION）

核心大白話理解

聯(lián)合查詢是縱向合并多個(gè)獨(dú)立SELECT查詢的結(jié)果，就像把兩個(gè)結(jié)構(gòu)相同的Excel表格，上下復(fù)制粘貼到一起。核心是「行合并」，不會新增列，只做結(jié)果的上下拼接。

核心語法&硬性規(guī)則

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

1. 硬性規(guī)則：每個(gè)SELECT語句的字段數(shù)量必須完全一致，對應(yīng)字段的類型必須兼容；

2. 最終結(jié)果的字段名，完全由第一個(gè)SELECT語句決定；

3. 關(guān)鍵字區(qū)別：

   • UNION（默認(rèn)）：自動去除結(jié)果中完全重復(fù)的行，有去重計(jì)算開銷；

   • UNION ALL：保留所有結(jié)果（包括重復(fù)行），無去重開銷，大數(shù)據(jù)量下速度遠(yuǎn)快于UNION。

實(shí)操示例1：基礎(chǔ)聯(lián)合查詢

查詢需求

查詢心內(nèi)科和兒科的醫(yī)生編號、姓名、職稱，合并成一個(gè)結(jié)果展示。

執(zhí)行SQL

-- 第一個(gè)查詢：心內(nèi)科醫(yī)生
SELECT emp_id, emp_name, title FROM his_emp WHERE dpt_code='NEI01'
UNION
-- 第二個(gè)查詢：兒科醫(yī)生
SELECT emp_id, emp_name, title FROM his_emp WHERE dpt_code='PED01';

原始表依據(jù)

數(shù)據(jù)來自【醫(yī)生表his_emp】，心內(nèi)科對應(yīng)dpt_code=\&\#39;NEI01\&\#39;（1號、3號醫(yī)生），兒科對應(yīng)dpt_code=\&\#39;PED01\&\#39;（5號醫(yī)生）。

查詢結(jié)果表

實(shí)操示例2：帶排序的聯(lián)合查詢

查詢需求

心內(nèi)科的醫(yī)生按薪資升序取前2名，其他科室按薪資降序取前2名，合并展示。

執(zhí)行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);

原始表依據(jù)

數(shù)據(jù)來自【醫(yī)生表his_emp】，子查詢1篩選心內(nèi)科醫(yī)生，子查詢2篩選非心內(nèi)科醫(yī)生。

查詢結(jié)果表

實(shí)操理解&避坑指南

1. 適用場景：同結(jié)構(gòu)分表數(shù)據(jù)匯總（如2024年和2025年掛號表年度數(shù)據(jù)合并）、不同表的同類型數(shù)據(jù)合并（如醫(yī)生和護(hù)士名單合并為全院醫(yī)護(hù)清單）；

2. 避坑點(diǎn)：排序必須給每個(gè)子查詢加括號，且配合LIMIT才能保證排序生效；無需去重時(shí)優(yōu)先用UNION ALL，避免不必要的性能開銷。

6.2 連接查詢

核心大白話理解

連接查詢是橫向拼接多張表的數(shù)據(jù)，就像把兩個(gè)Excel表，按指定的關(guān)聯(lián)條件，左右拼成一個(gè)寬表。核心是「列合并」，是多表查詢最核心的用法，解決了“關(guān)聯(lián)數(shù)據(jù)分散在不同表”的問題。

6.2.1 內(nèi)連接（INNER JOIN）—— 最常用

核心大白話

只保留兩張表中完全匹配上關(guān)聯(lián)條件的行，兩邊有任何一邊匹配不上，這行數(shù)據(jù)就會被丟棄。比如沒有科室的孫醫(yī)生，和科室表匹配不上，內(nèi)連接結(jié)果里就不會出現(xiàn)。

核心語法

-- 顯式內(nèi)連接（推薦，速度更快、可讀性更好）
SELECT 表1.字段, 表2.字段...
FROM 表1 [INNER] JOIN 表2
ON 兩張表的關(guān)聯(lián)條件;

-- 隱式內(nèi)連接（簡寫格式，新手易踩坑）
SELECT 表1.字段, 表2.字段...
FROM 表1, 表2
WHERE 兩張表的關(guān)聯(lián)條件;

實(shí)操示例

查詢需求

查詢所有醫(yī)生的姓名、職稱，以及對應(yīng)的科室名稱、科室位置。

執(zhí)行SQL

SELECT 
  a.emp_name 醫(yī)生姓名, 
  a.title 職稱, 
  b.dpt_name 科室名稱, 
  b.location 科室位置
FROM his_emp a INNER JOIN his_dpt b
ON a.dpt_code = b.dpt_code; -- 關(guān)聯(lián)條件：醫(yī)生表的科室編碼=科室表的科室編碼

原始表依據(jù)

左表【醫(yī)生表his_emp】、右表【科室表his_dpt】，通過dpt_code字段關(guān)聯(lián)，僅保留兩邊都有匹配值的行。

查詢結(jié)果表

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

核心大白話

外連接會強(qiáng)制保留某一張表的所有行，另一張表匹配不上的字段，自動填充NULL，解決了內(nèi)連接會丟失數(shù)據(jù)的問題。日常開發(fā)99%使用左外連接。

核心分類

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

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

實(shí)操示例1：左外連接（和PPT示例完全對應(yīng)）

查詢需求

查詢所有醫(yī)生的掛號記錄，哪怕這個(gè)醫(yī)生沒有接診過任何患者，也要顯示醫(yī)生的基本信息。

執(zhí)行SQL

SELECT 
  a.emp_id 醫(yī)生編號, 
  a.emp_name 醫(yī)生姓名, 
  b.reg_id 掛號單號, 
  b.pat_name 患者姓名, 
  b.reg_fee 掛號費(fèi)
FROM his_emp a LEFT JOIN his_reg b
ON a.emp_id = b.emp_id; -- 關(guān)聯(lián)條件：醫(yī)生編號=接診醫(yī)生編號

原始表依據(jù)

左表【醫(yī)生表his_emp】6行數(shù)據(jù)全部保留，右表【掛號表his_reg】僅匹配有掛號記錄的醫(yī)生。

查詢結(jié)果表

實(shí)操示例2：右外連接

查詢需求

查詢所有科室的醫(yī)生信息，哪怕這個(gè)科室沒有醫(yī)生，也要顯示科室信息。

執(zhí)行SQL

SELECT 
  b.dpt_name 科室名稱, 
  a.emp_name 醫(yī)生姓名, 
  a.title 職稱
FROM his_emp a RIGHT JOIN his_dpt b
ON a.dpt_code = b.dpt_code;

原始表依據(jù)

右表【科室表his_dpt】4行數(shù)據(jù)全部保留，左表【醫(yī)生表his_emp】匹配對應(yīng)科室的醫(yī)生。

查詢結(jié)果表

6.2.3 交叉連接（CROSS JOIN）—— 避坑專用

核心大白話

交叉連接就是數(shù)學(xué)里的「笛卡爾積」，第一張表的每一行，都會和第二張表的每一行強(qiáng)行拼接。比如4個(gè)科室、6個(gè)醫(yī)生，交叉連接結(jié)果是4×6=24行，完全沒有業(yè)務(wù)意義，99%的場景僅用于避坑。

核心語法

-- 完整語法
SELECT * FROM 表1 CROSS JOIN 表2;
-- 簡寫語法（新手最易踩坑：隱式內(nèi)連接忘記寫關(guān)聯(lián)條件，就會變成交叉連接）
SELECT * FROM 表1,表2;

實(shí)操理解&避坑指南

新手寫隱式內(nèi)連接時(shí)，忘記寫WHERE關(guān)聯(lián)條件，就會生成交叉連接，產(chǎn)生大量無效臟數(shù)據(jù)。這也是推薦使用顯式內(nèi)連接的核心原因——必須寫ON關(guān)聯(lián)條件，不會誤寫成交叉連接。

6.3 子查詢

核心大白話理解

子查詢就是SQL套娃，在一個(gè)大的主查詢里，嵌套一個(gè)小的、完整的SELECT查詢。先執(zhí)行里面的子查詢拿到結(jié)果，再把結(jié)果給主查詢當(dāng)篩選條件/數(shù)據(jù)源，子查詢必須用小括號\(\)包裹，且能獨(dú)立執(zhí)行。

6.3.1 標(biāo)量子查詢（單值子查詢）—— 最簡單、最常用

核心大白話

子查詢執(zhí)行后，只返回1個(gè)值（1行1列），通常和=、\>、\<、\>=、\<=這些比較運(yùn)算符搭配使用。

實(shí)操示例

查詢需求

查詢藥品單價(jià)低于「3號藥品（布洛芬緩釋膠囊）」的藥品編號、名稱、單價(jià)。

執(zhí)行SQL

SELECT 
  drug_id 藥品編號, 
  drug_name 藥品名稱, 
  drug_price 藥品單價(jià)
FROM his_drug
WHERE drug_price < (SELECT drug_price FROM his_drug WHERE drug_id=3);

執(zhí)行邏輯

子查詢先執(zhí)行，拿到3號藥品的單價(jià)18\.60，主查詢再用這個(gè)值做篩選。

原始表依據(jù)

數(shù)據(jù)來自【藥品表his_drug】，3號藥品單價(jià)18.60。

查詢結(jié)果表

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

核心大白話

子查詢執(zhí)行后，返回1列N行的結(jié)果集，必須搭配IN、NOT IN、ANY、ALL這些關(guān)鍵字使用，不能直接用比較運(yùn)算符。

核心關(guān)鍵字說明

實(shí)操示例1：IN關(guān)鍵字基礎(chǔ)用法

查詢需求

查詢有接診過患者的醫(yī)生信息（有掛號記錄的醫(yī)生）。

執(zhí)行SQL

SELECT 
  emp_id 醫(yī)生編號, 
  emp_name 醫(yī)生姓名, 
  title 職稱, 
  sal 薪資
FROM his_emp
WHERE emp_id IN (SELECT emp_id FROM his_reg);

執(zhí)行邏輯

子查詢先執(zhí)行，拿到掛號表里所有接診醫(yī)生編號\[1,1,2,3,4\]，主查詢匹配編號保留對應(yīng)行。

原始表依據(jù)

左表【醫(yī)生表his_emp】，子查詢數(shù)據(jù)來自【掛號表his_reg】。

查詢結(jié)果表

實(shí)操示例2：ALL關(guān)鍵字用法（和PPT示例完全對應(yīng)）

查詢需求

查詢藥品單價(jià)高于「華北制藥生產(chǎn)的所有藥品」的藥品信息。

執(zhí)行SQL

SELECT 
  drug_id 藥品編號, 
  drug_name 藥品名稱, 
  drug_price 藥品單價(jià), 
  drug_manu 生產(chǎn)廠家
FROM his_drug
WHERE drug_price > ALL (SELECT drug_price FROM his_drug WHERE drug_manu='華北制藥');

執(zhí)行邏輯

子查詢拿到華北制藥所有藥品的單價(jià)\[12\.80\]，主查詢要求比所有值都大，也就是比最大值還大。

查詢結(jié)果表

6.3.3 EXISTS 子查詢

核心大白話

EXISTS子查詢不關(guān)心子查詢返回什么內(nèi)容，只判斷子查詢有沒有結(jié)果返回：

• 子查詢有結(jié)果返回 → 返回TRUE，主查詢保留當(dāng)前行；

• 子查詢無結(jié)果返回 → 返回FALSE，主查詢丟棄當(dāng)前行。
  大數(shù)據(jù)量下，EXISTS的性能遠(yuǎn)優(yōu)于IN子查詢，因?yàn)樗业揭粭l匹配數(shù)據(jù)就會停止，不用遍歷全表。

實(shí)操示例

查詢需求

查詢從來沒有接診過患者的醫(yī)生信息（無掛號記錄的醫(yī)生）。

執(zhí)行SQL

SELECT 
  emp_id 醫(yī)生編號, 
  emp_name 醫(yī)生姓名, 
  title 職稱, 
  sal 薪資
FROM his_emp a
WHERE NOT EXISTS (SELECT * FROM his_reg b WHERE a.emp_id = b.emp_id);

執(zhí)行邏輯

遍歷醫(yī)生表的每一行，去掛號表查有沒有這個(gè)醫(yī)生的掛號記錄，沒有匹配結(jié)果就保留該行。

原始表依據(jù)

左表【醫(yī)生表his_emp】，子查詢數(shù)據(jù)來自【掛號表his_reg】。

查詢結(jié)果表

6.4 外鍵約束

核心大白話理解

外鍵約束就是給兩張關(guān)聯(lián)表上的「數(shù)據(jù)安全鎖」，保證關(guān)聯(lián)數(shù)據(jù)的一致性和完整性，杜絕臟數(shù)據(jù)。
舉個(gè)例子：醫(yī)生表的dpt_code關(guān)聯(lián)科室表的dpt_code，加了外鍵約束后：

1. 不能在醫(yī)生表里，添加一個(gè)科室表里不存在的科室編碼；

2. 不能先刪除科室表里的心內(nèi)科，再刪除醫(yī)生表里的心內(nèi)科醫(yī)生（科室還有關(guān)聯(lián)醫(yī)生，直接報(bào)錯(cuò)）。

核心概念

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

• 從表（子表）：引用外鍵的表，比如醫(yī)生表his_emp。

6.4.1 添加外鍵約束

1. 創(chuàng)建表時(shí)直接添加外鍵

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 級聯(lián)規(guī)則]
    [ON UPDATE 級聯(lián)規(guī)則]
);

2. 已存在的表添加外鍵

ALTER TABLE 從表名
ADD [CONSTRAINT 外鍵名稱] FOREIGN KEY(從表外鍵字段) 
REFERENCES 主表名(主表主鍵字段)
[ON DELETE 級聯(lián)規(guī)則]
[ON UPDATE 級聯(lián)規(guī)則];

醫(yī)院場景實(shí)操示例

給已存在的醫(yī)生表，添加和科室表的外鍵約束

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

級聯(lián)規(guī)則詳解（ON DELETE/ON UPDATE）

這是主表數(shù)據(jù)被刪除/修改時(shí)，從表關(guān)聯(lián)數(shù)據(jù)的處理規(guī)則，核心常用3個(gè)：

?? 實(shí)操警告：生產(chǎn)環(huán)境盡量不要用CASCADE級聯(lián)刪除，萬一誤刪主表的一行數(shù)據(jù)，從表所有關(guān)聯(lián)數(shù)據(jù)都會被自動刪除，無法挽回。

6.4.2 刪除外鍵約束

核心語法

ALTER TABLE 從表名 DROP FOREIGN KEY 外鍵名稱;

實(shí)操示例

刪除醫(yī)生表的外鍵約束fk_emp_dpt

ALTER TABLE his_emp DROP FOREIGN KEY fk_emp_dpt;

實(shí)操理解&避坑指南

1. 外鍵約束是保證數(shù)據(jù)一致性的利器，但會降低插入、刪除、更新的性能，高并發(fā)場景要權(quán)衡使用；

2. 外鍵字段和主表的主鍵字段，數(shù)據(jù)類型必須完全一致（比如都是VARCHAR(10)），否則無法創(chuàng)建外鍵；

3. 主表的被引用字段，必須有主鍵約束或唯一約束，否則無法創(chuàng)建外鍵。

6.5 綜合動手實(shí)踐（醫(yī)院場景）

練習(xí)1：查詢每個(gè)科室的名稱，以及科室下的醫(yī)生人數(shù)

執(zhí)行SQL

SELECT b.dpt_name 科室名稱, COUNT(a.emp_id) 醫(yī)生人數(shù)
FROM his_dpt b LEFT JOIN his_emp a
ON b.dpt_code = a.dpt_code
GROUP BY b.dpt_code, b.dpt_name;

查詢結(jié)果表

練習(xí)2：查詢掛號費(fèi)最高的掛號記錄，對應(yīng)的患者姓名、接診醫(yī)生姓名、科室名稱

執(zhí)行SQL

SELECT 
  r.pat_name 患者姓名, 
  e.emp_name 接診醫(yī)生, 
  d.dpt_name 科室名稱, 
  r.reg_fee 掛號費(fèi)
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);

查詢結(jié)果表

本章核心總結(jié)

1. 聯(lián)合查詢UNION：縱向合并多個(gè)查詢結(jié)果，適合同結(jié)構(gòu)數(shù)據(jù)匯總，優(yōu)先用UNION ALL提升性能；

2. 連接查詢：橫向拼接多張表，內(nèi)連接只保留匹配數(shù)據(jù)，左外連接保留左表全量數(shù)據(jù)，是多表查詢的核心；

3. 子查詢：嵌套查詢解決復(fù)雜條件，標(biāo)量子查詢處理單值條件，EXISTS子查詢大數(shù)據(jù)量下性能更優(yōu)；

4. 外鍵約束：給關(guān)聯(lián)數(shù)據(jù)上鎖，保證數(shù)據(jù)一致性，生產(chǎn)環(huán)境優(yōu)先用默認(rèn)RESTRICT規(guī)則，慎用級聯(lián)刪除。

（）