银行ATM取款机系统项目案例SQL实操

作者: 爱学习的蹭蹭 | 来源:发表于2019-04-24 21:57 被阅读9次

1、前言与目的

为了更好的了解SQL语法与知识体系
提高自身对SQL脚本的认识。如：SQL的结构，SQL的关键字的使用
在深入了解SQL之后可以提高自己的认知度，比如：工作中不论是建表（建模）或是开发工作中，经过自身的学习，在不同的场景下使用它肯定会意想不到的收获的喜悦与丰收

2、学习三部曲

什么是SQL
为什么要SQL
怎么使用SQL

3、什么是SQL

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

4、为什么要SQL

学习SQL的意义，就在于你学会以后，将掌握数据分析应聘中的SQL能力，并找到一份数据分析工作。例如社群会员扑克牌成功转行数据分析，现在是饿了么业务分析师，就是靠擅长SQL数据分析。

5、怎么使用SQL

会利用SQL操作关系数据库进行查询数据
使用SQL操作存储过程（创建、删除）等操作
对于数据库表的增、删、改、约束、索引，视图，触发器等操作
以上场景只是列了部分，在实际工作或学习过程中的场景会更多，就不列举。比如：使用SQL进行数据统计,报表的统计等操作.

6 、SQL实操场景使用

经过了解之后，咋们以银行ATM取款机系统项目案例实操进行分解
SQLServer版本为：SQL Server 2005
SQLServer 系统数据库表

表名	表描述(作用)
sysdatabases	系统数据库，用于查询用户创建的数据库
sysobjects	系统对象库，用户查询用户创建的数据库的表或视图
sysindexes	查询数据库的表的索引

SQLServer 常用关键字

关键字	关键字描述(作用)
begin	开始，在存储过程或声明声明式脚本中表示SQL语句的开始
begin tran	开始事务
rollback tran	事务回滚
commit tran	提交事务
end	结束，在存储过程或声明声明式脚本中表示SQL语句的结束
view	视图，在创建视图中使用
go	表示去执行的SQL语句
proc	创建存储过程关键字
declare	声明变量，变量以@进行修饰
exec	执行存储过程
print	打印信息
output	输出参数
filename	文件存储到某个地方
filegrowth	文件以百分之15增长
size	分配大小
use	使用当前数据库或说是切换到某个数据库
alter	修改或改变
exists	存在

创建bankDB数据库

-- `切换master系统主干数据库`
use master 
go
    SET NOCOUNT ON
    if exists(select * from sysdatabases where name='bankDB')
    drop database bankDB
go

--`exec xp_cmdshell 'mkdir D:\bank'  调用DOS创建文件夹`
--`创建一个名称为bankDB数据库`
create database bankDB
ON
(
    name='bankDB_data',
    filename='D:\bank\bankDB_data.mdf', --文件存储到操作系统D盘的bank
    size=5mb,   -- 分配5兆
    filegrowth=15% -- 文件以百分之15增长
)
log on
(
    name='bankDB_log',
    filename='D:\bank\bankDB_log.ndf',
    size=5mb,
    filegrowth=15%
)
go

切换到BANKDB数据库

USE BANKDB

在BANKDB数据库检测表是否存在

表名	表描述(作用)
cardInfo	银行卡信息表
transInfo	交易信息表
userInfo	用户信息表

--`注意：删除表时有主外约束，首先删从表先`
go
--`银行卡信息表`
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
go

--`交易信息表`
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
go

--`用户信息表`
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
go

在BANKDB数据库创建表SQL


--`创建用户信息表`
create table userInfo
(
customerID int identity(1,1) not null primary key, --顾客编号,主键
customerName varchar(10)not null,     --开户名
PID varchar(30)not null unique,      --身份证号,身份证号唯一约束
telephone varchar(15)not null,      --联系电话格式xxxx-xxxxxxxx或手机号11位
address varchar(250)        --居住地址,可选输入
)
go
--`创建银行卡信息表`
create table cardInfo
(
cardID  varchar(50)not null primary key ,  --卡号,主键,格式为:1010 3576 xxxx xxxx 后面是随机的
curType varchar(10)not null,     --货币种类
savingType varchar(20),       --存款类型,活期/定活两便/定期
openDate datetime not null default getdate(), --开户日期,默认为系统当前日期
openMoney Money not null,      --开户金额,
balance money not null,       --余额,不低一元否则将稍户
pass varchar(50) default('888888')not null,  --密码,6位数字,
IsReportLoss char(10)not null default('否'), --是否挂失,否/是 默认为"否"
customerID int not null       --顾客编号表示该卡对应的顾客的编号,一位顾客允许办理多张卡
)

go
--`创建交易信息表`
create table transInfo
(
transDate datetime not null default getdate(), --交易日期,默认为当前日期
cardID varchar(50) not null,     --卡号,外键可重复索引
transType varchar(10) not null,     --交易类型,只能是存入或支取
transMoney money not null,      --交易金额,大于零
remark varchar(250),       --备注,可选输入,其他说明
)
go

在BANKDB数据库的表添加约束条件SQL

-- `用户表`
alter table userInfo  add constraint ck_PID check(len(PID)>15 or len(PID)>18)--身份证号只能是18或15位,
--联系电话
alter table userInfo add constraint CK_telephone check(telephone like '[0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
or telephone like '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

-- `银行信息表`
alter table cardInfo add constraint CK_cardID check(cardid like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')
alter table cardinfo add constraint DF_curType default('RMB') FOR CURTYPE --默认为RMB
ALTER table cardinfo add constraint CK_savingType check (savingType in('活期','定活两便','定期'))
ALTER table cardinfo add constraint CK_openMoney check (openMoney>=1) --不低于一元
ALTER table cardinfo add constraint CK_balance check (balance>=1)  --不低于一元
ALTER table cardinfo add constraint DF_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]') --开户时密码 
alter table cardInfo add constraint FK_customerID foreign key(customerID)references userInfo(customerID)

--`交易表`
--`只能是存入或支取`
alter table transInfo add constraint CK_transType check(transType in('存入','支取'))  --或者是(transType='存入' or  transType='支取')
alter table transinfo ADD constraint CK_transMoney check(transMoney>0)    --大于零
alter table transInfo add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)

在BANKDB数据库的表添加数据

--`插入数据`
go
    insert into userInfo(customerName,PID,telephone,address) values('张三','1234567890123451','010-67898978','北京海淀区')
    insert into userInfo(customerName,PID,telephone) values('李四','32145678912345678','0478-44443333')
go
    insert into cardinfo values('1010 3576 1212 1134','RMB','定期',default,1.00,5001.00,888888,0,2)
    insert into cardinfo values('1010 3576 1234 5678','RMB','活期',default,1000.00,1000.00,888888,0,1)
    
    --`查询cardinfo`
    select * from cardinfo 
go
    insert into transInfo(cardID,transDate,transType,transMoney)values('1010 3576 1212 1134',getdate(),'存入',5000.00)
go

在BANKDB数据库的表修改(修改密码)

场景1: 当张三取款9000时，会交易信息表(transinfo)中添加一条交易记录，同时应自动更新卡信息（cardinfo）中的现有余额（减少900元），先假定手动插入更新信息

--`声明变量`
declare @cardID varchar(50)
    select @cardID=cardID from cardInfo  where customerID=(select customerID from userInfo where customerName='张三')
    update cardinfo set balance=balance-900  where customerid =(select customerid from userinfo   where customerName ='张三')
  
    insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@cardID,'支取',900.00)
    --`查询cardinfo`
    select * from cardinfo

场景2: 修改密码
张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456，
李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123

update cardInfo set pass=case
 when cardid='1010 3576 1234 5678' then '123456'
 when cardid='1010 3576 1212 1134' then '123123'
end
select * from cardinfo

场景3: 银行卡挂失：李四（卡号1010 3576 1212 1134）因银行卡丢失，申请挂失

update cardinfo set IsReportLoss='是' where cardid='1010 3576 1212 1134'
select * from cardinfo

场景4: 统计银行和银行的资金流量通余和盈利结算。
存入代表资金流入，支取代表资金流出。资金流通金额=总存入量-总支取量假定存款利率为千分之8 盈利结算=总支取量x0.008-总存入量x0.003

declare @InMoney money, @OutMoney money
select @InMoney  = sum(transMoney) from transinfo where transType='存入'
SELEct @OutMoney = sum(transMoney)from transinfo where transType='支取'
print '银行流通余额总计为：'+convert(varchar(20),@InMoney-@OutMoney)+'RMB'
print '盈利结算为：'+convert(varchar(20),@OutMoney*0.008-@InMoney*0.003)+'RMB'

场景5: 查询本周开户的卡号，显示该卡相关信息

select * from cardinfo 
 where datename(wk,getdate())=datepart(wk,openDate)

select * from cardinfo
 where  datepart(wk,getdate())=datepart(wk,openDate)

场景6: 查询本月交易金额最高的卡号,在交易信息表中,采子查询和distinct去掉重复的卡号

select distinct卡号=cardid from transinfo
  where transMoney=(select max(transMoney)from transinfo
   where datepart(mm,transDate)=datepart(mm,getdate()))

场景7: 查询挂失账号的客户信息,利用子查询in 方式或内部inner join

select customerName as 客户姓名,联系电话=telephone from userinfo
  where customerId in(select customerID from cardinfo where IsReportLoss='是')

SELECT *FROM userinfo AS a INNER JOIN cardinfo AS p ON a.customerId=p.customerId where  IsReportLoss='是'

场景8: 催款提醒业务：例如某种的需要，每个月末，如果发现用户账上余额少于200元，将致电催款

select customerName as 客户姓名,联系电话=telephone,账上余额=balance
 from userInfo Inner Join cardInfo 
   on userinfo.customerId=cardInfo.customerId where balance<200

场景9: 创建索引和视图,表卡号cardId字段创建重复索引，以便加速查询，填充因子为70%

IF exists(select name from sysindexes where name='index_cardID')
drop index transInfo.index_cardID
go
create nonclustered index index_cardID
 on transInfo(cardid)
 with fillfactor=70
go

场景10:创建索引查询张三（卡号为1010 3576 1212 1134）的交易记录

select * from transInfo with(index =index_cardID)
 where cardid=(select cardid from cardinfo 
  where customerid=(select customerid from userinfo 
   where customerName='张三'))

场景11:创建视图：为了向客户显示信息友好，查询各表，要求字段全为中文字段名3个表 , 对应的视图为：view_userInfo 、view_cardInfo view_tranInfo

-- `view_userInfo 用户视图`
if exists(select * from sysobjects where name='view_userInfo')
drop view view_userInfo
go
create view view_userInfo
as
 select 客户编号=customerid,开户名=customername,身份证号=PID,电话号码=telephone,
  地址=address from userInfo
go
select * from view_userInfo


-- `view_cardInfo 卡信息视图`
if exists(select * from sysobjects where name='view_cardInfo')
    drop view view_cardInfo
go
create view view_cardInfo
as
  select  卡号=cardid,货币类型=curType, 存款类型=savingType,开户时间= openDate,
   开户金额=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID
  from cardinfo
go
select * from view_cardInfo

--`view_tranInfo 交易信息视图`
if exists(select * from sysobjects where name='view_tranInfo')
    drop view view_tranInfo
go
    create view view_tranInfo
as
    select 交易日期=getdate(),交易类型=transType,卡号=cardId,交易金额=transMoney, 备注=remark from transinfo 
go
    select * from view_tranInfo

场景12: 创建存储过程，1.取钱或存钱的存储过程proc_takeMoney
调用存储过程取钱或存钱，张三的卡号支取300（密码123456）,李四的卡号存入500

if exists(select * from sysobjects where name='proc_takeMoney')
drop proc proc_takeMoney
go
--`创建取款的存储过程`
create proc proc_takeMoney 
  @cardID char(22),
  @Money money,
  @type char(4),
  @inputPass varchar(6)=' ' 
as 
 declare @Mybalance money
 select @Mybalance=balance from cardInfo where cardid=@cardID

if (@type='支取')
begin
  if((select pass from cardinfo where cardID=@cardID)<>@inputPass)
   begin
  print '交易正进行,请稍后.....'
   raiserror('密码错误',16,1)
  return
   end
if(@Mybalance<=@Money) 
  begin
  raiserror('交易失败,余额不足',16,1)
   print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
  return 
  end 
else
  begin
  update cardinfo set balance=balance-@Money where cardID=@cardID
    print '交易成功，交易金额为'+convert(varchar(20),@Money)
  print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
  end
end
else
 begin
  update cardinfo set balance=balance+@Money where cardID=@cardID
   print '交易成功!交易金额为'+convert(varchar(10),@Money)
  print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
 end
--`插入数据`
insert into transInfo(transDate,cardID,transType,transMoney)
values(getdate(),@cardID,@type,@Money)
go

--`显示相关信息`
go
declare @cardID varchar(25),@balance money
select @cardID=cardid,@balance=balance from cardinfo 
  where customerid=(select customerid from userinfo 
   where customerName='张三')
exec proc_takeMoney @cardID,300,'支取','123456' --执行存储过程

--declare @cardID varchar(25),@balance money
select @cardID=cardid,@balance=balance from cardinfo 
 where customerid=(select customerid from userinfo 
  where customerName='李四')
--`插入记录`
exec proc_takeMoney @cardID,500,'存入',' '   --执行存储过程
go
select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

场景13: 产生随机数卡号存储过程proc_randCardID

if exists(select * from sysobjects where name='proc_randCardID')
drop proc proc_randCardID
go
create proc proc_randCardID
 @randCardID char(25)output    --输出参数
as 
 declare @R numeric(15,8),@tempStr varchar(20) --15位数,保留8位小数
 select @R=rand((datepart(mm,getdate())*100000)+
 (datepart(ss,getdate())*1000)+datepart(ms,getdate()))
set @tempStr=convert(varchar(25),@R)  --存放随机数
set @randCardID='1010 3576'+' '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)--截取
go

--`执行存储过程`
declare @RandID char(19)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应

场景14: 开户的存储过程Proc_openAccount

if exists(select * from sysobjects where name='Proc_openAccount')
drop proc Proc_openAccount
go
create proc Proc_openAccount
 @customerName varchar(20),
 @PID char(20),
 @telephone varchar(20),
 @openMoney money,
 @savingType varchar(20),
 @address varchar(100)=' '　　
as
declare @RandID char(19)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应
 while exists(select * from cardInfo where cardID=@RandID)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应

print '尊敬的客户,开户成功!系统为你产生的随机数卡号为: '+@RandID
print '开户日期'+convert(varchar(10),getdate(),111)+'开户金额为:'+convert(varchar(10),@openMoney)
--`插入数据`
insert into userInfo(customerName,PID,telephone,address)values(@customerName,@PID,@telephone,@address)
--`声明变量`
declare @customerID int 
select @customerID=customerID from userinfo where PID=@PID
exec proc_randCardID @RandID output 
--`插入数据`
insert into cardinfo(cardid,curType,savingType,openDate,openMoney,balance,pass,customerID)
values(@RandID,default,@savingType,getdate(),@openMoney,@openMoney,default,@customerID)
go
--`执行存储过程`
exec Proc_openAccount '王五','3344568890126780','2222-63598978',1000,'活期','河南新乡'
exec Proc_openAccount '赵二','213445678912342222','2222-44446666',1,'定期',' '

select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

场景15: 创建事务,同银行的帐号间一般都支持功能，因为转帐的规则是：一方支取另一方存入相应金额。这两步要么同时成立要么同时失败所以采用事务处理。如果任何一方失败可以撤消操作，现模拟从李四转帐2000到张三的帐号上要求显示转帐结果；是否转帐成功，转帐双方金额，采用存储过程，需要三个参数转出方卡号和转帐金额

create proc proc_transfer 
 @card1 char(19),
 @card2 char(19),
 @outMoney money 
as
--`开始事务`
begin tran
print '开始转账，请稍后........'
declare @error int
 set @error=0  --初始化
exec proc_takeMoney @card1,@outMoney,'支取','123123'  --调用取款存储过程
 set @error=@error+@@error
exec proc_takeMoney @card2,@outMoney,'存入'
 set @error=@error+@@error
if(@error>0)
 begin 
  print '交易失败'
  rollback tran 
 end
else 
 begin
  print '交易成功，交易金额：'+convert(varchar(9),@outMoney)
  commit tran
 end
go

--`执行`
--`从李四的账号转账2000元到张三`
declare @card1 varchar(22),@card2 varchar(22)
    select @card1=cardid from cardinfo inner join userinfo  on userinfo.customerid=cardinfo.customerid where userinfo.customerName='李四'
    select @card2=cardid from cardinfo inner join userinfo  on userinfo.customerid=cardinfo.customerid where userinfo.customerName='张三'
exec proc_transfer @card1,@card2,2000

select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

场景16: 创建登录账号和数据库用户.sql登录账号Admin密码为1234作为系统维护的账号具有对上述3个表增、删、改、查的权限阶段

exec sp_addlogin 'Admin','1234' --SQL Server 
exec sp_grantdbaccess 'Admin','sysAdminDBUser'
grant select,insert,update,delete on userInfo to  sysAdminDBUser
grant select,insert,update,delete on cardInfo to  sysAdminDBUser
grant select,insert,update,delete on transInfo to  sysAdminDBUser

7、总结

此文从整体来以银行ATM取款机系统项目案例从建库，建表，添加索引，存储过程，视图、插数据，创建用户，授权等操作，以实际场景演练了一遍。
此案例是在N久年前在学习课本中老师布置一道作业，通过自身完成的实操，仅供参考学习,非某个银行ATM的数据库SQL脚本，此文章属于本人原创可以转载或收藏

银行ATM取款机系统项目案例SQL实操

1、前言与目的

2、学习三部曲

3、什么是SQL

4、为什么要SQL

5、怎么使用SQL

6 、SQL实操场景使用

SQLServer 系统数据库表

SQLServer 常用关键字

创建bankDB数据库

切换到BANKDB数据库

在BANKDB数据库检测表是否存在

在BANKDB数据库创建表SQL

在BANKDB数据库的表添加约束条件SQL

在BANKDB数据库的表添加数据

在BANKDB数据库的表修改(修改密码)

7、总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Oracle数据库管理之道

读书

Java 杂谈

MySQL