Notorious
Enthusiast
Hi!
I wanted to go over some basic cleanup behavior and housekeeping ways in SQL.
A lot of people already know this but, just for the sake of beginners in SQL, it's very good to know these basic things.
These are also the standards on many servers such as Eternal-WoW, where knowing these simple strategies to improve your style was crucial to even get hired.
Perhaps you don't understand what I mean with this, but it's basically that to be able to run a query multiple times, you obviously have to remove the result from the previous query. This is especially important whilst working with World of Warcraft databases. Let's say that you wish to disable the spell Warlock's ritual of Summoning inside Alterac Valley (usable to hinder warlocks from being able to go through the gate at spawn).
The original query (in Trinity) would look like this;
But because this is going to be run into our database, we want to add in this line of code above the very first query;
So that you'll end up with this;
This will simply remove any previous entries containing the entry 698 and the params_0 401 (mapID), allowing our query to run properly without any errors whatsoever.
2. Double checking your queries.
Sometimes, you'll end up with huge queries like this one.
It's then going to be important that you before running it, you double check it, my recommendation is actually using Microsoft Word for the Find/replace function. There you can easily replace 10000 words with the click of a button.
Why do this? Because if you run it into your database, the amount of errors (because often these queries are copy-pasted and remade (10000 lines+)) will pile up and slow down both your computer and your database, therefore reducing the amount you are able to do.
3. Joins are your friend.
Try and make use of them and save yourself some time.
Using joins, allow you to do things like this:
Where normally, you'd have to cross-reference the GUIDs.
4. Writing a large query!
Don't be afraid to hit the enter or tab key once in a while to format it properly.
If you find yourself writing the same query over and over, why not just make a view?
and to view,
Think I should add something? Please type it in a post below and it will be added with proper credits.
I wanted to go over some basic cleanup behavior and housekeeping ways in SQL.
A lot of people already know this but, just for the sake of beginners in SQL, it's very good to know these basic things.
These are also the standards on many servers such as Eternal-WoW, where knowing these simple strategies to improve your style was crucial to even get hired.
1. Deleting your entries before running queries in them.
Perhaps you don't understand what I mean with this, but it's basically that to be able to run a query multiple times, you obviously have to remove the result from the previous query. This is especially important whilst working with World of Warcraft databases. Let's say that you wish to disable the spell Warlock's ritual of Summoning inside Alterac Valley (usable to hinder warlocks from being able to go through the gate at spawn).
The original query (in Trinity) would look like this;
Code:
INSERT into `disables` (`sourceType`, `entry`, `flags`, `params_0`, `params_1`, `comment`)
VALUES (0, 698, (1+16+32), "401", "2597");But because this is going to be run into our database, we want to add in this line of code above the very first query;
Code:
DELETE FROM disables WHERE entry='698' AND params_0='401';So that you'll end up with this;
Code:
DELETE * from disables WHERE entry='698' AND params_0='401';
INSERT into `disables` (`sourceType`, `entry`, `flags`, `params_0`, `params_1`, `comment`)
VALUES (0, 698, (1+16+32), "401", "2597")This will simply remove any previous entries containing the entry 698 and the params_0 401 (mapID), allowing our query to run properly without any errors whatsoever.
2. Double checking your queries.
Sometimes, you'll end up with huge queries like this one.
It's then going to be important that you before running it, you double check it, my recommendation is actually using Microsoft Word for the Find/replace function. There you can easily replace 10000 words with the click of a button.
Why do this? Because if you run it into your database, the amount of errors (because often these queries are copy-pasted and remade (10000 lines+)) will pile up and slow down both your computer and your database, therefore reducing the amount you are able to do.
3. Joins are your friend.
Try and make use of them and save yourself some time.
Using joins, allow you to do things like this:
Code:
SELECT
`c`.`name` AS `name`,
COUNT(`g`.`closedBy`) AS `closed`
FROM `gm_tickets` `g`
JOIN `characters` `c`
ON (`g`.`closedBy` = `c`.`guid`)
GROUP BY `g`.`closedBy`
ORDER BY COUNT(`g`.`closedBy`) DESC;Where normally, you'd have to cross-reference the GUIDs.
4. Writing a large query!
Don't be afraid to hit the enter or tab key once in a while to format it properly.
If you find yourself writing the same query over and over, why not just make a view?
Code:
DELIMITER $$
CREATE VIEW `tickets_done` AS (
SELECT
`c`.`name` AS `name`,
COUNT(`g`.`closedBy`) AS `closed`
FROM `gm_tickets` `g`
JOIN `characters` `c`
ON (`g`.`closedBy` = `c`.`guid`)
GROUP BY `g`.`closedBy`
ORDER BY COUNT(`g`.`closedBy`) DESC
)$$
DELIMITER ;
Code:
SELECT * FROM `tickets_done`;Think I should add something? Please type it in a post below and it will be added with proper credits.
