🏗️ Lesson 4: Creating Databases & Tables

Creating and Managing Databases

A MySQL server can hold many databases, and each database holds many tables. Think of it like a filing cabinet: the server is the cabinet, each database is a drawer, and each table is a folder inside that drawer.

CREATE DATABASE

To create a new database:

CREATE DATABASE bookstore;
                

Query OK, 1 row affected (0.01 sec)
                    

Verify it exists:

SHOW DATABASES;
                

You should see bookstore in the list alongside the system databases.

CREATE DATABASE IF NOT EXISTS

If you try to create a database that already exists, MySQL throws an error. To avoid this, use the IF NOT EXISTS clause:

-- Safe version — won't error if it already exists
CREATE DATABASE IF NOT EXISTS bookstore;
                

USE — Switch to Your Database

After creating a database, switch to it:

USE bookstore;
                

Database changed
                    

Now all CREATE TABLE, SELECT, and other commands will operate inside bookstore.

DROP DATABASE

To permanently delete a database and all its tables and data:

DROP DATABASE bookstore;
                

The safe version:

DROP DATABASE IF EXISTS bookstore;
                

Character Set and Collation

When creating a database, you can specify a character set (which characters can be stored) and collation (how those characters are sorted and compared). The modern default is utf8mb4, which supports all Unicode characters including emoji:

CREATE DATABASE IF NOT EXISTS bookstore
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
                

Naming Conventions

Good names make databases easy to understand and maintain. Here are the conventions used by most professionals:

💡 Tip: When in doubt, ask yourself: "If a new developer looked at this name, would they immediately understand what it holds?" If yes, it's a good name.

Creating Tables

The CREATE TABLE statement defines a new table — its name, its columns, and each column's data type. Here's the general syntax:

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

Let's break this down with a real example:

CREATE TABLE books (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    price       DECIMAL(8, 2) NOT NULL,
    pages       INT,
    published   DATE,
    in_stock    BOOLEAN DEFAULT TRUE
);
                

Here's what each part means:

CREATE TABLE IF NOT EXISTS

Just like databases, you can guard against errors:

CREATE TABLE IF NOT EXISTS books (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    title  VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    price  DECIMAL(8, 2) NOT NULL
);
                

Understanding the Key Constraints

You saw a few keywords after the data types. Here's a quick reference — we'll explore these deeply in Lesson 5:

Building Our Practice Database

Let's build a database we'll use throughout the rest of this course — an online bookstore. We'll start with three tables and expand them in later lessons.

Step 1: Create the Database

CREATE DATABASE IF NOT EXISTS bookstore
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE bookstore;
                

Step 2: Create the Books Table

CREATE TABLE IF NOT EXISTS books (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    price       DECIMAL(8, 2) NOT NULL,
    pages       INT,
    published   DATE,
    in_stock    BOOLEAN DEFAULT TRUE
);
                

Step 3: Create the Customers Table

CREATE TABLE IF NOT EXISTS customers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL UNIQUE,
    city        VARCHAR(50),
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);
                

Notice the new pieces:

• UNIQUE on email — no two customers can share the same email address
• DATETIME DEFAULT CURRENT_TIMESTAMP — automatically records when the row was created

Step 4: Create the Categories Table

CREATE TABLE IF NOT EXISTS categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255)
);
                

Step 5: Verify Your Work

-- List all tables in bookstore
SHOW TABLES;

-- Check each table's structure
DESCRIBE books;
DESCRIBE customers;
DESCRIBE categories;
                

+---------------------+
| Tables_in_bookstore |
+---------------------+
| books               |
| categories          |
| customers           |
+---------------------+
3 rows in set (0.00 sec)
                    

Modifying Tables with ALTER TABLE

Requirements change. You'll often need to add new columns, rename existing ones, change data types, or remove columns that are no longer needed. The ALTER TABLE statement handles all of this.

ADD a Column

-- Add a 'phone' column to the customers table
ALTER TABLE customers
ADD COLUMN phone VARCHAR(20);
                

By default, the new column is added at the end. You can control placement:

-- Add 'phone' after the 'email' column
ALTER TABLE customers
ADD COLUMN phone VARCHAR(20) AFTER email;

-- Add a column at the very beginning
ALTER TABLE customers
ADD COLUMN member_id VARCHAR(10) FIRST;
                

MODIFY a Column (change its type or constraints)

-- Make 'phone' longer and require it
ALTER TABLE customers
MODIFY COLUMN phone VARCHAR(30) NOT NULL;
                

RENAME a Column

-- Rename 'phone' to 'phone_number'
ALTER TABLE customers
RENAME COLUMN phone TO phone_number;
                

CHANGE a Column (rename + change type in one step)

-- Rename 'phone_number' to 'mobile' AND change its type
ALTER TABLE customers
CHANGE COLUMN phone_number mobile VARCHAR(25);
                

The difference between RENAME COLUMN and CHANGE COLUMN:

• RENAME COLUMN — just renames (simpler, MySQL 8.0+)
• CHANGE COLUMN — renames AND lets you change the data type (works in all versions)

DROP a Column

-- Remove the 'mobile' column entirely
ALTER TABLE customers
DROP COLUMN mobile;
                

Multiple Changes in One Statement

You can combine multiple alterations in a single ALTER TABLE:

ALTER TABLE books
    ADD COLUMN isbn VARCHAR(13) UNIQUE AFTER id,
    ADD COLUMN language VARCHAR(30) DEFAULT 'English',
    MODIFY COLUMN pages INT UNSIGNED;
                

This is more efficient than running three separate ALTER TABLE commands because MySQL only rebuilds the table once.

ALTER TABLE Quick Reference

Renaming and Dropping Tables

RENAME TABLE

-- Rename a table
RENAME TABLE categories TO genres;

-- Rename it back
RENAME TABLE genres TO categories;
                

You can also rename multiple tables in a single statement:

RENAME TABLE
    old_name1 TO new_name1,
    old_name2 TO new_name2;
                

DROP TABLE

Permanently deletes a table and all its data:

-- Dangerous — no undo!
DROP TABLE categories;

-- Safer — won't error if the table doesn't exist
DROP TABLE IF EXISTS categories;
                

TRUNCATE TABLE

If you want to keep the table structure but delete all rows, use TRUNCATE:

-- Removes all data but keeps the table
TRUNCATE TABLE books;
                

DROP DATABASE IF EXISTS bookstore;

-- Then re-run the CREATE statements from the
-- "Building Our Practice Database" section above.
                    

Inspecting Your Work

After creating or modifying tables, you'll often want to verify the results. Here are the most useful inspection commands:

DESCRIBE — Column Overview

DESCRIBE books;
-- Shorthand:
DESC books;
                

Shows columns, types, nullability, keys, and defaults in a quick table.

SHOW CREATE TABLE — Full Definition

SHOW CREATE TABLE books\G
                

This shows the exact SQL that would recreate the table, including all constraints, defaults, character sets, and engine settings. It's the most complete view of a table's definition:

*************************** 1. row ***************************
       Table: books
Create Table: CREATE TABLE `books` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `author` varchar(100) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `pages` int DEFAULT NULL,
  `published` date DEFAULT NULL,
  `in_stock` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                    

SHOW COLUMNS — Alternative to DESCRIBE

SHOW COLUMNS FROM books;

-- With more detail:
SHOW FULL COLUMNS FROM books;
                

SHOW FULL COLUMNS adds the collation, privileges, and any column comments.

In phpMyAdmin

Click on a table → Structure tab to see the same information visually. The SQL tab with SHOW CREATE TABLE books; works here too.

Exercises

-- create_bookstore.sql
-- Sets up the bookstore database from scratch

CREATE DATABASE IF NOT EXISTS bookstore
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE bookstore;

CREATE TABLE IF NOT EXISTS books (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    price       DECIMAL(8, 2) NOT NULL,
    pages       INT,
    published   DATE,
    in_stock    BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS customers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL UNIQUE,
    city        VARCHAR(50),
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255)
);
                        

-- Make changes
ALTER TABLE books ADD COLUMN rating DECIMAL(3,2);
ALTER TABLE customers ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE categories RENAME COLUMN description TO summary;

-- Verify
DESCRIBE books;
DESCRIBE customers;
DESCRIBE categories;

-- Undo changes
ALTER TABLE books DROP COLUMN rating;
ALTER TABLE customers DROP COLUMN phone;
ALTER TABLE categories RENAME COLUMN summary TO description;
                        

CREATE TABLE IF NOT EXISTS publishers (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL UNIQUE,
    country       VARCHAR(50),
    founded_year  INT,
    website       VARCHAR(200)
);

DESCRIBE publishers;
SHOW CREATE TABLE publishers\G
                        

Summary

📚 Additional Resources

• MySQL Docs — CREATE DATABASE
• MySQL Docs — CREATE TABLE
• MySQL Docs — ALTER TABLE

🚀 What's Next?

You've built the structure — now let's understand the building blocks. In the next lesson, you'll dive deep into data types and constraints — choosing the right type for each column, enforcing rules on your data, and understanding how MySQL stores numbers, text, dates, and more.