本次的王者荣耀信息管理系统数据库的1.0版本,需要在数据库中创建三个数据表:
其中,表1为订单表(表名为order),包含订单编号、所购皮肤id、下单用户id、购买时间、订单折扣、支付金额这6个属性。“购买时间”属性以时间戳的形式存储在数据库;
表2为皮肤信息表(表名为skinInfo),包含皮肤id、皮肤名称、归属英雄名称、皮肤品质、皮肤价格、皮肤资源Url这6个属性;
表3为英雄信息表(表名为heroInfo),包含英雄id、英雄名称、生命值、物理攻击、法术攻击、物理防御、法术防御、法力值、移速这9个属性。
请同学们根据题干的附件中提到的表结构和样本数据(表1-1、1-2、1-3,2-1、2-2、2-3),在Navicat或MySQL Workbench上完成下方的相应练习,并在附件中提交数据和表结构、以及查询语句转储而成的.sql文件。
我们将对这三个表进行查询。,以下是三个表内的相关数据。
注;表中的数据都是完成任务后的最终版,不是初版。
heroinfo表:
order表:
skininfo表:
任务一:插入heroinfo表数据
代码如图:
INSERT INTO是SQL语言中的一种语句,用于向数据库表中插入新的记录。
第一条代码:向名为"heroinfo"的数据表中插入一条记录。记录的字段包括英雄id、英雄名称、生命值、物理攻击、物理防御、法术防御、法力值和移速。对应的值分别为1、'孙尚香'、3235、168、0、89、50、440和350。
第二题代码:向名为"heroinfo"的数据表中插入一条记录。记录的字段包括英雄id、英雄名称、生命值、物理攻击、物理防御、法术防御、法力值和移速。对应的值分别为2、'孙策'、3320、151、0、97、50、420和380。
第三条代码:向名为"heroinfo"的数据表中插入一条记录。记录的字段包括英雄id、英雄名称、生命值、物理攻击、法术攻击、物理防御、法术防御、法力值和移速。对应的值分别为3、'诸葛亮'、3250、156、0、87、50、490和350。
第四条代码:名为"heroinfo"的数据表中插入一条记录。记录的字段包括英雄id、英雄名称、生命值、物理攻击、法术攻击、物理防御、法术防御、法力值和移速。对应的值分别为4、'荆轲'、3269、177、0、89、50、0和380。
这样,插入heroinfo表数据就插入完成。
效果如图:
注:由于后续任务点有“删除荆轲”所以上面的表未能显现出荆轲的相关资料。
同时,后续插入的order表数据和skinlnfo表数据同插入heroinfo表数据操作相同即可。
任务点二:接下来,为了提高诸葛亮的前期野区发育能力,我们决定上调诸葛亮的基础法术攻击值,由0调整为10
代码如图:
首先,我们需要找到诸葛亮的相关数据在哪个表,根据上面的相关资料显示,诸葛亮的相关数据在heroinfo表。
这条SQL语句使用了UPDATE语句,用于更新数据库中的数据。具体来说,它将"heroinfo"表中"英雄名称"为"诸葛亮"的记录的"法术攻击"字段更新为10。
这样诸葛亮的法术攻击记录就会更新为10.
任务点三:由于英雄名称编辑错误,需要删除英雄名称为“荆轲”的相应英雄信息
代码如图:
首先,我们需要找到荆轲的相关数据在哪个表,根据上面的相关资料显示,荆轲的相关数据在heroinfo表。
这条SQL语句使用了DELETE语句,用于删除数据库中的数据。具体来说,它将"heroinfo"表中"英雄名称"为"荆轲"的记录删除
这样,在表中关于荆轲的记录就会被删除。
任务点四:查询英雄信息表(heroinfo)中的英雄id,英雄名称和物理防御值。对于查询结果中的物理防御值,按以下规则进行替换:若物理防御值大于等于95,则替换为“强”;若物理防御值小于95,则替换为“弱”。被修改值所在列属性改为“承伤能力”。
代码如下:
代码解释:
SELECT
: 这是SQL查询的关键字,用于指定要检索的列
英雄id, 英雄名称
: 这些是要从"heroinfo"表中检索的列名。
CASE
: 这是SQL中的条件表达式,用于根据不同的条件返回不同的值。
WHEN 物理防御 >= 95 THEN '强'
: 这是第一个条件分支,检查"物理防御"的值是否大于或等于95。如果是,则返回字符串'强'。
WHEN 物理防御 < 95 THEN '弱'
: 这是第二个条件分支,检查"物理防御"的值是否小于95。如果是,则返回字符串'弱'。
END AS 承伤能力
: 这结束了CASE表达式,并将结果列名为"承伤能力"。
FROM heroinfo;
: 这指定了查询的数据来源是"heroinfo"表。
总的来说,这个查询会从"heroinfo"表中选择"英雄id"和"英雄名称"列,并添加一个新列"承伤能力",其值根据"物理防御"的值来决定是'强'还是'弱'。这样查询出来的结果就会如下图所示:
任务点五:查询数据库中的订单编号、所购皮肤id、皮肤名称和皮肤品质
代码如图所示:
由于上面的多条数据,出自不同的表,所以我们要使用联合查询。
代码解释:
SELECT
: 这是SQL查询的关键字,用于指定要检索的列。
order.订单编号, order.所购皮肤id
: 这些是要从"order"表中检索的列名,分别是"订单编号"和"所购皮肤id"。
skininfo.皮肤名称, skininfo.皮肤品质
: 这些是要从"skininfo"表中检索的列名,分别是"皮肤名称"和"皮肤品质"。
FROM order
: 这指定了查询的主要数据来源是"order"表。
JOIN skininfo ON order.所购皮肤id = skininfo.皮肤id
: 这是一个内连接(INNER JOIN),它根据"order"表中的"所购皮肤id"与"skininfo"表中的"皮肤id"相等的条件来连接两个表。
总的来说,这个查询会从"order"表和"skininfo"表中选择指定的列,并通过两个表中的相关ID字段进行连接,以获取订单中购买的皮肤的名称和品质信息。这样便能查出相关数据,查出的数据如图所示:
任务点六:查询数据库中的订单编号、所购皮肤id、皮肤名称和皮肤品质,并要求查询的订单信息在2024年4月22日0时(时间戳为1713715200)之后,且只包含“传说”品质的皮肤
代码如图所示:
查询的代码和任务点五差不多相同就不一一介绍了。
WHERE order.购买时间 > 1713715200 AND skininfo.皮肤品质 = '传说'
: 这是查询的筛选条件,选择"购买时间"大于1713715200的记录,并且"皮肤品质"为'传说'。
总的来说,这个查询会从"order"表和"skininfo"表中选择指定的列,并通过两个表中的相关ID字段进行连接,以获取订单中购买的传说品质皮肤的信息。就能查出订单信息在2024年4月20日0时购买皮肤品质等于传说级别的皮肤,结果如图所示:
任务点七:查询订单表中,“末日机甲”皮肤对应英雄的法力值小于430的所有订单信息
代码如下:
在上面任务点出现过的代码如select、from就不一一介绍了,可自行翻看
OIN heroinfo ON skininfo.皮肤名称 = '末日机甲' AND heroinfo.法力值 < 430
: 这又是一个内连接,它根据"skininfo"表中的"皮肤名称"等于'末日机甲'以及"heroinfo"表中的"法力值"小于430的条件来连接"skininfo"表和"heroinfo"表。
总的来说,这个查询会从"order"表、"skininfo"表和"heroinfo"表中选择指定的列,并通过多个表中的相关字段进行连接,以获取满足特定条件的订单信息,特别是那些购买了名为'末日机甲'且英雄法力值小于430的皮肤的订单信息。这样查询出来的结果就如图所示:
任务点八:按照订单折扣统计订单表中各种折扣下对应的总支付金额,结果按照对应总支付金额降序排列
代码如下:
代码解释:
SELECT '订单折扣', SUM('支付金额') AS '总支付金额'
: 这部分指定了查询的结果集中要包含的字段。这里选择了"订单折扣"字段,并计算了"支付金额"字段的总和,然后将这个总和命名为"总支付金额"。
FROM 'order'
: 这部分指定了查询的数据来源,即从名为"order"的表中获取数据。
GROUP BY '订单折扣'
: 这是SQL中的GROUP BY子句,用于将结果集按照"订单折扣"字段的值进行分组。这意味着结果集中将为每个不同的"订单折扣"值显示一个记录。
ORDER BY '总支付金额' DESC
: 这是SQL中的ORDER BY子句,用于指定结果集的排序方式。这里按照"总支付金额"字段的值进行降序排序,意味着金额最高的记录将显示在最上面。
总的来说,这个查询将显示不同"订单折扣"的分组,并计算每个分组的"支付金额"总和,然后按照这个总和的降序来排列结果。最后,查询出来的结果如图所示:
至此,所有任务点均已完成。