E-R图
#创建银行存取款系统数据库 如果存在先删除在创建
DROP DATABASE IF EXISTS bankDB;
CREATE DATABASE IF NOT EXISTS bankDB;
#创建普通用户bankMaster并授权
GRANT ALL ON bankDB TO [email protected] IDENTIFIED BY ‘1234’;
#创建银行客户表
DROP TABLE IF EXISTS userInfo;
CREATE TABLE IF NOT EXISTS userInfo(
customerID INT(8) PRIMARY KEY AUTO_INCREMENT COMMENT’客户编号’,
customerName VARCHAR(10) NOT NULL COMMENT’开户姓名’,
PID CHAR(18) NOT NULL COMMENT’省份证编号’,
telephone CHAR(11) NOT NULL COMMENT ‘手机号码’,
address VARCHAR(50) COMMENT’地址’
)ENGINE=INNODB CHARSET=utf8 COMMENT=‘客户表’;
#创建银行卡信息表
DROP TABLE IF EXISTS cardInfo;
CREATE TABLE IF NOT EXISTS cardInfo(
cardID VARCHAR(19) NOT NULL PRIMARY KEY COMMENT’银行卡卡号’,
curID VARCHAR(10) NOT NULL DEFAULT’RMB’ COMMENT’币种’,
savingID INT(2) NOT NULL COMMENT’存款类型 ‘,
openDate TIMESTAMP NOT NULL COMMENT’开卡日期’,
openmoney DECIMAL NOT NULL COMMENT ‘开卡金额’,
balance DECIMAL NOT NULL COMMENT ‘账户余额’,password VARCHAR(6) NOT NULL DEFAULT’888888’ COMMENT’密码’,
IsReportLoss BIT NOT NULL DEFAULT 0 COMMENT ‘是否挂失’,
customerID INT(8) NOT NULL COMMENT’客户编号’
#CONSTRAINT FK_customerID FOREIGN KEY(customerID) references userInfo(customerID)
)ENGINE=INNODB CHARSET=utf8 COMMENT=‘银行卡信息表’;
ALTER TABLE cardinfo MODIFY openmoney DECIMAL(9,2);
ALTER TABLE cardinfo MODIFY balance DECIMAL(9,2);
#创建外键
ALTER TABLE cardInfo
ADD CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userINfo(customerID);
ALTER TABLE cardInfo
ADD CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID);
#删除外键
#alter table cardInfo drop foreign key FK_savingID;
#交易信息表
DROP TABLE IF EXISTS tradeInfo;
CREATE TABLE IF NOT EXISTS tradeInfo(
transDate TIMESTAMP NOT NULL COMMENT’交易日期’,
cardID CHAR(19) NOT NULL COMMENT’卡号’,
transType CHAR(10) NOT NULL COMMENT’交易类型’,
transMonry DECIMAL NOT NULL COMMENT’交易金额’,
remark TEXT COMMENT’交易备注’,
CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID)
)ENGINE=INNODB CHARSET=utf8 COMMENT=‘交易信息表’;
#存款类型表
DROP TABLE IF EXISTS deposit;
CREATE TABLE IF NOT EXISTS deposit(
savingID INT(2) PRIMARY KEY COMMENT’存款类型 ‘,
savingName VARCHAR(20) NOT NULL COMMENT’存款类型名称’,
curID VARCHAR(10) NOT NULL DEFAULT’RMB’ COMMENT’存款币种’
)ENGINE=INNODB CHARSET=utf8 COMMENT=‘存款类型表’;
#银行客户表数据
INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
VALUES(1,‘丁六’,‘567891321242345618’,‘13645667783’,‘北京西城区’),
(2,‘王五’,‘56789132124234567X’,‘13642345112’,‘河北石家庄市’),
(3,‘张三’,‘567891321242345789’,‘13567893246’,‘北京海定区’),
(4,‘丁一’,‘123451321242345618’,‘13056434411’,‘河南新乡’),
(5,‘李四’,‘678911321242345618’,‘13318877954’,‘山东济南市’);
#存款类型表数据
INSERT INTO deposit(savingID,savingName)
VALUES(1,“定期一年”),(2,“定期二年”),(3,‘活期’);
#银行卡信息数据
INSERT INTO cardInfo(cardID,savingID,openDate,openmoney,balance,password,IsReportLoss,customerID)
VALUES(‘1010357612121004’,1,NOW(),1,1001,‘888888’,0,1),
(‘1010357612121130’,1,NOW(),1,1,‘888888’,0,2),
(‘1010357612125678’,3,NOW(),1000,6100,‘123456’,0,3),
(‘1010357612128284’,3,NOW(),1000,1001,‘888888’,0,4),
(‘1010357612123333’,1,NOW(),1,1501,‘123123’,1,5);
UPDATE cardINfo SET cardID=‘1010357612121134’ WHERE cardID=‘1010357612123333’;
#交易信息表数据
INSERT INTO tradeInfo(transDate,cardID,transType,transMonry,remark)
VALUES(NOW(),‘1010357612125678’,‘支取’,900,NOW()),
(NOW(),‘1010357612121130’,‘存入’,300,NOW()),
(NOW(),‘1010357612121004’,‘存入’,1000,NOW()),
(NOW(),‘1010357612121130’,‘存入’,1900,NOW()),
(NOW(),‘1010357612121134’,‘存入’,5000,NOW()),
(NOW(),‘1010357612121134’,‘存入’,500,NOW()),
(NOW(),‘1010357612121134’,‘支取’,2000,NOW()),
(NOW(),‘1010357612125678’,‘存入’,2000,NOW()),
(NOW(),‘1010357612121134’,‘支取’,2000,NOW()),
(NOW(),‘1010357612125678’,‘存入’,2000,NOW()),
(NOW(),‘1010357612125678’,‘存入’,2000,NOW());
#update tradeinfo set transType=‘支取’ where cardID='1010357612121134
#delete from tradeinfo;
#模拟常规业务
#（1）修改客户密码:张三、李四
UPDATE cardInfo SET password=123456 WHERE cardID=‘1010357612125678’;
UPDATE cardInfo SET password=123123 WHERE cardID=‘1010357612121134’;
#（2）办理银行卡挂失 :李四
UPDATE cardInfo SET IsReportLoss=1 WHERE cardID=‘1010357612121134’;
SELECT c.cardID ‘卡号’,c.curID ‘货币’, d.savingName ‘储存种类’, c.openmoney ‘开户金额’, c.balance‘余额’,
c.password ‘密码’,c.IsReportLoss‘是否挂失’,u.customerName‘客户姓名’
FROM cardInfo c
INNER JOIN userInfo u
ON c.customerID=u.customerID
INNER JOIN deposit d
ON c.savingID=d.savingID;
#统计银行总存入金额和总支出金额
SELECT transType 资金流向,SUM(transMonry) 总金额 FROM tradeinfo
WHERE transType='支取’OR transType=‘存入’
GROUP BY transType;
#查询本周开户信息
SELECT c.cardID ‘卡号’, u.customerName ‘姓名’,c.curID ‘货币’,d.savingName ‘存款类型’,
c.openDate ‘开户日期’, c.openmoney ‘开好户金额’, c.balance ‘存款余额’, c.IsReportLoss‘账户状态’
FROM userinfo u
INNER JOIN cardinfo c
ON u.customerID=c.customerID
INNER JOIN deposit d
ON c.savingID=d.savingID
WHERE WEEK(c.openDate)=WEEK(NOW());
#查询本月交易金额最高的卡号：存款、取款
SELECT cardID FROM tradeinfo WHERE transMonry=(
SELECT MAX(transMonry) FROM tradeInfo WHERE transType=‘支出’ OR transType=‘存入’
AND MONTH(transDate)= MONTH(NOW())
);
#查询挂失客户
SELECT u.customerName ‘客户姓名’,u.telephone ‘联系电话’,c.cardID ‘卡号’,c.IsReportLoss ‘1是/2否挂失’
FROM userinfo u
INNER JOIN cardinfo c
ON c.customerID=u.customerID
WHERE c.IsReportLoss= b’1’;
#催款提醒业务
SELECT u.customerName ‘客户姓名’,u.telephone ‘联系电话’,c.balance ‘存款余额’
FROM userinfo u
INNER JOIN cardinfo c
ON u.customerID=c.customerID
WHERE c.balance<200;
#创建用户视图
DROP VIEW IF EXISTS view_userInfo;
CREATE VIEW view_userInfo
AS
SELECT customerID ‘客户编号’, customerName ‘开户名’,PID ‘身份证号’,telephone ‘电话号码’,address ‘居住地址’
FROM userinfo u;
#创建银行卡视图
DROP VIEW IF EXISTS view_cardinfo;
CREATE VIEW view_cardinfo
AS
SELECT cardID ‘卡号’,customerName ‘客户名’,c.savingID ‘货币种类’,savingName ‘存款类型’,openDate ‘开户日期’,
balance’余额’, password‘密码’,IsReportLoss’是否挂失’
FROM userinfo u
INNER JOIN cardinfo c
ON u.customerID=c.customerID
INNER JOIN deposit d
ON c.savingID=d.savingID;
#创建交易记录视图
DROP VIEW IF EXISTS view_transInfo;
CREATE VIEW view_transInfo
AS
SELECT transDate ‘交易日期’,transType ‘交易类型’,cardID ‘卡号’, transMonry ‘交易金额’, remark ‘备注’
FROM tradeinfo;
#使用事物完成转账
#模拟从‘1010357612121134’账户中转账出300元给卡号为’1010357612125678‘的账户 李四转给丁一300
#先关闭自动提交
SET autocommit=0;
#开启事物
START TRANSACTION;
UPDATE cardinfo SET balance=balance-300 WHERE cardID=‘1010357612121134’;
UPDATE cardinfo SET balance=balance+300 WHERE cardID=‘1010357612125678’;
#提交事物 永久保存
COMMENT;
#遇到问题回滚数据回到初始状态
ROLLBACK;
#还原自动提交
SET autocommit=1;