©2020-2024 ioDraw All rights reserved,
Privacy Policy
银行业务系统数据库的设计与实现
1.创建数据库银行业务系统数据库---bankDB
Drop database if EXISTS bankDB; #删除bindDB数据库，即使没有数据库也不报错 CREATE database
bankDB;#创建数据库
2.创建客户表---userInfo
字段名称数据类型含义说明
customerIDint客户编号自动增量，从1开始，主键
customerNamevarchar开户名必填
PIDchar身份证号必填，只能是18位，唯一约束
telephonevarchar联系电话必填，格式为固定电话或者手机号
addressvarchar居住地址可选输入 use bankDB;#使用数据库 drop table if exists
userInfo;#删除userInfo数据表，即使没有数据表也不报错 create table userInfo #创建userInfo数据表---客户表
( customerID int auto_increment PRIMARY key, #客户编号，自动增量，从1开始，主键 customerName
varchar(20) not null, #开户名，必填非空 PID CHAR(18) NOT NULL, #身份证号，必填非空 telephone
varchar(15) not null, #电话号码，必填非空 address varchar(50) #地址，可以为空 ); alter table
userInfo add CONSTRAINT UQ_PID UNIQUE(PID); #给userInfo表的PID添加一个唯一约束 desc
userInfo; #查看数据表的表结构
3.创建银行卡表---cardInfo
字段名称数据类型含义说明
cardIDchar卡号必填，主键。
curIDvarchar货币种类外键，必填，默认为RMB
savingIDtinyint存款类型外键，必填
openDatedatetime开户日期必填，默认为系统系统当前日期和时间
openMoneydouble开户余额必填
balancedouble余额必填
passwordchar密码必填，6位数字，开户时默认为6个8
isReportLosschar是否挂失必填，是/否，默认为否
customerIDint客户编号外键，必填 drop table if exists
cardInfo;#删除cardInfo数据表，即使没有数据表也不报错 create table cardInfo
#创建cardInfo数据表---银行卡表 ( cardID char(19) not null, #卡号，必填非空 curID varchar(10)
not null,#货币种类，必填非空 savingID int not null, #存款类型，必填非空 openDate Datetime not
null,#开户日期，必填非空 openMoney DOUBLE not null,#开户金额，必填非空 balance DOUBLE not
null,#余额，必填非空 password char(6) not null,#密码，6位数字，开户时默认为6个8，必填非空 isReportLoss
char(1) not null,#是否挂失，是/否，默认为否，必填非空 customerID int not null#客户编号，外键。 ); desc
cardInfo;
4.创建交易表
字段名称数据类型含义说明
tradeDatedatetime交易日期必填，默认为系统当前日期和时间
cardIDvarchar卡号外键，必填
tradeTypechar交易类型必填，只能是存入/支取
tradeMoneydouble交易金额必填，大于0
machinechar终端机编号客户业务操作的机器编号 drop table if exists
tradeInfo;#删除tradeInfo数据表，即使没有数据表也不报错 create table tradeInfo
#创建tradeInfo数据表---交易表 ( tradeDate datetime not null,#交易日期，必填，默认为系统当前日期和时间
tradeType enum("存入","支出") not null,#交易类型，必填非空，只能是存入/支取 cardID char(19) not
null, #卡号，外键，必填非空 tradeMoney DOUBLE not null,#交易金额，必填非空，默认>0 machine char(8)
not null#终端机编号，默认非空 ); desc tradeInfo;
5.存款类型表结构
字段名称数据类型含义说明
savingIDtinyint存款类型编号自动增量，从1开始，主键
savingNamevarchar存款类型名称必填
descriptvarchar描述可空 drop table if exists deposit;#删除deposit数据表，即使没有数据表也不报错
create table deposit #创建deposit数据表--->存款类型表 ( savingID INT AUTO_INCREMENT
PRIMARY KEY, #存款类型编号，自动增量从1开始，主键 savingName varchar(20) not null,#存款类型名称，必填非空
descript varchar(50)#描述 ); desc deposit;
6.给cardInfo表添加约束
alter table cardInfo add CONSTRAINT PK_cardID PRIMARY key(cardID);
#给cardID添加一个主键约束 alter table cardInfo ALTER curID SET DEFAULT "RMB";
#给curID设置一个默认选项为RMB alter table cardInfo MODIFY COLUMN openDate DATETIME NOT
NULL DEFAULT CURRENT_TIMESTAMP; #开户日期设置默认为当前系统时间 alter table cardInfo ALTER
password SET DEFAULT "888888"; #给password设置一个默认选项为888888 alter table
cardInfo alter isReportLoss SET DEFAULT 0; #给isReportLoss设置默认选项为0。1为是，0为否
alter table cardInfo add CONSTRAINT FK_customerID FOREIGN key(customerID)
REFERENCES userInfo(customerID);
#设置外键，cardInfo为userInfo的从表，customerID参考为userInfo表的客户编号 alter table cardInfo
add CONSTRAINT FK_savingID FOREIGN key(savingID) REFERENCES deposit(savingID);
#设置外键，cardInfo为userInfo的从表，savingID参考为userInfo表的存款类型编号 desc cardInfo;
7.给tradeInfo表添加约束
alter table tradeInfo add CONSTRAINT PK_cardID_tradeInfo PRIMARY
key(cardID,tradeDate); #在tardeInfo表中设置cardID，tradeDate为联合主键 alter table
tradeInfo add CONSTRAINT FK_cardID FOREIGN key(cardID) REFERENCES
cardInfo(cardID); #设置外键，tradeInfo为cardInfo的从表，cardID参考cardInfo的卡号 alter
table tradeInfo MODIFY COLUMN tradeDate datetime not null DEFAULT
CURRENT_TIMESTAMP; #设置tradeInfo表的交易日期为非空约束，默认为系统当前日期 alter table tradeInfo
modify machine char(8) DEFAULT 1; #设置终端机编号默认为1 create index IX_cardID on
tradeInfo(cardID); #在tradeInfo表上使用create index 语句创建索引名为IX_card #语法结构：create
[unique(唯一索引)] [fulltext(全文索引)] [spatial(空间索引)] index 索引名 on
表名(字段名[(长度)][asc|desc][,....]);
8.向存款类型表中插入数据
insert into deposit(savingName,descript) VALUES ('活期','按存款日结算利息'); insert into
deposit(savingName,descript) VALUES ('定期一年','存款期为一年'); insert into
deposit(savingName,descript) VALUES ('定期两年','存款期为两年'); insert into
deposit(savingName,descript) VALUES ('定期三年','存款期为三年'); insert into
deposit(savingName) VALUES ('按定活两便'); insert into deposit(savingName,descript)
VALUES ('零存整取一年','存款期为一年'); insert into deposit(savingName,descript) VALUES
('零存整取两年','存款期为两年'); insert into deposit(savingName,descript) VALUES
('零存整取三年','存款期为三年'); select * from deposit; #查看deposit表中的数据 #插入完整记录：insert
into 表名(字段1,字段2，....) values ('字段1对应的数据1','字段2对应的数据2'); #查看数据：select 字段名 form
表名;
9.向客户信息表中插入数据
INSERT into userInfo(customerName,PID,telephone,address) VALUES
('周也','431281200108193619','0719-26224941','湖北省武汉市武昌区'),
('王科','441391200201294618','0729-44624944','湖北省武汉市洪山区'),
('何豪','42128120020413463X','0749-21227409','湖北省武汉市青山区'),
('王涵宇','761212200308191682','0927-22492341','湖北省鄂州市华容区'),
('鲁班','663221200308211352','1127-52592652','湖北省鄂州市华容区'); select * from
userInfo; #插入数据记录的一部分 insert into 表名(字段1,字段2，......) values
('字段1对应的数据1','字段2对应的数据2',.....), ('字段1对应的数据1','字段2对应的数据2',.....),
('字段1对应的数据1','字段2对应的数据2',.....);
10.向银行卡表中插入数据
INSERT into cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES
('6227 2666 1234 5678',1,1000,1000,1), ('6227 2666 5678 1234',2,6000,6000,2),
('6227 2666 1432 5876',3,9000,9000,3), ('6227 2666 1234 5666',4,2500,2500,4),
('6227 2666 1234 5888',5,3600,3600,5); select * from cardInfo;
11.向交易表中插入数据
INSERT into tradeinfo(tradeType,cardID,tradeMoney) VALUES ('支出','6227 2666
1234 5678',900); #在交易表中插入交易记录 UPDATE cardInfo SET balance = balance-900
where cardID = '6227 2666 1234 5678'; #更新银行卡表中的现有余额。 INSERT into
tradeinfo(tradeType,cardID,tradeMoney) VALUES ('存入','6227 2666 5678
1234',5000); #在交易表中插入交易记录 UPDATE cardInfo set balance = balance+5000 where
cardID = '6227 2666 5678 1234'; #更新银行卡表中的现有余额。 INSERT into
tradeinfo(tradeType,cardID,tradeMoney) VALUES ('支出','6227 2666 1432
5876',1000); #在交易表中插入交易记录 UPDATE cardInfo set balance = balance-1000 where
cardID = '6227 2666 1432 5876'; #更新银行卡表中的现有余额。 INSERT into
tradeinfo(tradeType,cardID,tradeMoney) VALUES ('存入','6227 2666 1234
5666',3000); #在交易表中插入交易记录 UPDATE cardInfo set balance = balance+3000 where
cardID = '6227 2666 1234 5666'; #更新银行卡表中的现有余额。 INSERT into
tradeinfo(tradeType,cardID,tradeMoney) VALUES ('存入','6227 2666 1234
5888',1000); #在交易表中插入交易记录 UPDATE cardInfo set balance = balance+1000 where
cardID = '6227 2666 1234 5888'; #更新银行卡表中的现有余额。 select * from cardInfo;
#查看银行卡信息表数据 --->结果1 select * from tradeInfo; #查看交易表数据--->结果2
12.编写SQL语句实现
1.修改客户密码
#修改customerID=1，周也的银行卡密码 UPDATE cardInfo set password = '123456' where
cardID = '6227 2666 1234 5678'; #修改customerID=3，何豪的银行卡密码 UPDATE cardInfo
set password = '654321' where cardID = '6227 2666 1432 5876'; select * from
cardInfo;#查看银行卡信息表 #表记录的修改：UPDATE语句 #语法格式：update [可选参数] 数据表名 set
字段1=值1[，字段2=值2....] [where 条件表达式] [orderby] [limit]
#可选参数：low_priority,表示在多用户访问数据库的情况下可用延迟UPDATE操作，直到没有别的用户读取数据为止，这个过程仅适用于表级锁的存储引擎。
ignore：在mysql中，如果更新语句出现错误，整个update语句操作都会被取消，错误发生更新的所有行将被恢复到他们原来的值。因此，为了发生错误时也要继续
更新，可以使用ignore参数
#set：指定数据表中需要修改的字段名及其字段值。其中的值可以是表达式，也可以是该字段对应的默认值。如果要制定默认值，则需要使用关键字default。
#where:用于限定表中要修改的行，如果不指定该子句，那么update语句会更新表中所有行。 #orderby：用于限定表中的行被修改的次序
#limit：用于限定被修改的行数
2.办理银行卡挂失
#周也不小心将银行卡丢失，申请挂失服务 update cardInfo set isReportLoss = 1 where cardID =
'6227 2666 1234 5678'; select * from cardInfo; #查看修改密码和挂失结果 select
cardID,curID,savingName,openDate,openMoney,balance,password, case isReportLoss
when 1 then '挂失' when 0 then '未挂失' end,customerName from cardInfo INNER
JOIN deposit on cardInfo.savingID = deposit.savingID INNER JOIN userInfo on
cardInfo.customerID = userInfo.customerID; #case语句为条件判断语句，用于多分支判断大的程序结构
case 表达式 when when_value then statement_list; [when when_value then
statement_list;]..... else select 'good'; end case;
如果when_value的值表达式的值相匹配，则执行then关键字后面statement_list语句。
如果when_value表达式的值都不匹配，则执行else关键字后面的语句。 #多表查询： #语法规则： select [表名.] 目标字段名 [AS 别名]
from 左表名[AS 别名] 连接类型 右表名[AS 别名] on 连接条件 [where 条件表达式]; #连接类型以及运算符： 1.cross
join：交叉连接 2.inner join或join：内连接 3.left join或left outer join：左外连接 4.right
join或right outer join：右外连接 5.full join或full outer join：完全连接
3.查询本周开户信息
#date_sub()函数从日期减去指定的时间间隔。语法格式： #data_sub(date,INTERVAL expr type)
#参数说明：date：合法的日期表达式，expr：指定的时间间隔。
#type：间隔类型，有miscrosecond，seconde，minute，hour，day，week，mounth，year等 select
cardInfo.cardID,userInfo.customerName,cardInfo.curID,deposit.savingName,cardInfo.openDate,cardInfo.openMoney,cardInfo.balance,
case cardInfo.isReportLoss when 1 then '挂失账户' when 2 then '正常账户' end from
cardInfo cardInfo INNER join userInfo on cardInfo.customerID =
userInfo.customerID INNER join deposit on cardInfo.savingID=deposit.savingID
where openDate > DATE_SUB(CURDATE(),INTERVAL 1 week); #非等值连接 #内连接：select
字段名列表 from 表名1 inner join 表名2 on 表名1 字段名 比较运算符 表名2 字段名;
4.查询本月交易金额最高的卡号
select DISTINCT cardID from tradeInfo #Distinct：去掉重复的卡号 where tradeMoney =
(SELECT MAX(tradeMoney) from tradeInfo #利用子查询 WHERE tradeDate >
DATE_SUB(CURDATE(),INTERVAL 1 MONTH)); #时间为本月
#DATE_SUB()函数---->从日期减去指定的时间间隔。 语法规则：DATE_SUB(date,INTERVAL expr type) type
参数可以是下列值： MICROSECOND #微秒级 SECOND #秒数 MINUTE #分钟数 HOUR #小时数 DAY #天数 WEEK
#周数 MONTH #月数 QUARTER #季度 YEAR #年数 ....... #CURDATE---->获取系统当前的日期
5.查询挂失客户
select customerName,telephone from userInfo where customerID in(SELECT
customerID from cardInfo where isReportLoss =1); #查询挂失客户，显示挂失客户的名称和电话 #SELECT
customerID from cardInfo where isReportLoss =1 查询银行卡信息表中isReportLoss =1的用户的ID
6.催款提示业务
#根据某种业务(如代缴电话费,代缴手机费等)的需要，每个月末，如果发现客户账户上余额少于1100元，将由银行统一致电催款。 #利用链接查询或者子查询
select customerName,telephone,balance from userInfo INNER JOIN cardInfo on
cardInfo.customerID = userInfo.customerID where balance<1100;
13.创建，使用视图
1.创建view_user试图：
###为了向客户显示信息友好，查询各表要求字段全为中文字段名. #输出银行客户记录 drop view if EXISTS view_user;
#删除view_user试图，避免数据库里面已经有view_user视图 create view view_user AS select customerID
as 客户编号,customerName as 开户名,PID AS 身份证号,telephone as 电话号码,address as 居住地址 from
userInfo; select * from view_user;#查看视图详细信息 #视图的创建 create [or replace]
[algoritm]={undefined|merge|temptable} view view_name [(字段名列表)] as select语句
[with[cascaded|local] check option]
2.创建view_card试图：
#输出银行卡记录 drop view if exists view_card; create view view_card AS SELECT
cardinfo.cardID 卡号,userinfo.customerName 客户姓名,cardinfo.curID
货币种类,deposit.savingName 存款类型,cardinfo.openDate 开户日期,cardinfo.balance
余额,cardinfo.password 密码, case cardinfo.isReportLoss when 1 then '挂失' when 0
then '正常' end 账户状态 from cardinfo INNER JOIN userInfo on
cardinfo.customerID=userinfo.customerID INNER JOIN deposit on cardinfo.savingID
= deposit.savingID; SELECT * from view_card;
3.创建view_trade试图：
#查看交易信息 drop view if EXISTS view_trade; CREATE view view_trade AS SELECT
tradeDate as 交易日期,tradeType as 交易类型,cardId as 卡号,tradeMoney as 交易金,machine as
终端机编号 from tradeInfo; select * from view_trade;
14.使用事务和存储过程实现业务处理
1.完成存款或取款业务
Drop PROCEDURE if EXISTS trade_proc; CREATE PROCEDURE trade_proc(IN t_type
char(2),In t_money DOUBLE,in card_id char(19),in m_id char(8)) MODIFIES sql
DATA #表示子程序中包含写数据的SQL语句。 BEGIN DECLARE ye DOUBLE; #声明变量ye为double类型 start
TRANSACTION; #启动事务处理 if(t_type="支取")then INSERT into
tradeinfo(tradeType,cardID,tradeMoney,machine) VALUES
(t_type,card_id,t_money,m_id); UPDATE cardinfo set balance = balance - t_money
where cardID = card_id; if(ye <0) then SELECT "余额不足"; ROLLBACK;#回滚事务 else
COMMIT;#保存在数据库 end if; end if; if(t_type="存入")then INSERT into
tradeinfo(tradeType,cardID,tradeMoney,machine) VALUES
(t_type,card_id,t_money,m_id); UPDATE cardinfo set balance = balance + t_money
where cardID = card_id; COMMIT; end if; end; #创建和调用不带输入参数的存储过程 create procedure
过程名() begin mysql语句 end; #if语句根据逻辑关系判断条件是ture还是false，转去执行相对应的分支中的语句 #语法结构： if
expr_condition then statement_list elseif expr_condition then statement_list
else statement_list end if;
2.产生随机卡号
#随机函数的rand的使用 #rand(随机种子) #将产生0~1的随机数，要求每次的随机种子不一样。为了保证随机种子每次都不相同，一般采用的算法为：
#随机种子=当前的月份*10000+当前的分钟数*1000+当前的秒钟数*100 #产生0~1的随机数后，取小数点后8为，即0.xxxxxxxx DROP
PROCEDURE IF EXISTS use_randCardID; CREATE PROCEDURE use_randCardID(OUT
randCardID char(19)) BEGIN DECLARE r DECIMAL(15,8); #declare声明变量 DECLARE
tempStr CHAR(10); SELECT
RAND((MONTH(NOW())*10000)+(MINUTE(NOW())*1000)+(SECOND(NOW())*100)) INTO r; SET
tempStr=CONVERT(r, CHAR(10));#CONVERT() 函数是把日期转换为新数据类型的通用函数。 SET
randCardID=CONCAT('6227 2666 ', SUBSTRING(tempStr, 3, 4), ' ',
SUBSTRING(tempStr, 7, 4)); END; #测试产生的随机卡号 set @kh=" "; call
use_randCardID(@kh); SELECT @kh;
3.统计银行资金流通金额和盈利结算
#资金流通金额=总存入的金额-总支出金额 #盈利结算=总支取金额x0.008-总存入金额x0.003
#提示：定义两个变量存放总存入金额和总支取金额。使用sum()函数进行汇总，使用转换函数convert() #计算银行资金流通金额 drop
procedure if EXISTS profit_proc1; create PROCEDURE profit_proc1(out y1 DOUBLE)
reads sql data BEGIN DECLARE l_in DOUBLE; declare l_out DOUBLE; SELECT
sum(tradeMoney) into l_in from tradeinfo where tradeType = "存入"; SELECT
sum(tradeMoney) into l_out from traneinfo where tradeType = "支出"; set
y1=l_out-l_in; end; #计算盈利结算 drop PROCEDURE if EXISTS profit_proc2; CREATE
PROCEDURE profit_proc(out y2 DOUBLE) reads sql data BEGIN DECLARE l_in DOUBLE;
declare l_out DOUBLE; SELECT sum(tradeMoney) into l_in from tradeinfo where
tradeType = "存入"; SELECT sum(tradeMoney) into l_out from traneinfo where
tradeType = "支出"; set y2=l_out*0.008 - l_in*0.003; end;
4.利用事务实现转账
DROP PROCEDURE IF EXISTS useTradefer_proc; CREATE PROCEDURE useTradefer_proc(
IN outCard_id CHAR(19), IN inCard_id CHAR(19), IN z_je DOUBLE, IN m_id CHAR(4))
MODIFIES SQL DATA BEGIN DECLARE ye DOUBLE; DECLARE err INT DEFAULT 0; # 错误信息累计数
DECLARE errl INT DEFAULT 0; -- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET
errl=1; # 转入账户不存在 DECLARE bankCount INT DEFAULT 0; SELECT count(*) into
bankCount FROM cardinfo WHERE cardID=inCard_id; IF !bankCount THEN SELECT
"转入账户不存在"; SET err=err+1; set errl = 1; END IF; # 转出账户余额不足（小于转账金额） SELECT
balance INTO ye FROM cardinfo WHERE cardID=outCard_id; IF(ye<z_je) THEN SELECT
"账户余额不足"; SET err=err+1; set errl = 1; END IF; # 没有错误信息正常转账并进行交易信息记录 IF(err=0)
THEN START TRANSACTION; UPDATE cardinfo SET balance=balance-z_je WHERE
cardID=outCard_id; UPDATE cardinfo SET balance=balance+z_je WHERE
cardID=inCard_id; INSERT INTO tradeinfo(tradeType, cardID, tradeMoney, machine)
VALUES('支出', outCard_id, z_je, m_id); INSERT INTO tradeinfo(tradeType, cardID,
tradeMoney, machine) VALUES('存入', inCard_id, z_je, m_id); # 没有报错则提交，有报错则滚回
IF(errl=1) THEN SELECT errl; ROLLBACK; ELSE COMMIT; END IF; END IF; END; call
useTradefer_proc('6227 2666 1234 5666', '6227 2666 1234 5678',1,1); SELECT *
from tradeinfo; --->结果1 select * from cardinfo; --->结果2