MYSQL + Command Line

<In the last post I spoke about my decision to start using MySql , now I will speak about how to set up a simple test database using the command line prompt>

CONNECTING TO THE SERVER

MySql has a basic tool to set up (almost) everything you need from your SQL server.

After installing MySql you just need to use the command:

mysql -h ServerIp -u username -p

That will connect to the mysql server with ServerIp, and try to validate, if you don't write the IP it will default to localhost, the username is the username used to login into the server, and finally -p makes mysql to ask for the password of the username:

jbea@eeepc1005HA:~$ mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Here we are, logged in..

CREATING DATABASES AND TABLES

Next step would be very simple, create our database:
 

mysql> CREATE DATABASE rpgdata;
Query OK, 1 row affected (0.00 sec)

Notice the semicolon ";", every instruction in the mysql command prompt must finish with ";", it's the end of sentence command

It will create a new database "rpgdata" , then we can check the databases in our server:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| rpgdata            |
+--------------------+
3 rows in set (0.00 sec)

We can delete the databases with the command:

mysql>DROP DATABASE rpgdata; 

After creating a database and before we can actually work with it we need to "use" it with the command:

mysql>USE rpgdata; 

Next step would be creating the tables to store the data:

mysql>CREATE TABLE table (datafield1, datafield2..); 

Example:

mysql> CREATE TABLE accounts (
    -> id INT AUTO_INCREMENT,
    -> fullname VARCHAR(60),
    -> email VARCHAR(120),
    -> password VARCHAR(30),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.06 sec)

Now we can check the tables we have in our database:

mysql> SHOW TABLES;
+-------------------+
| Tables_in_rpgdata |
+-------------------+
| accounts          |
+-------------------+
1 row in set (0.00 sec)

And another interesting thing: we can check the database structure with the command: 

mysql> DESCRIBE accounts;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| fullname | varchar(60)  | YES  |     | NULL    |                |
| email    | varchar(120) | YES  |     | NULL    |                |
| password | varchar(30)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

DATA HANDLING

At this point, we can start using standard SQL sentences to handle the data:

>INSERT INTO > Insert data

>UPDATE > update data

>DELETE FROM > delete data

>SELECT FROM > Get data 

A pair of examples:

mysql> INSERT INTO accounts VALUES
    -> (NULL, 'Jorge Bea Frau','jbeafrau@gmail.com','password');
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> SELECT * FROM accounts;
+----+----------------+--------------------+----------+
| id | fullname       | email              | password |
+----+----------------+--------------------+----------+
|  1 | Jorge Bea Frau | jbeafrau@gmail.com | password |
+----+----------------+--------------------+----------+
1 row in set (0.00 sec)

mysql>

SQL Language is almost a programming language itself so it's way beyond my hand to teach the language, anyway I will explain the sentences when I use them so you don't have to worry about it.

BACKUPS AND RESTORES

Finally, to make a backup from a database to a plain text file and restore it later we have two commands:

mysqldump -h localhost -u root -p DATABASE > backupfile.sql

To make a backup from a database to a file...

mysql -h localhost -u root -p DATABASE < backupfile.sql

To restore a database from a backup file

<Notice that in both commands you have to execute the commands from the terminal window, not from the mysql prompt>

And that's all for now, that's all we are going to need to start using the mysql server, creating our database and the tables inside, we can now design our game database, and we could even insert the desired data inside (I Wont recommend that!), but instead  we are going to use c++ to connect to the mysql server and launch SQL sentences...

In the next post: Using mysql from c++ + code::blocks.