Chapter 3: Database Creation and Management
Think of a database as a big folder (or a filing cabinet) that contains many tables (like spreadsheets inside that folder). One server/computer can hold hundreds or thousands of these databases — each one completely separate from the others.
We’ll cover four main commands today:
- CREATE DATABASE → Make a new database
- DROP DATABASE → Delete a database (careful!)
- USE / SELECT DATABASE → Switch to / choose which database you want to work in
- SHOW DATABASES → See the list of all databases on your server
I’ll explain each one in detail, with real examples, warnings, and best practices — just like we’re sitting together in a Mumbai café and I’m showing you on my laptop.
1. CREATE DATABASE – Making a New Database
This is how you create a brand-new, empty database.
Basic Syntax:
|
0 1 2 3 4 5 6 |
CREATE DATABASE database_name; |
Real-world example:
|
0 1 2 3 4 5 6 7 |
-- Let's create a database for an online bookstore CREATE DATABASE bookshop; |
Important options you’ll often use:
|
0 1 2 3 4 5 6 7 8 9 |
-- Most common & recommended way (with character set and collation) CREATE DATABASE bookshop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
- CHARACTER SET utf8mb4 → Supports emojis, all Indian languages (Hindi, Marathi, Tamil, etc.), and special characters
- COLLATE utf8mb4_unicode_ci → Case-insensitive sorting, perfect for most apps
Even better (full modern example):
|
0 1 2 3 4 5 6 7 8 |
CREATE DATABASE IF NOT EXISTS bookshop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
- IF NOT EXISTS → Super safe! If the database already exists, it won’t throw an error — just skips creation.
Pro Tip: Always use utf8mb4 in 2026 — it’s the modern standard that handles everything correctly.
2. SHOW DATABASES – Listing All Databases
Once you have some databases, you’ll want to see what’s there.
Syntax:
|
0 1 2 3 4 5 6 |
SHOW DATABASES; |
Example output (what you might see):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | bookshop | | ecommerce | | hr_system | +--------------------+ |
- information_schema, mysql, performance_schema, sys → These are system databases that come with every MySQL/PostgreSQL server. Never touch or delete them!
Tip: You can filter the list:
|
0 1 2 3 4 5 6 |
SHOW DATABASES LIKE 'book%'; -- Shows only databases starting with 'book' |
3. USE / SELECT DATABASE – Switching to a Database
After creating a database, you need to tell the system “Hey, I want to work inside THIS database now.”
Syntax (most common):
|
0 1 2 3 4 5 6 |
USE database_name; |
Alternative (works in some tools):
|
0 1 2 3 4 5 6 7 |
SELECT DATABASE(); -- Just to check which one you're currently in USE bookshop; |
Example session:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SHOW DATABASES; -- See the list USE bookshop; -- Now everything I do happens inside bookshop -- Now I can create tables here CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200), author VARCHAR(100), price DECIMAL(8,2) ); SELECT DATABASE(); -- Confirms: bookshop |
Important note:
- In MySQL, MariaDB, SQL Server → You use USE database_name;
- In PostgreSQL → You use \c database_name in psql, or SET search_path TO database_name; — but most tools let you do USE too.
- In SQLite → There is only one database file, so no USE needed.
4. DROP DATABASE – Deleting a Database (Be Very Careful!)
This command permanently deletes the entire database and everything inside it — tables, data, indexes, everything. No recycle bin!
Syntax:
|
0 1 2 3 4 5 6 |
DROP DATABASE database_name; |
Safe version:
|
0 1 2 3 4 5 6 |
DROP DATABASE IF EXISTS bookshop; |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- First check what exists SHOW DATABASES; -- Oops, we don't need this test database anymore DROP DATABASE test_playground; -- Now it's gone forever! |
Big Warning (listen carefully!):
- There is no UNDO for DROP DATABASE.
- In production, always double-check the name.
- Many teams disable DROP DATABASE permission for regular users in production servers — only DBAs can do it.
- Before dropping, people often do:
SQL01234567SHOW TABLES FROM old_database; -- Check what's inside-- or even better: take a backup first!
Real-life story: A junior developer once typed DROP DATABASE production; instead of DROP DATABASE test_production; in a live server… 3 hours of panic and restore from backup. Lesson: Always triple-check!
Full Example Workflow (copy-paste this!)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Step 1: Create a new database safely CREATE DATABASE IF NOT EXISTS online_store CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Step 2: See all databases SHOW DATABASES; -- Step 3: Switch to our new database USE online_store; -- Step 4: (Later we'll create tables here, but for now just confirm) SELECT DATABASE(); -- Should say: online_store -- Step 5: If we want to delete it later (only for testing!) -- DROP DATABASE IF EXISTS online_store; |
Best Practices Summary (2026 Style)
| Command | Best Practice Recommendation |
|---|---|
| CREATE DATABASE | Always use IF NOT EXISTS + utf8mb4_unicode_ci |
| SHOW DATABASES | Use LIKE ‘prefix%’ to filter when you have many DBs |
| USE | Run it at the start of every new session/script |
| DROP DATABASE | Never run it without IF EXISTS and confirmation |
| Naming | Use lowercase + underscores: ecommerce_2026, hr_system |
| Security | Don’t give DROP privilege to app users — only to admins |
