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:
- Users: Stores information about the customers.
- Products: Information about products sold in the eCommerce store.
- Orders: Keeps track of customer orders.
- OrderItems: Details of products purchased in each order.
- Payments: Payment records for each order.
- 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! 😃
Top comments (0)