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

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

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货币种类外键，必填，默认为RMBsavingIDtinyint存款类型外键，必填openDatedatetime开户日期必填，默认为系统系统当前日期和时间openMoneydouble开户余额必填balancedouble余额必填passwordchar密码必填，6位数字，开户时默认为6个8isReportLosschar是否挂失必填，是/否，默认为否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交易金额必填，大于0machinechar终端机编号客户业务操作的机器编号

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

标签：数据库 database sql

本文转载自: https://blog.csdn.net/m0_60981735/article/details/125172614
版权归原作者 你的晚安 所有，如有侵权，请联系我们删除。