银行ATM存取款机系统MySQL数据库

建表

CREATE TABLE cardInfo
(
cardid CHAR(16)not NULL PRIMARY key,
`password` CHAR(6)not NULL ,
curID INT(4)not NULL,
savingID INT(11)not NULL,
openDate TIMESTAMP not NULL,
openMoney DECIMAL(20) not NULL,
balance DECIMAL(20) not NULL,
IsReportLoss bit not NULL,
customerID INT(4)not NULL
)
ALTER TABLE cardinfo ADD CONSTRAINT Fk_cuid FOREIGN KEY(customerID) REFERENCES userinfo(customerID);
ALTER TABLE cardinfo ADD CONSTRAINT FK_saving FOREIGN KEY(savingID) REFERENCES deposit(savingID);


#drop table if exists cardinfo;


CREATE TABLE userInfo
(
customerID INT(4) PRIMARY KEY not NULL,
customerName CHAR(8) not NULL,
PID CHAR(18) not NULL,
telephone CHAR(20) not NULL,
address VARCHAR(50)
)


CREATE TABLE tradeInfo
(
cardID CHAR(16) not NULL,
tradeDate TIMESTAMP not NULL,
tradeMoney DECIMAL(20)not NULL,
tradeType char(4)not NULL,
remark text 
)
ALTER TABLE tradeinfo ADD CONSTRAINT FK_cid FOREIGN KEY(cardid) REFERENCES cardinfo(cardid);

CREATE TABLE deposit
(
savingID INT(4) NOT NULL PRIMARY KEY,
savingName VARCHAR(20) NOT NULL,
descrip VARCHAR(50)
)

测试数据：

#存款类型
INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息');
INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年');
INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年');
INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年');
INSERT INTO deposit (savingName) VALUES ('定活两便');
INSERT INTO deposit (savingName) VALUES ('通知');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年');
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年');
INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息');
SELECT * FROM deposit;

INSERT INTO userInfo(customerName,PID,telephone,address )
     VALUES('张三','123456789012345','010-67898978','北京海淀');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612345678',1,1000,1000,1);

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('李四','321245678912345678','0478-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121134',2,1,1,2);

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('王五','567891234532124670','010-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121130',2,1,1,3);

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('丁六','567891321242345618','0752-43345543');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121004',2,1,1,4);

SELECT * FROM userInfo;
SELECT * FROM cardInfo;


/*
张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。
说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，
      同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少300元）
      
*/
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('支取','1010357612345678',900);  
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010357612345678';


/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('存入','1010357612121134',5000);   
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010357612121134';


/*--------检查测试数据是否正确---------*/
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;

实现功能：

SELECT * from userinfo;
SELECT * from cardinfo;
SELECT * from deposit;
SELECT * from tradeinfo;

#修改张三的密码为：123456 李四的密码为123123
UPDATE cardinfo SET `PASSWORD`=123456 WHERE customerID=1;
UPDATE cardinfo SET `PASSWORD`=123123 WHERE customerID=4;

#办理银行卡挂失
SELECT cardid 卡号,curID 货币,savingName 储蓄种类, openDate 开户日期, openMoney 开户金额,balance 余额,`password` 密码,IF(IsReportLoss=0,'未挂失','已挂失') 是否挂失,customerName 客户姓名  
FROM cardinfo,userinfo,deposit WHERE cardinfo.customerID=userinfo.customerID and cardinfo.savingID=deposit.savingID；/*若全为未挂失则自己修改几条*/

#统计银行总存入和总支出
SELECT  tradetype 资金流向,SUM(trademoney) 总金额 FROM tradeinfo GROUP BY tradetype;

#查询本周开户信息    
SELECT cardid 卡号,customerName 客户姓名 ,curID 货币,savingName 储蓄种类, openMoney 开户金额,balance 余额,IF(IsReportLoss=0,'未挂失','已挂失') 账户状态
FROM cardinfo,userinfo,deposit WHERE cardinfo.customerID=userinfo.customerID and cardinfo.savingID=deposit.savingID AND WEEK(NOW())=WEEK(opendate);

#查询本月交易金额最高的卡号
SELECT cardID from tradeinfo WHERE tradeMoney IN(SELECT MAX(tradeMoney) from tradeinfo WHERE MONTH(tradeDate)=MONTH(NOW()))

#查询挂失客户
SELECT customerName 客户姓名,telephone 联系电话 from userinfo WHERE customerID in
(SELECT customerID from cardinfo WHERE IsReportLoss=1);

#查询客户余额<200则提醒缴费
SELECT customerName 客户名称,telephone 联系电话,balance 存款余额 FROM userinfo,cardinfo WHERE userinfo.customerID=cardinfo.customerID AND balance<200;

#银行卡视图
DROP VIEW IF EXISTS vw_cardinfo;
CREATE VIEW vw_cardInfo 
AS 
(SELECT cardid 卡号,customerName 客户姓名, curID 货币,savingName 储蓄种类, openDate 开户日期, openMoney 开户金额,balance 余额,`password` 密码,IF(IsReportLoss=0,'未挂失','已挂失') 是否挂失 
FROM cardinfo,userinfo,deposit WHERE cardinfo.customerID=userinfo.customerID and cardinfo.savingID=deposit.savingID);

#银行卡交易记录视图
CREATE VIEW vw_transInfo
AS
(SELECT tradeDate 交易日期,tradeType 交易类型,cardinfo.cardid 卡号,tradeMoney 交易金额,remark 备注 FROM tradeinfo,cardinfo WHERE tradeinfo.cardID=cardinfo.cardid);