Jump to content
Habilis

SQL Requests Game

Recommended Posts

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  :B):

Let's play

Edited by Habilis

Share this post


Link to post
Share on other sites
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 :B):)

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

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

 

Anyways, feel like playing???

Edited by Habilis

Share this post


Link to post
Share on other sites

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 by Habilis

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.