编辑代码

CREATE DATABASE ClothingManagementSystem;
USE ClothingManagementSystem;

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Phone VARCHAR(20) UNIQUE,
Address VARCHAR(100)
);
CREATE TABLE Merchants (
MerchantID INT PRIMARY KEY AUTO_INCREMENT,
ClothingID INT UNIQUE,
Size VARCHAR(10),
Color VARCHAR(20),
Price DECIMAL(10,2) NOT NULL,
Brand VARCHAR(50) NOT NULL,
Manufacturer VARCHAR(50)
);
CREATE TABLE Manufacturers (
InventoryID INT PRIMARY KEY AUTO_INCREMENT,
Quantity INT NOT NULL,
Location VARCHAR(100) NOT NULL
);
CREATE TABLE Platforms (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
OrderDate DATE NOT NULL,
OrderStatus VARCHAR(20) NOT NULL
);
CREATE TABLE CustomerMerchant (
CustomerID INT,
MerchantID INT,
PurchaseQuantity INT NOT NULL,
PRIMARY KEY (CustomerID,MerchantID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (MerchantID) REFERENCES Merchants(MerchantID)
);
CREATE TABLE MerchantManufacturer (
MerchantID INT,
InventoryID INT,
OrderQuantity INT NOT NULL,
PRIMARY KEY (MerchantID,InventoryID),
FOREIGN KEY (MerchantID) REFERENCES Merchants(MerchantID),
FOREIGN KEY (InventoryID) REFERENCES Manufacturers(InventoryID)
);
CREATE TABLE PlatformMerchant (
OrderID INT,
MerchantID INT,
PRIMARY KEY (OrderID,MerchantID),
FOREIGN KEY (OrderID) REFERENCES Platforms(OrderID),
FOREIGN KEY (MerchantID) REFERENCES Merchants(MerchantID)
);
CREATE TABLE CustomerPlatform (
CustomerID INT,
OrderID INT,
PRIMARY KEY (CustomerID,OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (OrderID) REFERENCES Platforms(OrderID)
);

INSERT INTO Customers VALUES ('刘一','18123456748','山西省忻州市');
INSERT INTO Customers VALUES ('陈二','18435762345','福建省福州市');
INSERT INTO Customers VALUES ('张三','18736482648','四川省成都市');
INSERT INTO Customers VALUES ('李四','13578972357','江西省赣州市');
INSERT INTO Customers VALUES ('王五','13456287638','黑龙江省双鸭山市');

SELECT * FROM Customers;
SELECT * FROM Customers WHERE Name = '张三';

UPDATE Customers SET Phone = '13800138002' WHERE CustomerID = 1;
DELETE FROM Customers WHERE CustomerID = 2;


INSERT INTO Merchants (ClothingID, Size, Color, Price, Brand, Manufacturer) 
VALUES (1,'M','红色',199.99,'耐克','耐克中国工厂'),
       (2,'L','蓝色',299.50,'阿迪达斯','阿迪达斯中国工厂');

SELECT * FROM Merchants;
SELECT * FROM Merchants WHERE Brand = '耐克';

UPDATE Merchants SET Price = 179.99 WHERE ClothingID = 1;
DELETE FROM Merchants WHERE ClothingID = 2;


INSERT INTO Manufacturers (Quantity, Location) 
VALUES (1,'北京仓库'),
       (2,'上海仓库');

SELECT * FROM Manufacturers;
SELECT * FROM Manufacturers WHERE Location = '北京仓库';

UPDATE Manufacturers SET Quantity = 3 WHERE InventoryID = 1;
DELETE FROM Manufacturers WHERE InventoryID = 2;


INSERT INTO Platforms (OrderDate, OrderStatus) 
VALUES ('2025-06-25', '已完成'),
       ('2025-06-30', '处理中');

SELECT * FROM Platforms;
SELECT * FROM Platforms WHERE OrderStatus = '已完成';

UPDATE Platforms SET OrderStatus = '已发货' WHERE OrderID = 1;
DELETE FROM Platforms WHERE OrderID = 2;


INSERT INTO CustomerMerchant (CustomerID, MerchantID, PurchaseQuantity) 
VALUES (1,1,2),
       (1,2,1);

SELECT c.Name, m.Brand, cm.PurchaseQuantity 
FROM Customers c
JOIN CustomerMerchant cm ON c.CustomerID = cm.CustomerID
JOIN Merchants m ON cm.MerchantID = m.MerchantID;

UPDATE CustomerMerchant SET PurchaseQuantity = 3 WHERE CustomerID = 1 AND MerchantID = 1;
DELETE FROM CustomerMerchant WHERE CustomerID = 1 AND MerchantID = 2;



ALTER TABLE Customers ADD Email VARCHAR(50);
ALTER TABLE Merchants MODIFY Price DECIMAL(10, 2) NOT NULL DEFAULT 0;
ALTER TABLE Manufacturers DROP COLUMN Location;

DROP TABLE IF EXISTS CustomerPlatform;
DROP DATABASE IF EXISTS ClothingManagementSystem;