網吧管理系統

if exists(select * from sysobjects where [name]='ATM') drop database ATM go create database ATM on ( name='ATM_data', filename='E:\SQL项目案例\ATM_data.mdf', size=10, filegrowth=15 ) log on( name='ATM_log', filename='E:\SQL项目案例\ATM_log.ldf', size=10, filegrowth=15 ) go -------用户信息表 create table userInfo -----用户信息表 ( customerID int identity(1,1) not null, ------- 顾客编号 customerName varchar(20) not null, ------- 开户名 PID varchar(20) not null, ------- 身份证号 telephone varchar(15) not null, ------- 联系电话 address varchar(20) null ------- 居住地址 ) go alter table userInfo add constraint pk_ID ------ 顾客编号设为主键 primary key(customerID) alter table userinfo add constraint ck_PID -------身份证只能在１８位或15位 check((pid>1 and pid<15)or(pid>1 and pid <18)) alter table userinfo add constraint ck_phone -------联系电话只能在11 位 check(telephone>1 and telephone<11) alter table userinfo add constraint uq_pid unique(pid) ------身份证设为唯一约束 go -----------银行卡 create table cardInfo ( cardID varchar not null, -------卡号 curtype char not null, 　　　-----　货币种类 savingType varchar(20) not null, ------- 存款类型 openDate varchar(30) not null, -------开户日期 openMoney float not null, -------- 开户金额 balance float not null, --------- 余额 pass varchar(20) not null, ---------密码 IsReplortLoss char(2) not null, --------是否挂失 customerID char(20) not null -----------顾客编号 ) go alter table cardinfo add constraint ck_cardid check(cardid like '1010 3576 [0-9]') alter table cardinfo add constraint pk_cardid primary key(cardid) alter table cardinfo add constraint df_curtype default('RMB') for curtype alter table cardinfo add constraint df_opendate default(getdate()) for opendate 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 ck_pass check(pass>6) alter table cardinfo add constraint df_pass default(6) for pass go -------------交易表 create table transinfo ( transDate datetime null, ----- 交易日期 cardID varchar(20) not null, ------卡号 transType varchar(10) not null, -----交易类型 transMoney float not null, ----交易金额 remark varchar(50) null ------备注 ) go alter table transinfo add constraint df_transdate default(getdate()) for transdate alter table transinfo add constraint ck_transMoney check(transMoney>0) --------插入信息到用户表中 insert into userinfo values('张三','123456789012345','010_67898978','北京海淀') insert into userinfo values('李四','321245678912345678','0478-4444333',null) ----------插入信息到银行卡信息中 insert into cardinfo values('1010 3576 1234 1134','RMB','定期',2005-3-25,1,1,888888,0,2) insert into cardinfo values('1010 3576 1234 5678','RMB','活期',2005-9-16,1000,1000,8888885,0,1) ----------插入信息到交易表中 if exists(select * from sysobjects where [name]='proc_transinfo') drop proc proc_transinfo go -------创建存储器 并把数据存入到交易表中 create proc proc_transinfo @money money, @cardid varchar(19), @type varchar(10) as print '取款为:'+convert(varchar(10),@money) if(@type='取出') begin update cardinfo set balance=balance-@money where cardid=@cardid insert transinfo(transtype,cardid,transmoney) values('取出',@cardid,@money) end else begin update cardinfo set balance=balance+@money where cardid=@cardid insert transinfo(transtype,cardid,transmoney) values('存入',@cardid,@money) end go exec proc_transinfo 900,'1010 3576 1234 5678','取出' exec proc_transinfo 5000,'1010 3576 1234 1134','存入' -------修改密码 create proc proc_cardinfo @pass varchar(20), @card varchar(20), as update cardinfo set pass=@pass where cardid=@card go exec proc_cardinfo '123456','1010 3576 1234 5678' exec proc_cardinfo'123123','1010 3576 1234 1134' go --------- 挂失帐号 if exists(select * from sysobjects where [name]='proc_cardinfo') drop proc proc_cardinfo go create proc proc_cardinfo @card varchar(20), @isreplort int as update cardinfo set isreplortloss=@isreplort where cardid=@card go exec proc_cardinfo '1010 3576 1234 1134',1 go ---------催款提醒业务 alter proc proc_in @cardid varchar(20)='1010 3576 1234 5678' as declare @balance float declare @balance1 float select @balance=balance from cardinfo select @balance1=balance from cardinfo if (@balance<200) begin select [user].customerName as 客户姓名,[user].telephone as 联系电话,card.balance as 帐上余额 from userinfo as [user] inner join cardinfo as card on [user].customerid=card.customerid where card.cardid=@cardid end select [user].customerName as 客户姓名,[user].telephone as 联系电话 from userinfo as [user] inner join cardinfo as card on [user].customerid=card.customerid where card.balance>200 go exec proc_in go -----本询挂失帐号的客户信息 create proc proc_guashi @card varchar(21) as select customername as 客户姓名,telephone as 联系电话 from userinfo where customerid in (select customerid from cardinfo where cardid=@card) go exec proc_guashi '1010 3576 1234 5678' go -------统计银行的资金通余额和盈利结算 declare @inmoney money ---总存入量 declare @inmoney1 money ----总支取量 declare @summoney money -----资金流通余额 declare @gain money -----盈利结算 select @inmoney=sum(transmoney) from transinfo where transtype='存入' select @inmoney1=sum(transmoney) from transinfo where transtype='取出' select @summoney=@inmoney-@inmoney1 ------资金流通余额=总存入量-总支取量 print '银行流通余额总计为: '+convert(varchar(20),@summoney)+' RMB' select @gain=@inmoney1*0.008-@inmoney*0.003 print '盈利结算为: ' +convert(varchar(20),@gain)+' RMB' go -------查询本月交易金额最高的卡号 select distinct(cardid) from transinfo where transmoney= (select max(transmoney) from transinfo ) ------ 创建索引 if exists(select * from sysindexes where [name]='In_cardinfo_cardid') drop index cardinfo.in_cardinfo_cardid ----删除银卡中的索引 go create nonclustered index in_cardinfo_cardid on cardinfo(cardid) with fillfactor=70 go -------按照索引'in_cardinfo_cardid'进行查询 select * from cardinfo (index=in_cardinfo_cardid) where cardid='1010 3576 1234 1134' --------创建视图 if exists(select * from sysobjects where name='view_userinfo_cardinfo_transinfo') drop view view_userinfo_cardinfo_transinfo go select * from transinfo alter view view_userinfo_cardinfo_transinfo as select userinfo.customerid as 客户编号, 客户姓名=customerName,身份证号=pid,电话号码=telephone,家庭住址=address from userinfo left join cardinfo on userinfo.customerid=cardinfo.customerid left join transinfo on transinfo.cardid=cardinfo.cardid -- select cardinfo.cardid as 卡号,curtype as 货币种类,savingtype as 存款类型,opendate as 开户日期,balance as 余额,pass as 密码,isreplortloss as 是否丢失,cardinfo.customerid as 客户编号 from cardinfo -- left join userinfo on userinfo.customerid=cardinfo.customerid --select transdate as 交易日期,transinfo.cardid as 卡号,transtype as 交易类型,transmoney as 交易金额,remark as 备注 from transinfo --left join cardinfo on transinfo.cardid=cardinfo.cardid go alter view view_cardinfo as select cardinfo.cardid as 卡号,curtype as 货币种类,savingtype as 存款类型,opendate as 开户日期,balance as 余额,pass as 密码,isreplortloss as 是否丢失,cardinfo.customerid as 客户编号 from cardinfo left join userinfo on userinfo.customerid=cardinfo.customerid go alter view view_transinfo as select transdate as 交易日期,transinfo.cardid as 卡号,transtype as 交易类型,transmoney as 交易金额,remark as 备注 from transinfo left