Oracle数据库管理之道读书Java 杂谈

银行ATM取款机系统项目案例SQL实操

2019-04-24  本文已影响9人  爱学习的蹭蹭

1、前言与目的

2、学习三部曲

3、什么是SQL

4、为什么要SQL

5、怎么使用SQL

6 、SQL实操场景使用

表名 表描述(作用)
sysdatabases 系统数据库，用于查询用户创建的数据库
sysobjects 系统对象库，用户查询用户创建的数据库的表或视图
sysindexes 查询数据库的表的索引
关键字 关键字描述(作用)
begin 开始，在存储过程或声明声明式脚本中表示SQL语句的开始
begin tran 开始事务
rollback tran 事务回滚
commit tran 提交事务
end 结束，在存储过程或声明声明式脚本中表示SQL语句的结束
view 视图，在创建视图中使用
go 表示去执行的SQL语句
proc 创建存储过程关键字
declare 声明变量，变量以@进行修饰
exec 执行存储过程
print 打印信息
output 输出参数
filename 文件存储到某个地方
filegrowth 文件以百分之15增长
size 分配大小
use 使用当前数据库或说是切换到某个数据库
alter 修改或改变
exists 存在 
-- `切换master系统主干数据库`
use master 
go
    SET NOCOUNT ON
    if exists(select * from sysdatabases where name='bankDB')
    drop database bankDB
go

--`exec xp_cmdshell 'mkdir D:\bank'  调用DOS创建文件夹`
--`创建一个名称为bankDB数据库`
create database bankDB
ON
(
    name='bankDB_data',
    filename='D:\bank\bankDB_data.mdf', --文件存储到操作系统D盘的bank
    size=5mb,   -- 分配5兆
    filegrowth=15% -- 文件以百分之15增长
)
log on
(
    name='bankDB_log',
    filename='D:\bank\bankDB_log.ndf',
    size=5mb,
    filegrowth=15%
)
go

USE BANKDB
表名 表描述(作用)
cardInfo 银行卡信息表
transInfo 交易信息表
userInfo 用户信息表 
--`注意：删除表时有主外约束，首先删从表先`
go
--`银行卡信息表`
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
go

--`交易信息表`
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
go

--`用户信息表`
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
go


--`创建用户信息表`
create table userInfo
(
customerID int identity(1,1) not null primary key, --顾客编号,主键
customerName varchar(10)not null,     --开户名
PID varchar(30)not null unique,      --身份证号,身份证号唯一约束
telephone varchar(15)not null,      --联系电话格式xxxx-xxxxxxxx或手机号11位
address varchar(250)        --居住地址,可选输入
)
go
--`创建银行卡信息表`
create table cardInfo
(
cardID  varchar(50)not null primary key ,  --卡号,主键,格式为:1010 3576 xxxx xxxx 后面是随机的
curType varchar(10)not null,     --货币种类
savingType varchar(20),       --存款类型,活期/定活两便/定期
openDate datetime not null default getdate(), --开户日期,默认为系统当前日期
openMoney Money not null,      --开户金额,
balance money not null,       --余额,不低一元否则将稍户
pass varchar(50) default('888888')not null,  --密码,6位数字,
IsReportLoss char(10)not null default('否'), --是否挂失,否/是 默认为"否"
customerID int not null       --顾客编号表示该卡对应的顾客的编号,一位顾客允许办理多张卡
)

go
--`创建交易信息表`
create table transInfo
(
transDate datetime not null default getdate(), --交易日期,默认为当前日期
cardID varchar(50) not null,     --卡号,外键可重复索引
transType varchar(10) not null,     --交易类型,只能是存入或支取
transMoney money not null,      --交易金额,大于零
remark varchar(250),       --备注,可选输入,其他说明
)
go

-- `用户表`
alter table userInfo  add constraint ck_PID check(len(PID)>15 or len(PID)>18)--身份证号只能是18或15位,
--联系电话
alter table userInfo add 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]')

-- `银行信息表`
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]')
alter table cardinfo add constraint DF_curType default('RMB') FOR CURTYPE --默认为RMB
ALTER table cardinfo add constraint CK_savingType check (savingType in('活期','定活两便','定期'))
ALTER table cardinfo add constraint CK_openMoney check (openMoney>=1) --不低于一元
ALTER table cardinfo add constraint CK_balance check (balance>=1)  --不低于一元
ALTER table cardinfo add constraint DF_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]') --开户时密码 
alter table cardInfo add constraint FK_customerID foreign key(customerID)references userInfo(customerID)

--`交易表`
--`只能是存入或支取`
alter table transInfo add constraint CK_transType check(transType in('存入','支取'))  --或者是(transType='存入' or  transType='支取')
alter table transinfo ADD constraint CK_transMoney check(transMoney>0)    --大于零
alter table transInfo add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)

--`插入数据`
go
    insert into userInfo(customerName,PID,telephone,address) values('张三','1234567890123451','010-67898978','北京海淀区')
    insert into userInfo(customerName,PID,telephone) values('李四','32145678912345678','0478-44443333')
go
    insert into cardinfo values('1010 3576 1212 1134','RMB','定期',default,1.00,5001.00,888888,0,2)
    insert into cardinfo values('1010 3576 1234 5678','RMB','活期',default,1000.00,1000.00,888888,0,1)
    
    --`查询cardinfo`
    select * from cardinfo 
go
    insert into transInfo(cardID,transDate,transType,transMoney)values('1010 3576 1212 1134',getdate(),'存入',5000.00)
go

在BANKDB数据库的表修改(修改密码)

--`声明变量`
declare @cardID varchar(50)
    select @cardID=cardID from cardInfo  where customerID=(select customerID from userInfo where customerName='张三')
    update cardinfo set balance=balance-900  where customerid =(select customerid from userinfo   where customerName ='张三')
  
    insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@cardID,'支取',900.00)
    --`查询cardinfo`
    select * from cardinfo

update cardInfo set pass=case
 when cardid='1010 3576 1234 5678' then '123456'
 when cardid='1010 3576 1212 1134' then '123123'
end
select * from cardinfo

update cardinfo set IsReportLoss='是' where cardid='1010 3576 1212 1134'
select * from cardinfo

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'

select * from cardinfo 
 where datename(wk,getdate())=datepart(wk,openDate)

select * from cardinfo
 where  datepart(wk,getdate())=datepart(wk,openDate)

select distinct卡号=cardid from transinfo
  where transMoney=(select max(transMoney)from transinfo
   where datepart(mm,transDate)=datepart(mm,getdate()))

select customerName as 客户姓名,联系电话=telephone from userinfo
  where customerId in(select customerID from cardinfo where IsReportLoss='是')

SELECT *FROM userinfo AS a INNER JOIN cardinfo AS p ON a.customerId=p.customerId where  IsReportLoss='是'

select customerName as 客户姓名,联系电话=telephone,账上余额=balance
 from userInfo Inner Join cardInfo 
   on userinfo.customerId=cardInfo.customerId where balance<200

IF exists(select name from sysindexes where name='index_cardID')
drop index transInfo.index_cardID
go
create nonclustered index index_cardID
 on transInfo(cardid)
 with fillfactor=70
go

select * from transInfo with(index =index_cardID)
 where cardid=(select cardid from cardinfo 
  where customerid=(select customerid from userinfo 
   where customerName='张三'))

-- `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
select * from view_userInfo


-- `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,
   开户金额=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID
  from cardinfo
go
select * from view_cardInfo

--`view_tranInfo 交易信息视图`
if exists(select * from sysobjects where name='view_tranInfo')
    drop view view_tranInfo
go
    create view view_tranInfo
as
    select 交易日期=getdate(),交易类型=transType,卡号=cardId,交易金额=transMoney, 备注=remark from transinfo 
go
    select * from view_tranInfo

if exists(select * from sysobjects where name='proc_takeMoney')
drop proc proc_takeMoney
go
--`创建取款的存储过程`
create proc proc_takeMoney 
  @cardID char(22),
  @Money money,
  @type char(4),
  @inputPass varchar(6)=' ' 
as 
 declare @Mybalance money
 select @Mybalance=balance from cardInfo where cardid=@cardID

if (@type='支取')
begin
  if((select pass from cardinfo where cardID=@cardID)<>@inputPass)
   begin
  print '交易正进行,请稍后.....'
   raiserror('密码错误',16,1)
  return
   end
if(@Mybalance<=@Money) 
  begin
  raiserror('交易失败,余额不足',16,1)
   print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
  return 
  end 
else
  begin
  update cardinfo set balance=balance-@Money where cardID=@cardID
    print '交易成功，交易金额为'+convert(varchar(20),@Money)
  print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
  end
end
else
 begin
  update cardinfo set balance=balance+@Money where cardID=@cardID
   print '交易成功!交易金额为'+convert(varchar(10),@Money)
  print '卡号 '+@cardID+' 余额 '+convert(varchar(10),@Mybalance)
 end
--`插入数据`
insert into transInfo(transDate,cardID,transType,transMoney)
values(getdate(),@cardID,@type,@Money)
go

--`显示相关信息`
go
declare @cardID varchar(25),@balance money
select @cardID=cardid,@balance=balance from cardinfo 
  where customerid=(select customerid from userinfo 
   where customerName='张三')
exec proc_takeMoney @cardID,300,'支取','123456' --执行存储过程

--declare @cardID varchar(25),@balance money
select @cardID=cardid,@balance=balance from cardinfo 
 where customerid=(select customerid from userinfo 
  where customerName='李四')
--`插入记录`
exec proc_takeMoney @cardID,500,'存入',' '   --执行存储过程
go
select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

if exists(select * from sysobjects where name='proc_randCardID')
drop proc proc_randCardID
go
create proc proc_randCardID
 @randCardID char(25)output    --输出参数
as 
 declare @R numeric(15,8),@tempStr varchar(20) --15位数,保留8位小数
 select @R=rand((datepart(mm,getdate())*100000)+
 (datepart(ss,getdate())*1000)+datepart(ms,getdate()))
set @tempStr=convert(varchar(25),@R)  --存放随机数
set @randCardID='1010 3576'+' '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)--截取
go

--`执行存储过程`
declare @RandID char(19)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应

if exists(select * from sysobjects where name='Proc_openAccount')
drop proc Proc_openAccount
go
create proc Proc_openAccount
 @customerName varchar(20),
 @PID char(20),
 @telephone varchar(20),
 @openMoney money,
 @savingType varchar(20),
 @address varchar(100)=' '　　
as
declare @RandID char(19)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应
 while exists(select * from cardInfo where cardID=@RandID)
exec proc_randCardID @RandID output  --输出参数与创建存储过程一一对应

print '尊敬的客户,开户成功!系统为你产生的随机数卡号为: '+@RandID
print '开户日期'+convert(varchar(10),getdate(),111)+'开户金额为:'+convert(varchar(10),@openMoney)
--`插入数据`
insert into userInfo(customerName,PID,telephone,address)values(@customerName,@PID,@telephone,@address)
--`声明变量`
declare @customerID int 
select @customerID=customerID from userinfo where PID=@PID
exec proc_randCardID @RandID output 
--`插入数据`
insert into cardinfo(cardid,curType,savingType,openDate,openMoney,balance,pass,customerID)
values(@RandID,default,@savingType,getdate(),@openMoney,@openMoney,default,@customerID)
go
--`执行存储过程`
exec Proc_openAccount '王五','3344568890126780','2222-63598978',1000,'活期','河南新乡'
exec Proc_openAccount '赵二','213445678912342222','2222-44446666',1,'定期',' '

select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

create proc proc_transfer 
 @card1 char(19),
 @card2 char(19),
 @outMoney money 
as
--`开始事务`
begin tran
print '开始转账，请稍后........'
declare @error int
 set @error=0  --初始化
exec proc_takeMoney @card1,@outMoney,'支取','123123'  --调用取款存储过程
 set @error=@error+@@error
exec proc_takeMoney @card2,@outMoney,'存入'
 set @error=@error+@@error
if(@error>0)
 begin 
  print '交易失败'
  rollback tran 
 end
else 
 begin
  print '交易成功，交易金额：'+convert(varchar(9),@outMoney)
  commit tran
 end
go

--`执行`
--`从李四的账号转账2000元到张三`
declare @card1 varchar(22),@card2 varchar(22)
    select @card1=cardid from cardinfo inner join userinfo  on userinfo.customerid=cardinfo.customerid where userinfo.customerName='李四'
    select @card2=cardid from cardinfo inner join userinfo  on userinfo.customerid=cardinfo.customerid where userinfo.customerName='张三'
exec proc_transfer @card1,@card2,2000

select * from view_tranInfo
select * from view_cardInfo
select * from view_userInfo

exec sp_addlogin 'Admin','1234' --SQL Server 
exec sp_grantdbaccess 'Admin','sysAdminDBUser'
grant select,insert,update,delete on userInfo to  sysAdminDBUser
grant select,insert,update,delete on cardInfo to  sysAdminDBUser
grant select,insert,update,delete on transInfo to  sysAdminDBUser

7、总结

上一篇下一篇

猜你喜欢

热点阅读