编辑代码

CREATE DATABASE test;
USE test;
CREATE TABLE Part (
PSno INT PRIMARY KEY,
PName VARCHAR(55) NOT NULL,
PColor VARCHAR(25),
PWeight INT
);
INSERT INTO Part (PSno, PName, PColor, PWeight)
VALUES 
(1, 'Part A', 'Red', 10),
(2, 'Part B', 'Blue', 15),
(3, 'Part C', 'Green', 20);

CREATE TABLE Supplier (
S_Sno INT PRIMARY KEY,
SName VARCHAR(25) NOT NULL,
SCity VARCHAR(40) NOT NULL
);
INSERT INTO Supplier (S_Sno, SName, SCity)
VALUES 
(101, 'Supplier X', '上海'),
(102, 'Supplier Y', '北京'),
(103, 'Supplier Z', '广州');

CREATE TABLE Partsupp (
PS_Sno INT NOT NULL,
PSName VARCHAR(55),
PSCity VARCHAR(25),
FOREIGN KEY (PS_Sno) REFERENCES Supplier(S_Sno)
);
INSERT INTO Partsupp (PS_Sno, PSName, PSCity)
VALUES 
(101, 'Partsupp for X', '上海'),
(102, 'Partsupp for Y', '北京'),
(103, 'Partsupp for Z', '广州');

CREATE TABLE Customer (
CSno INT PRIMARY KEY,
CName VARCHAR(25) NOT NULL,
CCity VARCHAR(40) NOT NULL
);
INSERT INTO Customer (CSno, CName, CCity)
VALUES 
(201, 'Customer A', '深圳'),
(202, 'Customer B', '成都'),
(203, 'Customer C', '北京');


CREATE TABLE Nation (
NSno INT PRIMARY KEY,
NName CHAR(25) NOT NULL,
NSize VARCHAR(152) NOT NULL
);
INSERT INTO Nation (NSno, NName, NSize)
VALUES 
(301, 'Nation A', 'Large'),
(302, 'Nation B', 'Medium'),
(303, 'Nation C', 'Small');

-- 创建 Region 表
CREATE TABLE Region (
RSno INT PRIMARY KEY,
RName CHAR(25) NOT NULL,
RSize VARCHAR(152) NOT NULL
);
INSERT INTO Region (RSno, RName, RSize)
VALUES 
(401, 'Region A', 'Big'),
(402, 'Region B', 'Average'),
(403, 'Region C', 'Tiny');

CREATE TABLE Orders (
OSno INT PRIMARY KEY,
CUSSno INT NOT NULL,
OCount INT,
OSpends DECIMAL(10, 2),
FOREIGN KEY (CUSSno) REFERENCES Customer(CSno)
);
INSERT INTO Orders (OSno, CUSSno, OCount, OSpends)
VALUES 
(501, 201, 3, 100.00),
(502, 202, 2, 80.00),
(503, 201, 4, 120.00);

CREATE TABLE Lineitem (
LORDER INT NOT NULL,
LSUPPLY INT NOT NULL,
LTime VARCHAR(55) NOT NULL,
LSum INT NOT NULL,
PRIMARY KEY (LORDER, LSUPPLY),
FOREIGN KEY (LORDER) REFERENCES Orders(OSno),
FOREIGN KEY (LSUPPLY) REFERENCES Supplier(S_Sno)
);
INSERT INTO Lineitem (LORDER, LSUPPLY, LTime, LSum)
VALUES 
(501, 101, '2025-01-01', 30),
(502, 102, '2025-02-01', 40),
(503, 103, '2025-03-01', 50);