Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

Junior Spellweaver
Joined
Jan 26, 2007
Messages
111
Reaction score
0
Hi, this is a guide will tell u about SQL programming and what can we do for our KO PWs in query analyzer...

'll explain about SQL command codes...

if you learn well, you will dont want to use E.P anymore ,)
______________________________________________________
begin with notes;
# i never used sql2005, so i dont know CMDs/etc. running in 2005,too. but i think QA commands are the same in both versions.

# im using sql2000; and i explained that guide as 2000.

# 2005 users should pass the first 2-3 steps. i believe they know how to run QA, how to execute a code in QA. then; they need only sql commands etc.
______________________________________________________
BEGIN();
--------

1# What's the QueryAnalyzer?
query analyzer is a application that lets you to work faster on sql. in enterpriseManager; while you're working/editting tables, i often get errors ; i dont understand:S.
Query analyzer doesnt do this,)

2# Starting QUERY Analyzer.
Now go start>all prog>ms sql server>query analyzer.
in the first window; tick windows auth.
IN sql2000 put your sql server as " . " [DOT].
melo717171 - SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE] - RaGEZONE Forums


OK> connect.

3# Beginning in QA
in the second window; first; dont forget to setting your DB!
melo717171 - SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE] - RaGEZONE Forums
>>>
melo717171 - SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE] - RaGEZONE Forums


now. white window is our code window; we'll enter our sql command codes here to execute it on our DB>Tables..
query analyzer works faster and well than enterprise manager...

4# STARTING TO CODING
we learned how to run QA. now beginning to coding.

when we entered a code TO EXECUTE it; we gotta press F5 button or > button in the app. menu.

when a code executed; a result window will be occured bottom. he will say us what's the status?. so we can understand is my code right?.

BEGINNING CODING;

### SELECT command [Search/select in SQL] ###

this command lets us to make a search in our DB... when we executed a searching code; result box will show us the search results...
: NOTE : GREENs are variables, you will modify them as yourself only.

TYPE OF CODE :
Code:
select [COLOR=green]COLUMN,COLUMN,COLUMN[/COLOR] from [COLOR=green]TABLE[/COLOR] where [COLOR=green]COLUMN[/COLOR] = [COLOR=green]'value'[/COLOR]

HERE; column: is column name in table, table: table name you will search in. value=value you will search by it.

this code may gets:
Code:
select column from table
: shows all of table with only entered column.

Code:
select * from table
: shows everything in that table.
Code:
select * from table where column='value'
: shows everything in this table where entered column is entered value.

NOW AN EXAMPLE ; I WILL SEARCH for Kecoon monster (in table k_monster)

1:
Code:
select * from k_monster where strname='kecoon'
it will show me everything where strname is kecoon in table.
2:
Code:
select ssid from k_monster where strname='kecoon'
it will show me only SSID column where strname is kecoon in table.
3:
Code:
select ssid,slevel,iloyalty from k_monster where strname='kecoon'
it will show me ssid,slevel,iloyalty columns where strname is kecoon in table.

I WILL SEARCH NOTHING IN K_monster but I WILL GET ONLY SOME OF THE COLUMNS THAT I WANT:

1:
Code:
select ssid,strname from k_monster
it will show me all of the rows in ssid,strname columns in table.. [i will see all of the monsters']
2:
Code:
select * from k_monster
no any orders! : opens to me the k_monster table..

EXAMPLE SCREENSHOT:
melo717171 - SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE] - RaGEZONE Forums


OTHER THINGS in searching::

LIKE;
if you dont know anything exactly use this:
example: i will search for everything where its strname like "harp"...

:
Code:
select * from k_monster where strname like '%harp%'
it will show me everything where strname like (harp). VALUE MUST BE LIKE : '%value%' : dont modify others!

BETWEEN...AND... :
limit your search.. example: im searching everything but ssid must be between 10-1000 in k_monster:

Code:
select * from k_monster where ssid between 10 and 1000
shows me everything where its ssid between 10 and 1000

ORDER BY:::

order your results as you want::

Code:
select * from k_monster order by strname
: shows me everything in table but orders it by strname (a>z).

other things are those... you can use it at all commands. [not only in select command ..]

### INSERT COMMAND ###

inserts/adds new rows in table.

TYPE:
Code:
insert into TABLE values ('column1value','column2value'.........'lastColumn'sValue')

EXAMPLE I'M adding a new monster to my PW:
Code:
insert into k_monster values ('9999','MyMonster','1400','150'......)

9999 is ssid (first column in table.)
......second > third.... that's so..

### UPDATE COMMAND ###
update your rows ..

TYPE:
Code:
update TABLE set column='value',column='value' where column='whereValue'

EXAMPLE I will update kecoon ;

Code:
update k_monster set slevel='60',iloyalty='50' where strname='kecoon'
this command will update columns as i want where all rows that strname is kecoon .

you can use other things (like..etc.) in this command type ,too.

### DELETE COMMAND ###
deletes the rows as you want...

TYPE:
Code:
delete * from TABLE where column='value'

Example:

Code:
delete * from k_monster where strname='kecoon'
this command will delete the KECOON monster in my DB xD.


###AND - OR ###

this is optional.. you can upgrade your working via those.

Example:
Code:
select * from k_monster where (strname = 'kecoon' or strname='harpy)
this code will show me everything about strname is kecoon or harpy --- THIS COMMAND MAKES LARGE YOUR SEARCH

AND:
select * from k_monster where (strname='kecoon' and ssid='100')
shows you only the strname is kecoon and ssid is 100. this is 1 row. THIS COMMAND MAKES Narrow YOUR SEARCH



any QUESTIONS=??? dont send your questions as pm . send them as post please.






















 
Junior Spellweaver
Joined
Jan 26, 2007
Messages
111
Reaction score
0
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

thx . just, i like sharing ,)
 
Experienced Elementalist
Joined
Jan 3, 2008
Messages
299
Reaction score
1
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

thanks

But, is there a command where you can organize a table and put it in order?

Lets say, a way to order K_Monster by sSid from least to most?

or a way to order K_Monster_Item by sIndex from least to most? That would help me edit drops a bunch.
 
Last edited:
Initiate Mage
Joined
May 21, 2007
Messages
42
Reaction score
0
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

select * from k_monster order by ssid ASC
 
Experienced Elementalist
Joined
Jan 3, 2008
Messages
299
Reaction score
1
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

Whoops wasn't looking at the forum thread clearly lol thx replay
 
Junior Spellweaver
Joined
Jan 26, 2007
Messages
111
Reaction score
0
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

use: order by ASC/DESC
ASC: A to Z / 10 to 1
DESC: Z to A / 1 to 10
 
Initiate Mage
Joined
Jun 27, 2007
Messages
47
Reaction score
0
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

Well Thanks For Nice Guide , But I wanna Know If I wanna change In Monster Drop ( I wanna make all Monsters Drop % same ) Which Script for it?
2. I wanna Add new Drop for 5 Monsters + %.
 
Experienced Elementalist
Joined
Jan 3, 2008
Messages
299
Reaction score
1
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

Darkaa:

For this, %= the % drop rate you want it to be.

100=1%
1000=10%
2500=25%
5000=50%
7500=75%
10000=100%
NOTE: Put the value on the left.

1)
Code:
update k_monster_item set sPersent01='%', sPersent02='%', sPersent03='%', sPersent04='%', sPersent05='%'

2)If you're talking about editting monsters:
Code:
update k_monster_item set iItem01='#', iItem02='#', iItem03='#', iItem04='#', iItem05='#' where sIndex='MobID'

If you want to add new rows:
[Code]insert into k_monster_item values('MobID', 'ItemGroupNum1', '%', 'ItemGroupNum2', '%', 'AnItemExtension3', '%', 'AnItemExtension4', '%', 'AnItemExtension5', '%')
 
Initiate Mage
Joined
Jun 27, 2007
Messages
47
Reaction score
0
Re: [GUIDE] SQL Programming | Using Query Analyzer | Detailed+Image [GUIDE]

Thank You . Your Really Helped me ;)
 
Back
Top