Managing Databases in MySQL

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. The IF NOT EXISTS part prevents you from error if there is a database with the given name exists in the database catalog.

For example, to create classicmodels database, you just need to execute the CREATE DATABASE statement above as follows:

CREATE DATABASE classicmodels;

After executing the statement, the MySQL will returns you a message to indicate whether the data created sucessfully or not.

Showing Databases Statement

SHOW DATABASE statement shows 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, for example:

SHOW DATABASES;

On my database server, the output is :

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

Selecting database to work with

To select a database which you plan to work with, you can use USE statement as follows:

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 physically. 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. MySQL provide standard SQL statement DROP DATABASE to allow you to delete a database:

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 DATABASE statement, you can create a temporary database, show the database on the database server, and drop it. The step to execute the query is as follows:

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

Search