CREATE DATABASE shop_db;
USE  shop_db;
CREATE TABLE goods(
goods_id int PRIMARY KEY  ,
goods_name VARCHAR(32) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) not NULL DEFAULT 0,
category int not NULL DEFAULT 0,
provider VARCHAR(32) NOT NULL DEFAULT '',
CHECK(unitprice>1.0 and unitprice<9999.99 )
);
CREATE TABLE customer(
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
address VARCHAR(32) NOT NULL DEFAULT '' ,
email VARCHAR(32) UNIQUE,
sex ENUM('男','女'),
card_id CHAR(18)
);
CREATE table purchase(
                oder_id INT PRIMARY KEY  ,
                customer_id CHAR(8) NOT NULL DEFAULT '',
                goods_id INT NOT NULL DEFAULT 0,
                nums INT  NOT NULL DEFAULT 0,
                FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
                FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
       );
drop TABLE purchase;