[Guide]Sql Statements

[vampir]

Hello, i will teach u something usefull today

Look at this table: http://imageshack.us/photo/my-images/191/imagenqg.png/ lets say left table is named "characters" and right table is named "clans"

SELECT

Ok so lets do something easy, lets take all variables from online characters:

SELECT * FROM characters WHERE online=1

* means every column(ID, name, title etc), so thats easy right?

 

So now lets get every online character with the clan

SELECT * FROM characters WHERE online=1 AND clanId>0

Thats easy too, we will have 2 results

 

Now we will take every Name of the character, but i want them sorted by PvpPoints, so character with most pvp points will be at first place:

SELECT Name FROM characters ORDER BY PvpKills ASC

We will have "Vampir, Koko, Tester, PolakPl" as result. ASC - means from a to z/lowest to biggest, DESC means from z-a/biggest to lowest.

 

Hmm and what if i want to take just top 3 guys?

SELECT TOP 3 Name FROM characters ORDER BY PvpKills ASC

Thats simple :P

 

Ok, now lets take Titles of characters, that have got clan, or their sex is 1

SELECT Title FROM characters WHERE clanId>0 OR Sex=1

Piece of cake

 

We have got some different things also, like taking average:

SELECT AVG(PkKills) FROM characters

It will give us: 12, since (4+12+0+32)/4=12

You can also look at Count(), Max(), Min(), Sum()

 

Sometimes it happends, that we need to get 2 tables in same time, like for example get name of player's clan, and its not in same table, so thats a bit harder:

SELECT clans.Name, characters.Name FROM characters INNER JOIN clans ON characters.clanId=clans.clanId WHERE characters.clanId>0

Thats a bit complicated, isnt it? So we are taking Name from table clans and Name from table characters.

We need to connect tables, so after FROM we put name of first table, later INNER JOIN and after that name of 2nd table.

After that we put word ON and choose columns that are connected(here clanId, sometimes we may have in characters char_id and in other table also char_id, so thats what we need to connect), so we do it like this: table.column_name=2ndTable.2ndColumn_name

Column names doesnt have to be different!

 

UPDATE

Ok, lets leave that boring SELECT, i will also teach u how to update, add and remove records.

UPDATE characters SET PvpKills=PvpKills+1

It will add 1 pvp Point to every character in database.

So first UPDATE, later name of the table, later SET and what we wanna do

 

UPDATE characters SET Title='' WHERE clanId=0

This will set every character title empty, if he doesnt have a clan.

 

Insert

Now lets add some new Records

INSERT INTO clans VALUES (19,'Losers', 1, 0, '', 0, 0)

Ok so i added new Clan to clans table, id=19, name=Losers, level=1, reputation=0, ally name is empty, castle and fortress is set to 0

 

if we want to put only some values and make the rest be default, do this:

INSERT INTO clans(clanId, Name, Level) VALUES (20, 'NewClan', 2)

So clanId will be 20, name=NewClan, and level=2, rest will be set to default so it will probably be null or 0

 

DELETE

The last thing will be deleting records.

DELETE FROM clans WHERE hasCastle=0

it will remove all clans, that doesnt have castle.

 

What if we wanna remove everything?

DELETE FROM clans

 

Thats all, thx for reading it, i know its very basic thing but maybe somebody will find it usefull, if u wanna learn more http://www.w3schools.com/sql/

Feel free to ask about anything :)