【版本】
当前版本号v20230113
| 版本 | 修改说明 |
|---|---|
| v20230113 | 修改数据表设计 |
| v20220313 | 初始化 |
【实验名称】2.1 智能锁数据库设计
【实验目的】
- 掌握 SQL 的使用，包括创建表和增删查改
- 掌握数据库总体设计与分析
【实验环境】
- MairaDB 10.4
- HeidiSQL
【实验要求】
- 打开 MariaDB 客户端 HeidiSQL，使用 root 账户登录，并创建一个库
iotlock。
密码表 pwd
- 设计一个保存智能锁密码的表，要求如下:
- (1) 密码分为2种类型，一种是固定密码，另一种是临时密码。
- (2) 密码要求是纯数字，大于等于6位，小于等于18位。
- (3) 固定密码不会过期，用户可以修改。
- (4) 临时密码有有效期，在有效期内仅能解锁一次，解锁以后失效。
- (5) 密码表
pwd列说明
| 列 | 说明 | 允许为NULL |
|---|---|---|
| id | 行编号，数字自增 | N |
| typ | 密码类型，固定-fixed，临时-temp | N |
| pwd | 密码，长度不超过18位 | N |
| status | 状态，启用-enabled，禁用-disabled | N |
| upt_time | 更新时间 | N |
| expired_time | 过期时间 | Y |
请根据以上说明，按要求完成创建密码表pwd的SQL语句的编写。
- 【练习】请在以下输入框内执行 SQL 语句查询所有 pwd 表内的数据。
- 【练习】按以下表要求编写 SQL 语句，往密码表
pwd插入一条固定密码。
| id | typ | pwd | status | upt_time | expired_time |
|---|---|---|---|---|---|
| 3 | fixed | 123456 | enabled | 当前时间 | NULL |
- 【练习】按以下表要求编写 SQL 语句，往密码表
pwd插入一条临时密码。
关于时间和日期的计算函数，可以参考DATE_ADD 函数
| id | typ | pwd | status | upt_time | expired_time |
|---|---|---|---|---|---|
| 4 | temp | 000000 | enabled | 当前时间 | 当前时间+30分钟 |
- 【练习】编写 SQL 语句，把固定密码为
123456修改为654321，同时修改更新时间 upt_time为当前时间。
- 【练习】编写 SQL 语句，把临时密码为
000000的状态修改为disabled。
【实验名称】2.2 物联网云平台数据库总体设计
【实验目的】
- 掌握 SQL 的使用，包括创建表和增删查改
- 掌握数据库总体设计与分析
【实验环境】
- MairaDB
- HeidiSQL
【实验要求】
- 在 MariaDB 创建一个库
iotcloud
用户表 user
- 设计一个保存用户信息的表，要求如下:
- (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) 设备表
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语句的编写。
往上面创建的2个表插入一条数据，用户ID都设置为
zhangsan。编写 SQL 查询用户ID
zhangsan的所有设备，按创建时间降序排列。需要展示列包括用户ID，用户姓名，设备名称，设备状态，创建时间。
参考表连接
