create table UserInfo(CustomerId char(20),CustomerName char(50)not null,PID varchar(20)unique,Telephone varchar(20),Address char(50),PRIMARY KEY(CustomerId))create table CardInfo(cardID char(50)not null,curType varchar(10)not null default'RMB',savingType varchar(10),openDate datetime default getdate(),openMoney int not null,balance int not null,pass char(50)not null default'888888',IsReportLoss char(50)not null default'否',customerID char(20)not null foreign key(customerID)references userinfo(customerID), PRIMARY KEY(CardId))create table TransInfo(transDate datetime not null default'getdate()',cardID char(50)foreign key(cardID)references cardinfo(cardID),transType char(10)not null,transMoney bigint not null,remark char(20),ID int identity(1,1)not null)Insert into userinfovalues('10001','李清','420101************','2071-84216821','湖北武汉');Insert into userinfovalues('10002','玉清','420101************','2071-84216820','湖北武汉');INSERT INTO CardInfoV ALUES('102128001','RMB','活期',getdate(),'10000','10000','84212121','否','10001'); INSERT INTO CardInfoV ALUES('102128002','RMB','活期',getdate(),'10001','10001','84202020','否','10002');select*FROM cardInfo where(datediff(day,getDate(),openDate)<datepart(weekday,openDate))Select cardid from cardinfoWhere(datediff(month,getDate(),openDate))<0and cardid in(select top 1 with ties cardid from transinfoorder by transMoney)Select sum(transMoney)from Transinfo Where transType='存入'Select sum(transMoney)from transinfo Where transType='支取'Select((Select sum(transMoney)from Transinfo Where transType='存入')-(Select sum(transMoney)from transinfo Where transType='支取'))as资金流通余额Select((Select sum(transMoney)from Transinfo Where transType='存入')*0.08-(Select sum(transMoney)from transinfo Where transType='支取')*0.03)as盈利结算Create unique index T_cardid on cardinfo(cardid)Create index S_cardid on transinfo(cardid)Create VIEW test(卡号,开户名,开户日期,余额,存款类型,身份证号,电话号码,居住地址)ASSelect cardid,customername,opendate,openmoney,savingtype,PID,Telephone,address From cardinfo left outer join userinfoOn cardinfo. customerID=userinfo. customerIDcreate proc zhuanzhang@tmoney bigint,@password char(50),@putcardid char(50),@incardid char(50)asBEGINif(@password!=(select pass from cardinfo where cardid=@putcardid))print'密码错误，请重试'else if(((select balance from cardinfo where cardid=@putcardid)-@tmoney)<1)print'余额不足，请确定后输入'elsebegininsert into transinfo values(getdate(),@putcardid,'支取',@tmoney,'无')insert into transinfo values(getdate(),@incardid,'存入',@tmoney,'无')update cardinfo set balance=balance-@tmoney where cardid=@putcardidupdate cardinfo set balance=balance+@tmoney where cardid=@incardid endENDcreate proc cunkuan@inmoney bigint,@password char(50),@inercardid char(50)asBEGINif(@password!=(select pass from cardinfo where cardid=@inercardid))print'密码错误，请重试'else if(@inmoney<0)print'不能输入负金额'elsebegininsert into transinfo values(getdate(),@inercardid,'存入',@inmoney,'无')update cardinfo set balance=balance+@inmoney where cardid=@inercardid endENDcreate proc qunkuan@inmoney bigint,@password char(50),@inercardid char(50)asBEGINif(@password!=(select pass from cardinfo where cardid=@inercardid))print'密码错误，请重试'else if(@inmoney<0)print'不能输入负金额'else if(((select balance from cardinfo where cardid=@inercardid)-@inmoney)<1) print'余额不足，请确定后输入'elsebegininsert into transinfo values(getdate(),@inercardid,'存入',@inmoney,'无')update cardinfo set balance=balance+@inmoney where cardid=@inercardidendENDcreate trigger WARMon cardinfoinstead of updateasBEGINdeclare @oldmoney bigintdeclare @newmoney bigintselect @oldmoney=balance from deleted;select @newmoney=balance from inserted;if(@newmoney<1)beginselect cardid,balance from deleted;print'您的余额不足'end;END。