Chapter 3: Creating and Managing Databases
Creating and Managing Databases
We’re going to do everything step-by-step, with real examples, copy-paste commands, and what you will see on your screen. Just open your MySQL command line or Workbench and follow along. If you get stuck, just tell me!
1. CREATE DATABASE statement
(This is how you create your first database!)
Syntax (simple version):
|
0 1 2 3 4 5 6 |
CREATE DATABASE database_name; |
Real-life example – Let’s create our coaching class database
Type this in your MySQL prompt (mysql>):
|
0 1 2 3 4 5 6 |
CREATE DATABASE coaching_class; |
What happens:
- MySQL creates a completely empty database called coaching_class.
- You will see: Query OK, 1 row affected (or sometimes just Query OK).
Important variations (very useful):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- Create database only if it doesn't already exist (prevents error) CREATE DATABASE IF NOT EXISTS coaching_class; -- Create with specific character set and collation (very important for Indian languages!) CREATE DATABASE coaching_class CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
utf8mb4 is the modern standard character set — it supports:
- English
- Hindi (हिंदी)
- Marathi (मराठी)
- Emojis 😊👍
- Almost every language in the world!
So from now on, I recommend always creating databases like this:
|
0 1 2 3 4 5 6 7 8 |
CREATE DATABASE coaching_class CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
2. USE database
(This is how you tell MySQL: “Hey, I want to work inside this database now!”)
Syntax:
|
0 1 2 3 4 5 6 |
USE database_name; |
After creating the database, do:
|
0 1 2 3 4 5 6 |
USE coaching_class; |
You will see: Database changed
Now everything you do (creating tables, inserting data, etc.) will happen inside coaching_class.
Tip: If you forget to USE a database, MySQL will give errors like “No database selected”. Always remember to USE it first!
3. SHOW DATABASES
(This shows you all the databases present on your MySQL server)
Syntax:
|
0 1 2 3 4 5 6 |
SHOW DATABASES; |
You will see something like this (depending on your installation):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
+--------------------+ | Database | +--------------------+ | coaching_class | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
- information_schema, mysql, performance_schema, sys → These are system databases created by MySQL itself. Never touch them!
- coaching_class → This is the one you just created 🎉
4. DROP DATABASE
(This deletes a database permanently — be careful!)
Syntax:
|
0 1 2 3 4 5 6 |
DROP DATABASE database_name; |
Safe version (recommended):
|
0 1 2 3 4 5 6 |
DROP DATABASE IF EXISTS coaching_class; |
Example:
|
0 1 2 3 4 5 6 7 8 9 10 11 |
-- First let's create and use it again CREATE DATABASE test_db; USE test_db; -- Now delete it DROP DATABASE test_db; |
After this, if you do SHOW DATABASES;, test_db will be gone forever. Warning: DROP DATABASE deletes everything inside it (tables, data, views, procedures — everything!). There is no undo!
So always double-check the name before running DROP!
5. Database Character Sets and Collations
(This is super important — especially if you want to store Hindi, Marathi, special characters, or emojis)
What is Character Set? It’s the alphabet MySQL uses to store text. Examples:
- latin1 → Only English + some Western European characters
- utf8 → Old 3-byte version (not good for emojis)
- utf8mb4 → Modern 4-byte version → supports everything (recommended!)
What is Collation? It’s the rulebook for how text is compared and sorted. Examples:
- utf8mb4_general_ci → Case-insensitive, general sorting
- utf8mb4_unicode_ci → Better sorting for all languages (including Hindi, Marathi), case-insensitive
- utf8mb4_bin → Case-sensitive, binary comparison (rarely used)
Best practice in 2026 (for Indian developers): Always use:
|
0 1 2 3 4 5 6 7 |
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci |
Why?
- unicode_ci handles Hindi sorting correctly (क before ख)
- Supports emojis 😄
- No weird ???? characters when you insert Marathi or Hindi text
How to check current character set and collation of a database?
|
0 1 2 3 4 5 6 |
SHOW CREATE DATABASE coaching_class; |
You will see something like:
|
0 1 2 3 4 5 6 |
CREATE DATABASE `coaching_class` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
How to change character set/collation later?
|
0 1 2 3 4 5 6 7 8 |
ALTER DATABASE coaching_class CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
Quick test – Let’s see Hindi working!
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE coaching_class; CREATE TABLE students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; INSERT INTO students (name) VALUES ('प्रिया शर्मा'); INSERT INTO students (name) VALUES ('राहुल पाटील 😊'); SELECT * FROM students; |
You should see Hindi names and emoji perfectly!
That’s it for Chapter 3! 🎉
Summary – What we learned today:
- CREATE DATABASE → Make a new database
- USE → Switch to a database
- SHOW DATABASES → See all databases
- DROP DATABASE → Delete (carefully!)
- Always use utf8mb4 + utf8mb4_unicode_ci for modern apps
Homework for today (do it right now – very important!)
- Open MySQL
- Run these commands one by one and copy-paste the output here:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SHOW DATABASES; CREATE DATABASE my_coaching CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE my_coaching; SHOW DATABASES; |
- (Optional fun) Try inserting a Hindi name like above and show me the result!
