gto32132 發表於 2016-8-3 12:19

查詢資料庫SQL語法

SELECT
`npc`.`name` AS `怪物名稱`,
`npc`.`npcid` AS `怪物編號`,
`spawnlist`.`count` AS `隻數`,
`spawnlist`.`min_respawn_delay` AS `最小重生`,
`spawnlist`.`max_respawn_delay` AS `最大重生`,
`mapids`.`locationname` AS `地圖名稱`,
`mapids`.`mapid` AS `地圖編號`
FROM
`spawnlist`
Inner Join `npc` ON `spawnlist`.`npc_templateid` = `npc`.`npcid`
Inner Join `mapids` ON `spawnlist`.`mapid` = `mapids`.`mapid`
WHERE
`spawnlist`.`mapid` = '180'

輸入怪物名稱 查詢分布地區

SELECT
`npc`.`name` AS `怪物名稱`,
`npc`.`npcid` AS `怪物編號`,
`spawnlist`.`count` AS `隻數`,
`spawnlist`.`min_respawn_delay` AS `最小重生`,
`spawnlist`.`max_respawn_delay` AS `最大重生`,
`mapids`.`locationname` AS `地圖名稱`,
`mapids`.`mapid` AS `地圖編號`
FROM
`spawnlist`
Inner Join `npc` ON `spawnlist`.`npc_templateid` = `npc`.`npcid`
Inner Join `mapids` ON `spawnlist`.`mapid` = `mapids`.`mapid`
WHERE
`npc`.`name` = '死亡騎士'

輸入玩家名稱查詢 魔法狀態(變身名稱)

SELECT
`characters`.`char_name` AS `玩家名稱`,
`skills`.`name` AS `魔法名稱`,
`character_buff`.`remaining_time` AS `剩餘時間`,
`characters`.`objid` AS `玩家代碼`,
`character_buff`.`poly_id` AS `變身編號`,
`polymorphs`.`name` AS `變身名稱`
FROM
`character_buff`
Inner Join `characters` ON `character_buff`.`char_obj_id` = `characters`.`objid`
Inner Join `skills` ON `character_buff`.`skill_id` = `skills`.`skill_id`
Left Join `polymorphs` ON `character_buff`.`poly_id` = `polymorphs`.`id`
WHERE
`characters`.`char_name` = '線上GM'

輸入玩家名稱查詢 學習的魔法

SELECT
`characters`.`char_name` AS `玩家名稱`,
`characters`.`objid` AS `玩家代碼`,
`character_skills`.`skill_name` AS `魔法名稱`,
`character_skills`.`skill_id` AS `魔法編號`
FROM
`characters` ,
`character_skills`
WHERE
`characters`.`char_name` = '線上GM' AND
`characters`.`objid` = `character_skills`.`char_obj_id`
ORDER BY
`魔法編號` ASC

輸入玩家名稱 查詢 身上的道具(依照 道具的等級排序)

SELECT
`characters`.`char_name` AS `玩家名稱`,
`characters`.`level` AS `等級`,
`character_items`.`item_name` AS `持有道具`,
`character_items`.`enchantlvl` AS `道具等級`,
`character_items`.`count` AS `持有數量`,
`characters`.`MaxHp` AS `血`,
`characters`.`MaxMp` AS `魔`,
`characters`.`Str` AS `力量`,
`characters`.`Con` AS `體質`,
`characters`.`Dex` AS `魅力`,
`characters`.`Cha` AS `敏捷`,
`characters`.`Intel` AS `智力`,
`characters`.`Wis` AS `精神`
FROM
`characters` ,
`character_items`
WHERE
`characters`.`objid` = `character_items`.`char_id` AND
`characters`.`char_name` = '線上GM'
ORDER BY
`道具等級` DESC,
`持有數量` DESC,
`持有道具` ASC

輸入怪物編號 查詢 怪物掉落物品

SELECT
npc.name AS `怪物名稱`,
droplist.mobId AS `怪物編號`,
droplist.itemId AS `道具編號`,
weapon.name AS `武器名稱`,
armor.name AS `防具名稱`,
etcitem.name AS `道具名稱`,
droplist.chance AS `掉落機率`
FROM
droplist
Inner Join npc ON droplist.mobId = npc.npcid
Left Join weapon ON droplist.itemId = weapon.item_id
Left Join armor ON droplist.itemId = armor.item_id
Left Join etcitem ON droplist.itemId = etcitem.item_id
WHERE
droplist.mobId = '45601'

qoo99281 發表於 2016-8-6 00:07

這很實用

請問有沒有查詢所有怪物掉落的

montblanc1117 發表於 2016-11-10 23:11

推推~感謝大大無私分享

etit00001 發表於 2016-11-22 19:55

SQL的語法，多謝教學分享，要花時間學習了。

jjen0206 發表於 2018-1-27 06:21

感謝大大教學

king0727 發表於 2018-1-27 09:24

有點看不懂... 來摸看看好了...

jg6621 發表於 2018-4-16 22:27

太棒了大大德感謝

winboy 發表於 2018-4-18 06:20

感謝大大教學

bennyst 發表於 2021-7-16 05:53

KeyCats 發表於 2022-4-7 12:59

Thank You.

y88882003 發表於 2022-4-7 13:41

Thank You.

rpg654081 發表於 2022-4-22 20:18

感謝分享

rpg654081 發表於 2022-4-26 18:28

謝謝大大分享

censer 發表於 2022-4-27 14:31

謝謝分享

KeyCats 發表於 2022-4-27 23:11

Thank You.                        : >

dcmapyyy 發表於 2022-4-28 11:36

6666666666666666

angel766738 發表於 2022-5-24 22:54

thank you~!!!!

741010tony 發表於 2022-5-24 23:17

感謝大大提供教學

x6588 發表於 2022-5-24 23:18

......................

snchi77 發表於 2022-10-15 08:59

谢谢分享资源
頁: [1] 2
查看完整版本: 查詢資料庫SQL語法