智能家居数据库设计及视图创建 SQL 代码示例

{ "title": "智能家居数据库设计及视图创建 SQL 代码示例", "description": "本文提供了智能家居数据库设计 SQL 代码示例，包括用户、设备、场景、定时任务等多个表，并创建了用户角色、设备状态、家庭成员设备权限、场景设备等视图，方便查询和管理数据。", "keywords": "智能家居, 数据库设计, SQL, 视图, 用户, 设备, 场景, 定时任务, 角色, 状态, 权限", "content": "```sql create database smarthome; use smarthome; --用户表 create table userb ( UserID int primary key,--用户ID Username varchar(50),--用户名 Password varchar(50),--密码 Email varchar(100),--电子邮件 Phone varchar(15)--手机号码 ); insert into userb values (10011, '李聪', '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, -- 角色ID 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, -- 用户角色关联ID UserID int foreign key references userb(UserID), -- 用户ID 外键关联到用户表 RoleID int foreign key references UserRole(RoleID), -- 角色ID 外键关联到角色表 ); 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,--设备ID DeviceName varchar(50),--设备名称 DeviceType int,--设备类型 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 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,--设备类型ID DeviceTypeName varchar(50)--设备类型名称 ); insert into DeviceType values(1, '灯光'),(2, '空调'),(3, '窗帘'),(4, '电视'),(5, '音响'), (6, '温湿度传感器'),(7, '洗衣机'),(8, '冰箱'),(9, '烤箱'),(10, '风扇')

--设备状态表 create table DeviceStatus ( StatusID int primary key,--设备状态ID StatusName varchar(50)--设备状态名称 ); insert into DeviceStatus values (1, '开'),(2, '关'),(3, '运行中'),(4, '停止'), (5, '故障'),(6, '待机'),(7, '正常'),(8, '异常'),(9, '低电量'),(10, '高温警告')

--房间表 create table Room ( RoomID int primary key,--房间ID RoomName varchar(50),--房间名称 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 ); 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,--房间设备关联ID RoomID int foreign key references Room(RoomID),--房间ID 外键关联到房间表 DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 ); 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,--传感器数据ID DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 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,--场景ID SceneName varchar(50),--场景名称 SceneDescription varchar(100),--场景描述 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 ); 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,--场景设备关联ID SceneID int foreign key references Scene(SceneID),--场景ID 外键关联到场景表 DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 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,--任务ID TaskName varchar(50),--任务名称 TaskDescription varchar(100),--任务描述 ExecutionTime time,--执行时间 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 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, -- 记录ID UserID int foreign key references userb(UserID), -- 用户ID 外键关联到用户表 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, -- 成员ID MemberName varchar(50), -- 成员姓名 Relationship varchar(50), -- 成员关系 UserID int foreign key references userb(UserID), -- 所属用户ID 外键关联到用户表 ); 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, -- 权限ID MemberID int foreign key references FamilyMember(MemberID), -- 成员ID 外键关联到家庭成员表 DeviceID int foreign key references Device(DeviceID), -- 设备ID 外键关联到设备表 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)

-- 为以上数据表创建所需要的视图：

-- 1. 用户角色视图：显示用户及其对应的角色信息

create view UserRolesView as
select u.UserID, u.Username, r.RoleName
from userb u
join UserRoleMapping m on u.UserID = m.UserID
join UserRole r on m.RoleID = r.RoleID;

-- 2. 设备状态视图：显示设备及其对应的状态信息

create view DeviceStatusView as
select d.DeviceID, d.DeviceName, s.StatusName
from Device d
join DeviceStatus s on d.StatusID = s.StatusID;

-- 3. 家庭成员设备权限视图：显示家庭成员及其对应的设备权限信息

create view MemberDevicePermissionView as
select m.MemberID, m.MemberName, d.DeviceName, p.HasPermission
from FamilyMember m
join MemberDevicePermission p on m.MemberID = p.MemberID
join Device d on p.DeviceID = d.DeviceID;

-- 4. 场景设备视图：显示场景及其对应的设备信息

create view SceneDeviceView as
select s.SceneID, s.SceneName, d.DeviceName, st.StatusName
from Scene s
join SceneDevice sd on s.SceneID = sd.SceneID
join Device d on sd.DeviceID = d.DeviceID
join DeviceStatus st on sd.StatusID = st.StatusID;

-- 5. 设备类型视图：显示设备类型及其对应的设备信息

create view DeviceTypeView as
select dt.DeviceTypeID, dt.DeviceTypeName, d.DeviceID, d.DeviceName
from DeviceType dt
join Device d on dt.DeviceTypeID = d.DeviceType;

-- 6. 房间设备视图：显示房间及其对应的设备信息

create view RoomDeviceView as
select r.RoomID, r.RoomName, d.DeviceID, d.DeviceName
from Room r
join RoomDevice rd on r.RoomID = rd.RoomID
join Device d on rd.DeviceID = d.DeviceID;

-- 7. 定时任务视图：显示定时任务及其对应的设备信息

create view ScheduledTaskView as
select t.TaskID, t.TaskName, t.TaskDescription, t.ExecutionTime, t.UserID, d.DeviceID, d.DeviceName, s.StatusID, s.StatusName
from ScheduledTask t
join Device d on t.DeviceID = d.DeviceID
join DeviceStatus s on t.StatusID = s.StatusID;

-- 8. 消费记录视图：显示消费记录及其对应的用户信息

create view ExpenseRecordView as
select e.RecordID, e.UserID, u.Username, e.Amount, e.ExpenseTime
from ExpenseRecord e
join userb u on e.UserID = u.UserID;

-- 9. 家庭成员视图：显示家庭成员及其对应的用户信息

create view FamilyMemberView as
select f.MemberID, f.MemberName, f.Relationship, u.UserID, u.Username
from FamilyMember f
join userb u on f.UserID = u.UserID;

-- 10. 场景用户视图：显示场景及其对应的用户信息

create view SceneUserView as
select s.SceneID, s.SceneName, s.SceneDescription, u.UserID, u.Username
from Scene s
join userb u on s.UserID = u.UserID;