简易银行存取款系统-建立数据库

use master

if exists(select * from sysdatabases where name='Bank')

  drop database Bank

go

;EXEC sp_configure 'show advanced options', 1 --

;RECONFIGURE WITH OVERRIDE --

;EXEC sp_configure 'xp_cmdshell', 1 --

;RECONFIGURE WITH OVERRIDE --

;EXEC sp_configure  'show advanced options', 0 --

 

exec xp_cmdshell 'mkdir E:/bank'

create database Bank

on primary

(

    name='Bank_data',

    filename='E:/bank/Bank_data.mdf',

    filegrowth=15%

)

log on

(

    name='Bank_log',

    filename='E:/bank/Bank_log.ldf'

)

go

use Bank

--创建表userInfo

if exists(select * from sysobjects where name ='userInfo')

  drop table userInfo

go

create table userInfo

(

    customerID int identity(1,1) primary key,

    customerName varchar(15) not null,

    PID varchar(18) not null check(len(PID)=15 or len(PID)=18) unique,---唯一约束

    telephone varchar(13) not null check(len(telephone)=11 or (len(telephone)=13 and (charindex('-',telephone)=5 or charindex('-',telephone)=4))),

    address varchar(30)

)

---创建表cardInfo

if exists(select * from sysobjects where name='cardInfo')

  drop table cardInfo

create table cardInfo

(

    cardID varchar(19) not null 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(8) not null check(savingType in ('活期','定活两便','定期')),

    openData datetime not null default(getdate()),

    openMoney money not null check(openMoney>=1),

    balance money not null check(balance>=1),

    pass varchar(6) not null check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]') default(888888),---密码为位数字，默认为个“”

    IsReportLoss char(2) not null default('否'),

    customerID int not null foreign key references userInfo(customerID)

)

---创建表transInfo

if exists (select * from sysobjects where name='transInfo')

    drop table transInfo

go

create table transInfo

(

    transData datetime not null default(getdate()),

    cardID varchar(19) not null foreign key references cardInfo(cardID),

    transType varchar(4) not null check(transType in ('存入','支出')),

    transMoney money not null check(transMoney>0),

    remark varchar(50)  ---备注

)

 

---插入测试数据

insert into userInfo

select '张三','123456789012345','010-6789978','北京朝阳' union

select '李四','321245678978945621','0478-44443333',NULL

--select charindex('-','sda-21fgf3')

select * from userInfo

 

insert into CardInfo

select '1010 3576 1234 5678','RMB','活期','2008-9-9',1000,1000,'888888','否',1 union

select '1010 3576 1212 1134','RMB','定期','2007-8-9',1,900,'888888','否',2

select * from CardInfo

 

---插入交易信息

print '交易前：'

print ''

select * from CardInfo

 

declare @errorSum int

begin transaction

    insert into transInfo(cardID,transType,transMoney) values('1010 3576 1234 5678','支出',500)

    set @errorSum=@errorSum+@@error

    update dbo.cardInfo set balance=balance-100 where cardID in (select cardID from dbo.cardInfo where customerID in (select customerID from dbo.userInfo where customerName='张三'))

    set @errorSum=@errorSum+@@error

if @errorSum<>0

begin

    rollback transaction

    print '交易失败'

end

else

begin

    commit transaction

    print '交易成功'

end

go

declare @errorSum2 int

begin transaction

    insert into transInfo(cardID,transType,transMoney) values('1010 3576 1212 1134','存入',300)

    set @errorSum2=@errorSum2+@@error

    update dbo.cardInfo set balance=balance+500 where cardID=(select cardID from dbo.cardInfo where customerID=(select customerID from userInfo where customerName='李四'))

    set @errorSum2=@errorSum2+@@error

if @errorSum2<>0

begin

    rollback transaction

    print '交易失败'

end

else

begin

    commit transaction

    print '交易成功'

end

print '交易后：'

print ''

select * from CardInfo