期末测试

Inventory.sql

CREATE TABLE Inventory (
                           ProductCode VARCHAR(50) PRIMARY KEY,
                           StockQuantity INT,
                           ProductName VARCHAR(100),
                           Price DECIMAL(10, 2)
);
INSERT INTO Inventory (ProductCode, StockQuantity, ProductName, Price)
VALUES
    ('PRD001', 100, '产品A', 99.99),
    ('PRD002', 50, '产品B', 49.50),
    ('PRD003', 200, '产品C', 19.99),
    ('PRD004', 30, '产品D', 299.99),
    ('PRD005', 80, '产品E', 39.95);

product_production_cycle.sql

CREATE TABLE product_production_cycle (
                                          product_code VARCHAR(50) PRIMARY KEY, -- 产品代码，主键
                                          design_weeks INT NOT NULL, -- 设计阶段周数
                                          purchase_weeks INT NOT NULL, -- 原材料采购周数
                                          production_weeks INT NOT NULL, -- 生产阶段周数
                                          quality_weeks INT NOT NULL, -- 质量检验周数
                                          packaging_weeks INT NOT NULL, -- 包装阶段周数
                                          total_weeks INT NOT NULL -- 总生产周期周数
);
INSERT INTO product_production_cycle (product_code, design_weeks, purchase_weeks, production_weeks, quality_weeks, packaging_weeks, total_weeks) VALUES
                                                                                                                                                     ('PRD001', 2, 3, 4, 1, 1, 11),
                                                                                                                                                     ('PRD002', 3, 4, 5, 2, 2, 16),
                                                                                                                                                     ('PRD003', 1, 2, 3, 1, 1, 8);

production_work_order.sql

CREATE TABLE production_work_order (
                                       work_order_id INT PRIMARY KEY AUTO_INCREMENT, -- 生产工单编号，主键
                                       order_id INT , -- 关联销售订单表
                                       product_code VARCHAR(50) , -- 关联产品表
                                       work_order_date DATE , -- 生产工单下达日期
                                       actual_start_date DATE, -- 实际开始日期
                                       actual_completion_date DATE, -- 实际完成日期
                                       production_quantity INT NOT NULL, -- 计划生产数量
                                       status int DEFAULT 0, -- 生产工单状态
                                       remarks TEXT-- 备注
);

sales_order.sql

CREATE TABLE sales_order (
                             order_id INT PRIMARY KEY AUTO_INCREMENT,
                             customer_id VARCHAR(50) NOT NULL,
                             order_date DATE NOT NULL,
                             deliver_date DATE NOT NULL,
                             salesperson VARCHAR(50) ,
                             status int DEFAULT 0,
                             number int,
                             product_code VARCHAR(100)
);

user.sql

CREATE TABLE User
(
    UserID      VARCHAR(50) PRIMARY KEY ,
    Username    VARCHAR(50)  NOT NULL UNIQUE,
    Password    VARCHAR(100) NOT NULL, -- 密码建议加密存储
    Name        VARCHAR(50),
    Department  int ,
    Phone       VARCHAR(50)
);
INSERT INTO User (UserID, Username, Password, Name, Department, Phone)
VALUES
    ('ADM_001', 'admin', 'admin_password_encrypted', '张三', 0, '13800000001'),
    ('CLI_001', 'client1', 'client1_password', '李四', 1, '13800000002'),
    ('BUS_001', 'business1', 'business1_password', '王五', 2, '13800000003'),
    ('MAR_001', 'marketing1', 'marketing1_password', '赵六', 3, '13800000004');