模拟常规业务

1.办理银行卡挂失
   使用UPDATE语句实现密码变更和卡挂失
     UPDATE … WHERE …
UPDATE cardInfo SET IsReportLoss=1 WHERE cardID='1010357612121134' 
        SELECT * FROM cardInfo


2.统计银行总存入金额和总支取金额
   使用聚合函数SUM()
      SELECT tradeType 资金流向, SUM(tradeMoney) 总金额 FROM…
SELECT * FROM tradeInfo; 
SELECT tradeType 资金流向, SUM(tradeMoney) 总金额 FROM tradeInfo GROUP BY tradeType;


3.查询本周开户的卡号，显示该卡相关信息
       SELECT …FROM…WHERE WEEK(NOW()) = WEEK(openDate);
SELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,d.savingName 存款类型,c.openDate 开户日期,c.openMoney 开户金额,c.balance 存款余额,IsReportLoss 账户状态
FROM cardInfo c INNER JOIN userInfo u ON (c.customerID = u.customerID)
INNER JOIN deposit d ON (c.savingID = d.savingID )
WHERE WEEK(NOW()) = WEEK(openDate);


4.查询本月交易金额最高的卡号
   使用子查询和DISTINCT关键字去掉重复的卡号
      SELECT DISTINCT cardID  FROM transInfo  WHERE transMoney = ( SELECT … FROM … )
SELECT * FROM tradeInfo;
SELECT DISTINCT cardID FROM tradeInfo WHERE  tradeMoney=
(SELECT MAX(tradeMoney) FROM tradeInfo
WHERE MONTH(tradeDate)=MONTH(NOW())
AND YEAR(tradeDate)=YEAR(NOW()));



5.查询挂失账号的客户信息
     使用子查询IN 或内联接查询INNER JOIN
        SELECT … FROM userInfo  WHERE customerID IN ( SELECT … FROM … )
 SELECT customerName AS 客户姓名,telephone AS 联系电话 FROM userInfo 
          WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss='挂失');


6.催款提醒业务
    使用子查询IN 或内联接查询IN0.NER JOIN
       SELECT … FROM userInfo  INNER JOIN …
SELECT customerName AS 客户姓名,telephone AS 联系电话,balance AS 存款余额 
FROM userInfo INNER JOIN cardInfo ON  userInfo.customerID=cardInfo.customerID 
WHERE balance<200;