SQL小型数据库系统设计

--------------创建数据库--------------------------

create database bankDB

on primary

(

name = 'bankDB',

filename = 'e:\bank\bankDB.mdf',

size = 3MB,

maxsize = 100MB,

filegrowth = 15%

)

log on

(

name = 'bankDB_log',

filename = 'e:\bank\bankDB_log.ldf',

size = 2MB,

filegrowth = 1MB

)

go

--------------------创建数据表---------------------

use bankDB

gocreate table userInfo

(

customerID int identity(1,1) not null,

customerName varchar(10) not null,

PID char(18) not null,

telphone char(13) not null,

adress varchar(100)

)alter table userInfo

add constraint PK_customerID primary key(customerID)

alter table userInfo

add constraint CK_PID check(PID like '[1-9][0-9][0-9][0-9][0-9][0-9][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

PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]X' or

PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

alter table userInfo

add constraint CK_telphone check(telphone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telphone 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]')

alter table userInfo

add constraint DF_telphone default('地址不详') for adress

alter table userInfo

add constraint UQ_PID unique(PID)create table cardInfo

(

cardID char(19) not null,

curType varchar(5) not null,

savingType varchar(6) not null,

openDate datetime not null,

openMoney money not null,

balance money not null,

pass char(6) not null,

IsReportLoss char(2) not null,

customerID int not null

)alter table cardInfo

add constraint FK_customerID foreign key(customerID) references userInfo(customerID)

alter table cardInfo

add constraint PK_cardID primary key(cardID)

alter table cardInfo

add constraint DF_openDate default(getdate()) for openDate

alter table cardInfo

add constraint DF_pass default('888888') for pass

alter table cardInfo

add constraint DF_curType default('RMB') for curType

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 CK_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]')

alter table cardInfo

add constraint DF_cardID default('1010 3576 ' + convert(varchar(4),convert(int,rand()*10000)) + ' ' +convert(varchar(4),convert(int,rand()*10000))) for cardID

alter table cardInfo

add constraint DF_IsReportLoss default('否') for IsReportLoss

alter table cardInfo

add constraint CK_IsReportLoss check(IsReportLoss like '是' or IsReportLoss like '否')

alter table cardInfo

add constraint CK_savingType check(savingType like '定期' or savingType like '活期' or savingType like '定活期')create table transInfo

(

transDate datetime not null,

cardID char(19) not null,

transType char(4) not null,

transMoney money not null,

remark varchar(100)

)alter table transInfo

add constraint DF_transDate default(getdate()) for transDate

alter table transInfo

add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)

alter table transInfo

add constraint CK_stransType check(transType like '存入' or transType like '支取')

alter table transInfo

add constraint CK_transMoney check(transMoney >0)go

--------------------创建开户用户卡信息视图---------

create view v_cus

as

select customerName, cardID,curType,savingType,telphone,adress

from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID

where datediff(wk,openDate,getdate()) = 0-----------------------修改密码--------------------

create proc changePWD

@cardID char(19),

@newpwd char(6),

@oldpwd char(6) = '888888'

as

declare @temppwd int

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

select @temppwd = pass from cardInfo where cardID = @cardID

if(@temppwd = @oldpwd)

begin

update cardInfo set pass = @newpwd where cardID = @cardID

if(@@error = 0)

print '密码修改成功！'

else

print '密码修改失败！'

end

else

print '您输入的原始密码不对，不能修改密码！'----------------------yhk挂失-------------------

create proc ReportLoss

@cardID char(19),

@PID char(18)

as

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失！'

return

end

if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)

begin

update cardInfo set IsReportLoss = '是' where cardID = @cardID

if(@@error = 0)

print '挂失成功！'

else

print '挂失失败！'

end

else

begin

print '您输入的身份z错误！不能挂失！'

return

end----------------------取消yhk挂失-------------------

create proc CancelReportLoss

@cardID char(19),

@PID char(18)

as

if((select count(*) from cardInfo where cardID = @cardID) = 0)

begin

print '您输入的卡号不存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID) = '否')

begin

print '该卡没有挂失！不能进行此项 *** 作！'

return

end

if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)

begin

update cardInfo set IsReportLoss = '否' where cardID = @cardID

if(@@error = 0)

print '取消挂失成功！'

else

print '取消挂失失败！'

end

else

begin

print '您输入的身份z错误！不能取消挂失！'

return

end----------------------统计银行的资金流通余额和盈利结算--------------------create proc StatMoney

as

select ((select sum(transMoney) as 资金流通余额 from transInfo where transType like '存入') - (select sum(transMoney) as 资金流通余额 from transInfo where transType like '支取'))

as 资金流通余额 select ((select sum(transMoney) as 资金流通余额 from transInfo where transType like '存入')*0.008

- (select sum(transMoney) as 资金流通余额 from transInfo where transType like '支取')*0.003)

as 盈利结算 ---------------------查询本周开户的卡号，显示该卡的信息-------------------

create proc SearchNewCus

as

print '本周开户卡号信息'

select * from v_cus -----------------------查询本月交易金额最高的卡号-------------------------

create proc SearchHigh

as

select top 1 cardID, sum(transMoney) as 交易金额 from transInfo

group by cardID------------------------------查询挂失帐号的客户信息----------------------

create proc SearchLossCus

as

print '挂失帐号客户信息'

select * from userInfo where customerID = (select customerID from cardInfo where IsReportLoss = '是')--------------------------催款提醒业务------------------------------------

create proc Awoke

as

select telphone, balance, customerName, cardID

from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID

where balance <200-------------------------银行开户-----------------------------------------

create proc OpenUser

@curType varchar(5),

@savingType varchar(6),

@openMoney money,

@PID char(18),

@telphone char(13),

@customerName varchar(10)

as

begin tran OpenU

declare @tag int

declare @tempID varchar(10)

set @tag = 0 insert into userInfo (customerName,PID,telphone) values (@customerName, @PID, @telphone)

set @tag = @tag + @@error

if(@tag = 0)

set @tempID = (select customerID from userInfo where PID = @PID)

insert into cardInfo (curType, savingType, openMoney, balance, customerID) values (@curType, @savingType, @openMoney, @openMoney, @tempID)

set @tag = @tag + @@error

if(@tag = 0)

begin

print '开户成功！'

commit tran

end

else

begin

print '开户失败！'

rollback tran

end-----------------------------客户存款-------------------------------------

create proc SaveMoney

@cardID char(19),

@transMoney money,

@transType char(4) = '存入'

as

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran Saving

declare @tag int

set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)

set @tag = @tag + @@error

update cardInfo set balance = balance + @transMoney where cardID = @cardID

set @tag = @tag + @@error

if(@tag = 0)

begin

print '存款成功！'

commit tran

end

else

begin

print '存款失败!'

rollback tran

end ------------------------------客户取款------------------------------------

create proc DrawMoney

@cardID char(19),

@transMoney money,

@transType char(4) = '支取'

as

if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')

begin

print '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran Drawing

declare @tag int

set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)

set @tag = @tag + @@error

update cardInfo set balance = balance - @transMoney where cardID = @cardID

set @tag = @tag + @@error

if(@tag = 0)

begin

print '取款成功！'

commit tran

end

else

begin

print '取款失败!'

rollback tran

end----------------------------转帐------------------------------------------

create proc Transfer

@cardID_out char(19),

@cardID_in char(19),

@tranMoney money

as

if((select count(*) from cardInfo where cardID = @cardID_out or cardID = @cardID_in) <>2)

begin

print '请确认两张卡是否都存在！'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID_out) = '是')

begin

print @cardID_out + '该卡已挂失!无法对其进行 *** 作!'

return

end

if((select IsReportLoss from cardInfo where cardID = @cardID_in) = '是')

begin

print @cardID_in + '该卡已挂失!无法对其进行 *** 作!'

return

end

begin tran TransMoney

declare @tag int

set @tag = 0 update cardInfo set balance = balance - @tranMoney where cardID = @cardID_out

set @tag = @tag + @@error

update cardInfo set balance = balance + @tranMoney where cardID = @cardID_in

set @tag = @tag + @@error

if(@tag = 0)

begin

print '转帐成功！'

commit tran

end

else

begin

print '转帐失败!'

rollback tran

end

用access建立一个数据库例子如下：

单击“开始”→”所有程序“→”Microsoft

Office“→”Microsoft

Access

2010“，打开Microsoft

Access

2010软件

在打开的Microsoft

Access

2010软件中选中“空数据库”双击即可创建。或者打击“创建”命令按钮创建，其中文件名处可以选择指定具体的文件名和路径。

创建空白数据库后可以看到一个新建了一个名为“表1”的数据表，界面右侧显示了它的字段。若要添加字段，可以单击“单击以添加”旁的倒三角箭头，选择要添加的字段类型。

此时光标会定位在字段名称上，可以对字段名称进行重命名，重命名字段名称后按下回车键，将继续下一个字段的添加 *** 作。

通过以上的步骤就可以创建一个包含单个数据表的简易的空白数据库了。

---