xp_cmdshell 'md E:\斌\S2 SQL Server\项目实战\DataBase'
go
--检测是否存在bankDB数据库
if exists(select 1 from sys.databases where name='bankDB')
drop database bankDB
go
--创建bankDB数据库
create database bankDB
on primary
(
name='bankDB_data',
filename='E:\斌\S2 SQL Server\项目实战\DataBase\bankDB_data.mdf',
size=3mb,
maxsize=500mb,
filegrowth=15%
)
log on
(
name='bankDB_data_log',
filename='E:\斌\S2 SQL Server\项目实战\DataBase\bankDB_data_log.ldf'
)
go
--创建表userInfo(用户信息表),并添加约束
use bankDB
go
create table userInfo
(
customerID int identity(1,1) primary key, --主键,顾客编号
customerName varchar(10) not null, --用户名
PID varchar(18) not null, --身份证号
telephone varchar(11) not null, --电话号码
address text --地址
)
go
--添加表userInfo(用户信息表)约束
alter table userInfo
add constraint CK_PID check(len(PID) in(15,18)),
constraint UQ_PID unique(PID),
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]' 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]' or telephone like '1[3-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
go
--创建表cardInfo(银行卡信息表),并添加约束
create table cardInfo
(
cardID char(19) primary key not null, --主键,银行的卡号
curType varchar(10) not null, --货币种类
savingType varchar(8) not null, --存款类型
openDate datetime not null, --开户日期
openMoney money not null, --开户金额
balance money not null, --余额
pass int not null, --密码
IsReportLoss char(2) not null, --是否挂失
customerID int not null --顾客编号
)
go
--添加表cardInfo(银行卡信息表)约束
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]'),
constraint DF_curType default('RMB') for curType,
constraint CK_savingType check(savingType in('活期','定活两便','定期')),
constraint DF_openDate default(getdate()) for openDate,
constraint CK_openMoney check(openMoney>=1),
constraint CK_balance check(balance>=1),
constraint DF_pass default(888888) for pass,
constraint DF_IsReportLoss default('否') for IsReportLoss,
constraint FK_cardInfo_userInfo foreign key(customerID) references userInfo(customerID)
go
--创建表transInfo(交易信息表),并添加约束
create table transInfo
(
transDate datetime not null, --交易日期
cardID char(19) not null, --卡号
transType char(4) not null, --交易类型
transMoney money not null, --交易金额
remark text --备注
)
go
--添加表transInfo(交易信息表)约束
alter table transInfo
add constraint DF_transDate default(getdate()) for transDate,
constraint FK_cardID_cardInfo foreign key(cardID) references cardInfo(cardID),
constraint CK_transType check(transType in('存入','支取')),
constraint CK_transMoney check(transMoney>0)
go
/*------插入测试数据------*/
insert into userInfo(customerName,PID,telephone,address)
values('张三',123456789012345,'010-67898978','北京海淀')
insert into userInfo(customerName,PID,telephone)
values('李四',321234567891234567,'0478-44443333')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1234 5678','活期','1000','1000',1)
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1212 1134','定期','1','1',2)
--插入交易信息
update cardInfo set balance=balance-900
from cardInfo where cardID='1010 3576 1234 5678'
update cardInfo set balance=balance+5000
from cardInfo where cardID='1010 3576 1212 1134'
insert into transInfo(cardID,transType,transMoney)
values('1010 3576 1234 5678','支取','900')
insert into transInfo(cardID,transType,transMoney)
values('1010 3576 1212 1134','存入','5000')
select * from transInfo
select * from cardInfo
/*------常规业务模拟------*/
--修改密码
update cardInfo set pass='123456' where cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' where cardID='1010 3576 1212 1134'
--银行卡挂失
update cardInfo set IsReportLoss='是' where cardID='1010 3576 1212 1134'
--统计银行的资金流通余额和盈利结算
select 资金流通余额=((select sum(transMoney) from transInfo where transType='存入')-(select sum(transMoney) from transInfo where transType='支取'))
select 盈利结算=((select sum(transMoney) from transInfo where transType='支取')*0.008-(select sum(transMoney) from transInfo where transType='存入')*0.003)
--查询本周开户的卡号
select * from cardinfo
where datediff(dd,openDate,getdate())<datepart(dw,getdate())
--查询本月交易金额最高的卡号
select cardID from transInfo where transMoney=(select max(transMoney) from transInfo)
--查询挂失账号的客户信息
select 客户姓名=customerName,身份证号码=PID,电话号码=telephone,家庭住址=address
from cardInfo inner join userInfo
on (userInfo.customerID=cardInfo.customerID)
where IsReportLoss='是'
--催款提醒业务
select 客户姓名=customerName,身份证号码=PID,电话号码=telephone,家庭住址=address,账上余额=balance
from cardInfo inner join userInfo
on (userInfo.customerID=cardInfo.customerID)
where balance<200
/*------创建索引和视图------*/
--创建索引
if exists(select 1 from sys.indexes where name='IX_cardInfo_cardID')
drop index transInfo.IX_cardInfo_cardID
go
create nonclustered index IX_cardInfo_cardID
on transInfo(cardID)
with fillfactor=70
go
select * from transInfo with(index=IX_cardInfo_cardID) where cardID='1010 3576 1212 1134'
--创建视图
--1
if exists(select 1 from sys.objects where name='View_userInfo')
drop view View_userInfo
go
create view View_userInfo
as
select * from userInfo
go
--2
if exists(select 1 from sys.objects where name='View_cardInfo')
drop view View_cardInfo
go
create view View_cardInfo
as
select * from cardInfo
go
--3
if exists(select 1 from sys.objects where name='View_transInfo')
drop view View_transInfo
go
create view View_transInfo
as
select * from transInfo
go
--查询测试
select * from View_userInfo
select * from View_cardInfo
select * from View_transInfo
go
/*------创建存储过程------*/
--创建取钱的存储过程
if exists(select 1 from sys.objects where name='proc_takeMoney')
drop procedure proc_takeMoney
go
create procedure proc_takeMoney
@cardID char(19), --取钱的卡号
@takeMoney money, --取钱的金额
@takeType char(4), --交易类型
@pass char(6) --密码
as
if(@pass=(select pass from cardInfo where cardID=@cardID)) --密码正确
begin
if((select balance from cardInfo where cardID=@cardID)-@takeMoney>=1) --满足交易条件
begin
update cardInfo set balance=balance-@takeMoney
from cardInfo where cardID=@cardID
insert into transInfo(cardID,transType,transMoney)
values(@cardID,@takeType,@takeMoney)
declare @balance1 money --余额
select @balance1=balance from cardInfo where cardID=@cardID
print '交易正在进行,请稍后......'
print '交易成功!交易金额:'+convert(varchar(20),@takeMoney)
print '卡号'+@cardID+' 余额:'+convert(varchar(20),@balance1)
end
else --不满足交易条件,金额不足!
begin
declare @balance2 money --余额
select @balance2=balance from cardInfo where cardID=@cardID
print '交易正在进行,请稍后......'
print '交易失败!余额不足!'
print '卡号'+@cardID+' 余额:'+@balance2
end
end
else --密码不正确
begin
print '密码错误!'
end
go
--插入测试数据
exec proc_takeMoney '101
ATM机模拟系统、SQL Server 2005
3星 · 超过75%的资源 需积分: 10 11 浏览量
2009-02-04
14:19:08
上传
评论 1
收藏 382KB RAR 举报 
ATM机模拟系统.rar （3个子文件） 
DataBase 
bankDB_data_log.ldf 1024KB bankDB_data.mdf 3MB SQL文件 项目实战.sql 12KB资源评论
sinat_291616072015-06-24只有ＳＱＬ语句代码，要是有实现整个系统的源代码就好了
