银行 atm （取款机）系统数据库设计.sql

ATM sql设计.rar ATM sql设计.rar

use master
go

--创建目录
exec xp_cmdshell 'md D:\bank',NO_OUTPUT            
go

if exists (select * from sysdatabases where name='bankDB')
	drop database bankDB
go

------------创建数据库-------------
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,	--联系电话
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'



/**------------常规业务摸拟--------------------**/

--修改密码
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'

--统计银行的资金流通余额和盈利结算

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 '盈利结算为：'+convert(varchar(20),(@main_out*0.003)-(@main_com*0.008))+'RMB'

--查询本周开户的卡号，显示该卡相关信息
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 联系电话 from userInfo
	where customerID in (select customerID from cardInfo where IsReportLoss=1)

--催款提醒业务
select customerName as 客户姓名,telephone as 联系电话,balance as 帐上余额
from userInfo inner join cardInfo
on (userInfo.customerID=cardInfo.customerID)
where balance<200

/**---------------创建视图和索引-------------------**/

--给交易表的卡号cardID 创建索引
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'

--创建视图
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

/**--------------------------------创建触发器-------------------------**/

create trigger trig_trans
on transInfo
for insert 
as
begin transaction

					--开始事务
	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 	--回滚事务
				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 	--提交事务
				end
		end

	else if(@myTransType<>'存入' and @myTransType<>'支取')
		begin
		
			print '当前系统故障！无法交易，请重试交易！'
			rollback transaction	--回滚事务
		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	--提交事务
		end
go


/**------------测试触发器--------------**/
--张三卡号支取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

/**-----------创建存储过程---------------**/

--取钱或存钱的存储过程

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
--产生随机卡号的存储过程
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  '系统故障！请重试...'
			rollback transaction
		end

end
go


--开户
execute proc_openAccount @openName='王五',@openPID='334456889012678',@openCall='2222-63598978',@openAddress='河南新乡',@openType='活期',@openMoneys=1000


execute proc_openAccount @openName='赵二',@openPID='213445678912342222',@openCall='0760-44446666',@openAddress=' ',@openType='定期',@openMoneys=1

/**--------------------创建事务---------------------**/

alter procedure proc_transfer 
@card1 varchar(25),@card2 varchar(25),@outMoney	money
as
begin
	print '开始转帐，请稍后...'
	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 '转帐成功!'
			commit transaction
		end
	else
		begin
			print '转帐失败!'
			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

/**------------------------创建登陆帐号和数据库用户---------------------**/

use bankDB
go

execute sp_addlogin 'sysAdmin','1234'	--添加 SQL 登陆帐号

execute sp_grantdbaccess 'sysAdmin','sysAdminDBUser'	--创建数据库用户(角色)

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 --交易信息表