新增mysql练习题

 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 local infile 'E:/data/rawdata/UserInfo.csv' 
	into table userinfo
    fields terminated by ','
	ignore 1 lines;

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

-- 创建新的日期时间字段并赋值
alter table userinfo add regtime_ datetime not null;    #创建日期时间型的字段

SET SQL_SAFE_UPDATES = 0;   #打开数据库的更新权限

update userinfo set regtime_ = from_unixtime(regtime);     ##将注册时间转换为24小时制

-- 创建新的日期时间字段并赋值

alter table userinfo add lastlogin_ datetime not null;
update userinfo set lastlogin_ = from_unixtime(lastlogin);

-- 删除原有错误日期格式字段
alter table userinfo drop column regtime;
alter table userinfo drop column lastlogin;

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 local infile 'E:/data/rawdata/regioninfo.csv' 
	into table regioninfo
    fields terminated by ','
	ignore 1 lines;

select * from regioninfo;


-- 提取文本型字段的中间八位字符
update regioninfo set pt = substring(pt,1,8);

-- 创建新的日期时间字段并赋值
alter table regioninfo add pt_ date not null;
update regioninfo set pt_ = date_format(pt,'%Y-%m-%d');  ##转换日期格式

-- 删除原有错误日期格式字段
alter table regioninfo drop column pt;

desc regioninfo;

-- 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 local infile 'E:/data/rawdata/UserAddress.csv' 
	into table useraddress
    fields terminated by ','
	ignore 1 lines;
    
select * from useraddress;
    
update useraddress set pt = substring(pt,1,8);

alter table useraddress add pt_ date not null;
update useraddress set pt_ = date_format(pt,'%y-%m-%d');


select length(pt_) from useraddress;    ##计算该字段的长度

alter table useraddress drop column pt;

select count(*) from useraddress;

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 local infile 'E:/data/rawdata/GoodsInfo.csv' 
	into table goodsinfo
    fields terminated by ','
	ignore 1 lines;

select * from goodsinfo limit 10;

alter table goodsinfo add addtime_ datetime not null;
update goodsinfo set addtime_ = from_unixtime(addtime);

alter table goodsinfo drop column addtime;


-- 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 local infile 'E:/data/rawdata/GoodsBrand.csv' 
	into table goodsbrand
    fields terminated by ','
	ignore 1 lines;

select * from goodsbrand limit 10;

update goodsbrand set pt = substring(pt,1,8);

alter table goodsbrand add pt_ date not null;
update goodsbrand set pt_ = date_format(pt,'%y-%m-%d');

alter table goodsbrand drop column pt;

-- 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 local infile 'E:/data/rawdata/goodscolor.csv' 
	into table goodscolor
    fields terminated by ','
	ignore 1 lines;

select * from goodscolor limit 10;

update goodscolor set pt = substring(pt,1,8);

alter table goodscolor add pt_ date not null;
update goodscolor set pt_ = date_format(pt,'%y-%m-%d');

alter table goodscolor drop column pt;

-- 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 local infile 'E:/data/rawdata/goodssize.csv' 
	into table goodssize
    fields terminated by ','
	ignore 1 lines;

select * from goodssize limit 10;

update goodssize set pt = substring(pt,1,8);

alter table goodssize add pt_ date not null;
update goodssize set pt_ = date_format(pt,'%y-%m-%d');

alter table goodssize drop column pt;

-- 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) not null default '-',
    AddTime varchar(20) not null default '-'
);

#导入数据
load data local infile 'E:/data/rawdata/OrderInfo.csv' 
	into table OrderInfo
    fields terminated by ','
	ignore 1 lines;

select * from OrderInfo limit 10;
alter table OrderInfo add PayTime_ datetime not null;
update OrderInfo set PayTime_ = from_unixtime(PayTime);



alter table OrderInfo add AddTime_ datetime not null;
update OrderInfo set AddTime_ = from_unixtime(AddTime);


alter table OrderInfo drop column PayTime;
alter table OrderInfo drop column AddTime;

-- 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 local infile 'E:/data/rawdata/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.查询订单总金额和订单总数量
select sum(orderamount),count(OrderID)
from orderinfo;

-- 2、查询订单金额超过3000元的订单信息：订单id、用户id、金额
select orderid,userid,orderamount
from orderinfo
where  orderamount>3000;


-- 3、查询平均消费金额超过10000的顾客
select avg(OrderAmount),userinfo.*
from orderinfo left join userinfo on orderinfo.UserID=userinfo.userid
group by userinfo.userid
having avg(orderamount)>10000;

-- 4.查询订单消费金额最多的前十名顾客的最后登录时间
select  orderinfo.userid,lastlogin_,sum(orderamount) 订单消费金额
	from  orderinfo left join userinfo on orderinfo.UserID=userinfo.userid
	group by orderinfo.userid
	order by 订单消费金额 desc
    limit 10;
    
-- 5、查询订单消费总金额最多的前十名顾客的所在城市

    
##更换city就可以改变查询的城市或者省份国家等
select  o.userid,sum(orderamount) 订单消费金额  ,regionname 城市
	from orderinfo o left join regioninfo r on o.city=r.regionid
	group by o.userid
	order by 订单消费金额 desc
    limit 10;
        
-- 6、查询购买力最强的前十个城市
select sum(orderamount) 购买力 ,regionname 城市
from orderinfo a left join regioninfo b on a.city = b.regionid
group by city
order by 购买力 desc
limit 10;


-- 7、查询购买力最强的前十个城市以及他们所在的省份  #购买力通过各省下单费用排名得到，，，
#先查询购买力最强的前十个城市
##由于城市和省份都在表regioninfo中，，所以进行表的自连接查询


select  s.regionname 省份,r.regionname 城市,sum(orderamount) 订单消费总金额 
	from orderinfo o 
    left join regioninfo r on o.city=r.regionid 
    left join regioninfo s on s.regionid = o.province
	group by o.city
	order by 订单消费总金额 desc
    limit 10;


-- 8、查询产品的总销量和总销售额

select sum(amount) 总销量 , sum(goodsprice*amount) 总销售额
from orderdetail;



-- 9、查询最畅销的十种商品


select sum(amount) 销量,goodsid
from orderdetail
group by goodsid
order by 销量 desc
limit 10;



-- 10、查询最畅销的十个品牌


select sum(amount) 销量,brandtype
from orderdetail o 
left join goodsinfo g on o.goodsid=g.goodsid 
left join goodsbrand c on g.typeid=c.supplierid 
group by typeid
order by 销量 desc
limit 10;


use ds;
-- 11、查询最畅销的十种商品所属品牌中所有商品的销售额
#查询最畅销的十种商品所属的品牌

select distinct e.typeid from (
select a.goodsid,typeid,sum(amount)
from orderdetail a left join goodsinfo b on a.goodsid = b.goodsid
group by a.goodsid
order by sum(amount) desc
limit 10) e;



select c.goodsid,sum(amount*goodsprice)
from orderdetail c 
left join goodsinfo d on c.goodsid = d.goodsid
where d.typeid in
(select distinct e.typeid
from (select a.GoodsID,typeid,sum(Amount)
from orderdetail a
left join goodsinfo b on a.GoodsID=b.goodsid
group by a.GoodsID
order by sum(Amount) desc
limit 10) e)
group by c.goodsid;


-- 12、查询购买不同商品种类最多的前十名客户所使用的收货城市分别有哪些   ##因为此时的一种商品对应多个城市，在一行中，所以用group_concat()函数将收货城市合并一行
#商品的种类进行分组，先查找购买过不同种类最多的前十名客户




select *
from orderdetail a left join orderinfo b on a.orderid = b.orderid left join regioninfo c on b.city = c.regionid;   ###每一个单品所对应的订单信息

select b.userid,count(distinct goodsid) 商品种类,
	group_concat(distinct regionname) 收货城市
from orderdetail a 
	left join orderinfo b on a.orderid = b.orderid 
	left join regioninfo c on b.city = c.regionid
	group by b.userid
    order by 商品种类 desc
    limit 10;