MySQL: How to Create and Delete Databases

Creating a MySQL Database

The CREATE DATABASE statement is used to create a new database. Its syntax is simple but powerful:

CREATE DATABASE database_name;

Example: Step-by-Step

Suppose we want to create a database to store information about a bookstore. Here’s how it works:

CREATE DATABASE BookstoreDB;

Explanation of the code:

1. CREATE DATABASE – This keyword tells MySQL that we want to create a new database.
2. BookstoreDB – This is the name of the database. You can choose any name, but it should be unique and follow MySQL naming conventions (no spaces, start with a letter, avoid special characters).

Once executed, MySQL creates a new database in the system catalog, making it ready to store tables and data.

Checking Your Databases

After creating a database, you may want to confirm it exists. Use the following command:

SHOW DATABASES;

This command lists all databases currently available in your MySQL server. You should see BookstoreDB in the list.

Using the Database

Before creating tables or inserting data, you need to select the database:

USE BookstoreDB;

This command tells MySQL to set BookstoreDB as the active database for all subsequent operations.

Deleting a MySQL Database

If a database is no longer needed, you can remove it using the DROP DATABASE statement:

DROP DATABASE database_name;

Example: Step-by-Step

Continuing with our example, let’s delete the BookstoreDB database:

DROP DATABASE BookstoreDB;

Explanation of the code:

1. DROP DATABASE – This keyword instructs MySQL to delete an entire database.
2. BookstoreDB – The name of the database to remove.

⚠️ Important: Dropping a database is irreversible. All tables and data within it will be permanently deleted. Always double-check the database name before executing this command.

Checking Deletion

To confirm the deletion, use the same command as before:

SHOW DATABASES;

You should no longer see BookstoreDB in the list.

Best Practices for Creating and Deleting Databases

1. Use descriptive names: Database names should reflect their purpose, making it easier to manage multiple databases.
2. Back up important data: Always back up a database before deleting it to prevent accidental data loss.
3. Use lowercase letters: While MySQL is case-insensitive on some systems, using lowercase ensures compatibility across platforms.
4. Test in a development environment: Avoid creating or deleting databases directly on a production server without proper testing.