[GUIDE] SQL Programming | Using Query Analyzer

melo717171 · 08-12-2008, 11:27 AM

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].

OK> connect.

3# Beginning in QA
in the second window; first; dont forget to setting your DB!

>>>

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 COLUMN,COLUMN,COLUMN from TABLE where COLUMN = 'value'

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:

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.

**DaRkAnGeL** · 08-12-2008, 02:50 PM

added on Updated "All guides and downloads"

melo717171 · 08-12-2008, 02:58 PM

thx . just, i like sharing ,)

OMEGAbuffer · 12:36 AM

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.

SexyReplay · 08-13-2008, 01:21 AM

select * from k_monster order by ssid ASC

OMEGAbuffer · 08-13-2008, 04:56 AM

Whoops wasn't looking at the forum thread clearly lol thx replay

melo717171 · 08-13-2008, 08:42 AM

use: order by ASC/DESC
ASC: A to Z / 10 to 1
DESC: Z to A / 1 to 10

Darkaa · 08-21-2008, 11:44 AM

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 + %.

OMEGAbuffer · 08-21-2008, 02:55 PM

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', '%')

Darkaa · 08-22-2008, 07:44 AM

Thank You . Your Really Helped me ;)

RaGEZONE is proudly sponsored by