【WY】MySQL 阶段二：实战案例 一：电商业务数据分析：商品信息

版权声明：本文为博主原创文章，未经博主允许不得转载。

---

---

1.1 E-R 图

1.2 创建表并导入数据

use test;

-- GoodsColor：创建表 ------------
create table Goods_Color(

	ColorID varchar(4) not null default '-',
	
	ColorNote varchar(20) not null default '-'
);

-- 导入数据 ------------
load data local infile 'D:/GoodsColor.txt'
into table Goods_Color
fields terminated by '\t'
ignore 1 lines;


-- GoodsSize：创建表------------
create table Goods_Size(

	SizeID varchar(4) not null default '-',

	SizeNote varchar(100) not null default '-'
);

-- 导入数据 ------------
load data local infile 'D:/GoodsSize.txt'
into table Goods_Size
fields terminated by '\t'
ignore 1 lines;


-- OrderDetail：创建表------------
create table Order_Detail(

	OrderID varchar(6) not null default '-',

	GoodsID varchar(6) not null default '-',

	GoodsPrice double not null default 0,

	ColorID varchar(4) not null default '-',

	SizeID varchar(4) not null default '-',

	Amount int not null default 0
);

-- 导入数据 ------------
load data local infile 'D:/OrderDetail.txt'
into table Order_Detail
fields terminated by '\t'
ignore 1 lines;


-- 查看表结构
select * from Goods_Color;
select * from Goods_Size;
select * from Order_Detail;

1.3 练习操作

-- 练习1：倒序查询卖的金额最多的产品
select GoodsID,SUM(GoodsPrice*Amount)
from Order_Detail 
group by GoodsID
order by SUM(GoodsPrice*Amount) desc;


-- 练习2. 查询不同尺码下的产品销售数量
select SizeNote, GoodsID,SUM(Amount)  
from Order_Detail left join Goods_Size on  Order_Detail.SizeID = Goods_Size.SizeID
group by SizeNote, GoodsID
order by SUM(Amount) desc;


-- 练习3. 查询不同颜色下的产品销售金额
select ColorNote as 颜色, GoodsID, SUM(GoodsPrice*Amount) as 销售额
from Order_Detail left join Goods_Color on Order_Detail.ColorID = Goods_Color.ColorID
group by 颜色, GoodsID
order by 颜色, 销售额 desc;


-- 练习4. 查询不同尺码下的不同颜色的产品销售金额
select SizeNote, ColorNote, GoodsID, sum(GoodsPrice * Amount) as 销售额
from Order_Detail left join Goods_Size on  Order_Detail.SizeID = Goods_Size.SizeID
left join Goods_Color on Order_Detail.ColorID = Goods_Color.ColorID
group by SizeNote, ColorNote, GoodsID
order by SizeNote, ColorNote, 销售额 desc;

---

版权声明：本文为博主原创文章，未经博主允许不得转载。