您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 基礎教學標準樣本數據：Customers、Orders、Products、Vendors 完整建表與測試數據腳本

編輯：MySQL 綜合教學

SQL 基礎教學標準樣本數據：Customers、Orders、Products、Vendors 完整建表與測試數據腳本

經典五表模型，適用於 SQL 入門教學、工具測試、ORM 框架驗證、報表開發練習。標準鍵約束、外鍵關係、多樣化數據類型。

DDL：建表與約束

-- 客戶表
CREATE TABLE Customers (
    cust_id      CHAR(10)  NOT NULL PRIMARY KEY,
    cust_name    CHAR(50)  NOT NULL,
    cust_address CHAR(50)  NULL,
    cust_city    CHAR(50)  NULL,
    cust_state   CHAR(5)   NULL,
    cust_zip     CHAR(10)  NULL,
    cust_country CHAR(50)  NULL,
    cust_contact CHAR(50)  NULL,
    cust_email   CHAR(255) NULL
);

-- 訂單項表
CREATE TABLE OrderItems (
    order_num    INT       NOT NULL,
    order_item   INT       NOT NULL,
    prod_id      CHAR(10)  NOT NULL,
    quantity     INT       NOT NULL,
    item_price   DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (order_num, order_item)
);

-- 訂單表
CREATE TABLE Orders (
    order_num  INT         NOT NULL PRIMARY KEY,
    order_date DATETIME    NOT NULL,
    cust_id    CHAR(10)    NOT NULL
);

-- 產品表
CREATE TABLE Products (
    prod_id    CHAR(10) NOT NULL PRIMARY KEY,
    vend_id    CHAR(10) NOT NULL,
    prod_name  CHAR(255) NOT NULL,
    prod_price DECIMAL(8,2) NOT NULL,
    prod_desc  TEXT NULL
);

-- 供應商表
CREATE TABLE Vendors (
    vend_id      CHAR(10) NOT NULL PRIMARY KEY,
    vend_name    CHAR(50) NOT NULL,
    vend_address CHAR(50) NULL,
    vend_city    CHAR(50) NULL,
    vend_state   CHAR(5)  NULL,
    vend_zip     CHAR(10) NULL,
    vend_country CHAR(50) NULL
);

-- 外鍵約束
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders   FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id)   REFERENCES Products (prod_id);
ALTER TABLE Orders     ADD CONSTRAINT FK_Orders_Customers    FOREIGN KEY (cust_id)   REFERENCES Customers (cust_id);
ALTER TABLE Products   ADD CONSTRAINT FK_Products_Vendors    FOREIGN KEY (vend_id)   REFERENCES Vendors (vend_id);

DML：標準測試數據（含 NULL、多國、不同價格）

-- Customers
INSERT INTO Customers VALUES('1000000001','Village Toys','200 Maple Lane','Detroit','MI','44444','USA','John Smith','sales@villagetoys.com');
INSERT INTO Customers VALUES('1000000002','Kids Place','333 South Lake Drive','Columbus','OH','43333','USA','Michelle Green',NULL);
INSERT INTO Customers VALUES('1000000003','Fun4All','1 Sunny Place','Muncie','IN','42222','USA','Jim Jones','jjones@fun4all.com');
INSERT INTO Customers VALUES('1000000004','Fun4All','829 Riverside Drive','Phoenix','AZ','88888','USA','Denise L. Stephens','dstephens@fun4all.com');
INSERT INTO Customers VALUES('1000000005','The Toy Store','4545 53rd Street','Chicago','IL','54545','USA','Kim Howard',NULL);

-- Vendors
INSERT INTO Vendors VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444','USA');
INSERT INTO Vendors VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333','USA');
INSERT INTO Vendors VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999','USA');
INSERT INTO Vendors VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111','USA');
INSERT INTO Vendors VALUES('FNG01','Fun and Games','42 Galaxy Road','London',NULL,'N16 6PS','England');
INSERT INTO Vendors VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris',NULL,'45678','France');

-- Products
INSERT INTO Products VALUES('BR01','BRS01','8 inch teddy bear',5.99,'8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products VALUES('BR02','BRS01','12 inch teddy bear',8.99,'12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products VALUES('BR03','BRS01','18 inch teddy bear',11.99,'18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products VALUES('BNBG01','DLL01','Fish bean bag toy',3.49,'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products VALUES('BNBG02','DLL01','Bird bean bag toy',3.49,'Bird bean bag toy, eggs are not included');
INSERT INTO Products VALUES('BNBG03','DLL01','Rabbit bean bag toy',3.49,'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products VALUES('RGAN01','DLL01','Raggedy Ann',4.99,'18 inch Raggedy Ann doll');
INSERT INTO Products VALUES('RYL01','FNG01','King doll',9.49,'12 inch king doll with royal garments and crown');
INSERT INTO Products VALUES('RYL02','FNG01','Queen doll',9.49,'12 inch queen doll with royal garments and crown');

-- Orders
INSERT INTO Orders VALUES(20005,'2012-05-01','1000000001');
INSERT INTO Orders VALUES(20006,'2012-01-12','1000000003');
INSERT INTO Orders VALUES(20007,'2012-01-30','1000000004');
INSERT INTO Orders VALUES(20008,'2012-02-03','1000000005');
INSERT INTO Orders VALUES(20009,'2012-02-08','1000000001');

-- OrderItems
INSERT INTO OrderItems VALUES(20005,1,'BR01',100,5.49);
INSERT INTO OrderItems VALUES(20005,2,'BR03',100,10.99);
INSERT INTO OrderItems VALUES(20006,1,'BR01',20,5.99);
INSERT INTO OrderItems VALUES(20006,2,'BR02',10,8.99);
INSERT INTO OrderItems VALUES(20006,3,'BR03',10,11.99);
INSERT INTO OrderItems VALUES(20007,1,'BR03',50,11.49);
INSERT INTO OrderItems VALUES(20007,2,'BNBG01',100,2.99);
INSERT INTO OrderItems VALUES(20007,3,'BNBG02',100,2.99);
INSERT INTO OrderItems VALUES(20007,4,'BNBG03',100,2.99);
INSERT INTO OrderItems VALUES(20007,5,'RGAN01',50,4.49);
INSERT INTO OrderItems VALUES(20008,1,'RGAN01',5,4.99);
INSERT INTO OrderItems VALUES(20008,2,'BR03',5,11.99);
INSERT INTO OrderItems VALUES(20008,3,'BNBG01',10,3.49);
INSERT INTO OrderItems VALUES(20008,4,'BNBG02',10,3.49);
INSERT INTO OrderItems VALUES(20008,5,'BNBG03',10,3.49);
INSERT INTO OrderItems VALUES(20009,1,'BNBG01',250,2.49);
INSERT INTO OrderItems VALUES(20009,2,'BNBG02',250,2.49);
INSERT INTO OrderItems VALUES(20009,3,'BNBG03',250,2.49);

關係圖解

Customers 1 ──< Orders >──< OrderItems >──< Products >──< Vendors
   │                                                         │
   └─────────────── 查詢客戶購買的產品供應商 ──────────────┘