use master
go
set nocount on
--使用 DOS 命令创建文件夹 bank
exec xp_cmdshell 'mkdir D:\bank',no_output
--检查是否存在'bankDB'数据库
if exists(select * from sysDatabases where name='bankDB')
drop database bankDB
go
/*-- 创建数据库 bankDB --*/
create database bankDB ---主数据库文件
on
(
name='bankDB_Data',
filename='D:\bank\bankDB_Data.mdf',
size=5MB,
filegrowth=15%
)
log on ---日志文件
(
name='bankDB_log',
filename='D:\bank\bankDB_Log.ldf',
size=3MB,
filegrowth=15%
)
go
use bankDB
go
/*----- 创建用户表 (userInfo)-----*/
if exists(select * from sysObjects where name='userInfo')
drop table userInfo
go
create table userInfo
(
customerID int identity(1,1) primary key not null, --顾客编号，主键，自动增长
customerName varchar(10) not null, --开户名
PID varchar(18) unique check(len(PID)=15 or len(PID)=18) not null, --身份证号，唯一约束
telephone varchar(15)
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]') not null, --联系电话
address varchar(100) --居住地址
)
go
/*----- 创建银行卡信息表 (cardInfo)-----*/
if exists(select * from sysObjects where name='cardInfo')
drop table cardInfo
go
create table cardInfo
(
cardID char(19) primary key check(cardID like'1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]') not null, --卡号，主键
curType varchar(8) default('RMB') not null, --货币种类，默认为 'RMB'
savingType varchar(8) check(savingType in('活期','定活两便','定期')) not null, --存款类型
openDate datetime default(getDate()) not null, --开户日期，默认为当前日期
openMoney money check(openMoney>=1) not null, --开户金额，不低于 1元
balance money check(balance>=1) not null, --余额，不低于 1元
pass char(6) check(len(pass)=6) default('888888') not null, --密码，6位数字
IsReportLoss char(2) check(IsReportLoss in('是','否')) default('否') not null, --是否挂失
customerID int foreign key references userInfo(customerID) not null --顾客编号，外键，引用 userInfo(customerID) 列
)
go
/*----- 创建交易表(transInfo)-----*/
if exists(select * from sysObjects where name='transInfo')
drop table transInfo
go
create table transInfo
(
transDate datetime default(getDate()) not null, --交易日期，默认为当前日期
transType char(4) check(transType in('存入','支取')) not null, --交易类型，只能是 存入/支取
cardID char(19) foreign key references cardInfo(cardID) not null, --卡号，外键，引用 cardInfo(cardID) 列
transMoney money check(transMoney >0) not null, --交易金额，必须 >0
remark varchar(100) --备注
)
go
/*----- 向用户表 (userInfo) 插入数据-----*/
insert into userInfo(customerName,PID,telephone,address)
values('张三','123456789012345','010-67898978','北京海淀')
insert into userInfo(customerName,PID,telephone,address)
values('李四','321245678912345678','0478-44443333',null)
select * from userInfo
/*----- 向卡号表 (cardInfo) 插入数据-----*/
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,pass,IsReportLoss,customerID)
values('1010 3576 1212 1134',default,'定期',default,1,1,default,'否',2)
insert into cardInfo(cardID,curType,savingType,openDate,openMoney,balance,pass,IsReportLoss,customerID)
values('1010 3576 1234 5678',default,'活期',default,1000,1000,default,'否',1)
select * from cardInfo
go
/*----- 向交易表 (transInfo) 插入数据-----*/
insert into transInfo(transDate,transType,cardID,transMoney,remark)
values(default,'支取','1010 3576 1234 5678',900,null)
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'
insert into transInfo(transDate,transType,cardID,transMoney,remark)
values(default,'存入','1010 3576 1212 1134',5000,null)
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'
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' --李四
--银行卡挂失(李四)
update cardInfo set IsReportLoss='是' where cardID='1010 3576 1212 1134'
select * from cardInfo
go
---流通额和盈利计算
declare @inMoney money ---总存入量
declare @outMoney money ---总支取量
select @inMoney=sum(transMoney) from transInfo where transType='存入'
print convert(varchar,@inMOney)
select @outMoney=sum(transMoney) from transInfo where transType='支取'
print '银行流通余额总计为：'+convert(varchar,@inMoney-@outMoney)+' RMB'
print '盈利结算为：'+convert(varchar,@outMoney*0.008-@inMoney*0.003) +'RMB'
---查询本周开户的卡号信息
select * from cardInfo where datepart(week,getDate()) = datepart(week,openDate)
---查询本月交易金额最高的卡号
select top 1 卡号=cardID ,最高交易金额=max(transMoney)from transInfo
where datepart(mm,getDate())=datepart(mm,transDate)
group by cardID order by max(transMoney)desc
---查询挂失账号信息
select 客户姓名=customerName,联系电话=telephone
from userInfo where customerID =(select customerID from cardInfo where IsReportLoss='是')
---催款提醒业务
select 客户姓名=customerName,联系电话=telephone,账上余额=cardInfo.balance
from userInfo inner join cardInfo on userInfo.customerID = cardInfo.customerID
where balance<200 and datepart(day,getDate())>=25
/** ---检查是否存在索引：IX_transInfo_cardID --- **/
if exists(select * from sysIndexes where name='IX_transInfo_cardID')
drop index transInfo.IX_transInfo_cardID
go
--创建索引：IX_cardInfo_cardID
create nonclustered index IX_transInfo_cardID
on transInfo(cardID)
with fillfactor=70
go
---按索引查询
select * from transInfo with(index=IX_transInfo_cardID)
where cardID=(select cardID from cardInfo where customerID=
(select customerID from userInfo where customerName='张三')
)
go
/** ---创建视图(1)：view_userInfo --- **/
if exists(select * from sysObjects where name='view_userInfo')
drop view view_userInfo
go
create view view_userInfo
as
select 客户编号=customerID,开户名称=customerName,身份证号=PID,电话号码=telephone,居住地址=address
from userInfo
go
/** ---创建视图(2)：view_cardInfo --- **/
if exists(select * from sysObjects where name='view_cardInfo')
drop view view_cardInfo
go
create view view_cardInfo
as
select 卡号=cardId,货币类型=curType,存款类型=savingType,开户日期=openDate,
余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID
from cardInfo
go
/** ---创建视图(3)：view_transInfo --- **/
if exists(select * from sysObjects where name='view_transInfo')
drop view view_transInfo
go
create view view_transInfo
as
select 交易日期=transDate,交易类型=transType,卡号=cardID,交易金额=transMoney,备注=remark
from transInfo
go
---按查询视图：view_userInfo、view_cardInfo、view_transInfo
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
/*------------------ 创建存储过程(1)：proc_takeMoney --------------------*/
if exists(select * from sysObjects where name='proc_takeMoney')
drop procedure proc_takeMoney
go
create procedure proc_takeMoney
@err int output,
@card char(19),@m money,@type char(4),@pwd char(6)='' --输入参数：卡号、交易金额、交易类型、密码
as
set nocount on
declare @balance money --变量：余额
if(@type='支取')
begin
print '<<<<<交易进行中，请稍后......>>>>>'
if @pwd=(select pass from cardInfo where cardID=@card)
begin
if @m>(select balance from cardInfo where cardID=@card )
begin
raiserror('交易失败！余额不足！',16,1)
set @err=1
select @balance=balance from cardInfo where cardID=@card
print '卡号：'+@card+' 余额：'+convert(varchar,@balance)
return
end
else
begin
print '交易成功! 交易金额：' +convert(varchar,@m)
select @ba
bank.rar （2个子文件） 
bank 
bank.sql 13KB bank.vsd 82KB
yikejie
- 粉丝: 0
最新资源
- 集成 Apollo 配置中心实现数据库、MongoDB、Redis 动态切换及 MyBatis 与 Redis 缓存集成
- 轻量型文件数据采集抓取与日志信息汇总工具
- 使用DriveMonitor软件调试MM440变频器
- 使用starter调试MM440
- 基于Python语言的Spark数据处理分析案例集锦（PySpark）
- CCKS’2021 研究：SGSum- 面向体育赛事摘要的人工标注数据集介绍
- PHP与MySQL动态网站设计实战指南
- 基于 MapReduce 的大数据采集清洗处理及离线分析完整案例
- 《Rust并发编程：解锁高性能系统的密钥》，详细介绍Rust并发编程的基础知识及其在高性能系统开发中的应用
- 轻量型文件数据采集抓取及日志信息汇总工具
- 使用 YOLO 检测废弃行李 该项目旨在通过使用实时视频源和 YOLO（You Only Look Once）物体检测算法自动检测无人看管或遗弃的行李来提高公共交通安全
- 用户为中心的设计：系统设计师必读
- 大数据采集、清洗、处理：使用MapReduce进行离线数据分析完整案例
- 3089048582变压器测试仪 快速操作指南（第一版）.pdf
- HDFS 集群之间的数据相互交换方式
- easy prism - 安卓平台便捷好用的埋点数据收集中间件
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
评论0