Creating and Dropping Databases in MySQL

Every MySQL project starts with a database. In this chapter you’ll learn how to create databases with the right character set and collation, how to inspect existing databases on your server, and how to safely drop databases when they are no longer needed — along with the critical safeguards MySQL provides to prevent accidental data loss.

What is a Database in MySQL?

In MySQL, a database (also called a schema — both terms are interchangeable in MySQL) is a named container that holds a collection of related tables, views, stored procedures, functions, triggers, and events. Think of it as a folder that organises everything belonging to one project or application.

A Single MySQL Server Can Host Many Databases

shop_db

Tables: customers, orders, products, payments. One database per application is the standard practice.

blog_db

Tables: posts, categories, tags, comments. Completely isolated from shop_db — different data, different users.

analytics_db

Tables: events, sessions, user_actions. Data warehouse for reporting — separate from operational databases.

MySQL vs Schema: In MySQL, CREATE DATABASE and CREATE SCHEMA are exact synonyms — they do the same thing. Other databases like PostgreSQL treat schemas differently (as a namespace inside a database). In MySQL, database = schema.

CREATE DATABASE — Full Syntax

The CREATE DATABASE statement creates a new database on the MySQL server. Here is the complete syntax with all optional clauses:

Syntax

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

✓ IF NOT EXISTS

— Prevents an error if the database already exists. Always recommended.

CHARACTER SET

— The character encoding to use. Default in MySQL 8.0+ is utf8mb4.

COLLATE

— Rules for comparing/sorting strings. Default is utf8mb4_0900_ai_ci.

SQL — CREATE DATABASE Examples

1-- Simplest form: create a database with default settings
2CREATE DATABASE shop_db;
3
4-- Safe form: won't throw an error if shop_db already exists
5CREATE DATABASE IF NOT EXISTS shop_db;
6
7-- Production-grade: explicitly set character set and collation
8CREATE DATABASE IF NOT EXISTS shop_db
9    CHARACTER SET  utf8mb4
10    COLLATE        utf8mb4_0900_ai_ci;
11
12-- CREATE SCHEMA is identical to CREATE DATABASE in MySQL
13CREATE SCHEMA IF NOT EXISTS blog_db
14    CHARACTER SET  utf8mb4
15    COLLATE        utf8mb4_unicode_ci;

Best practice — always use utf8mb4. MySQL has an older encoding called utf8 which only stores 3 bytes per character and cannot store emoji or certain East Asian characters. utf8mb4 is the true 4-byte UTF-8 encoding and handles every Unicode character, including emoji (😊, 🚀). Always use utf8mb4 for any new database.

Character Set vs. Collation — What’s the Difference?

These two settings are often confused. Here’s a clear distinction:

CHARACTER SET

Defines how characters are stored — which bytes represent which characters.

utf8mb4 → Full Unicode, supports all languages + emoji
latin1 → Western European characters only
utf8 → Old MySQL pseudo-UTF8, avoid for new databases

COLLATION

Defines how characters are compared and sorted — affects ORDER BY, WHERE, and indexes.

_ci → Case-Insensitive: ‘Apple’ = ‘apple’
_cs → Case-Sensitive: ‘Apple’ ≠ ‘apple’
_bin → Binary comparison by byte value

Recommended combination for MySQL 8.0+:
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
This is case-insensitive (_ci), accent-insensitive (_ai), and uses Unicode 9.0 sorting rules. It’s the MySQL 8.0+ default and the best choice for most applications.

Viewing Existing Databases

After creating databases, use SHOW DATABASES to see all databases on the MySQL server. You’ll notice MySQL ships with several built-in system databases.

SQL — SHOW DATABASES

1-- List all databases on this MySQL server
2SHOW DATABASES;
3
4-- View full creation details of a specific database
5SHOW CREATE DATABASE shop_db;
6
7-- Query the information_schema for metadata about databases
8SELECT schema_name, default_character_set_name, default_collation_name
9FROM   information_schema.schemata;

The output of SHOW DATABASES on a fresh MySQL 8.0 installation typically looks like this:

Database
information_schema
mysql
performance_schema
sys
shop_db

⚠️ Never delete or modify the system databases. mysql, information_schema, performance_schema, and sys are built-in MySQL system databases. Dropping or altering them can corrupt or completely break your MySQL server installation.

Modifying a Database — ALTER DATABASE

You can change the default character set or collation of an existing database using ALTER DATABASE. Note that this only affects new tables created after the change — existing tables keep their original encoding unless you convert them separately.

SQL — ALTER DATABASE

1-- Change the default charset and collation for future tables
2ALTER DATABASE shop_db
3    CHARACTER SET  utf8mb4
4    COLLATE        utf8mb4_unicode_ci;
5
6-- Verify the change took effect
7SHOW CREATE DATABASE shop_db;

DROP DATABASE — Permanent Deletion

DROP DATABASE permanently deletes a database and all of its contents — every table, view, procedure, trigger, and every row of data inside it. This operation is irreversible and is the most destructive command in MySQL.

Syntax

DROP DATABASE [IF EXISTS] database_name;

SQL — DROP DATABASE Examples

1-- Drop a database (throws error if it doesn't exist)
2DROP DATABASE shop_db;
3
4-- Safe form: silently does nothing if the database doesn't exist
5DROP DATABASE IF EXISTS shop_db;
6
7-- DROP SCHEMA is identical to DROP DATABASE in MySQL
8DROP SCHEMA IF EXISTS old_project_db;

🔴 DROP DATABASE destroys everything — instantly and permanently. There is no Recycle Bin, no undo, no confirmation prompt. MySQL executes it immediately.

Before running DROP DATABASE in any environment, always: (1) confirm you have a recent backup, (2) double-check the database name, and (3) ideally run it during a scheduled maintenance window.

Database Naming Rules and Best Practices

MySQL identifiers (database names, table names, column names) follow specific rules:

❌ Avoid

Names with spaces: my database
Names starting with a number: 123shop
Reserved keywords without backticks: order, select
Very long names (over 64 characters)
Mixed case: MySQL on Windows is case-insensitive by default, Linux is case-sensitive
Special characters other than _ and $

✓ Recommended

Use lowercase with underscores: shop_db
Add _db suffix for clarity: blog_db
Be descriptive but concise: inventory_db
Use backticks for reserved words: `order`
Stick to letters, numbers, and underscores
Consistent naming across your organisation

Complete Workflow: Real-World Example

Here is a complete, practical workflow — the exact sequence you would run when starting a new project from scratch:

SQL — Full Database Setup Workflow

1-- Step 1: Check what databases currently exist
2SHOW DATABASES;
3
4-- Step 2: Create a new database for an e-commerce project
5CREATE DATABASE IF NOT EXISTS ecommerce_db
6    CHARACTER SET  utf8mb4
7    COLLATE        utf8mb4_0900_ai_ci;
8
9-- Step 3: Verify the database was created
10SHOW DATABASES;
11
12-- Step 4: Inspect its full CREATE statement
13SHOW CREATE DATABASE ecommerce_db;
14
15-- Step 5 (later): Clean up a test database when done
16DROP DATABASE IF EXISTS test_db;

What MySQL returns: After a successful CREATE DATABASE, MySQL responds with Query OK, 1 row affected (0.01 sec). If you use IF NOT EXISTS and the database already exists, you’ll see Query OK, 1 row affected, 1 warning (0.00 sec) — a warning but no error.

Key Takeaways

CREATE DATABASE and CREATE SCHEMA are identical in MySQL — both create a named container for tables and other objects.
Always use IF NOT EXISTS with CREATE and IF EXISTS with DROP to write safe, re-runnable scripts.
Always specify CHARACTER SET utf8mb4 — it’s the true 4-byte UTF-8 encoding that supports all Unicode characters including emoji.
Use SHOW DATABASES to list all databases and SHOW CREATE DATABASE db_name to inspect settings.
DROP DATABASE permanently deletes the database and all its data — it cannot be undone without a backup.
Use lowercase names with underscores (e.g., shop_db) for portability across Windows and Linux MySQL servers.

What’s Next?

In Chapter 2.2 — Selecting a Database (USE Statement), we’ll learn how to tell MySQL which database to work with, explore context-aware naming with dot notation, and understand how the active database affects every query you run.

2.1 Creating and Dropping Databases