【一周入门MySQL—5】

数据库实例应用

【电商数据处理案例】

目标需求：将某电商脱敏后数据导入数据库进行加工处理，使用加工好的数据分析业务问题数据获取

• 客户相关：UserInfo.csv:用户主表、RegionInfo.csv:区域表、UserAddress.csv:用户地址表
• 商品相关：GoodsInfo.csv:商品主表、GoodsBrand.csv:商品品牌表、GoodsColor.csv:商品颜色表、GoodsSize.csv:商品尺码
• 订单相关文件：OrderInfo.csv:订单主表、OrderDetail.csv:订单详情表

SQL数据处理：

数据清洗→数据筛选→数据透视→数据排序

表结构一览：

表之间的关联关系：

数据准备：

新建数据库

create database ds;use ds;

建表并导入数据

--  UserInfo tablecreate table userinfo(userid varchar(6) not null default '-',username varchar(20) not null default '-',userpassword varchar(100) not null default '-',   sex int not null default 0,usermoney int not null default 0,frozenmoney int not null default 0,addressid varchar(20) not null default '-',regtime varchar(20) not null default '-',lastlogin varchar(20) not null default '-',lasttime date not null);#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/UserInfo.csv"into table userinfofields terminated by ','ignore 1 lines;-- regioninfocreate table regioninfo(regionid varchar(4) not null default '-',parentid varchar(4) not null default '-',regionname varchar(20) not null default '-',   regiontype int not null default 0,agencyid int not null default 0,pt varchar(11) not null default '-');#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/RegionInfo.csv"into table regioninfofields terminated by ','ignore 1 lines;-- UserAddresscreate table useraddress(addressid varchar(5) not null default '-',userid varchar(6) not null default '-',  consignee varchar(50) not null default '-',country varchar(1) not null default '-',province varchar(2) not null default '-',city varchar(4) not null default '-',district varchar(4) not null default '-', address varchar(200) not null default '-',pt varchar(11) not null default '-');#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/UserAddress.csv"into table useraddressfields terminated by ','ignore 1 lines;-- GoodsInfocreate table goodsinfo(goodsid varchar(6) not null default '-',typeid varchar(3) not null default '-',markid varchar(4) not null default '-',goodstag varchar(100) not null default '-',brandtag varchar(100) not null default '-',customtag varchar(100) not null default '-',goodsname varchar(100) not null default '-',clickcount int not null default 0,clickcr int not null default 0,goodsnumber int not null default 0,goodsweight int not null default 0,marketprice double not null default 0,shopprice double not null default 0,addtime varchar(20) not null default 0,isonsale int not null default 0,sales int not null default 0,realsales int not null default 0,extraprice double not null default 0,goodsno varchar(10) not null default '-');#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/goodsinfo.csv"into table goodsinfofields terminated by ','ignore 1 lines;-- GoodsBrandcreate table goodsbrand(SupplierID varchar(4) not null default '-',BrandType varchar(100) not null default '-',pt varchar(11) not null default '-');#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/GoodsBrand.csv"into table goodsbrandfields terminated by ','ignore 1 lines;-- GoodsColorcreate table goodscolor(ColorID varchar(4) not null default '-',ColorNote varchar(20) not null default '-',ColorSort int not null default 0,   pt varchar(11) not null default '-');#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/GoodsColor.csv"into table goodscolorfields terminated by ','ignore 1 lines;-- GoodsSizecreate table goodssize(SizeID varchar(4) not null default '-',SizeNote varchar(100) not null default '-',SizeSort int not null default 0,   pt varchar(11) not null default '-');#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/GoodsSize.csv"into table goodssizefields terminated by ','ignore 1 lines;-- OrderInfocreate table OrderInfo(OrderID varchar(6) not null default '-',UserID varchar(10) not null default '-',OrderState int not null default 0,PayState int not null default 0,AllotStatus int not null default 0,Consignee varchar(100) not null default '-',Country int not null default 0,Province int not null default 0,City int not null default 0,District int not null default 0,Address varchar(100) not null default '-',GoodsAmount double not null default 0,OrderAmount double not null default 0,ShippingFee int not null default 0,RealShippingFee int not null default 0,PayTool int not null default 0,IsBalancePay int not null default 0,BalancePay double not null default 0,OtherPay double not null default 0,PayTime varchar(20),AddTime varchar(20) not null default '-');#导入数据load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/orderinfo.csv"into table OrderInfofields terminated by ','ignore 1 lines;-- OrderDetailcreate table OrderDetail(RecID varchar(7) not null default '-',OrderID varchar(6) not null default '-',UserID varchar(6) not null default '-',SpecialID 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 infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/OrderDetail.csv"into table OrderDetailfields terminated by ','ignore 1 lines;-- 查询导入表的行数select count(*) from userinfo; -- 1000select count(*) from RegionInfo; -- 3415select count(*) from useraddress; -- 10000select count(*) from goodsinfo; -- 10000select count(*) from goodsbrand; -- 64select count(*) from goodscolor; -- 2641select count(*) from goodssize; -- 289select count(*) from orderinfo; -- 3711select count(*) from orderdetail; -- 10000

实践题

表数据调整

用户信息表

select * from userinfo;-- 时间戳转换为标准的日期时间型格式set sql_safe_updates=0; -- 设置数据库的安全权限update userinfo set regtime = from_unixtime(regtime);-- 执行报错 ：Error Code: 1406. Data too long for column 'regtime'-- 因为日期时间型不能直接放到文本格式内alter table userinfo modify regtime datetime; -- 表中列有数据的情况下不能直接修改数据类型-- 在表中添加一个新字段“regtime_new”，类型为日期时间型alter table userinfo add regtime_new datetime;-- 这个时候可以去更新新字段“regtime_new”的值，将时间戳转换为标准日期时间格式update userinfo set regtime_new = from_unixtime(regtime);-- 同样的方法，设置lastlogin（最后登录时间）alter table userinfo add lastlogin_new datetime;update userinfo set lastlogin_new = from_unixtime(lastlogin);

区域信息表

select * from RegionInfo;-- 需要将“pt”字段由文本型转换为日期型-- 首先需要提取文本中的日期信息（中间8个字符）-- 然后将提取的信息转换为日期型格式(同样使用添加一列的方式)alter table RegionInfo add pt_new date;update RegionInfo set pt_new=mid(pt,2,8);

其他表的调整

select * from useraddress;alter table useraddress add pt_new date;update useraddress set pt_new=mid(pt,2,8);select * from goodsinfo;alter table goodsinfo add addtime_new datetime;update goodsinfo set addtime_new = from_unixtime(addtime);select * from goodsbrand;alter table goodsbrand add pt_new date;update goodsbrand set pt_new=mid(pt,2,8);select * from goodscolor;alter table goodscolor add pt_new date;update goodscolor set pt_new=mid(pt,2,8);select * from goodssize;alter table goodssize add pt_new date;update goodssize set pt_new=mid(pt,2,8);select * from orderinfo;alter table orderinfo add addtime_new datetime;update orderinfo set addtime_new = from_unixtime(addtime);-- paytime字段是支付时间，0的数据不能直接通过from_unixtime()转换（否则会变成1970-01-01 00:00:00），需要先转换成nullalter table orderinfo add paytime_new datetime;update orderinfo set paytime_new = from_unixtime(paytime) where paytime <> '0';;select * from orderdetail;-- orderdetail表不需要调整列数据字段格式

【实际查询案例】

-- 1、不同时段的登陆用户数

-- 按照时间分组，统计每个小时有多少用户登录-- 针对字段“lastlogin_new”提取小时select hour(lastlogin_new) 时段,count(userid) 登录用户数from userinfogroup by hour(lastlogin_new)order by 时段;-- 在实际情况中最后登录时间会是不同的日期，我们就可以用来统计最近7天或者30天登录的用户数，用来统计潜在流失用户

-- 2、不同时段的下单数量

-- 在订单信息表orderinfo中，orderid是唯一的select hour(addtime_new) 时段,count(orderid) 下单数量from orderinfogroup by hour(addtime_new)order by 时段;-- 不同时段的累计下单数量select hour(addtime_new) 时段,count(orderid) 下单数量,sum(count(orderid)) over( order by hour(addtime_new) )累计下单数量from orderinfogroup by hour(addtime_new);-- 上面的例子指定了分区内的排序， 默认就是统计滑动窗口第一行到当前行的订单数量

-- 3、当日GMV(未付款订单金额0+待发货订单金额1+已发货订单金额2+已取消订单金额3)

-- GMV（Gross Merchandise Volume）即商品交易总额，是一段时间内的成交总额-- 按照订单状态进行orderstate分组select orderstate,sum(orderamount) 订单金额from orderinfogroup by orderstatewith rollup; -- with rollup 对分组之后的聚合值进行求和

-- 4、各省市消费金额(orderinfo-orderamount)

-- orderinfo表中的省份字段province 城市字段city-- RegionInfo表中都存放在regionid字段-- select * from RegionInfo;select r1.regionname 省份,r2.regionname 城市,round(sum(orderamount),2)   消费金额from orderinfojoin RegionInfo as r1 on province = r1.regionidjoin RegionInfo as r2 on city = r2.regionidgroup by province,cityorder by province,city;

-- 5、不同支付方式的订单量

-- 支付工具 paytool 用户提交订单后会进入支付界面，选择支付方式，不管支付成功与否，都会产生支付方式select paytool 支付方式,count(orderid) 订单量from orderinfogroup by paytoolorder by paytool;

-- 6、哪种支付方式可能导致用户支付不成功而取消订单

-- 查询因为支付不成功而取消的订单数量-- 订单状态 orderstate = 3 支付失败 paystate = 0select t1.paytool, 未支付取消的订单量,每个支付工具订单总量,ifnull(未支付取消的订单量/每个支付工具订单总量,0) 占比from(select paytool,count(orderid) 每个支付工具订单总量from orderinfogroup by paytool) t1join(select paytool,count(orderid) 未支付取消的订单量from orderinfowhere orderstate = '3' and paystate = '0'group by paytool) t2on t1.paytool = t2.paytool;

-- 7、当日不同品牌的总销量

-- 销量在表orderdetail   品牌在表goodsbrand-- 通过中间表商品表goodsinfo进行三表连接-- 会存在有的商品品牌没有在品牌表中存在的情况select goodsinfo.typeid 品牌ID,brandtype 品牌名称,sum(amount) 销量from orderdetailleft join goodsinfo on orderdetail.goodsid = goodsinfo.goodsidleft join goodsbrand on goodsinfo.typeid = goodsbrand.supplieridgroup by goodsinfo.typeid;

-- 8、当日不同品牌的复购用户数

select  t.品牌ID,brandtype,count(t.userid) 复购用户数from(select goodsinfo.typeid 品牌ID,brandtype,userid,count(distinct orderid) 购买次数from orderdetailleft join goodsinfo on orderdetail.goodsid = goodsinfo.goodsidleft join goodsbrand on goodsinfo.typeid = goodsbrand.supplieridgroup by goodsinfo.typeid,useridhaving count(distinct orderid)  > 1) tgroup by t.品牌ID;

-- 9、查询结果保存为表，用于后续重复使用

create table pro_amount asselect r1.regionname 省份,r2.regionname 城市,round(sum(orderamount),2)   消费金额from orderinfojoin RegionInfo as r1 on province = r1.regionidjoin RegionInfo as r2 on city = r2.regionidgroup by province,cityorder by province,city;select * from pro_amount;

-- 10、将查询结果导出到指定的路径中

-- 主要需要使用以下路径 "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"select r1.regionname 省份,r2.regionname 城市,round(sum(orderamount),2)   消费金额from orderinfojoin RegionInfo as r1 on province = r1.regionidjoin RegionInfo as r2 on city = r2.regionidgroup by province,cityorder by province,cityinto outfile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/pro_amount.csv";

 

 

总结

以上是生活随笔为你收集整理的【一周入门MySQL—5】的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。