MySQL DDL Database Commands — CREATE, DROP, USE & More (2026)

Introduction#

Why This Matters#

Before you can store a single row of data in MySQL, you need a database to house your tables. Database-level DDL (Data Definition Language) commands are the first commands every MySQL user must learn. They are issued once during setup and again whenever the architecture of your system changes — when you add a new service, retire an old one, or reorganise schemas.

Mastering these commands also prevents catastrophic mistakes: running DROP DATABASE on the wrong schema can wipe months of data in milliseconds. Understanding what each command does, when to use it, and how to verify your state is essential for any developer or DBA.

What Is DDL?#

Data Definition Language is the subset of SQL that defines and manages the structure of database objects. DDL commands affect the schema (the blueprint), not the data itself. The four categories of SQL are:

CREATE DATABASE — Creating a New Database#

CREATE DATABASE creates a new, empty database on the MySQL server. The name must be unique on that server.

CREATE DATABASE school;

You can also add IF NOT EXISTS to avoid an error if the database already exists:

CREATE DATABASE IF NOT EXISTS school;

To set the character set and collation explicitly (recommended for multilingual applications):

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

utf8mb4 supports the full Unicode range including emoji, while the older utf8 alias in MySQL only supports 3-byte characters.

DROP DATABASE — Deleting a Database#

DROP DATABASE permanently deletes the database and every table, view, procedure, and row inside it. There is no recycle bin.

DROP DATABASE school;

Add IF EXISTS to suppress the error when the database does not exist:

DROP DATABASE IF EXISTS school;

Always take a backup with mysqldump before running this command in production:

mysqldump -u root -p school > school_backup.sql

USE — Switching the Active Database#

USE sets the default database for the current session. All subsequent table references that are not fully qualified (e.g., school.students) resolve against this database.

USE library;

After this command, you can write SELECT * FROM books instead of SELECT * FROM library.books.

SHOW DATABASES — Listing All Databases#

SHOW DATABASES lists every database the current user has at least some privilege on.

SHOW DATABASES;

Sample output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| library            |
| mysql              |
| school             |
+--------------------+

information_schema and mysql are system databases — never drop them.

SELECT DATABASE() — Confirming the Active Database#

SELECT DATABASE() returns the name of the currently active database, or NULL if none is selected.

SELECT DATABASE();
-- Output: library

This is useful in scripts to verify context before executing destructive operations.

Putting It All Together — Typical Workflow#

-- 1. Create a new database for a school management system
CREATE DATABASE IF NOT EXISTS school_mgmt
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
 
-- 2. Switch to it
USE school_mgmt;
 
-- 3. Verify
SELECT DATABASE();
-- Output: school_mgmt
 
-- 4. Later, list all databases to confirm it exists
SHOW DATABASES;

Common Mistakes#

• Running DROP DATABASE without a backup. Always export first with mysqldump.
• Forgetting USE before creating tables. Tables land in the wrong database or MySQL returns an error.
• Using reserved words as database names. Wrap them in backticks: CREATE DATABASE `select`; (avoid this in practice).
• Mixing utf8 and utf8mb4. Use utf8mb4 for all new databases to avoid silent data truncation on emoji or special characters.
• Not checking SELECT DATABASE() in scripts. A misplaced USE statement or missing one can cause DDL to run on the wrong schema.

Best Practices#

• Name databases in lowercase with underscores (school_mgmt, not SchoolMgmt) for cross-platform consistency.
• Always specify CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci when creating databases.
• Use IF NOT EXISTS and IF EXISTS modifiers in automation scripts to make them idempotent.
• Restrict DROP DATABASE privilege in production — grant it only to DBAs, not application users.
• Document every database with a README or wiki entry describing its purpose, owner, and creation date.
• Use separate databases per environment: app_dev, app_staging, app_prod.

Key Takeaways#

• CREATE DATABASE initialises an empty schema; without USE, subsequent DDL targets no database.
• DROP DATABASE is irreversible — it destroys all tables, views, routines, and data inside the schema instantly.
• USE databasename sets the session-level default database, scoping all unqualified table references.
• SHOW DATABASES lists all databases visible to the current user, including system databases like information_schema.
• SELECT DATABASE() returns the currently active database name and is the fastest way to confirm context in scripts.
• Specifying CHARACTER SET utf8mb4 at creation time prevents encoding issues with multilingual data and emoji.
• IF NOT EXISTS and IF EXISTS make scripts idempotent and safe to run in CI/CD pipelines without extra error handling.
• DDL commands in MySQL trigger an implicit COMMIT, so any open transaction is automatically committed when DDL runs.