1. 材料数据库结构设计与SQLite建库

为什么材料实验数据需要结构化建库

材料实验数据天然分散：成分记录在Excel、工艺参数写在实验本、结构表征结果存于仪器软件、性能测试数据又在另一张表。字段命名不统一（如"抗拉强度"与"σb"、"温度"与"T"）导致后续清洗和建模成本极高。本案例用SQLite建立一张材料实验主表，把成分、工艺、结构、性能四类字段统一到同一记录中，并通过约束保证数据质量。

建库SQL与约束设计

以下SQLite脚本完成建库、建表和约束设置。字段命名采用物理量_单位后缀（如 yield_strength_MPa），从命名层面消除单位歧义；数值字段用 CHECK 约束拦截明显异常值（如负强度、孔隙率超过100%）。

-- 1. 创建数据库并启用外键支持
PRAGMA foreign_keys = ON;

-- 2. 材料实验主表
CREATE TABLE IF NOT EXISTS materials (
    material_id          TEXT PRIMARY KEY,
    alloy_name           TEXT NOT NULL,
    fe_wt%               REAL CHECK (fe_wt% >= 0 AND fe_wt% <= 100),
    cr_wt%               REAL CHECK (cr_wt% >= 0 AND cr_wt% <= 100),
    ni_wt%               REAL CHECK (ni_wt% >= 0 AND ni_wt% <= 100),
    process_route        TEXT,
    solution_temp        REAL CHECK (solution_temp > 0),
    aging_temp           REAL CHECK (aging_temp > 0),
    aging_time_h         REAL CHECK (aging_time_h >= 0),
    cooling_rate         TEXT,
    phase                TEXT,
    grain_size_um        REAL CHECK (grain_size_um > 0),
    porosity_pct         REAL CHECK (porosity_pct >= 0 AND porosity_pct <= 100),
    texture              TEXT,
    yield_strength_MPa   REAL CHECK (yield_strength_MPa > 0),
    tensile_strength_MPa REAL CHECK (tensile_strength_MPa > 0),
    elongation_pct       REAL CHECK (elongation_pct >= 0),
    hardness_HV          REAL CHECK (hardness_HV > 0),
    test_temp_C          REAL,
    test_standard        TEXT,
    data_source          TEXT,
    created_at           TEXT DEFAULT (datetime('now'))
);

-- 3. 常用查询索引
CREATE INDEX IF NOT EXISTS idx_alloy ON materials(alloy_name);
CREATE INDEX IF NOT EXISTS idx_phase ON materials(phase);

样例数据导入与验证

插入3条教学示例数据（非实测值，仅用于演示字段完整性和约束效果），覆盖不同工艺路线与相组成。

INSERT INTO materials
(material_id, alloy_name, "fe_wt%", "cr_wt%", "ni_wt%",
 process_route, solution_temp, aging_temp, aging_time_h, cooling_rate,
 phase, grain_size_um, porosity_pct, texture,
 yield_strength_MPa, tensile_strength_MPa, elongation_pct, hardness_HV,
 test_temp_C, test_standard, data_source)
VALUES
('M001', '316L', 68.0, 17.0, 12.0,
 '固溶+时效', 1050, 650, 4.0, '水淬',
 '奥氏体', 25.0, 0.5, '随机',
 280, 580, 45.0, 180,
 25, 'GB/T 228.1', '实验记录A'),
('M002', '17-4PH', 75.0, 16.0, 4.0,
 '固溶+时效', 1040, 480, 1.0, '空冷',
 '马氏体+析出相', 15.0, 0.2, '随机',
 1100, 1200, 12.0, 380,
 25, 'GB/T 228.1', '实验记录B'),
('M003', '304', 70.0, 19.0, 9.0,
 '固溶', 1080, NULL, NULL, '水淬',
 '奥氏体', 30.0, 0.8, '随机',
 250, 520, 50.0, 160,
 25, 'GB/T 228.1', '实验记录C');

验证约束是否生效：尝试插入一条屈服强度为负的记录，应被 CHECK 拦截。

-- 预期报错：CHECK constraint failed
INSERT INTO materials (material_id, alloy_name, yield_strength_MPa)
VALUES ('M999', 'BadSample', -100);

查询验证：按相组成统计平均抗拉强度，确认数据可正常聚合。

SELECT phase,
       COUNT(*) AS n,
       ROUND(AVG(tensile_strength_MPa), 1) AS avg_uts
FROM materials
GROUP BY phase;

字段设计检查清单