数据库课程设计完全代码
--建库
createdatabase Bank onprimary (
name ='Bank',
filename='D:\\project\\Bank.mdf', size = 5, maxsize= 100, filegrowth= 10% ) logon (
name ='Bank_log',
filename='D:\\project\\Bank_log.ldf', size=2, filegrowth=1 ) go
--建表 use Bank
createtable Depositors(
BNovarchar(20)primarykey,--账号 BNamevarchar(20)notnull,--姓名
BPasswordchar(6)notnullcheck(len(BPassword)= 6),--密码 BID varchar(20)notnull,--身份证号
BSexchar(2)notnullcheck(BSex='男'orBSex='女'),--性别
BStylevarchar(20)notnullcheck(BStyle='活期存款'orBStyle='定期存款'),--业务类型 BDatedatetimenotnull,--开户时间
BYearintnotnullcheck(BYear= 0 orBYear= 1 orBYear= 2 orBYear= 3),--存款期限,0表示活期 BMoneydecimal(10,4)notnullcheck(BMoney>= 0)--账户余额 )
createtableCurrentAccounts(
nIDintprimarykeyidentity(1,1),--流水号
BNovarchar(20)notnullreferences Depositors(BNo),--账号 BNamevarchar(20)notnull,--姓名
BStylevarchar(20)notnullcheck(BStyle='活期存款'orBStyle='活期取款'),--操作类型
BCashdecimal(10,4)nullcheck(BCash>= 0),--操作金额 BDatedatetimenotnull,--操作时间
BInterestdecimal(10,4)nullcheck(BInterest>= 0),--利息 BMoneydecimal(10,4)notnullcheck(BMoney>= 0),--账户余额 )
createtableFixedAccounts(
nIDintprimarykeyidentity(1,1),--流水号
BNovarchar(20)notnullreferences Depositors(BNo),--账号 BNamevarchar(20)notnull,--姓名
BStylevarchar(20)notnullcheck(BStyle='定期存款'orBStyle='定期取款'),--操作类型 BMoneydecimal(10,4)notnullcheck(BMoney>= 0),--存取金额
BYearintnotnullcheck(BYear= 1 orBYear= 2 orBYear= 3),--存款期限 BDatedatetimenotnull--存款时间
插入触发器
createtriggerInsertIntoCAorFAon Depositors afterinsert as
declare @year int
select @year =BYearfrom inserted if @year = 0
insertintoCurrentAccounts(BNo,BName,BStyle,BDate,BMoney)selectBNo,BName,BStyle,BDate,BMoneyfrom inserted else
insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)selectBNo,BName,BStyle,BMoney,BYear,BDatefrom inserted
删除触发器
createtriggerDeleteFromCAorFAon Depositors insteadofdelete as
declare @no varchar(20) select @no =BNofrom deleted
deletefromCurrentAccountswhereBNo= @no deletefromFixedAccountswhereBNo= @no deletefrom Depositors whereBNo= @no
（1）开户登记&（2）定期存款
insertinto
Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10001,'张三',123456,1405115001,'男','活期存款','2016-01-01',0,10000) insertinto
Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10002,'李四',123456,1405115002,'男','活期存款','2016-01-02',0,20000) insertinto
Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10003,'王五',123456,1405115003,'男','定期存款','2016-01-03',2,30000) insertinto
Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10004,'小丽',123456,1405115004,'女','定期存款','2016-01-04',3,40000)
createviewViewOfCurrentAccounts--参考 as
selectBNo账号,BName姓名,BStyle操作类型,BCash操作金额,BDate操作时间,BInterest利息,BMoney账户余额
fromCurrentAccounts
select*from Depositors select*fromCurrentAccounts select*fromFixedAccounts
（3）定期取款
createprocedureFixedWithdraw @No varchar(20), @Date datetime as
if((selectBYearfromFixedAccountswhereBNo= @No)= 1) begin
insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)values(@No,(selectBNamefromFixedAccountswhereBNo= @No),'定期取款',(selectBMoneyfromFixedAccountswhereBNo= @No)*1.0275,1,@Date)--利息计算
if((selectdatediff(day,(selectBDatefromFixedAccountswhereBNo= @No),@Date))> 360) begin
end
end
select*fromFixedAccountswhereBNo= @No
else
print'定期存款未满一年！'
elseif((selectBYearfromFixedAccountswhereBNo= @No)= 2) begin
insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)values(@No,(selectBNamefromFixedAccountswhereBNo= @No),'定期取款',(selectBMoneyfromFixedAccountswhereBNo= @No)*power(1.035,2),2,@Date) end else begin
insertintoFixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)values(@No,(selectBNamefromFixedAccountswhereBNo= @No),'定期取款',(selectBMoneyfromFixedAccountswhereBNo= @No)*power(1.04,3),3,@Date) end
execFixedWithdraw10003,'2018-01-04'--取款
end else
print'定期存款未满三年！'
select*fromFixedAccountswhereBNo= @No
if((selectdatediff(day,(selectBDatefromFixedAccountswhereBNo= @No),@Date))> 360*3) begin end else
print'定期存款未满两年！'
select*fromFixedAccountswhereBNo= @No
if((selectdatediff(day,(selectBDatefromFixedAccountswhereBNo= @No),@Date))> 360*2) begin
（4）&（5）活期存取款
createprocCurrentWithdraw
@No varchar(20), @Money float, @Date datetime as
declare @temp decimal(10,4)
select @temp =(((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo= @No),@Date))/360.0*0.0035+1)*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from (selectnIDfromCurrentAccountswhereBNo= @No)as temp)))+@Money --当前余额 if(@Money > 0)--存款 begin
insertintoCurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)
values(@No,
(selectdistinctBNamefromCurrentAccountswhereBNo= @No), '活期存款', @Money, @Date,
((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo=
@No),@Date))/360.0*0.0035*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from (selectnIDfromCurrentAccountswhereBNo= @No)as temp))),--（6）利息计算 end
else--取款
if(abs(@Money)> @temp)
print'余额不足！' else begin
insertintoCurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)
values(@No,
(selectdistinctBNamefromCurrentAccountswhereBNo= @No), '活期取款', abs(@Money), @Date,
((selectdatediff(day,(selectmax(BDate)fromCurrentAccountswhereBNo=
@temp)
select*fromCurrentAccountswherenID=(selectmax(temp.nID)from
(selectnIDfromCurrentAccountswhereBNo= @No)as temp)--显示存款记录
@No),@Date))/360.0*0.0035*(selectBMoneyfromCurrentAccountswherenID=(selectmax(temp.nID)from (selectnIDfromCurrentAccountswhereBNo= @No)as temp))), end
@temp)
select*fromCurrentAccountswherenID=(selectmax(temp.nID)from
(selectnIDfromCurrentAccountswhereBNo= @No)as temp)--显示取款记录
execCurrentWithdraw10001,5000,'2016-03-30'--存款
execCurrentWithdraw10001,-5000,'2016-05-30'--取款 execCurrentWithdraw10001,5000,'2016-07-30'--存款
execCurrentWithdraw10001,-20000,'2016-08-30'--取款，返回消息：余额不足！
（7） 活期明细
createprocDetailOfCurrentAccount--活期明细 @no varchar(20) as
select*fromCurrentAccountswhereBNo= @no
execDetailOfCurrentAccount10001
定期明细
createprocDetailOfFixedAccount--定期明细 @no varchar(20) as
select*fromFixedAccountswhereBNo= @no
execDetailOfFixedAccount10003
（8）数据库备份与恢复使用图形化界面操作即可