数据库优秀课程设计银行储蓄系统完全代码.docx

数据库课程设计完全代码-建库create database Bankon primary(name = Bank,filename=D:projectBank.mdf,size = 5,maxsize = 100,filegrowth = 10%)log on(name = Bank_log,filename = D:projectBank_log.ldf,size=2,filegrowth=1)go-建表use Bankcreate table Depositors(BNo varchar(20) primary key, -账号BName varchar(20) not null, -姓名BPassword char(6) not null check(len(BPassword) = 6), -密码BID varchar(20) not null, -身份证号BSex char(2) not null check(BSex = 男 or BSex = 女), -性别BStyle varchar(20) not null check(BStyle = 活期存款 or BStyle = 定时存款), -业务类型BDate datetime not null, -开户时间BYear int not null check(BYear = 0 or BYear = 1 or BYear = 2 or BYear = 3), -存款期限,0表示活期BMoney decimal(10,4) not null check(BMoney = 0) -账户余额)create table CurrentAccounts(nID int primary key identity(1,1), -流水号BNo varchar(20) not null references Depositors(BNo), -账号BName varchar(20) not null, -姓名BStyle varchar(20) not null check(BStyle = 活期存款 or BStyle = 活期取款), -操作类型BCash decimal(10,4) null check(BCash = 0), -操作金额BDate datetime not null, -操作时间BInterest decimal(10,4) null check(BInterest = 0), -利息BMoney decimal(10,4) not null check(BMoney = 0), -账户余额)create table FixedAccounts(nID int primary key identity(1,1), -流水号BNo varchar(20) not null references Depositors(BNo), -账号BName varchar(20) not null, -姓名BStyle varchar(20) not null check(BStyle = 定时存款 or BStyle = 定时取款), -操作类型BMoney decimal(10,4) not null check(BMoney = 0), -存取金额BYear int not null check(BYear = 1 or BYear = 2 or BYear = 3), -存款期限BDate datetime not null -存款时间插入触发器create trigger InsertIntoCAorFA on Depositorsafter insertasdeclare year intselect year = BYear from insertedif year = 0insert into CurrentAccounts(BNo,BName,BStyle,BDate,BMoney) select BNo,BName,BStyle,BDate,BMoney from insertedelseinsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) select BNo,BName,BStyle,BMoney,BYear,BDate from inserted删除触发器create trigger DeleteFromCAorFA on Depositorsinstead of deleteasdeclare no varchar(20)select no = BNo from deleteddelete from CurrentAccounts where BNo = nodelete from FixedAccounts where BNo = nodelete from Depositors where BNo = no（1）开户登记&（2）定时存款insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10001,张三,123456,男,活期存款,-01-01,0,10000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10002,李四,123456,男,活期存款,-01-02,0,0)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10003,王五,123456,男,定时存款,-01-03,2,30000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney) values(10004,小丽,123456,女,定时存款,-01-04,3,40000)create view ViewOfCurrentAccounts -参考asselect BNo 账号,BName 姓名,BStyle 操作类型,BCash 操作金额,BDate 操作时间,BInterest 利息,BMoney 账户余额from CurrentAccountsselect * from Depositorsselect * from CurrentAccountsselect * from FixedAccounts（3）定时取款create procedure FixedWithdrawNo varchar(20),Date datetimeasif(select BYear from FixedAccounts where BNo = No)= 1)beginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360)begininsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(No,(select BName from FixedAccounts where BNo = No),定时取款,(select BMoney from FixedAccounts where BNo = No)*1.0275,1,Date) -利息计算select * from FixedAccounts where BNo = Noend elseprint 定时存款未满十二个月！endelse if(select BYear from FixedAccounts where BNo = No)= 2)beginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360*2)begininsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(No,(select BName from FixedAccounts where BNo = No),定时取款,(select BMoney from FixedAccounts where BNo = No)*power(1.035,2),2,Date)select * from FixedAccounts where BNo = Noendelseprint 定时存款未满两年！endelsebeginif(select datediff(day,(select BDate from FixedAccounts where BNo = No),Date) 360*3)begininsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate) values(No,(select BName from FixedAccounts where BNo = No),定时取款,(select BMoney from FixedAccounts where BNo = No)*power(1.04,3),3,Date)select * from FixedAccounts where BNo = Noendelseprint 定时存款未满三年！endexec FixedWithdraw 10003,-01-04 -取款（4）&（5）活期存取款create proc CurrentWithdrawNo varchar(20),Money float,Date datetimeasdeclare temp decimal(10,4)select temp = (select datediff(day,(select max(BDate) from CurrentAccounts where BNo = No),Date)/360.0*0.0035+1)*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp)+Money -目前余额if(Money 0) -存款begininsert into CurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)values(No,(select distinct BName from CurrentAccounts where BNo = No),活期存款,Money,Date,(select datediff(day,(select max(BDate) from CurrentAccounts where BNo = No),Date)/360.0*0.0035*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp), -（6）利息计算temp)select * from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp) -显示存款统计endelse -取款if(abs(Money) temp)print 余额不足！elsebegininsert into CurrentAccounts(BNo,BName,BStyle,BCash,BDate,BInterest,BMoney)values(No,(select distinct BName from CurrentAccounts where BNo = No),活期取款,abs(Money),Date,(select datediff(day,(select max(BDate) from CurrentAccounts where BNo = No),Date)/360.0*0.0035*(select BMoney from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp),temp)select * from CurrentAccounts where nID = (select max(temp.nID) from (select nID from CurrentAccounts where BNo = No) as temp) -显示取款统计endexec CurrentWithdraw 10001,5000,-03-30 -存款exec CurrentWithdraw 10001,-5000,-05-30 -取款exec CurrentWithdraw 10001,5000,-07-30 -存款exec CurrentWithdraw 10001,-0,-08-30 -取款，返回消息：余额不足！（7）活期明细create proc DetailOfCurrentAccount -活期明细no varchar(20)asselect * from CurrentAccounts where BNo = noexec DetailOfCurrentAccount 10001定时明细create proc DetailOfFixedAccount -定时明细no varchar(20)asselect * from FixedAccounts where BNo = noexec DetailOfFixedAccount 10003（8）数据库备份和恢复使用图形化界面操作即可