MySQL多对一（Many to one）关系：护士与患者表关联方法问询

嘿，作为MySQL新手能想到用关联表来处理护士和患者的对应关系，已经迈出很关键的一步啦！针对你说的2名护士负责6名患者的场景，咱们可以通过主表（护士表）+ 从表（患者表）+ 外键约束来实现多对一关联，具体步骤如下：

1. 先创建「一」的一方：护士表 #

护士是“一对多”里的“一”，先建护士表，用唯一主键标识每个护士，比如nurse_id：

CREATE TABLE nurses (
    nurse_id INT AUTO_INCREMENT PRIMARY KEY,  -- 护士ID，自增主键，唯一标识每个护士
    nurse_name VARCHAR(50) NOT NULL,          -- 护士姓名
    nurse_employee_no VARCHAR(20) UNIQUE     -- 护士工号，保证唯一
);

-- 插入2名护士的示例数据
INSERT INTO nurses (nurse_name, nurse_employee_no)
VALUES 
('张护士', 'NURSE001'),
('李护士', 'NURSE002');

2. 再创建「多」的一方：患者表 #

患者是“多”的一方，每个患者对应一名护士，所以在患者表里加nurse_id字段，用它关联护士表的主键（这就是外键）：

CREATE TABLE patients (
    patient_id INT AUTO_INCREMENT PRIMARY KEY,  -- 患者ID，自增主键
    patient_name VARCHAR(50) NOT NULL,          -- 患者姓名
    patient_age INT,                            -- 患者年龄
    nurse_id INT,                               -- 关联护士的ID
    -- 外键约束：保证patient里的nurse_id必须存在于nurses表中
    FOREIGN KEY (nurse_id) REFERENCES nurses(nurse_id)
        ON DELETE SET NULL  -- 如果护士被删除，患者的nurse_id设为NULL（也可用ON DELETE RESTRICT禁止删除有患者的护士）
        ON UPDATE CASCADE   -- 如果护士的nurse_id更新，患者的对应字段自动同步
);

-- 插入6名患者的示例数据，分配给两位护士
INSERT INTO patients (patient_name, patient_age, nurse_id)
VALUES 
('王患者', 45, 1),
('赵患者', 32, 1),
('孙患者', 58, 1),
('周患者', 27, 2),
('吴患者', 62, 2),
('郑患者', 39, 2);

3. 关联查询示例 #

表建好后，若想查询某护士负责的所有患者，用JOIN语句就能轻松实现：

-- 查询张护士负责的所有患者
SELECT n.nurse_name, p.patient_name, p.patient_age
FROM nurses n
JOIN patients p ON n.nurse_id = p.nurse_id
WHERE n.nurse_name = '张护士';

关键知识点说明 #

• 外键的核心作用：确保数据完整性，比如你不能给患者分配一个不存在的护士ID，避免出现脏数据。
• 外键行为可自定义：ON DELETE SET NULL和ON UPDATE CASCADE可以根据业务需求调整，比如如果不允许删除仍有患者的护士，可改成ON DELETE RESTRICT。

内容的提问来源于stack exchange，提问作者Vig Killa