MySQL tutorial - Managing database

Creating Database

To create a database in MySQL, you use the CREATE DATABASE statement as follows:

CREATE DATABASE [IF NOT EXISTS] database_name;

CREATE DATABASE statement will create the database with the given name you specified. IF NOT EXISTS is an option part of the statement, this part prevents you from error if there is a database with the given name exists on the database server. In our tutorial, for example, to create classicmodels database, you just only apply CREATE DATABASE statement above as follows:

CREATE DATABASE classicmodels;

After executing the statement, the MySQL will returns you a message to indicate whether the execution are successful or not.

Showing Databases

SHOW DATABASE statement will show all databases in your database server. You can use SHOW DATABASE statement  to check the database you've created or to see all the databases' name on the database server before you create a new database.

SHOW DATABASES;

On my database server, the output is :

+--------------------+
| Database           |
+--------------------+
| information_schema |
| classicmodels      |
| mysql              |
+--------------------+
8 rows in set (0.00 sec)

Selecting Database

To select a database which you plan to work with, you use USE statement

USE database_name;

You can select our sample database by using the USE statement as follows:

USE classicmodels;

From now you can query the tables' data and do whatever you want inside the selected database.

Removing Database

Removing database means you delete the database. All the data and related objects inside the database are permanently deleted and cannot be undone. So it is very important to execute this query with cares. To remove the database you can use DROP DATABASE statement as follows :

DROP DATABASE [IF EXISTS] database_name;

Like CREATE DATABASE statement, IF EXIST part is an optional part to prevents you from removing database which is not existed. In order to practice with DROP DATABASEstatement, you can create a temporary database, show the database on the database server, and drop it step by step as follows :

CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;