电商项目-图文Mysql一对多查询,多对多查询

---

前言

项目中遇到了关于数据库的问题,学习了一下,在此分享.可能表的设计也不够好,仅供参考

---

一、创建表

//创建主表手机
create table phone(
id int primary key auto_increment,
pname varchar(20),
ptype varchar(20)
)

//创建主表用户
create table user (
id int primary key auto_increment,
userName varchar(20),
userPwd int(11),
)

//创建从表手机颜色
create table pcolor(
id int primary key auto_increment,
pcolor varchar(10),
pphoneid int,
foreign key(pphoneid) references phone(id)
on delete cascade
on update cascade
)

//创建从表手机图片
create table pimg(
id int primary key auto_increment,
pimg varchar(20),
pphoneid int,
foreign key(pphoneid) references phone(id)
on delete cascade
on update cascade
)

//创建从表手机价格
create table pprice(
id int primary key auto_increment,
pprice varchar(10),
pphoneid int,
foreign key(pphoneid) references phone(id)
on delete cascade
on update cascade
)

//创建从表手机内存
create table pstorage(
id int primary key,
pstorage varchar(10),
pphoneid int,
foreign key(pphoneid) references phone(id)
)

//创建关系表内存和手机关联
create table storage2phone(
id int primary key auto_increment,
pstorageid int,
pphoneid int,
foreign key(pphoneid) references phone(id),
foreign key(pstorageid) references pstorage(id)
)

//创建关系表用户与手机关联
create table user2phone(
id int primary key auto_increment,
userid int,
phoneid int,
pcolorid int,
pstorageid int,
ppriceid int,
pimgid int,
pnumid int,
foreign key(userid) references users(id)
on delete cascade
on update cascade,
foreign key(phoneid) references phone(id)
on delete cascade
on update cascade,
foreign key(pcolorid) references pcolor(id)
on delete cascade
on update cascade,
foreign key(pstorageid) references pstorage(id)
on delete cascade
on update cascade,
foreign key(ppriceid) references ppriceid(id)
on delete cascade
on update cascade,
foreign key(pimgid) references pimg(id)
on delete cascade
on update cascade
)

二、添加数据

//添加手机数据
insert into phone values(null,"HUAWEI Mate系列","HUAWEI Mate 40 Pro","5G全网通"),
(null,"HUAWEI P系列","HUAWEI P40 Pro","5G全网通"),
(null,"HUAWEI nova系列","HUAWEI nova 8","5G全网通"),
(null,"荣耀 V系列","荣耀V30 PRO","5G全网通")

//添加手机颜色
insert into pcolor values(null,"亮黑色",1)
insert into pcolor values(null,"亮黑色",1),(null,"釉白色",1),(null,"秘银色",1),(null,"夏日胡杨",1),(null,"秋日胡杨",1)
insert into pcolor values(null,"冰霜银",2),(null,"零度白",2),(null,"深渊蓝",2),(null,"深渊蓝",2),(null,"晨曦金",2)
insert into pcolor values(null,"亮黑色",3),(null,"普罗旺斯",3),(null,"8号色",3),(null,"绮境森林",3)
insert into pcolor values(null,"冰岛幻境",4),(null,"幻夜星河",4),(null,"魅海星蓝",4)

//添加手机内存
insert into pstorage values(null,"8GB+128GB"),(null,"8GB+256GB"),(null,"8GB+512GB")

//添加手机价格
insert into pprice values(null,"5988",2),(null,"6488",2),(null,"7388",2)
insert into pprice values(null,"6499",1),(null,"6999",1),(null,"7999",1)
insert into pprice values(null,"3299",3),(null,"3699",3)
insert into pprice values(null,"3899",4),(null,"4199",4)

//添加手机图片
insert into storage2phone values(null,1,1),(null,2,1),(null,3,1),(null,1,2),(null,2,2),(null,3,2),(null,3,2),(null,1,3),(null,2,3),(null,1,4),(null,2,4)
insert into pimg values(null,"upload/p40pro5g.png",2),(null,"upload/p40pro5g2.png",2),(null,"upload/p40pro5g3.png",2),
(null,"upload/p40pro5g4.png",2),(null,"upload/p40pro5g5.png",2),(null,"upload/p40pro5g6.png",2),(null,"upload/p40pro5g7.png",2)
insert into pimg values(null,"upload/mate40p1.png",1),(null,"upload/mate40p2.png",1),(null,"upload/mate40p3.png",1),(null,"upload/mate40p4.png",1),
(null,"upload/mate40p5.png",1),(null,"upload/mate40p6.png",1),(null,"upload/mate40p7.png",1)
insert into pimg values(null,"upload/nova8p1.png",3),(null,"upload/nova8p2.png",3),(null,"upload/nova8p3.png",3),(null,"upload/nova8p4.png",3),
(null,"upload/nova8p5.png",3),(null,"upload/nova8p6.png",3),(null,"upload/nova8p7.png",3)
insert into pimg values(null,"upload/honor30p1.png",4),(null,"upload/honor30p2.png",4),(null,"upload/honor30p3.png",4),(null,"upload/honor30p4.png",4),
(null,"upload/honor30p5.png",4),(null,"upload/honor30p6.png",4),(null,"upload/honor30p7.png",4)

手机与内存的关系

//添加用户与手机数据
insert into user2phone values (null,1,1,1,1,1,1,1)
insert into user2phone values (null,1,2,1,1,1,1,1)

添加用户表

三、查询语句

1.单项查询

//查询手机表里id是1的数据
select * from phone where id=1

2.一对多查询

一个手机有多种颜色，多张图片，多个价格

//查询从表里手机id与主表里手机主键id相同的行，且当主表里id是1的数据 
select * from pimg left join phone on pimg.pphoneid = phone.id where phone.id = 1
select * from pcolor left join phone on pcolor.pphoneid = phone.id where phone.id = 1
select * from pprice left join phone on pprice.pphoneid = phone.id where phone.id = 1

3.多对多查询

• 1.一个手机有多个内存版本，一个内存版本也有多款手机

select * from storage2phone sp left join pstorage on sp.pstorageid = pstorage.id left join phone on sp.pphoneid = phone.id where phone.id = 1

• 2.一个用户可以买多个手机，一个手机也可以被多个用户购买

select * from user2phone sp left join phone on sp.phoneid = phone.id 
left join pcolor on sp.pcolorid = pcolor.id
left join pstorage on sp.pstorageid = pstorage.id 
left join pprice on sp.ppriceid = pprice.id
left join pimg on sp.pimgid = pimg.id
where userid = 1