MySQL Explained — Beginner's Guide with Examples

# Install MySQL server
sudo apt update
sudo apt install mysql-server -y

# Start the service
sudo systemctl start mysql
sudo systemctl enable mysql

# Secure the installation
sudo mysql_secure_installation
# Follow prompts: set root password, remove anonymous users,
# disable remote root login, remove test database

brew install mysql
brew services start mysql
mysql_secure_installation

mysql --version
# Expected: mysql  Ver 8.0.x for Linux on x86_64

# Log in as root
sudo mysql -u root -p

-- Create a database for our application
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a user and grant permissions
CREATE USER 'shop_user'@'localhost' IDENTIFIED BY 'secure_password_123';
GRANT ALL PRIVILEGES ON shop.* TO 'shop_user'@'localhost';
FLUSH PRIVILEGES;

-- Use the database
USE shop;

-- Create a customers table with MySQL-specific features
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active'
) ENGINE=InnoDB;

-- Create an orders table with foreign key
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Create order_items table
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Insert customers
INSERT INTO customers (first_name, last_name, email, phone) VALUES
    ('Alice', 'Johnson', 'alice@example.com', '555-0101'),
    ('Bob', 'Smith', 'bob@example.com', '555-0102'),
    ('Charlie', 'Brown', 'charlie@example.com', '555-0103'),
    ('Diana', 'Prince', 'diana@example.com', '555-0104');

-- Insert orders
INSERT INTO orders (customer_id, total_amount) VALUES
    (1, 150.00),
    (1, 75.50),
    (2, 200.00),
    (3, 45.99);

-- Insert order items
INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
    (1, 'Wireless Mouse', 2, 25.00),
    (1, 'USB-C Hub', 1, 100.00),
    (2, 'Notebook Set', 5, 15.10),
    (3, 'Monitor Stand', 1, 200.00),
    (4, 'Desk Lamp', 1, 45.99);

-- Get all orders with customer information
SELECT
    o.order_id,
    c.first_name,
    c.last_name,
    o.order_date,
    o.total_amount,
    o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

-- Show all customers, even those without orders
SELECT
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY total_spent DESC;

-- Detailed order breakdown with items
SELECT
    o.order_id,
    c.first_name,
    c.last_name,
    oi.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.product_name;

-- See how MySQL executes a query (before index)
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
-- type: ALL (full table scan — slow!)

-- Create an index on customer_id
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Now check again
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
-- type: ref (using index — fast!)

-- Index columns used in WHERE, JOIN, and ORDER BY
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_customers_name ON customers(last_name, first_name);

-- Unique indexes for columns that must be unique
CREATE UNIQUE INDEX idx_customers_email_unique ON customers(email);

# CORRECT (Python/MySQL)
cursor.execute("SELECT * FROM users WHERE email = %s", (user_email,))

# WRONG — SQL injection risk!
cursor.execute(f"SELECT * FROM users WHERE email = '{user_email}'")

# Backup MySQL database
mysqldump -u root -p shop > shop_backup.sql

# Restore from backup
mysql -u root -p shop < shop_backup.sql

SELECT
    c.first_name,
    c.last_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY total_spent DESC
LIMIT 1;

-- Create database
CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecommerce;

-- Products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_products_price (price),
    INDEX idx_products_name (name)
) ENGINE=InnoDB;

-- Cart table
CREATE TABLE cart (
    cart_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_cart_customer (customer_id)
) ENGINE=InnoDB;

-- Insert sample products
INSERT INTO products (name, description, price, stock_quantity) VALUES
    ('Ergonomic Keyboard', 'Mechanical keyboard with wrist rest', 89.99, 50),
    ('HD Webcam', '1080p webcam with built-in microphone', 59.99, 30),
    ('Laptop Stand', 'Adjustable aluminum laptop stand', 34.99, 100),
    ('Noise-Canceling Headphones', 'Wireless over-ear headphones', 199.99, 25);

-- Which products are low in stock?
SELECT name, stock_quantity
FROM products
WHERE stock_quantity < 50
ORDER BY stock_quantity;

-- What customer has the most expensive cart?
SELECT c.first_name, c.last_name, SUM(p.price * cart.quantity) AS cart_total
FROM cart
INNER JOIN customers c ON cart.customer_id = c.customer_id
INNER JOIN products p ON cart.product_id = p.product_id
GROUP BY cart.customer_id
ORDER BY cart_total DESC
LIMIT 1;