?? 銀行 atm （取款機）系統(tǒng)數(shù)據(jù)庫設(shè)計.sql
字號:
use master
go
--創(chuàng)建目錄
exec xp_cmdshell 'md D:\bank',NO_OUTPUT
go
if exists (select * from sysdatabases where name='bankDB')
drop database bankDB
go
------------創(chuàng)建數(shù)據(jù)庫-------------
create database bankDB
on
(
name='bank_Data',
filename='D:\bank\bank_Data.mdf',
size=1GB,
maxSize=10GB,
filegrowth=15%
)
log on
(
name='bank_Log',
filename='D:\bank\bank_Log.ldf',
size=50MB,
maxSize=5GB,
filegrowth=5%
)
go
use bankDB
go
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
go
------------------------用戶信息表 (userInfo)----------------------
create table userInfo
(
customerID int identity(1,1) not null, --顧客編號
customerName varchar(10) not null, --開戶名
PID varchar(18) not null, --身份證號
telephone varchar(20) not null, --聯(lián)系電話
address varchar(50) null, --居住地址
--約束
constraint PK_customerID primary key(customerID),
constraint UQ_PID unique (PID),
constraint CK_PID check(PID 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][0-9][0-9][0-9]' or PID 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][0-9][0-9][0-9][0-9][0-9][0-9]'),
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]' 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]')
)
use bankDB
go
if exists (select * from sysobjects where name='cardInfo')
drop table cardInfo
go
---------------------------銀行卡信息表 (cardInfo)-----------------------------
create table cardInfo
(
cardID varchar(25) not null, --卡號
curType varchar(20) not null --貨幣種類
constraint DF_curType default 'RMB',
savingType varchar(10) null, --存款類型
openDate dateTime not null --開戶日期
constraint DF_openDate default getDate(),
openMoney money not null, --開戶金額
balance money not null, --余額
pass varchar(6) not null --密碼
constraint DF_pass default '888888',
IsReportLoss char(2) not null --是否掛失
constraint DF_IsReportLoss default '否',
customerID int not null, --客戶編號
--約束
constraint PK_cardID primary key(cardID),
constraint CK_cardID check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
constraint CK_openMoney check(openMoney>=1),
constraint CK_balance check(balance>=1),
constraint FK_customerID foreign key(customerID) references userInfo(customerID)
)
go
use bankDB
go
if exists (select * from sysobjects where name='transInfo')
drop table transInfo
go
---------------------------交易信息表 (transInfo)-----------------------------
create table transInfo
(
transDate dateTime not null --交易日期
constraint DF_transDate default getdate(),
cardID varchar(25) not null, --卡號
transType varchar(4) not null, --交易類型
transMoney money not null, --交易金額
remark varchar(50) null, --備注
--約束
constraint FK_cardID foreign key(cardID) references cardInfo(cardID),
constraint CK_transType check(transType in ('存入','支取')),
constraint CK_transMoney check(transMoney>0)
)
/**-------------------------開戶-------------------------**/
insert into userInfo(customerName,PID,telephone,address)
values ('張三','123456789012345','010-67898978','北京海淀')
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,pass,IsReportLoss,customerID)
values ('1010 3576 1234 5678',default,'活期',default,1000,1000,default,0,1)
insert into userInfo(customerName,PID,telephone)
values ('李四','321245678912345678','0478-44443333')
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,pass,IsReportLoss,customerID)
values ('1010 3576 1212 1134',default,'定期',default,1,1,default,0,2)
/**---------------------取款和存款-------------------------**/
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,'1010 3576 1234 5678','支取',900)
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,'1010 3576 1212 1134','存入',5000)
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'
/**------------常規(guī)業(yè)務(wù)摸擬--------------------**/
--修改密碼
update cardInfo set pass='123456' where cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' where cardID='1010 3576 1212 1134'
--掛失帳號
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
--統(tǒng)計銀行的資金流通余額和盈利結(jié)算
declare @main_com money,@main_out money
select @main_com=sum(transMoney) from transInfo where transType='存入'
select @main_out=sum(transMoney) from transInfo where transType='支取'
print '銀行流通余額總計為：'+convert(varchar(20),@main_com-@main_out)+'RMB'
print '盈利結(jié)算為：'+convert(varchar(20),(@main_out*0.003)-(@main_com*0.008))+'RMB'
--查詢本周開戶的卡號，顯示該卡相關(guān)信息
select * from cardInfo
where datediff(wk,openDate,getDate())=0
--查詢本月交易金額最高的卡號
select distinct cardID from transInfo
where transMoney=(select max(transMoney) from transInfo)
--查詢掛失帳號的客戶信息
select customerName as 客戶姓名,telephone as 聯(lián)系電話 from userInfo
where customerID in (select customerID from cardInfo where IsReportLoss=1)
--催款提醒業(yè)務(wù)
select customerName as 客戶姓名,telephone as 聯(lián)系電話,balance as 帳上余額
from userInfo inner join cardInfo
on (userInfo.customerID=cardInfo.customerID)
where balance<200
/**---------------創(chuàng)建視圖和索引-------------------**/
--給交易表的卡號cardID 創(chuàng)建索引
create index index_transInfo_cardID
on transInfo(cardID)
--按指定索引查詢 張三 (卡號為 1010 3576 1212 1134) 的交易記錄
select * from transInfo(index=index_transInfo_cardID) where cardID='1010 3576 1212 1134'
--創(chuàng)建視圖
create view view_userInfo
as
select customerID as 客戶編號,customerName as 客戶姓名,PID as 身份證號,telephone as 電話號碼,address as 居住地址
from userInfo
go
create view view_cardInfo
as
select cardID as 卡號,curType as 貨幣種類,savingType as 存款類型,openDate as 開戶日期,openMoney as 開戶金額,balance as 余額,pass as 密碼,IsReportLoss as 是否掛失,customerID as 客戶編號
from cardInfo
go
create view view_transInfo
as
select transDate as 交易日期,transType as 交易類型,cardID as 卡號,remark as 備注
from transInfo
go
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
/**--------------------------------創(chuàng)建觸發(fā)器-------------------------**/
create trigger trig_trans
on transInfo
for insert
as
begin transaction
--開始事務(wù)
declare @myTransType char(5),@outMoney money,@myCardID varchar(25),@mybalance money
select @myTransType=transType,@outMoney=transMoney,@myCardID=cardID from inserted
select @mybalance=balance from cardInfo where cardID=@myCardID
if(@myTransType='支取')
begin
if(@mybalance-@outMoney<1)
begin
print '交易失敗！余額不足!'
print '卡號：'+@myCardID+' '+'余額：'+convert(varchar(20),@mybalance)
rollback transaction --回滾事務(wù)
end
else
begin
update cardInfo set balance=balance-@outMoney where cardID=@myCardID
print '交易成功!交易金額：'+convert(varchar(20),@outMoney)
print '卡號：'+@myCardID+' '+'余額：'+convert(varchar(20),@mybalance-@outMoney)
commit transaction --提交事務(wù)
end
end
else if(@myTransType<>'存入' and @myTransType<>'支取')
begin
print '當(dāng)前系統(tǒng)故障！無法交易，請重試交易！'
rollback transaction --回滾事務(wù)
end
else
begin
update cardInfo set balance=balance+@outMoney where cardID=@myCardID
print '交易成功！交易金額：'+convert(varchar(20),@outMoney)
print '卡號：'+@myCardID+' '+'余額：'+convert(varchar(20),@mybalance+@outMoney)
commit transaction --提交事務(wù)
end
go
/**------------測試觸發(fā)器--------------**/
--張三卡號支取1000
declare @id varchar(25)
select @id=cardID from cardInfo
where customerID in (select customerID from userInfo where customerName='張三')
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,@id,'支取',1000)
go
--李四卡號存放 200
declare @id varchar(25)
select @id=cardID from cardInfo
where customerID in (select customerID from userInfo where customerName='李四')
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,@id,'存入',200)
go
/**-----------創(chuàng)建存儲過程---------------**/
--取錢或存錢的存儲過程
create procedure proc_takeMoney
@id varchar(25),@pass varchar(6),@type varchar(5),@money money
as
begin
if (@type='支取')
begin
if(@pass in (select pass from cardInfo where cardID=@id))
begin
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,@id,@type,@money)
end
else
begin
print '密碼不正確!交易失敗!'
end
end
else
begin
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,@id,@type,@money)
end
end
go
--張三卡號支取300
declare @id varchar(25)
select @id=cardID from cardInfo
where customerID in (select customerID from userInfo where customerName='張三')
execute proc_takeMoney @id,123456,'支取',300
go
--李四卡號存放 500
declare @id varchar(25)
select @id=cardID from cardInfo
where customerID in (select customerID from userInfo where customerName='李四')
execute proc_takeMoney @id,' ','存入',500
go
--產(chǎn)生隨機卡號的存儲過程
create procedure proc_randCardID
@randCardID varchar(25) output
as
begin
declare @r numeric(15,8),@string varchar(10)
set @r=rand((datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+(datepart(ms,getdate())))
set @string=convert(varchar(10),@r)
set @randCardID='1010 3576 '+substring(@string,3,4)+' '+substring(@string,7,4)
end
go
declare @mycardID varchar(25)
execute proc_randCardID @randCardID=@mycardID output
print @mycardID
go
--開戶的存儲過程
alter procedure proc_openAccount
@openName varchar(10),
@openPID varchar(18),
@openCall varchar(20),
@openAddress varchar(50)=' ',
@openType varchar(10),
@openMoneys money
as
begin
begin transaction --開始
declare @id int,@sumerror int,@mycardID varchar(25)
set @sumerror=0
execute proc_randCardID @randCardID=@mycardID output
insert into userInfo(customerName,PID,telephone,address)
values (@openName,@openPID,@openCall,@openAddress)
set @sumerror=@sumerror+@@error
select @id=max(customerID) from userInfo
while exists (select * from cardInfo where cardID=@mycardID)
execute proc_randCardID @randCardID=@mycardID output
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,pass,IsReportLoss,customerID)
values (@mycardID,default,@openType,default,@openMoneys,@openMoneys,default,default,@id)
set @sumerror=@sumerror+@@error
if(@sumerror=0)
begin
print '尊敬的客戶，開戶成功！您的卡號為：'+@mycardID
print '開戶日期：'+convert(varchar(10),getdate(),111)+' '+'開戶金額：'+convert(varchar(20),@openMoneys)
commit transaction
end
else
begin
print '系統(tǒng)故障！請重試...'
rollback transaction
end
end
go
--開戶
execute proc_openAccount @openName='王五',@openPID='334456889012678',@openCall='2222-63598978',@openAddress='河南新鄉(xiāng)',@openType='活期',@openMoneys=1000
execute proc_openAccount @openName='趙二',@openPID='213445678912342222',@openCall='0760-44446666',@openAddress=' ',@openType='定期',@openMoneys=1
/**--------------------創(chuàng)建事務(wù)---------------------**/
alter procedure proc_transfer
@card1 varchar(25),@card2 varchar(25),@outMoney money
as
begin
print '開始轉(zhuǎn)帳，請稍后...'
declare @counterror int
set @counterror=0
begin transaction --開始
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,@card1,'支取',@outMoney)
set @counterror=@counterror+@@error
insert into transInfo(transDate,cardID,transType,transMoney)
values (default,@card2,'存入',@outMoney)
set @counterror=@counterror+@@error
if(@counterror=0)
begin
print '轉(zhuǎn)帳成功!'
commit transaction
end
else
begin
print '轉(zhuǎn)帳失敗!'
rollback transaction
end
end
go
declare @id1 varchar(25),@id2 varchar(25)
select @id1=cardID from cardInfo
where customerID in (select customerID from userInfo where customerName='張三')
select @id2=cardID from cardInfo
where customerID in (select customerID from userInfo where customerName='李四')
execute proc_transfer @card1=@id2,@card2=@id1,@outMoney=2000
go
select * from view_cardInfo
select * from view_transInfo
go
/**------------------------創(chuàng)建登陸帳號和數(shù)據(jù)庫用戶---------------------**/
use bankDB
go
execute sp_addlogin 'sysAdmin','1234' --添加 SQL 登陸帳號
execute sp_grantdbaccess 'sysAdmin','sysAdminDBUser' --創(chuàng)建數(shù)據(jù)庫用戶(角色)
grant select,insert,update,delete on transInfo to sysAdminDBUser
grant select,insert,update,delete on userInfo to sysAdminDBUser
grant select,insert,update,delete on cardInfo to sysAdminDBUser
-----------------------------------------------------------------------------
select * from userInfo --帳戶信息表
select * from cardInfo --銀行卡信息表
select * from transInfo --交易信息表
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -