MySQL: Difference between revisions

From Yogstation-13
Jump to navigation Jump to search
imported>Errorage
No edit summary
 
(Added code-formatting and adjusted setup-steps for the MySQL Workbench into a single list.)
 
(6 intermediate revisions by 3 users not shown)
Line 5: Line 5:
#Xampp ([http://www.apachefriends.org/en/xampp.html link]) (See our [[Downloading the source code#Setting up the database|Setting up the database]] guide)
#Xampp ([http://www.apachefriends.org/en/xampp.html link]) (See our [[Downloading the source code#Setting up the database|Setting up the database]] guide)
#MySQL Workbench ([http://www.mysql.com/products/workbench/ link])
#MySQL Workbench ([http://www.mysql.com/products/workbench/ link])
 
#*Once you have the database set up, open MySQL Workbench
Set up MySQL Workbench:
#*Select ''"New Connection"''
 
#:*'''Connection name:''' My Database ''(or whatever you want)''
*Once you have the database set up, open MySQL Workbench
#:*'''Hostname:''' 127.0.0.1
*Select 'New Connection'
#:*'''Port:''' 3306
:*Connection name: My Database (or whatever you want)
#:*'''Username:''' root
:*Hostname: 127.0.0.1
#*Ok
:*Port: 3306
#*Doubleclick the new connection in the list
:*Username: root
*Ok
*Doubleclick the new connection in the list


== Types of queries ==
== Types of queries ==


Their names describe what you want to do.
Their names describe what you want to do.
{| class="wikitable"
! style="width: 90px;"| 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
|}


SELECT - Selects data from the database<br>
There are more, but this guide doesn't deal with them:
INSERT - Inserts data into a table<br>
{| class="wikitable"
UPDATE - Updates existing data in a table<br>
! style="width: 90px;"| Priority
DELETE - Deletes existing data from a table
!Description
 
|-
There are more, but this guide doesn't deal with them:<br>
!CREATE
CREATE - Creates databases, tables, views, procedures, triggers, etc. Whatever you define <br>
|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)<br>
|-
DROP - Deletes something you created (DELETE only deletes the data, DROP deletes the table itself.)
!ALTER
|Changes the properties of something you created (for example, which columns a table has)
|-
!DROP
|Deletes something you created (<code>DELETE</code> only deletes the data, <code>DROP</code> deletes the table itself.)
|}


== Table Naming Convention ==
== 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.
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 ==
== SELECT ==
Line 43: Line 61:
Execute the following statement:
Execute the following statement:


SELECT *<br>
  SELECT *
FROM erro_player
  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.
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.
Line 52: Line 70:
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:
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>
  SELECT ckey, lastadminrank
FROM erro_player
  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.
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.
Line 59: Line 77:
=== Filter rows ===
=== 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.
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 <code>WHERE</code> portion of the <code>SELECT</code> statement.


==== Strings ====
==== Strings ====
Line 65: Line 83:
Execute this:
Execute this:


SELECT *<br>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE lastadminrank = "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.
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>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE lastadminrank != "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:
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:
Line 79: Line 97:
==== LIKE ====
==== LIKE ====


SELECT *<br>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE ckey LIKE "%rro%"
  WHERE ckey LIKE "%abc%"


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: "e'''rro'''", "'''rro'''n", "aocwegijaw'''rro'''maobr", "'''rro'''", "'''RRO'''", "'''RrO'''", "E'''Rro'''", "aWAEGaewgaWEG'''rRo'''aegawe". Your example might require you to replace the string rro with a different character combination, depending on which ckeys you have logged.
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 ====
Line 89: Line 107:
Numbers use what you'd expect. >, >=, <=, <, =, !=
Numbers use what you'd expect. >, >=, <=, <, =, !=


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


will return all lines where the id is lower than 50.
will return all lines where the id is lower than 50.
Line 97: Line 115:
==== Date and time ====
==== Date and time ====


The erro_player table contains two fields which are of type 'datetime': firstseen and lastseen.
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:
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>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE firstseen = lastseen
  WHERE firstseen = lastseen


===== DATE() =====
===== 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:
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 *<br>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE DATE(firstseen) = "2013-04-19"
  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.)
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>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE DATE(firstseen) > "2013-04-17"
  WHERE DATE(firstseen) > "2013-04-17"


===== DATEDIFF(), NOW() =====
===== DATEDIFF(), NOW() =====
Line 123: Line 141:
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:
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>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE DATEDIFF(Now(),firstseen) < 7
  WHERE DATEDIFF(Now(),firstseen) < 7


This will select everyone who joined 7 or fewer days ago.
This will select everyone who joined 7 or fewer days ago.
Line 133: Line 151:
Easy:
Easy:


SELECT ckey, firstseen<br>
  SELECT ckey, firstseen
FROM erro_player<br>
  FROM ss13_player
WHERE DATEDIFF(Now(),firstseen) < 7
  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:
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 <code>WHERE</code> section, this will still work:


SELECT ckey<br>
  SELECT ckey
FROM erro_player<br>
  FROM ss13_player
WHERE DATEDIFF(Now(),firstseen) < 7
  WHERE DATEDIFF(Now(),firstseen) < 7


=== Multiple conditions, AND and OR ===
=== 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:
So from here on, when we talk about conditions, we mean stuff in the <code>WHERE</code> 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 <code>DATEDIFF(Now(),firstseen)</code> has to be higher than 7, but lower than 14. Well, the section title gave it away:


SELECT *<br>
  SELECT *
FROM erro_player<br>
  FROM ss13_player
WHERE DATEDIFF(Now(),firstseen) >= 7
  WHERE DATEDIFF(Now(),firstseen) >= 7
:AND DATEDIFF(Now(),firstseen) < 14
  :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.
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.
Line 160: Line 178:
== INSERT ==
== INSERT ==


INSERT is used to insert a new entry into a table.
<code>INSERT</code> 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.
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.
Line 168: Line 186:
* For strings, use single or double quotes to define them. Multiline strings work.
* 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 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 do a date based on <code>Now()</code>, do <code>NOW() + INTERVAL 1 DAY</code> (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"
* 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.
* 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:
Here's an example of a working insert statement:


INSERT INTO `erro_player`
  INSERT INTO `ss13_player`
(`id`,`ckey`,`firstseen`,`lastseen`,`ip`,`computerid`,`lastadminrank`)
  (`id`,`ckey`,`firstseen`,`lastseen`,`ip`,`computerid`,`lastadminrank`)
VALUES
  VALUES
(null, "errorage", Now() - INTERVAL 7 DAY, Now(), "123.123.123.123", "12345", "GameAdmin");
  (null, "myusername", Now() - INTERVAL 7 DAY, Now(), "123.123.123.123", "12345", "GameAdmin");


=== In MySQL Workbench ===
=== In MySQL Workbench ===
Line 185: Line 203:
== UPDATE ==
== 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.
Guide not made yet. What I want to point out is that UPDATE is very dangerous. If you do <code>UPDATE ss13_player SET ckey = "someone"</code>, 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:
Here is an example of how to do this right, but you REALLY need to be careful with the <code>UPDATE</code> statement:


UPDATE erro_player SET ckey = "someone" WHERE id = 6
  UPDATE ss13_player SET ckey = "someone" WHERE id = 6


=== In MySQL Workbench ===
=== In MySQL Workbench ===
Line 197: Line 215:
== DELETE ==
== DELETE ==


Don't even use. DELETE FROM erro_player will delete the entire player log (forever).  
Don't even use. <code>DELETE FROM ss13_player</code> will delete the entire player log (forever).  


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


DELETE FROM erro_player WHERE id = 6
  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:
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
  ss13_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.<br>
Then, instead of using <code>DELETE FROM ss13_player WHERE id = 6</code>, you'd delete it by doing <code>UPDATE ss13_player SET deleted = 1 WHERE id = 6</code><br>
When selecting data from it, instead of doing SELECT * FROM erro_player, you'd do SELECT * FROM erro_player WHERE ISNULL(deleted)<br>
When selecting data from it, instead of doing <code>SELECT * FROM ss13_player</code>, you'd do <code>SELECT * FROM ss13_player WHERE ISNULL(deleted)</code><br>
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.
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 <code>DELETE</code> statement, the data would be pretty much lost, if you hadn't previously backed it up.


=== In MySQL Workbench ===
=== 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.
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 <code>DELETE</code> works, you can see the statements it provides you with.




{{Contribution guides}}
{{Contribution guides}}
[[Category:Guides]] [[Category:Game Resources]]
[[Category:Guides]] [[Category:Game Resources]]

Latest revision as of 19:53, 9 April 2021

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