tmall_sql语句表

叁歲伎倆 2022-05-19 05:23 166阅读 0赞

CREATE TABLE category (  
id int identity(1,1) primary key,  
name varchar(255) null,  
)  
CREATE TABLE users (  
id int identity(1,1) primary key,  
name varchar(255) null,  
passwords varchar(255) null,  
)

\--依赖用户表  
CREATE TABLE order\_ (  
id int identity(1,1) primary key,  
orderCode varchar(255) NULL,  
useraddress varchar(255) DEFAULT NULL,  
post varchar(255) DEFAULT NULL,  
receiver varchar(255) DEFAULT NULL,  
mobile varchar(255) DEFAULT NULL,  
userMessage varchar(255) DEFAULT NULL,  
createDate datetime DEFAULT NULL,  
payDate datetime DEFAULT NULL,  
deliveryDate datetime DEFAULT NULL,  
confirmDate datetime DEFAULT NULL,  
uid int DEFAULT NULL,  
status varchar(255) DEFAULT NULL,  
CONSTRAINT fk\_order\_user FOREIGN KEY (uid) REFERENCES users (id)

)

\--依赖分类表  
CREATE TABLE product (  
id int identity(1,1) primary key,  
name varchar(255) DEFAULT NULL,  
subTitle varchar(255) DEFAULT NULL,  
orignalPrice float DEFAULT NULL,  
promotePrice float DEFAULT NULL,  
stock int DEFAULT NULL,  
cid int DEFAULT NULL,  
createDate datetime DEFAULT NULL,  
CONSTRAINT fk\_product\_category FOREIGN KEY (cid) REFERENCES category (id)  
)

\--依赖产品表  
CREATE TABLE productimage (  
id int identity(1,1) primary key,  
pid int DEFAULT NULL,  
type varchar(255) DEFAULT NULL,  
CONSTRAINT fk\_productimage\_product FOREIGN KEY (pid) REFERENCES product (id)

)

\--依赖分类表  
CREATE TABLE property (  
id int identity(1,1) primary key,  
cid int DEFAULT NULL,  
name varchar(255) DEFAULT NULL,  
CONSTRAINT fk\_property\_category FOREIGN KEY (cid) REFERENCES category (id)  
)

\--依赖属性表  
CREATE TABLE propertyvalue (  
id int identity(1,1) primary key,  
ptid int DEFAULT NULL,  
value varchar(255) DEFAULT NULL,  
CONSTRAINT fk\_propertyvalue\_property FOREIGN KEY (ptid) REFERENCES property (id)  
  
)

\--依赖用户和产品表  
CREATE TABLE review (  
id int identity(1,1) primary key,  
content varchar(4000) DEFAULT NULL,  
uid int DEFAULT NULL,  
pid int DEFAULT NULL,  
createDate datetime DEFAULT NULL,  
CONSTRAINT fk\_review\_product FOREIGN KEY (pid) REFERENCES product (id),  
CONSTRAINT fk\_review\_user FOREIGN KEY (uid) REFERENCES users (id)  
)  
\--依赖用户和产品表  
CREATE TABLE orderitem (  
id int identity(1,1) primary key,  
pid int DEFAULT NULL,  
oid int DEFAULT NULL,  
uid int DEFAULT NULL,  
number int DEFAULT NULL,  
CONSTRAINT fk\_orderitem\_user FOREIGN KEY (uid) REFERENCES users (id),  
CONSTRAINT fk\_orderitem\_product FOREIGN KEY (pid) REFERENCES product (id)  
)