--1 建库
--1.1 在D盘创建bank文件夹
use master
exec xp_cmdshell 'mkdir d:\bank',NO_OUTPUT
go
-- 1.2 检测是否存在bankDB
if exists(select * from sysdatabases where name='bankDB' )
begin
drop database bankDB
end
go
--1.3 建bankDB
create database bankDB -- 数据库的逻辑名称
on primary --默认的组(可不写)
(
name='bankDB_data',
filename='d:\bank\bankDB_data.mdf', --数据库的物理文件
size=5,
maxsize=100,
filegrowth=15%
)
log on --数据库的日志文件
(
name='bankDB_log',
filename='d:\bank\bankDB_log.ldf',
size=2,
filegrowth=1
)
GO
--2 建表加约束
use bankDB
--(因数据库是肯定是刚创好的，所以可以不写 drop table userInfo)
--2.1 create table userInfo
create table userInfo
(
customerID int identity(1,1) primary key ,
customerName varchar(20) not null,
PID varchar(18) not null unique check (len(PID)=18 or len(PID)=15),
telephone varchar(20) not null 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 len(telephone)=11),
address varchar(50) null
)
go
-- 2.2 create table cardInfo
create table cardInfo
(
cardID varchar(20) primary key check( cardId like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
curType varchar(10) not null default('RMB'),
savingType varchar(10) check (savingType in('活期','定活两便','定期')),
openDate datetime not null default (getdate()),
openMoney money not null check (openMoney>=1),
balance money not null check (balance>=1),
pass char(6) not null default '888888' check( len(pass)=6),
IsReportLoss varchar(2) default '否' check(IsReportLoss in('是','否')),
customerID int foreign key references userInfo(customerID)
)
go
-- 2.3 create table transDate
create table transInfo
(
transDate datetime not null default(getdate()),
cardID varchar(20) not null foreign key references cardInfo(cardID),
transType char(4) check(transType in ('存入','支取')),
transMoney money not null check(transMoney >0),
remark varchar(40)
)
go
--3 写入测试数据 (人工)
--3.1 insert into UserInfo
insert into userInfo(customerName,PID,telephone,address) values('张三','123456789012345','0010-67898978','北京海淀')
insert into userInfo(customerName,PID,telephone) values('李四','321245678912345678','0478-44443333')
go
--3.2 insert into cardInfo
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID) values('1010 3576 1212 1134',default,'定期',default,1,1,2)
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID) values('1010 3576 1234 5678',default,'活期',default,1000,1000,1)
go
--3.3 insert into transInfo
insert into transInfo(transDate,transType,cardID,transMoney)values(getDate(),'支取','1010 3576 1212 1134',900)
insert into transInfo(transDate,transType,cardID,transMoney)values(getDate(),'存入','1010 3576 1234 5678',5000)
go
--显示
select * from userInfo
select * from cardInfo
go
--4 常规业务模似
--4.1 修改用户密码
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
go
--4.2 挂失账号
update cardinfo set IsReportLoss='是' where cardID='1010 3576 1212 1134' -- 李四
select * from cardInfo where cardID='1010 3576 1212 1134'
go
--4.3 资金流通余额和盈利结算
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'
print '盈利结算为：'+convert(varchar(20),@outmoney*0.008-@inmoney*0.003)+'RMB'
go
--4.4 查询本周开户的Card
select * from cardInfo where DATEDIFF (ww,OpenDate,getDate())=0
--4.4 查询本月交易最高的卡号
select top 1 cardId,sum(transmoney) as 交易总金额 from transinfo
where datediff(mm,getDate(),transdate)=0
group by cardId
order by sum(transmoney) desc
--4.5 挂失
select customername as 客户姓名,telephone as 联系电话 from userinfo u inner join cardinfo c on u.customerid=c.customerid where isreportloss='是'
--4.6 提醒业务
select customername as 客户姓名,telephone as 联系电话,balance as 卡上余额
from userinfo u inner join cardinfo c on u.customerid=c.customerid
where balance<=200
--5 创建索引和视图
--5.1 创建索引
CREATE INDEX index_card
ON transinfo (cardid)
WITH FILLFACTOR = 70
go
select * from transinfo where cardid='1010 3576 1212 1134'
go
--5.2创建视图
--5.21 v_userinfo
create view v_userinfo
as
select customerID as 客户编号,customername as 开户名,pid as 身份证号,telephone as 电话号码,address as 居住地址 from userinfo
go
select * from v_userinfo
go
--5.22 v_cardinfo
create view v_cardinfo
as
select cardid as 卡号,curtype as 货币种类,savingtype as 存款类型,opendate as 开户日期,balance as 余额,pass as 密码,isreportloss as 是否挂失,customerid as 客户编号 from cardinfo
go
select * from v_cardinfo
go
--5.23 v_transInfo
create view v_transInfo
as
select transdate as 交易日期,transtype as 交易类型,cardid as 卡号,transmoney as 交易金额,remark as 备注 from transInfo
go
select * from v_transInfo
go
--6 创建触发器
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_trans' )
DROP TRIGGER trig_trans
GO
create trigger trig_trans
on transInfo
for insert
as
declare @myTransType char(4),@outMoney money,@myCardID varchar(30),@zhi_balance money
select @myTransType=transType,@outMoney=transMoney,@myCardID=cardID from inserted
if(@myTranstype='存入')
begin
update cardinfo set balance =balance+@outMoney where cardid=@mycardid
print '交易成功,存入'+convert(varchar(15),@outMoney)+'RMB'
end
else
begin
select @zhi_balance=balance-@outMoney from cardinfo where cardid=@mycardid
if(@zhi_balance<=0)
begin
raiserror('余额不足！',16,1)
rollback tran
end
else
begin
update cardinfo set balance =balance-@outMoney where cardid=@mycardid
print '交易成功,支取'+convert(varchar(15),@outMoney)+'RMB'
end
end
--////////////////////
go
--7：测试触发器
-- 7.1.1 Error
insert into transInfo(transDate,transType,cardID,transMoney)values(getDate(),'支取','1010 3576 1234 5678',1000)
go
-- 7.1.2 NotError
insert into transInfo(transDate,transType,cardID,transMoney)values('2005-9-1','存入','1010 3576 1212 1134',200)
go
--8：创建存储过程
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc_takeMoney' AND type = 'P')
DROP PROCEDURE proc_takeMoney
GO
create proc proc_takeMoney
@card char(19),@m money,@type char(4),@inputpass char(6)=''
as
if exists(select * from cardinfo where cardid=@card and pass=@inputpass)
begin
if(@type='支取')
begin
insert into transInfo(transType,cardID,transMoney)values('支取',@card,@m)
print '成功支取'+convert(varchar(12),@m)+'RMB'
end
else
begin
insert into transInfo(transType,cardID,transMoney)values('存入',@card,@m)
print '成功存入'+convert(varchar(12),@m)+'RMB'
end
end
else
begin
--rollback tran
print '密码错误'
end
go
exec proc_takeMoney '1010 3576 1234 5678',300,'支取','34323' --密码错误
exec proc_takeMoney '1010 3576 1234 5678',300,'支取','888888'
go
--9：产生随机卡号存储过程
IF
评论0