根据教材中的user表、card表、transInfo表进行多表查询

完成以下功能；

1. 查询展示交易时间、银行卡号、客户姓名、交易类型、交易金额
2. 查询交易次数最多的银行卡信息
3. 查询交易次数最多的客户信息
   
4. 统计每个银行卡的交易金额，展示银行卡号、银行卡余额，交易总金额
5. 统计每个客户的交易次数，展示客户姓名、交易次数
6. 统计每个客户的交易金额，展示客户姓名、交易金额，按交易金额降序排列
7. 查询北京地区2019年7月的总交易金额
8. 查询哪个城市消费总金额最大（消费：支取和转出）
9. 查询哪个城市最不爱花钱

-- 三张表的查询

select transDate,card.cardNo,name,type,transinfo.money from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo;

select card.*, count(type)from card,transinfo where transinfo.cardNo=card.cardNo

group by transinfo.cardNo order by count(type) desc limit 0,2;

select user.*, transinfo.cardNo,count(type)from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo

group by transinfo.cardNo order by count(type) desc limit 0,2;

-- 统计每个银行卡的交易金额，展示银行卡号、银行卡余额，交易总金额

select card.cardNo,sum(card.money)余额,sum(transinfo.money)交易金额

from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo

GROUP BY card.cardNo;

-- 统计每个客户的交易次数，展示客户姓名、交易次数

select user.name , count(transinfo.type)交易次数

from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo

GROUP BY user.name

-- 统计每个客户的交易金额，展示客户姓名、交易金额，按交易金额降序排列

select user.name , sum(transinfo.money)交易金额

from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo

GROUP BY user.name

ORDER BY sum(transinfo.money) desc

-- 查询北京地区2019年7月的总交易金额

select user.city,sum(transinfo.money)交易金额

from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo  and transinfo.transDate BETWEEN '2019-07-01' and '2019-07-31' and user.city='北京'

-- 查询哪个城市消费总金额最大（消费：支取和转出）

select city,sum(transinfo.money)消费

from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo and transinfo.type = '支取' or transinfo.type='转出'

GROUP BY city

ORDER BY sum(transinfo.money) desc LIMIT 1;

-- 查询哪个城市最不爱花钱

select city,sum(transinfo.money)消费

from user,card,transinfo where user.userld=card.userId and transinfo.cardNo=card.cardNo and transinfo.type in('支取','转出')

GROUP BY city

ORDER BY sum(transinfo.money)  LIMIT 1;