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
