MySQL
MySQL is a language you use with databases. Queries are however not interpreted 'one line at a time', instead they are best described as a single instruction, with lots of arguments, some of which can be conditions.
Software
- Xampp (link) (See our Setting up the database guide)
- MySQL Workbench (link)
Set up MySQL Workbench:
- Once you have the database set up, open MySQL Workbench
- Select 'New Connection'
- Connection name: My Database (or whatever you want)
- Hostname: 127.0.0.1
- Port: 3306
- Username: root
- Ok
- Doubleclick the new connection in the list
Types of queries
Their names describe what you want to do.
SELECT - Selects data from the database
INSERT - Inserts data into a table
UPDATE - Updates existing data in a table
DELETE - Deletes existing data from a table
There are more, but this guide doesn't deal with them:
CREATE - Creates databases, tables, views, procedures, triggers, etc. Whatever you define
ALTER - Changes the properties of something you created (for example, which columns a table has)
DROP - Deletes something you created (DELETE only deletes the data, DROP deletes the table itself.)
Table Naming Convention
Tables are usually named in lower case, they use the underscore as a word separator and the first word is usually the name of the user that created the table or who owns the database. Most tables in the tgs database are called erro_X, because 'erro' was the user who made them. The last common thing is to always use singular. So 'user_reports' is not a good name, 'user_report' is. These naming conventions are there to help you down the road, so you will not be in doubt whether a table is called 'erro_players' or 'erro_player', etc. Examples: erro_player, erro_connection_log, erro_poll_question, etc.
SELECT
Let's select something. This assumes you have the tgs database set up and that you played a few rounds with it enabled.
Everything
Execute the following statement:
SELECT *
FROM erro_player
You now have a list of players who played on your server so far. I can't provide you with a screenshot for privacy reasons, but you can see what you get in your output. The * in the statement defines that you want to select all columns.
Filter columns
The problem is that when you are selecting something from the database in programs, you'll want as little data transfer as possible. So selecting all columns is rarely needed. Let's suppose we want a list of players and what their last admin rank was:
SELECT ckey, lastadminrank
FROM erro_player
Voila. Once you execute this, you will get a table, which has a list of all the players who played on your server. The returned table will contain two columns: Their ckey and the admin rank they held when they last connected.
Filter rows
Well, now that we managed to isolate a few columns, let's see about filtering through rows. We'll return to selecting all columns tho. Conditions for which rows you want returned and which you don't are defined in the WHERE portion of the SELECT statement.
Strings
Execute this:
SELECT *
FROM erro_player
WHERE lastadminrank = "Player"
This will return a list of people, who have their 'lastadminrank' column set to "Player". Note the single = So no admins will be in the returned list. So how do we get admins? Simple.
SELECT *
FROM erro_player
WHERE lastadminrank != "Player"
As you know != means "not equal". It is however case sensitive. It also doesn't allow you to do any clever filtering. Let's look at LIKE:
LIKE
SELECT *
FROM erro_player
WHERE ckey LIKE "%rro%"
This will return a list of players, whose ckey contains the letters 'err'. The % signs before and after rro mean that anything can be located before or after rro. LIKE also ignores case, so all of the following will work: "erro", "rron", "aocwegijawrromaobr", "rro", "RRO", "RrO", "ERro", "aWAEGaewgaWEGrRoaegawe". Your example might require you to replace the string rro with a different character combination, depending on which ckeys you have logged.
Numbers
Numbers use what you'd expect. >, >=, <=, <, =, !=
SELECT *
FROM erro_player
WHERE id < 50
will return all lines where the id is lower than 50.
Date and time
The erro_player table contains two fields which are of type 'datetime': firstseen and lastseen.
You can compare them directly with >, <, =, !=. Note the single =. For example: If you want to only select people who have only logged in once, and then never again:
SELECT *
FROM erro_player
WHERE firstseen = lastseen
DATE()
You can however also select people who were first seen on a particular date. You'll need to use a function for that tho:
SELECT *
FROM erro_player
WHERE DATE(firstseen) = "2013-04-19"
This will return a list of players who were first seen on 19 April 2013. If you want to get people who joined after April 17 2013, execute the following: (NOTE, This will only show people who joined on 18 April or later! Use >= if you want to include 17 April.)
SELECT *
FROM erro_player
WHERE DATE(firstseen) > "2013-04-17"
DATEDIFF(), NOW()
Okay, so now you know how to select people who joined after a constant date. But what about if you want to only select people who joined in the last 7 days? The date keeps changing all the time, the date in the select statement, however, remains the same. The fix is pretty easy:
SELECT *
FROM erro_player
WHERE DATEDIFF(Now(),firstseen) < 7
This will select everyone who joined 7 or fewer days ago.
Combining column and row filtering
Easy:
SELECT ckey, firstseen
FROM erro_player
WHERE DATEDIFF(Now(),firstseen) < 7
Does the same as the statement a few lines above, but also filters columns. Note that you don't have to select all the columns you use in your where section. For example, even if you are not returning 'firstseen', but are using it in the WHERE section, this will still work:
SELECT ckey
FROM erro_player
WHERE DATEDIFF(Now(),firstseen) < 7
Multiple conditions, AND and OR
So from here on, when we talk about conditions, we mean stuff in the WHERE section. You learned pretty much everything about filtering by columns already, so we'll concentrate on how to get the data you want. Let's suppose you want to select people who joined between 2 weeks ago and a week ago. So people who were already here 7 days ago, but were not yet here 14 days ago. Their DATEDIFF(Now(),firstseen) has to be higher than 7, but lower than 14. Well, the section title gave it away:
SELECT *
FROM erro_player
WHERE DATEDIFF(Now(),firstseen) >= 7
- AND DATEDIFF(Now(),firstseen) < 14
You can use AND, OR, NOT and XOR. &&, || and ! also work, but it is more common to use the words, so I encourage you to do that. Brackets ( and ) also work.
Joining tables
This is where the magic of MySQL happens... But I'm still writing it.
INSERT
INSERT is used to insert a new entry into a table.
If you have MySQL workbench opened, click "create a new SQL tab for executing queries" in the upper left, then right click the table you wish to insert into and select "Send to sql editor > insert statement". Edit the grayed out section with actual data.
- For columns marked 'id', use null. They have auto increment enabled, so they will just add the next value.
- For numbers, just write the number
- For strings, use single or double quotes to define them. Multiline strings work.
- If you wish to add the current date, use Now()
- If you wish to do a date based on Now(), do NOW() + INTERVAL 1 DAY (You can use SECOND, MINUTE, HOUR, DAY, WEEK,... Use google.)
- If you wish to add a date, use the format (with quotes) "2013-04-19 12:05:27"
- You can also add null for columns that don't need a value, for example: erro_ban inserts null into the 'unbanned', which is set to 1 when the person gets unbanned.
Here's an example of a working insert statement:
INSERT INTO `erro_player` (`id`,`ckey`,`firstseen`,`lastseen`,`ip`,`computerid`,`lastadminrank`) VALUES (null, "errorage", Now() - INTERVAL 7 DAY, Now(), "123.123.123.123", "12345", "GameAdmin");
In MySQL Workbench
In the menu on the left, expand the database (probably called 'feedback'), right click the table you wish to delete from, select 'edit table data', at the end of the table, there is a row of 'null' values, click on it, it'll change the entry you clicked on to an input field. Fill out the rest of the line, and repeat until you entered all the lines you want. Then hit 'Apply' in the bottom right and execute. Look at the output you get when you hit 'Apply' if you want more examples for the INSERT statement.
UPDATE
Guide not made yet. What I want to point out is that UPDATE is very dangerous. If you do UPDATE erro_player SET ckey = "someone", it will set the value "someone" to the ckey column for EVERY SINGLE ROW.
Here is an example of how to do this right, but you REALLY need to be careful with the UPDATE statement:
UPDATE erro_player SET ckey = "someone" WHERE id = 6
In MySQL Workbench
In the menu on the left, expand the database (probably called 'feedback'), right click the table you wish to delete from, select 'edit table data', doubleclick any field, edit it, select something else (so the 'Apply' button in the bottom right is enabled), hit the 'Apply' button and execute it. If you wish to see more examples of how UPDATE works, you can see the statements it provides you with.
DELETE
Don't even use. DELETE FROM erro_player will delete the entire player log (forever).
Here is an example of how to use it if you ever need to:
DELETE FROM erro_player WHERE id = 6
The way you should handle this when making new databases is to include a 'deleted' column in a table. For example:
erro_player: ckey, lastadminrank, firstseen, lastseen, deleted
Then, instead of using DELETE FROM erro_player WHERE id = 6, you'd delete it by doing UPDATE erro_player SET deleted = 1 WHERE id = 6.
When selecting data from it, instead of doing SELECT * FROM erro_player, you'd do SELECT * FROM erro_player WHERE ISNULL(deleted)
So you'd only 'effectively' be deleting them, they would however still remain in the database. This allows you to fix potential issues, allowing you to restore entries by editing the 'deleted' column. If you'd have used the DELETE statement, the data would be pretty much lost, if you hadn't previously backed it up.
In MySQL Workbench
In the menu on the left, expand the database (probably called 'feedback'), right click the table you wish to delete from, select 'edit table data', select any number of entries you wish to delete (don't have to select the entire row, just have something in the row selected), right click the selected area and select 'Delete Row(s)', select something else (so the 'Apply' button in the bottom right is enabled), hit the 'Apply' button and execute it. If you wish to see more examples of how DELETE works, you can see the statements it provides you with.
