Java基础入门day64

day64

web项目

数据库设计

在小米商城主页，主要的内容是多种商品类型的展示，分别有手机，智能穿戴，笔记本平板，家电，生活电器，厨房电器，智能家具等大的七个分类，根据这个设计我们可以分析一张商品类型表

create database mi DEFAULT CHARACTER SET utf8;
​
use mi;
​
create table types(tid int primary key auto_increment,tname varchar(20) not null unique,tdesc varchar(20) 
);
​
insert into types values(null, '手机', '手机描述');
​
insert into types values(null, '智能穿戴', '智能穿戴描述');
​
insert into types values(null, '笔记本平板', '笔记本平板描述');
​
insert into types values(null, '家电', '家电描述');
​
insert into types values(null, '生活电器', '生活电器描述');
​
insert into types values(null, '厨房电器', '厨房电器描述');
​
insert into types values(null, '智能家具', '智能家具描述');

在手机分类页面中，点开手机详情，可以得出手机数据字段的信息，有标题，二级标题，描述，手机与类型的关联字段tid

create table cellphone(cid int primary key auto_increment,title varchar(20) not null,subtitle varchar(20) not null,  wtype varchar(20),img varchar(50),cdesc varchar(50), oprice double,nprice double,tid int
);
​
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10','小米10青春版','小米','img/cellphone/1.jpg','小米10青春版',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('Xiaomi 14 Ultra','Xiaomi 14 Ultra','小米自营','img/cellphone/2.jpg','徕卡光学 Summilux 镜头，第三代骁龙®8移动平台',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/3.jpg','【直播间购机加赠多彩腕带保护壳】性能旋风，席卷而来',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/4.jpg','骁龙845处理器，AI变焦双摄，红外人脸解锁，AI变焦双摄，红外人脸解锁',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/5.jpg','骁龙845处理器，AI变焦双摄，红外人脸解锁，AI变焦双摄，红外人脸解锁',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/6.jpg','骁龙845处理器，AI变焦双摄，红外人脸解锁，AI变焦双摄，红外人脸解锁',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/7.jpg','骁龙845处理器，AI变焦双摄，红外人脸解锁，AI变焦双摄，红外人脸解锁',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/8.jpg','骁龙845处理器，AI变焦双摄，红外人脸解锁，AI变焦双摄，红外人脸解锁',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/9.jpg','骁龙845处理器，AI变焦双摄，红外人脸解锁，AI变焦双摄，红外人脸解锁',5999,4999,1);
insert into cellphone(title,subtitle,wtype,img,cdesc,oprice,nprice,tid) values('小米10 Pro','小米10 Pro','小米自营','img/cellphone/10.jpg','骁龙845处理器，AI变焦双摄，红外人脸解锁，AI变焦双摄，红外人脸解锁',5999,4999,1);

手机还有图片，关于图片，更好的设计是新增一张图片表，其中有一个字段与手机的cid关联

create table img(iid int primary key auto_increment,name varchar(20) not null,idesc varchar(20),cid int
);
​
insert into img values(null,'1.jpg','xiaomi 10',1);
insert into img values(null,'2.jpg','xiaomi 10',1);
insert into img values(null,'3.jpg','xiaomi 10',1);
insert into img values(null,'4.jpg','Xiaomi 14 Ultra',2);
insert into img values(null,'5.jpg','Xiaomi 14 Ultra',2);
insert into img values(null,'6.jpg','Xiaomi 14 Ultra',2);
insert into img values(null,'7.jpg','Xiaomi 14 Pro',3);
insert into img values(null,'8.jpg','Xiaomi 14 Pro',3);
insert into img values(null,'9.jpg','Xiaomi 14 Pro',3);
insert into img values(null,'10.jpg','Xiaomi 14',4);
insert into img values(null,'11.jpg','Xiaomi 14',4);
insert into img values(null,'12.jpg','Xiaomi 14',4);
insert into img values(null,'13.jpg','Xiaomi 14',4);
insert into img values(null,'14.jpg','Xiaomi 14',4);
insert into img values(null,'15.jpg','Xiaomi 14',5);

手机还有对应的规格表，ram和rom

create table ram(raid int primary key auto_increment,ram varchar(10),rdesc varchar(20),cid int
);
​
insert into ram(ram,rdesc,cid) values('4G','4G内存',1);
insert into ram(ram,rdesc,cid) values('8G','8G内存',1);
insert into ram(ram,rdesc,cid) values('16G','16G内存',1);
insert into ram(ram,rdesc,cid) values('32G','32G内存',1);
insert into ram(ram,rdesc,cid) values('32G','32G内存',2);
insert into ram(ram,rdesc,cid) values('64G','64G内存',2);
insert into ram(ram,rdesc,cid) values('4G','4G内存',3);
insert into ram(ram,rdesc,cid) values('8G','8G内存',3);
insert into ram(ram,rdesc,cid) values('12G','12G内存',3);
insert into ram(ram,rdesc,cid) values('16G','16G内存',3);
​
create table rom(roid int primary key auto_increment,rom varchar(10),rdesc varchar(20),cid int
);
​
insert into rom(rom,rdesc,cid) values('128GB','128GB存储',1);
insert into rom(rom,rdesc,cid) values('256GB','256GB存储',1);
insert into rom(rom,rdesc,cid) values('128GB','128GB存储',2);
insert into rom(rom,rdesc,cid) values('256GB','256GB存储',2);
insert into rom(rom,rdesc,cid) values('128GB','128GB存储',3);
insert into rom(rom,rdesc,cid) values('256GB','256GB存储',3);
insert into rom(rom,rdesc,cid) values('1T','1T存储',3);
insert into rom(rom,rdesc,cid) values('256GB','256GB存储',4);

手机还有颜色表

create table colors(coid int primary key auto_increment,coname varchar(20) not null,codesc varchar(20),cid int
);
​
insert into colors(coname,codesc,cid) values('镜瓷白','镜瓷白描述',1);
insert into colors(coname,codesc,cid) values('镜瓷灰','镜瓷灰描述',1);
insert into colors(coname,codesc,cid) values('墨晶','镜瓷黑描述',1);
insert into colors(coname,codesc,cid) values('龙晶蓝','龙晶蓝描述',2);
insert into colors(coname,codesc,cid) values('龙晶白','龙晶白描述',2);
insert into colors(coname,codesc,cid) values('墨晶','墨晶灰描述',2);
insert into colors(coname,codesc,cid) values('镜晶','镜晶灰描述',3);
insert into colors(coname,codesc,cid) values('冰钛','冰钛描述',3);
insert into colors(coname,codesc,cid) values('冰钛','冰钛描述',4);

目前只是设计了关于手机表的详细表结构设计，如果还有其他几大类的具体设计，也可以参考手机表的相关设计