柚子快報(bào)激活碼778899分享：MySQL實(shí)訓(xùn)--原神數(shù)據(jù)庫(kù)

http://yzkb.51969.com/

原神數(shù)據(jù)庫(kù)

er圖DDL/DML語句查詢語句存儲(chǔ)過程/觸發(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, '冒險(xiǎn)家', '開啟各類寶箱后的5秒內(nèi)，持續(xù)恢復(fù)30%生命值。');

INSERT INTO `artifacts` VALUES (2, '幸運(yùn)兒', '拾取摩拉時(shí)，恢復(fù)300點(diǎn)生命值。');

INSERT INTO `artifacts` VALUES (3, '游醫(yī)', '釋放元素爆發(fā)時(shí)，恢復(fù)20%生命值。');

INSERT INTO `artifacts` VALUES (4, '行者之心', '重?fù)舻谋袈侍嵘?0%。');

INSERT INTO `artifacts` VALUES (5, '奇跡', '受到某個(gè)元素類型的傷害后，相應(yīng)的抗性提升30%，持續(xù)10秒。該效果每10秒只能觸發(fā)一次。');

INSERT INTO `artifacts` VALUES (6, '戰(zhàn)狂', '生命值低于70%時(shí)，暴擊率額外提升24%。');

INSERT INTO `artifacts` VALUES (7, '教官', '觸發(fā)元素反應(yīng)后，隊(duì)伍中所有角色的元素精通提高120點(diǎn)，持續(xù)8秒。');

INSERT INTO `artifacts` VALUES (8, '流放者', '施放元素爆發(fā)后，每2秒為隊(duì)伍中所有角色(不包括自己)恢復(fù)2點(diǎn)元素能量。該效果持續(xù)6秒，無法疊加。');

INSERT INTO `artifacts` VALUES (9, '守護(hù)之心', '隊(duì)伍里每有不同—種元素類型的自己的角色，自身獲得30%相應(yīng)的元素抗性。');

INSERT INTO `artifacts` VALUES (10, '勇士之心', '對(duì)生命值高于50%的敵人，造成的傷害增加30%。');

INSERT INTO `artifacts` VALUES (11, '武人', '釋放元素戰(zhàn)技后的8秒內(nèi)，普通攻擊和重?fù)粼斐傻膫μ嵘?5%。');

INSERT INTO `artifacts` VALUES (12, '賭徒', '打倒敵人時(shí)，有100%概率清除元素戰(zhàn)技的冷卻時(shí)間。該效果每15秒至多觸發(fā)—次。');

INSERT INTO `artifacts` VALUES (13, '學(xué)士', '獲得元素微?；蛟鼐驎r(shí)，隊(duì)伍中所有弓箭和法器角色額外恢復(fù)3點(diǎn)元素能量。該效果每3秒只能觸發(fā)一次。');

INSERT INTO `artifacts` VALUES (14, '祭火之人', '受到的火元素附著效果的持續(xù)時(shí)間減少40%。');

INSERT INTO `artifacts` VALUES (15, '祭水之人', '受到的水元素附著效果的持續(xù)時(shí)間減少40%。');

INSERT INTO `artifacts` VALUES (16, '祭雷之人', '受到的雷元素附著效果的持續(xù)時(shí)間減少40%。');

INSERT INTO `artifacts` VALUES (17, '祭冰之人', '受到的冰元素附著效果的持續(xù)時(shí)間減少40%。');

INSERT INTO `artifacts` VALUES (18, '冰封迷途的勇士', '攻擊處于冰元素影響下的敵人時(shí)，暴擊率提高20%;若敵人處于凍結(jié)狀態(tài)下，則暴擊率額外提高20%。');

INSERT INTO `artifacts` VALUES (19, '平息鳴雷的尊者', '對(duì)處于雷元素影響下的敵人造成的傷害提升35%。');

INSERT INTO `artifacts` VALUES (20, '渡過烈火的賢人', '對(duì)處于火元素影響下的敵人造成的傷害提升35%。');

INSERT INTO `artifacts` VALUES (21, '被憐愛的少女', '施放元素戰(zhàn)技或元素爆發(fā)后的10秒內(nèi)，隊(duì)伍中所有角色受治療效果加成提高20%。');

INSERT INTO `artifacts` VALUES (22, '角斗士的終幕禮', '裝備該圣遺物套裝的角色為單手劍、雙手劍、長(zhǎng)柄武器角色時(shí)，角色普通攻擊造成的傷害提高35%。');

INSERT INTO `artifacts` VALUES (23, '翠綠之影', '擴(kuò)散反應(yīng)造成的傷害提升60%。根據(jù)擴(kuò)散的元素類型，降低受到影響的敵人40%的對(duì)應(yīng)元素抗性，持續(xù)10秒。');

INSERT INTO `artifacts` VALUES (24, '流浪大地的樂章', '裝備該圣遺物套裝的角色為法器、弓箭角色時(shí)，角色重?fù)粼斐傻膫μ岣?5%。');

INSERT INTO `artifacts` VALUES (25, '如雷的盛怒', '超載、感電、超導(dǎo)、超綻放反應(yīng)造成的傷害提升40%，超激化反應(yīng)帶來的傷害提升提高20%。觸發(fā)上述元素反應(yīng)或原激化反應(yīng)時(shí)，元素戰(zhàn)技冷卻時(shí)間減少1秒。該效果每0.8秒最多觸發(fā)—次。');

INSERT INTO `artifacts` VALUES (26, '熾烈的炎之魔女', '超載、燃燒、烈綻放反應(yīng)造成的傷害提升40%，蒸發(fā)、融化反應(yīng)的加成系數(shù)提高15%。施放元素戰(zhàn)技后的10秒內(nèi)，2件套的效果提高50%，該效果最多疊加3次。');

INSERT INTO `artifacts` VALUES (27, '昔日宗室之儀', '釋放元素爆發(fā)后，隊(duì)伍中所有角色攻擊力提升20%，持續(xù)12秒，該效果不可疊加。');

INSERT INTO `artifacts` VALUES (28, '染血的騎士道', '擊敗敵人后的10秒內(nèi)，施放重?fù)魰r(shí)不消耗體力，且重?fù)粼斐傻膫μ嵘?0%。');

INSERT INTO `artifacts` VALUES (29, '悠古的磐巖', '獲得結(jié)晶反應(yīng)形成的晶片時(shí)，隊(duì)伍中所有角色獲得35%對(duì)應(yīng)元素傷害加成，持續(xù)10秒。同時(shí)只能通過該效果獲得一種元素傷害加成。');

INSERT INTO `artifacts` VALUES (30, '逆飛的流星', '處于護(hù)盾庇護(hù)下時(shí)，額外獲得40%普通攻擊和重?fù)魝映伞?);

INSERT INTO `artifacts` VALUES (31, '沉淪之心', '施放元素戰(zhàn)技后的15秒內(nèi)，普通攻擊與重?fù)粼斐傻膫μ岣?0%。');

INSERT INTO `artifacts` VALUES (32, '千巖牢固', '元素戰(zhàn)技命中敵人后，使隊(duì)伍中附近的所有角色攻擊力提升20%，護(hù)盾強(qiáng)效提升30%，持續(xù)3秒。該效果每0.5秒至多觸發(fā)一次。裝備此圣遺物套裝的角色處于隊(duì)伍后臺(tái)時(shí)，依然育能觸發(fā)該效果。');

INSERT INTO `artifacts` VALUES (33, '蒼白之火', '元素戰(zhàn)技命中敵人后，攻擊力提升9%。該效果持續(xù)7秒，至多疊加2層，每0.3秒至多觸發(fā)一次。疊滿2層時(shí)，2件套的效果提升100%。');

INSERT INTO `artifacts` VALUES (34, '追憶之注連', '釋放元素戰(zhàn)技時(shí)，如果角色的元素能星高于或等于15點(diǎn)，則會(huì)流失15點(diǎn)元素能量，使接下來的10秒內(nèi)，普通攻擊、重?fù)?、下落攻擊造成的傷害提?0%，持續(xù)期間內(nèi)效果不會(huì)再次觸發(fā)。');

INSERT INTO `artifacts` VALUES (35, '絕緣之旗印', '基于元素充能效率的25%，提高元素爆發(fā)造成的傷害。至多通過這種方式獲得75%提升。');

INSERT INTO `artifacts` VALUES (36, '華館夢(mèng)醒形骸記', '裝備此圣遺物套裝的角色在以下情況下，將獲得「問答」效果:在場(chǎng)上用巖元素攻擊命中敵人后獲得一層，每0.3秒至多觸發(fā)一次;在隊(duì)伍后臺(tái)中，每3秒獲得一層。問答至多疊加4層，每層能提供6%防御力與6%巖元素傷害加成。每6秒，若未獲得問答效果，將損失一層。');

INSERT INTO `artifacts` VALUES (37, '海染硨磲', '裝備此圣遺物套裝的角色對(duì)隊(duì)伍中的角色進(jìn)行治療時(shí)，將產(chǎn)生持續(xù)3秒的海染泡沫，記錄治療的生命值回復(fù)量（包括溢出值)。持續(xù)時(shí)間結(jié)束時(shí)，海染泡沫將會(huì)爆炸，對(duì)周圍的敵人造成90%累計(jì)回復(fù)量的傷害（該傷害結(jié)算方式同感電、超導(dǎo)等元素反應(yīng)，但不受元素精通、等級(jí)或反應(yīng)傷害加成效果影響)。每3.5秒至多產(chǎn)生一個(gè)海染泡沬;海染泡沫至多記錄30000點(diǎn)回復(fù)量，含溢出部分的治療量;自己的隊(duì)伍中同時(shí)至多存在一個(gè)海染泡沫。裝備此圣遺物套裝的角色處于隊(duì)伍后臺(tái)時(shí);依然能觸發(fā)該效果。');

INSERT INTO `artifacts` VALUES (38, '辰砂往生錄', '施放元素爆發(fā)后，將產(chǎn)生持續(xù)16秒的「潛光」效果:攻擊力提升8%;并在角色的生命值降低時(shí)，攻擊力進(jìn)一步提升10%，至多通過這種方式提升4次，每0.8秒至多觸發(fā)一次?！笣摴狻剐Ч麑⒃诮巧藞?chǎng)時(shí)消失;持續(xù)期間再次施放元素爆發(fā)，將移除原有的「潛光」。');

INSERT INTO `artifacts` VALUES (39, '來歆余響', '普通攻擊命中敵人時(shí)，有36%概率觸發(fā)「幽谷祝祀」︰普通攻擊造成的傷害提高，傷害提高值為攻擊力的70%，該效果將在普通攻擊造成傷害后的0.05秒后清除。普通攻擊未觸發(fā)「幽谷祝祀」時(shí)，會(huì)使下次觸發(fā)概率提升20%;0.2秒內(nèi)至多判定1次觸發(fā)與否。');

INSERT INTO `artifacts` VALUES (40, '深林的記憶', '元素戰(zhàn)技或元素爆發(fā)命中敵人后，使命中目標(biāo)的草元素抗性降低30%，持續(xù)8秒。裝備者處于隊(duì)伍后臺(tái)時(shí)，依然能觸發(fā)該效果。');

INSERT INTO `artifacts` VALUES (41, '飾金之夢(mèng)', '觸發(fā)元素反應(yīng)后的8秒內(nèi)，會(huì)根據(jù)隊(duì)伍內(nèi)其他角色的元素類型，使裝備者獲得強(qiáng)化:隊(duì)伍中每存在1個(gè)和裝備者同類元素的角色，攻擊力提升14%;每存在1個(gè)和裝備者不同元素類型的角色，元素精通提升50點(diǎn)。上述每類效果至多計(jì)算3個(gè)角色。該效果每8秒至多觸發(fā)一次。裝備者處于隊(duì)伍后臺(tái)時(shí)，依然能觸發(fā)該效果。');

INSERT INTO `artifacts` VALUES (42, '沙上樓閣史話', '重?fù)裘袛橙撕螅摻巧钠胀ü羲俣忍嵘?0%，普通攻擊、重?fù)襞c下落攻擊造成的傷害提升40%，持續(xù)15秒。');

INSERT INTO `artifacts` VALUES (43, '樂園遺落之花', '裝備者綻放、超綻放、烈綻放反應(yīng)造成的傷害提升40%。此外，裝備者觸發(fā)綻放、超綻放、烈綻放后，上述效果帶來的加成提升25%，該效果持續(xù)10秒，至多疊加4次，每1秒至多觸發(fā)一次。裝備者處于隊(duì)伍后臺(tái)時(shí)依然能觸發(fā)該效果。');

INSERT INTO `artifacts` VALUES (44, '水仙之夢(mèng)', '普通攻擊、重?fù)?、下落攻擊、元素?zhàn)技或元素爆發(fā)命中敵人后，將產(chǎn)生1層持續(xù)8秒的「鏡中水仙」效果。處于1/2/3層及以上「鏡中水仙」效果下時(shí)，攻擊力將提高7%/16%/25%，水元素傷害加成提升4%/9%/15%。由普通攻擊、重?fù)?、下落攻擊、元素?zhàn)技或元素爆發(fā)產(chǎn)生的「鏡中水仙」將分別獨(dú)立存在。');

INSERT INTO `artifacts` VALUES (45, '花海甘露之光', '元素戰(zhàn)技與元素爆發(fā)造成的傷害提升10%;裝備者受到傷害后的5秒內(nèi)，上述傷害提升效果提高80%。該提高效果至多疊加5層。每層持續(xù)時(shí)間獨(dú)立計(jì)算，處于隊(duì)伍后臺(tái)時(shí)依然能觸發(fā)該效果。');

INSERT INTO `artifacts` VALUES (46, '逐影獵人', '當(dāng)前生命值提升或降低時(shí)，暴擊率提升12%，該效果持續(xù)5秒，至多疊加3次。');

INSERT INTO `artifacts` VALUES (47, '黃金劇團(tuán)', '元素戰(zhàn)技造成的傷害提升25%;此外，處于隊(duì)伍后臺(tái)時(shí)，元素戰(zhàn)技造成的傷害還將進(jìn)一步提升25%，該效果將在登場(chǎng)后2秒移除。');

INSERT INTO `artifacts` VALUES (48, '昔時(shí)之歌', '裝備者對(duì)隊(duì)伍中的角色進(jìn)行治療時(shí)將產(chǎn)生持續(xù)6秒的渴盼效果，記錄治療的生命值回復(fù)量（包括溢出值)。持續(xù)時(shí)間結(jié)束時(shí)，渴盼效果將轉(zhuǎn)變?yōu)椤副藭r(shí)的浪潮」效果:隊(duì)伍中自己的當(dāng)前場(chǎng)上角色的普通攻擊、重?fù)?、下落攻擊、元素?zhàn)技與元素爆發(fā)命中敵人時(shí)，將基于渴盼效果所記錄的回復(fù)量的8%提高造成的傷害，「彼時(shí)的浪潮」將在生效5次或10秒后移除。一次渴盼效果至多記錄15000點(diǎn)回復(fù)量，同時(shí)至多存在一個(gè)能夠記錄多個(gè)裝備者的產(chǎn)生的回復(fù)量;裝備者處于隊(duì)伍后臺(tái)時(shí)，依然能觸發(fā)該效果。');

INSERT INTO `artifacts` VALUES (49, '回聲之林夜話', '施放元素戰(zhàn)技后的10秒內(nèi)，巖元素傷害加成提升20%;若處于結(jié)晶反應(yīng)產(chǎn)生的護(hù)盾庇護(hù)下，上述效果提高150%，進(jìn)一步提高的效果將在失去結(jié)晶護(hù)盾庇護(hù)的1秒后移除。');

INSERT INTO `artifacts` VALUES (50, '諧律異想斷章', '生命之契的數(shù)值提升或降低時(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)下的敵人時(shí)，每秒提升10%，直到達(dá)到50%。裝備此圣遺物套裝的角色處于隊(duì)伍后臺(tái)時(shí)，依然會(huì)觸發(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 '時(shí)之沙',

`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%風(fēng)杯', '31.1%暴率');

INSERT INTO `artifacts_select` VALUES (7, 42, 56, '4780', '311', '46.6%攻擊', '46.6%風(fēng)杯', '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 '武器等級(jí)',

`artifacts_id` int DEFAULT NULL COMMENT '圣遺物id',

`user_level` int DEFAULT NULL COMMENT '角色等級(jí)',

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, '風(fēng)', '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, '風(fēng)', '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, '風(fēng)', '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, '風(fēng)', '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, '風(fēng)', '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, '風(fēng)', '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, '風(fēng)', '5', NULL, NULL, NULL, NULL);

INSERT INTO `user` VALUES (47, '可莉', 4, '火', '5', NULL, NULL, NULL, NULL);

INSERT INTO `user` VALUES (48, '達(dá)達(dá)利亞', 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, '風(fēng)', '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, '風(fēng)', '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, '風(fēng)', '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, '風(fēng)', '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', '訓(xùn)練大劍', '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', '西風(fēng)大劍', '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', '桂木斬長(zhǎng)正', '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', '便攜動(dòng)力鋸', '454攻擊力55.1%生命', NULL);

INSERT INTO `weapon` VALUES (26, 1, '4', '究極霸王超級(jí)魔劍', '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', '松籟響起之時(shí)', '741攻擊力20.7物傷', NULL);

INSERT INTO `weapon` VALUES (31, 1, '5', '赤角石潰杵', '542攻擊力88.2爆傷', NULL);

INSERT INTO `weapon` VALUES (32, 1, '5', '葦海信標(biāo)', '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', '西風(fēng)劍', '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', '宗室長(zhǎng)劍', '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', '黑巖長(zhǎng)劍', '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', '船塢長(zhǎng)劍', '510攻擊力41.3%生命', NULL);

INSERT INTO `weapon` VALUES (60, 2, '4', '水仙十字圣劍', '510攻擊力41.3%攻擊', NULL);

INSERT INTO `weapon` VALUES (61, 2, '5', '風(fēng)鷹劍', '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', '新手長(zhǎng)槍', 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', '西風(fēng)長(zhǎng)槍', NULL, NULL);

INSERT INTO `weapon` VALUES (84, 3, '4', '宗室獵槍', NULL, NULL);

INSERT INTO `weapon` VALUES (86, 3, '4', '龍脊長(zhǎng)槍', NULL, NULL);

INSERT INTO `weapon` VALUES (87, 3, '4', '千巖長(zhǎng)槍', 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', '斷浪長(zhǎng)鰭', NULL, NULL);

INSERT INTO `weapon` VALUES (91, 3, '4', '貫月矢', NULL, NULL);

INSERT INTO `weapon` VALUES (92, 3, '4', '峽灣長(zhǎng)歌', NULL, NULL);

INSERT INTO `weapon` VALUES (93, 3, '4', '公義的報(bào)酬', NULL, NULL);

INSERT INTO `weapon` VALUES (94, 3, '4', '勘探鉆機(jī)', NULL, NULL);

INSERT INTO `weapon` VALUES (95, 3, '4', '沙中偉賢的對(duì)答', 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', '護(hù)摩之杖', '608攻擊力66.2%爆傷', NULL);

INSERT INTO `weapon` VALUES (100, 3, '5', '薙草之稻光', '608攻擊力55.1%充能', NULL);

INSERT INTO `weapon` VALUES (101, 3, '5', '息災(zāi)', '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', '學(xué)徒筆記', NULL, NULL);

INSERT INTO `weapon` VALUES (105, 4, '2', '魔導(dǎo)緒論', 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', '甲級(jí)寶鈺', NULL, NULL);

INSERT INTO `weapon` VALUES (110, 4, '4', '西風(fēng)秘典', 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', '萬國(guó)諸海圖譜', 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', '暗巷的酒與詩(shī)', NULL, NULL);

INSERT INTO `weapon` VALUES (123, 4, '4', '白辰之環(huán)', NULL, NULL);

INSERT INTO `weapon` VALUES (124, 4, '4', '盈滿之實(shí)', 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', '無垠蔚藍(lán)之歌', NULL, NULL);

INSERT INTO `weapon` VALUES (129, 4, '5', '天空之卷', '674攻擊力33.1攻擊', NULL);

INSERT INTO `weapon` VALUES (130, 4, '5', '四風(fēng)原典', '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', '千夜浮夢(mèng)', '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', '西風(fēng)獵弓', 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', '宗室長(zhǎng)弓', 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', '烈陽(yáng)之嗣', NULL, NULL);

INSERT INTO `weapon` VALUES (164, 5, '4', '靜謐之曲', NULL, NULL);

INSERT INTO `weapon` VALUES (165, 5, '4', '測(cè)距規(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, '長(zhǎng)柄武器');

SET FOREIGN_KEY_CHECKS = 1;

查詢語句

-- 簡(jiǎn)單查詢

-- 查詢每個(gè)角色的名稱，等級(jí)，各項(xiàng)屬性，武器，武器等級(jí)，武器類型，圣遺物，圣遺物相關(guān)屬性

select u.userName '角色名',u.rarity '角色等級(jí)',u.dendro '屬性',w.weapon_name '武器',w.rarity '武器等級(jí)',

wt.weapon_type '武器類型',a.artifacts_name '圣遺物',ast.flower_of_life '生之花',ast.plume_of_death '死之羽',ast.sands_of_eon '時(shí)之沙',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;

-- 索引 查詢武器類型編號(hào)為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;

-- 復(fù)雜查詢

-- 查詢排除魈后的 五星 風(fēng)系 使用法器或單手劍或長(zhǎng)槍的 角色和角色的武器類型

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 = '風(fēng)'

and u.userName != '魈'

and wt.weapon_type in ('法器','單手劍','長(zhǎng)槍');

-- 按元素分組查詢各元素所有五星角色的死之羽圣遺物的總攻擊力

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;

-- 查詢每個(gè)角色所使用的武器類型及其對(duì)應(yīng)的平均星級(jí)，根據(jù)平均星級(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;

存儲(chǔ)過程/觸發(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='等級(jí)錯(cuò)誤';

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武器表存儲(chǔ)過程

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,'測(cè)試1','測(cè)試1-1');

call insert_weapon(1,7,'錯(cuò)誤測(cè)試2','錯(cuò)誤測(cè)試2-1');

-- 圣遺物表存儲(chǔ)過程

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('測(cè)試1','測(cè)試1');

# 等級(jí)更新限制

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 = '更新失敗：用戶等級(jí)不能大于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，則拋出錯(cuò)誤

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = '更新失敗：武器等級(jí)不能大于90或小于0';

END IF;

END$$

DELIMITER ;

柚子快報(bào)激活碼778899分享：MySQL實(shí)訓(xùn)--原神數(shù)據(jù)庫(kù)

http://yzkb.51969.com/

好文閱讀