Creating a comprehensive example that covers most of MySQL’s features requires combining data modeling, querying, and advanced features such as indexing, stored procedures, triggers, transactions, views, and more. Below is a detailed example that touches on various aspects of MySQL. The scenario involves an eCommerce system with features such as user management, products, orders, inventory, and payments.

Schema Design

We’ll define the following tables:

1. Users: Stores information about the customers.
2. Products: Information about products sold in the eCommerce store.
3. Orders: Keeps track of customer orders.
4. OrderItems: Details of products purchased in each order.
5. Payments: Payment records for each order.
6. Inventory: Keeps track of product inventory.

Step 1: Create the Database and Tables

1. Create the database

CREATE DATABASE ecommerce;
USE ecommerce;

2. Users Table

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active'
);

3. Products Table

CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4. Orders Table

CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total DECIMAL(10, 2) CHECK (total >= 0),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

5. OrderItems Table

CREATE TABLE OrderItems (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

6. Payments Table

CREATE TABLE Payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount DECIMAL(10, 2) CHECK (amount > 0),
    payment_method ENUM('credit_card', 'paypal', 'bank_transfer'),
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

7. Inventory Table

CREATE TABLE Inventory (
    product_id INT PRIMARY KEY,
    stock INT NOT NULL CHECK (stock >= 0),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Step 2: Basic Operations

Insert Data into Tables

• Insert Users
  

  INSERT INTO Users (username, email, password_hash)
  VALUES
  ('john_doe', 'john@example.com', 'hashed_password1'),
  ('jane_doe', 'jane@example.com', 'hashed_password2');
  

• Insert Products
  

  INSERT INTO Products (name, description, price)
  VALUES
  ('Laptop', 'A high-end laptop', 1500.00),
  ('Smartphone', 'A flagship smartphone', 800.00);
  

• Insert Inventory
  

  INSERT INTO Inventory (product_id, stock)
  VALUES 
  (1, 100),
  (2, 50);
  

Fetch Data Using SELECT Queries

• Get all users:
  

  SELECT * FROM Users;
  

• Get products that are in stock:
  

  SELECT p.name, i.stock 
  FROM Products p
  JOIN Inventory i ON p.product_id = i.product_id
  WHERE i.stock > 0;
  

• Fetch order details for a specific user:
  

  SELECT o.order_id, o.order_date, o.status, oi.quantity, p.name 
  FROM Orders o
  JOIN OrderItems oi ON o.order_id = oi.order_id
  JOIN Products p ON oi.product_id = p.product_id
  WHERE o.user_id = 1;
  

Step 3: More Advanced Features

Transactions

In an eCommerce system, placing an order involves multiple steps such as checking inventory, placing the order, and recording the payment. Transactions ensure that these steps are either fully completed or rolled back in case of any failure.

START TRANSACTION;

-- Check if there is enough stock
SELECT stock FROM Inventory WHERE product_id = 1 FOR UPDATE;

-- Place an order
INSERT INTO Orders (user_id, total) VALUES (1, 1500.00);

-- Get the newly inserted order ID
SET @order_id = LAST_INSERT_ID();

-- Insert order items
INSERT INTO OrderItems (order_id, product_id, quantity, price)
VALUES (@order_id, 1, 1, 1500.00);

-- Reduce stock
UPDATE Inventory SET stock = stock - 1 WHERE product_id = 1;

COMMIT;

Stored Procedures

Let’s create a stored procedure to add a new product and update the inventory at the same time.

DELIMITER //
CREATE PROCEDURE AddProduct(IN pname VARCHAR(255), IN pdesc TEXT, IN pprice DECIMAL(10, 2), IN pstock INT)
BEGIN
    DECLARE pid INT;

    -- Insert into products table
    INSERT INTO Products (name, description, price) 
    VALUES (pname, pdesc, pprice);

    -- Get the last inserted product_id
    SET pid = LAST_INSERT_ID();

    -- Insert into inventory
    INSERT INTO Inventory (product_id, stock) 
    VALUES (pid, pstock);
END //
DELIMITER ;

Call the procedure:

CALL AddProduct('Tablet', 'A new tablet', 300.00, 200);

Triggers

Create a trigger that updates the stock in the Inventory table when a new order is placed.

DELIMITER //
CREATE TRIGGER UpdateStockAfterOrder
AFTER INSERT ON OrderItems
FOR EACH ROW
BEGIN
    UPDATE Inventory
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
END //
DELIMITER ;

Views

Create a view to easily fetch user order details:

CREATE VIEW UserOrderDetails AS
SELECT u.username, o.order_id, o.order_date, o.status, p.name AS product_name, oi.quantity, oi.price
FROM Users u
JOIN Orders o ON u.user_id = o.user_id
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id;

Now, querying the view:

SELECT * FROM UserOrderDetails WHERE username = 'john_doe';

Indexes

Create an index on the Orders table for faster querying by user_id:

CREATE INDEX idx_user_id ON Orders(user_id);

Step 4: Joins, Subqueries, and Aggregations

Join Example

Fetch the total quantity ordered by each user:

SELECT u.username, SUM(oi.quantity) AS total_quantity
FROM Users u
JOIN Orders o ON u.user_id = o.user_id
JOIN OrderItems oi ON o.order_id = oi.order_id
GROUP BY u.username;

Subquery Example

Get the names of users who have placed an order for a product priced over $1000:

SELECT username 
FROM Users 
WHERE user_id IN (
    SELECT o.user_id 
    FROM Orders o
    JOIN OrderItems oi ON o.order_id = oi.order_id
    WHERE oi.price > 1000
);

Other Commands

1. EXPLAIN Command

The EXPLAIN command helps you understand how MySQL executes a query by showing a detailed breakdown of the query plan. This is essential for performance tuning.

Example:

EXPLAIN SELECT u.username, SUM(oi.quantity) AS total_quantity
FROM Users u
JOIN Orders o ON u.user_id = o.user_id
JOIN OrderItems oi ON o.order_id = oi.order_id
GROUP BY u.username;

Output details will include columns like:

• id: The order of operations.
• select_type: Whether it’s a simple query, subquery, etc.
• table: The table being accessed.
• type: The type of join (e.g., ALL, index, range, etc.).
• possible_keys: Any possible indexes MySQL could use.
• key: The index MySQL actually used.
• rows: The number of rows examined.
• Extra: Additional info (e.g., Using index, Using temporary, etc.).

This information helps identify performance bottlenecks, missing indexes, or unnecessary full table scans.

2. Foreign Key Constraints

Foreign key constraints help maintain referential integrity between tables. They ensure that relationships between tables are consistent and that referenced rows in parent tables cannot be deleted or modified unless explicitly handled.

Example:

Add a foreign key to the Payments table:

ALTER TABLE Payments
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES Orders(order_id)
ON DELETE CASCADE;

This ensures that when an order is deleted, its associated payments are automatically deleted as well.

3. Full-Text Search

MySQL supports full-text indexing for advanced text searches, especially useful in large text fields like descriptions, comments, or blogs.

Example:

ALTER TABLE Products ADD FULLTEXT(name, description);

Now, you can perform full-text searches:

SELECT * FROM Products
WHERE MATCH(name, description) AGAINST('high-end laptop');

Full-text search provides more sophisticated search capabilities than LIKE for large text data.

4. Prepared Statements

Prepared statements help avoid SQL injection attacks and improve performance by pre-compiling SQL queries.

Example:

PREPARE stmt FROM 'SELECT * FROM Users WHERE email = ?';
SET @email = 'john@example.com';
EXECUTE stmt USING @email;
DEALLOCATE PREPARE stmt;

This binds the query parameter safely, improving both performance and security.

5. Error Handling

MySQL supports handling errors using DECLARE and HANDLER within stored procedures. You can gracefully handle errors like integrity constraint violations or failed queries.

Example:

DELIMITER //
CREATE PROCEDURE SafeInsertUser(IN pname VARCHAR(255), IN pemail VARCHAR(255))
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Handle the error
        ROLLBACK;
    END;

    START TRANSACTION;

    -- Insert data
    INSERT INTO Users (username, email, password_hash) 
    VALUES (pname, pemail, 'hashed_password');

    COMMIT;
END //
DELIMITER ;

This ensures that if an error occurs during the insert, the transaction will roll back.

6. User Permissions

In a real-world environment, you need to manage user access to different databases or tables. You can define which users can perform which operations (like SELECT, INSERT, DELETE, etc.).

Example:

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON ecommerce.* TO 'readonly_user'@'localhost';

This creates a user with read-only access to the ecommerce database.

7. Replication and Partitioning

While not common for small-scale systems, replication and partitioning are crucial for scaling.

• Replication: MySQL supports master-slave and master-master replication setups for high availability.

   -- Set up master and slave replication, involving binary logs and configuration.

• Partitioning: You can partition large tables to improve query performance by distributing data across different partitions.

   CREATE TABLE OrdersPartitioned (
    order_id INT,
    user_id INT,
    order_date DATE,
    total DECIMAL(10, 2)
    )
    PARTITION BY RANGE(YEAR(order_date)) (
        PARTITION p0 VALUES LESS THAN (2020),
        PARTITION p1 VALUES LESS THAN (2021),
        PARTITION p2 VALUES LESS THAN (2022)
    );

This partitions the Orders table by year.

8. UNION and UNION ALL

UNION is used to combine results from multiple SELECT statements. UNION ALL does the same but allows duplicate results.

Example:

SELECT username FROM Users WHERE status = 'active'
UNION
SELECT username FROM Admins WHERE active = 1;

If duplicates are acceptable:

SELECT username FROM Users WHERE status = 'active'
UNION ALL
SELECT username FROM Admins WHERE active = 1;

9. Temporary Tables

Temporary tables are useful when you need to store intermediate results that are only needed for the duration of a session.

Example:

CREATE TEMPORARY TABLE tempUserOrders AS
SELECT user_id, COUNT(order_id) AS total_orders 
FROM Orders 
GROUP BY user_id;

These tables are automatically dropped when the session ends.

10. Locks

MySQL provides various locks for concurrency control.

• Table Lock:
  

  LOCK TABLES Orders WRITE;
  -- Perform operations
  UNLOCK TABLES;
  

• Row Lock (with InnoDB):
  

  START TRANSACTION;
  SELECT * FROM Inventory WHERE product_id = 1 FOR UPDATE;
  -- Perform operations
  COMMIT;
  

  Locks prevent data inconsistency in concurrent environments.

11. CASE Statements

CASE statements are used for conditional logic within queries.

Example:

SELECT 
    order_id,
    CASE 
        WHEN status = 'pending' THEN 'Waiting'
        WHEN status = 'shipped' THEN 'On the way'
        ELSE 'Delivered'
    END AS order_status
FROM Orders;

This displays a custom message based on the order status.

12. Advanced Indexing

Apart from basic indexes, MySQL supports composite indexes, full-text indexes, and unique indexes.

• Composite Index: Improves query performance on multiple columns.
  

  CREATE INDEX idx_user_order ON Orders(user_id, order_date);
  

• Unique Index: Ensures all values in the column are unique.
  

  CREATE UNIQUE INDEX idx_unique_email ON Users(email);
  

Conclusion

This example provides a comprehensive overview of MySQL features, covering:

• Basic operations (create, read, update, delete)
• Transactions for maintaining data consistency
• Stored procedures for reusable logic
• Triggers for automatic actions
• Views for simplified querying
• Indexes for performance optimization
• Advanced querying techniques (joins, subqueries, aggregations)
• EXPLAIN: For query analysis and optimization.
• Foreign Key Constraints: For enforcing referential integrity.
• Full-Text Search: For advanced text searches.
• Prepared Statements: For security and performance.
• Error Handling: Using stored procedures.
• User Permissions: For access control.
• Replication and Partitioning: For scaling and performance.
• UNION and UNION ALL: For combining results.
• Temporary Tables: For session-specific operations.
• Locks: For concurrency control.
• CASE Statements: For conditional logic in queries.
• Advanced Indexing: For performance improvements.

This covers most of the essential features you would encounter in real-world MySQL projects.

If you found this helpful, let me know by leaving a 👍 or a comment!, or if you think this post could help someone, feel free to share it! Thank you very much! 😃