exec xp_cmdshell 'mkdir D:\bank',no_outputgoif exists (select * from sysdatabases where name='bankDB') drop database bankDBcreate database bankDBon primary( name='bankDB_data', filename='D:\bank\bankDB_data.mdf',  size=10mb, filegrowth=10%)log on ( name='bankDB_log', filename='D:\bank\bankDB_log.ldf', maxsize=50mb,  filegrowth=2)gouse bankDBgo------------创建用户信息表if exists(select * from sysobjects where name='userInfo') drop table userInfogocreate table userInfo( customerID int not null identity(1,1) primary key, customerName varchar(30) not null, PID bigint not null, telephone varchar(13) not null, address varchar(30) null)go------------创建银行卡信息表if exists(select * from sysobjects where name='cardInfo') drop table cardInfogocreate table cardInfo( cardID varchar(19) not null primary key, curType varchar(10) not null,  savingType varchar(8) not null, openDate datetime not null, openMoney float not null, balance float not null, pass int not null, IsReportLoss bit not null, customerID int not null)go------------创建交易信息表if exists(select * from sysobjects where name='transInfo') drop table transInfogocreate table transInfo( transDate datetime not null, cardID varchar(19) not null, transType nchar(2) not null, transMoney float not null, remark varchar(30) null)go---------添加约束alter table userInfoadd constraint CK_PID check(len(PID)=18 or len(PID)=15),constraint CK_telephone check(telephone like replicate('[0-9]',4)+'-'+replicate('[0-9]',8) or telephone like replicate('[0-9]',3)+'-'+replicate('[0-9]',8) or len(telephone)=11)goalter table cardInfoadd constraint CK_cardID check(cardID like '1010 3576 '+replicate('[0-9]',4)+' '+replicate('[0-9]',4)),constraint DF_curType default('RMB') for curType,constraint CK_savingType check(savingType in('活期','定活两便','定期')),constraint DF_openDate default(getDate()) for openDate,constraint CK_openMoney check(openMoney >= 1),constraint CK_balance check(balance >= 1),constraint CK_pass check(len(pass)=6),constraint DF_pass default(888888) for pass,constraint DF_IsReportLoss default(0) for IsReportLoss,constraint FK_customerID foreign key(customerID) references userInfo(customerID)goalter table transInfoadd constraint DF_transDate default(getDate()) for transDate,constraint FK_cardID foreign key(cardID) references cardInfo(cardID),constraint CK_transType check(transType in('存入','支取')),constraint CK_transMoney check(transMoney >0)go---------插入测试数据insert into userInfo values('张三',123456789012345,'010-67898978','北京海淀')insert into userInfo (customerName,PID,telephone) values('李四',321245678912345678,'0478-44443333')insert into cardInfo values('1010 3576 1212 1134',default,'定期',default,1.00,1.00,default,0,2)insert into cardInfo values('1010 3576 1234 5678',default,'活期',default,1000.00,1000.00,default,0,1)---------插入交易信息insert into transInfo(transType,cardID,transMoney) values('支取','1010 3576 1234 5678',900.00)update cardInfo set balance=balance-900.00 where cardID='1010 3576 1234 5678'insert into transInfo(transType,cardID,transMoney) values('存入','1010 3576 1212 1134',5000.00)update cardInfo set balance=balance+5000.00 where cardID='1010 3576 1212 1134'---------常规业务模拟--(1)修改密码update cardInfo set pass=123456 where cardID='1010 3576 1234 5678'update cardInfo set pass=123123 where cardID='1010 3576 1212 1134'--(2)银行挂失update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'--(3)统计银行的资金流通余额和盈利结算declare @inMoney money --总存入量declare @outMoney money --总支取量select @inMoney=sum(transMoney) from transInfo where transType='存入'select @outMoney=sum(transMoney) from transInfo where transType='支取'print '银行流通余额总计为：'+convert(varchar,(@inMoney-@outMoney))+'RMB'print '盈利总结为：'+convert(varchar,(@outMoney*0.008-@inMoney*0.003))+'RMB'--(4)查询本周开户的卡号，显示该卡的相关信息select userInfo.*,cardInfo.* from cardInfo inner join userInfo on userInfo.customerID=cardInfo.customerIDwhere datepart(wk,getDate())=datepart(wk,openDate) and datediff(yy,openDate,getDate())=0--(5)查询本月交易金额最最高的卡号select distinct cardID as '卡号'from transInfo  where transMoney = (select max(transMoney) from transInfo where datediff(mm,transDate,getDate())=0)--(6)查询挂失账号的客户的信息select  '客户姓名'=customerName,'身份证号'=PID,'电话号码'=telephone,'地址'=addressfrom userInfowhere customerID in (select distinct customerID from cardInfo where IsReportLoss=1)--(7)催款提醒业务select '客户姓名'=customerName,'联系电话'=telephone,'账上余额'=balancefrom userInfo inner join cardInfo on userInfo.customerID=cardInfo.customerIDwhere balance<200--(8)创建索引和视图--创建索引if exists(select * from sysindexes where name='index_cardID') drop index transInfo.index_cardIDcreate nonclustered index index_cardID on transInfo(cardID) with fillfactor=70goselect * from transInfo where cardID='1010 3576 1212 1134'go--创建视图if exists(select * from sysobjects where name='view_userInfo') drop view view_userInfogocreate view view_userInfoas  select '客户编号'=customerID,'开户名'=customerName,'身份证号'=PID,'电话'=telephone,'地址'=address from userInfogoif exists(select * from sysobjects where name='view_cardInfo') drop view view_cardInfogocreate view view_cardInfoas  select '卡号'=cardID,'货币种类'=curType,'存款类型'=savingType,'开户日期'=openDate,'开户金额'=openMoney ,'余额'=balance,'密码'=pass,'是否挂失'=IsReportLoss,'客户编号'=customerID from cardInfogoif exists(select * from sysobjects where name='view_Info') drop view view_Infogocreate view view_Infoas  select '交易日期'=transDate,'卡号'=cardID,'交易类型'=transType,'交易金额'=transMoney,'备注'=remark from transInfogo--(9)创建存储过程drop procedure proc_takeMoneycreate procedure proc_takeMoney  @card char(19),--用户的卡号  @m money,--存入或支取的钱的数量  @type char(4), --支取还是存入  @inputPass char(6)='' --密码  as  print '交易正在进行，请稍候..' if(@type='支取')    begin   declare @balance float --在交易前的余额  select @balance=balance from cardInfo where cardID=@card and pass=@inputPass  if(@balance-@m>=1)   begin    insert into transInfo(transType,cardID,transMoney) values(@type,@card,@m)    update cardInfo set balance=balance-@m where cardID=@card    print '支取交易成功！交易金额：'+convert(varchar,@m)    print '卡号'+@card+'  余额:'+convert(varchar,@balance-@m)        end  else    begin    raiserror('支取交易失败！余额不足',16,123)    print '卡号'+@card+'  余额:'+convert(varchar,@balance)    return 1     end      end     else if(@type='存入')       begin  declare @balance2 float --交易成功或者失败的余额   select @balance2=balance from cardInfo where cardID=@card       insert into transInfo(transType,cardID,transMoney) values(@type,@card,@m)  update cardInfo set balance=balance+@m where cardID=@card  print '存入交易成功！交易金额：'+convert(varchar,@m)  print '卡号'+@card+'  余额:'+convert(varchar,@balance2+@m)       end  else   begin    declare @balance3 float --交易成功或者失败的余额    select @balance3=balance from cardInfo where cardID=@card    raiserror('存入交易失败!',16,1)   print '卡号'+@card+'  余额:'+convert(varchar,@balance3)  end go--张三支取300元declare @cardID varchar(19) --卡号select @cardID=cardID from cardInfo where customerID=(select customerID from userInfo where customerName='张三')exec proc_takeMoney @cardID,300,'支取','123456'go--李四存入500元declare @cardID varchar(19) select @cardID=cardID from cardInfo where customerID=(select customerID from userInfo where customerName='李四')exec proc_takeMoney @cardID,500,'存入'go--产生随机卡号的存储过程drop proc proc_randCardIDcreate procedure proc_randCardID @randCardID char(19) outputas  declare @r numeric(15,8) declare @tempStr varchar(10) select @r=rand(convert(int,(datepart(mm,getdate())*10000+datepart(ss,getdate())*1000+(datepart(ms,getdate()))))) set @tempStr=convert(varchar,@r) print '生成的随机数:'+@tempStr set @randCardID='1010 3576 '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)godeclare @myCardID char(19)exec proc_randCardID @myCardID outputprint '产生的随机卡号为：'+@myCardID--开户的存储过程create procedure proc_openAccount @customerName char(8),@PID char(18), @telephone char(13),@openMoney money,@savingType char(8),@address varchar(50)=' ' as  set nocount on declare @mycardID char(19) declare @cur_customerID int  --根据身份证查出的客户的ID exec proc_randCardID @mycardID output --随机获得卡号 while exists(select * from cardInfo where cardID=@mycardID)  exec proc_randCardID @mycardID output insert into userInfo (customerName,PID,telephone,address)  values(@customerName,@PID,@telephone,@address) print '尊敬的客户，开户成功！系统为您产生的随机卡号为：'+@mycardID print '开户日期'+convert(varchar,getDate())+'  开户金额：'+convert(varchar,@openMoney) select @cur_customerID=customerID from userInfo where PID=@PID insert into cardInfo(cardID,savingType,openMoney,balance,customerID)  values(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)goexec proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'exec proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期'--(10)创建事务drop proc proc_transfercreate proc proc_transfer @card1 char(19),@card2 char(19),@outmoney moneyas declare @return int --接受返回的值 set @return=0 begin transaction  print '开始转账，请稍后...'  exec @return=proc_takeMoney @card1,@outmoney,'支取','123123'  if (@return=0)   exec proc_takeMoney @card2,@outmoney,'存入'     else    exec proc_takeMoney @card2,@outmoney,''  if(@return=0)   begin    print '转账成功！'    commit transaction   end  else   begin    print '转帐失败！'    rollback transaction   endgodeclare @cardID1 varchar(19) --李四卡号declare @cardID2 varchar(19) --张三卡号select @cardID1=cardID from cardInfo where customerID=(select customerID from userInfo where customerName='张三')select @cardID2=cardID from cardInfo where customerID=(select customerID from userInfo where customerName='李四')exec proc_transfer @cardID2,@cardID1,2000--(11)创建登录账号和数据库用户exec sp_addlogin 'sysAdmin2','1234' --添加SQL登录账号goexec sp_grantdbaccess 'sysAdmin2','sysAdminDBUser'  --创建数据库用户gogrant select,insert,update,delete on transInfo to sysAdminDBUser --数据库用户授权go