drop database Mobile_phone_mall; create database Mobile_phone_mall; use Mobile_phone_mall; create table users( #用户表 uid int primary key auto_increment,#编号 uLoginName varchar(20) not null,#用户名 uLoginPassword varchar(256) not null,#密码 uEmail varchar(60) ,#email uName varchar(10) not null,#姓名 uSex varchar(5) not null,#性别 uTel varchar(11) ,#电话 uAddress varchar(100),#家庭住址 ustatus varchar(10) not null #状态 ); create table admins( aid int primary key auto_increment,#编号 aname varchar(10) not null,#用户名 apwd varchar(256) not null,#密码 atype varchar(50) not null,#管理员类型 astatus varchar(20) not null#状态 ); create table ProductType( #产品类型表 tid int primary key auto_increment,#编号 tname varchar(50) not null, timg varchar(50) ); create table Product( #产品表 pid varchar(20) primary key,#编号 pname varchar(256),#名称 pprice decimal(8,2),#价格 pnumber int,#库存数量 pimg varchar(256),#图片 ptime datetime,#发布时间 ptid int ,#品牌类型 foreign key(ptid) references ProductType(tid),#品牌类型 pstatus varchar(25)#状态 ); create table OrderIndormation( #订单信息表 oid varchar(20)primary key ,#订单编号 otime datetime not null,#下单日期 osuretime datetime,#确定订单日期 ouid int,#用户id foreign key(ouid) references users(uid),#用户id kd varchar(100), #快递类型 address varchar(100),#订单地址 omoney decimal(8,2),#订单总价 ostatus varchar(20),#订单状态 otype varchar(256)#备注 ); create table Orderdetails( #订单详细 dtid int primary key auto_increment,#编号 dtpid varchar(20) ,#产品编号 foreign key(dtpid) references Product(pid),#产品编号 dtnumber int ,#数量 dtoid varchar(20),#订单编号 foreign key(dtoid) references OrderIndormation(oid)#订单编号 ); insert into admins(aname,apwd,atype,astatus)values('韦邦杠','123456','高级管理','可用'); insert into admins(aname,apwd,atype,astatus)values('梁光健','123456','高级管理','可用'); insert into admins(aname,apwd,atype,astatus)values('欧乐乐','123456','普级管理','可用'); insert into admins(aname,apwd,atype,astatus)values('刘大仙','123456','普级管理','可用'); insert into admins(aname,apwd,atype,astatus)values('果果','123456','高级管理','可用'); select * from admins insert into ProductType(tname,timg) values('vivo','vivo.jpg'); insert into ProductType(tname,timg) values('360','360.jpg'); insert into ProductType(tname,timg) values('小米','xiaomi.jpg'); insert into ProductType(tname,timg) values('魅族','魅族.jpg'); insert into ProductType(tname,timg) values('华为','华为.jpg'); insert into ProductType(tname,timg) values('oppo','oppo.jpg'); insert into ProductType(tname,timg) values('iphone','iphone.jpg'); select * from ProductType insert into Product(pid,pname,pprice,pnumber,pimg,ptime,ptid,pstatus)values('46512334223','vivo x20',3998,3000,'vivo','2018-06-09',1,'上架中'); insert into Product(pid,pname,pprice,pnumber,pimg,ptime,ptid,pstatus)values('46512456456','小米 max2',1598,5000,'xiaommi','2015-07-09',3,'上架中'); insert into Product(pid,pname,pprice,pnumber,pimg,ptime,ptid,pstatus)values('465456456413','华为 x9',1998,3000,'vivo','2016-09-09',5,'上架中'); insert into Product(pid,pname,pprice,pnumber,pimg,ptime,ptid,pstatus)values('465124565463','360 mix2',998,300,'360','2014-09-09',9,'上架中'); insert into Product(pid,pname,pprice,pnumber,pimg,ptime,ptid,pstatus)values('4651245645513','苹果8',7998,500,'vivo','2018-09-18',7,'上架中'); insert into Product(pid,pname,pprice,pnumber,pimg,ptime,ptid,pstatus)values('465145645513','魅族 note6',1199,1000,'vivo','2017-09-09',4,'上架中'); select *from Product
