First, you should read it all carefully before you start asking questions!
I've seen a lot of people not understanding how SQL actually works so I thought I could give you a little taste of how to run your own queries.
I will only show you how to insert new rows, delete rows, and to get/view rows.
Assuming we are going to use the command promt (CMD) to do this we have to connect and select database first. Notice that I use colored text to refer to the specific parts in the example.
Remember that this is general SQL syntax. Meaning it can be used with many other programs and databases. (You can use this with phpMyAdmin, Mysql GUI tools (query browser), LuaSQL and so on.)
Figure out where mysql.exe is located and go to that directory. It may be that you don't need to do it if you installed it yourself and selected "add to windows PATH" (like me).
Open CMD and write:
IMPORTANT: All QUERIES needs an ; to mark the end of the query. (Notice that "USE forgotten" does not need it (=not a query), but "SHOW tables;" needs it.)
We can start off with how to view/get rows:
It is quite easy, logical and simple to understand:
You start of with SELECT, notice, it's easier to read the query if you write all the commands with CAPITAL letters and everything else with normal letters, but not necessary.
After SELECT you write which fields you want to get. If the table got many fields, and your not interested in all of them you simply write the name of the fields you want. If you want to get all fields, just write *
Next you write which table to get it FROM. In the example I'm using the players table.
This is really all you need, but it is with the command WHERE you can do a lot of things.
Lets say we want to get everyone above level 30, name starting with B, group_id like 1 and ordered by last login? Notice: if we are searching for text we need to enclose it in ' and we need to use LIKE to compare. % is wildcard.
To separate the different conditions we use AND or OR depending on how we want it.
Then we use ORDER BY to order it by the field we want. And since we want the one who logged on last to be first we use DESC after the field name, which is short for descending. (default is ascending).
At last we can use LIMIT to restraint the number of results we want.
Also, around every table name and field name it is smart to use ` so that like the field "time" is understood correctly by mysql.
Example:
This was a bit "complex" example, but it can be as simple as this too:
Now lets move onto deleting.
Its quite similar to SELECT, but doesn't print any results, only how many rows that was deleted.
We can use almost the exact same query, just changing some words.
Here, LIMIT can be your best friend, as it is NOT POSSIBLE to undelete anything!
Lets say we want to delete the 150 oldest players below level 30.
The WHERE part is the same as above, so we'll use what we've already learned.
And to get the 100 oldest players, we need to ORDER BY `lastlogin` and LIMIT 100
Since ORDER BY as I said, default orders ascending, we don't need to add anything more.
With LIMIT 100 we will delete the 100 first rows (and no more) matching the conditions set in WHERE.
Now maybe the "hardest" part. Inserting new rows.
It is pretty straight forward here too, but a bit more to remember.
Lets say we want to insert a new group.
We start of with INSERT INTO ´table-name´ ...
then comes which fields, or the order of the fields like this ... (id, name, flags, access, maxdepotitems, maxviplist) ...
If you are going to insert something into all fields, you can just skip it and go straight to the next part.
... VALUES (4, 'more players', 0, 0, 0, 0), (5, 'even more players', 0, 0, 0, 0), (6 'and even more players', 0, 0, 0, 0);
And put it all together:
We use commas , to separate the different new rows and (always!) semicolon ; to end the query.
When writing this into the console, you can press new line to make it easier to read.
And as I said, in the example over, we could just have skipped the underlined part because we are entering a value for all the fields and they are in the right order.
If there is any questions please post them here.
And since this is just my second tutorial, I would like if you could give me some feedback on the structure and the language. Thank you.
Please give some REP (the button!) if you found it useful!
I've seen a lot of people not understanding how SQL actually works so I thought I could give you a little taste of how to run your own queries.
I will only show you how to insert new rows, delete rows, and to get/view rows.
Assuming we are going to use the command promt (CMD) to do this we have to connect and select database first. Notice that I use colored text to refer to the specific parts in the example.
Remember that this is general SQL syntax. Meaning it can be used with many other programs and databases. (You can use this with phpMyAdmin, Mysql GUI tools (query browser), LuaSQL and so on.)
Figure out where mysql.exe is located and go to that directory. It may be that you don't need to do it if you installed it yourself and selected "add to windows PATH" (like me).
Open CMD and write:
Code:
C:\>[B]mysql -u root -p[/B]
Enter password: [B]*******[/B]
mysql>[B]SHOW DATABASES[/B];
+--------------------+
| Database |
+--------------------+
| information_schema |
| forgotten |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql>[B]USE forgotten[/B]
Database changed
mysql>[B]SHOW TABLES;[/B]
+---------------------+
| Tables_in_forgotten |
+---------------------+
| accounts |
| bans |
| groups |
| guild_invites |
| guild_ranks |
| guilds |
| house_lists |
| houses |
| player_deaths |
| player_depotitems |
| player_items |
| player_skills |
| player_spells |
| player_storage |
| player_viplist |
| players |
| players_online |
| tile_items |
| tiles |
+---------------------+
19 rows in set (0.00 sec)We can start off with how to view/get rows:
It is quite easy, logical and simple to understand:
You start of with SELECT, notice, it's easier to read the query if you write all the commands with CAPITAL letters and everything else with normal letters, but not necessary.
After SELECT you write which fields you want to get. If the table got many fields, and your not interested in all of them you simply write the name of the fields you want. If you want to get all fields, just write *
Next you write which table to get it FROM. In the example I'm using the players table.
This is really all you need, but it is with the command WHERE you can do a lot of things.
Lets say we want to get everyone above level 30, name starting with B, group_id like 1 and ordered by last login? Notice: if we are searching for text we need to enclose it in ' and we need to use LIKE to compare. % is wildcard.
To separate the different conditions we use AND or OR depending on how we want it.
Then we use ORDER BY to order it by the field we want. And since we want the one who logged on last to be first we use DESC after the field name, which is short for descending. (default is ascending).
At last we can use LIMIT to restraint the number of results we want.
Also, around every table name and field name it is smart to use ` so that like the field "time" is understood correctly by mysql.
Example:
Code:
SELECT [COLOR=Sienna]`id`, `name`, `level`[/COLOR] [COLOR=DarkOliveGreen]FROM `players`[/COLOR] [COLOR=Navy][COLOR=Purple]WHERE `level` > 30 AND `name` LIKE 'B%' AND `group_id` = 1[/COLOR] [COLOR=DarkOrange]ORDER BY `lastlogin`[/COLOR][/COLOR][COLOR=DarkOrange] DESC[/COLOR] [COLOR=Red]LIMIT 10;[/COLOR]
Code:
mysql>[B]SELECT * FROM `groups`;[/B]
+----+--------------+--------------+--------+---------------+------------+
| id | name | flags | access | maxdepotitems | maxviplist |
+----+--------------+--------------+--------+---------------+------------+
| 1 | player | 0 | 0 | 0 | 0 |
| 2 | a gamemaster | 137438953471 | 1 | 0 | 0 |
| 3 | a god | 267898560504 | 1 | 0 | 0 |
+----+--------------+--------------+--------+---------------+------------+
3 rows in set (0.02 sec)Its quite similar to SELECT, but doesn't print any results, only how many rows that was deleted.
We can use almost the exact same query, just changing some words.
Here, LIMIT can be your best friend, as it is NOT POSSIBLE to undelete anything!
Lets say we want to delete the 150 oldest players below level 30.
The WHERE part is the same as above, so we'll use what we've already learned.
And to get the 100 oldest players, we need to ORDER BY `lastlogin` and LIMIT 100
Since ORDER BY as I said, default orders ascending, we don't need to add anything more.
With LIMIT 100 we will delete the 100 first rows (and no more) matching the conditions set in WHERE.
Code:
DELETE FROM `players` WHERE `level` < 30 AND `group_id` = 1 ORDER BY `lastlogin` LIMIT 100;It is pretty straight forward here too, but a bit more to remember.
Lets say we want to insert a new group.
We start of with INSERT INTO ´table-name´ ...
then comes which fields, or the order of the fields like this ... (id, name, flags, access, maxdepotitems, maxviplist) ...
If you are going to insert something into all fields, you can just skip it and go straight to the next part.
... VALUES (4, 'more players', 0, 0, 0, 0), (5, 'even more players', 0, 0, 0, 0), (6 'and even more players', 0, 0, 0, 0);
And put it all together:
Code:
mysql>[B]INSERT INTO ´groups´ [U](id, name, flags, access, maxdepotitems, maxviplist)[/U] VALUES[/B]
>[B](4, 'more players', 0, 0, 0, 0), [/B]
>[B](5, 'even more players', 0, 0, 0, 0), [/B]
>[B](6 'and even more players', 0, 0, 0, 0);[/B]When writing this into the console, you can press new line to make it easier to read.
And as I said, in the example over, we could just have skipped the underlined part because we are entering a value for all the fields and they are in the right order.
If there is any questions please post them here.
And since this is just my second tutorial, I would like if you could give me some feedback on the structure and the language. Thank you.
Please give some REP (the button!) if you found it useful!
Last edited:
