SQL Requests Game

[Habilis 119]

Hiya all.

Let's play a SQL game.

You from your Experience tell me what Data from SQL you would need to have.

I will provide the SQL code of the query to extract that information.

(A lot of fun game)

For example :

You : "I want to spy on my Event GMs and Higher GMs to control the abuse of @commands"

Habilis :

SELECT 
  ac.`atcommand_date`
  ,ac.`account_id`
  ,ac.`char_id`
  ,ac.`char_name`
  ,l.`group_id`
  ,ac.`map`
  ,ac.`command` 
FROM `atcommandlog` ac
LEFT JOIN `login` l ON l.`account_id` = ac.`account_id`
WHERE
	l.`group_id` >= 30
	AND ac.`command` NOT LIKE "@emotion%" 
	AND ac.`command` NOT LIKE "@reload%"
	AND ac.`command` NOT LIKE "@refresh%"
	AND ac.`command` NOT LIKE "@go %"
	AND ac.`command` NOT LIKE "@jump%"
	AND ac.`command` NOT LIKE "@warp%" 
ORDER BY ac.`atcommand_id` DESC 
LIMIT 200;

 

Selects :

• DateTime (when copmmand was executed)
• Account & Char Ids (If not needed remove them)
• Char name
• GM level (if not needed remove it)
• map on which command was executed
• command

Features :

• Extracts 200 last @commands executed
• Extracts only for GM of 30 level and above (No normal players)
• Ignores commans (@Emotion, @reload, @refresh, @go, @jump, @warp)

If you like You may make this query focus only on particular comamnds to track particularly abusable commands

WHERE `command` LIKE "@item%" 

Don't be afraid to ask lika anything Habilis's level of knowlege in SQL is simply GURU

 

Queries of any complexity and any difficulty  

Let's play

Edited August 31, 2017 by Habilis

[bWolfie 138]

You: I want to delete my entire server while it is running.
Habilis: DROP DATABASE habilisro_rag;

[Habilis 119]

20 minutes ago, Myriad said:

You: I want to delete my entire server while it is running.
Habilis: DROP DATABASE habilisro_rag;

Nah, If I ever Hack into someones game server database.

I would spawn cards and sell them for real money $$$  (Not like Habilis already did such an awfull atrocity )

But, it is way more profitable than being destructive to someone's database 

Without making my account a GM, without spawning them through @comamnd, without logs or traces 

 

Anyways, feel like playing???

Edited August 31, 2017 by Habilis

[Habilis 119]

On teh Russian Forum, I've been requested a query to find a particular item

and to find someone who has the most of that item

 

DELIMITER //
set @nameid = 601;


SELECT 
	c.account_id
	,c.name
	,inv.inventory_amnt
	,crt.cart_amnt
	,mail.mail_amnt
	,stor.storage_amnt
	,(ifnull(inv.inventory_amnt, 0) + ifnull(crt.cart_amnt, 0) + ifnull(mail.mail_amnt, 0) + ifnull(stor.storage_amnt, 0)) AS tot_amnt
FROM `char` c
LEFT JOIN (SELECT char_id, SUM(amount) AS inventory_amnt FROM inventory WHERE nameid=@nameid GROUP BY char_id) inv
	ON inv.char_id=c.char_id
LEFT JOIN (SELECT char_id, SUM(amount) AS cart_amnt FROM cart_inventory WHERE nameid=@nameid GROUP BY char_id) crt
	ON crt.char_id=c.char_id
LEFT JOIN (SELECT dest_id AS char_id, SUM(amount) AS mail_amnt FROM mail WHERE nameid=@nameid GROUP BY dest_id) mail
	ON mail.char_id=c.char_id
LEFT JOIN (SELECT account_id, SUM(amount) AS storage_amnt FROM storage WHERE nameid=@nameid GROUP BY account_id) stor
	ON stor.account_id=c.account_id
	
ORDER BY tot_amnt DESC;
	
//
DELIMITER ;

 

Here is an example of what is being selected

+------------+------------+----------------+-----------+-----------+--------------+----------+
| account_id | name       | inventory_amnt | cart_amnt | mail_amnt | storage_amnt | tot_amnt |
+------------+------------+----------------+-----------+-----------+--------------+----------+
|    2000002 | Wiwrtas    |             19 |      NULL |      NULL |         NULL |       19 |
|    2000002 | Strelok    |              9 |      NULL |      NULL |         NULL |        9 |
|    2000000 | test       |           NULL |      NULL |      NULL |         NULL |        0 |
|    2000002 | aaaaa      |           NULL |      NULL |      NULL |         NULL |        0 |
|    2000003 | ladyAdA    |           NULL |      NULL |      NULL |         NULL |        0 |
|    2000000 | toto       |           NULL |      NULL |      NULL |         NULL |        0 |
|    2000000 | Habilis    |           NULL |      NULL |      NULL |         NULL |        0 |
|    2000001 | tata       |           NULL |      NULL |      NULL |         NULL |        0 |
|    2000002 | asdasfasfd |           NULL |      NULL |      NULL |         NULL |        0 |
|    2000004 | tatatatata |           NULL |      NULL |      NULL |         NULL |        0 |
+------------+------------+----------------+-----------+-----------+--------------+----------+
10 rows in set (0.01 sec)

 

Item 601 (flywing) in Inventory, Cart, Mail, Kafra storage(Attention Kafra storage is per account, there fore all Characters under same account will have SAME value there) and a total, I'm using it to sort and find the leader (Who has the most of that item)..

 

I didn't include Guild storage sincie it should be a separate query

Attention doesn't work 100% with cards, cards may be in slots (not taken into account here). Maybe will create separate query for cards....

 

UPD : I just though you may wan't to extract only users that have this ite? Then use:

WHERE tot_amnt > 0

 

Edited September 5, 2017 by Habilis