drop table if exists commodity_evaluation; drop table if exists consignee_management; drop table if exists goods_info; drop table if exists order_detail; drop table if exists order_management; drop table if exists user_info; create table commodity_evaluation ( commodity_id int not null auto_increment, goods_id int, user_id int comment '1.普通用户 2.管理员', commodity_memo varchar(2000), primary key (commodity_id)
); create table consignee_management ( consignee_id int not null auto_increment, user_id int comment '1.普通用户 2.管理员', consignee_name varchar(50), consignee_address varchar(1000), consignee_code varchar(10), consignee_phone varchar(11), primary key (consignee_id) ); create table goods_info ( goods_id int not null auto_increment, goods_name varchar(200), goods_price double, goods_url varchar(1000), goods_desc varchar(2000),
goods_type varchar(50) comment 'comment 1 上架 2 下架', primary key (goods_id) ); create table order_detail ( order_id int not null, goods_id int, order_number int, order_price double, primary key (order_id) ); create table order_management ( order_id int not null auto_increment, consignee_id int, user_id int comment '1.普通用户
2.管理员', order_time datetime, order_total double, order_type varchar(10), primary key (order_id) ); create table user_info ( user_id int not null auto_increment comment '1.普通用户 2.管理员', user_name varchar(30), user_sex varchar(10), user_phone varchar(11), user_pw varchar(50), user_type varchar(10), primary key (user_id) );
alter table commodity_evaluation add constraint FK_Reference_6 foreign key (goods_id) references goods_info (goods_id) on delete restrict on update restrict; alter table commodity_evaluation add constraint FK_Reference_7 foreign key (user_id) references user_info (user_id) on delete restrict on update restrict; alter table consignee_management add constraint FK_Reference_1 foreign key (user_id) references user_info (user_id) on delete restrict on update restrict; alter table order_detail add constraint FK_Reference_4 foreign key (order_id) references order_management (order_id) on delete restrict on update restrict; alter table order_detail add constraint FK_Reference_5 foreign key (goods_id) references goods_info (goods_id) on delete restrict on update restrict; alter table order_management add constraint FK_Reference_2 foreign key (consignee_id)
references consignee_management (consignee_id) on delete restrict on update restrict; alter table order_management add constraint FK_Reference_3 foreign key (user_id) references user_info (user_id) on delete restrict on update restrict;