Creating Databases and Tables

A clean database starts with a deliberate charset, engine and table design. Getting this right early avoids migration pain.

Create a Database

Use utf8mb4 so emoji and all Unicode characters are stored correctly. InnoDB supports transactions and foreign keys.

CREATE DATABASE school
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE school;

Create Tables

Use INT for identifiers, VARCHAR for short text, TEXT for long text, DECIMAL for money and DATETIME or TIMESTAMP for time.

CREATE TABLE courses (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(150) NOT NULL,
    description TEXT,
    price       DECIMAL(10, 2) NOT NULL DEFAULT 0,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_courses_active (is_active)
) ENGINE=InnoDB;

Modify Tables

Use ALTER TABLE to add columns, change types or add indexes after a table exists. Test migrations on a copy first.

ALTER TABLE courses
    ADD COLUMN level VARCHAR(20) NOT NULL DEFAULT 'beginner' AFTER price;

ALTER TABLE courses
    ADD INDEX idx_courses_level (level);

Practice

1. Create a database called library with utf8mb4.
2. Create a books table with title, author and year.
3. Add an index on author.

Hands-on exercise

Create a students table with:
1. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY.
2. name VARCHAR(100) NOT NULL.
3. email VARCHAR(150) NOT NULL UNIQUE.
4. created_at TIMESTAMP defaulting to now.
5. InnoDB engine.

Recap

Create databases with utf8mb4, choose types that match the data and use InnoDB for transactions and foreign keys.