编辑代码

CREATE DATABASE test2 CHARSET=utf8;
use test2;

CREATE TABLE t_suppliers(
SupplierID CHAR(6) PRIMARY  KEY,
Name VARCHAR(32),
CONSTRAINT t_suppliers_chk_1
CHECK (CHAR_LENGTH(SupplierID)=6)

) ENGINE=InnoDB;

CREATE TABLE t_goods(
GoodID INT PRIMARY KEY AUTO_INCREMENT,
Names VARCHAR(32),
Price DECIMAL(9,2),
SupplierID CHAR(6),
Addr VARCHAR(64)
CONSTRAINT supplies_goods_Scode
FOREIGN KEY (SupplierID)
REFERENCES t_suppliers(SupplierID)
ON DELETE CASCADE) ENGINE=InnoDB;

CREATE TABLE t_orders(
OrderID INT PRIMARY  KEY AUTO_INCREMENT,
OrderCount INT,
TotalCost DECIMAL(9,2),
GoodID INT,
CONSTRAINT order_goods_Gid
FOREIGN KEY (GoodID)
REFERENCES t_goods(GoodID)
ON DELETE CASCADE,
CONSTRAINT t_orders_chk_1
CHECK (OrderCount>=1 AND OrderCount<=20)
) ENGINE=InnoDB;

INSERT INTO  t_suppliers VALUES('a00001','a1'),('a00002','a2'),('a00003','a3');

INSERT INTO  t_goods VALUES(0,'g001',99.8,'a00001','sz'),(0,'g002',199.8,'a00002','js'),(0,'g003',9.8,'a00003','nj'),(0,'g004',9.8,'a00001','sh');

INSERT INTO  t_orders VALUES(0,2,9999.99,1),(0,20,88.99,2),
(0,2,666.99,1);

UPDATE t_goods SET Price=Price*0.9 WHERE Names='g001';
UPDATE t_orders SET OrderCount=10 WHERE OrderID=1;

SELECT t_suppliers.SupplierID,t_suppliers.Name FROM t_suppliers,t_goods WHERE t_goods.SupplierID=t_suppliers.SupplierID AND  t_goods.Names='g001';

DELETE FROM t_goods WHERE GoodID=3;