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;
使用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;