Редактирование: MySQL (раздел)

== 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 *<br>
FROM ss13_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<br>
FROM ss13_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 *<br>
FROM ss13_player<br>
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 *<br>
FROM ss13_player<br>
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 *<br>
FROM ss13_player<br>
WHERE ckey LIKE "%abc%"

This will return a list of players, whose ckey contains the letters 'abc'. The % signs before and after abc mean that anything can be located before or after abc. LIKE also ignores case, so all of the following will work: "'''abc'''n", "aocwegijaw'''abc'''maobr", "'''abc'''", "'''ABC'''", "'''AbC'''", "E'''Abc'''", "aWAEGaewgaWEG'''aBc'''aegawe". Your example might require you to replace the string abc with a different character combination, depending on which ckeys you have logged.

==== Numbers ====

Numbers use what you'd expect. >, >=, <=, <, =, !=

SELECT *<br>
FROM ss13_player<br>
WHERE id < 50

will return all lines where the id is lower than 50.

==== Date and time ====

The ss13_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 *<br>
FROM ss13_player<br>
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 *<br>
FROM ss13_player<br>
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 *<br>
FROM ss13_player<br>
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 *<br>
FROM ss13_player<br>
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<br>
FROM ss13_player<br>
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<br>
FROM ss13_player<br>
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 *<br>
FROM ss13_player<br>
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.