mysql数据库设计以及sql语句

123213

1、数据库的关系表 2、数据库的SQL语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

); 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),

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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.普通用户

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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) );

1 2 3 4 5 6 7 8 9 10 11

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)

1 2 3

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;