SQL Guide

[mag1212]

SQL is used to manipulate data in databases. For instance, the account and player data on our servers. It is massively useful but can cause issues if used incorrectly. This guide is the basics ONLY and follows the MySQL implementation of the SQL 2003 standard. I mention this because MySQL (a database) handles SQL slightly differently to Microsoft Access (a different database) and Microsoft SQL Server (another database).
-----
SQL does queries on TABLES inside of a database. A database can have 1 or 1 million tables, it doesn't matter. Each table contains data in rows, much like Excel or Access.

With SQL you can:

* SELECT - Gets data from the database
* DELETE - Deletes data from the database
* UPDATE - Updates data in the database
* INSERT INTO - Adds NEW data to the database


There is also:

* FROM - (Used with SELECT and DELETE) Tells the query which table to run the query on
* SET - (Used with UPDATE) Tells the query what to the value to (this will make sense a little later)
* ORDER BY - (Used with SELECT) Tells the query the order to show data in
* WHERE - (Used with SELECT, UPDATE and DELETE but NOT REQUIRED) This allows for the setting of conditions for the command. The conditions must be met for the command to proceed.

in this guide i will tell you somethign about Select, delete and update statements


SELECT STATEMENTS (Getting data from the database)
-----

Select statements will ALWAYS follow this same structure. Some parts are interchangeable, but the same basic structure applies.

SELECT *
FROM Accounts

This query will show all of the data in the database (Acct, Login, Password, Email, Banned). Useful, but given we have something like 400 000 accounts, it's a lot of data. To help refine this data we can narrow down the fields we want to; Login, Email, Banned.

SELECT Login, Email, Banned
FROM Accounts

This will show all of the users Logins, Emails and whether they are banned. There is less data here now, but still to much to be useful. Lets say we want to see all of the data for the account "Mag1212". NOTE: If the value is a Text field, you must wrap the text in " ". If it is a number, you don't need to worry

SELECT *
FROM Accounts
WHERE Login = "mag1212

This is probably the most common query you will ever see run. It is simple, and to the point, but sometimes you need a more complex query.

This is where AND and OR come in. They are used to seperate conditions in the WHERE statement.
With AND - Both conditions need to be met otherwise the data will not be returned
With OR - Only 1 condition needs to be met in order to return

Lets say I want to find 2 accounts: Mag and Mag1212. This is how I would do it:

SELECT *
FROM Accounts
WHERE Login = "Mag" OR Login = "Mag1212"

If I used AND here, there would be no data shown because your login cannot be: Mag1212 AND Mag at the same time

If I wanted to know all of my accounts that are banned:

SELECT *
FROM Accounts
WHERE Email = "[email protected]" AND Banned = 1


_____________________________________________________

DELETE STATEMENTS (Removing data from the database)
-----

Delete Statements follow the same structure as select statements. In fact they are so similar that I often write Delete Statements as Select Statements so I can test them first. You do not need anything following Delete as Delete removes all data by default (Like * in select statements).

DO NOT USE THE FOLLOWING UNLESS YOU WANT TO DELETE EVERYTHING:

DELETE
FROM Accounts

Lets say you want to delete someone's account. Like mine for instance >.> You can use a WHERE statement to do this, just like SELECT

DELETE
FROM Accounts
WHERE Login = "Mag1212"

Lets say you also want to delete My brothers account also:

DELETE
FROM Accounts
WHERE Login = "Mag1212" or Login = "Mag1212's Brother"


Or all the banned accounts

DELETE
FROM Accounts
WHERE Banned = 1

BE CAREFUL USING DELETE COMMANDS. ONCE THE DATA IS DELETED IT IS GONE FOREVER!!!

When the command completes, it well tell you how many rows have been deleted. If it returns 400204 rows, then I suggest you might have broken something When in doubt, write the command as a select statement first.


UPDATE STATEMENTS (For changing information already in the database)
-----

Update Statements use a different structure to SELECT and DELETE statements, but follow the same general idea

Update statements are useful for changing information in the database quickly and easily. For instance, let's say that I want to change the email address on all of my accounts.

UPDATE Accounts
SET Email = "[email protected]"
WHERE email = "[email protected]"

As you can see, the "From" statement comes first this time, but it's basically the same idea. Rather then from we are using UPDATE however. The SET command is also used with UPDATE commands.

Now, technically you can use multiple values in the set command, however this doesn't work overly well on our database for some reason, and as such I am more likely to use multiple commands to update the information

All of the same rules that apply to SELECT statements still work with the WHERE statements. AND and OR can be used

UPDATE Accounts
SET Email = "[email protected]"
WHERE Login = "Mag" or Login = "Mag1212"

When the command is completed, it will return how many rows have been updated. This lets you know how much has changed. IF YOU MAKE A CHANGE AND THE DATA WAS ALREADY SET TO WHAT YOUR CHANGING IT TOO. IT WILL NOT CHANGE AND THUS WILL NOT BE COUNTED.

There are 2 methods of Insert commands used commonly. You can either:
INSERT INTO Accounts
VALUES (value1, value2, value3,...)
In this case the values are lined up with the columns in the database. Value 1 = the leftmost column, Value 2 = the second leftmost and so on..

The second method defines what columns will receive which values.
INSERT INTO Accounts (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

[mag1212]

reserved .

[worldofevisock]

Nice guide, +rep

[mag1212]

100 views and only 1 comment

[Reflection]

Well done! Learned a few things.

[mag1212]

your welcome mate

[Mr. E]

nice guide

[mag1212]

thank you dude

[Link_S]

You don't see SQL guides out there a lot. You've deserved my 2x +Rep

[Alcopop]

Amazing - even a noob could understand

[mag1212]

thanks everyone

[P1raten]

Thank you.. +rep x3

EDIT: Need to spread.

[mag1212]

Thank you.. +rep x3

EDIT: Need to spread.

thank you .

[lopiecart]

nice guide

[mag1212]

thank you .