ArcEmu: Working with SQL Queries! - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (07 December 2014 - 12:06 PM) Arcemu is in hibernation mode, please read http://arcemu.org/fo...showtopic=26903
dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Resize Shouts Area

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Working with SQL Queries!

#1 User is offline   Knigh_t 

  • Member
  • Pip
  • Group: Members
  • Posts: 53
  • Joined: 10-March 09
  • Gender:Male
  • Interests:Making Databases, Working with SQL and Mmowired.com
  • Server OS:Windows

Posted 30 June 2009 - 12:00 AM

Hey All!!
Im making a Shot Guide which teaches you how to make some easy SQL Queries.

there are 4 Most Common Commands for everyone to remember:
Insert:
Delete:
Update:
Select:

Alright, Lets work with the Insert Query Now.

Lets Suppose you want to Add an Npc in creature_names with entry Id 123456
and add more stuff to it e.t.c, e.t.c...
Now we will type the Query in the "Query" Box or you can also type it in notepad. I recommend using Notepad atm for better understanding...
Alright so here is the Query i typed till now:
Insert into `creature_names` ( `entry`, `name`, `Subname`)

The above query only commands the Database to add fields into the Column Creature_names, but this Query will fail if you execute it cause its Half and it has no values. We will want to type in all the columns creature_names have with a comma and a space between them all.
So lets do all of them now:
insert into `creature_names` (`entry`, `name`, `subname`, `info_str`, `Flags1`, `type`, `family`, `rank`, `unk4`, `spelldataid`, `male_displayid`, `female_displayid`, `male_displayid2`, `female_displayid2`, `unknown_float1`, `unknown_float2`, `civilian`, `leader`) 


So, these are all the tables in creature_names and notice how all of them have a `` on them.
Now lets make the same query but put in the values with it:

insert into `creature_names` (`entry`, `name`, `subname`, `info_str`, `Flags1`, `type`, `family`, `rank`, `unk4`, `spelldataid`, `male_displayid`, `female_displayid`, `male_displayid2`, `female_displayid2`, `unknown_float1`, `unknown_float2`, `civilian`, `leader`) 
values ('123456', "Tigerresse", "", '', '0', '7', '0', '0', '0', '0', '14330', '0', '0', '0', '1', '1', '0', '0');

You should have understood which value is for which field. We started with entry then name, then Subname and in the Values the 1st number 123456 tells us that its for entry Id, for Name its Tigerresse and you would have noticed that "" is also there for Subname. It actually means the field is Blank.

This is How you can work with every table and every column in the database. For example Items, for that you ll have to start with
Insert into `items` and e.t.c e.t.c

the name after Insert into tells what column you want to insert it in.


Now the Second Command is Delete:
Okay, this is like the easiest Query ever!!!

Now for example you want to delete some record from the database.

Well 1st you need to know what column it is and what Entry Id is that record saved with.

Lets Suppose we want to delete the Tigerresse we made above.
So we will use the following:

Delete from `creature_names` where `entry` = 123456

I think the Query itself explains what its doing.

Its deleting a Record which starts with the Entry Id 123456 and its deleting from the table creature_names.


Update:
This Query is for updating some Old Record saved in the database.

For example if we want to Update the Entry Id of the Tigerresse we made above.
So we will use the following query:
Update `creature_names` Set `entry` = 654321 where `entry` = 123456

Okay this 1 also self explains like any other Query.
Its telling the DB to Update the field with Entry ID 654321 whose entry Id is 123456 in the Creature_names Column.

Now if we want to Update the name too, we would use the following Query:
Update `creature_names` Set `entry` = 654321 where `entry` = 123456;
Update `creature_names` Set `name` = Panther Where `entry` = 654321

You would have noticed how this time i inserted a ";" at the end of the 1st line.
It shows how there is a Line break and how the Sql Query is continuing.


Now the 4th Query Select is what i will teach you later, as I gtg atm. But if you learned above 3, you would be able to make up your own Query and work with it.


Anyways!
Thanks For Reading!!!
1

#2 User is offline   rampage6968 

  • Newbie
  • Group: Members
  • Posts: 7
  • Joined: 06-July 08

Posted 03 July 2009 - 06:16 AM

quick question for you
I am trying to merge 3 tables into 1 table called creature proto. (output database)
most of the data is in creature template and 2 tabs in 1 table and 1 from another
everything i have tried fails (base database)
Can you help.

(base database)
REPLACE creature_proto (entry, minlevel, maxlevel, faction, minhealth, maxhealth, mana, scale, npcflags, attacktime, mindamage , maxdamage, rangedattacktime, rangemindamage, rangemaxdamage, armor, resistance1, resistance2, resistance3, resistance4, resistance5, resistance6, combat_reach, bounding_radius, auras, money, spell1, spell2, spell3, spell4)
SELECT entry, minlevel, maxlevel, faction_A, minhealth, maxhealth, maxmana, scale, npcflag, baseattacktime, mindmg , maxdmg, rangeattacktime, minrangedmg, maxrangedmg, armor, resistance1, resistance2, resistance3, resistance4, resistance5, resistance6, combat_reach, bounding_radius, auras, maxgold, spell1, spell2, spell3, spell4
FROM robsbase.creature_template

from robsbase.creature model::combat_reach, bounding_radius,
from robsbase.creatureaddon:: auras
0

#3 User is offline   Knigh_t 

  • Member
  • Pip
  • Group: Members
  • Posts: 53
  • Joined: 10-March 09
  • Gender:Male
  • Interests:Making Databases, Working with SQL and Mmowired.com
  • Server OS:Windows

Posted 05 July 2009 - 08:39 AM

Okay, i quite didnt understand what you mean by merging?? you want the same table(same data) to be merged with the 3rd table??

or you have 3 different tables with diff data and you're trying to merge them in??
0

#4 User is offline   raphaellb2003 

  • Newbie
  • Group: Members
  • Posts: 0
  • Joined: 04-August 09
  • Gender:Male

Posted 30 April 2010 - 07:04 PM

I prefer navicat i dont have to deal with commands even in-game...;)
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users