PHP Fundamentals

SQL: Creating & Managing Databases

13 min Lesson 31 of 45

Creating a Database

Before you can store data, you need to create a database. The CREATE DATABASE statement creates a new database.

Basic Syntax

CREATE DATABASE database_name;

Example: Creating a Database

CREATE DATABASE my_shop;

Naming Conventions:

Use lowercase letters
Use underscores instead of spaces: my_shop not My Shop
Be descriptive: ecommerce_db is better than db1
Avoid special characters and reserved keywords

Create Database If Not Exists

To avoid errors if the database already exists, use IF NOT EXISTS:

CREATE DATABASE IF NOT EXISTS my_shop;

Character Set and Collation

Specify character encoding for proper handling of international characters:

CREATE DATABASE my_shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Character Set Options

utf8mb4: Recommended for full Unicode support (emojis, international characters)
utf8: Basic Unicode (limited emoji support)
latin1: Western European characters only

Collation determines how text is compared and sorted:

utf8mb4_unicode_ci: Case-insensitive, accurate sorting
utf8mb4_bin: Case-sensitive, binary comparison

Selecting a Database

Before working with tables, you need to select which database to use:

USE my_shop;

Viewing Databases

List All Databases

SHOW DATABASES;

Output example:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_shop            |
| mysql              |
| performance_schema |
+--------------------+

Show Current Database

SELECT DATABASE();

Creating Tables

Tables are created within a database using the CREATE TABLE statement.

Basic Table Structure

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints
);

Example: Users Table

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Understanding Column Constraints

Common Constraints

PRIMARY KEY: Uniquely identifies each row (automatically NOT NULL)
AUTO_INCREMENT: Automatically generates sequential numbers
NOT NULL: Column cannot contain NULL values
UNIQUE: All values in column must be different
DEFAULT: Sets default value if none provided
FOREIGN KEY: Links to another table (covered later)

Example: Products Table

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DECIMAL for Money: Always use DECIMAL for monetary values, not FLOAT or DOUBLE. DECIMAL provides exact precision, while floating-point types can cause rounding errors.

Example: DECIMAL(10, 2) = 10 total digits, 2 after decimal (max: 99999999.99)

Example: Orders Table with Foreign Key

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Foreign Key Actions

ON DELETE CASCADE: Delete orders when user is deleted
ON DELETE SET NULL: Set user_id to NULL when user is deleted
ON DELETE RESTRICT: Prevent user deletion if orders exist
ON UPDATE CASCADE: Update foreign key if referenced key changes

Create Table If Not Exists

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

Viewing Tables

List All Tables in Current Database

SHOW TABLES;

Output example:

+-------------------+
| Tables_in_my_shop |
+-------------------+
| orders            |
| products          |
| users             |
+-------------------+

View Table Structure

DESCRIBE users;
-- OR
DESC users;
-- OR
SHOW COLUMNS FROM users;

Output example:

+------------+--------------+------+-----+-------------------+
| Field      | Type         | Null | Key | Default           |
+------------+--------------+------+-----+-------------------+
| id         | int(11)      | NO   | PRI | NULL              |
| username   | varchar(50)  | NO   | UNI | NULL              |
| email      | varchar(100) | NO   | UNI | NULL              |
| password   | varchar(255) | NO   |     | NULL              |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+

View Complete Table Creation Statement

SHOW CREATE TABLE users;

Modifying Tables

Adding a Column

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

Adding Multiple Columns

ALTER TABLE users
ADD COLUMN address TEXT,
ADD COLUMN city VARCHAR(50),
ADD COLUMN country VARCHAR(50);

Adding Column at Specific Position

-- Add after specific column
ALTER TABLE users
ADD COLUMN middle_name VARCHAR(50) AFTER username;

-- Add as first column
ALTER TABLE users
ADD COLUMN user_id INT FIRST;

Modifying a Column

-- Change column data type
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30);

-- Change column name and type
ALTER TABLE users
CHANGE COLUMN phone phone_number VARCHAR(30);

Dropping a Column

ALTER TABLE users
DROP COLUMN middle_name;

Warning: Dropping a column permanently deletes all data in that column. Always backup your database before making structural changes!

Adding Constraints

-- Add UNIQUE constraint
ALTER TABLE users
ADD UNIQUE (email);

-- Add INDEX for faster searches
ALTER TABLE products
ADD INDEX idx_category (category);

-- Add PRIMARY KEY (if not exists)
ALTER TABLE users
ADD PRIMARY KEY (id);

Renaming a Table

RENAME TABLE old_name TO new_name;

-- OR
ALTER TABLE old_name RENAME TO new_name;

Deleting Tables and Databases

Drop a Table

DROP TABLE table_name;

-- Safe version (no error if doesn't exist)
DROP TABLE IF EXISTS table_name;

Truncate a Table (Delete All Data, Keep Structure)

TRUNCATE TABLE users;

DROP vs TRUNCATE vs DELETE

DROP TABLE: Removes entire table and all data permanently
TRUNCATE TABLE: Removes all data but keeps table structure, resets AUTO_INCREMENT
DELETE FROM: Removes data row by row, can use WHERE clause, doesn't reset AUTO_INCREMENT

Drop a Database

DROP DATABASE database_name;

-- Safe version
DROP DATABASE IF EXISTS database_name;

Danger Zone: DROP DATABASE permanently deletes the entire database and all its tables. There is no undo! Always create backups first.

Indexes for Performance

Indexes speed up data retrieval but slow down inserts/updates. Use them on columns frequently used in WHERE, ORDER BY, and JOIN clauses.

Creating Indexes

-- Single column index
CREATE INDEX idx_username ON users(username);

-- Multiple column index (composite)
CREATE INDEX idx_name_category ON products(name, category);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

Viewing Indexes

SHOW INDEX FROM users;

Dropping an Index

DROP INDEX idx_username ON users;

-- OR
ALTER TABLE users DROP INDEX idx_username;

Practical Example: Complete E-Commerce Database

-- Create database
CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ecommerce;

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

-- Categories table
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    image_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_category (category_id),
    INDEX idx_price (price)
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_status (status)
);

-- Order items table (many-to-many relationship)
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

Exercise: Build a Blog Database

Create a database structure for a blogging platform with these requirements:

Database: Create blog_platform with UTF-8 support
Authors table: id, name, email (unique), bio, created_at
Posts table: id, author_id (foreign key), title, content, published_at, views (default 0), created_at, updated_at
Comments table: id, post_id (foreign key), author_name, email, content, created_at
Tags table: id, name (unique)
Post_tags table: post_id, tag_id (many-to-many relationship)

Add appropriate indexes on foreign keys and frequently searched columns.

Best Practices Summary

Always use IF NOT EXISTS when creating databases/tables in scripts
Set UTF-8 encoding (utf8mb4) for international character support
Every table should have a PRIMARY KEY with AUTO_INCREMENT
Use NOT NULL for required fields
Add timestamps (created_at, updated_at) to track changes
Use DECIMAL for money, not FLOAT or DOUBLE
Add indexes on foreign keys and frequently queried columns
Use descriptive, consistent naming conventions
Document your database structure
Always backup before making structural changes

What's Next?

Now that you know how to create and manage databases and tables, the next lesson will cover inserting and selecting data using SQL INSERT and SELECT statements.