1,133
edits
MySQL: Difference between revisions
Added code-formatting and adjusted setup-steps for the MySQL Workbench into a single list.
imported>LiamLime No edit summary |
(Added code-formatting and adjusted setup-steps for the MySQL Workbench into a single list.) |
||
| 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 | |||
#*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 | |||
|} | |||
There are more, but this guide doesn't deal with them: | |||
{| class="wikitable" | |||
! style="width: 90px;"| Priority | |||
!Description | |||
|- | |||
There are more, but this guide doesn't deal with them: | !CREATE | ||
CREATE | |Creates databases, tables, views, procedures, triggers, etc. Whatever you define | ||
ALTER | |- | ||
DROP | !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 == | ||
| Line 43: | Line 61: | ||
Execute the following statement: | Execute the following statement: | ||
SELECT * | SELECT * | ||
FROM ss13_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 | SELECT ckey, lastadminrank | ||
FROM ss13_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 * | SELECT * | ||
FROM ss13_player | 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 * | SELECT * | ||
FROM ss13_player | 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 * | SELECT * | ||
FROM ss13_player | FROM ss13_player | ||
WHERE ckey LIKE "%abc%" | 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. | 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. | ||
| Line 89: | Line 107: | ||
Numbers use what you'd expect. >, >=, <=, <, =, != | Numbers use what you'd expect. >, >=, <=, <, =, != | ||
SELECT * | SELECT * | ||
FROM ss13_player | 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 101: | Line 119: | ||
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 * | SELECT * | ||
FROM ss13_player | 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 | 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 * | SELECT * | ||
FROM ss13_player | 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 * | SELECT * | ||
FROM ss13_player | 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 * | SELECT * | ||
FROM ss13_player | 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 | SELECT ckey, firstseen | ||
FROM ss13_player | 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 | SELECT ckey | ||
FROM ss13_player | 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 * | SELECT * | ||
FROM ss13_player | 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: ss13_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 `ss13_player` | INSERT INTO `ss13_player` | ||
(`id`,`ckey`,`firstseen`,`lastseen`,`ip`,`computerid`,`lastadminrank`) | (`id`,`ckey`,`firstseen`,`lastseen`,`ip`,`computerid`,`lastadminrank`) | ||
VALUES | VALUES | ||
(null, "myusername", 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 ss13_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 ss13_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 ss13_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 ss13_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: | ||
ss13_player: ckey, lastadminrank, firstseen, lastseen, deleted | 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 | 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 ss13_player, you'd do SELECT * FROM ss13_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]] | ||