【学习笔记】create database ds; use ds; -- 建表导数------------------- ...

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

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; -- 检查数据结构

-- 添加一个新字段
alter table userinfo add lastlogin_new datetime;
set sql_safe_updates=0;
-- 将时间戳转换为时间格式并赋值
update userinfo set lastlogin_new=from_unixtime(lastlogin);






-- 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;
alter table OrderInfo add AddTime_new datetime;
update OrderInfo set AddTime_new=from_unixtime(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 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



-- 不同时段的登陆用户数
select hour(lastlogin_new) 时段,count(userid) 登陆用户数 from userinfo
group by hour(lastlogin_new)
order by hour(lastlogin_new);


-- 不同时段的累计销售额
select
        hour(addtime_new) 时段,
    sum(orderamount) 订单总金额,
    sum(sum(orderamount)) over(order by hour(addtime_new)) 累计销售额
from orderinfo
group by hour(addtime_new);


-- 各省市消费金额：省份、城市、消费金额
select r2.regionname 省份,r1.regionname 城市,sum(orderamount) 消费金额
from orderinfo
left join regioninfo r1 on city=r1.regionid
left join regioninfo r2 on r1.parentid=r2.regionid
group by r2.regionid,r1.regionid;

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


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

select
        paytool,
    sum(orderstate=3 and paystate=0)支付不成功而取消订单量,
    count(orderstate=3 and paystate=0)总订单量,
    avg(orderstate=3 and paystate=0)未支付而取消的订单占比
from orderinfo
group by paytool;
-- 不同品牌的总销量
select count(SupplierID) from goodsbrand;-- 64
select count(goodsid) from goodsinfo;-- 10000
select count(distinct typeid) from goodsinfo;-- 69
select typeid,brandtype,sum(amount) 总销量 from orderdetail
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on typeid=SupplierID
group by typeid;

-- 不同品牌的复购用户数
#不同品牌下每个用户的购买次数
select typeid,brandtype,userid,count(distinct orderid) 购买次数
from orderdetail
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on typeid=SupplierID
group by typeid,userid;


#不同品牌的复购用户
select typeid,brandtype,userid 复购用户
from orderdetail
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on typeid=SupplierID
group by typeid,userid
having count(distinct orderid)>=2;

#不同品牌的复购用户数
select typeid,brandtype,count(复购用户) 复购用户数
from
(select typeid,brandtype,userid 复购用户
from orderdetail
left join goodsinfo on orderdetail.goodsid=goodsinfo.goodsid
left join goodsbrand on typeid=SupplierID
group by typeid,userid
having count(distinct orderid)>=2) t
group by typeid;

-- 查询每个月的复购率
select 自然月份,复购月份,复购用户数,总用户数,复购用户数/总用户数 复购率
from
(select t1.dt 自然月份,t2.dt 复购月份,count(t1.user_id) 复购用户数
from (select month(create_time) dt,user_id from order_info group by month(create_time),user_id) t1
join (select month(create_time) dt,user_id from order_info group by month(create_time),user_id) t2
on t1.user_id=t2.user_id and t1.dt<t2.dt
group by t1.dt,t2.dt) a
join
(select month(create_time) 月份,count(distinct user_id) 总用户数
from order_info
group by month(create_time)) b
on a.自然月份=b.月份;

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：database Create creat Data Base