摘要：欢迎来到 SQL 系列的最后一讲，这里你将在一个小型项目中测试您的 SQL 技能。在这一讲中，我们将概述一个实践项目，该项目结合了你从前几讲中学到的知识。本项目将允许你应用 SQL 概念来解决实际场景问题，巩固你的理解，并获得实践经验。

1. 迷你项目场景：库存管理（Inventory Management）

假设你的任务是为库存管理系统设计和管理数据库。目标是创建一个数据库模式，用样例数据填充它，并编写 SQL 查询来执行各种与库存相关的任务。

2. 项目目标（Project Objectives）

数据库模式设计：首先设计数据库模式。确定表、表之间的关系以及管理库存所需的属性。假设有产品、类别、供应商和库存事务表。
数据填充：用样例数据填充表。创建各种产品、类别、供应商和事务，模拟真实世界的场景。
库存跟踪：编写 SQL 查询来跟踪库存水平。例如，你应该能够确定每种产品的当前库存数量。
销售和采购：实现记录销售和采购事务的功能。当产品售出或收到新库存时，你应该能够更新库存数量。
报告和分析：创建 SQL 查询以生成报告并对库存数据执行分析。例如，你可能想知道最畅销的产品、库存水平较低的产品或库存的总价值。
用户界面（可选）：如果你具备相关技能，可以考虑构建一个简单的用户界面来与数据库交互。其形式可以是一个 web 应用程序、桌面应用程序、甚至是命令行工具。

3. 示例数据库模式（Sample Database Schema）

下面是一个简化的数据库模式示例:

products Table:
— ProductID (Primary Key)
— ProductName
— CategoryID (Foreign Key)
— SupplierID (Foreign Key)
— UnitPrice
— QuantityOnHand

categories Table:
— CategoryID (Primary Key)
— CategoryName

suppliers Table:
— SupplierID (Primary Key)
— SupplierName

transactions Table:
— TransactionID (Primary Key)
— TransactionType (e.g., Sale, Purchase)
— ProductID (Foreign Key)
— Quantity
— TransactionDate

实体关系图（ERD）如下所示：

表创建脚本：

CREATE TABLE Categories
(
    CategoryID   INT AUTO_INCREMENT PRIMARY KEY,
    CategoryName VARCHAR(255) NOT NULL
);

CREATE TABLE Suppliers
(
    SupplierID   INT AUTO_INCREMENT PRIMARY KEY,
    SupplierName VARCHAR(255) NOT NULL
);

CREATE TABLE Products
(
    ProductID      INT AUTO_INCREMENT PRIMARY KEY,
    ProductName    VARCHAR(255) NOT NULL,
    CategoryID     INT,
    SupplierID     INT,
    UnitPrice      DECIMAL(10, 2),
    QuantityOnHand INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
    FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
);

CREATE TABLE Transactions
(
    TransactionID   INT AUTO_INCREMENT PRIMARY KEY,
    TransactionType ENUM ('Sale', 'Purchase') NOT NULL,
    ProductID       INT,
    Quantity        INT,
    TransactionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
);

示例数据填充：我们采用存储过程给每个表填充100条随机示例数据

-- Stored procedure to populate sample data
DROP PROCEDURE IF EXISTS PopulateSampleData;
DELIMITER //

CREATE PROCEDURE PopulateSampleData()
BEGIN
    DECLARE counter INT DEFAULT 1;

    WHILE counter <= 100
        DO
            -- Inserting into Categories table
            INSERT INTO Categories (CategoryName)
            VALUES (CONCAT('Category ', counter));

            -- Inserting into Suppliers table
            INSERT INTO Suppliers (SupplierName)
            VALUES (CONCAT('Supplier ', counter));

            -- Inserting into Products table
            INSERT INTO Products (ProductName, CategoryID, SupplierID, UnitPrice, QuantityOnHand)
            VALUES (CONCAT('Product ', counter),
                    counter,
                    counter,
                    ROUND(RAND() * 500 + 50, 2),
                    FLOOR(RAND() * 100) + 10);

            -- Inserting into Transactions table
            INSERT INTO Transactions (TransactionType, ProductID, Quantity)
            VALUES (IF(MOD(counter, 2) = 0, 'Sale', 'Purchase'),
                    counter,
                    FLOOR(RAND() * 10) + 1);

            SET counter = counter + 1;
        END WHILE;

END //

DELIMITER ;

-- Calling store procedure to populate sample data
CALL PopulateSampleData();

4. 示例查询（Sample Queries）

检索所有产品及其它们当前的库存信息：

SELECT ProductName, QuantityOnHand 
FROM products;

计算库存总价值：

SELECT SUM(UnitPrice * QuantityOnHand) AS TotalValue 
FROM products;

找到最畅销的产品（销量最高的产品）：

SELECT ProductName, 
       MAX(Quantity) AS MaxSalesQuantity 
FROM transactions
WHERE TransactionType = 'Sale'
GROUP BY ProductName;

在销售后更新特定产品的库存数量：这个需求我们就可以通过触发器实现，当事务表（transactions）中某产品发生销售（TransactionType字段值为‘Sale’）后，更新其在产品表（products）中的库存。

CREATE TRIGGER AfterSaleTransaction
    AFTER INSERT
    ON Transactions
    FOR EACH ROW
BEGIN
    -- If the transaction type is 'Sale'
    IF NEW.TransactionType = 'Sale' THEN
        UPDATE Products
        SET QuantityOnHand = QuantityOnHand - NEW.Quantity
        WHERE ProductID = NEW.ProductID;
    END IF;
END;

当然，还有其他各种各样的查询需求，你可以尽情发挥你的想象力和创造力，并用所学技术去实现它们。

5. 项目收益（Project Benefits）

实践经验：这个小项目可以让你将之前所学的 SQL 知识应用于实际环境，帮助你更加精通 SQL。
解决问题的能力：在管理库存中你将会遇到各种真实的挑战，这将提高你解决问题的能力。
产品开发：如果你选择构建一个用户界面，这个项目可以成为你作品集中一个有价值的补充，展示你的 SQL 和应用程序开发技能。
创造性自由：您可以根据自己的喜好灵活地设计数据库模式和查询，从而发挥您的创造性。
适用性：库存管理是一种常见的业务需求，你从本项目中获得的技能可以应用到不同的行业中。

6. 项目完成（Project Completion）

一旦你完成了你的库存管理小项目，请花点时间回顾你的工作，反思你遇到的挑战和你设计的解决方案。考虑进一步增强功能或优化 SQL 查询以获得更好性能的方法。

总结（Summary）
总之，这个小项目是巩固 SQL 技能和获得实践经验的绝佳机会。它结合了真实场景中的数据库设计、数据操作和查询编写。完成这个项目不仅是一个有益的成就，而且是对你 SQL 熟练程度的证明。每一讲中的单个主题拿出来都可以扩展和深挖，因此，你应该持续练习、探索新的 SQL 概念和技术，向着 SQL 专家的方向迈进。