enegue
07-10-09, 05:11 AM
SQL stands for "Structured Query Language". In my time I have heard it called about 20 different things by different people, the most common being "Sequenced Query Language". The relevance here is that both of the names are true. The language has a cleary defined structure, and it executes in sequence.
SQL is used to manipulate data in databases. For instance, the account and player data on your server. 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 1or 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.
Lastly: There is something called a "Wild Card". This is used to represent date that is unknown or to save time.
* - Represents ALL or EVERYTHING
% - Represents a single letter or number
For all of my examples I will use a simplified version of our account database, just because it gives you an idea of real world usage smile.gif
Accounts contains:
Acct [Number] - The accounts numeric ID
Login [Text] - The login name for the account
Password [Text] - The password for the account
Email [Text] - The accounts email
Banned [Number] - A number indicating whether that account is banned/for how long.
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 "Satan". 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 :D
SELECT *
FROM Accounts
WHERE Login = "Satan
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: Satan and GMSatan. This is how I would do it:
SELECT *
FROM Accounts
WHERE Login = "Satan" OR Login = "GMSatan"
If I used AND here, there would be no data shown because your login cannot be: Satan AND GMSatan at the same time :D
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 CJR's for instance >.> You can use a WHERE statement to do this, just like SELECT smile.gif
DELETE
FROM Accounts
WHERE Login = "CJR"
Lets say you also want to delete Lexxie's account also:
DELETE
FROM Accounts
WHERE Login = "CJR" or Login = "Lexxie"
Or all the banned accounts :D
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 smile.gif
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 smile.gif
All of the same rules that apply to SELECT statements still work with the WHERE statements. AND and OR can be used :D
UPDATE Accounts
SET Email = "[email protected]"
WHERE Login = "Satan" or Login = "GMSatan"
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.
Disclaimer
I absolutely take no credit for this guide/intro. Any +rep to me will be a bonus.
Credits
This SQL intro has been made by satan of WOC.
SQL is used to manipulate data in databases. For instance, the account and player data on your server. 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 1or 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.
Lastly: There is something called a "Wild Card". This is used to represent date that is unknown or to save time.
* - Represents ALL or EVERYTHING
% - Represents a single letter or number
For all of my examples I will use a simplified version of our account database, just because it gives you an idea of real world usage smile.gif
Accounts contains:
Acct [Number] - The accounts numeric ID
Login [Text] - The login name for the account
Password [Text] - The password for the account
Email [Text] - The accounts email
Banned [Number] - A number indicating whether that account is banned/for how long.
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 "Satan". 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 :D
SELECT *
FROM Accounts
WHERE Login = "Satan
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: Satan and GMSatan. This is how I would do it:
SELECT *
FROM Accounts
WHERE Login = "Satan" OR Login = "GMSatan"
If I used AND here, there would be no data shown because your login cannot be: Satan AND GMSatan at the same time :D
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 CJR's for instance >.> You can use a WHERE statement to do this, just like SELECT smile.gif
DELETE
FROM Accounts
WHERE Login = "CJR"
Lets say you also want to delete Lexxie's account also:
DELETE
FROM Accounts
WHERE Login = "CJR" or Login = "Lexxie"
Or all the banned accounts :D
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 smile.gif
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 smile.gif
All of the same rules that apply to SELECT statements still work with the WHERE statements. AND and OR can be used :D
UPDATE Accounts
SET Email = "[email protected]"
WHERE Login = "Satan" or Login = "GMSatan"
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.
Disclaimer
I absolutely take no credit for this guide/intro. Any +rep to me will be a bonus.
Credits
This SQL intro has been made by satan of WOC.