Sql

[Th3_L0st]

Now I will give you a little bit a helping hand ^ ^
With these instructions, you can fix your bugs on the servers or use SQL for other things. These instruction, however, refers mainly to your WoW server and the Db's.
This guide is the first of its kind and may not be copied or published without my permission.

Important:
This guide is designed for Aspire. You can use it even for mangoes, but you need to change all tables and databases:-P

Contents:
1. What do I need?
2. What to do now?
3.1 The understanding of the standards and Querry's
3.2 The SELECT command
3.2.1 Structure of the SELECT command
3.2.2 All information immediately
3.3 WHERE Querry's
4.1 The INSERT command
4.1.1 INSERT INTO
4.1.2 The `column` Values Querry
4.2 UPDATE commands
4.2.1 UPDATE, SET, and WHERE
5.1 The DELETE command
5.1.1 Using the DELETE command

1. What do I need?

Visual Studio:
Download details: Visual Studio 2008 Service Pack 1
(Of which you installed Visual Studio C + + Express Edition, it contains. Net Framework 3.5)

XAMPP:
apache friends - xampp

Navicat:
http://www.navicat.com/download/

HeidiSQL:
Download HeidiSQL

SQLyog:
Download - SQLyog MySQL Frontend, MONyog MySQL Monitoring Tool

Your favorite Database:
Filebeam - Beam up that File Scottie!

Notepad (++):
http://notepad-plus.sourceforge.net/en/site.htm


2. What to do now?

Now you have to all that you have downloaded it to install and configure.
If you have everything installed, we can move on to the next point.


3.1 The understanding of the standards and Querry's

You all have now installed a database, but do you also understand what you have installed there and how? ^ ^ I dont think so, but this will change after you read this guide.
First we open a simply structured table. I'm thinking of the table "vendors". I do all this with Navicat. Rightclick the vendors table and click on
Context menu "Open Table". You should now see a tab to open the table and its columns named "entry, item, amount, max_amount, inctime and extended_cost". I will firstly explain these columns.

entry: ID of the seller
item: ID of the item that he should sell
amount: number of items to be sold
max_amount: Maximum number of items to be sold (not important for beginners, it is by default 0)
inctime: (not important for beginners and by default 0)
extended_cost: not important for beginners (describing a brand price)

In this table, all this is still very easy to understand, but we will soon be dealing with tables such as "creature_proto. Then it becomes much harder.


3.2 The SELECT command

This command is the simplest command from the huge arsenal of commands. With it you can read data from the tables, without looking for them until long in the browser to have.
It changes nothing, but just read something from the tables. So do not worry ^ ^


3.2.1 Structure of the SELECT command

Actually, he is basically made up of the command and specify SELECT FROM WHERE.
Like all other Querry's also ends it with a ";". It marks the end of a Querry.
To execute a Querry, you need to select to your database and from the context menu, right-console / console.
Now you can enter your Querry.
Normally, a SELECT Querry looks like this:

 SELECT `colum_name1` FROM `table_name` WHERE `column_nameX` = 'value',

This also looks for beginners probably still a little funny, but will immediately be explained. We take the dealer "Corina Steele" as an example. Her ID is 54
The Querry for the table "vendors" should look like this:

 SELECT `item` FROM `vendors` WHERE `entry` = '54 ';

If you program it into your Sql and run it, it´s showing you all ItemID's, which are sold by Corina.
You can also get more information at once. It looks like this:

 SELECT `colum_name1`, `column_name2` FROM `table_name` WHERE `column_nameX` = 'value',

Our next command is read out so that 2 information. With us the information from the column "item" and the column "amount".

 SELECT `item`, `amount` FROM `vendors` WHERE `entry` = '54 ';

Now you will see the parameters of the two columns.


3.2.2 All information immediately

You can also directly read the information from all columns, but this may take a long time for large data sets.
The Querry for this is:

 SELECT * FROM `table_name` WHERE `column_nameX` = 'value',

or in our case:

 SELECT * FROM `vendors` WHERE `entry` = '54 ';

Now you will see all the data from this dealer.


3.3 WHERE Querry's

The WHERE command is already almost become indispensable and very important. He also changed anything, but only reads.
However, if you want to run a delete command, you have to accept a WHERE command, since you delete everything else in this table.
As assistance is now also featured the "AND-expression to it."
Querry should look like this:

 SELECT * FROM `table_name` WHERE `column_nameX` = 'valuex' AND `column_nameY` = 'valueY';

Now we take the time "amounts to." That's how you know so, the number of sold items (stacks of arrows, etc.).

 SELECT * FROM `vendors` WHERE `entry` = '54 'AND `amount` = '1';

Now you all are Angezigt sold items that are sold einzelnd.
Of course you can specify any quantity, such as 200 at arrows.

 SELECT * FROM `vendors` WHERE `entry` = '54 'AND `amount` = '200';

(This command will appear with a message entitled "Empty Set" because those dealers only einzelnd items sold.)

Vllt but we will not only know what job the seller sells, but also with all other stacks of X (in our case 200).
We just replace AND with OR and there you go:

 SELECT * FROM `vendors` WHERE `entry` = '54 'OR `amount` = '200';

Now are you all ItemID of our ID's 54 and displays all sellers 200s stack.

That's it then use the SELECT command. You currently rules him now (in theory:-P)

4.1 The INSERT command

The INSERT command that will drive new columns in your example, you can existiernden Tabllen erstellen.So NPC's or create new items, and make Npc's to vendors, which we will now make it easy times.


4.1.1 INSERT INTO

The simplest type of INSERT command, the INSERT INTO command. Thus one simple columns in existing tables created.
Now we process the Archbishop ([url = "http://wow.buffed.de/?n=1284"] Archbishop Benedictus - NPCs - World of Warcraft Database buffed.de [/ url]) so that it considers this item sold [url] http://wow.buffed.de/?i=33034 [/ url]. Therefore we use the INSERT command first to get him an entry in the "vendors" Create table.
The Querry looks generally like this:

 INSERT INTO `table_name` VALUES ( 'v1', 'v2', 'v3', ....., 'vX');

What corresponds with us:

 INSERT INTO `vendors` VALUES ('1284 ', '33034', '1 ', '0', '0 ', '0');

If you were to import these Querry, a new entry in the table "vendors" will be created for this ID (Just do it:P Everything will be reversed to its original state later).
As you see, is the INSERT INTO command one of the simplest commands at all. But no matter how simple it looks, it has some disadvantages. If now for example 20 INSERT INTO finished, and a new column is added to the table, the Querry's are useless, unless you add the new column to your Querry´s.
The resulting error looks like this:

 / * SQL Error: Column count does not match value count at row 1 * /

So you see:

Good side: Very easy to use and implement
Bad side: Vulnerable to column changes and local errors


4.1.2 The `column` Values Querry

Another option for the INSERT command is to specify the exact columns that you want to, the information will be added
Well, that still provides a correct work out ^ ^:

 INSERT INTO `table_name` ( `column_name1`, `column_name2`, `column_name3 `,.....,` column_nameX) VALUES ( 'v1', 'v2', 'v3', ....., 'vX');

Rebuilt for our sellers Bishop ^ ^:

 INSERT INTO `vendors` (entry, item, amount max_amount, inctime, extended_cost) VALUES (1284,33034,1,0,0,0);

And ready is Querry with the same result, but which is easier to adapt to a structural change.

Good side: split changes are no longer a problem
Bad side: it simply takes longer to write:-P

4.2 UPDATE commands

With UPDATE commands that can be all sorts of things to adjust and correct in the simplest way.

Good side: You can correct everything immediately and fixed
Bad side: Is not:-P

4.2.1 update, SET, and WHERE

Constructed is the UPDATE command, much like the SELECT command. It is based on the same basis.
He does look like this:

Now

 UPDATE `table_name` SET `column_name` = 'value' WHERE `column_nameX` = 'valueX';

back to our Bishop ^ ^ We have already created so its entry in the "vendors" table.
This is so good to us but little or nothing :-). We need him as a seller. We use an UPDATE command.
This is how:

 UPDATE `creature_proto` SET `npcflags` = '128 'WHERE `entry` = '1284';

Now, the good old Bishop, a seller and acts with good vodka ^ ^
Such a ye now you can also treat yourself ^ ^ You now know almost everything you need^ ^

5.1 The DELETE command

This is the most powerful command of the whole manual. If it is incorrectly applied, it can destroy entire tables.
He is also coupled with a WHERE command, and this is particularly important here.

5.1.1 Using the DELETE command

The use of the DELETE command is not heavy, like the INSERT command to hair.
He usually looks like this:

 DELETE FROM `table_name` WHERE `column_nameX` = 'ValueX';

Since our Alkibischof yes it is blizzlike, we'll fix him equal time, as you see ^ ^
This is, by deleting first time the police entry in the "vendors" table.

 DELETE FROM `vendors` WHERE `entry` = '1284 ';

(Also, you must still change the flags again soon:-P
This is also an UPDATE Querry:

 UPDATE `creature_proto` SET `npcflags` = '2 'WHERE `entry` = '1284';

Now is your DB again as it was in the beginning, but you know a lot more ^ ^)
MfG Th3_L0st

[Th3_L0st]

*bump da thing