项目1.银行ATM取款机系统

-----------------------------1.建库------------------------------------------------ use master set nocount on--不显示影响行 EXEC XP_cmdshell'mkdir d:bank',NO_OUTPUT GO if exists(select * from sysdatabases where name='bankDB') DROP DATABASE bankDB GO CREATE DATABASE bankDB--建哭库ATM ON PRIMARY ( NAME='bankDB_data',--主数据文件 FILENAME='D:\bank\bankDB_data.mdf', size=3mb, filegrowth=15% ) LOG ON ( NAME='bankDB_log',--日志文件 FILENAME='D:\bank\bankDB_log.ldf', SIZE=2MB, FILEGROWTH=15% ) GO -------------------------2,创建数据库表-------------------------------------------- use bankDB GO /*用户信息表*/ IF EXISTS(SELECT * FROM sysobjects where name='userInfo') drop table userInfo GO create table userInfo ( customerID int identity(1,1), --顾客编号 customerName varchar(20) not null, --开户名 PID numeric not null, --身份证号 telephone varchar(20) not null, --联系电话 address text --居住地址 ) GO --/*创建用户信息表约束*/ alter table userInfo add constraint PK_customerID primary key(customerID) --主键约束 ALTER TABLE userInfo ADD CONSTRAINT UQ_PID UNIQUE(PID)--身份证唯一约束 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]' 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][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]') GO --/*插入用户信息表数据*/ insert into userInfo(customerName,PID,telephone,address) values ('张三',123456789012345,'010-67898978','北京海定') insert into userInfo(customerName,PID,telephone) values ('李四',321245678912345678,'0478-44443333') GO --------------------- --------------------- /*卡信息表*/ IF EXISTS(SELECT * FROM sysobjects where name='cardInfo') drop table cardInfo GO create table cardInfo ( cardID varchar(50) not null , --卡号 curType varchar(20) not null, --货币种类 savingType varchar(10) not null, --存款种类 openDate datetime not null, --开户日期 openMoney money not null, --开户金额 balance money not null, --余额 pass varchar(6) not null, --密码 IsReportLoss VARCHAR(10) not null, --是否挂失 customerID int not null --顾客编号 ) GO --/*创建卡信息表约束*/ alter table cardInfo add constraint PK_cardID primary key(cardID) --主键约束 ALTER TABLE cardInfo ADD CONSTRAINT FK_customerID foreign key(customerID) references userInfo(customerID)--外键 alter table cardInfo add constraint DF_curType DEFAULT('RMB') for curType--默认货币类型 alter table cardInfo add constraint CK_savingType check (savingType in('活期','定活两便','定期')) alter table cardInfo add constraint DF_openDate default(getdate()) for openDate--开户日期默认为当前系统时间 alter table cardInfo add constraint CK_openMoney check(openMoney>=1)--开户金额大于1 alter table cardInfo add constraint CK_balance check(balance>=1)--余额大于1 alter table cardInfo add constraint DF_pass default(888888) for pass--开户日期默认为当前系统时间 alter table cardInfo add constraint DF_IsReportLoss default('否') for IsReportLoss--是否挂失默认为否 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]') GO --/*插入卡信息表数据*/ insert into cardInfo values ('1010 3576 1212 1134',default,'定期',default,1,1,default,default,2) insert into cardInfo values ('1010 3576 1234 5678',default,'活期',default,1000,1000,default,default,1) GO ----------------------- ----------------------- /*交易信息*/ IF EXISTS(SELECT * FROM sysobjects where name='transInfo') drop table transInfo GO create table transInfo ( transDate datetime not null, --交易日期 cardID varchar(50) not null , --卡号 transType varchar(20) not null, --交易类型 transMoney money not null, --交易金额 remark text --备注 ) GO --/*创建交易信息约束*/ ALTER TABLE transInfo ADD CONSTRAINT FK_cardID foreign key(cardID) references cardInfo(cardID)--外键 alter table transInfo add constraint DF_transDate default(getdate()) for transDate--交易日期为当前系统时间 alter table transInfo add constraint CK_transType CHECK(transType IN ('存入','支取'))--存款类型为存入或支取 alter table transInfo add constraint CK_transMoney CHECK(transMoney>0)--交易金额必须大于0 GO --/*插入交易信息表数据*/ insert into transInfo values (default,'1010 3576 1234 5678','支取',900,NULL) update cardinfo set balance=balance-900 where cardid='1010 3576 1234 5678' insert into transInfo values (default,'1010 3576 1212 1134','存入',5000,NULL) update cardinfo set balance=balance+5000 where cardid='1010 3576 1212 1134' GO --查询数据库表信息 print '原始数据如下' print '用户信息表:' select * from userInfo print '卡信息表:' select * from cardInfo print '交易信息表' select * from transInfo GO --------------------------------3,常规业务----------------------------------------- /*(1)修改密码*/ update cardinfo set pass='123456' where cardid='1010 3576 1234 5678' update cardinfo set pass='123123' where cardid='1010 3576 1212 1134' GO /*(2)银行卡挂失*/ update cardinfo set IsReportLoss='是' where cardid='1010 3576 1212 1134' print '密码修改及卡挂失后数据为:' select * from cardInfo GO /*(3)统计银行的资金流通余额和盈利结算*/ declare @intmoney money,@outmoney money select @intmoney=sum(transMoney) from transInfo where transType='存入' select @outmoney=sum(transMoney) from transInfo where transType='支取' print '银行的资金流通余额为:'+convert(varchar(20),@intmoney-@outmoney)+'RMB' print '银行的资金盈利结算为:'+convert(varchar(20),@outmoney*0.008-@intmoney*0.003)+'RMB' GO /*(4)查询本周开户的卡号,显示该卡的相关信息*/ --求得今天星期几DATEPART(weekday,getdate()-1),在求出开户日期的时间在找个星期内 print '本周开户的卡号信息如下:' select * from cardinfo where datediff(day,getdate(),opendate)<=DATEPART(weekday,getdate()-1) GO /*(5)查询本月交易金额最高的卡号*/ print '本月交易金额最高的卡号是:' select 本月交易最高金额=cardid from transinfo where transmoney =(select max(transmoney) from transinfo) GO /*(6)查询挂失账号的客户信息*/ print '挂失账号的客户信息:' select 客户姓名=customerName,身份证=PID,联系电话=telephone,居住地址=address from userinfo where customerID in (select customerID from cardinfo where IsReportLoss='是') /*或者采用内部连接 select 客户姓名=customerName,身份证=PID,联系电话=telephone,居住地址=address from userinfo as u inner join cardinfo as c on u.customerID=c.customerID where c.IsReportLoss='是'*/ GO /*(7)催款提醒业务,查询余额少于200的用户致电*/ print '催款提醒业务,查询余额少于200的需要致电的用户有:' select 客户姓名=customerName,联系电话=telephone,卡上余额=balance from userinfo as u inner join cardinfo as c on u.customerID=c.customerID where balance<200 GO ----------------------------------4,创建索引和视图------------------------------------------------- use bankDB GO --1,创建索引 if exists(select name from sysindexes where name='IX_cardinfo_cardID') drop index cardinfo.IX_cardinfo_cardID GO create nonclustered index IX_cardinfo_cardID--创建卡信息表中卡号字段的索引 on transInfo(cardid) with fillfactor=70 GO --2,创建索引查询张三的交易记录 print '根据索引查询张三的交易记录:' select * from transInfo with(index=IX_cardinfo_cardID ) where cardid='1010 3576 1212 1134' GO --3,创建视图 --用户表 if exists(select * from sysobjects where name='viwe_userinfo') drop view viwe_userinfo GO create view viwe_userinfo AS select 客户姓名=customerName,身份证=PID,联系电话=telephone,居住地址=address from userinfo GO --卡信息表 if exists(select * from sysobjects where name='viwe_cardinfo') drop view viwe_cardinfo GO create view viwe_cardinfo AS select 卡号=cardID,货币种类=cur