Thread: [SQL]Tutorial.

////////////SQL Introduction.////////////

Although SQL is an ANSI standard, there are many different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard. However, the above posted commands, are quite nice, and come in handy, when working with a WorldOfWarcraft database, for any emulator.<

Another note : Standing the other way of Lua, SQL isn't case sensitive, you should keep that in mind.

////////////The Start Of The Guide////////////
If you know this SQL commands, you're quite far.

Code:

   *  SELECT - extracts data from a database
    * UPDATE - You can update your data with this commands in your database.
   * DELETE - You can delete unwished collumns with this command from your database.
    * INSERT INTO - You can insert new data in your database, with this command.

& then standing at the side of this

Code:

    *  CREATE DATABASE - creates a new database
    * CREATE TABLE - Name says it itself.
    * ALTER TABLE - Alter, means modify, so you can simply modify a table with this command.
    * DROP TABLE - Delete a table with this command.

So, we'll start with the acctual guide now.
Your first SQL statement.

The following SQL statement will select all the records in the "Items" table:

Code:

SELECT * FROM Items

Now, that you selected the records, from the Items table, you can also select a certain collumn out of it. We'll still be working in the 'Items' database.

SQL SELECT Syntax

Code:

SELECT Name (This is the collumn name.)
FROM Items  (Table name.)

and

Code:

SELECT * FROM Items (Table name.)

Now, you want to select the content of the columns named Names, Itemid from the table 'Items.' This can be done this way :

Code:

SELECT Names,Itemid(Collumn Names) FROM Items(Table Name)

Next, selecting all collumns, from the table 'Items.' You can do this on a quite simple way. The * in SQL stands for selecting all the collumns.
So :

Code:

Select * From Items.

The next part. Using Distincts. Hence I'm not so good at them yet, I took them from a small guide, posted on a private forum.

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

Code:

SELECT DISTINCT column_name(s)
FROM table_name

Now we want to select only the distinct values from the column named "City" from the table above.

We use the following SELECT statement:

Code:

SELECT DISTINCT City FROM Persons

End of the copied part.
Now, we'll move on to the next part of my personal guide.

Using the 'Where' command. When you select somthing, with the 'Where' command, you select collumns, with specified vallues.

Example.

Code:

SELECT Names (Collumn name.
FROM Items (Table name.)
WHERE (Your specification here.)

We'll use the WHERE command now, to select all collumns in the table 'Items' where it contains a 'Lol' in the name.

Code:

SELECT * FROM Items (Selects all collumns from the table items.)
WHERE Name='Lol' (Speaks for itself.)

When you're using 1,2,3,.. numbers, so, you arn't needed to use ' at the begin, and to close it at the end. When you're using text values, you're supposed to open, and close it.

Code:

This is correct:

SELECT * FROM Items WHERE Name='Lol'

This is wrong:

SELECT * FROM Persons WHERE Name=Lol

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

So :

Code:

Select * FROM Items 
Where Name = 'Lol' (Name, both are collumns.)
AND Id ='4500' (The Itemid.)

So no need to apply a second 'Where.' when you're using an AND command.

Next, the use of the 'OR.' command :

Code:

Select * FROM Items
Where Name ='Lol'
OR Name='Lol1'.

Q:
A:Yes you can.
On this way :

Code:

SELECT * FROM ITEMS 
WHERE Name='Lol'
AND (Itemid='4500' OR Itemid='4501')

Congrats, you now can 'look up' collumns, and tables, select them, from the database.

////////////INSERTING////////////

The INSERT INTO statement is used to insert a new row in a table.

Code:

INSERT INTO Items
VALUES (value1, value2, value3,...)

The value insert of 1 - 2 - 3 has to be the structure of your database.



for example.

Code:

INSERT INTO Items
VALUES (4500(Id),'Loller(Name)', '25484(Displayid)')

This will add the data into all collumns, now we're going to add data into specific collumns :

Code:

INSERT INTO Persons (Id, Name)
VALUES (4500, 'Loller')

In this case, your inserted item doesn't have a displayid.

Now we're going to update the collumns.

Code:

UPDATE Items
SET Names=Lollerlol, Itemid=4500
WHERE Names=Lol AND Itemid=45000

Deleting collumns. <3

Code:

DELETE FROM Items
WHERE Itemid=4500

Can it be more easyer?

Code:

DELETE FROM Items
WHERE Name='Lol' AND Name='Loller'

This is it so far. Hope you enjoyed it.

---

› See More: [SQL]Tutorial.

thanks,