下面我将为 **智慧养老院管理系统** 的每个核心表插入 **10 条测试数据**,确保数据合理、业务逻辑清晰,并保持与你要求一致:
- `elderly_profiles` 表结构不变
- 不使用紧急联系人表(仅用 `emergency_contact_phone` 字段)
---
## ✅ 数据生成说明
- 使用标准 SQL `INSERT INTO ... VALUES` 语法
- 时间字段使用当前或近似时间(如 `'2024-01-01'`)
- 所有外键关系正确对齐(例如床位绑定入住记录)
- 老人 ID 从 1 开始连续编号
- 床位按 A栋3楼 → B栋4楼分布
- 费用项目包含常见收费项
- 支持后续查询和账单生成
---
### ✅ 1. `elderly_profiles` 老人档案表(10条)
```sql
INSERT INTO elderly_profiles (name, id_card, phone, emergency_contact_phone, health_history, allergy_history) VALUES
('张三', '11010119500101001X', '13800000001', '13900000001', '高血压', '青霉素'),
('李四', '110101195202020028', '13800000002', '13900000002', '糖尿病', NULL),
('王五', '110101194803030037', '13800000003', '13900000003', '冠心病', '磺胺类药物'),
('赵六', '110101195504040046', '13800000004', '13900000004', '慢性支气管炎', NULL),
('钱七', '110101195305050055', '13800000005', '13900000005', '关节炎', '头孢'),
('孙八', '110101194906060064', '13800000006', '13900000006', '无重大病史', NULL),
('周九', '110101195107070073', '13800000007', '13900000007', '哮喘', '阿司匹林'),
('吴十', '110101195408080082', '13800000008', '13900000008', '脑梗后遗症', NULL),
('郑一', '110101195609090091', '13800000009', '13900000009', '帕金森', '无过敏'),
('陈二', '110101194710100100', '13800000010', '13900000010', '心脏病', '红霉素');
```
---
### ✅ 2. `beds` 床位信息表(10条)
```sql
INSERT INTO beds (building, floor_num, room_number, bed_number, type, status, elderly_id) VALUES
('A栋', 3, '301', '1床', 'normal', 'occupied', 1),
('A栋', 3, '301', '2床', 'normal', 'free', NULL),
('A栋', 3, '302', '1床', 'nursing', 'occupied', 2),
('A栋', 3, '302', '2床', 'nursing', 'maintenance', NULL),
('A栋', 3, '303', '1床', 'special', 'occupied', 3),
('B栋', 4, '401', '1床', 'normal', 'occupied', 4),
('B栋', 4, '401', '2床', 'normal', 'free', NULL),
('B栋', 4, '402', '1床', 'nursing', 'occupied', 5),
('B栋', 4, '403', '1床', 'normal', 'free', NULL),
('B栋', 4, '403', '2床', 'normal', 'occupied', 6);
```
---
### ✅ 3. `check_ins` 入住记录表(对应在住的老人:1,2,3,4,5,6)
```sql
INSERT INTO check_ins (elderly_id, bed_id, deposit_amount, contract_file_url, status) VALUES
(1, 1, 2000.00, '/contract/2024/elderly_1.pdf', 'active'),
(2, 3, 3000.00, '/contract/2024/elderly_2.pdf', 'active'),
(3, 5, 5000.00, '/contract/2024/elderly_3.pdf', 'active'),
(4, 6, 2000.00, '/contract/2024/elderly_4.pdf', 'active'),
(5, 8, 3000.00, '/contract/2024/elderly_5.pdf', 'active'),
(6, 10, 2000.00, '/contract/2024/elderly_6.pdf', 'active'),
(7, NULL, 0.00, NULL, 'checked_out'), -- 曾入住但退房
(8, NULL, 0.00, NULL, 'reserved'), -- 预约未入
(9, NULL, 0.00, NULL, 'reserved'),
(10, NULL, 0.00, NULL, 'out');
```
> 📌 只有 `status = 'active'` 的才算当前在住。
---
### ✅ 4. `fee_items` 费用项目表(10个常见收费项)
```sql
INSERT INTO fee_items (item_name, amount, unit, status) VALUES
('床位费', 2000.00, '月', 1),
('护理费', 3000.00, '月', 1),
('特级护理费', 6000.00, '月', 1),
('伙食费', 800.00, '月', 1),
('取暖费', 500.00, '季', 1),
('空调费', 300.00, '月', 1),
('水电公摊', 100.00, '月', 1),
('健康监测服务', 200.00, '月', 1),
('心理辅导', 150.00, '次', 1),
('家属探视停车费', 50.00, '次', 0); -- 已停用
```
---
### ✅ 5. `bills` 账单表(为前6位在住老人生成部分账单)
```sql
-- 假设当前是 2025 年 3 月,生成 2 月份账单
INSERT INTO bills (elderly_id, item_id, item_name, amount, paid_amount, status, bill_month) VALUES
(1, 1, '床位费', 2000.00, 2000.00, 'paid', '2025-02-01'),
(1, 4, '伙食费', 800.00, 800.00, 'paid', '2025-02-01'),
(2, 2, '护理费', 3000.00, 3000.00, 'paid', '2025-02-01'),
(2, 1, '床位费', 2000.00, 2000.00, 'paid', '2025-02-01'),
(3, 3, '特级护理费', 6000.00, 6000.00, 'paid', '2025-02-01'),
(4, 1, '床位费', 2000.00, 1000.00, 'partial', '2025-02-01'),
(5, 2, '护理费', 3000.00, 0.00, 'unpaid', '2025-02-01'),
(5, 1, '床位费', 2000.00, 0.00, 'unpaid', '2025-02-01'),
(6, 1, '床位费', 2000.00, 2000.00, 'paid', '2025-02-01'),
(6, 4, '伙食费', 800.00, 800.00, 'paid', '2025-02-01');
```
---
### ✅ 6. `payments` 缴费记录表(对应已支付的账单)
```sql
INSERT INTO payments (bill_id, elderly_id, pay_amount, pay_method, receipt_no, operator, remark) VALUES
(1, 1, 2000.00, 'wechat', 'REC20250201001', '财务员小李', '2月床位费'),
(2, 1, 800.00, 'cash', 'REC20250201002', '财务员小李', '2月伙食费'),
(3, 2, 3000.00, 'alipay', 'REC20250201003', '财务员小李', '2月护理费'),
(4, 2, 2000.00, 'bank', 'REC20250201004', '财务员小李', '2月床位费'),
(5, 3, 6000.00, 'wechat', 'REC20250201005', '财务员小李', '2月特护费'),
(8, 5, 1500.00, 'cash', 'REC20250201006', '财务员小李', '部分付款'),
(9, 6, 2000.00, 'wechat', 'REC20250201007', '财务员小李', '2月床位费'),
(10, 6, 800.00, 'alipay', 'REC20250201008', '财务员小李', '2月伙食费'),
(1, 1, 500.00, 'cash', 'REC20250201009', '财务员小李', '补缴差额'),
(4, 2, 500.00, 'wechat', 'REC20250201010', '财务员小李', '额外支付');
```
---
### ✅ 7. `health_records` 健康记录表(每人选几天记录)
```sql
INSERT INTO health_records (elderly_id, record_date, temperature, blood_pressure, heart_rate, weight, recorder) VALUES
(1, '2025-03-01', 36.5, '130/85', 78, 68.5, '护理员小张'),
(1, '2025-03-02', 36.6, '128/84', 76, 68.4, '护理员小张'),
(2, '2025-03-01', 36.7, '140/90', 80, 72.0, '护理员小王'),
(3, '2025-03-01', 36.4, '135/88', 75, 65.0, '护理员小刘'),
(4, '2025-03-01', 36.8, '125/80', 74, 70.2, '护理员小赵'),
(5, '2025-03-01', 36.9, '145/92', 82, 69.8, '护理员小钱'),
(6, '2025-03-01', 36.6, '132/86', 77, 71.0, '护理员小孙'),
(7, '2025-03-01', 36.5, '120/78', 73, 66.5, '护理员小周'),
(8, '2025-03-01', 37.0, '150/95', 85, 73.0, '护理员小吴'),
(9, '2025-03-01', 36.7, '138/89', 79, 64.0, '护理员小郑');
```
---
### ✅ 8. `care_records` 护理执行记录表(随机护理任务)
```sql
INSERT INTO care_records (elderly_id, care_item, execute_time, duration_minutes, note, operator) VALUES
(1, '喂药', '2025-03-01 08:00:00', 10, '按时服用降压药', '护理员小张'),
(1, '翻身', '2025-03-01 14:00:00', 5, '防止褥疮', '护理员小张'),
(2, '洗澡', '2025-03-01 10:30:00', 30, '水温适宜,无不适', '护理员小王'),
(3, '康复训练', '2025-03-01 09:00:00', 45, '完成上肢活动训练', '护理员小刘'),
(4, '喂饭', '2025-03-01 12:00:00', 20, '进食顺利', '护理员小赵'),
(5, '测量血压', '2025-03-01 07:00:00', 5, '偏高,已提醒医生', '护理员小钱'),
(6, '心理疏导', '2025-03-01 15:00:00', 25, '情绪稳定', '心理咨询师李老师'),
(7, '换药', '2025-03-01 10:00:00', 15, '伤口愈合良好', '护士小陈'),
(8, '夜间巡视', '2025-03-01 22:00:00', 3, '睡眠中', '夜班护理员小黄'),
(9, '协助如厕', '2025-03-01 06:30:00', 10, '行动不便需搀扶', '护理员小徐');
```
---
### ✅ 9. `incident_records` 事件记录表(部分突发情况)
```sql
INSERT INTO incident_records (elderly_id, incident_type, occur_time, description, handler, handle_result, image_urls) VALUES
(2, 'fall', '2025-02-15 10:20:00', '在卫生间滑倒,轻微擦伤', '护理员小王', '消毒包扎,观察24小时无异常', '["/img/incident_1.jpg"]'),
(5, 'illness', '2025-02-20 03:30:00', '突发胸闷、呼吸困难', '值班医生刘主任', '吸氧并送医检查,诊断为心绞痛', '["/img/incident_2_1.jpg", "/img/incident_2_2.jpg"]'),
(3, 'conflict', '2025-02-25 16:00:00', '与其他老人因电视音量发生争执', '主管张经理', '调解后双方和解', NULL),
(7, 'fall', '2025-01-10 09:15:00', '起床时跌倒,腿部疼痛', '护理员小陈', '拍片排除骨折,建议卧床休息', '/img/incident_4.jpg'),
(1, 'illness', '2025-03-01 11:00:00', '头晕恶心,测量血压偏高', '医生王大夫', '调整用药,密切监测', NULL),
(4, 'other', '2025-02-18 13:20:00', '家属送来贵重物品寄存', '前台小林', '已登记并放入保险柜', NULL),
(6, 'illness', '2025-02-22 08:45:00', '早餐后呕吐,怀疑食物不适', '护士小周', '禁食观察,通知家属', NULL),
(8, 'fall', '2025-01-05 21:10:00', '夜间起夜摔倒', '夜班护理员小黄', 'CT检查无骨折,继续观察', '/img/incident_8.jpg'),
(9, 'other', '2025-02-14 15:30:00', '收到匿名捐赠物资一批', '院长办公室', '清点入库,公示感谢', NULL),
(10, 'conflict', '2025-01-30 17:00:00', '护工态度问题引发投诉', '人事部', '批评教育并调岗', NULL);
```
---
## ✅ 如何导入这些数据?
你可以将以上所有 `INSERT` 语句保存为一个 `.sql` 文件,例如:
```bash
init_data.sql
```
然后通过命令行或图形工具执行:
```bash
mysql -u root -p your_database < init_data.sql
```
或者在 Navicat / DBeaver 等工具中直接运行。
---
## ✅ 数据验证建议
| 验证点 | SQL 示例 |
|--------|---------|
| 查看在住老人及房间 | `SELECT ep.name, b.room_number, b.bed_number FROM elderly_profiles ep JOIN check_ins ci ON ep.id=ci.elderly_id AND ci.status='active' JOIN beds b ON ci.bed_id=b.id;` |
| 统计各状态床位数 | `SELECT status, COUNT(*) FROM beds GROUP BY status;` |
| 查询某人缴费记录 | `SELECT * FROM payments WHERE elderly_id = 1;` |
---