» Basic MySQL Tutorial » Managing Database

Managing Databases in MySQL

Summary: In this tutorial you will learn how to manage database in MySQL. You will learn how to create a new database, list all databases in the MySQL database server and remove databases from database catalog.

Let's start creating a new database 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 optional 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;

In 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 operate data 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 provides a 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 prevent you from removing database which is not existed. In order to practice with DROP DATABASE statement, it is recommended to create a temporary database, show the database on the database server, and drop it. The sequence of SQL queries is as follows:

CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;
Related Tutorials
Search