银行业务系统数据库的设计与实现

1.创建数据库银行业务系统数据库—bankDB

Drop database if EXISTS bankDB; #删除bindDB数据库，即使没有数据库也不报错CREATE database bankDB;#创建数据库

2.创建客户表—userInfo

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

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.创建交易表

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,#交易金额，必填非空，默认>0machine char(8) not null#终端机编号，默认非空);desc tradeInfo;

5.存款类型表结构

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; #查看银行卡信息表数据 --->结果1select * 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 isReportLosswhen 1 then '挂失'when 0 then '未挂失'end,customerName from cardInfo INNER JOIN deposit on cardInfo.savingID = deposit.savingIDINNER 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.isReportLosswhen 1 then '挂失账户'when 2 then '正常账户'endfrom cardInfo cardInfo INNER join userInfo on cardInfo.customerID = userInfo.customerIDINNER 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.customerIDwhere balance<1100;

13.创建，使用视图

1.创建view_user试图：

###为了向客户显示信息友好，查询各表要求字段全为中文字段名.#输出银行客户记录drop view if EXISTS view_user; #删除view_user试图，避免数据库里面已经有view_user视图create view view_userASselect 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 ASSELECT cardinfo.cardID 卡号,userinfo.customerName 客户姓名,cardinfo.curID 货币种类,deposit.savingName 存款类型,cardinfo.openDate 开户日期,cardinfo.balance 余额,cardinfo.password 密码,case cardinfo.isReportLosswhen 1 then '挂失'when 0 then '正常'end 账户状态from cardinfo INNER JOIN userInfoon 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 ASSELECT 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="支取")thenINSERT 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;#回滚事务elseCOMMIT;#保存在数据库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过程名()beginmysql语句end;#if语句根据逻辑关系判断条件是ture还是false，转去执行相对应的分支中的语句#语法结构：if expr_condition thenstatement_listelseif expr_condition thenstatement_listelse statement_listend if;

2.产生随机卡号

#随机函数的rand的使用#rand(随机种子)#将产生0~1的随机数，要求每次的随机种子不一样。为了保证随机种子每次都不相同，一般采用的算法为：#随机种子=当前的月份*10000+当前的分钟数*1000+当前的秒钟数*100#产生0~1的随机数后，取小数点后8为，即0.xxxxxxxxDROP PROCEDURE IF EXISTS use_randCardID;CREATE PROCEDURE use_randCardID(OUT randCardID char(19))BEGINDECLARE 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 dataBEGINDECLARE 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 dataBEGIN 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 DATABEGINDECLARE 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 THENSELECT "转入账户不存在";SET err=err+1;set errl = 1;END IF;# 转出账户余额不足（小于转账金额）SELECT balance INTO ye FROM cardinfo WHERE cardID=outCard_id;IF(ye结果1select * from cardinfo; --->结果2