Database Creation Guide

Introduction

Learn how to create and manage MySQL databases using phpMyAdmin, MySQL command line, and SQL queries in WAMP/XAMPP environments.

What is a Database?

A database is a structured collection of data organized for efficient access, management, and updates. In MySQL, databases contain tables, which store the actual data.

Creating a Database with phpMyAdmin

Step-by-Step Process

1. Access phpMyAdmin:
   • Navigate to http://localhost/phpmyadmin
   • Login with your credentials (default: root, no password)
2. Create New Database:
   • Click on “Databases” tab
   • Enter database name in “Create database” field
   • Select collation (recommended: utf8mb4_unicode_ci)
   • Click “Create”
3. Verify Creation:
   • Your new database should appear in the left sidebar
   • Click on it to view its structure

Naming Conventions

Good database names:

• my_store
• blog_database
• user_management

Avoid:

• Spaces in names
• Special characters (except underscore)
• Reserved MySQL keywords
• Starting with numbers

Creating a Database with SQL

Basic CREATE DATABASE Syntax

CREATE DATABASE database_name;

With Character Set and Collation

CREATE DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Checking if Database Exists

CREATE DATABASE IF NOT EXISTS my_database;

Using MySQL Command Line

Accessing MySQL Command Line

WAMP:

# Navigate to MySQL bin directory
cd C:\wamp64\bin\mysql\mysql[version]\bin
mysql -u root -p

XAMPP Windows:

cd C:\xampp\mysql\bin
mysql -u root -p

XAMPP macOS/Linux:

/Applications/XAMPP/xamppfiles/bin/mysql -u root -p

Creating Database via Command Line

-- Create database
CREATE DATABASE test_db;

-- Show all databases
SHOW DATABASES;

-- Select database to use
USE test_db;

-- Show currently selected database
SELECT DATABASE();

Database Character Sets and Collations

Understanding Character Sets

• Character Set: Defines which characters can be stored
• Collation: Defines how characters are compared and sorted

Common Character Sets

1. utf8mb4 (Recommended):
   • Full Unicode support
   • Supports emojis and special characters
   • Modern standard
2. utf8:
   • Limited Unicode support
   • Legacy option
3. latin1:
   • Western European characters only
   • Not recommended for new projects

Setting Character Set

CREATE DATABASE my_database
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

Managing Databases

Viewing All Databases

SHOW DATABASES;

Selecting a Database

USE database_name;

Viewing Current Database

SELECT DATABASE();

Checking Database Size

SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name'
GROUP BY table_schema;

Deleting a Database

Using SQL

DROP DATABASE database_name;

With Safety Check

DROP DATABASE IF EXISTS database_name;

Using phpMyAdmin

1. Select the database from left sidebar
2. Click “Operations” tab
3. Scroll to “Remove database”
4. Click “Drop the database (DROP)”
5. Confirm the action

Warning: Dropping a database permanently deletes all data. Always backup first!

Database Backup and Restore

Backup Using phpMyAdmin

1. Select database
2. Click “Export” tab
3. Choose “Quick” or “Custom” method
4. Select format (SQL recommended)
5. Click “Go”

Backup Using Command Line

# WAMP/XAMPP
mysqldump -u root -p database_name > backup.sql

# With timestamp
mysqldump -u root -p database_name > backup_$(date +%Y%m%d).sql

Restore Database

mysql -u root -p database_name < backup.sql

Best Practices

1. Naming Conventions

• Use lowercase with underscores
• Be descriptive but concise
• Follow consistent patterns

2. Character Set

• Always use utf8mb4 for new databases
• Set appropriate collation

3. Organization

• One database per application
• Separate development, testing, and production databases

4. Documentation

• Document database purpose
• Maintain schema documentation
• Keep track of changes

5. Security

• Use appropriate user privileges
• Never use root for application access
• Regularly backup databases

Example: Complete Database Setup

-- Create database with proper settings
CREATE DATABASE IF NOT EXISTS online_store
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Verify creation
SHOW DATABASES LIKE 'online_store';

-- Select the database
USE online_store;

-- Verify selection
SELECT DATABASE();

-- Show character set settings
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

Troubleshooting

Database Creation Failed

Problem: Error creating database

Solutions:

1. Check if database name already exists
2. Verify you have CREATE privileges
3. Check for invalid characters in name
4. Ensure MySQL service is running

Cannot Select Database

Problem: “Database doesn’t exist” error

Solutions:

1. Verify database name spelling
2. Run SHOW DATABASES; to list all databases
3. Check user privileges

Character Set Issues

Problem: Special characters not displaying correctly

Solutions:

1. Use utf8mb4 character set
2. Update existing database:

   ALTER DATABASE database_name
   CHARACTER SET utf8mb4
   COLLATE utf8mb4_unicode_ci;
   

Practice Exercises

Exercise 1: Basic Database Creation

-- Create a database for a library system
-- Include proper character set and collation
-- Verify it was created successfully

Exercise 2: Multiple Databases

-- Create three databases: dev_blog, test_blog, prod_blog
-- List all databases
-- Drop the test database
-- Verify it was deleted

Exercise 3: Database Information

-- Create a database
-- Check its character set
-- Check its collation
-- Find its size

Next Steps

• Tables and Schemas
• Data Types
• SQL Queries

Additional Resources

• MySQL CREATE DATABASE Documentation
• Character Sets and Collations
• MySQL Database Management