柚子快報激活碼778899分享：MySQL實訓--原神數(shù)據(jù)庫
柚子快報激活碼778899分享：MySQL實訓--原神數(shù)據(jù)庫
原神數(shù)據(jù)庫
er圖DDL/DML語句查詢語句存儲過程/觸發(fā)器
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秒內(nèi)，持續(xù)恢復30%生命值。');
INSERT INTO `artifacts` VALUES (2, '幸運兒', '拾取摩拉時，恢復300點生命值。');
INSERT INTO `artifacts` VALUES (3, '游醫(yī)', '釋放元素爆發(fā)時，恢復20%生命值。');
INSERT INTO `artifacts` VALUES (4, '行者之心', '重擊的暴擊率提升30%。');
INSERT INTO `artifacts` VALUES (5, '奇跡', '受到某個元素類型的傷害后，相應的抗性提升30%，持續(xù)10秒。該效果每10秒只能觸發(fā)一次。');
INSERT INTO `artifacts` VALUES (6, '戰(zhàn)狂', '生命值低于70%時，暴擊率額外提升24%。');
INSERT INTO `artifacts` VALUES (7, '教官', '觸發(fā)元素反應后，隊伍中所有角色的元素精通提高120點，持續(xù)8秒。');
INSERT INTO `artifacts` VALUES (8, '流放者', '施放元素爆發(fā)后，每2秒為隊伍中所有角色(不包括自己)恢復2點元素能量。該效果持續(xù)6秒，無法疊加。');
INSERT INTO `artifacts` VALUES (9, '守護之心', '隊伍里每有不同—種元素類型的自己的角色，自身獲得30%相應的元素抗性。');
INSERT INTO `artifacts` VALUES (10, '勇士之心', '對生命值高于50%的敵人，造成的傷害增加30%。');
INSERT INTO `artifacts` VALUES (11, '武人', '釋放元素戰(zhàn)技后的8秒內(nèi)，普通攻擊和重擊造成的傷害提升25%。');
INSERT INTO `artifacts` VALUES (12, '賭徒', '打倒敵人時，有100%概率清除元素戰(zhàn)技的冷卻時間。該效果每15秒至多觸發(fā)—次。');
INSERT INTO `artifacts` VALUES (13, '學士', '獲得元素微?；蛟鼐驎r，隊伍中所有弓箭和法器角色額外恢復3點元素能量。該效果每3秒只能觸發(fā)一次。');
INSERT INTO `artifacts` VALUES (14, '祭火之人', '受到的火元素附著效果的持續(xù)時間減少40%。');
INSERT INTO `artifacts` VALUES (15, '祭水之人', '受到的水元素附著效果的持續(xù)時間減少40%。');
INSERT INTO `artifacts` VALUES (16, '祭雷之人', '受到的雷元素附著效果的持續(xù)時間減少40%。');
INSERT INTO `artifacts` VALUES (17, '祭冰之人', '受到的冰元素附著效果的持續(xù)時間減少40%。');
INSERT INTO `artifacts` VALUES (18, '冰封迷途的勇士', '攻擊處于冰元素影響下的敵人時，暴擊率提高20%;若敵人處于凍結(jié)狀態(tài)下，則暴擊率額外提高20%。');
INSERT INTO `artifacts` VALUES (19, '平息鳴雷的尊者', '對處于雷元素影響下的敵人造成的傷害提升35%。');
INSERT INTO `artifacts` VALUES (20, '渡過烈火的賢人', '對處于火元素影響下的敵人造成的傷害提升35%。');
INSERT INTO `artifacts` VALUES (21, '被憐愛的少女', '施放元素戰(zhàn)技或元素爆發(fā)后的10秒內(nèi)，隊伍中所有角色受治療效果加成提高20%。');
INSERT INTO `artifacts` VALUES (22, '角斗士的終幕禮', '裝備該圣遺物套裝的角色為單手劍、雙手劍、長柄武器角色時，角色普通攻擊造成的傷害提高35%。');
INSERT INTO `artifacts` VALUES (23, '翠綠之影', '擴散反應造成的傷害提升60%。根據(jù)擴散的元素類型，降低受到影響的敵人40%的對應元素抗性，持續(xù)10秒。');
INSERT INTO `artifacts` VALUES (24, '流浪大地的樂章', '裝備該圣遺物套裝的角色為法器、弓箭角色時，角色重擊造成的傷害提高35%。');
INSERT INTO `artifacts` VALUES (25, '如雷的盛怒', '超載、感電、超導、超綻放反應造成的傷害提升40%，超激化反應帶來的傷害提升提高20%。觸發(fā)上述元素反應或原激化反應時，元素戰(zhàn)技冷卻時間減少1秒。該效果每0.8秒最多觸發(fā)—次。');
INSERT INTO `artifacts` VALUES (26, '熾烈的炎之魔女', '超載、燃燒、烈綻放反應造成的傷害提升40%，蒸發(fā)、融化反應的加成系數(shù)提高15%。施放元素戰(zhàn)技后的10秒內(nèi)，2件套的效果提高50%，該效果最多疊加3次。');
INSERT INTO `artifacts` VALUES (27, '昔日宗室之儀', '釋放元素爆發(fā)后，隊伍中所有角色攻擊力提升20%，持續(xù)12秒，該效果不可疊加。');
INSERT INTO `artifacts` VALUES (28, '染血的騎士道', '擊敗敵人后的10秒內(nèi)，施放重擊時不消耗體力，且重擊造成的傷害提升50%。');
INSERT INTO `artifacts` VALUES (29, '悠古的磐巖', '獲得結(jié)晶反應形成的晶片時，隊伍中所有角色獲得35%對應元素傷害加成，持續(xù)10秒。同時只能通過該效果獲得一種元素傷害加成。');
INSERT INTO `artifacts` VALUES (30, '逆飛的流星', '處于護盾庇護下時，額外獲得40%普通攻擊和重擊傷害加成。');
INSERT INTO `artifacts` VALUES (31, '沉淪之心', '施放元素戰(zhàn)技后的15秒內(nèi)，普通攻擊與重擊造成的傷害提高30%。');
INSERT INTO `artifacts` VALUES (32, '千巖牢固', '元素戰(zhàn)技命中敵人后，使隊伍中附近的所有角色攻擊力提升20%，護盾強效提升30%，持續(xù)3秒。該效果每0.5秒至多觸發(fā)一次。裝備此圣遺物套裝的角色處于隊伍后臺時，依然育能觸發(fā)該效果。');
INSERT INTO `artifacts` VALUES (33, '蒼白之火', '元素戰(zhàn)技命中敵人后，攻擊力提升9%。該效果持續(xù)7秒，至多疊加2層，每0.3秒至多觸發(fā)一次。疊滿2層時，2件套的效果提升100%。');
INSERT INTO `artifacts` VALUES (34, '追憶之注連', '釋放元素戰(zhàn)技時，如果角色的元素能星高于或等于15點，則會流失15點元素能量，使接下來的10秒內(nèi)，普通攻擊、重擊、下落攻擊造成的傷害提高50%，持續(xù)期間內(nèi)效果不會再次觸發(fā)。');
INSERT INTO `artifacts` VALUES (35, '絕緣之旗印', '基于元素充能效率的25%，提高元素爆發(fā)造成的傷害。至多通過這種方式獲得75%提升。');
INSERT INTO `artifacts` VALUES (36, '華館夢醒形骸記', '裝備此圣遺物套裝的角色在以下情況下，將獲得「問答」效果:在場上用巖元素攻擊命中敵人后獲得一層，每0.3秒至多觸發(fā)一次;在隊伍后臺中，每3秒獲得一層。問答至多疊加4層，每層能提供6%防御力與6%巖元素傷害加成。每6秒，若未獲得問答效果，將損失一層。');
INSERT INTO `artifacts` VALUES (37, '海染硨磲', '裝備此圣遺物套裝的角色對隊伍中的角色進行治療時，將產(chǎn)生持續(xù)3秒的海染泡沫，記錄治療的生命值回復量（包括溢出值)。持續(xù)時間結(jié)束時，海染泡沫將會爆炸，對周圍的敵人造成90%累計回復量的傷害（該傷害結(jié)算方式同感電、超導等元素反應，但不受元素精通、等級或反應傷害加成效果影響)。每3.5秒至多產(chǎn)生一個海染泡沬;海染泡沫至多記錄30000點回復量，含溢出部分的治療量;自己的隊伍中同時至多存在一個海染泡沫。裝備此圣遺物套裝的角色處于隊伍后臺時;依然能觸發(fā)該效果。');
INSERT INTO `artifacts` VALUES (38, '辰砂往生錄', '施放元素爆發(fā)后，將產(chǎn)生持續(xù)16秒的「潛光」效果:攻擊力提升8%;并在角色的生命值降低時，攻擊力進一步提升10%，至多通過這種方式提升4次，每0.8秒至多觸發(fā)一次?！笣摴狻剐Ч麑⒃诮巧藞鰰r消失;持續(xù)期間再次施放元素爆發(fā)，將移除原有的「潛光」。');
INSERT INTO `artifacts` VALUES (39, '來歆余響', '普通攻擊命中敵人時，有36%概率觸發(fā)「幽谷祝祀」︰普通攻擊造成的傷害提高，傷害提高值為攻擊力的70%，該效果將在普通攻擊造成傷害后的0.05秒后清除。普通攻擊未觸發(fā)「幽谷祝祀」時，會使下次觸發(fā)概率提升20%;0.2秒內(nèi)至多判定1次觸發(fā)與否。');
INSERT INTO `artifacts` VALUES (40, '深林的記憶', '元素戰(zhàn)技或元素爆發(fā)命中敵人后，使命中目標的草元素抗性降低30%，持續(xù)8秒。裝備者處于隊伍后臺時，依然能觸發(fā)該效果。');
INSERT INTO `artifacts` VALUES (41, '飾金之夢', '觸發(fā)元素反應后的8秒內(nèi)，會根據(jù)隊伍內(nèi)其他角色的元素類型，使裝備者獲得強化:隊伍中每存在1個和裝備者同類元素的角色，攻擊力提升14%;每存在1個和裝備者不同元素類型的角色，元素精通提升50點。上述每類效果至多計算3個角色。該效果每8秒至多觸發(fā)一次。裝備者處于隊伍后臺時，依然能觸發(fā)該效果。');
INSERT INTO `artifacts` VALUES (42, '沙上樓閣史話', '重擊命中敵人后，該角色的普通攻擊速度提升10%，普通攻擊、重擊與下落攻擊造成的傷害提升40%，持續(xù)15秒。');
INSERT INTO `artifacts` VALUES (43, '樂園遺落之花', '裝備者綻放、超綻放、烈綻放反應造成的傷害提升40%。此外，裝備者觸發(fā)綻放、超綻放、烈綻放后，上述效果帶來的加成提升25%，該效果持續(xù)10秒，至多疊加4次，每1秒至多觸發(fā)一次。裝備者處于隊伍后臺時依然能觸發(fā)該效果。');
INSERT INTO `artifacts` VALUES (44, '水仙之夢', '普通攻擊、重擊、下落攻擊、元素戰(zhàn)技或元素爆發(fā)命中敵人后，將產(chǎn)生1層持續(xù)8秒的「鏡中水仙」效果。處于1/2/3層及以上「鏡中水仙」效果下時，攻擊力將提高7%/16%/25%，水元素傷害加成提升4%/9%/15%。由普通攻擊、重擊、下落攻擊、元素戰(zhàn)技或元素爆發(fā)產(chǎn)生的「鏡中水仙」將分別獨立存在。');
INSERT INTO `artifacts` VALUES (45, '花海甘露之光', '元素戰(zhàn)技與元素爆發(fā)造成的傷害提升10%;裝備者受到傷害后的5秒內(nèi)，上述傷害提升效果提高80%。該提高效果至多疊加5層。每層持續(xù)時間獨立計算，處于隊伍后臺時依然能觸發(fā)該效果。');
INSERT INTO `artifacts` VALUES (46, '逐影獵人', '當前生命值提升或降低時，暴擊率提升12%，該效果持續(xù)5秒，至多疊加3次。');
INSERT INTO `artifacts` VALUES (47, '黃金劇團', '元素戰(zhàn)技造成的傷害提升25%;此外，處于隊伍后臺時，元素戰(zhàn)技造成的傷害還將進一步提升25%，該效果將在登場后2秒移除。');
INSERT INTO `artifacts` VALUES (48, '昔時之歌', '裝備者對隊伍中的角色進行治療時將產(chǎn)生持續(xù)6秒的渴盼效果，記錄治療的生命值回復量（包括溢出值)。持續(xù)時間結(jié)束時，渴盼效果將轉(zhuǎn)變?yōu)椤副藭r的浪潮」效果:隊伍中自己的當前場上角色的普通攻擊、重擊、下落攻擊、元素戰(zhàn)技與元素爆發(fā)命中敵人時，將基于渴盼效果所記錄的回復量的8%提高造成的傷害，「彼時的浪潮」將在生效5次或10秒后移除。一次渴盼效果至多記錄15000點回復量，同時至多存在一個能夠記錄多個裝備者的產(chǎn)生的回復量;裝備者處于隊伍后臺時，依然能觸發(fā)該效果。');
INSERT INTO `artifacts` VALUES (49, '回聲之林夜話', '施放元素戰(zhàn)技后的10秒內(nèi)，巖元素傷害加成提升20%;若處于結(jié)晶反應產(chǎn)生的護盾庇護下，上述效果提高150%，進一步提高的效果將在失去結(jié)晶護盾庇護的1秒后移除。');
INSERT INTO `artifacts` VALUES (50, '諧律異想斷章', '生命之契的數(shù)值提升或降低時角色造成的傷害提升18%，該效果持續(xù)6秒，至多疊加3次。');
INSERT INTO `artifacts` VALUES (51, '未竟的遐思', '脫離戰(zhàn)斗狀態(tài)3秒后，造成的傷害提升50%。在戰(zhàn)斗狀態(tài)下，附近不存在處于燃燒狀態(tài)下的敵人超過6秒后，上述傷害提升效果每秒降低10%直到降低至0%;存在處于燃燒狀態(tài)下的敵人時，每秒提升10%，直到達到50%。裝備此圣遺物套裝的角色處于隊伍后臺時，依然會觸發(fā)該效果。');
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, '優(yōu)菈', 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, '白術(shù)', 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, '執(zhí)行官散兵', 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', '磐巖結(jié)綠', '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', '波亂月白經(jīng)津', '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', '白辰之環(huán)', 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', '金流監(jiān)督', '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', '黑巖戰(zhàn)弓', 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', '測距規(guī)', 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', '最初的大魔術(shù)', '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;
查詢語句
-- 簡單查詢
-- 查詢每個角色的名稱，等級，各項屬性，武器，武器等級，武器類型，圣遺物，圣遺物相關(guān)屬性
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';
-- 聚合函數(shù) 查詢各元素角色數(shù)量
select dendro,count() '各元素角色數(shù)量' 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;
-- 查詢每個角色所使用的武器類型及其對應的平均星級，根據(jù)平均星級倒序排列
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;
存儲過程/觸發(fā)器
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 = '更新失?。河脩舻燃壊荒艽笥?0或小于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 = '更新失?。何淦鞯燃壊荒艽笥?0或小于0';
END IF;
END$$
DELIMITER ;
柚子快報激活碼778899分享：MySQL實訓--原神數(shù)據(jù)庫
好文閱讀
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點和立場。
轉(zhuǎn)載請注明，如有侵權(quán)，聯(lián)系刪除。
Grailed復古購物
