SQL Server 数据库设计

SQL Server 系统数据库在安装软件时自动创建，用于协助系统共同完成对数据库的 *** 作；也是数据库运行的基础；

1，master数据库

是SQL Server 2012的核心数据库，如果损坏则数据库软件无法运行，主要包含如下主要信息：

1）所有用户登陆名和用户ID所属角色

2）数据库存储路径

3）服务器中数据库的名称和相关信息

4）系统配置设置， SQL Server 初始化信息

2，model数据库

在创建数据库时，总是以一套预定义的标准为模板进行创建的。以model数据库为模板来创建其他数据库。且model数据库是tempdb数据库的基础。

3，tempdb数据库

它是一个临时数据库，用来存储用户建立的临时表和临时存储过程，存储用户定义的全局变量值。它存在于SQL Server会话期间，会话结束，则关闭tempdb数据库，且数据库丢失。

4，msdb数据库

用于代理计划警报和作业

SQL Server 数据库存储文件

数据库文件是由数据文件和事务日志文件组成。

1，数据库文件指数据库中用来存储数据库数据和数据库对象的文件，一个数据库只能由一个主数据库文件，扩展名为 .mdf

2, 次数据库文件包含除主数据库文件外的所有数据文件，一个数据库可以没有次数据库文件，也可以由多个，扩展名为 .ndf

3, 日志文件由一系列日志记录组成，它记录了存储数据库的更新情况等事务日志信息，用户对数据库进行的插入，删除，更新都会记录在日志文件中。数据库发生损坏时可根据日志文件分析出错原因，或者数据丢失时，使用事务日志恢复数据库。每个数据库至少要有一个日志文件。

SQL Server 数据库创建，使用T-SQL语言创建：

使用T-SQL语言删除数据库：

SQL Server 数据库迁移：

方法1：“分离/附加”数据库，即把数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘上作备份，然后把这两个文件再拷贝到任何需要这个数据库的系统之中。

分离数据库就是将某个数据库从SQL Server数据库列表中删除，使其不再被SQL Server管理和使用，但该数据库的文件（.MDF）和对应的日志文件（.LDF）完好无损。分离成功后，就可以把该数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘中作为备份保存。

分离之前，设置数据库为单个用户，并记住该数据库所在路径。

“任务”—“分离”

然后分离数据库页面选中“更新统计信息”复选框。若“消息”列中没有显示存在活动连接，则“状态”列显示为“就绪”；否则显示“未就绪”，此时必须勾选“删除连接”列的复选框。分离后资源管理器中数据库消失

将需要附加的数据库文件和日志文件拷贝到某个已经创建好的文件夹中。

右击数据库对象，并在快捷菜单中选择“附加”命令，打开“附加数据库”窗口。

添加—选择需要附件的数据库的.MDF文件。“附件为”数据库名称可修改。

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

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

---