use master
go
/*建库*/
if exists(select*from sysdatabases where name='bankDB')
DROP database bankDB
go
create database bankDB
on
(
name='bankDB_data',
filename='F:\十一月\非常重要\我的银行系统\bankDB_data.mdf',
size=3mb,
filegrowth=15%
)
log on
(
name='bankDB_log',
filename='F:\十一月\非常重要\我的银行系统\bankDB_log.ldf',
size=3mb,
filegrowth=15%
)
go
use bankDB
GO
/*建表*/
CREATE TABLE UserInfo
(
customerID int identity (1,1),
customerName char(8) not null,
pID char (18)not null,
telephone char (13)not null,
address varchar(50)
)
go
create table CardInfo
(
cardID char(19)not null,
curType char(5)not null,
savingType char(8)not null,
openDate datetime not null,
openMoney money not null,
balance money not null ,
pass char(6)not null,
Isreportloss bit not null,
customerID int not null
)
go
create table transInfo
(
transDate datetime not null,
transType char(4)not null,
cardID CHAR(19) NOT NULL,
transMoney MONEY NOT NULL,
remark TEXT
)
go
/*加约束*/
alter table UserInfo
add constraint PK_customerID primary key(customerID),
constraint CK_PID check(len(PID)=18or len(PID)=15),
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]' or len(telephone)=13 )
go
alter table CardInfo
add constraint PK_cardID primary key(cardID),
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 CK_pass check(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
constraint DF_pass default('888888') for pass,
constraint DF_IsReportLoss default(0) for IsReportLoss,
constraint FK_customerID foreign key (customerID) references userInfo(customerID)
go
alter table TransInfo
add constraint CK_transType check(transType IN ('存入','支取')),
constraint FK_cardID foreign key(cardID) references cardInfo(cardID),
constraint CK_transMoney check(transMoney>0),
constraint DF_transDATE default(getdate()) for transDate
go
/*插入测试数据*/
set nocount on
insert into userInfo(customerName,PID,telephone,address )
values('张玲','341225198801021234','010-67898978','北京天安门')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1111 2222','活期',1000,1000,1)
insert into userInfo(customerName,PID,telephone,address)
values('李迎会','341225199209174924','0558-68162030','安徽阜阳')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 1212 1134','定期',10000,10000,5)
insert into userInfo(customerName,PID,telephone)
values('帅小李','341225199806258888','0558-68168888')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values('1010 3576 6666 5210','定期',1,1,6)
select *from userInfo
select * from cardInfo
GO
/*存入支取*/
insert into transInfo(transType,cardID,transMoney)
values('支取','1010 3576 1111 2222',900)
update cardInfo set balance=balance-900
where cardID='1010 3576 1111 2222'
insert into transInfo(transType,cardID,transMoney)
values('存入','1010 3576 1212 1134',5000)
update cardInfo set balance=balance+5000
where cardID='1010 3576 1212 1134'
insert into transInfo(transType,cardID,transMoney)
values('存入','1010 3576 6666 5210',5000)
update cardInfo set balance=balance+5000
where cardID='1010 3576 6666 5210'
GO
/* 检 测 */
select * FROM cardInfo
select * FROM transInfo
update cardInfo set pass='123456' WHERE cardID='1010 3576 1111 2222'
update cardInfo set pass='123123' WHERE cardID='1010 3576 1212 1134'
update cardInfo set pass='917917' WHERE cardID='1010 3576 6666 5210'
select * FROM cardInfo
update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 1212 1134'
select * FROM cardInfo
GO
select * FROM cardInfo WHERE ((balance between 3000 and 6000) and (savingType='定期') )
DECLARE @inMoney money
DECLARE @outMoney money
DECLARE @profit money
select * FROM transInfo
select @inMoney=sum(transMoney) FROM transInfo WHERE (transType='存入')
select @outMoney=sum(transMoney) FROM transInfo WHERE (transType='支取')
print '银行流通余额总计为:'+ convert(varchar(20),@inMoney-@outMoney)+'RMB'
set @profit=@outMoney*0.008-@inMoney*0.003
print '盈利结算为:'+ convert(varchar(20),@profit)+'RMB'
GO
select * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate))
select * FROM transInfo
select DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney) FROM transInfo)
select customerName as 客户姓名,telephone as 联系电话 FROM userInfo
WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)
select customerName as 客户姓名,telephone as 联系电话,balance as 帐上余额
FROM userInfo INNER JOIN cardInfo ON userInfo.customerID=cardInfo.customerID WHERE balance<200
/*索引和视图*/
create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITH FILLFACTOR=70
GO
select * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 6666 5210'
GO
create VIEW view_userInfo
AS
select customerID as 客户编号,customerName as 开户名, PID as 身份证号,
telephone as 电话号码,address as 居住地址 from userInfo
GO
create VIEW view_cardInfo
AS
select cardID as 卡号,curType as 货币种类, savingType as 存款类型,openDate as 开户日期,
balance as 余额,pass 密码,IsReportLoss as 是否挂失,customerID as 客户编号 from cardInfo
GO
create VIEW view_transInfo
AS
select transDate as 交易日期,transType as 交易类型, cardID as 卡号,transMoney as 交易金额,
remark as 备注 from transInfo
GO
/*存储过程*/
create procedure proc_takeMoney @card char(19),@m money,@type char(4),@inputPass char(6)=''
AS
print '交易正进行,请稍后......'
if (@type='支取')
if ((SELECT pass FROM cardInfo WHERE cardID=@card)<>@inputPass )
begin
raiserror ('密码错误!',16,1)
return
end
DECLARE @myTransType char(4),@outMoney MONEY,@myCardID char(19)
SELECT @myTransType=transType,@outMoney=transMoney ,@myCardID=cardID FROM transInfo where cardID=@card
DECLARE @mybalance money
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card
if (@type='支取')
if (@mybalance>=@m+1)
update cardInfo set balance=balance-@m WHERE cardID=@myCardID
else
begin
raiserror ('交易失败!余额不足!',16,1)
print '卡号'+@card+' 余额:'+convert(varchar(20),@mybalance)
return
end
else
update cardInfo set balance=balance+@m WHERE cardID=@card
print '交易成功!交易金额:'+convert(varchar(20),@m)
SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card
print '卡号'+@card+' 余额:'+convert(varchar(20),@mybalance)
INSERT INTO transInfo(transType,cardID,transMoney) VALUES(@type,@card,@m)
GO
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张玲'
EXEC proc_takeMoney @card,300 ,'支取','123456'
GO
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='李迎会'
EXEC proc_takeMoney @card,500 ,'存入'
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userI