Manage MySQL Databases

In this article, you will learn how to manage or we can say list, create, select and drop databases in MySQL.

Read the entire article to better understand how you can manage MySQL databases. Each statement is explained with examples.

List Databases

To display the list of existing databases, we use following statement:

SHOW DATABASES;

Apart from list of databases, it also return the count

Sometimes, you may need to list out all databases and this statement would be really helpful.

Example

Create Database

To create a MySQL database, we use CREATE DATABASE statement.

Syntax

CREATE DATABASE [IF NOT EXISTS] db_name
[CHARACTER SET charset_name]
[COLLATE collation_name]

Let’s understand the above syntax:

The CREATE DATABASE statement must be followed by name of the database. Database name must be unique, but if a database with same name already exists then it will result an error.

If you specify IF NOT EXISTS option then it will not result an error even if the database already exists.

Besides, you can also specify character set and collation for the database with CHARACTER SET and COLLATE options.

NOTE: Square brackets indicate, it is optional.

Example

Let’s display the list of existing database.

Again, if you try to execute the same CREATE DATABASE statement which we executed previously, but without IF NOT EXISTS option then you will get this error:

Select Database

To select a MySQL database, we use USE statement.

Syntax

USE db_name;

Example

As soon as you execute the USE statement, none is replaced with the selected database name.

To verify, execute following statement:

SELECT database();

Drop Database

DROP DATABASE statement deletes all the tables and database permanently.

Syntax

DROP DATABASE [IF EXISTS] db_name;

It returns the number of tables it deleted.

If the specified database does not exist then it will result an error and to prevent this error use IF EXISTS option.

Example

Summary

1. If you want to display the list of databases, there is SHOW DATABASES statement.
2. CREATE DATABASE statement, as the name states, creates a database.
3. To select the database, there is USE statement.
4. If you want to delete a database, use DROP DATABASE statement.

You can get MySQL from here. For more MySQL related posts, click here.