MySQL数据库基础练习系列4、商品库存管理系统

红目香薰

发布于 2024-06-07 15:56:26

720

发布于 2024-06-07 15:56:26

MySQL数据库基础练习系列目标

很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。

数据库环境

MySQL版本：5.7.31-log

数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。

字符集：utf8 排序规则：utf8_general_ci

使用工具：Navicat Premium 15，可以在下面的连接中下载

https://download.csdn.net/download/feng8403000/89403778

项目名称与项目简介

商品库存管理系统是一个旨在帮助企业和商户管理其商品库存、订单、供应商以及客户信息的综合性系统。该系统通过自动化和标准化的流程，提高了库存管理的效率和准确性，降低了库存积压和缺货的风险，从而为企业节省成本并提升客户满意度。

主要功能包括：

商品管理：添加、编辑、删除商品信息，设置库存预警阈值。
库存管理：实时更新库存数量，记录出入库信息，提供库存盘点功能。
订单管理：处理客户订单，包括订单生成、修改、取消和发货等功能。
供应商管理：维护供应商信息，记录采购订单和进货情况。
客户管理：记录客户信息，包括联系方式、购买记录等。
报表分析：生成库存报表、销售报表等，辅助企业进行决策分析。

数据库DDL（注意创建顺序）

为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。

-- 创建供应商表
CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '供应商ID（主键）',
    supplier_name VARCHAR(100) NOT NULL COMMENT '供应商名称',
    supplier_contact VARCHAR(50) COMMENT '联系人',
    supplier_phone VARCHAR(20) COMMENT '联系电话',
    supplier_email VARCHAR(100) COMMENT '电子邮箱',
    supplier_address VARCHAR(255) COMMENT '地址'
) COMMENT '供应商信息表';


-- 创建商品分类表
CREATE TABLE product_categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID（主键）',
    category_name VARCHAR(100) NOT NULL COMMENT '分类名称'
) COMMENT '商品分类表';


-- 创建商品表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID（主键）',
    product_name VARCHAR(100) NOT NULL COMMENT '商品名称',
    category_id INT NOT NULL COMMENT '所属分类ID',
    price DECIMAL(10, 2) NOT NULL COMMENT '价格',
    stock_quantity INT NOT NULL COMMENT '库存数量',
    stock_alert_threshold INT COMMENT '库存预警阈值',
    FOREIGN KEY (category_id) REFERENCES product_categories(category_id) ON DELETE CASCADE
) COMMENT '商品信息表';


-- 创建客户表
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '客户ID（主键）',
    customer_name VARCHAR(100) NOT NULL COMMENT '客户姓名',
    gender ENUM('男', '女') COMMENT '性别',
    phone_number VARCHAR(20) COMMENT '联系电话',
    email VARCHAR(100) COMMENT '电子邮箱',
    address VARCHAR(255) COMMENT '地址'
) COMMENT '客户信息表';


-- 创建订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID（主键）',
    customer_id INT NOT NULL COMMENT '客户ID',
    order_date DATE NOT NULL COMMENT '订单日期',
    total_price DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',
    order_status ENUM('待处理', '已发货', '已完成', '已取消') COMMENT '订单状态',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
) COMMENT '订单信息表';


-- 创建订单明细表
CREATE TABLE order_details (
    detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '明细ID（主键）',
    order_id INT NOT NULL COMMENT '订单ID',
    product_id INT NOT NULL COMMENT '商品ID',
    quantity INT NOT NULL COMMENT '购买数量',
    unit_price DECIMAL(10, 2) NOT NULL COMMENT '单价',
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
) COMMENT '订单明细表';


-- 创建采购订单表
CREATE TABLE purchase_orders (
    po_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '采购订单ID（主键）',
    supplier_id INT NOT NULL COMMENT '供应商ID',
    po_date DATE NOT NULL COMMENT '采购日期',
    total_cost DECIMAL(10, 2) NOT NULL COMMENT '总成本',
    status ENUM('待处理', '已接收', '已完成', '已取消') COMMENT '采购状态',
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id) ON DELETE CASCADE
) COMMENT '采购订单';

插入数据DML（注意插入数据顺序）

插入数据的时候也要注意主外键关系，如果没有外检的情况下是没有办法插入从表数据的。

-- 插入供应商数据
INSERT INTO suppliers (supplier_name, supplier_contact, supplier_phone, supplier_email, supplier_address) VALUES
('供应商A', '联系人A', '1234567890', 'supplierA@example.com', '地址A'),
('供应商B', '联系人B', '0987654321', 'supplierB@example.com', '地址B'),
('供应商C', '联系人C', '1122334455', 'supplierC@example.com', '地址C'),
('供应商D', '联系人D', '5544332211', 'supplierD@example.com', '地址D'),
('供应商E', '联系人E', '9012345678', 'supplierE@example.com', '地址E');


-- 插入商品分类数据
INSERT INTO product_categories (category_name) VALUES
('电子产品'),
('家居用品'),
('食品'),
('图书'),
('运动器材');


-- 插入商品数据，注意使用已存在的分类ID
INSERT INTO products (product_name, category_id, price, stock_quantity, stock_alert_threshold) VALUES
('智能手机', 1, 1999.99, 100, 20),
('电视', 1, 999.99, 50, 10),
('沙发', 2, 800.00, 30, 5),
('咖啡', 3, 20.00, 200, 40),
('小说', 4, 35.99, 150, 30),
('跑步机', 5, 1500.00, 80, 15);


-- 插入客户数据
INSERT INTO customers (customer_name, gender, phone_number, email, address) VALUES
('张三', '男', '13800138000', 'zhangsan@example.com', '客户地址1'),
('李四', '女', '13900139000', 'lisi@example.com', '客户地址2'),
('王五', '男', '13700137000', 'wangwu@example.com', '客户地址3'),
('赵六', '女', '13600136000', 'zhaoliu@example.com', '客户地址4'),
('孙七', '男', '13500135000', 'sunqi@example.com', '客户地址5');


-- 插入订单数据，注意使用已存在的客户ID
INSERT INTO orders (customer_id, order_date, total_price, order_status) VALUES
(1, '2023-01-01', 2020.99, '已完成'),
(2, '2023-01-02', 1020.00, '已发货'),
(3, '2023-01-03', 1600.00, '待处理'),
(4, '2023-01-04', 55.99, '已完成'),
(5, '2023-01-05', 1535.99, '已取消');


-- 插入订单明细数据，注意使用已存在的订单ID和商品ID
INSERT INTO order_details (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 2, 1999.99),
(2, 3, 1, 800.00),
(3, 2, 1, 999.99),
(4, 4, 2, 35.99),
(5, 5, 1, 1500.00),
(5, 6, 1, 35.99);


-- 插入采购订单数据，注意使用已存在的供应商ID
INSERT INTO purchase_orders (supplier_id, po_date, total_cost, status) VALUES
(1, '2023-01-06', 5000.00, '已完成'),
(2, '2023-01-07', 3000.00, '已接收'),
(3, '2023-01-08', 4500.00, '待处理'),
(4, '2023-01-09', 2200.00, '已完成'),
(5, '2023-01-10', 6000.00, '已取消');

遵循的数据库三范式

数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：

第一范式（1NF, First Normal Form）

定义：