[Guide]SQL Commands

[ServeSATAN]

Hello Maxcheaters.in this guide i will show you sql commands thats helps you to find/update/create things.

so lets start.

 

1.SQL command - ALTER TABLE

 

Table structure can be changed by using alter command. With this command Field type or property can be changed or a new field can be added. This sql alter table command is used like create table command.

 

for example we need to change banchat_time field name to ban_time:

 

ALTER TABLE `characters` CHANGE `banchat_time` `ban_time` DECIMAL( 20, 0 ) NULL DEFAULT '0'

 

2.SQL command - BETWEEN

 

Many times we may require to find out records between a range of value.  We can specify one upper limit and one lower limit and the query should return all the records between these two values. The sql BETWEEN command will give us the required result in all these cases. We will apply here BETWEEN command to a numeric field and see how the records are returned from a Mysql table.

 

for example we need to see all items enchantet from 20 to 25:

 

SELECT * FROM `items` WHERE enchant_level BETWEEN 20 and 25

 

3.SQL command - COUNT

 

We can count the number of records in a table with different combinations. Let us first count the total number of records in the table with this count command.

 

for example we need to see count of noblesee characters:

 

SELECT count(*) FROM `characters` WHERE nobless = '1'

 

4.SQL command - DELETE

 

Delete query is used to delete records from the table. This query is used along with some condition to selectively delete records from the table. To delete all the records from the table we can use truncate command like this

 

TRUNCATE `characters`

 

This will remove all the records from the table. This is one way of deleting all the records from a table. Now let us try to delete records based on some selection.

 

Now let us delete all characters hows lvl less then 70:

 

DELETE FROM characters WHERE level < 70

 

5.SQL command - DROP

 

We have seen how to remove records from a table by using truncate sql command. Now we will learn how to use DROP query command to delete a table or a field or some other properties associated with the table. Let us start with deleting a field by DROP command.

 

ALTER TABLE `characters` DROP `ViP`;

 

Here characters is our table name and ViP is one of the field of this table. By this command we can delete the field ViP of the table characters.

 

Deleting a Table

We can use DROP command to remove a table inside the database. Now here is the code to delete the table characters.

 

DROP table characters

 

6.SQL command  - HAVING

 

We can use sql having command to add condition to the query. The advantage of using HAVING command is it can be used in aggregate functions like count, max etc. We can't use SQL WHERE clause here. This way we can add value to our select command as per requirement.

 

For example we need to see all npcs names and levels thats level is upper when 70

 

SELECT name,level FROM npc GROUP BY name HAVING level > 70

 

7.SQL command - INSERT

 

INSERT command is used to append or add a record to a database. A new record will be added at the end of the table every time INSERT command is used. We have to specify what are the fields to be filled while inserting the record, we can add records even without specifying the field names but we have to maintain the order of the field existed in the table and the the order of the data we are inserting. We have to take care of proper formatting of the data we are inserting to the table. We can't insert a string to a numeric field. So we have to format the data and apply the insert command.

 

for example you want to add you characters olympiad points

 

INSERT INTO olympiad_nobles (`char_id`, `class_id`, `char_name`, `olympiad_points`, `competitions_done`) VALUES ('1111','128','Leki','22','9');

 

8.SQL command - IN

 

SQL IN statement can be used with where clause to list a set of matching records of a table. We can use SQL in query with NOT  combination also to filter out some records.

 

for example you want to see characters that are heroes.

 

SELECT * FROM characters WHERE obj_Id IN (SELECT char_id FROM heroes)

 

9.SQL command - LIKE

 

LIKE sql command is used to retrieve or match records in different combinations to get desired result with wildcard. We will try LIKE sql query command and how to use it in our MySQL tables. But the best way to use LIKE query command is to apply it in a text or varchar field along with wildcard %.

 

for example you want to view all gludio clanhalls

 

SELECT * FROM clanhall WHERE location LIKE '%Gludio%'

 

10.SQL command - MAX

 

Some time we will be searching for the maximum value in a field of any MySql table. MAX sql command will return the record with  maximum or highest value in the SQL table.

 

for example you want to see best pvp in server

 

SELECT MAX(pvpkills) FROM characters

 

11.SQL command - MIN

 

Minimum value in a numeric field can be collected by applying  MIN sql command. We will try MIN sql command and how to use it in our tables. But the best way to use MIN command is to apply it in a numeric field.

 

for example you want to know which armor have less pdef

 

SELECT MIN(p_def) FROM armor

 

12.SQL command - UPDATE

 

Update command in SQL is used to change any record in the table. Records are to be manipulated or updated using update command. Conditional update is the most common type of update command used in MySQL also. You are requested to go through the SQL WHERE command before using update command as both are to be used in proper combinations. Here is one simple command but before trying this please understand the implication of this. Without using any where command (or without using any restriction) the command will change all the records of the table. So let us start with the simple command.

 

UPDATE char_templates SET STR="10"

 

This command will change all the records of the table char_templates and will change all the STR field to 10. This is not what is required in common cases so we will be changing records based on some conditions.now we will change all classes MEN to 50 WHERE classes have MEN 40. Our command should selectively update those records for which MEN is equal to “40” and will update them to “50”. We will use one where clause along with update command for updating the records.

 

UPDATE char_templates SET MEN="50" WHERE MEN="40";

 

This command will update only those records for which MEN is equal to '40'. So this way we can update records selectively. Now let us move one more step and change the records selectively based on some value in some other field. We will change records for which MEN is equal to '40' and P_ATK is more than 3. We will promote those classes only who has got more than or equal to 3 P_ATK.

 

UPDATE char_templates SET MEN="50" WHERE MEN="40" AND P_ATK >=3

 

13.SQL command - WHERE

 

WHERE clause is used to collect the records from the table based on some condition specified by the where clause. More than one condition can be added to the where clause by using various logical expressions like AND, OR, < ( less than ), greater than (> ) etc. Logical expressions plays important role in returning the desire records. Let us start with some examples.

 

for example we want to see all heavy armors

 

SELECT * FROM armor WHERE armor_type="heavy"

 

now for example we want to see all heavy armors but only s grades.

 

SELECT * FROM armor WHERE armor_type="heavy" AND crystal_type="s"

 

if you want to see armors thats p.def is more then 300

 

SELECT * FROM armor WHERE p_def > 300

 

if you want to see armor thats p.def is between 200 and 300

 

SELECT * FROM armor WHERE p_def between 200 and 300

 

if you want to see armors thats name contains "imperial"

 

SELECT * FROM armor WHERE name LIKE '%Imperial%'

 

So with this guide is finish. but i will update it with more commands.

 

credits to me

 

 

[HouLiGaN]

Very good. i will need some of these when i start my server in a few days...

Thanks for the share

[Realtek2]

Amaizing guide dude !

Its realy good explaned and alot of examples given..!

+1 by me !

[HellRuLeR]

Very Nice Guide!!!!If i were VIP Member you would have +1!xD!