====Creating and managing databases in MySQL====

First install the MySQL server.\\

<code>
$ sudo apt-get install mysql-server
</code>

Automatically it will request a root password. Now we can login mysqlserver, write in console:

<code>
$ mysql -uroot -p
</code>

And write your password. Now you can create and user, commands ends with semicolon:

<code>
> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
</code>

This new user does not have any privileges, but we haven't create a database to give him privileges so lets create a database:

<code>
> CREATE DATABASE [database_name];
</code>

We can see which databases are available:

<code>
> SHOW DATABASES;
<code>

Now select the database, and give the 'user' privileges:

<code>
> USE [database_name];
> GRANT ALL PRIVILEGES ON 'database' TO 'user'@'localhost' WITH GRANT OPTION ; 
</code>

Creating a basic table:
<code>
> CREATE TABLE [table_name]
> ( column_name_1  type  [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] , 
>   column_name_2  type  [NOT NULL | NULL] ... ,
    ...
>   column_name_n  type  [NOT NULL | NULL] ... ) ;
</code>

The types are describe [[http://dev.mysql.com/doc/refman/5.0/en/data-types.html|here]]. And you can describe a table with:

<code>
> DESCRIBE  [table_name] ; 
</code>

MySql has a function called ALTER TABLE, it changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. How to use it [[http://dev.mysql.com/doc/refman/5.1/en/alter-table.html|here]]

Also you can use an existing table, save the table in .txt or .cvs format, and write:

<code>
> LOAD DATA INFILE [file_path] INTO TABLE  [table_name];
</code>

Showing the table content:

<code>
> SELECT * FROM  [table_name] ;
</code>

Delete a database:\\

<code>
> DROP DATABASE [database_name] ;
</code>

Delete any table:\\
<code>
> DROP TABLE  [table_name] ;
</code>

Delete the records in a table:\\

<code>
>TRUNCATE TABLE  [table_name] ;
</code>

Save a database in a file for this, in a console, in the directory you want to save the database:
<code>
$ mysqldump -u[user] -p  [database_name]  > [file_name].sql
</code>

Read a database from a file
<code>
> SOURCE [file_path]/[file_name].sql
</code>