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

一、SQL的作用：

1、SQL主要用于数据库系列的软件的查询、汇总、写入、删改等方面的 *** 控，具体应用于数据库编程或数据库数据的维护。

2、也就是说SQL一般用于数据库编程，以及现有数据库错误排查。所以一般涉及数据库的编程人员或电脑信息化系统维护人员需要对SQL（也就是对数据库）知识进行学习。

二、相关知识：

1、SQL是结构化查询语言(Structured Query Language)的简称。

2、在当前它最主要应用于微软的SQL Server和甲骨文公司的Oracle数据库服务器中对数据的 *** 控。其它的小型数据库、单机数据库也往往使用它作为接口指令语句，比如MYSQL、ACCESS等。

3、在大型数据库应用中，一般都会经由程序开发设计人员将特定功能对应的SQL语句嵌入到程序中，需要多条复杂SQL语句时，还会以储存过程等方式集写在数据库服务器中进行调用。用户往往不需要与专业化的SQL语句打交道。

1、MSSQL，不管网络的单机的，都适合。2、是要装，一般都是把安装脚本写好，跟你的应用程序一起打包装的。3、ACCESS对生成的安装包比SQL要简单，至于要安全，数据部分可以用MD5算法加密，再写进数据库里。

---