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

完成以下功能；

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

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

-- 2
SELECT card.*
FROM card,transinfo
where card.cardNo=transinfo.cardNo
GROUP BY cardNo
ORDER BY count( type ) DESC LIMIT 2

-- 3
SELECT user.*
FROM user,card,transinfo
where user.userId=card.userId and card.cardNo=transinfo.cardNo
GROUP BY transinfo.cardNo
ORDER BY count( type ) DESC  LIMIT 2

-- 4
select card.cardNo,sum(card.money)余额,sum(transinfo.money)交易金额
from card,user,transinfo
where user.userId=card.userId and card.cardNo=transinfo.cardNo
GROUP BY card.cardNo 

-- 5
select user.name , count(transinfo.type)交易次数
FROM user,card,transinfo
where user.userId=card.userId and card.cardNo=transinfo.cardNo
GROUP BY user.name

-- 6
select user.name , sum(transinfo.money)交易金额
FROM user,card,transinfo
where user.userId=card.userId and card.cardNo=transinfo.cardNo
GROUP BY user.name
ORDER BY sum(transinfo.money) desc

-- 7
select user.city,sum(transinfo.money)交易金额
FROM user,card,transinfo 
where user.userId=card.userId and card.cardNo=transinfo.cardNo and transinfo.transDate BETWEEN '2019-07-01' and '2019-07-31' and user.city='北京'

-- 8
select city,sum(transinfo.money)消费
FROM user,card,transinfo 
where user.userId=card.userId and card.cardNo=transinfo.cardNo and transinfo.type = '支取' or transinfo.type='转出'
GROUP BY city
ORDER BY sum(transinfo.money) desc LIMIT 1

-- 9
select city,sum(transinfo.money)消费
FROM user,card,transinfo 
where user.userId=card.userId and card.cardNo=transinfo.cardNo and transinfo.type = '支取' or transinfo.type='转出'
GROUP BY city
ORDER BY sum(transinfo.money)  LIMIT 1