MySQL考核习题设计（1-5）

最新推荐文章于 2024-10-08 10:01:21 发布

A123456789011223

最新推荐文章于 2024-10-08 10:01:21 发布

阅读量1k

点赞数 25

文章标签： oracle 数据库 sql sqlite mysql

本文链接：https://blog.csdn.net/A123456789011223/article/details/142029382

版权

1.2. 用户与权限管理：
- 创建一个新的MySQL用户，命令如下：
用户名为new_user，密码为password123。
仅允许该用户访问blockchain_db数据库，并赋予SELECT、INSERT、UPDATE、DELETE操作权限。
- 验证该用户只能在blockchain_db中执行操作，展示成功执行的SQL语句和操作结果。

创建用户名：

创建数据库

赋予权限：

第2题：数据库表结构设计

1. 交易表设计：
- 设计一个区块链交易表，要求包含以下字段：
  - 交易ID：UUID类型，主键。
  - 交易金额：浮点数类型，必须大于0，并设置非负约束。
  - 交易时间：日期时间类型，默认值为当前时间戳。
  - 交易类型：枚举类型，取值为“收入”或“支出”。
  - 交易状态：默认为“待处理”，取值范围为“待处理”、“完成”、“失败”。
  - 备注：字符串类型，允许为空，最大长度为255字符。
- 请展示创建该表的SQL语句，确保字段设计满足数据一致性要求，并截图展示表结构。

2. 复杂约束条件设置：
- 为交易金额字段设置CHECK约束，确保金额为正数。
- 为交易ID字段创建唯一约束，防止重复交易。

TransactionID 字段使用 CHAR(36) 而不是 UUID 类型，因为MySQL没有内置的UUID类型。通常，UUID以字符串形式存储，长度为36个字符（包括4个短横线）。
TransactionAmount 字段使用 DECIMAL(10, 2) 类型，其中10是总位数（包括小数点两侧），2是小数位数。这允许存储最大为999,999,999.99的金额。
CHECK (TransactionAmount > 0) 约束确保交易金额始终为正数。
TransactionTime 字段使用 DATETIME 类型，并默认设置为当前时间戳。
TransactionType 和 TransactionStatus 字段使用 ENUM 类型，限制只能为指定的值。
Remark 字段为可选的字符串，最大长度为255字符。
PRIMARY KEY (TransactionID) 和 UNIQUE KEY unique_transaction_id (TransactionID) 实际上做了相同的事情，即确保 TransactionID 的唯一性。在大多数情况下，仅使用主键约束就足够了。

展示表结构：SHOW COLUMNS FROM BlockchainTransactions;

第3题：数据插入与更新操作

1. 插入复杂交易数据：
- 使用SQL语句向交易表中插入5条交易记录，要求每条记录具备如下特征：
  - 交易ID为自动生成的UUID。
  - 一条收入交易的金额为500，交易状态为“完成”，备注为空。
  - 两条支出交易，交易状态为“待处理”，备注中包含中文字符。
  - 一条交易金额为负数的支出交易，尝试插入后验证失败，并记录错误信息。
  - 一条交易备注长度超过255字符，尝试插入后验证失败，并记录错误信息。
- 提交成功插入数据的截图，并说明失败的插入原因。

2. 更新交易记录
- 更新交易ID为某条记录的交易状态为“完成”，并自动更新交易时间为当前时间，验证更新成功。

失败的原因因为有之前的设置的有限制

更新交易记录：

要更新特定交易ID的交易状态为“完成”，并自动更新交易时间为当前时间，你需要编写一个SQL更新（UPDATE）语句，该语句包含两个部分的更新：一是交易状态，二是交易时间。交易时间通常存储在日期时间（DATETIME 或 TIMESTAMP）类型的列中。

假设你的BlockchainTransactions表有一个名为TransactionID的列（假设为UUID类型或VARCHAR类型，具体取决于你的实现），一个名为TransactionStatus的列（用于存储交易状态，如“完成”、“待处理”等），以及一个名为TransactionTime（或类似的名称）的列（用于存储交易发生的日期和时间）。

第4题：数据查询与过滤

1. 条件查询与排序：
- 查询所有交易状态为“完成”且交易金额大于200的记录，按交易金额降序排列，显示字段仅包括交易ID、交易金额和交易时间。
- 执行SQL查询语句，展示查询结果截图。

2. 复杂条件查询：
- 查询最近7天内所有类型为“收入”的交易记录，按时间排序，且只显示备注字段不为空的交易记录。
- 展示SQL查询语句与结果截图。

查询：SELECT TransactionID, TransactionAmount, TransactionTime FROM BlockchainTransactions WHERE TransactionStatus = '完成' AND TransactionAmount > 200 ORDER BY TransactionAmount DESC; 这个查询语句从BlockchainTransactions表中选取TransactionID、TransactionAmount和TransactionTime字段，条件是TransactionStatus等于'完成'且TransactionAmount大于200，结果按TransactionAmount降序排列。

. 复杂条件查询：

第5题：索引创建与优化

1. 索引创建与类型对比：
- 为交易ID字段创建唯一索引，展示SQL语句。
- 为交易时间字段创建普通索引，展示SQL语句。
- 说明唯一索引和普通索引的区别，并给出适用场景。

2. 索引优化测试：
- 执行以下查询，比较在创建索引前后查询速度的差异：
- 查询所有交易金额大于500并按交易时间排序的记录。
- 使用模糊查询LIKE，查找备注字段中包含“区块链”关键词的记录，测试索引前后的查询效率。
- 展示索引优化前后的执行时间截图，并分析索引对查询性能的影响。

- 为交易ID字段创建唯一索引:

为交易时间字段创建普通索引:

唯一索引和普通索引的区别及适用场景

区别：
- 唯一性：唯一索引保证列中的每个值都是唯一的，而普通索引没有这个要求。
- 性能：两者都能提高查询性能，但在维护唯一性方面，唯一索引需要额外的开销来检查唯一性约束。
- 使用场景：唯一索引适用于那些必须保证数据唯一性的场景（如用户ID、电子邮件地址等）。普通索引适用于需要提高查询速度但不要求数据唯一性的场景。
适用场景：
- 唯一索引：用户ID、邮箱地址、电话号码等，确保数据的唯一性。
- 普通索引：频繁查询列。
- 查询所有交易金额大于500并按交易时间排序的记录：SELECT * FROM BlockchainTransactions WHERE TransactionAmount > 500 ORDER BY TransactionTime;
- 使用模糊查询LIKE，查找备注字段中包含“区块链”关键词的记录，测试索引前后的查询效率。CREATE INDEX idx_remark ON BlockchainTransactions(Remark);