mysql练习题(20210819)

作者: yayooo | 来源:发表于2021-08-12 12:23 被阅读0次

mysql练习题(20210819)
数据库语言杂记
20210819
数据蛙第九期就业班 2020/7/23
2020 -7-23
Mysql练习题-50
7.27 mysql练习
10-16练习题
LeetCode-mysql练习题
mysql练习题

题

create table product (
zid varchar(20),
category_id int,
`operator` varchar(20),
sellerid int,
shop_name varchar(64)
);

insert into product(zid,category_id,`operator`,sellerid,shop_name) values('A1', 2, '张三', 4, '服装1');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('A2', 2, '张三', 4, '服装1');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('A3', 4, '李四', 2, '服装4');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('c1', 4, '赵二', 8, '服装3');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('c2', 6, '赵二', 6, '服装6');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('c3', 8, '李二', 10, '服装4');




create table category_info (
id int,
fist_name varchar(20),
second_name varchar(64)
);
insert into category_info(id,fist_name,second_name) values(2,'女装','女裙');
insert into category_info(id,fist_name,second_name) values(4,'女装','短袖');
insert into category_info(id,fist_name,second_name) values(6,'男装','上衣');
insert into category_info(id,fist_name,second_name) values(8,'男装','短裤');



create table `order` (
orderid varchar(20),
zid varchar(20),
quantity int,
`date` date,
gmv double,
buyerid varchar(20)
);


insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-001','A1',1,'2018-01-01', 156.7,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-002','A2',1,'2018-06-01', 196.7,'u_002');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-003','A2',1,'2018-06-02', 176.7,'u_005');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-004','A3',1,'2018-06-17', 199.9,'u_006');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-005','A3',1,'2018-06-20', 1188.7,'u_007');

insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-006','c1',1,'2018-07-09', 166.8,'u_002');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-007','c2',1,'2018-07-10', 136.5,'u_005');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-008','A3',1,'2018-07-29', 123.8,'u_006');

insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-009','A2',1,'2018-08-10', 123.8,'u_007');


insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-010','A3',50,'2018-04-01', 2600.0,'u_003');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-011','A1',20,'2018-04-05', 2000.0,'u_004');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-020','A1',1,'2018-04-07', 20.0,'u_099');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c1',1,'2018-04-07', 50.0,'u_098');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c2',1,'2018-04-09', 30.0,'u_097');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c3',20,'2018-04-09', 1999.0,'u_096');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c3',2,'2018-04-09', 90.0,'u_095');


insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-012','c1',1,'2019-01-01', 19.5,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-013','c2',1,'2019-02-01', 25.6,'u_011');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-014','c3',1,'2019-03-01', 126.7,'u_012');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-015','c1',1,'2019-04-01', 555.5,'u_013');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-033','A2',1,'2019-02-01', 10.9,'u_011');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-034','A3',1,'2019-03-01', 99.9,'u_012');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-035','A1',1,'2019-04-01', 299.9,'u_013');

insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-016','A1',8,'2020-01-01', 653.7,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-017','A1',7,'2020-01-01', 328.7,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-018','A3',6,'2020-02-01', 428.7,'u_008');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','A3',3,'2020-12-01', 25.7,'u_009');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','c2',2,'2020-12-01', 100.0,'u_021');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','c3',5,'2020-12-01', 100.0,'u_022');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','c3',5,'2020-12-01', 100.0,'u_022');

1.导出女装类目2020年整体销额，订单数，销量

-- 先求女装
select t2.zid
from category_info t1 left join product t2 on t1.id = t2.category_id
where t1.fist_name='女装'
;

-- 关联求聚合，注意：double会损失精度，替代使用decimal
select sum(gmv) as total_gmv,
       count(orderid) as order_cnt,
       sum(quantity) as total_quantity
from `order` t3
where t3.date >= '2020-01-01'
  and t3.date <= '2021-12-31'
  and t3.zid in (
    select t2.zid
    from category_info t1
             left join product t2 on t1.id = t2.category_id
    where t1.fist_name = '女装'
);

2.导出2018年4月总销额大于2000元的商品，整年的订单数，销量，购买用户数，总金额

-- 先求销售额大于2000的商品id(zid)
select zid,
       sum(gmv) as zid_total_gmv
from `order`
where year(date)='2018'
and month(date)='04'
group by zid
having zid_total_gmv > 2000;

-- 求 整年 的订单数，销量，购买用户数，总金额
select count(orderid)            as total_order,
       sum(quantity)             as total_quantity,
       count(distinct (buyerid)) as total_user,
       sum(gmv)                  as total_gmv
from `order`
where year(date) = '2018'
  and zid in (
    select zid,
           sum(gmv) as zid_total_gmv
    from `order`
    where year(date) = '2018'
      and month(date) = '04'
    group by zid
    having zid_total_gmv > 2000
)
;

3.导出2019年订单单价在如下价格阶段下商品的总销量，总订单，总销额(0-20,20-50,50-100,100-200,200-500,500+)

-- 根据商品id分组聚合(这道题有点歧义，也就是是否要根据商品id分组）
select zid,
       sum(quantity) as total_quantity,
       count(orderid) as total_order,
       sum(gmv) as total_gmv,
       '0-20'
from `order`
where year(date)='2019'
  group by zid
having total_gmv < 20

union all
select zid,
       sum(quantity) as total_quantity,
       count(orderid) as total_order,
       sum(gmv) as total_gmv,
       '20-50'
from `order`
where year(date)='2019'
  group by zid
having total_gmv >= 20 and total_gmv < 50

union all
select zid,
       sum(quantity) as total_quantity,
       count(orderid) as total_order,
       sum(gmv) as total_gmv,
       '50-100'
from `order`
where year(date)='2019'
  group by zid
having total_gmv >= 50 and total_gmv < 100

union all
select zid,
       sum(quantity) as total_quantity,
       count(orderid) as total_order,
       sum(gmv) as total_gmv,
       '100-200'
from `order`
where year(date)='2019'
  group by zid
having total_gmv >= 100 and total_gmv < 200

union all
select zid,
       sum(quantity) as total_quantity,
       count(orderid) as total_order,
       sum(gmv) as total_gmv,
       '200-500'
from `order`
where year(date)='2019'
  group by zid
having total_gmv >= 200 and total_gmv < 500

union all
select zid,
       sum(quantity) as total_quantity,
       count(orderid) as total_order,
       sum(gmv) as total_gmv,
       '200-500'
from `order`
where year(date)='2019'
  group by zid
having total_gmv >= 500
;

4.导出2020年每个店铺销量前3名商品，并输出一级类目，运营，店铺等基本信息

-- 写法1
select t2.zid,
       t4.fist_name,
       t3.operator,
       t3.sellerid,
       t3.shop_name
from (
         select zid,
                sum(quantity) as zid_total_quantity
         from `order`
         where year(date) = '2020'
         group by zid
         order by zid_total_quantity desc
         limit 3
     ) t2
         left join product t3 on t2.zid = t3.zid
         left join category_info t4 on t3.category_id = t4.id
;


-- 写法2
-- 定义变量
set @crank = 0;
select t2.zid,
       t4.fist_name,
       t3.operator,
       t3.sellerid,
       t3.shop_name

from(
    select t1.zid,
       t1.zid_total_quantity,
       @crank := @crank + 1 as crank
from (
         select zid,
                sum(quantity) as zid_total_quantity
         from `order`
         where year(date) = '2020'
         group by zid
         order by zid_total_quantity desc
     )t1
        )t2 left join product t3 on t2.zid = t3.zid
left join category_info t4 on t3.category_id = t4.id
where t2.crank <= 3
;

5.导出在2018年6月下单的用户，分别在7月，8月，9月下单的购买的用户数

select buyerid,
       '07'
from `order` t2
where year (t2.date)='2018'
  and month (t2.date)='07'
  and buyerid in (
    select t1.buyerid
    from `order` t1
    where year (t1.date)='2018'
  and month (t1.date)='06'
    )

union all

select buyerid,
       '08'
from `order` t2
where year (t2.date)='2018'
  and month (t2.date)='08'
  and buyerid in (
    select t1.buyerid
    from `order` t1
    where year (t1.date)='2018'
  and month (t1.date)='06'
    )

union all
select buyerid,
       '09'
from `order` t2
where year (t2.date)='2018'
  and month (t2.date)='09'
  and buyerid in (
    select t1.buyerid
    from `order` t1
    where year (t1.date)='2018'
  and month (t1.date)='06'
    )

;

延伸，用户漏斗分析，也叫用户留存，如：6月下单的用户，7月还剩哪些，8月还剩哪些，9月还剩哪些

mysql练习题(20210819)
1.导出女装类目2020年整体销额，订单数，销量 2.导出2018年4月总销额大于2000元的商品，整年的订单数，...
数据库语言杂记
MySQL ORDER BY 排序 IF 及 IN 字符串连接函数concat() MySQL练习题：练习题一 ...
20210819
虽然告别了“梦魇” 但感觉并不轻松结束时她的话仿佛又让我难以释怀那种感觉又来了，它又来了我无法控制自己不...
数据蛙第九期就业班 2020/7/23
MYSQL练习题 1、MySQL中的varchar和char有什么区别？ 1、CHAR的长度是固定的，而VARCH...
2020 -7-23
mysql 练习题1、mysql中的varchar 和 char 有什么区别？二者存储长度不同，char 是不可变...
Mysql练习题-50
Mysql 练习题文章来自网络，仅供自学我使用的Mysql版本是5.7.19。答案可能会因版本会有少许出入。 ...
7.27 mysql练习
mysql练习题目: GROUP BY 语句基本用法： GROUP BY 语句中的GROUP_CONCAT()函...
10-16练习题
MySQL练习题题目1 问题1：如何暂停或开启mysql服务？按win+r打开，输入cmd,快速打开命令行界面...
LeetCode-mysql练习题
leetcode-mysql练习题总结：老师指路->https://www.jianshu.com/u/989c...
mysql练习题
端口登录 3306或3308