sql终结

一个真实的电商案例，下面是表结构

create database ds;

use ds;



-- 建表导数---------------------------------------
--  UserInfo table
create 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 userinfo
    fields terminated by ','
	ignore 1 lines;

select * from userinfo limit 10; -- 检查数据信息
select count(*) from userinfo; -- 检查总行数1000
desc userinfo; -- 检查数据结构










-- drop table userinfo;

-- --------------regioninfo-------
create 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 regioninfo
    fields terminated by ','
	ignore 1 lines;

select * from regioninfo limit 10;








-- drop table regioninfo;

-- --------------UserAddress-------
create 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 useraddress
    fields terminated by ','
	ignore 1 lines;
    

select * from useraddress limit 10;










-- drop table useraddress;

-- ----GoodsInfo----
create 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 goodsinfo
    fields terminated by ','
	ignore 1 lines;

select * from goodsinfo limit 10;











-- drop table goodsinfo;

-- ----GoodsBrand----
create 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 goodsbrand
    fields terminated by ','
	ignore 1 lines;

select * from goodsbrand limit 10;










-- drop table goodsbrand;

-- ----GoodsColor----
create 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 goodscolor
    fields terminated by ','
	ignore 1 lines;

select * from goodscolor limit 10;










-- drop table goodscolor;

-- ----GoodsSize----
create 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 goodssize
    fields terminated by ','
	ignore 1 lines;

select * from goodssize limit 10;










-- drop table goodssize;

-- ----OrderInfo----
create 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 OrderInfo
    fields terminated by ','
	ignore 1 lines;

select * from OrderInfo limit 10;









-- drop table OrderInfo;

-- ----OrderDetail----
create 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 OrderDetail
    fields terminated by ','
	ignore 1 lines;
    
select * from OrderDetail limit 10;

select count(*) from OrderDetail;

-- drop table orderdetail;


-- 查询导入表的行数
select count(*) from userinfo; -- 1000
select count(*) from RegionInfo; -- 3415
select count(*) from useraddress; -- 10000
select count(*) from goodsinfo; -- 10000
select count(*) from goodsbrand; -- 64
select count(*) from goodscolor; -- 2641
select count(*) from goodssize; -- 289
select count(*) from orderinfo; -- 3711
select count(*) from orderdetail; -- 10000
#数据预处理 
#1.把时间戳转化为时间 ,时间戳为0 的转化为时间不是null而是1970
set sql_safe_updates=0;#没有批量更改先执行权限 
update userinfo set regtime=from_unixtime(regtime);#也会报错原因因为太长，若更改字段也会报错，时间戳改不了 
alter table userinfo add regtime_new datetime;
alter table userinfo add lastlongin_new datetime;
#把时间戳转化为时间再复制给新字段 
update userinfo set regtime_new=from_unixtime(regtime);
update userinfo set lastlongin_new=from_unixtime(lastlogin);
#设置日期型的字段，原来是文本赋值给原来的还是文本
alter table regioninfo add pt_new date;
update regioninfo set pt=mid(pt,2,8);
select * from regioninfo limit 10;

-- 不同时段的登陆用户数,hour 小时进行分组 ,可以对数量进行一个排序 ,报错了，但语法没错 
select hour(lastlogin_new) 时段,count(userid) 登入用户数 from userinfo 
group by hour(lastlogin_new) order by hour(lastlogin);


-- 不同时段的下单数量
select hour(addtime_new) as 时段,count(orderid) 下单数量 
from orderinfo 
group by hour(addtime_new)
order by hour(addtime_new); 
#计算累计订单数量开窗函数排序了会自动默认从当第一行到当前行排序计算,不然全表就是一个区，结果全一样，不是第一行到当前行了 

 select hour(addtime_new) as 时段,count(orderid) as 下单数量 ,sum(count(orderid)) 
 over(order by hour(addtime_new)) as 累计订单
from orderinfo 
group by hour(addtime_new); 



-- 当日GMV(未付款订单金额+待发货订单金额+已发货订单金额+已取消订单金额)，gmv就是流水 
select orderstate,sum(orderamount) 订单金额 
from orderinfo group by orderstste with rollup;#wuth rollup 与group by 配合使用，计算分组后的聚合值 





-- 各省市消费金额，要显示省份，城市，订单金额,多个条件分组，先按第一个再按第二个分组  
select r1.regionname 省份,r2.regionname 城市,sum(orderamount)
from orderinfo
join regioninfo r1 on province=r1.regionid 
join regioninfo r2 on city=r2.regionid 
group by province,city order by province,city;
#两张表字段相同需要加表限定，字段不同不需要加表限定 



-- 不同支付方式的订单量
select paytool,count(orderid) as 订单量 from orderinfo 
group by paytool;

-- 哪种支付方式可能导致用户支付不成功而取消订单
select paytool,count(orderid) /(select count(orderid) from orderinfo )
from orderinfo
where orderstate=3 and paystate=0
group by paytool;


-- 不同品牌的总销量，本次就是同一天的，多表作为主表就不会造成缺失的情况 
select goodsinfo.typeid,brandtypeid,sum(amount) as 总销量
from orderdetail 
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on goodsinfo.typeid=goodsbrand.supplierid
group by goodsinfo.typeid;#聚合只显示分组字段和聚合字段就行了 





-- 不同品牌的复购用户数，同一天的 #经常遇到 
select t.typeid,brandtype,count(recid) 复购用户数  from
(select goodsinfo.typeid,brandtype,userid,count(distinct orderid) 下单次数
from orderdetail 
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on goodsinfo.typeid=goodsbrand.supplierid
group by goodsinfo.typeid,userid
having count(distinct orderid)>1) 
t group by t.typeid;