MySQL Database: Create, List, Select and Drop

A database is a named container for tables, views and other database objects. In MySQL you can create a database, select it for use, inspect its definition, list visible databases, and drop it when it is no longer needed.

CREATE DATABASE IF NOT EXISTS my_database
CHARACTER SET utf8mb4;

Create a Database

CREATE DATABASE my_database;

To avoid an error if it already exists:

CREATE DATABASE IF NOT EXISTS my_database;

For new applications, utf8mb4 is the preferred Unicode character set:

CREATE DATABASE IF NOT EXISTS my_database
CHARACTER SET utf8mb4;

Check the Current Database

SELECT DATABASE();

If the session has no default database, DATABASE() returns NULL.

List Databases

SHOW DATABASES;

The list depends on the privileges of the connected account. Filter visible names with LIKE:

SHOW DATABASES LIKE '%plus2%';

For more listing options, see SHOW DATABASES and SHOW TABLES.

Select a Database

USE my_database;

Applications often choose the database directly in the connection configuration, so a separate USE statement may not be required in application code.

Inspect the Database Definition

SHOW CREATE DATABASE my_database;

This displays the statement MySQL would use to recreate the database, including its default character set and collation.

Delete a Database

DROP DATABASE my_database;

To avoid an error when the database does not exist:

DROP DATABASE IF EXISTS my_database;

Database and Table Name Case

Do not build applications that depend on database/table identifiers being case-insensitive. Identifier case behavior can differ by operating system and MySQL configuration. Use consistent naming everywhere.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: