Part 2 - 物联网云平台总体分析与设计

2022-02-21
5分钟阅读时长

【版本】

当前版本号v20230113

版本修改说明
v20230113修改数据表设计
v20220313初始化

任务2.1 智能锁数据库设计

【任务目的】

  • 掌握 SQL 的使用,包括创建表和增删查改
  • 掌握数据库总体设计与分析

【任务环境】

  • MairaDB 10.4
  • HeidiSQL

【任务要求】

  1. 打开 MariaDB 客户端 HeidiSQL,使用 root 账户登录,并创建一个库iotlock

参考SQL 的 create database 语句

密码表 pwd

  1. 设计一个保存智能锁密码的表,要求如下:
  • (1) 密码分为2种类型,一种是固定密码,另一种是临时密码。
  • (2) 密码要求是纯数字,大于等于6位,小于等于18位。
  • (3) 固定密码不会过期,用户可以修改。
  • (4) 临时密码有有效期,在有效期内仅能解锁一次,解锁以后失效。
  • (5) 密码表pwd列说明
说明允许为NULL
id行编号,数字自增N
typ密码类型,固定-fixed,临时-tempN
pwd密码,长度不超过18位N
status状态,启用-enabled,禁用-disabledN
upt_time更新时间N
expired_time过期时间Y

参考SQL 的 create table 语句

请根据以上说明,按要求完成创建密码表pwd的SQL语句的编写。

  1. 【练习】请在以下输入框内执行 SQL 语句查询所有 pwd 表内的数据。

参考SQL 的 select 语句

  1. 【练习】按以下表要求编写 SQL 语句,往密码表pwd插入一条固定密码。

参考SQL 的 insert 语句

idtyppwdstatusupt_timeexpired_time
3fixed123456enabled当前时间NULL
  1. 【练习】按以下表要求编写 SQL 语句,往密码表pwd插入一条临时密码。

关于时间和日期的计算函数,可以参考DATE_ADD 函数

idtyppwdstatusupt_timeexpired_time
4temp000000enabled当前时间当前时间+30分钟
  1. 【练习】编写 SQL 语句,把固定密码为123456修改为654321,同时修改更新时间 upt_time为当前时间。

参考SQL 的 update 语句

  1. 【练习】编写 SQL 语句,把临时密码为000000的状态修改为disabled

参考SQL 的 update 语句

任务2.2 物联网云平台数据库总体设计

【任务目的】

  • 掌握 SQL 的使用,包括创建表和增删查改
  • 掌握数据库总体设计与分析

【任务环境】

  • MairaDB
  • HeidiSQL

【任务要求】

  1. 在 MariaDB 创建一个库iotcloud

用户表 user

  1. 设计一个保存用户信息的表,要求如下:
  • (1) 用户表user列说明
说明允许为NULL
id行编号,数字自增N
user_id用户ID,长度32字符N
pwd用户密码,长度32字符N
user_name用户姓名,长度64字符N
user_secret用户密钥,长度128字符N
avatar用户头像路径,长度255字符Y
  • 请根据以上说明,按要求完成创建用户表user的SQL语句的编写。

设备表 device

  1. 设计一个保存设备信息的表,要求如下
  • (1) 设备表device列说明
说明允许为NULL
id行编号,数字自增N
iot_id物联网设备ID,长度32字符N
dev_name设备名称,长度32字符N
user_id拥有者用户ID,长度32字符N
dev_type设备类型,长度32字符N
status设备状态,状态,启用-enabled,禁用-disabled,长度16字符N
dev_secret设备密钥,长度128字符N
description描述,长度256字符N
create_time创建时间N
  • (2)请根据以上说明,按要求完成创建用户表device的SQL语句的编写。
  1. 往上面创建的2个表插入一条数据,用户ID都设置为zhangsan

  2. 编写 SQL 查询用户IDzhangsan的所有设备,按创建时间降序排列。需要展示列包括用户ID用户姓名设备名称设备状态创建时间

参考表连接

扫码或长按识别访问