当前位置:   article > 正文

MySQL实训--原神数据库

MySQL实训--原神数据库

er图

在这里插入图片描述
在这里插入图片描述

DDL/DML语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `artifacts`;
CREATE TABLE `artifacts`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `artifacts_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `set_bonus` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '套装效果',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 52 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `artifacts` VALUES (1, '冒险家', '开启各类宝箱后的5秒内,持续恢复30%生命值。');
INSERT INTO `artifacts` VALUES (2, '幸运儿', '拾取摩拉时,恢复300点生命值。');
INSERT INTO `artifacts` VALUES (3, '游医', '释放元素爆发时,恢复20%生命值。');
INSERT INTO `artifacts` VALUES (4, '行者之心', '重击的暴击率提升30%。');
INSERT INTO `artifacts` VALUES (5, '奇迹', '受到某个元素类型的伤害后,相应的抗性提升30%,持续10秒。该效果每10秒只能触发一次。');
INSERT INTO `artifacts` VALUES (6, '战狂', '生命值低于70%时,暴击率额外提升24%。');
INSERT INTO `artifacts` VALUES (7, '教官', '触发元素反应后,队伍中所有角色的元素精通提高120点,持续8秒。');
INSERT INTO `artifacts` VALUES (8, '流放者', '施放元素爆发后,每2秒为队伍中所有角色(不包括自己)恢复2点元素能量。该效果持续6秒,无法叠加。');
INSERT INTO `artifacts` VALUES (9, '守护之心', '队伍里每有不同—种元素类型的自己的角色,自身获得30%相应的元素抗性。');
INSERT INTO `artifacts` VALUES (10, '勇士之心', '对生命值高于50%的敌人,造成的伤害增加30%。');
INSERT INTO `artifacts` VALUES (11, '武人', '释放元素战技后的8秒内,普通攻击和重击造成的伤害提升25%。');
INSERT INTO `artifacts` VALUES (12, '赌徒', '打倒敌人时,有100%概率清除元素战技的冷却时间。该效果每15秒至多触发—次。');
INSERT INTO `artifacts` VALUES (13, '学士', '获得元素微粒或元素晶球时,队伍中所有弓箭和法器角色额外恢复3点元素能量。该效果每3秒只能触发一次。');
INSERT INTO `artifacts` VALUES (14, '祭火之人', '受到的火元素附着效果的持续时间减少40%。');
INSERT INTO `artifacts` VALUES (15, '祭水之人', '受到的水元素附着效果的持续时间减少40%。');
INSERT INTO `artifacts` VALUES (16, '祭雷之人', '受到的雷元素附着效果的持续时间减少40%。');
INSERT INTO `artifacts` VALUES (17, '祭冰之人', '受到的冰元素附着效果的持续时间减少40%。');
INSERT INTO `artifacts` VALUES (18, '冰封迷途的勇士', '攻击处于冰元素影响下的敌人时,暴击率提高20%;若敌人处于冻结状态下,则暴击率额外提高20%。');
INSERT INTO `artifacts` VALUES (19, '平息鸣雷的尊者', '对处于雷元素影响下的敌人造成的伤害提升35%。');
INSERT INTO `artifacts` VALUES (20, '渡过烈火的贤人', '对处于火元素影响下的敌人造成的伤害提升35%。');
INSERT INTO `artifacts` VALUES (21, '被怜爱的少女', '施放元素战技或元素爆发后的10秒内,队伍中所有角色受治疗效果加成提高20%。');
INSERT INTO `artifacts` VALUES (22, '角斗士的终幕礼', '装备该圣遗物套装的角色为单手剑、双手剑、长柄武器角色时,角色普通攻击造成的伤害提高35%。');
INSERT INTO `artifacts` VALUES (23, '翠绿之影', '扩散反应造成的伤害提升60%。根据扩散的元素类型,降低受到影响的敌人40%的对应元素抗性,持续10秒。');
INSERT INTO `artifacts` VALUES (24, '流浪大地的乐章', '装备该圣遗物套装的角色为法器、弓箭角色时,角色重击造成的伤害提高35%。');
INSERT INTO `artifacts` VALUES (25, '如雷的盛怒', '超载、感电、超导、超绽放反应造成的伤害提升40%,超激化反应带来的伤害提升提高20%。触发上述元素反应或原激化反应时,元素战技冷却时间减少1秒。该效果每0.8秒最多触发—次。');
INSERT INTO `artifacts` VALUES (26, '炽烈的炎之魔女', '超载、燃烧、烈绽放反应造成的伤害提升40%,蒸发、融化反应的加成系数提高15%。施放元素战技后的10秒内,2件套的效果提高50%,该效果最多叠加3次。');
INSERT INTO `artifacts` VALUES (27, '昔日宗室之仪', '释放元素爆发后,队伍中所有角色攻击力提升20%,持续12秒,该效果不可叠加。');
INSERT INTO `artifacts` VALUES (28, '染血的骑士道', '击败敌人后的10秒内,施放重击时不消耗体力,且重击造成的伤害提升50%。');
INSERT INTO `artifacts` VALUES (29, '悠古的磐岩', '获得结晶反应形成的晶片时,队伍中所有角色获得35%对应元素伤害加成,持续10秒。同时只能通过该效果获得一种元素伤害加成。');
INSERT INTO `artifacts` VALUES (30, '逆飞的流星', '处于护盾庇护下时,额外获得40%普通攻击和重击伤害加成。');
INSERT INTO `artifacts` VALUES (31, '沉沦之心', '施放元素战技后的15秒内,普通攻击与重击造成的伤害提高30%。');
INSERT INTO `artifacts` VALUES (32, '千岩牢固', '元素战技命中敌人后,使队伍中附近的所有角色攻击力提升20%,护盾强效提升30%,持续3秒。该效果每0.5秒至多触发一次。装备此圣遗物套装的角色处于队伍后台时,依然育能触发该效果。');
INSERT INTO `artifacts` VALUES (33, '苍白之火', '元素战技命中敌人后,攻击力提升9%。该效果持续7秒,至多叠加2层,每0.3秒至多触发一次。叠满2层时,2件套的效果提升100%。');
INSERT INTO `artifacts` VALUES (34, '追忆之注连', '释放元素战技时,如果角色的元素能星高于或等于15点,则会流失15点元素能量,使接下来的10秒内,普通攻击、重击、下落攻击造成的伤害提高50%,持续期间内效果不会再次触发。');
INSERT INTO `artifacts` VALUES (35, '绝缘之旗印', '基于元素充能效率的25%,提高元素爆发造成的伤害。至多通过这种方式获得75%提升。');
INSERT INTO `artifacts` VALUES (36, '华馆梦醒形骸记', '装备此圣遗物套装的角色在以下情况下,将获得「问答」效果:在场上用岩元素攻击命中敌人后获得一层,每0.3秒至多触发一次;在队伍后台中,每3秒获得一层。问答至多叠加4层,每层能提供6%防御力与6%岩元素伤害加成。每6秒,若未获得问答效果,将损失一层。');
INSERT INTO `artifacts` VALUES (37, '海染砗磲', '装备此圣遗物套装的角色对队伍中的角色进行治疗时,将产生持续3秒的海染泡沫,记录治疗的生命值回复量(包括溢出值)。持续时间结束时,海染泡沫将会爆炸,对周围的敌人造成90%累计回复量的伤害(该伤害结算方式同感电、超导等元素反应,但不受元素精通、等级或反应伤害加成效果影响)。每3.5秒至多产生一个海染泡沬;海染泡沫至多记录30000点回复量,含溢出部分的治疗量;自己的队伍中同时至多存在一个海染泡沫。装备此圣遗物套装的角色处于队伍后台时;依然能触发该效果。');
INSERT INTO `artifacts` VALUES (38, '辰砂往生录', '施放元素爆发后,将产生持续16秒的「潜光」效果:攻击力提升8%;并在角色的生命值降低时,攻击力进一步提升10%,至多通过这种方式提升4次,每0.8秒至多触发一次。「潜光」效果将在角色退场时消失;持续期间再次施放元素爆发,将移除原有的「潜光」。');
INSERT INTO `artifacts` VALUES (39, '来歆余响', '普通攻击命中敌人时,有36%概率触发「幽谷祝祀」︰普通攻击造成的伤害提高,伤害提高值为攻击力的70%,该效果将在普通攻击造成伤害后的0.05秒后清除。普通攻击未触发「幽谷祝祀」时,会使下次触发概率提升20%;0.2秒内至多判定1次触发与否。');
INSERT INTO `artifacts` VALUES (40, '深林的记忆', '元素战技或元素爆发命中敌人后,使命中目标的草元素抗性降低30%,持续8秒。装备者处于队伍后台时,依然能触发该效果。');
INSERT INTO `artifacts` VALUES (41, '饰金之梦', '触发元素反应后的8秒内,会根据队伍内其他角色的元素类型,使装备者获得强化:队伍中每存在1个和装备者同类元素的角色,攻击力提升14%;每存在1个和装备者不同元素类型的角色,元素精通提升50点。上述每类效果至多计算3个角色。该效果每8秒至多触发一次。装备者处于队伍后台时,依然能触发该效果。');
INSERT INTO `artifacts` VALUES (42, '沙上楼阁史话', '重击命中敌人后,该角色的普通攻击速度提升10%,普通攻击、重击与下落攻击造成的伤害提升40%,持续15秒。');
INSERT INTO `artifacts` VALUES (43, '乐园遗落之花', '装备者绽放、超绽放、烈绽放反应造成的伤害提升40%。此外,装备者触发绽放、超绽放、烈绽放后,上述效果带来的加成提升25%,该效果持续10秒,至多叠加4次,每1秒至多触发一次。装备者处于队伍后台时依然能触发该效果。');
INSERT INTO `artifacts` VALUES (44, '水仙之梦', '普通攻击、重击、下落攻击、元素战技或元素爆发命中敌人后,将产生1层持续8秒的「镜中水仙」效果。处于1/2/3层及以上「镜中水仙」效果下时,攻击力将提高7%/16%/25%,水元素伤害加成提升4%/9%/15%。由普通攻击、重击、下落攻击、元素战技或元素爆发产生的「镜中水仙」将分别独立存在。');
INSERT INTO `artifacts` VALUES (45, '花海甘露之光', '元素战技与元素爆发造成的伤害提升10%;装备者受到伤害后的5秒内,上述伤害提升效果提高80%。该提高效果至多叠加5层。每层持续时间独立计算,处于队伍后台时依然能触发该效果。');
INSERT INTO `artifacts` VALUES (46, '逐影猎人', '当前生命值提升或降低时,暴击率提升12%,该效果持续5秒,至多叠加3次。');
INSERT INTO `artifacts` VALUES (47, '黄金剧团', '元素战技造成的伤害提升25%;此外,处于队伍后台时,元素战技造成的伤害还将进一步提升25%,该效果将在登场后2秒移除。');
INSERT INTO `artifacts` VALUES (48, '昔时之歌', '装备者对队伍中的角色进行治疗时将产生持续6秒的渴盼效果,记录治疗的生命值回复量(包括溢出值)。持续时间结束时,渴盼效果将转变为「彼时的浪潮」效果:队伍中自己的当前场上角色的普通攻击、重击、下落攻击、元素战技与元素爆发命中敌人时,将基于渴盼效果所记录的回复量的8%提高造成的伤害,「彼时的浪潮」将在生效5次或10秒后移除。一次渴盼效果至多记录15000点回复量,同时至多存在一个能够记录多个装备者的产生的回复量;装备者处于队伍后台时,依然能触发该效果。');
INSERT INTO `artifacts` VALUES (49, '回声之林夜话', '施放元素战技后的10秒内,岩元素伤害加成提升20%;若处于结晶反应产生的护盾庇护下,上述效果提高150%,进一步提高的效果将在失去结晶护盾庇护的1秒后移除。');
INSERT INTO `artifacts` VALUES (50, '谐律异想断章', '生命之契的数值提升或降低时角色造成的伤害提升18%,该效果持续6秒,至多叠加3次。');
INSERT INTO `artifacts` VALUES (51, '未竟的遐思', '脱离战斗状态3秒后,造成的伤害提升50%。在战斗状态下,附近不存在处于燃烧状态下的敌人超过6秒后,上述伤害提升效果每秒降低10%直到降低至0%;存在处于燃烧状态下的敌人时,每秒提升10%,直到达到50%。装备此圣遗物套装的角色处于队伍后台时,依然会触发该效果。');

DROP TABLE IF EXISTS `artifacts_select`;
CREATE TABLE `artifacts_select`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `artifacts_id` int NOT NULL,
  `user_id` int NULL DEFAULT NULL,
  `flower_of_life` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生之花',
  `plume_of_death` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '死之羽',
  `sands_of_eon` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '时之沙',
  `goblet_of_eonothem` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '空之杯',
  `circlet_of_logos` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '理之冠',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `dacishuwang`(`artifacts_id`) USING BTREE,
  INDEX `zhixingguansanbing`(`user_id`) USING BTREE,
  CONSTRAINT `dacishuwang` FOREIGN KEY (`artifacts_id`) REFERENCES `artifacts` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `zhixingguansanbing` FOREIGN KEY (`user_id`) REFERENCES `user` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `artifacts_select` VALUES (1, 41, 68, '4780', '311', '187精通', '46.6%草伤', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (2, 18, 57, '4780', '311', '46.6%攻击', '46.6%冰杯', '46.6%生命');
INSERT INTO `artifacts_select` VALUES (3, 19, 58, '4780', '311', '51.8%充能', '46.6%雷杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (4, 35, 42, '4780', '311', '51.8%充能', '46.6%水杯', '62.2%爆伤');
INSERT INTO `artifacts_select` VALUES (5, 47, 76, '4780', '165', '46.6%生命', '46.6%水杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (6, 42, 69, '4780', '311', '46.6%攻击', '46.6%风杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (7, 42, 56, '4780', '311', '46.6%攻击', '46.6%风杯', '62.2%爆伤');
INSERT INTO `artifacts_select` VALUES (8, 26, 65, '4780', '311', '46.6%生命', '46.6%火杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (9, 26, 53, '4780', '311', '46.6%攻击', '46.6%火杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (10, 33, 33, '4780', '200', '46.6%攻击', '46.6%物伤', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (11, 31, 55, '4780', '311', '46.6%攻击', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (12, 39, 84, '4780', '555', '46.6%生命', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (13, 30, 54, '4780', '311', '46.6%治疗加成', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (14, 30, 67, '4780', '311', '51.8%充能', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (15, 26, 68, '4780', '311', '51.8%充能', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (16, 37, 77, '4780', '232', '51.8%充能', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (17, 27, 78, '4780', '311', '187精通', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (18, 37, 76, '4780', '311', '46.6%攻击', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (19, 24, 85, '4780', '311', '187精通', '46.6%岩杯', '31.1%暴率');
INSERT INTO `artifacts_select` VALUES (20, 22, 77, '4780', '311', '46.6%生命', '46.6%岩杯', '31.1%暴率');

DROP TABLE IF EXISTS `talent`;
CREATE TABLE `talent`  (
  `user_id` int NOT NULL,
  `normal_attack` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `E` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Q` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE,
  CONSTRAINT `qiyejun` FOREIGN KEY (`user_id`) REFERENCES `user` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `talent` VALUES (53, '10', '10', '10');
INSERT INTO `talent` VALUES (57, '10', '9', '8');
INSERT INTO `talent` VALUES (58, '9', '10', '10');
INSERT INTO `talent` VALUES (68, '10', '10', '10');
INSERT INTO `talent` VALUES (69, '10', '10', '10');
INSERT INTO `talent` VALUES (76, '9', '9', '9');
INSERT INTO `talent` VALUES (82, '10', '10', '10');
INSERT INTO `talent` VALUES (84, '10', '10', '10');
INSERT INTO `talent` VALUES (85, '10', '10', '10');
INSERT INTO `talent` VALUES (86, '10', '10', '10');

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) NOT NULL COMMENT '角色名\r\n',
  `weapon_type_id` int NOT NULL COMMENT '武器类型',
  `dendro` varchar(255) NOT NULL COMMENT '元素类型',
  `rarity` varchar(255) NOT NULL COMMENT '稀有度',
  `weapon_id` int DEFAULT NULL COMMENT '武器id',
  `weapon_grade` int DEFAULT '90' COMMENT '武器等级',
  `artifacts_id` int DEFAULT NULL COMMENT '圣遗物id',
  `user_level` int DEFAULT NULL COMMENT '角色等级',
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `weapon_id` (`weapon_id`,`artifacts_id`) USING BTREE,
  KEY `bengbeng123` (`weapon_type_id`) USING BTREE,
  CONSTRAINT `bengbeng123` FOREIGN KEY (`weapon_type_id`) REFERENCES `weapon_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO `user` VALUES (1, '安柏', 5, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (2, '凯亚', 2, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (3, '丽莎', 4, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (4, '芭芭拉', 4, '水', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (5, '雷泽', 1, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (6, '香菱', 3, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (7, '北斗', 1, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (8, '行秋', 2, '水', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (9, '凝光', 4, '岩', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (10, '菲谢尔', 5, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (11, '班尼特', 2, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (12, '诺艾尔', 1, '岩', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (13, '重云', 1, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (14, '砂糖', 4, '风', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (15, '迪奥娜', 5, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (16, '辛焱', 1, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (17, '罗莎莉亚', 3, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (18, '烟绯', 4, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (19, '早柚', 1, '风', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (20, '九条裟罗', 5, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (21, '托马', 3, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (22, '五郎', 5, '岩', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (23, '云堇', 3, '岩', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (24, '久岐忍', 2, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (25, '鹿野院平藏', 4, '风', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (26, '柯莱', 5, '草', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (27, '多莉', 1, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (28, '坎蒂丝', 3, '水', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (29, '莱依拉', 2, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (30, '珐露珊', 5, '风', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (31, '瑶瑶', 3, '草', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (32, '米卡', 3, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (33, '卡维', 1, '草', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (34, '绮良良', 2, '草', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (35, '琳妮特', 2, '风', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (36, '菲米尼', 1, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (37, '夏洛蒂', 4, '冰', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (38, '夏沃蕾', 3, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (39, '嘉明', 1, '火', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (40, '赛索斯', 5, '雷', '4', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (41, '琴', 2, '风', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (42, '迪卢克', 1, '火', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (43, '七七', 2, '冰', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (44, '莫娜', 4, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (45, '刻晴', 2, '雷', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (46, '温迪', 5, '风', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (47, '可莉', 4, '火', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (48, '达达利亚', 5, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (49, '钟离', 3, '岩', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (50, '阿贝多', 2, '岩', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (51, '甘雨', 5, '冰', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (52, '魈', 3, '风', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (53, '胡桃', 3, '火', '5', 99, 90, 9, 90);
INSERT INTO `user` VALUES (54, '优菈', 1, '冰', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (55, '枫原万叶', 2, '风', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (56, '神里绫华', 2, '冰', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (57, '宵宫', 5, '火', '5', 169, 90, 2, 90);
INSERT INTO `user` VALUES (58, '雷电将军', 3, '雷', '5', 100, 90, 3, 90);
INSERT INTO `user` VALUES (59, '珊瑚宫心海', 4, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (60, '荒泷一斗', 1, '岩', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (61, '申鹤', 3, '冰', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (62, '八重神子', 4, '雷', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (63, '神里绫人', 2, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (64, '夜兰', 5, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (65, '提纳里', 5, '草', '5', NULL, NULL, 8, NULL);
INSERT INTO `user` VALUES (66, '赛诺', 3, '雷', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (67, '妮露', 2, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (68, '纳西妲', 4, '草', '5', 135, 90, 1, 90);
INSERT INTO `user` VALUES (69, '流浪者', 4, '风', '5', 136, 90, 6, 90);
INSERT INTO `user` VALUES (70, '艾尔海森', 2, '草', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (71, '迪希雅', 1, '火', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (72, '白术', 4, '草', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (73, '林尼', 5, '火', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (74, '那维莱特', 4, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (75, '莱欧斯利', 4, '冰', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (76, '芙宁娜', 2, '水', '5', 70, 90, 5, 90);
INSERT INTO `user` VALUES (77, '娜维娅', 1, '岩', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (78, '闲云', 4, '风', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (79, '千织', 2, '岩', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (80, '阿蕾奇诺', 3, '火', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (81, '克洛琳德', 2, '雷', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (82, '希格雯', 5, '水', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (83, '埃洛伊', 5, '冰', '5', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (84, '执行官散兵', 2, '雷', '6', 136, 90, 12, 90);
INSERT INTO `user` VALUES (85, '倾奇者', 2, '雷', '7', 136, 90, NULL, 90);
INSERT INTO `user` VALUES (86, '大慈树王', 4, '草', '7', NULL, NULL, 19, 90);



CREATE TABLE `weapon` (
  `id` int NOT NULL AUTO_INCREMENT,
  `weapon_type_id` int DEFAULT NULL,
  `rarity` varchar(255) DEFAULT NULL,
  `weapon_name` varchar(255) DEFAULT NULL,
  `weapon_details` varchar(255) DEFAULT NULL,
  `weapon_level` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `nahida66` (`weapon_type_id`) USING BTREE,
  CONSTRAINT `nahida66` FOREIGN KEY (`weapon_type_id`) REFERENCES `weapon_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=176 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;









INSERT INTO `weapon` VALUES (1, 1, '1', '训练大剑', '185攻击力', NULL);
INSERT INTO `weapon` VALUES (2, 1, '2', '佣兵重剑', '243攻击力', NULL);
INSERT INTO `weapon` VALUES (3, 1, '3', '铁影阔剑', '401攻击力35.2%生命值', NULL);
INSERT INTO `weapon` VALUES (4, 1, '3', '白铁大剑', '401攻击力43.9%防御力', NULL);
INSERT INTO `weapon` VALUES (5, 1, '3', '以理服人', '401攻击力35.2%攻击力', NULL);
INSERT INTO `weapon` VALUES (6, 1, '3', '飞天大御剑', '401攻击力43.9%物理伤害加成', NULL);
INSERT INTO `weapon` VALUES (7, 1, '4', '西风大剑', '454攻击力61.3%充能', NULL);
INSERT INTO `weapon` VALUES (8, 1, '4', '钟剑', '510攻击力41.3%生命', NULL);
INSERT INTO `weapon` VALUES (9, 1, '4', '祭礼大剑', '565攻击力30.6%元素效率', NULL);
INSERT INTO `weapon` VALUES (10, 1, '4', '宗室大剑', '565攻击力27.6%攻击力', NULL);
INSERT INTO `weapon` VALUES (11, 1, '4', '雨裁', '510攻击力165精通', NULL);
INSERT INTO `weapon` VALUES (12, 1, '4', '试作古华', '565攻击力27.6%攻击', NULL);
INSERT INTO `weapon` VALUES (13, 1, '4', '白影剑', '510攻击力51.7%防御力', NULL);
INSERT INTO `weapon` VALUES (14, 1, '4', '黑岩斩刀', '510攻击力55.1%暴伤', NULL);
INSERT INTO `weapon` VALUES (15, 1, '4', '螭骨剑', '510攻击力27.6%暴率', NULL);
INSERT INTO `weapon` VALUES (16, 1, '4', '雪葬的星银', '565攻击力34.5%物伤', NULL);
INSERT INTO `weapon` VALUES (17, 1, '4', '千岩古剑', '510攻击力41.3%攻击', NULL);
INSERT INTO `weapon` VALUES (18, 1, '4', '桂木斩长正', '510攻击力45.9%充能', NULL);
INSERT INTO `weapon` VALUES (19, 1, '4', '恶王丸', '510攻击力41.3%攻击', NULL);
INSERT INTO `weapon` VALUES (20, 1, '4', '森林王器', '565攻击力30.6%元素效率', NULL);
INSERT INTO `weapon` VALUES (21, 1, '4', '玛海菈的水色', '510攻击力165精通', NULL);
INSERT INTO `weapon` VALUES (22, 1, '4', '饰铁之花', '565攻击力110精通', NULL);
INSERT INTO `weapon` VALUES (23, 1, '4', '聊聊棒', '565攻击力18.4暴率', NULL);
INSERT INTO `weapon` VALUES (24, 1, '4', '浪影阔剑', '510攻击力41.3%攻击', NULL);
INSERT INTO `weapon` VALUES (25, 1, '4', '便携动力锯', '454攻击力55.1%生命', NULL);
INSERT INTO `weapon` VALUES (26, 1, '4', '究极霸王超级魔剑', '510攻击力41.3%攻击', NULL);
INSERT INTO `weapon` VALUES (27, 1, '5', '天空之傲', '674攻击力36.8%充能', NULL);
INSERT INTO `weapon` VALUES (28, 1, '5', '狼的末路', '608攻击力49.6%攻击力', NULL);
INSERT INTO `weapon` VALUES (29, 1, '5', '无工之剑', '608攻击力49.6%攻击力', NULL);
INSERT INTO `weapon` VALUES (30, 1, '5', '松籁响起之时', '741攻击力20.7物伤', NULL);
INSERT INTO `weapon` VALUES (31, 1, '5', '赤角石溃杵', '542攻击力88.2爆伤', NULL);
INSERT INTO `weapon` VALUES (32, 1, '5', '苇海信标', '608攻击力33.1%暴率', NULL);
INSERT INTO `weapon` VALUES (33, 1, '5', '裁断', '674攻击力22.1%暴率', NULL);
INSERT INTO `weapon` VALUES (34, 2, '1', '无锋剑', '185攻击力', NULL);
INSERT INTO `weapon` VALUES (35, 2, '2', '银剑', '243攻击力', NULL);
INSERT INTO `weapon` VALUES (36, 2, '3', '冷刃', '401攻击力35.2%攻击', NULL);
INSERT INTO `weapon` VALUES (37, 2, '3', '黎明神剑', '401攻击力46.9%爆伤', NULL);
INSERT INTO `weapon` VALUES (38, 2, '3', '旅行剑', '448攻击力29.3%防御力', NULL);
INSERT INTO `weapon` VALUES (39, 2, '3', '吃虎鱼刀', '401攻击力35.2%攻击', NULL);
INSERT INTO `weapon` VALUES (40, 2, '3', '飞天御剑', '354攻击力52.1%充能', NULL);
INSERT INTO `weapon` VALUES (41, 2, '4', '西风剑', '454攻击力61.3%充能', NULL);
INSERT INTO `weapon` VALUES (42, 2, '4', '笛剑', '510攻击力41.3%攻击', NULL);
INSERT INTO `weapon` VALUES (43, 2, '4', '祭礼剑', '454攻击力61.3%充能', NULL);
INSERT INTO `weapon` VALUES (44, 2, '4', '宗室长剑', '510攻击力41.3攻击', NULL);
INSERT INTO `weapon` VALUES (45, 2, '4', '匣里龙吟', '510攻击力41.3攻击', NULL);
INSERT INTO `weapon` VALUES (46, 2, '4', '试作斩岩', '565攻击力34.5%物伤', NULL);
INSERT INTO `weapon` VALUES (47, 2, '4', '铁蜂刺', '510攻击力165精通', NULL);
INSERT INTO `weapon` VALUES (48, 2, '4', '黑岩长剑', '565攻击力36.8%爆伤', NULL);
INSERT INTO `weapon` VALUES (49, 2, '4', '黑剑', '510攻击力27.6%暴率', NULL);
INSERT INTO `weapon` VALUES (50, 2, '4', '降临之剑', '440攻击力35.2%攻击力', NULL);
INSERT INTO `weapon` VALUES (51, 2, '4', '暗巷闪光', '620攻击力55精通', NULL);
INSERT INTO `weapon` VALUES (52, 2, '4', '天目影打刀', '454攻击力55.1攻击', NULL);
INSERT INTO `weapon` VALUES (53, 2, '4', '笼钓瓶一心', '510攻击力41.3%攻击', NULL);
INSERT INTO `weapon` VALUES (54, 2, '4', '原木刀', '565攻击力30.6%充能', NULL);
INSERT INTO `weapon` VALUES (55, 2, '4', '西福斯的月光', '510攻击力165精通', NULL);
INSERT INTO `weapon` VALUES (56, 2, '4', '狼牙', '510攻击力27.6%暴率', NULL);
INSERT INTO `weapon` VALUES (57, 2, '4', '海渊终曲', '565攻击力27.6%攻击', NULL);
INSERT INTO `weapon` VALUES (58, 2, '4', '灰河渡手', '510攻击力45.9%充能', NULL);
INSERT INTO `weapon` VALUES (59, 2, '4', '船坞长剑', '510攻击力41.3%生命', NULL);
INSERT INTO `weapon` VALUES (60, 2, '4', '水仙十字圣剑', '510攻击力41.3%攻击', NULL);
INSERT INTO `weapon` VALUES (61, 2, '5', '风鹰剑', '674攻击力41.3%物伤', NULL);
INSERT INTO `weapon` VALUES (62, 2, '5', '天空之刃', '608攻击力55.1%充能', NULL);
INSERT INTO `weapon` VALUES (63, 2, '5', '斫峰之刃', '608攻击力49.6%攻击', NULL);
INSERT INTO `weapon` VALUES (64, 2, '5', '磐岩结绿', '542攻击力44.1%暴率', NULL);
INSERT INTO `weapon` VALUES (65, 2, '5', '苍古自由之誓', '608攻击力198精通', NULL);
INSERT INTO `weapon` VALUES (66, 2, '5', '雾切之回光', '674攻击力44.1%爆伤', NULL);
INSERT INTO `weapon` VALUES (67, 2, '5', '波乱月白经津', '608攻击力33.1暴率', NULL);
INSERT INTO `weapon` VALUES (68, 2, '5', '圣显之钥', '542攻击力66.2%生命', NULL);
INSERT INTO `weapon` VALUES (69, 2, '5', '裁叶萃光', '542攻击力88.2%爆伤', NULL);
INSERT INTO `weapon` VALUES (70, 2, '5', '静水流涌之辉', '542攻击力88.2%爆伤', NULL);
INSERT INTO `weapon` VALUES (71, 2, '5', '有乐御簾切', '542攻击力88.2%爆伤', NULL);
INSERT INTO `weapon` VALUES (72, 2, '5', '赦罪', '674攻击力44.1%爆伤', NULL);
INSERT INTO `weapon` VALUES (73, 3, '1', '新手长枪', NULL, NULL);
INSERT INTO `weapon` VALUES (74, 3, '2', '铁尖枪', NULL, NULL);
INSERT INTO `weapon` VALUES (75, 3, '3', '白缨枪', NULL, NULL);
INSERT INTO `weapon` VALUES (76, 3, '3', '钺矛', NULL, NULL);
INSERT INTO `weapon` VALUES (77, 3, '3', '黑缨枪', NULL, NULL);
INSERT INTO `weapon` VALUES (78, 3, '4', '匣里灭辰', NULL, NULL);
INSERT INTO `weapon` VALUES (79, 3, '4', '试作星镰', NULL, NULL);
INSERT INTO `weapon` VALUES (80, 3, '4', '流月针', NULL, NULL);
INSERT INTO `weapon` VALUES (81, 3, '4', '黑岩刺枪', NULL, NULL);
INSERT INTO `weapon` VALUES (82, 3, '4', '决斗之枪', NULL, NULL);
INSERT INTO `weapon` VALUES (83, 3, '4', '西风长枪', NULL, NULL);
INSERT INTO `weapon` VALUES (84, 3, '4', '宗室猎枪', NULL, NULL);
INSERT INTO `weapon` VALUES (86, 3, '4', '龙脊长枪', NULL, NULL);
INSERT INTO `weapon` VALUES (87, 3, '4', '千岩长枪', NULL, NULL);
INSERT INTO `weapon` VALUES (88, 3, '4', '喜多院十文字', NULL, NULL);
INSERT INTO `weapon` VALUES (89, 3, '4', '渔获', NULL, NULL);
INSERT INTO `weapon` VALUES (90, 3, '4', '断浪长鳍', NULL, NULL);
INSERT INTO `weapon` VALUES (91, 3, '4', '贯月矢', NULL, NULL);
INSERT INTO `weapon` VALUES (92, 3, '4', '峡湾长歌', NULL, NULL);
INSERT INTO `weapon` VALUES (93, 3, '4', '公义的报酬', NULL, NULL);
INSERT INTO `weapon` VALUES (94, 3, '4', '勘探钻机', NULL, NULL);
INSERT INTO `weapon` VALUES (95, 3, '4', '沙中伟贤的对答', NULL, NULL);
INSERT INTO `weapon` VALUES (96, 3, '5', '和璞鸢', '674攻击力22.1暴率', NULL);
INSERT INTO `weapon` VALUES (97, 3, '5', '天空之脊', '674攻击力36.8%充能', NULL);
INSERT INTO `weapon` VALUES (98, 3, '5', '贯虹之槊', '608攻击力49.6%攻击', NULL);
INSERT INTO `weapon` VALUES (99, 3, '5', '护摩之杖', '608攻击力66.2%爆伤', NULL);
INSERT INTO `weapon` VALUES (100, 3, '5', '薙草之稻光', '608攻击力55.1%充能', NULL);
INSERT INTO `weapon` VALUES (101, 3, '5', '息灾', '741攻击力16.5%攻击', NULL);
INSERT INTO `weapon` VALUES (102, 3, '5', '赤沙之杖', '542攻击力44.1%暴率', NULL);
INSERT INTO `weapon` VALUES (103, 3, '5', '赤月之形', '674攻击力22.1暴率', NULL);
INSERT INTO `weapon` VALUES (104, 4, '1', '学徒笔记', NULL, NULL);
INSERT INTO `weapon` VALUES (105, 4, '2', '魔导绪论', NULL, NULL);
INSERT INTO `weapon` VALUES (106, 4, '3', '讨龙英杰谭', NULL, NULL);
INSERT INTO `weapon` VALUES (107, 4, '3', '异世界行记', NULL, NULL);
INSERT INTO `weapon` VALUES (108, 4, '3', '翡玉法球', NULL, NULL);
INSERT INTO `weapon` VALUES (109, 4, '3', '甲级宝钰', NULL, NULL);
INSERT INTO `weapon` VALUES (110, 4, '4', '西风秘典', NULL, NULL);
INSERT INTO `weapon` VALUES (111, 4, '4', '流浪乐章', NULL, NULL);
INSERT INTO `weapon` VALUES (114, 4, '4', '祭礼残章', NULL, NULL);
INSERT INTO `weapon` VALUES (115, 4, '4', '宗室秘法录', NULL, NULL);
INSERT INTO `weapon` VALUES (116, 4, '4', '匣里日月', NULL, NULL);
INSERT INTO `weapon` VALUES (117, 4, '4', '试作金珀', NULL, NULL);
INSERT INTO `weapon` VALUES (118, 4, '4', '万国诸海图谱', NULL, NULL);
INSERT INTO `weapon` VALUES (119, 4, '4', '黑岩绯玉', NULL, NULL);
INSERT INTO `weapon` VALUES (120, 4, '4', '昭心', NULL, NULL);
INSERT INTO `weapon` VALUES (121, 4, '4', '忍冬之果', NULL, NULL);
INSERT INTO `weapon` VALUES (122, 4, '4', '暗巷的酒与诗', NULL, NULL);
INSERT INTO `weapon` VALUES (123, 4, '4', '白辰之环', NULL, NULL);
INSERT INTO `weapon` VALUES (124, 4, '4', '盈满之实', NULL, NULL);
INSERT INTO `weapon` VALUES (125, 4, '4', '流浪的晚星', NULL, NULL);
INSERT INTO `weapon` VALUES (126, 4, '4', '遗祀玉珑', NULL, NULL);
INSERT INTO `weapon` VALUES (127, 4, '4', '纯水流华', NULL, NULL);
INSERT INTO `weapon` VALUES (128, 4, '4', '无垠蔚蓝之歌', NULL, NULL);
INSERT INTO `weapon` VALUES (129, 4, '5', '天空之卷', '674攻击力33.1攻击', NULL);
INSERT INTO `weapon` VALUES (130, 4, '5', '四风原典', '608攻击力33.1暴率', NULL);
INSERT INTO `weapon` VALUES (131, 4, '5', '尘世之锁', '608攻击力49.6%攻击力', NULL);
INSERT INTO `weapon` VALUES (132, 4, '5', '不灭月华', '608攻击力49.6%生命', NULL);
INSERT INTO `weapon` VALUES (134, 4, '5', '神乐之真意', '608攻击力66.2爆伤', NULL);
INSERT INTO `weapon` VALUES (135, 4, '5', '千夜浮梦', '542攻击力265精通', NULL);
INSERT INTO `weapon` VALUES (136, 4, '5', '图莱杜拉的回忆', '674攻击力44.1爆伤', NULL);
INSERT INTO `weapon` VALUES (137, 4, '5', '碧落之珑', '608攻击力49.6生命值', NULL);
INSERT INTO `weapon` VALUES (138, 4, '5', '金流监督', '674攻击力22.1暴率', NULL);
INSERT INTO `weapon` VALUES (139, 4, '5', '万世涌流大典', '542攻击力88.2%爆伤', NULL);
INSERT INTO `weapon` VALUES (140, 4, '5', '鹤鸣余音', '741攻击力16.5%攻击', NULL);
INSERT INTO `weapon` VALUES (141, 5, '1', '猎弓', NULL, NULL);
INSERT INTO `weapon` VALUES (142, 5, '2', '历练的猎弓', NULL, NULL);
INSERT INTO `weapon` VALUES (143, 5, '3', '鸦羽弓', NULL, NULL);
INSERT INTO `weapon` VALUES (144, 5, '3', '神射手之誓', NULL, NULL);
INSERT INTO `weapon` VALUES (145, 5, '3', '反曲弓', NULL, NULL);
INSERT INTO `weapon` VALUES (146, 5, '3', '弹弓', NULL, NULL);
INSERT INTO `weapon` VALUES (147, 5, '3', '信使', NULL, NULL);
INSERT INTO `weapon` VALUES (148, 5, '4', '西风猎弓', NULL, NULL);
INSERT INTO `weapon` VALUES (149, 5, '4', '绝弦', NULL, NULL);
INSERT INTO `weapon` VALUES (150, 5, '4', '祭礼弓', NULL, NULL);
INSERT INTO `weapon` VALUES (151, 5, '4', '宗室长弓', NULL, NULL);
INSERT INTO `weapon` VALUES (152, 5, '4', '弓藏', NULL, NULL);
INSERT INTO `weapon` VALUES (153, 5, '4', '试作澹月', NULL, NULL);
INSERT INTO `weapon` VALUES (154, 5, '4', '钢轮弓', NULL, NULL);
INSERT INTO `weapon` VALUES (155, 5, '4', '黑岩战弓', NULL, NULL);
INSERT INTO `weapon` VALUES (156, 5, '4', '苍翠猎弓', NULL, NULL);
INSERT INTO `weapon` VALUES (157, 5, '4', '暗巷猎手', NULL, NULL);
INSERT INTO `weapon` VALUES (158, 5, '4', '幽夜华尔兹', NULL, NULL);
INSERT INTO `weapon` VALUES (159, 5, '4', '破魔之弓', NULL, NULL);
INSERT INTO `weapon` VALUES (160, 5, '4', '曚云之月', NULL, NULL);
INSERT INTO `weapon` VALUES (161, 5, '4', '王下近侍', NULL, NULL);
INSERT INTO `weapon` VALUES (162, 5, '4', '竭泽', NULL, NULL);
INSERT INTO `weapon` VALUES (163, 5, '4', '烈阳之嗣', NULL, NULL);
INSERT INTO `weapon` VALUES (164, 5, '4', '静谧之曲', NULL, NULL);
INSERT INTO `weapon` VALUES (165, 5, '4', '测距规', NULL, NULL);
INSERT INTO `weapon` VALUES (166, 5, '5', '天空之翼', '674攻击力22.1%暴率', NULL);
INSERT INTO `weapon` VALUES (167, 5, '5', '阿莫斯之弓', '608攻击力49.6%攻击', NULL);
INSERT INTO `weapon` VALUES (168, 5, '5', '终末嗟叹之弓', '608攻击力55.1%充能', NULL);
INSERT INTO `weapon` VALUES (169, 5, '5', '飞雷之弦振', '608攻击力66.2%爆伤', NULL);
INSERT INTO `weapon` VALUES (170, 5, '5', '冬极白星', '608攻击力33.1%暴率', NULL);
INSERT INTO `weapon` VALUES (171, 5, '5', '若水', '542攻击力88.2%爆伤', NULL);
INSERT INTO `weapon` VALUES (172, 5, '5', '猎人之径', '542攻击力44.1%暴率', NULL);
INSERT INTO `weapon` VALUES (173, 5, '5', '最初的大魔术', '608攻击力66.2爆伤', NULL);
INSERT INTO `weapon` VALUES (174, 5, '5', '白雨心弦', NULL, NULL);

DROP TABLE IF EXISTS `weapon_type`;
CREATE TABLE `weapon_type`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `weapon_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '武器类型',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `weapom_type`(`weapon_type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `weapon_type` VALUES (2, '单手剑');
INSERT INTO `weapon_type` VALUES (1, '双手剑');
INSERT INTO `weapon_type` VALUES (5, '弓');
INSERT INTO `weapon_type` VALUES (4, '法器');
INSERT INTO `weapon_type` VALUES (3, '长柄武器');

SET FOREIGN_KEY_CHECKS = 1;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242
  • 243
  • 244
  • 245
  • 246
  • 247
  • 248
  • 249
  • 250
  • 251
  • 252
  • 253
  • 254
  • 255
  • 256
  • 257
  • 258
  • 259
  • 260
  • 261
  • 262
  • 263
  • 264
  • 265
  • 266
  • 267
  • 268
  • 269
  • 270
  • 271
  • 272
  • 273
  • 274
  • 275
  • 276
  • 277
  • 278
  • 279
  • 280
  • 281
  • 282
  • 283
  • 284
  • 285
  • 286
  • 287
  • 288
  • 289
  • 290
  • 291
  • 292
  • 293
  • 294
  • 295
  • 296
  • 297
  • 298
  • 299
  • 300
  • 301
  • 302
  • 303
  • 304
  • 305
  • 306
  • 307
  • 308
  • 309
  • 310
  • 311
  • 312
  • 313
  • 314
  • 315
  • 316
  • 317
  • 318
  • 319
  • 320
  • 321
  • 322
  • 323
  • 324
  • 325
  • 326
  • 327
  • 328
  • 329
  • 330
  • 331
  • 332
  • 333
  • 334
  • 335
  • 336
  • 337
  • 338
  • 339
  • 340
  • 341
  • 342
  • 343
  • 344
  • 345
  • 346
  • 347
  • 348
  • 349
  • 350
  • 351
  • 352
  • 353
  • 354
  • 355
  • 356
  • 357
  • 358
  • 359
  • 360
  • 361
  • 362
  • 363
  • 364
  • 365
  • 366
  • 367
  • 368
  • 369
  • 370
  • 371
  • 372
  • 373
  • 374
  • 375
  • 376
  • 377
  • 378
  • 379
  • 380
  • 381
  • 382
  • 383
  • 384
  • 385
  • 386
  • 387
  • 388
  • 389
  • 390
  • 391
  • 392
  • 393
  • 394
  • 395
  • 396
  • 397
  • 398
  • 399
  • 400
  • 401
  • 402
  • 403
  • 404
  • 405
  • 406
  • 407
  • 408
  • 409
  • 410
  • 411
  • 412
  • 413
  • 414
  • 415
  • 416
  • 417
  • 418
  • 419
  • 420
  • 421
  • 422
  • 423
  • 424
  • 425
  • 426
  • 427
  • 428
  • 429
  • 430
  • 431
  • 432
  • 433
  • 434
  • 435

查询语句

	-- 简单查询
-- 查询每个角色的名称,等级,各项属性,武器,武器等级,武器类型,圣遗物,圣遗物相关属性
select u.userName '角色名',u.rarity '角色等级',u.dendro '属性',w.weapon_name '武器',w.rarity '武器等级',
wt.weapon_type '武器类型',a.artifacts_name '圣遗物',ast.flower_of_life '生之花',ast.plume_of_death '死之羽',ast.sands_of_eon '时之沙',ast.goblet_of_eonothem '空之杯',ast.circlet_of_logos '理之冠' 
from `user` u 
left join weapon w on u.weapon_id=w.id
left join weapon_type wt on u.weapon_type_id=wt.id 
left join artifacts a on u.artifacts_id = a.id
left join artifacts_select ast on u.ID = ast.user_id;


-- 索引 查询武器类型编号为2的角色
explain select  from `user` where weapon_type_id='2';

-- 聚合函数 查询各元素角色数量
select dendro,count() '各元素角色数量' from `user` group by dendro order by count() desc;


-- 复杂查询
-- 查询排除魈后的 五星 风系 使用法器或单手剑或长枪的 角色和角色的武器类型
select u.userName,wt.weapon_type from `user` u 
join weapon_type wt
on u.weapon_type_id = wt.id
where u.rarity  4 
and u.dendro = '风' 
and u.userName != '魈'
and wt.weapon_type in ('法器','单手剑','长枪');

-- 按元素分组查询各元素所有五星角色的死之羽圣遗物的总攻击力
select dendro '元素',sum(a.plume_of_death) '各元素所有五星角色的死之羽总攻击力' from `user` u
join artifacts_select  a
on a.user_id =u.ID
where rarity 4 
group by dendro 
order by sum(a.plume_of_death) desc;

-- 查询每个角色所使用的武器类型及其对应的平均星级,根据平均星级倒序排列
SELECT 
    u.userName,
    wt.weapon_type,
    AVG(w.rarity) AS avg_rarity
FROM 
    user u
JOIN 
    weapon w ON u.weapon_id = w.id
JOIN 
    weapon_type wt ON w.weapon_type_id = wt.id
GROUP BY 
    u.userName, wt.weapon_type
ORDER BY 
    u.userName, avg_rarity DESC;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52

存储过程/触发器

user表插入限制

drop procedure if exists insert_user;
DELIMITER $$
CREATE PROCEDURE insert_user(
    in userName VARCHAR(255), 
    IN weapon_type INT,
    IN dendro VARCHAR(255),   
		in rarity int,
		in user_level int
)
BEGIN
if user_level>90 OR user_level<0 then
	signal sqlstate '45000' set message_text='等级错误';
end if;
    INSERT INTO user (userName, weapon_type_id, dendro,rarity,user_level) VALUES (userName, weapon_type, dendro,rarity,user_level);
END$$

DELIMITER ;

call insert_user('胡桃',1,'火',4,70);

#weapon武器表存储过程

drop procedure if exists insert_weapon;
DELIMITER $$
CREATE PROCEDURE insert_weapon(
    in weapon_type_id int,  
    IN rarity INT,        
    IN weapon_name VARCHAR(255),    
		in weapon_details varchar(255)
)
BEGIN
if	(weapon_type_id < 0 OR weapon_type_id > 5) OR (rarity < 0 OR rarity > 5) then
	signal sqlstate '45000' set message_text='超出类型id限制(1-5)';
end if;
    INSERT INTO weapon (weapon_type_id,rarity,weapon_name,weapon_details) VALUES (weapon_type_id,rarity,weapon_name,weapon_details);
END$$

DELIMITER ;

call insert_weapon(1,4,'测试1','测试1-1');
call insert_weapon(1,7,'错误测试2','错误测试2-1');

-- 圣遗物表存储过程
drop procedure if exists insert_artifacts;
DELIMITER $$
CREATE PROCEDURE insert_artifacts(        
    IN artifacts_name VARCHAR(255),    
		in set_bonus varchar(255)
)
BEGIN
    INSERT INTO artifacts (artifacts_name,set_bonus) VALUES (artifacts_name,set_bonus);
END$$

DELIMITER ;

call insert_artifacts('测试1','测试1');

# 等级更新限制

drop trigger if exists before_update_user;
DELIMITER $$
CREATE TRIGGER before_update_user
BEFORE UPDATE ON `user`
FOR EACH ROW
BEGIN
	IF new.user_level>90 OR new.user_level<0 THEN
		SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = '更新失败:用户等级不能大于90或小于0';
	END IF;
END$$
DELIMITER ;

-- 武器更新限制
drop trigger if exists before_update_weapon;
DELIMITER $$
CREATE TRIGGER before_update_weapon
BEFORE UPDATE ON `weapon`
FOR EACH ROW
BEGIN
		IF new.weapon_level>90 OR new.weapon_level<0 THEN
				-- 如果大于100,则抛出错误
				SIGNAL SQLSTATE '45000'
				SET MESSAGE_TEXT = '更新失败:武器等级不能大于90或小于0';
		END IF;
END$$
DELIMITER ;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/774025
推荐阅读
相关标签
  

闽ICP备14008679号