文末有关系模型
/*==============================================================*/
/* Table: Y_address */
/*==============================================================*/
create table Y_address
(
uid int not null comment '用户ID',
aid int not null comment '地址编号',
aaddress varchar(1000) not null comment '收获地址',
aperson varchar(30) not null comment '收货人',
aphone bigint not null comment '收货人电话',
primary key (uid, aid)
);
alter table Y_address comment '收货地址管理';
/*==============================================================*/
/* Table: Y_buyed */
/*==============================================================*/
create table Y_buyed
(
bid int not null auto_increment comment '购买ID',
uid int not null comment '用户ID',
sid int not null comment '商品ID',
bnum int not null default 1 comment '购买数量(默认为1)',
saimg01 varbinary(100) comment '晒单图01',
saimg02 varbinary(100) comment '晒单图02',
saimg03 varbinary(100) comment '晒单图03',
btime datetime not null comment '购买时间',
primary key (bid)
);
alter table Y_buyed comment '全价购买表
';
/*==============================================================*/
/* Table: Y_buyedappraise */
/*==============================================================*/
create table Y_buyedappraise
(
bpid int not null auto_increment comment '评论ID',
uid int not null comment '用户ID',
sid int not null comment '商品ID',
bpcontent varchar(1000) not null comment '评论内容',
bptime datetime not null comment '评论时间',
primary key (bpid)
);
alter table Y_buyedappraise comment '全价商品购买评价';
/*==============================================================*/
/* Table: Y_duobao */
/*==============================================================*/
create table Y_duobao
(
dduonum bigint not null auto_increment comment '夺宝号码(‘前台使用时在次号码前拼接100或1000’)',
uid int not null comment '用户id',
sid int not null comment '商品id',
dlasttime datetime not null comment '最后一次夺宝时间',
dstaic tinyint not null default 0,
primary key (dduonum)
);
alter table Y_duobao comment '夺宝记录表';
/*==============================================================*/
/* Table: Y_rechare */
/*==============================================================*/
create table Y_rechare
(
rid int not null auto_increment comment '充值ID',
uid int not null comment '用户ID',
rmoney int not null comment '充值金额',
rtime datetime not null comment '充值时间',
primary key (rid)
);
alter table Y_rechare comment '用户充值';
/*==============================================================*/
/* Table: Y_shop */
/*==============================================================*/
create table Y_shop
(
sid int not null auto_increment comment '商品ID
',
sname varchar(100) not null comment '商品名',
svid int not null comment '商品种类ID',
sprice int not null comment '商品总价格(最小单位/元)',
ssingleprice tinyint default 1 comment '夺宝单价(1元/人;10元/人,默认是一元夺宝)',
smainimg varchar(100) not null comment '正面商品图',
sjieshaoimg01 varchar(100) not null comment '商品介绍图第一张',
sjieshaoimg02 varchar(100) not null comment '商品介绍图第二张',
sjieshaoimg03 varchar(100) comment '商品介绍图第三张',
sjieshaoimg04 varchar(100) comment '商品介绍图第四张',
sjieshao varchar(300) comment '商品介绍',
ssave int not null default 200 comment '商品库存(默认200)',
sstaic tinyint not null default 1 comment '是否下架(默认为1,未下架)',
ssavetime datetime not null comment '入库时间',
primary key (sid)
);
alter table Y_shop comment '商品信息表';
/*==============================================================*/
/* Table: Y_shopappraise */
/*==============================================================*/
create table Y_shopappraise
(
pid int not null auto_increment comment '评论编号',
luckynum int not null comment '幸运号码',
pcontent varchar(1000) comment '评价',
saimg01 varbinary(100) comment '晒单图01',
saimg02 varbinary(100) comment '晒单图02',
saimg03 varbinary(100) comment '晒单图03',
ptime datetime not null comment '评价时间',
primary key (pid)
);
alter table Y_shopappraise comment '商品评价
';
/*==============================================================*/
/* Table: Y_shoppingcar */
/*==============================================================*/
create table Y_shoppingcar
(
uid int not null comment '用户ID',
sid int not null comment '商品ID',
snum int not null default 1 comment '购买数量(默认为1)',
sctime datetime not null comment '加入购物车时间',
primary key (uid, sid)
);
alter table Y_shoppingcar comment '购物车(仅针对全价购买商品,夺宝清单参照duobao表)';
/*==============================================================*/
/* Table: Y_shopvariety */
/*==============================================================*/
create table Y_shopvariety
(
svid int not null comment '商品种类ID',
svmiaoshu varchar(60) not null comment '种类描述',
svnote varchar(600) comment '备注',
primary key (svid)
);
alter table Y_shopvariety comment '商品种类
';
/*==============================================================*/
/* Table: Y_showbuyed */
/*==============================================================*/
create table Y_showbuyed
(
luckynum int not null comment '幸运号',
swtime datetime not null comment '夺宝成功的时间(或者说是揭晓时间)',
primary key (luckynum)
);
alter table Y_showbuyed comment '晒单(夺宝成功商品)';
/*==============================================================*/
/* Table: Y_user */
/*==============================================================*/
create table Y_user
(
uid int not null auto_increment comment '用户ID',
uname varchar(30) comment '用户的真实姓名',
ushowname varchar(200) comment '用户昵称',
uIDnumber varchar(18) comment '身份证号码',
uemail varchar(100) not null comment '用户邮箱',
uphone bigint not null comment '手机号码',
upassword varchar(32) not null comment '用户密码',
utouxiang varchar(100) not null comment '用户头像',
umoney bigint not null default 100 comment '用户初始余额(100元)',
ucoupon int default 5 comment '用户优惠券(抵用现金/元,初始为5元)',
ubaoshi int default 20 comment '用户宝石(用于兑换商品/个,初始为20个)',
uregtiem datetime not null comment '用户注册时间',
ustaic tinyint not null default 1 comment '用户当前状态(默认为“1”,有效用户)',
primary key (uid),
unique key AK_Key_2 (uIDnumber),
unique key AK_Key_3 (uphone),
unique key AK_Key_4 (uemail)
);
alter table Y_user comment '用户表
';
alter table Y_address add constraint FK_AU foreign key (uid)
references Y_user (uid) on delete restrict on update restrict;
alter table Y_buyed add constraint FK_BS foreign key (sid)
references Y_shop (sid) on delete restrict on update restrict;
alter table Y_buyed add constraint FK_BU foreign key (uid)
references Y_user (uid) on delete restrict on update restrict;
alter table Y_buyedappraise add constraint FK_BP_S foreign key (sid)
references Y_shop (sid) on delete restrict on update restrict;
alter table Y_buyedappraise add constraint FK_BP_U foreign key (uid)
references Y_user (uid) on delete restrict on update restrict;
alter table Y_duobao add constraint FK_Lucky_S foreign key (sid)
references Y_shop (sid) on delete restrict on update restrict;
alter table Y_duobao add constraint FK_Lucky_U foreign key (uid)
references Y_user (uid) on delete restrict on update restrict;
alter table Y_rechare add constraint FK_RU foreign key (uid)
references Y_user (uid) on delete restrict on update restrict;
alter table Y_shop add constraint FK_SV foreign key (svid)
references Y_shopvariety (svid) on delete restrict on update restrict;
alter table Y_shopappraise add constraint FK_PD foreign key (luckynum)
references Y_duobao (dduonum) on delete restrict on update restrict;
alter table Y_shoppingcar add constraint FK_CS foreign key (sid)
references Y_shop (sid) on delete restrict on update restrict;
alter table Y_shoppingcar add constraint FK_UC foreign key (uid)
references Y_user (uid) on delete restrict on update restrict;
alter table Y_showbuyed add constraint FK_Lucky_D foreign key (luckynum)
references Y_duobao (dduonum) on delete restrict on update restrict;
##更改:建立期号表 create table Y_qihao ( qnum int comment '期号', sid int comment '商品ID', qtime datetime comment '该期开始时间', qlasttime datetime comment '该期结束时间', primary key (qnum,qtime) );
alter table y_duobao add constraint FK_DQ foreign key (dqihao) references Y_qihao (qnum) on delete restrict on update restrict; ALTER TABLE y_qihao ADD CONSTRAINT FK_QS FOREIGN KEY (sid) REFERENCES Y_shop (sid) ON DELETE RESTRICT ON UPDATE RESTRICT;
关系模型图: