Yogstation-13

MySQL: Difference between revisions

MySQL (view source)

Revision as of 05:20, 4 October 2015

2 bytes added ,  05:20, 4 October 2015
no edit summary
imported>LiamLime
imported>LiamLime
No edit summary
Line 44: Line 44:


SELECT *<br>
SELECT *<br>
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 53: Line 53:


SELECT ckey, lastadminrank<br>
SELECT ckey, lastadminrank<br>
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 66: Line 66:


SELECT *<br>
SELECT *<br>
FROM erro_player<br>
FROM ss13_player<br>
WHERE lastadminrank = "Player"
WHERE lastadminrank = "Player"


Line 72: Line 72:


SELECT *<br>
SELECT *<br>
FROM erro_player<br>
FROM ss13_player<br>
WHERE lastadminrank != "Player"
WHERE lastadminrank != "Player"


Line 80: Line 80:


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


Line 90: Line 90:


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


Line 97: Line 97:
==== 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 *<br>
FROM erro_player<br>
FROM ss13_player<br>
WHERE firstseen = lastseen
WHERE firstseen = lastseen


Line 110: Line 110:


SELECT *<br>
SELECT *<br>
FROM erro_player<br>
FROM ss13_player<br>
WHERE DATE(firstseen) = "2013-04-19"
WHERE DATE(firstseen) = "2013-04-19"


Line 116: Line 116:


SELECT *<br>
SELECT *<br>
FROM erro_player<br>
FROM ss13_player<br>
WHERE DATE(firstseen) > "2013-04-17"
WHERE DATE(firstseen) > "2013-04-17"


Line 124: Line 124:


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


Line 134: Line 134:


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


Line 140: Line 140:


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


Line 148: Line 148:


SELECT *<br>
SELECT *<br>
FROM erro_player<br>
FROM ss13_player<br>
WHERE DATEDIFF(Now(),firstseen) >= 7
WHERE DATEDIFF(Now(),firstseen) >= 7
:AND DATEDIFF(Now(),firstseen) < 14
:AND DATEDIFF(Now(),firstseen) < 14
Line 170: Line 170:
* 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 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"
* 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 185:
== 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 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:
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
UPDATE ss13_player SET ckey = "someone" WHERE id = 6


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


Don't even use. DELETE FROM erro_player will delete the entire player log (forever).  
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:
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 DELETE FROM ss13_player WHERE id = 6, you'd delete it by doing UPDATE ss13_player SET deleted = 1 WHERE id = 6.<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 SELECT * FROM ss13_player, you'd do SELECT * FROM ss13_player WHERE ISNULL(deleted)<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 DELETE statement, the data would be pretty much lost, if you hadn't previously backed it up.


Anonymous user
Retrieved from "https://wiki.yogstation.net/wiki/Special:MobileDiff/3729"