/**//* 作者:花满天 QQ:122273014 */ --1 建库 --1.1 在D盘创建bank文件夹 use master exec xp_cmdshell 'mkdir d:ank',NO_OUTPUT go -- 1.2 检测是否存在bankDB ifexists(select*from sysdatabases where name='bankDB' ) begin dropdatabase bankDB end go --1.3 建bankDB createdatabase bankDB -- 数据库的逻辑名称 onprimary--默认的组(可不写) ( name='bankDB', filename='d:ankankDB.mdf', --数据库的物理文件 size=5, maxsize=100, filegrowth=15% ) logon--数据库的日志文件 ( name='bankDB_log', filename='d:ankankDB.ldf', size=2, filegrowth=1 ) GO --2 建表加约束 use bankDB --(因数据库是肯定是刚创好的,所以可以不写 drop table userInfo) --2.1 create table userInfo createtable userInfo ( customerID intidentity(1,1) primarykey , customerName varchar(20) notnull, PID varchar(18) notnulluniquecheck (len(PID)=18orlen(PID)=15), telephone varchar(20) notnullcheck( telephone like'[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'orlen(telephone)=11), address varchar(50) null ) go -- 2.2 create table cardInfo createtable cardInfo ( cardID varchar(20) primarykeycheck( cardId like'1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), curType varchar(10) notnulldefault('RMB'), savingType varchar(10) check (savingType in('活期','定活两便','定期')), openDate datetimenotnulldefault (getdate()), openMoney moneynotnullcheck (openMoney>=1), balance moneynotnullcheck (balance>=1), pass char(6) notnulldefault'888888'check( len(pass)=6), IsReportLoss varchar(2) default'否'check(IsReportLoss in('是','否')), customerID intforeignkeyreferences userInfo(customerID) ) go -- 2.3 create table transDate createtable transInfo ( transDate datetimenotnulldefault(getdate()), cardID varchar(20) notnullforeignkeyreferences cardInfo(cardID), transType char(4) check(transType in ('存入','支取')), transMoney moneynotnullcheck(transMoney >0), remark varchar(40) ) go --3 写入测试数据 (人工) --3.1 insert into UserInfo insertinto userInfo(customerName,PID,telephone,address) values('张三','123456789012345','0010-67898978','北京海淀') insertinto userInfo(customerName,PID,telephone) values('李四','321245678912345678','0478-44443333') go --3.2 insert into cardInfo insertinto cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID) values('1010 3576 1212 1134',default,'定期',default,1,1,2) insertinto cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID) values('1010 3576 1234 5678',default,'活期',default,1000,1000,1) go --3.3 insert into transInfo insertinto transInfo(transDate,transType,cardID,transMoney)values(getDate(),'支取','1010 3576 1212 1134',900) insertinto transInfo(transDate,transType,cardID,transMoney)values(getDate(),'存入','1010 3576 1234 5678',5000) go --显示 select*from userInfo select*from cardInfo go --4 常规业务模似 --4.1 修改用户密码 update cardinfo set pass='123456'where cardID='1010 3576 1234 5678'-- 张三 update cardinfo set pass='123123'where cardID='1010 3576 1212 1134'-- 李四 select*from cardInfo go --4.2 挂失账号 update cardinfo set IsReportLoss='是'where cardID='1010 3576 1212 1134'-- 李四 select*from cardInfo where cardID='1010 3576 1212 1134' go --4.3 资金流通余额和盈利结算 declare@inMoneymoney, --总存入 @outMoneymoney--总支出 select@inmoney=sum(transmoney) from transinfo where transtype='存入' select@outmoney=sum(transmoney) from transinfo where transtype='支取' print'银行流通余额总计为:'+convert(varchar(20),@inmoney-@outmoney)+'RMB' print'盈利结算为:'+convert(varchar(20),@outmoney*0.008-@inmoney*0.003)+'RMB' go --4.4 查询本周开户的Card select*from cardInfo whereDATEDIFF (ww,OpenDate,getDate())=0 --4.4 查询本月交易最高的卡号 selecttop1 cardId,sum(transmoney) as 交易总金额 from transinfo wheredatediff(mm,getDate(),transdate)=0 groupby cardId orderbysum(transmoney) desc --4.5 挂失 select customername as 客户姓名,telephone as 联系电话 from userinfo u innerjoin cardinfo c on u.customerid=c.customerid where isreportloss='是' --4.6 提醒业务 select customername as 客户姓名,telephone as 联系电话,balance as 卡上余额 from userinfo u innerjoin cardinfo c on u.customerid=c.customerid where balance<=200 --5 创建索引和视图 --5.1 创建索引 CREATEINDEX index_card ON transinfo (cardid) WITHFILLFACTOR=70 go select*from transinfo where cardid='1010 3576 1212 1134' go --5.2创建视图 --5.21 v_userinfo createview v_userinfo as select customerID as 客户编号,customername as 开户名,pid as 身份证号,telephone as 电话号码,address as 居住地址 from userinfo go select*from v_userinfo go --5.22 v_cardinfo createview v_cardinfo as select cardid as 卡号,curtype as 货币种类,savingtype as 存款类型,opendate as 开户日期,balance as 余额,pass as 密码,isreportloss as 是否挂失,customerid as 客户编号 from cardinfo go select*from v_cardinfo go --5.23 v_transInfo createview v_transInfo as select transdate as 交易日期,transtype as 交易类型,cardid as 卡号,transmoney as 交易金额,remark as 备注 from transInfo go select*from v_transInfo go --6 创建触发器 IFEXISTS (SELECT name FROM sysobjects WHERE name ='trig_trans' ) DROPTRIGGER trig_trans GO createtrigger trig_trans on transInfo forinsert as declare@myTransTypechar(4),@outMoneymoney,@myCardIDvarchar(30),@zhi_balancemoney select@myTransType=transType,@outMoney=transMoney,@myCardID=cardID from inserted if(@myTranstype='存入') begin update cardinfo set balance =balance+@outMoneywhere cardid=@mycardid print'交易成功,存入'+convert(varchar(15),@outMoney)+'RMB' end else begin select@zhi_balance=balance-@outMoneyfrom cardinfo where cardid=@mycardid if(@zhi_balance<=0) begin raiserror('余额不足!',16,1) rollbacktran end else begin update cardinfo set balance =balance-@outMoneywhere cardid=@mycardid print'交易成功,支取'+convert(varchar(15),@outMoney)+'RMB' end end -- go --7:测试触发器 -- 7.1.1 Error insertinto transInfo(transDate,transType,cardID,transMoney)values(getDate(),'支取','1010 3576 1234 5678',1000) go -- 7.1.2 NotError insertinto transInfo(transDate,transType,cardID,transMoney)values('2005-9-1','存入','1010 3576 1212 1134',200) go --8:创建存储过程 IFEXISTS (SELECT name FROM sysobjects WHERE name ='proc_takeMoney'AND type ='P') DROPPROCEDURE proc_takeMoney GO createproc proc_takeMoney @cardchar(19),@mmoney,@typechar(4),@inputpasschar(6)='' as ifexists(select*from cardinfo where cardid=@cardand pass=@inputpass) begin if(@type='支取') begin insertinto transInfo(transType,cardID,transMoney)values('支取',@card,@m) print'成功支取'+convert(varchar(12),@m)+'RMB' end else begin insertinto transInfo(transType,cardID,transMoney)values('存入',@card,@m) print'成功存入'+convert(varchar(12),@m)+'RMB' end end else begin --rollback tran print'密码错误' end go exec proc_takeMoney '1010 3576 1234 5678',300,'支取','34323'--密码错误 exec proc_takeMoney '1010 3576 1234 5678',300,'支取','888888' go --9:产生随机卡号存储过程 IFEXISTS (SELECT name FROM sysobjects WHERE name ='proc_randCardID') DROPPROCEDURE proc_randCardID GO createproc proc_randCardID @randCardIDchar(19) output as declare@rchar(15), @indexint, @tempStrchar(9) --产生随机数 select@r=convert(numeric(15,8),datepart(ms,getdate())*rand()) select@index=charindex('.',@r) select@tempStr=substring(@r,@index+1,4)+''+substring(@r,@index+5,4) --获得后面8位随机数 --随机卡号 set@randCardID='1010 3576'+''+@tempStr go ------ -- 测试随机号 declare@randcardIDchar(19) exec proc_randCardID @randcardID output print'产生的卡号为:'+@randcardID --10 ;开户开户存储过程 IFEXISTS (SELECT name FROM sysobjects WHERE name ='proc_openAccount') DROPPROCEDURE proc_openAccount GO createproc proc_openAccount @customernamevarchar(20),@pidchar(20),@telephonechar(13),@openMoneymoney,@savingTypechar(8), @addressvarchar(50)='' as declare@mycardIDchar(19), @cur_customerIDint execute proc_randCardID @mycardID output -- 得到我的随机号 whileexists(select*from cardinfo where cardid=@mycardid) --如果随在,在随机一次 execute proc_randCardID @mycardID output insertinto userinfo(customerName,PID,telephone,address)values(@customername,@pid,@telephone,@address)--写入数据userinfo select@cur_customerID=customerID from userinfo where pid=@pid--得到当前的ID号 insertinto cardinfo(cardid,savingtype,openmoney,balance,customerid) values (@mycardid,@savingtype,@openMoney,@openMoney,@cur_customerID)--写入cardinfo print'尊敬的客户,开户成功!您的卡号为:'+@mycardid+'开户金额:'+convert(varchar(12),@openMoney)+'开户日期:'+convert(varchar(10),getdate()) go --测验10 exec proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡' exec proc_openAccount '赵二','213345678912342222','0760-44446666',1,'定期' --11创建事务 go createproc proc_transfer @card1char(19), @card2char(19), @outmoneymoney as begintran declare@ErrorSumint set@ErrorSum=0 insertinto transinfo(transtype,cardid,transmoney) values('支取',@card1,@outmoney) set@ErrorSum=@ErrorSum+@@ERROR insertinto transinfo(transtype,cardid,transmoney) values('存入',@card2,@outmoney) set@ErrorSum=@ErrorSum+@@ERROR if( @ErrorSum<>0) rollbacktran else committran --测试 go declare@card_1char(19),@card_2char(19) select@card_1=cardid from cardinfo c innerjoin userinfo u on c.customerid=u.customerid where customername='李四' select@card_2=cardid from cardinfo c innerjoin userinfo u on c.customerid=u.customerid where customername='张三' exec proc_transfer @card_1,@card_2,200 select*from v_cardinfo select*from v_transinfo go --12 创建账号和数据用户服务 exec sp_addlogin 'sysAdmin','1234'--SQL 登陆帐号 exec sp_grantdbaccess 'sysAdmin','sysAdminDBUser'--创建数据库用户 grantselect,insert,update,deleteon transinfo to sysAdminDBUser --给权力 /**//* 作者:花满天 QQ:122273014 如有更好的编写方法,请和我联系 */