Yogstation-13

MySQL

Revision as of 19:53, 9 April 2021 by Helpful Herbert (talk | contribs) (Added code-formatting and adjusted setup-steps for the MySQL Workbench into a single list.)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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

  1. Xampp (link) (See our Setting up the database guide)
  2. MySQL Workbench (link)
    • 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.

Priority Description
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:

Priority Description
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 project the table is a part of. Most tables in the tgs database are called ss13_X. 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 'ss13_players' or 'ss13_player', etc. Examples: ss13_player, ss13_connection_log, ss13_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 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
  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 *
  FROM ss13_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 ss13_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 ss13_player
  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: "abcn", "aocwegijawabcmaobr", "abc", "ABC", "AbC", "EAbc", "aWAEGaewgaWEGaBcaegawe". 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 *
  FROM ss13_player
  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 *
  FROM ss13_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 though: 

  SELECT *
  FROM ss13_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 ss13_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 ss13_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 ss13_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 ss13_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 ss13_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: ss13_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 `ss13_player`
  (`id`,`ckey`,`firstseen`,`lastseen`,`ip`,`computerid`,`lastadminrank`)
  VALUES
  (null, "myusername", 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 ss13_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 ss13_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 ss13_player will delete the entire player log (forever).

Here is an example of how to use it if you ever need to: 

  DELETE FROM ss13_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: 

  ss13_player: ckey, lastadminrank, firstseen, lastseen, deleted

Then, instead of using DELETE FROM ss13_player WHERE id = 6, you'd delete it by doing UPDATE ss13_player SET deleted = 1 WHERE id = 6
When selecting data from it, instead of doing SELECT * FROM ss13_player, you'd do SELECT * FROM ss13_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.


Contribution guides
General Hosting a server, Setting up git, Guide to GitKraken, Downloading the source code, Guide to contributing to the game, Reporting issues, Game resources category, Guide to changelogs
Database (MySQL) Setting up the database, MySQL
Coding Understanding SS13 code, SS13 for experienced programmers, Text Formatting
Mapping Guide to mapping, Room Structure, Map merger
Spriting Guide to spriting
Wiki Guide to contributing to the wiki, Wikicode, Styleguide
Retrieved from "https://wiki.yogstation.net/index.php?title=MySQL&oldid=35652"