Written by cyrex at SDB (modified and added to by KingPin on an ongoing basis soon)

MySQL is one of the best database systems in the world, some of the best webs uses them, like yahoo, this forum, well most forums. Of course you got Oracle, Dbase, SQL family (MsSQL, PostgreSQL) BUT for learning reasons (Specially to study and work with mangos) we will start with MySQL. We will use SDB Community database as the base for this AND i need you to have the following tools avaliable. This is not intented to force you to use this tools, you can choose any tools you damn want, but for practical reasons and to start learning as a beginner up to a pro this is the way. BTW if you master this tool, move to the others recommended afterwards:

TOOLS NEEDED
MYSQL 5.X and above installed FREE (Community Edition) www.mysql.com
MYSQL GUI TOOLS (Comes with Mysql Administrator, Mysql Query Browser (This we will use a lot), Mysql Benchmark) FREE www.mysql.com
IMAGINATION & CREATIVITY (This is a MUST have, since some assignments will need this 100% to be done.)

Recommended Afterwards:
Navicat (Very good Admin tool) 30 Day trial Google for it
PHPMyadmin (The killer of SQL Tools, this baby is more compatible that a human with oxygen) FREE Google for it

We will start using in all lessons the Mysql Query Browser or what i like to call it... The Query!! (Nice huh?!!)
run THE QUERY and select the mangos table from the right panel. Double click on it until you see all tables roll down. or just type in the box above:
USE mangos; (To execute a query in THE QUERY you can press CTRL+ENTER)

BASIC LESSONS

Mysql as you have seen throughout this forum uses some commands or statements to start a query. You ask, Whats a Query?. Well a query is like a question or an order you make to SQL so it executes and returns either a value (group of values) or nothing. When i ask you Whats your age?, thats a query, you would then give me your age, which is the value that you return from your SQL (Brain). But if i ask you to stop walking, then you just stop walking without returning a value (Without asking why or returning a question).

We will begin the lesson with the SELECT statement.

SELECT - Selects a table from a database. You can also specify any column you want from that table.

SELECT * FROM creature;
This would select all columns from the table creature

SELECT guid FROM creature;
This would ONLY select the column guid from the table creature

SELECT guid,id,map,spawntimesecs FROM creature;
This would ONLY select the column guid,id,map and spawntimesecs from the table creature

As you can see you can play with the selection of columns from a table. You see the FROM in the query, well this little fellow is the one that separates what columns you want to select from what tables you want to select. Its as basic as this

SELECT ThisColumns FROM ThisTables;

If you read right you have seen the word ThisTables, yes you can select multiple tables and also multiple columns from multiple tables like this

SELECT spawndist,orientation,name,speed FROM creature,creature_template;
(This is a long query result, cancel it after you seen it giving a result and have an idea of how it works)

As you can see i told SQL to give me the spawndist and orientation columns that are ONLY in creature table, and give me the name and speed columns that are ONLY in the creature_template table. I say ONLY because i did not tell SQL to call any more tables to use.

When calling from multiple tables you need to becareful when calling multiple columns with the same name. For example, there is a name column in both, the item_template and creature_template tables. So if i were to use this:

SELECT name FROM item_template,creature_template;

you would get an error saying that name is in both tables and SQL is no psichic to be reading your mind.

BUT, if you run the same query like this:
SELECT item_template.name,creature_template.name FROM item_template,creature_template;

This would give you a long list again, of course cancel the list, but the point is, it worked. It gave you the name of all items and creatures from both tables. You just learned something new today, the DOT power(.). Yes this item_template.name means that you want to select the name column from the item_template table. Same for creature_template.name, where you want the name column from the creauture_template table.
So in general this would be ColumnName.TableName . Of course you cant do something like id.item_template because id column is not inside the item_template table, but it is in another table, so this wont work.
I hope you have noticed the ; at the end of every line, thats the symbol that tells SQL that the query has finished. You cant have more than one query running right after the other one without closing the previous so this ; helps from that, it tells that the query has finished and starts the next one it follows in the list. In only small specific queries you will see more than 1 query running at the same time, but i will leave that for the advanced lessons (You need to know new statements).
Well you now know how to select multiple tables and columns and how to select specific ones from a single table. Lets move on
Lets SELECT all tables from creature, this would mean:

SELECT * FROM creature;

Ok we have the creature table selected from top to bottom, but hey we want to organize it, its all messy. Thats were this puppies come for:

ORDER BY
GROUP BY

The query is SELECT Columns FROM Tables ORDER BY Column; for ORDER BY query
The query is SELECT Columns FROM Tables GROUP BY Column; for GROUP BY query

For example i would like to select all from creature but i want to order the whole thing by id, so i do the following:

SELECT * FROM creature ORDER by id;

After a few seconds you would see the whole table again...BUT, look at the id columns, is all neat and organized, whohooo you rock, you just made a organized table by id, how about that. You ask, hey but if i want to have it in reversed, lucky for u the Db god exists so here it is, the reverse version of the above one

SELECT * FROM creature ORDER by id DESC;

the DESC after the column you want to order it by will be done in reversed or in Descended format.

Hey but i think you are thinking, Can i order by multiple columns, hell yeah you can!!. Look at this
SELECT * FROM creature ORDER by id DESC, map;

Now you got a order by that FIRST checks the whole table and orders it by id in descended form, then after that, it orders the result by map. So id would be first level ordering and map would be second level.

Now to GROUP BY...

Lest select the creature_movement table:

SELECT * FROM creature_movement;

ok we got the table selected, but you are thinking, i want to know how many different waypoints we have on this table. Thats one way of using the GROUP BY command

SELECT * FROM creature_movement GROUP BY id;

This will simply do the following, grab an id, if it repeats in the following row it will just forget about it. So at the end you will get all ids that dont repeat themselves, there by making a group of all ids. So what you would be watching after running this query would be the groups of waypoints made into one, so you can count how many creatures have waypoints attach to them. If you are using the query then you would see below that it says 55 rows fetched. So we have for the moment 55 creatures that have waypoints.

Hey, but you are asking can i join both, the GROUP BY and ORDER BY, of course, but there is a trick here. you should not group by after a order by, and you should only try to do it group by then order by. Like this

SELECT * FROM creature GROUP BY id ORDER by map;

This would GROUP the ids into one, then order all ids by map. If you had done it backwards, first order then group you would get, or an error, or some weird freak of nature sql. Because you are telling it to order the whole thing my map, THEN asking it to group the remaining values by id, but this would mean that the order by did not take effect since you are nulling its order by grouping the whole thing after it.

So the way of the warrior is : SELECT columns FROM tables GROUP BY columns ORDER BY columns;

Now to the borther of the 2 above, the WHERE clause

WHERE is brother of ORDER BY and GROUP BY because he can also do almost the same as them, he can be very selectable to a table. And also join forces with ORDER BY and GROUP BY as we will see in the following example:

SELECT * FROM creature WHERE map=533;

Here you can see we have told SQL to give us all creature rows THAT have map = 533, thats the beauty of WHERE. You can call it the search tool.

Lets say i want to look for creature rows that have a spawntimesec above 1000, i would then do

SELECT * FROM creature WHERE spawntimesecs>1000;

Or i want to look for creature that have a spawndist above 10

SELECT * FROM creature WHERE spawndist > 10;

but i want to order it by spawndist:

SELECT * FROM creature WHERE spawndist >10 ORDER BY spawndist;

and i want to group it by spawndist

SELECT * FROM creature WHERE spawndist >10 GROUP BY spawndist ORDER BY spawndist;

only your imagination can LIMIT you with the WHERE clause...or is there another...well, there is, the LIMIT command, how about that!!

If you dont want see a whole bunch of rows but just a small group you can run the query with the LIMIT statement in it

SELECT * FROM creature WHERE spawndist >10 GROUP BY spawndist ORDER BY spawndist LIMIT 5;

this would give you only the first 5 rows that appear.

SELECT * FROM creature WHERE spawndist >10 GROUP BY spawndist ORDER BY spawndist LIMIT 5,10;

this would give you the rows between the 5th that appear up to 10 more. So it would actually give you rows 6 to 15.

WHERE as the twin brother of GROUP BY and ORDER BY can also be small orderly with the AND command

SELECT * FROM creature WHERE spawndist > 10 AND map=0;

This will select all spawndist rows that are above 10 and have map=0

Of course you can always join everything up together. use your imagination for that.

Well thats all for SELECT statement. Now we will move to UPDATE, INSERT and DELETE. I will leave as homework for you to look for the HAVING command in the SELECT statement. Also to look for BETWEEN and OR. This way you will also learn how to use google. HINT: DeveloperFussion.

BASIC LESSON 2
(DO NOT RUN ANY OF THIS QUERIES IN THE DB, BECAUSE THEY WILL UPDATE THE DB AND MAYBE DAMAGE SOMETHING, MAKE A BACKUP FIRST.)
UPDATE - Updates an already existing entry with the values you give.

UPDATE tables SET columns1 WHERE columns2; Where columns1 is the values you want to have updated, Columns2 is the values the row has to have for it to be updated.
For example:

UPDATE creature SET map=23 WHERE map=999;

This will change or update any row that has a value of map=999 to a value of 23

UPDATE creature_template SET name=Zelda WHERE id=1234567;

This will change the column name to Zelda where the id=1234567

UPDATE item_template SET name='Michael Jackson', health=334, mana=232, eyes=blue, sex=hornymale WHERE entry=69;

This will change name,health,mana,eyes and sex to the values specify in each where the entry in the table is 69.

The process in all works the following, first the where is run. It looks for the values that are mention in the WHERE command. In the last example is 69, it looks for the entry 69. If found (true) it looks and changes all the values mention in each column in the SET part of the query, in the last case, the changes are made to the name,health,mana,eyes and sex columns of the row where entry = 69.

INSERT - Adds a row to a existing table.

INSERT INTO creature VALUES (1,2,3,4,5,6,7,8);

This would add the values 1,2,3,4,5,6,7,8 to the row assuming the following:
1. The number of columns are 8
2. There is no need to specify columns between the table name and creature since you would be using all columns to add values. The below example illustrates this better.
3. There is no key to the column that forces some columns to exist in every row. For example, the column food SHOULD always be in a every row of the table.

INSERT INTO creature (C1,C3,C4,C7) VALUES (4,7,2,9);

This means that you would add the first value (4) to Column 1 (C1), the second value (7) to Column 3 (C3), The third value (2) to Column 4 (C4) and the the fourth value (9) to Column 7 (C7). The example value enhances the explanation:

INSERT INTO account (username,password,gmlevel) VALUES (Zelda,Link,1);

As many of you know, this is the way to add an account in the game, gmlevel is the one that makes you a GM or a Normal client. The query above those the following, It adds the value of Zelda to the column username, it adds Link to password and it adds 1 to gmlevel. The other columns in this example are created when you enter the game or automatically generated. The first column of the table account (id) is the KEY of this table and the one that orders the players by, well, by id lol.

NOTE: If you run into problems doing an INSERT statement, i recommend to study the table you are inserting to, many of the problems incounter with this are because of the rules of the table, which could be ranging from obligated columns to mentioning of the sum of other columns, etc... ALWAYS verify the properties of the table and its columns to do a perfect UPDATE to it.

to add multiple rows you can do the following:

INSERT INTO account (username,password,gmlevel) VALUES (Zelda,Link,1),(Peter,Pong,1),(Tommy,Cark,2),(Wend y,Superman,2);

Where each value in the () after the VALUES is a row, so you would create a row with
(Zelda,Link,1) - First row created
(Peter,Pong,1) - Second row created
(Tommy,Cark,2) - Third row create
Wendy,Superman,2) - Fourth row created

This would save you time doing the following which would waste time, space for sql and you:

INSERT INTO account (username,password,gmlevel) VALUES (Zelda,Link,1);
INSERT INTO account (username,password,gmlevel) VALUES (Peter,Pong,1);
INSERT INTO account (username,password,gmlevel) VALUES (Tommy,Cark,2);
INSERT INTO account (username,password,gmlevel) VALUES (Wendy,Superman,2);

Both, the above and INSERT INTO account (username,password,gmlevel) VALUES (Zelda,Link,1),(Peter,Pong,1),(Tommy,Cark,2),(Wend y,Superman,2);

will do the same thing, the diff is one is quicker to make and read, and can have less ; errors, the other one is not.

DELETE - Deletes a row from a table.

DELETE FROM creature WHERE guid IN (123,321,456,545);

This will search in creature table and delete all the rows inside the IN (). this would be 123, 321, 456 and 545;

As you can see it works as the UPDATE statement mixed with the SELECT statement. You can use your imagination for this one.

COMMON OPERATORS

= - This means that is equal to the value specify. For example, WHERE dist=5. This would mean that the UPDATE statmente would only run on rows where dist is equal to 5.

< - This means that is less than the value specify. For example, WHERE dist < 10. This would mean that the UPDATE statement would only run on rows where dist is less than 10.

> - This means that is more than the value specify. For example, WHERE dist > 15. This means that the UPDATE statement would only run on rows where dist is more than 15.

<> - This means that is different than the value specify. For example, WHERE dist <> 20. This means that the UPDATE statement would run on ALL rows EXCEPT on the ones that dist = 20.

|| - This means that if one of 2 cases is true the query is executed in that row. For example, WHERE {level=10 || level=20}. This means that the UPDATE statement will run only if level=10 OR level=20 are met. The following will explain better the OR operator:

OR, ||
Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL.

mysql> SELECT 1 || 1;
-> 1
mysql> SELECT 1 || 0;
-> 1
mysql> SELECT 0 || 0;
-> 0
mysql> SELECT 0 || NULL;
-> NULL
mysql> SELECT 1 || NULL

AND && - This means that if BOTH cases are met the query is executed for that row. For example, WHERE name=Peter && size=2; This means that the query will be run if the entry has a name=Peter AND a size of 2.

NOT ! - This is just to logical to explain. It means that if the value is NOT found the query will be executed. WHERE entry != 10; This means that any entry different than 10 will be updated.

BETWEEN - This is use to tell a statement to look between one condition and another. For example WHERE level between 10 AND 20; This will search/update/delete if the level columnd is between 10 and 20.

IN - This little puppy is very useful. Ill let you know why. You see the following example and then ill explain what id does:

DELETE FROM creature WHERE guid=999281;
DELETE FROM creature WHERE guid=12381;
DELETE FROM creature WHERE guid=13381;
DELETE FROM creature WHERE guid=4341;
DELETE FROM creature WHERE guid=4561;
DELETE FROM creature WHERE guid=6763;
DELETE FROM creature WHERE guid=2351;
DELETE FROM creature WHERE guid=67981;
DELETE FROM creature WHERE guid=565281;

Now using the IN operator i would turn the above into this:

DELETE FROM creature WHERE guid IN (999281,12381,13381,4341,4561,6763,2351,67981,5652 81);

This is the best example on how to use the IN operator. Is a operator that tells that anything inside the () will take the condition that you have made in the statement, delete/update. You can think of IN as INSIDE, so if you read the above it would read "Delete from the table creature everything where guid has inside the values (999281,12381,13381,4341,4561,6763,2351,67981,5652 81)".

NOTE: The last 3 operators CAN be used as words. For example instead of || you can use OR, instead of && you can use AND, and instead of ! you can use NOT.

CREATING VARIABLES

AS - The AS works AS it should. That was a joke. AS makes a column assing to a variable. In the following example we will use AS with the COUNT function:

SELECT *, COUNT(guid) AS NumberOfRows FROM creature WHERE guid > 1000 GROUP BY map;

In this example we select everything from the creature table, count the number of guid we find and assing that number to the NumberOfRows variable. This is done only to the guid that are above 1000 and after all this, they are grouped by map in the result. To know the number of guid from the NumberOfRows variable you would need another language like PHP to read and show you the variable as a number in a neat way. Of course there is
NOTE: When using the COUNT, you MUST use the GROUP BY since it is a needed part for the COUNT to work right.

HINT FOR FREE

+When using names with spaces in a value be sure to use ''. Examples are name='Peter Parker', food='Gummy bears'.

+ALWAYS verify that the ; value is at the end of each query you run.

+Study a table before working with it, specially here, since some tables have conditions you must meet to work with them well. A query could work on a table X, but not on a table Y or Z.

+When inserting or updating values from a row, be sure to put what columns you want to change, even if the row has a key column or not, this is a safety net, if the row is somehow modified in a near future. For example, the case about adding reputation columns to the quest template. Some devs could tell you they got a little mess with that, BUT if you were using the proper safety way to insert or update stuff then that would have not caused any troble.

INSERT INTO creature (C1,C3,C4,C7) VALUES (4,7,2,9); - Proper Way. This way does not matter if you add or remove columns, before or after others. The query is telling the table where it wants the values to go on each column.

INSERT INTO creature VALUES (4,7,2,9); - This is also a way. BUT if someone would add a column between the column 3 and 4 (This way, moving column 4 to 5) then the value 9 would not go to the proper column 5, instead it would go to 4. This can create problems for example:

THE TABLE HAS ONLY THE FOLLOWING COLUMNS: NAME,AGE,SEX AND FOOD
INSERT INTO creature (name,age,sex,food) VALUES (cyrex,27,male,chocolate); - Works Perfect
INSERT INTO creature VALUES (cyrex,27,male,chocolate); - Works Perfect

Then after some days, your boss changes the table, it ALTERS the table adding another column, lets say, between SEX and FOOD, called MONEY.
THE TABLE HAS NOW THE FOLLOWING COLUMNS: NAME,AGE,SEX,MONEY AND FOOD

INSERT INTO creature (name,age,sex,food) VALUES (cyrex,27,male,chocolate); - Works Perfect
INSERT INTO creature VALUES (cyrex,27,male,chocolate); - Will not work, because it will assing chocolate to the MONEY column, which btw has a property that only numeric values can go in so it will give you an error.

See the point. Always use the name of the columns you want to modify before giving the values for it.

+ALWAYS verify that the query you create is 100% perfect. This means that it has no side effects over other things in the DB or ingame.

+ALWAYS check is well written and can be executed without any problems.

+ALWAYS explain what it does so anybody that checks on it can easily study it.

+If is going to do a big change in the DB please do an extensive check on the whole game before commit here. For example, if you want to change some spell, then test out all spells you can from other classes, chances are you might change some other spell or damage it.

+Try to use logic for almost everything here. Its not save to assume anything in the DB. For example, dont assume that entry=696969 is the only one with health=12345645, always do a search first and be 100% sure about what u do. Thats what the SELECT statement is for mostly.

+Try to use the help from mysql.com (If you download the help file for the love of god dont try to understand it youll go nuts) try using any other good place for mysql help.

+Be patient and becareful.

+ Its ok to ask for help, even though some people are light headed, there are more that are willing to help you.

------------------------------------------------------------------------------------------------

HOMEWORK FOR NOOBS (Like me hehe)

EASY

1. Do a search in the item_template for every item that has the following conditions:

a. Its a sword or mace
b. Has damage above 30

2. Do a search for all items that have the following conditions

a. Can be stacked 20 times

3. Do an update in the creature_template that changes 2x the amount of health of all the entries that match the following conditions:

a. Have minhealth below 200
b. Have damage below 50
c. Have a level below 20
d. Have a spee less than 1

INTERMEDIATE

1. Do a search in the item_template for all items that match the following criteria:

a. Have a DPS of 40 and above. DPS formula will be DPS=(MinDamage+Maxdamage)/TimeToAttack
b. Are a sword
c. Are Blue or Purple Item.

And group them by DPS.

2. Do an update in the creature_template that updates the speed and mana of all mobs by 1.5x, but comply with the following:

a. Must be elite mobs.
b. Must have health above 1000
c. Must have DPS greater than 40
d. Must have mana above 200

and group by mana, speed.


--------------------------------------------------------------------------------------------------

THIS IS THE CORRECT ORDER TO PUT ALL KEYS FOR THE SELECT STATEMENT

SELECT --- COUNT AS -----FROM --- WHERE ---- GROUP BY -----HAVING ----- ORDER BY ------ LIMIT;
UPDATE ----SET ----WHERE;
DELETE FROM -----WHERE;
INSERT INTO -----WHERE;

For Example

SELECT * FROM creature WHERE health > 100 GROUP BY factionid HAVING name LIKE '%Guard%' ORDER BY name LIMIT 5,5;

This would select all columns from creature (SELECT * FROM creature), then search for the rows that have health above 100 (WHERE health > 100), after this it would group them by factionid the ones that are found with health above 100 (GROUP BY factionid), a condition then is made, the groups MUST have in their name column the word guard (HAVING name LIKE '%Guard%' ), after this, the result is then ordered by the name column (ORDER BY name) and it will show the results starting from the fifth result up until the 10th result (LIMIT 5,5). Always ending in a ; to tell the query this is the end.

nice KP. Once i get home i'll be downloading Mangos and all the other goodies.

we will benefit a lot from Lua scripters as they can add spells and many other routines cheaply(cheap time and coding skillwise) C++ for mangos is awesome but takes a longer time and is optional

i'm already looking for tutorials :P if someone could also point in the right direction would be great (that's if anyone knows where to go)