数据库课程设计完全代码--建库create database Bankon primary(name ='Bank',filename='D:\project\Bank.mdf',size = 5,maxsize = 100,filegrowth = 10%)log on(name ='Bank_log',filename='D:\project\Bank_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)selectBNo,BName,BStyle,BDate,BMoney from insertedelseinsert into FixedAccounts(BNo,BName,BStyle,BMoney,BYear,BDate)selectBNo,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,1405115001,'男','活期存款','2016-01-01',0,10000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10002,'李四',123456,1405115002,'男','活期存款','2016-01-02',0,20000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10003,'王五',123456,1405115003,'男','定期存款','2016-01-03',2,30000)insert into Depositors(BNo,BName,BPassword,BID,BSex,BStyle,BDate,BYear,BMoney)values(10004,'小丽',123456,1405115004,'女','定期存款','2016-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 FixedWithdraw@No 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 = @Noendelseprint'定期存款未满一年！'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 FixedWithdraw10003,'2018-01-04' --取款（4）&（5）活期存取款create proc CurrentWithdraw@No 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 =(selectmax(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 =(selectmax(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 CurrentWithdraw10001,5000,'2016-03-30' --存款exec CurrentWithdraw10001,-5000,'2016-05-30' --取款exec CurrentWithdraw10001,5000,'2016-07-30'--存款exec CurrentWithdraw10001,-20000,'2016-08-30'--取款，返回消息：余额不足！（7）活期明细create proc DetailOfCurrentAccount --活期明细@no varchar(20)asselect*from CurrentAccounts where BNo = @no exec DetailOfCurrentAccount10001定期明细create proc DetailOfFixedAccount --定期明细@no varchar(20)asselect*from FixedAccounts where BNo = @no exec DetailOfFixedAccount10003（8）数据库备份与恢复使用图形化界面操作即可。