银行业务系统数据库的设计与实现
1.创建数据库银行业务系统数据库---bankDB
Drop database if EXISTS bankDB; #删除bindDB数据库，即使没有数据库也不报错
CREATE database bankDB;#创建数据库
2.创建客户表---userInfo
| 字段名称 |
数据类型 |
含义 |
说明 |
| customerID |
int |
客户编号 |
自动增量，从1开始，主键 |
| customerName |
varchar |
开户名 |
必填 |
| PID |
char |
身份证号 |
必填，只能是18位，唯一约束 |
| telephone |
varchar |
联系电话 |
必填，格式为固定电话或者手机号 |
| address |
varchar |
居住地址 |
可选输入 |
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
| 字段名称 |
数据类型 |
含义 |
说明 |
| cardID |
char |
卡号 |
必填，主键。 |
| curID |
varchar |
货币种类 |
外键，必填，默认为RMB |
| savingID |
tinyint |
存款类型 |
外键，必填 |
| openDate |
datetime |
开户日期 |
必填，默认为系统系统当前日期和时间 |
| openMoney |
double |
开户余额 |
必填 |
| balance |
double |
余额 |
必填 |
| password |
char |
密码 |
必填，6位数字，开户时默认为6个8 |
| isReportLoss |
char |
是否挂失 |
必填，是/否，默认为否 |
| customerID |
int |
客户编号 |
外键，必填 |
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.创建交易表
| 字段名称 |
数据类型 |
含义 |
说明 |
| tradeDate |
datetime |
交易日期 |
必填，默认为系统当前日期和时间 |
| cardID |
varchar |
卡号 |
外键，必填 |
| tradeType |
char |
交易类型 |
必填，只能是存入/支取 |
| tradeMoney |
double |
交易金额 |
必填，大于0 |
| machine |
char |
终端机编号 |
客户业务操作的机器编号 |
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.存款类型表结构
| 字段名称 |
数据类型 |
含义 |
说明 |
| savingID |
tinyint |
存款类型编号 |
自动增量，从1开始，主键 |
| savingName |
varchar |
存款类型名称 |
必填 |
| descript |
varchar |
描述 |
可空 |
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
