MySQL Create Table

USE shop;

-- customers table
CREATE TABLE IF NOT EXISTS customers (
    customer_id  INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(255) NOT NULL,
    phone        VARCHAR(20),
    active       TINYINT(1)   NOT NULL DEFAULT 1,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- products table
CREATE TABLE IF NOT EXISTS products (
    product_id   INT           UNSIGNED NOT NULL AUTO_INCREMENT,
    name         VARCHAR(200)  NOT NULL,
    description  TEXT,
    price        DECIMAL(10,2) NOT NULL,
    stock        INT           UNSIGNED NOT NULL DEFAULT 0,
    category     VARCHAR(100),
    created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- orders table
CREATE TABLE IF NOT EXISTS orders (
    order_id     INT           UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id  INT           UNSIGNED NOT NULL,
    total        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status       ENUM('pending','processing','shipped','delivered','cancelled')
                               NOT NULL DEFAULT 'pending',
    ordered_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- order_items table
CREATE TABLE IF NOT EXISTS order_items (
    item_id      INT           UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id     INT           UNSIGNED NOT NULL,
    product_id   INT           UNSIGNED NOT NULL,
    quantity     INT           UNSIGNED NOT NULL DEFAULT 1,
    unit_price   DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- List all tables in the current database
SHOW TABLES;

-- Describe a table's columns, types, and constraints
DESCRIBE customers;
-- or shorthand:
DESC orders;

-- Show the full CREATE TABLE statement (useful for backups/migrations)
SHOW CREATE TABLE customers\G

-- Show table status (engine, row count, charset, etc.)
SHOW TABLE STATUS LIKE 'customers'\G

-- Add a new column
ALTER TABLE customers
    ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add a column at a specific position
ALTER TABLE customers
    ADD COLUMN date_of_birth DATE AFTER last_name;

-- Modify a column's data type or constraints
ALTER TABLE customers
    MODIFY COLUMN phone VARCHAR(30);

-- Rename a column (MySQL 8.0+)
ALTER TABLE customers
    RENAME COLUMN phone TO phone_number;

-- Drop a column
ALTER TABLE customers
    DROP COLUMN date_of_birth;

-- Rename a table
ALTER TABLE customers RENAME TO shop_customers;
-- Rename it back
ALTER TABLE shop_customers RENAME TO customers;

-- Remove all rows but keep the table structure
-- Also resets AUTO_INCREMENT counter to 1
TRUNCATE TABLE order_items;

-- Drop a table permanently (cannot be undone)
DROP TABLE order_items;

-- Drop only if it exists (avoids error)
DROP TABLE IF EXISTS order_items;

-- Drop multiple tables at once
DROP TABLE IF EXISTS order_items, orders, products, customers;

-- Create a new table based on an existing table's structure and data
CREATE TABLE customers_backup AS
    SELECT * FROM customers;

-- Create a table with the same structure but no data
CREATE TABLE customers_empty LIKE customers;