How to Create & Delete a MySQL Database?

In this tutorial, you will learn how to create and delete a MySQL Database in various ways.

SQL code for creating a database.
Create a database through phpMyAdmin.
Create database using MySQL workbench.
Create database using CLI.
Delete a database.

1. SQL statement or query to create a Database

The SQL statement or query for creating a database is – CREATE DATABASE then database name and then semi-colon (;) to close the statement.

CREATE DATABASE db_name;

If you want to check for the existence of a database before creating the database, then add IF NOT EXISTS before the database name.

CREATE DATABASE IF NOT EXISTS db_name;

Show the list of all Databases

The following SQL code will show the list of all databases.

SHOW DATABASES;

Switch Databases

If you want to run SQL queries for a specific database, such as you want to create a table inside a database, therefore you have to select that database first, then you can run SQL code for the database.

To select or switch to a database, use the USE keyword.

USE db_name;

2. Create a MySQL database using phpMyAdmin

Click on the New in the left sidebar of phpMyAdmin.

phpMyAdmin New link for creating new database.

After that, enter the database name, then click on the Create button.

PhpMyAdmin Enter the database name then click create button.

3. Create database using MySQL workbench

Click on the create new schema icon to create a new database using MySQL workbench.

OR – Right click on the Schemas Navigation bar, then click on the Create Schemas.

Enter the DB name then click on the apply button.

After that, Apply the review SQL script.

Your database has been created successfully, now click on the finish button.

database is successfully created in workbench

4. Create MySQL database using Command Line Interface (CLI)

First login to MySQL as root

sudo mysql -u root -p

Create a new database using the SQL statement

CREATE DATABASE my_test_db;

How to add a new user to an existing database using CLI?

Now we will see how you can add a new user for the my_test_db database;

DB name – my_test_db. already been created.
DB user – john45
DB Password – mypass1234

In the following SQL code, change the DB information according to yours.

grant all on my_test_db.* to john45@localhost identified by 'mypass1234';

After that, run the following SQL statement to enable the changes without reloading or restarting MySQL service.

flush privileges;

Delete a MySQL database

SQL statement for deleting a database

DROP DATABASE db_name;

DROP DATABASE IF EXISTS db_name;

Delete a database using phpMyAdmin

Select the DB that you want to delete.

Go to the Operations tab.

Click on the Drop the database.

Delete database using Workbench

Right click on the database which you want to delete, then click drop schema.