• 切换到BANKDB数据库

USE BANKDB

• 在BANKDB数据库检测表是否存在

表名表描述(作用)cardInfo银行卡信息表transInfo交易信息表userInfo用户信息表

• 在BANKDB数据库创建表SQL

--`创建用户信息表` create table userInfo ( customerID int identity(1,1) not null primary key, --顾客编号,主键 customerName varchar(10)not null, --开户名 PID varchar(30)not null unique, --身份证号,身份证号唯一约束 telephone varchar(15)not null, --联系电话格式xxxx-xxxxxxxx或手机号11位 address varchar(250) --居住地址,可选输入 ) go --`创建银行卡信息表` create table cardInfo ( cardID varchar(50)not null primary key , --卡号,主键,格式为:1010 3576 xxxx xxxx 后面是随机的 curType varchar(10)not null, --货币种类 savingType varchar(20), --存款类型,活期/定活两便/定期 openDate datetime not null default getdate(), --开户日期,默认为系统当前日期 openMoney Money not null, --开户金额, balance money not null, --余额,不低一元否则将稍户 pass varchar(50) default(888888)not null, --密码,6位数字, IsReportLoss char(10)not null default(否), --是否挂失,否/是 默认为"否" customerID int not null --顾客编号表示该卡对应的顾客的编号,一位顾客允许办理多张卡 ) go --`创建交易信息表` create table transInfo ( transDate datetime not null default getdate(), --交易日期,默认为当前日期 cardID varchar(50) not null, --卡号,外键可重复索引 transType varchar(10) not null, --交易类型,只能是存入或支取 transMoney money not null, --交易金额,大于零 remark varchar(250), --备注,可选输入,其他说明 ) go

• 在BANKDB数据库的表添加约束条件SQL

-- `用户表` alter table userInfo add constraint ck_PID check(len(PID)>15 or len(PID)>18)--身份证号只能是18或15位, --联系电话 alter table userInfo add constraint CK_telephone check(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] or telephone like [0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]) -- `银行信息表` alter table cardInfo add constraint CK_cardID check(cardid like 1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]) alter table cardinfo add constraint DF_curType default(RMB) FOR CURTYPE --默认为RMB ALTER table cardinfo add constraint CK_savingType check (savingType in(活期,定活两便,定期)) ALTER table cardinfo add constraint CK_openMoney check (openMoney>=1) --不低于一元 ALTER table cardinfo add constraint CK_balance check (balance>=1) --不低于一元 ALTER table cardinfo add constraint DF_pass check(pass like [0-9][0-9][0-9][0-9][0-9][0-9]) --开户时密码 alter table cardInfo add constraint FK_customerID foreign key(customerID)references userInfo(customerID) --`交易表` --`只能是存入或支取` alter table transInfo add constraint CK_transType check(transType in(存入,支取)) --或者是(transType=存入 or transType=支取) alter table transinfo ADD constraint CK_transMoney check(transMoney>0) --大于零 alter table transInfo add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)

• 在BANKDB数据库的表添加数据

--`插入数据` go insert into userInfo(customerName,PID,telephone,address) values(张三,1234567890123451,010-67898978,北京海淀区) insert into userInfo(customerName,PID,telephone) values(李四,32145678912345678,0478-44443333) go insert into cardinfo values(1010 3576 1212 1134,RMB,定期,default,1.00,5001.00,888888,0,2) insert into cardinfo values(1010 3576 1234 5678,RMB,活期,default,1000.00,1000.00,888888,0,1) --`查询cardinfo` select * from cardinfo go insert into transInfo(cardID,transDate,transType,transMoney)values(1010 3576 1212 1134,getdate(),存入,5000.00) go

在BANKDB数据库的表修改(修改密码)

• 场景1: 当张三取款9000时，会交易信息表(transinfo)中添加一条交易记录，同时应自动更新卡信息（cardinfo）中的现有余额（减少900元），先假定手动插入更新信息

--`声明变量` declare @cardID varchar(50) select @cardID=cardID from cardInfo where customerID=(select customerID from userInfo where customerName=张三) update cardinfo set balance=balance-900 where customerid =(select customerid from userinfo where customerName =张三) insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@cardID,支取,900.00) --`查询cardinfo` select * from cardinfo

• 场景2: 修改密码
  张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456，
  李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123

update cardInfo set pass=case when cardid=1010 3576 1234 5678 then 123456 when cardid=1010 3576 1212 1134 then 123123 end select * from cardinfo

• 场景3: 银行卡挂失：李四（卡号1010 3576 1212 1134）因银行卡丢失，申请挂失

update cardinfo set IsReportLoss=是 where cardid=1010 3576 1212 1134 select * from cardinfo

• 场景4: 统计银行和银行的资金流量通余和盈利结算。
  存入代表资金流入，支取代表资金流出。 资金流通金额=总存入量-总支取量假定存款利率为千分之8 盈利结算=总支取量x0.008-总存入量x0.003

declare @InMoney money, @OutMoney money 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

• 场景5: 查询本周开户的卡号，显示该卡相关信息

select * from cardinfo where datename(wk,getdate())=datepart(wk,openDate) select * from cardinfo where datepart(wk,getdate())=datepart(wk,openDate)

• 场景6: 查询本月交易金额最高的卡号,在交易信息表中,采子查询和distinct去掉重复的卡号

select distinct卡号=cardid from transinfo where transMoney=(select max(transMoney)from transinfo where datepart(mm,transDate)=datepart(mm,getdate()))

• 场景7: 查询挂失账号的客户信息,利用子查询in 方式或内部inner join

select customerName as 客户姓名,联系电话=telephone from userinfo where customerId in(select customerID from cardinfo where IsReportLoss=是) SELECT *FROM userinfo AS a INNER JOIN cardinfo AS p ON a.customerId=p.customerId where IsReportLoss=是

• 场景8: 催款提醒业务：例如某种的需要，每个月末，如果发现用户账上余额少于200元，将致电催款

select customerName as 客户姓名,联系电话=telephone,账上余额=balance from userInfo Inner Join cardInfo on userinfo.customerId=cardInfo.customerId where balance<200

• 场景9: 创建索引和视图,表卡号cardId字段创建重复索引，以便加速查询，填充因子为70%

IF exists(select name from sysindexes where name=index_cardID) drop index transInfo.index_cardID go create nonclustered index index_cardID on transInfo(cardid) with fillfactor=70 go

• 场景10:创建索引查询张三（卡号为1010 3576 1212 1134）的交易记录

select * from transInfo with(index =index_cardID) where cardid=(select cardid from cardinfo where customerid=(select customerid from userinfo where customerName=张三))

• 场景11:创建视图：为了向客户显示信息友好，查询各表，要求字段全为中文字段名3个表 , 对应的视图为：view_userInfo 、view_cardInfo view_tranInfo

-- `view_userInfo 用户视图` if exists(select * from sysobjects where name=view_userInfo) drop view view_userInfo go create view view_userInfo as select 客户编号=customerid,开户名=customername,身份证号=PID,电话号码=telephone, 地址=address from userInfo go select * from view_userInfo -- `view_cardInfo 卡信息视图` if exists(select * from sysobjects where name=view_cardInfo) drop view view_cardInfo go create view view_cardInfo as select 卡号=cardid,货币类型=curType, 存款类型=savingType,开户时间= openDate, 开户金额=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID from cardinfo go select * from view_cardInfo --`view_tranInfo 交易信息视图` if exists(select * from sysobjects where name=view_tranInfo) drop view view_tranInfo go create view view_tranInfo as select 交易日期=getdate(),交易类型=transType,卡号=cardId,交易金额=transMoney, 备注=remark from transinfo go select * from view_tranInfo

• 场景12: 创建存储过程 ，1.取钱或存钱的存储过程proc_takeMoney
  调用存储过程取钱或存钱，张三的卡号支取300（密码123456）,李四的卡号存入500

if exists(select * from sysobjects where name=proc_takeMoney) drop proc proc_takeMoney go --`创建取款的存储过程` create proc proc_takeMoney @cardID char(22), @Money money, @type char(4), @inputPass varchar(6)= as declare @Mybalance money select @Mybalance=balance from cardInfo where cardid=@cardID if (@type=支取) begin if((select pass from cardinfo where cardID=@cardID)<>@inputPass) begin print 交易正进行,请稍后..... raiserror(密码错误,16,1) return end if(@Mybalance<=@Money) begin raiserror(交易失败,余额不足,16,1) print 卡号 @cardID 余额 convert(varchar(10),@Mybalance) return end else begin update cardinfo set balance=balance-@Money where cardID=@cardID print 交易成功，交易金额为 convert(varchar(20),@Money) print 卡号 @cardID 余额 convert(varchar(10),@Mybalance) end end else begin update cardinfo set balance=balance @Money where cardID=@cardID print 交易成功!交易金额为 convert(varchar(10),@Money) print 卡号 @cardID 余额 convert(varchar(10),@Mybalance) end --`插入数据` insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@cardID,@type,@Money) go --`显示相关信息` go declare @cardID varchar(25),@balance money select @cardID=cardid,@balance=balance from cardinfo where customerid=(select customerid from userinfo where customerName=张三) exec proc_takeMoney @cardID,300,支取,123456 --执行存储过程 --declare @cardID varchar(25),@balance money select @cardID=cardid,@balance=balance from cardinfo where customerid=(select customerid from userinfo where customerName=李四) --`插入记录` exec proc_takeMoney @cardID,500,存入, --执行存储过程 go select * from view_tranInfo select * from view_cardInfo select * from view_userInfo

• 场景13: 产生随机数卡号存储过程proc_randCardID

if exists(select * from sysobjects where name=proc_randCardID) drop proc proc_randCardID go create proc proc_randCardID @randCardID char(25)output --输出参数 as declare @R numeric(15,8),@tempStr varchar(20) --15位数,保留8位小数 select @R=rand((datepart(mm,getdate())*100000) (datepart(ss,getdate())*1000) datepart(ms,getdate())) set @tempStr=convert(varchar(25),@R) --存放随机数 set @randCardID=1010 3576 substring(@tempStr,3,4) substring(@tempStr,7,4)--截取 go --`执行存储过程` declare @RandID char(19) exec proc_randCardID @RandID output --输出参数与创建存储过程一一对应

• 场景16: 创建登录账号和数据库用户.sql登录账号Admin密码为1234作为系统维护的账号具有对上述3个表增、删、改、查的权限阶段

exec sp_addlogin Admin,1234 --SQL Server exec sp_grantdbaccess Admin,sysAdminDBUser grant select,insert,update,delete on userInfo to sysAdminDBUser grant select,insert,update,delete on cardInfo to sysAdminDBUser grant select,insert,update,delete on transInfo to sysAdminDBUser