Mysql面试题(设计题) 01

Mysql面试题(设计题) 01

1.1 2021年过年时小明在这些天都收到了许多亲戚\朋友还有同事的红包,也发出了一些红包,有的是微信,有的是支付宝也有现金,请参考下面的题目帮小明设计表格保存红包的信息;

1. 设计表 (至少包含一张流水表)
2. 统计2021年2月15号到现在的所有红包收益
3. 查询2021年2月15号到现在 金额大于100 所有女性亲戚的名字和金额
4. 查询三个平台(微信,支付宝,现金)分别收入的红包金额

答题：

1. 设计表 (至少包含一张流水表)

• 列出需要保存的所有信息

  • 关系,红包类型,红包金额,时间,性别,名字

  id	money	time	type(支付)	name	gender	(rel)关系
  1	100	2021-03-20	现金	刘德华	男	亲戚
  2	-100	2021-03-20	微信	刘德华	男	亲戚
  3	100	2021-03-20	现金	刘德华	男	亲戚
  4

  因为可能小明可能和同一个人多次交易，所以会出现很多相同的人物，为避免出现数据冗余，我们再设计一张表格来存人物信息。

  • 人物表person

  id	name(姓名)	gender(性别)	rel(关系)
  1	刘德华	男	亲戚
  2	杨幂	女	亲戚
  …	…	…	…

  sql代码：

  create table person(id int primary key auto_increment,name varchar(20),gender varchar(5),rel varchar(20));
  

  • 流水表trade

  id	money	time	type	p_id
  1	1000	2021-03-20	微信	1
  2	-50	2021-04-14	现金	2
  3	200	2021-04-14	现金	2

  sql代码：

create table trade(id int primary key auto_increment, money int,time date,type varchar(5),p_id int);

• 准备数据:

  • 人物表:

    sql代码：

    insert into person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友');
    

  • 流水表:

    刘德华 微信 收1000 2021-03-20    
    杨幂 现金 收500 发50 2021-04-14    
    马云 支付宝 收20000 发5 2021-03-11    
    特朗普 微信 收2000  2021-05-18    
    貂蝉 微信 发20000 2021-07-22
    

    insert into trade values
    (null,1000,'2021-03-20','微信',1),
    (null,500,'2021-04-14','现金',2),
    (null,-50,'2021-04-14','现金',2),
    (null,20000,'2021-03-11','支付宝',3),
    (null,-5,'2021-03-11','支付宝',3),
    (null,2000,'2021-05-18','微信',4),
    (null,-20000,'2021-07-22','微信',5);
    

2. 统计2021年2月15号到现在的所有红包收益

   select sum(money) from trade where time>"2021-2-15";
   

3. 查询2021年2月15号到现在 金额大于100 所有女性亲戚的名字和金额

   select name,money from person p join trade t on p.id=t.p_id 
   where time >"2021-2-15" and money not between -100 and 100 and gender="女" and rel="亲戚";
   

4. 查询三个平台(微信,支付宝,现金)分别收入的红包金额

   select type,sum(money) from trade where money>0 group by type;
   

我亦无他，惟手熟尔