创建文件夹 USE master
GO
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
建库
IF exists(SELECT * FROM sysdatabases WHERE name='bankDB')
DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
ON
(
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=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
加约束
/* userInfo表的约束
customerID 顾客编号 自动编号(标识列),从1开始,主键
customerName 开户名 必填
PID 身份证号 必填,只能是18位或15位,身份证号唯一约束
telephone 联系电话 必填,格式为xxxx-xxxxxxxx或手机号13位
address 居住地址 可选输入
ALTER TABLE userInfo
ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID),
CONSTRAINT CK_PID CHECK( len(PID)=18 or 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][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 len(telephone)=13 )
GO
/*cardInfo表的约束
cardID 卡号 必填,主健 , 银行的卡号规则和电话号码一样,一般前8位代表特殊含义,
如某总行某支行等。假定该行要求其营业厅的卡号格式为:1010 3576 xxxx xxx开始
curType 货币 必填,默认为RMB
savingType 存款种类 活期/定活两便/定期
openDate 开户日期 必填,默认为系统当前日期
openMoney 开户金额 必填,不低于1元
balance 余额 必填,不低于1元,否则将销户
pass 密码 必填,6位数字,默认为6个8
IsReportLoss 是否挂失 必填,是/否值,默认为”否”
customerID 顾客编号 必填,表示该卡对应的顾客编号,一位顾客可以办理多张卡
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
/* transInfo表的约束
transType 必填,只能是存入/支取
cardID 卡号 必填,外健,可重复索引
transMoney 交易金额 必填,大于0
transDate 交易日期 必填,默认为系统当前日期
remark 备注 可选输入,其他说明
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
插入测试数据
张三开户,身份证:123456789012345,电话:010-67898978,地址:北京海淀
开户金额:1000 活期 卡号:1010 3576 1234 5678
李四开户,身份证:321245678912345678,电话:0478-44443333,
开户金额: 1 定期 卡号:1010 3576 1212 1134
*/
SET NOCOUNT ON
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES('张三','123456789012345','010-67898978','北京海淀')
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)
VALUES('1010 3576 1234 5678','活期',1000,1000,1)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('李四','321245678912345678','0478-44443333')
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)
VALUES('1010 3576 1212 1134','定期',1,1,2)
SELECT * FROM userInfo
SELECT * FROM cardInfo
GO
/*
张三的卡号(1010 3576 1234 5678)取款900元,李四的卡号(1010 3576 1212 1134)存款5000元,要求保存交易记录,以便客户查询和银行业务统计。
说明:当存钱或取钱(如300元)时候,会往交易信息表(transInfo)中添加一条交易记录,
同时应更新银行卡信息表(cardInfo)中的现有余额(如增加或减少500元)
*/
/*
INSERT INTO transInfo(transType,cardID,transMoney)
VALUES('支取','1010 3576 1234 5678',900)
/*
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'
/*
INSERT INTO transInfo(transType,cardID,transMoney)
VALUES('存入','1010 3576 1212 1134',5000)
/*
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134'
GO
/*
SELECT * FROM cardInfo
SELECT * FROM transInfo
/*
常规业务操作
/*
update cardInfo set pass='123456' WHERE cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' WHERE cardID='1010 3576 1212 1134'
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 1234 5678'
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 ,'存入'
select * from view_cardInfo
select * from view_transInfo
GO
create procedure proc_randCardID @randCardID char(19) OUTPUT
AS
DECLARE @r numeric(15,8)
DECLARE @tempStr char(10)
SELECT @r=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
set @tempStr=convert(char(10),@r)
set @randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)
GO
DECLARE @mycardID char(19)
EXECUTE proc_randCardID @mycardID OUTPUT
print '产生的随机卡号为:'+@mycardID
GO
create procedure proc_openAccount @customerName char(8),@PID char(18),@telephone char(13)
,@openMoney money,@savingType char(8),@address varchar(50)=''
AS
DECLARE @mycardID char(19),@cur_customerID int
EXECUTE proc_randCardID @mycardID OUTPUT
while exists(SELECT * FROM cardInfo WHERE cardID=@mycardID)
EXECUTE proc_randCardID @mycardID OUTPUT
print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID
print '开户日期'+convert(char(10),getdate(),111)+' 开户金额:'+convert(varchar(20),@openMoney)
IF not exists(select * from userInfo where PID=@PID)
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES(@customerName,@PID,@telephone,@address)
select @cur_customerID=customerID from userInfo where PID=@PID
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)
VALUES(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)
GO
EXEC proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
EXEC proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期'
select * from view_userInfo
select * from view_cardInfo
GO
/*
事务
create procedure proc_transfer @card1 char(19),@card2 char(19),@outmoney money
AS
begin tran
print '开始转帐,请稍后......'
DECLARE @errors int
set @errors=0
EXEC proc_takeMoney @card1,@outmoney ,'支取','123123'
set @errors=@errors+@@error
EXEC proc_takeMoney @card2,@outmoney ,'存入'
set @errors=@errors+@@error
if (@errors>0)
begin
print '转帐失败!'
rollback tran
end
else
begin
print '转帐成功!'
commit tran
end
GO
declare @card1 char(19),@card2 char(19)
select @card1=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='李四'
select @card2=cardID from cardInfo Inner Join userInfo ON
cardInfo.customerID=userInfo.customerID where customerName='张三'
EXEC proc_transfer @card1,@card2,2000
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
GO
/*
安全
If not exists(SELECT * FROM master.dbo.syslogins WHERE loginname='sysAdmin')
begin
EXEC sp_addlogin 'sysAdmin', '1234'
EXEC sp_defaultdb 'sysAdmin' , 'bankDB'
end
go
EXEC sp_grantdbaccess 'sysAdmin', 'sysAdminDBUser'
GO
GRANT SELECT,insert,update,delete,select ON transInfo TO sysAdminDBUser
GRANT SELECT,insert,update,delete,select ON userInfo TO sysAdminDBUser
GRANT SELECT,insert,update,delete,select ON cardInfo TO sysAdminDBUser
GO