atm.c

可以实现当前自动取款机的各功能

/****************2创建数据库*******************/
use master
go

if exists (select * from sysdatabases where name='bankDB')--检查系统中是否已存在数据库atm
drop database bankDB --如果存在则删除

go
exec xp_cmdshell 'mkdir G:\二期学习\SQL Server\bank' ,no_output--调用DOS命令创建文件夹
create database bankDB --新建数据库
on primary  --主文件
(
name = 'atm_data', --逻辑名称
filename='G:\二期学习\SQL Server\bank\bankDB_data.mdf', --物理名称
size=10 , --初始大小
filegrowth=15%  --增长率
)
log on --日志文件
(
name='atm_log', --逻辑名称
filename='G:\二期学习\SQL Server\bank\bankDB_log.ldf',--物理名称
size=1 , --初始大小
filegrowth=10% --增长率
)
go

/**********************3建表***********************/
use bankDB
go
-------------------1用户信息表userInfo------------------
if exists (select * from sysobjects where name='userInfo')
drop table userInfo
go
create table userInfo
(
customerID  int identity(1,1) not null,--顾客编号，自动编号（标识列),从1开始,主键
customerName varchar (8) not null ,--开户名
PID varchar(18) not null,--身份证号,必填，只能是18位或15位，身份证号唯一约束
telephone varchar(18) not null,--联系电话,必填，格式为xxxx-xxxxxxxx或手机号13位
address varchar(20)--居住地址,可选输入
)

--------------------2银行卡信息表cardInfo------------------
if exists (select * from sysobjects where name='cardInfo')
drop table  cardInfo
go

create table cardInfo
(
cardID char (19) not null,--卡号，必填，主健,银行的卡号规则和电话号码一样，一般前8位代表特殊含义 假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始,每4位号码后有空格，卡号一般是随机产生.
curType  char(4) not null, --货币种类 
savingType char(8), --存款类型 活期/定活两便/定期
openDate datetime not null,--开户日期 
openMoney money not null, --开户金额 
balance money not null, --余额  
pass int  not null ,--密码  
IsReportLoss bit  not null, --是否挂失  
customerID int not null --顾客编号 
)
go
------------------3交易信息表transInfo -------------------
if exists (select * from sysobjects where name='transInfo')
drop table  transInfo
go

create table transInfo
(
transDate  datetime not null,--交易日期，必填，默认为系统当前日期
cardID char (19) not null,--卡号，必填，外健，可重复索引
transType  char(8) not null,--交易类型，必填，只能是存入/支取
transMoney money not null,--交易金额，必填，大于0
remark text--备注，可选输入，其他说明
)
go

/************************4添加约束*****************************/

-----------------------1用户信息表userInfo 添加约束--------------

--主键约束customerID 顾客编号 自动编号（标识列）
if exists (select * from sysobjects where name='pr_customerID')
alter table userInfo
drop constraint pr_customerID
alter table userInfo
add constraint pr_customerID primary key (customerID)

--检查约束customerID 身份证号 只能是18位或15位
if exists (select * from sysobjects where name='ck_PID')
alter table userInfo
drop constraint ck_PID
alter table userInfo
add constraint ck_PID check (
PID 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][0-9][0-9][0-9]'
or PID 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][0-9][0-9][0-9][0-9][0-9][0-9,x]'
)


--唯一约束customerID身份证号 
if exists (select * from sysobjects where name='uq_PID')
alter table userInfo
drop constraint uq_PID
alter table userInfo
add constraint uq_PID  unique(customerID)


--检查约束telephone电话号码格式为xxxx-xxxxxxxx或手机号13位
if exists (select * from sysobjects where name='ck_telephone')
alter table userInfo
drop constraint ck_telephone
alter table userInfo
add constraint ck_telephone check 
(telephone like '1[3,5][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][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][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
--(len(telephone)-13 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])

 

-----------------2银行卡信息表cardInfo 添加约束------------------
--主键约束 cardID 卡号
if exists (select * from sysobjects where name='pr_cardID')
alter table cardInfo
drop constraint pr_cardID
alter table cardInfo
add constraint pr_cardID primary key (cardID)

--检查约束 cardID 卡号格式为：1010 3576 xxxx xxx开始
if exists (select * from sysobjects where name='ck_cardID')
alter table cardInfo
drop constraint ck_cardID
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]')

--检查约束  存款类型 活期/定活两便/定期
if exists (select * from sysobjects where name='ck_savingType')
alter table cardInfo
drop constraint ck_savingType
alter table cardInfo
add constraint ck_savingType 
check (savingType in('活期','定活两便','定期'))

--默认约束 curType货币种类 默认RMB
if exists (select * from sysobjects where name='df_curType')
alter table cardInfo
drop constraint df_curType
alter table cardInfo
add constraint df_curType 
default ('RMB') for curType 

--默认约束 openDate开户日期 默认为系统当前日期
if exists (select * from sysobjects where name='df_openDate')
alter table cardInfo
drop constraint df_openDate
alter table cardInfo
add constraint df_openDate 
default (getdate()) for openDate


--检查约束 openMoney开户金额 不低于1元
if exists (select * from sysobjects where name='ck_openMoney')
alter table cardInfo
drop constraint ck_openMoney
alter table cardInfo
add constraint ck_openMoney 
check (openMoney>=1)

--检查约束 balance余额 不低于1元,否则将销户
if exists (select * from sysobjects where name='ck_balance')
alter table cardInfo
drop constraint ck_balance
alter table cardInfo
add constraint ck_balance 
check (balance>=1)

--检查约束 pass密码为6位
if exists (select * from sysobjects where name='ck_pass')
alter table cardInfo
drop constraint ck_pass
alter table cardInfo
add constraint ck_pass 
check (len(pass)=6)

--默认约束 pass密码默认888888
if exists (select * from sysobjects where name='df_pass')
alter table cardInfo
drop constraint df_pass
alter table cardInfo
add constraint df_pass 
default (888888) for pass

--默认约束 IsReportLoss 是否挂失  是/否值，默认为”否”
if exists (select * from sysobjects where name='df_IsReportLoss')
alter table cardInfo
drop constraint df_IsReportLoss
alter table cardInfo
add constraint df_IsReportLoss 
default (0) for IsReportLoss 

--检查约束  IsReportLoss 是否挂失  只能为是/否值 
if exists (select * from sysobjects where name='ck_IsReportLoss')
alter table cardInfo
drop constraint ck_IsReportLoss
alter table cardInfo
add constraint ck_IsReportLoss 
check (IsReportLoss in (1,0))

--外键约束 customerID 表示该卡对应的顾客编号,一位顾客允许办理多张卡号
if exists (select * from sysobjects where name='fr_customerID')
alter table cardInfo
drop constraint fr_customerID
alter table cardInfo
add constraint fr_customerID 
foreign key (customerID) references userInfo(customerID)

 

------------------3交易信息表transInfo 添加约束---------------

--默认约束 transDate交易日期，默认为系统当前日期
if exists (select * from sysobjects where name='df_transDate')
alter table transInfo 
drop constraint df_transDate
alter table transInfo 
add constraint df_transDate 
default (getdate()) for transDate

--外键约束 cardID 卡号
if exists (select * from sysobjects where name='pr_cardID2')
alter table transInfo 
drop constraint pr_cardID2
alter table transInfo 
add constraint pr_cardID2
foreign key (cardID) references  cardInfo(cardID)

--检查约束 transType 交易类型 只能是存入/支取
if exists (select * from sysobjects where name='ck_transType')
alter table transInfo
drop constraint ck_transType
alter table transInfo
add constraint ck_transType 
check (transType in('存入','支取'))

--检查约束 transMoney 交易金额 大于0
if exists (select * from sysobjects where name='ck_transMoney')
alter table transInfo
drop constraint ck_transMoney
alter table transInfo
add constraint ck_transMoney 
check (transMoney>0)


/*********************5插入数据*******************************/
---------------1测试 插入 userInfo 用户信息表 和 cardInfo银行卡信息表 数据------
insert userInfo (customerName, PID ,telephone, address)
values  ( '张三','123456789012345','010-67898978','北京海淀' )
insert cardInfo (cardID ,savingType ,openMoney,balance,customerID)
values ('1010 3576 1234 5678','活期',1000,1000,@@identity)

insert userInfo (customerName, PID ,telephone)
values('李四','321245678912345678','0478-44443333')
insert cardInfo (cardID ,savingType ,openMoney,balance,customerID)
values ('1010 3576 1212 1134','定期',1 ,1 ,@@identity)


-- delete  from cardInfo  delete  from userInfo 

-----------------2测试 手动取款交易 插入交易信息表transInfo数据--------------
--测试1 手动添加交易表信息,更新卡号信息表中的数据
declare @cardid  char(19)--定义变量 @cardid 卡号

select @cardid=cardid from cardInfo --从userInfo用户表中获取客户为 张三 的卡号cardid
where customerID=(select customerID from userInfo where customername='张三')

insert into transinfo (transtype,cardid,transmoney)
values ('支取',@cardid,900)--向交易信息表transInfo插入数据：张三 支取 900元 卡号为@cardid 

update cardinfo set balance =balance-900 --更改 cardInfo银行卡信息表 张三 余额balance 减去900
where customerID =(select customerID from userInfo where customername='张三')

--测试2  手动添加交易表信息,更新卡号信息表中的数据
declare @cardid2  char(19)--定义变量 @cardid 卡号

select @cardid2=cardid from cardInfo  --从userInfo用户表中获取客户为 李四 的卡号cardid
where customerID=(select customerID from userInfo where customername='李四')

insert into transinfo (transtype,cardid,transmoney)
values ('存入',@cardid2,5000)--向交易信息表transInfo插入数据：李四 存入 5000元 卡号为@cardid2 

update cardinfo set balance =balance+5000 --更改 cardInfo银行卡信息表 李四 余额balance 增加5000
where customerID =(select customerID from userInfo where customername='李四')

 

/******************************6常规业务模拟***************************/

----------------------6.1修改密码--------------------------
--说明*修改张三(卡号为 1010 3576 1234 5678) 银行卡号密码为123456
update  cardInfo set pass=123456 
where customerid=(select customerid from userInfo where customername='张三')

--说明*修改李四(卡号为 1010 3567 1212 1134) 银行卡号密码为123123
update  cardInfo set pass=123123
where customerid=(select customerid from userInfo where customername='李四')
 
select * from cardInfo--2银行卡信息表：cardInfo 

------------------------6.2挂失帐号----------------------------
--说明*李四 (卡号为1010 3567 1212 1134) 因银行卡丢失,申请挂失
--说明*修改张三(卡号为 1010 3576 1234 5678) 银行卡号密码为123456
update  cardInfo set pass=123456 
where customerid=(select customerid from userInfo where customername='张三')

--说明*修改李四(卡号为 1010 3567 1212 1134) 银行卡号密码为123123
update  cardInfo set pass=123123
where customerid=(select customerid from userInfo where customername='李四')select * from cardInfo--2银行卡信息表：cardInfo 

------------------------6.3统计银行的支金流通余额和盈利结算----------
--说明*存入 代表资金流入,支取 代表资金流出

--declare @inMoney money
--select @inMoney- sum(transMoney) from transInfo where (transType-'存入')-sum(transMoney) from transinfo where transType-'支取')--错误不要了

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'
--盈利结算-总支取量*0.008-总存入量*0.003
print '盈利结算为'+left(convert(varchar(10),@outMoney*0.008-@inMoney*0.003),4)+'RMB'
 

--------------------------6.4查询本周开户的卡号-----------------------
/*declare @xtday datetime
set @xtday- datepart(dw,getdate())

declare @a datetime 
select  @a-openDate from cardInfo where cardID-'1010 3576 1212 1134'
print datediff (weekday,@a,getdate())
print datediff (dd,@a,getdate())
print @xtday 
print datepart(dw,getdate())-1*/

--print '本周开户的卡号信息如下:'--错误的
--select cardID from cardInfo 
--where datediff(day,datepart(weekday,opendate),datepart(weekday,getdate()))<7--错了
--where datename(dw,opendate)-datename(dw,getdate()) and month(openDate)-month(getdate())and year(openDate)-year(getdate())--同一天的

--显示样式1
print '本周开户的卡号信息如下:'
select cardID from cardInfo 
where datediff (dd,opendate,getdate())<-(datepart(dw,getdate())-1)
select * from cardInfo 
--显示样式2
print '本周开户的卡号信息如下:'
declare @temp int 
select 客户姓名=customerName,联系电话=telephone ,开户金额=openMoney,
开户日期=opendate from userInfo inner
join cardinfo on  userinfo.customerID=cardinfo.customerID 
where datediff(dd,opendate,getdate())<=(datepart(dw,getdate())-1)
select * from cardinfo

select datepart(dw,getdate())-1  --求出当前周几
select datediff(dd,opendate,getdate()) from cardinfo
--函数第1个参数放天数,第2个参数填写列中字段,第3个是当前日期(求出2个日期之间的差值) 

 


------------------------6.5查询本月交易金额最高的卡号-----------------
print '本月交易金额最高的卡号'
select distinct cardID from transinfo 
where transMoney=(select  max(transMoney) from transinfo) --交易金额最高
and datediff(Month,transDate,getdate())=0--本月