银行ATM工作原理大揭秘！| SQL数据库分解案例

通过本文，你可以学到：

• 会利用SQL操作关系数据库进行查询数据。
• 使用SQL操作存储过程（创建、删除）等操作。
• 对于数据库表的增、删、改、约束、索引，视图，触发器等操作。

注意：以上场景只是列了部分，在实际工作或学习过程中的场景会更多，就不列举。比如：使用SQL进行数据统计,报表的统计等操作。

一.学习SQL的意义

学习SQL的意义就在于你学会以后，将掌握数据分析应聘中的SQL能力，并找到一份数据分析工作。并且，在深入了解SQL之后还可以提高自己的认知度，比如：工作中不论是建表（建模）或是开发工作中，经过自身的学习，在不同的场景下使用它肯定会有意想不到的收获与喜悦。

二.什么是SQL

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

三.SQL实操场景使用

经过了解之后，咱们以银行ATM取款机系统项目案例实操进行分解。

SQLServer版本为：SQL Server 2005

1.SQLServer 系统数据库表

2.SQLServer 常用关键字

3.创建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:ank" 调用DOS创建文件夹`

--`创建一个名称为bankDB数据库`

create database bankDB

ON

(

name="bankDB_data",

filename="D:ankankDB_data.mdf", --文件存储到操作系统D盘的bank

size=5mb, -- 分配5兆

filegrowth=15% -- 文件以百分之15增长

)

log on

(

name="bankDB_log",

filename="D:ankankDB_log.ndf",

size=5mb,

filegrowth=15%

)

go

• 切换到BANKDB数据库

USE BANKDB

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

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

四.总结

此文从整体来以银行ATM取款机系统项目案例从建库，建表，添加索引，存储过程，视图、插数据，创建用户，授权等操作，以实际场景演练了一遍，希望对大家有帮助。

End.

作者：寅务

来源：简书

本文为转载分享，如有侵权请联系后台删除