智能家居数据库设计与实现：用户、设备、场景、定时任务等表结构示例

CREATE DATABASE smarthome; USE smarthome;

-- 用户表 CREATE TABLE userb ( UserID INT PRIMARY KEY, Username VARCHAR(50), Password VARCHAR(50), Email VARCHAR(100), Phone VARCHAR(15) ); INSERT INTO userb VALUES (1001, '李聪', 'password', '2602175453@qq.com', '2602175453'), (1002, '方强', 'password', '2488794964@qq.com', '2488794964'), (1003, '莫先杰', 'password', '3038895530@qq.com', '3038895530'), (1004, '巫锦生', 'password', '2631235361@qq.com', '2631235361'), (1005, '虞岚英', 'password', '3154868435@qq.com', '3154868435'), (1006, '谭桂华', 'password', '195121655@qq.com', '195121655'), (1007, '何彩英', 'password', '3381793379@qq.com', '3381793379'), (1008, '张三', 'password', 'as@example.com', '1234567890'), (1009, '李四', 'password', 'ls@example.com', '0987654321'), (1010, '赵六', 'password', 'zl@example.com', '9876543210');

-- 创建角色表 CREATE TABLE UserRole ( RoleID INT PRIMARY KEY, RoleName VARCHAR(50) ); INSERT INTO UserRole VALUES (1, '我'), (2, '父亲'), (3, '母亲'), (4, '儿子'), (5, '女儿'), (6, '祖父'), (7, '祖母'), (8, '外祖父'), (9, '外祖母'), (10, '叔叔'), (11, '阿姨');

-- 用户角色关联表 CREATE TABLE UserRoleMapping ( UserRoleMappingID INT PRIMARY KEY, UserID INT FOREIGN KEY REFERENCES userb(UserID), RoleID INT FOREIGN KEY REFERENCES UserRole(RoleID) ); INSERT INTO UserRoleMapping VALUES (1, 1002, 1), (2, 1002, 2), (3, 1003, 3), (4, 1004, 4), (5, 1005, 5), (6, 1006, 6), (7, 1007, 7), (8, 1008, 8), (9, 1009, 9), (10, 1010, 10);

-- 设备表 CREATE TABLE Device ( DeviceID INT PRIMARY KEY, DeviceName VARCHAR(50), DeviceType INT, UserID INT FOREIGN KEY REFERENCES userb(UserID), StatusID INT FOREIGN KEY REFERENCES DeviceStatus(StatusID) ); INSERT INTO Device VALUES (1, '灯光1', 1, 1001, 1), (2, '空调', 2, 1001, 2), (3, '窗帘', 3, 1002, 1), (4, '电视', 4, 1002, 2), (5, '音响', 5, 1001, 1), (6, '温湿度传感器', 6, 1003, 1), (7, '洗衣机', 7, 1006, 2), (8, '冰箱', 8, 1005, 1), (9, '烤箱', 9, 1005, 1), (10, '风扇', 1, 1003, 2);

-- 设备类型表 CREATE TABLE DeviceType ( DeviceTypeID INT PRIMARY KEY, DeviceTypeName VARCHAR(50) ); INSERT INTO DeviceType VALUES (1, '灯光'), (2, '空调'), (3, '窗帘'), (4, '电视'), (5, '音响'), (6, '温湿度传感器'), (7, '洗衣机'), (8, '冰箱'), (9, '烤箱'), (10, '风扇');

-- 设备状态表 CREATE TABLE DeviceStatus ( StatusID INT PRIMARY KEY, StatusName VARCHAR(50) ); INSERT INTO DeviceStatus VALUES (1, '开'), (2, '关'), (3, '运行中'), (4, '停止'), (5, '故障'), (6, '待机'), (7, '正常'), (8, '异常'), (9, '低电量'), (10, '高温警告');

-- 房间表 CREATE TABLE Room ( RoomID INT PRIMARY KEY, RoomName VARCHAR(50), UserID INT FOREIGN KEY REFERENCES userb(UserID) ); INSERT INTO Room VALUES (1, '客厅', 1001), (2, '卧室', 1001), (3, '厨房', 1002), (4, '餐厅', 1003), (5, '书房', 1006), (6, '浴室', 1005), (7, '儿童房', 1007), (8, '办公室', 1010), (9, '阳台', 1006), (10, '花园', 1004);

-- 房间设备关联表 CREATE TABLE RoomDevice ( RoomDeviceID INT PRIMARY KEY, RoomID INT FOREIGN KEY REFERENCES Room(RoomID), DeviceID INT FOREIGN KEY REFERENCES Device(DeviceID) ); INSERT INTO RoomDevice VALUES (1, 1, 1), (2, 1, 2), (3, 2, 3), (4, 2, 4), (5, 3, 5), (6, 3, 6), (7, 4, 7), (8, 4, 8), (9, 5, 9), (10, 5, 10);

-- 传感器数据表 CREATE TABLE SensorData ( DataID INT PRIMARY KEY, DeviceID INT FOREIGN KEY REFERENCES Device(DeviceID), SensorType VARCHAR(50), SensorValue FLOAT, Timestamp DATETIME ); INSERT INTO SensorData VALUES (1, 1, '温度', 25.5, GETDATE()), (2, 2, '湿度', 50.0, GETDATE()), (3, 3, '光照度', 500.0, GETDATE()), (4, 4, '二氧化碳浓度', 1000.0, GETDATE()), (5, 5, '噪音水平', 60.0, GETDATE()), (6, 6, '压力', 1001.2, GETDATE()), (7, 7, '电流', 2.5, GETDATE()), (8, 8, '电压', 220.0, GETDATE()), (9, 9, '湿度', 45.0, GETDATE()), (10, 10, '温度', 26.0, GETDATE());

-- 场景表 CREATE TABLE Scene ( SceneID INT PRIMARY KEY, SceneName VARCHAR(50), SceneDescription VARCHAR(100), UserID INT FOREIGN KEY REFERENCES userb(UserID) ); INSERT INTO Scene VALUES (1, '回家模式', '打开灯光、调节温度', 1005), (2, '离家模式', '关闭所有设备、锁门', 1001), (3, '晚餐模式', '打开餐厅灯光、播放音乐', 1003), (4, '睡眠模式', '关闭所有灯光、调整温度', 1001), (5, '读书模式', '调暗灯光、播放轻音乐', 1001), (6, '派对模式', '彩色灯光、高音量音乐', 1001), (7, '工作模式', '调亮办公室灯光、提醒休息', 1001), (8, '早晨模式', '打开窗帘、播放清新音乐', 1004), (9, '健身模式', '调整房间温度、开始计时', 1001), (10, '放松模式', '调暗灯光、播放自然声音', 1001);

-- 场景设备关联表 CREATE TABLE SceneDevice ( SceneDeviceID INT PRIMARY KEY, SceneID INT FOREIGN KEY REFERENCES Scene(SceneID), DeviceID INT FOREIGN KEY REFERENCES Device(DeviceID), StatusID INT FOREIGN KEY REFERENCES DeviceStatus(StatusID) ); INSERT INTO SceneDevice VALUES (1, 1, 1, 1), (2, 1, 2, 2), (3, 2, 3, 3), (4, 2, 4, 4), (5, 3, 5, 5), (6, 3, 6, 6), (7, 4, 7, 7), (8, 4, 8, 8), (9, 5, 9, 9), (10, 5, 10, 10);

-- 定时任务表 CREATE TABLE ScheduledTask ( TaskID INT PRIMARY KEY, TaskName VARCHAR(50), TaskDescription VARCHAR(100), ExecutionTime TIME, UserID INT FOREIGN KEY REFERENCES userb(UserID), DeviceID INT FOREIGN KEY REFERENCES Device(DeviceID), StatusID INT FOREIGN KEY REFERENCES DeviceStatus(StatusID) ); INSERT INTO ScheduledTask VALUES (1, '早晨闹钟', '每天早上7点的闹钟', '07:00:00', 1001, 1, 1), (2, '晚餐提醒', '每天晚上6点的用餐提醒', '18:00:00', 1001, 2, 1), (3, '空调定时开关', '每天下午3点打开空调', '15:00:00', 1002, 3, 1), (4, '音乐播放器', '每天晚上8点开始播放音乐', '20:00:00', 1003, 4, 3), (5, '温度调节', '每周五晚上9点调高温度', '21:00:00', 1004, 5, 5), (6, '照明控制', '每天晚上10点关闭灯光', '22:00:00', 1005, 6, 9), (7, '水泵定时', '每天中午12点启动水泵', '12:00:00', 1006, 7, 4), (8, '净化器定时', '每天上午9点启动净化器', '09:00:00', 1007, 8, 6), (9, '冷冻模式', '每周一至周五下午4点启动冷冻模式', '16:00:00', 1006, 9, 2), (10, '风扇定时', '每天晚上11点关闭风扇', '23:00:00', 1004, 10, 8);

-- 消费记录表 CREATE TABLE ExpenseRecord ( RecordID INT PRIMARY KEY, UserID INT FOREIGN KEY REFERENCES userb(UserID), Amount DECIMAL(10, 2), ExpenseTime DATETIME ); INSERT INTO ExpenseRecord VALUES (1, 1001, 100.00, '2022-01-01 09:30:00'), (2, 1002, 50.00, '2022-02-05 14:15:00'), (3, 1003, 80.00, '2022-02-10 18:45:00'), (4, 1004, 120.50, '2022-03-03 11:20:00'), (5, 1005, 200.00, '2022-04-15 16:30:00'), (6, 1006, 75.80, '2022-05-20 10:00:00'), (7, 1007, 150.00, '2022-06-08 13:45:00'), (8, 1008, 60.00, '2022-07-22 19:00:00'), (9, 1009, 90.50, '2022-08-12 15:10:00'), (10, 1010, 180.00, '2022-09-30 12:30:00');

-- 家庭成员表 CREATE TABLE FamilyMember ( MemberID INT PRIMARY KEY, MemberName VARCHAR(50), Relationship VARCHAR(50), UserID INT FOREIGN KEY REFERENCES userb(UserID) ); INSERT INTO FamilyMember VALUES (2, '李四', '母亲', 1002), (3, '王五', '儿子', 1003), (4, '赵六', '女儿', 1002), (5, '小明', '儿子', 1003), (6, '小红', '女儿', 1003), (7, '刘备', '父亲', 1004), (8, '关羽', '母亲', 1004), (9, '张飞', '儿子', 1005), (10, '诸葛亮', '妻子', 1005);

-- 家庭成员设备权限表 CREATE TABLE MemberDevicePermission ( PermissionID INT PRIMARY KEY, MemberID INT FOREIGN KEY REFERENCES FamilyMember(MemberID), DeviceID INT FOREIGN KEY REFERENCES Device(DeviceID), HasPermission BIT ); INSERT INTO MemberDevicePermission VALUES (4, 2, 3, 1), (5, 3, 2, 1), (6, 4, 1, 0), (7, 4, 4, 1), (8, 5, 3, 1), (9, 5, 5, 0), (10, 6, 2, 1);

-- 用户角色视图 CREATE VIEW vw_UserRole AS SELECT u.UserID, u.Username, r.RoleName FROM userb u JOIN UserRoleMapping urm ON u.UserID = urm.UserID JOIN UserRole r ON urm.RoleID = r.RoleID;

-- 设备房间视图 CREATE VIEW vw_DeviceRoom AS SELECT d.DeviceID, d.DeviceName, d.DeviceType, r.RoomName FROM Device d JOIN RoomDevice rd ON d.DeviceID = rd.DeviceID JOIN Room r ON rd.RoomID = r.RoomID;

-- 设备传感器数据视图 CREATE VIEW vw_DeviceSensorData AS SELECT d.DeviceName, s.SensorType, s.SensorValue, s.Timestamp FROM Device d JOIN SensorData s ON d.DeviceID = s.DeviceID;

-- 场景设备视图 CREATE VIEW vw_SceneDevice AS SELECT s.SceneName, d.DeviceName, ds.StatusName FROM Scene s JOIN SceneDevice sd ON s.SceneID = sd.SceneID JOIN Device d ON sd.DeviceID = d.DeviceID JOIN DeviceStatus ds ON sd.StatusID = ds.StatusID;

-- 定时任务设备视图 CREATE VIEW vw_ScheduledTaskDevice AS SELECT t.TaskName, d.DeviceName, ds.StatusName FROM ScheduledTask t JOIN Device d ON t.DeviceID = d.DeviceID JOIN DeviceStatus ds ON t.StatusID = ds.StatusID;