Jump to content

[Guide]Sql Statements


Recommended Posts

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"


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!



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.



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



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?



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

Link to comment
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.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  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.

  • Create New...

AdBlock Extension Detected!

Our website is made possible by displaying online advertisements to our members.

Please disable AdBlock browser extension first, to be able to use our community.

I've Disabled AdBlock