SQL银行数据库管理语句(详细版)

create table UserInfo(

CustomerId char(20),

CustomerName char(50)not null,

PID varchar(20)unique,

Telephone varchar(20),

Address char(50),

PRIMARY KEY(CustomerId))

create table CardInfo(

cardID char(50)not null,

curType varchar(10)not null default'RMB',

savingType varchar(10),

openDate datetime default getdate(),

openMoney int not null,

balance int not null,

pass char(50)not null default'888888',

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

customerID char(20)not null foreign key(customerID)references userinfo(customerID), PRIMARY KEY(CardId))

create table TransInfo(

transDate datetime not null default'getdate()',

cardID char(50)foreign key(cardID)references cardinfo(cardID),

transType char(10)not null,

transMoney bigint not null,

remark char(20),

ID int identity(1,1)not null)

Insert into userinfo

values('10001','李清','420101************','2071-84216821','湖北武汉');

Insert into userinfo

values('10002','玉清','420101************','2071-84216820','湖北武汉');

INSERT INTO CardInfo

V ALUES('102128001','RMB','活期',getdate(),'10000','10000','84212121','否','10001'); INSERT INTO CardInfo

V ALUES('102128002','RMB','活期',getdate(),'10001','10001','84202020','否','10002');

select*FROM cardInfo where(datediff(day,getDate(),openDate)

Select cardid from cardinfo

Where(datediff(month,getDate(),openDate))<0

and cardid in(select top 1 with ties cardid from transinfo

order by transMoney)

Select sum(transMoney)from Transinfo Where transType='存入'

Select sum(transMoney)from transinfo Where transType='支取'

Select((Select sum(transMoney)from Transinfo Where transType='存入')

-(Select sum(transMoney)from transinfo Where transType='支取'))as资金流通余额

Select((Select sum(transMoney)from Transinfo Where transType='存入')*0.08

-(Select sum(transMoney)from transinfo Where transType='支取')*0.03)as盈利结算

Create unique index T_cardid on cardinfo(cardid)

Create index S_cardid on transinfo(cardid)

Create VIEW test(卡号,开户名,开户日期,余额,存款类型,身份证号,电话号码,居住地址)

AS

Select cardid,customername,opendate,openmoney,savingtype,PID,Telephone,address From cardinfo left outer join userinfo

On cardinfo. customerID=userinfo. customerID

create proc zhuanzhang

@tmoney bigint,

@password char(50),

@putcardid char(50),

@incardid char(50)

as

BEGIN

if(@password!=(select pass from cardinfo where cardid=@putcardid))

print'密码错误，请重试'

else if(((select balance from cardinfo where cardid=@putcardid)-@tmoney)<1)

print'余额不足，请确定后输入'

else

begin

insert into transinfo values(getdate(),@putcardid,'支取',@tmoney,'无')

insert into transinfo values(getdate(),@incardid,'存入',@tmoney,'无')

update cardinfo set balance=balance-@tmoney where cardid=@putcardid

update cardinfo set balance=balance+@tmoney where cardid=@incardid end

END

create proc cunkuan

@inmoney bigint,

@password char(50),

@inercardid char(50)

as

BEGIN

if(@password!=(select pass from cardinfo where cardid=@inercardid))

print'密码错误，请重试'

else if(@inmoney<0)print'不能输入负金额'

else

begin

insert into transinfo values(getdate(),@inercardid,'存入',@inmoney,'无')

update cardinfo set balance=balance+@inmoney where cardid=@inercardid end

END

create proc qunkuan

@inmoney bigint,

@password char(50),

@inercardid char(50)

as

BEGIN

if(@password!=(select pass from cardinfo where cardid=@inercardid))

print'密码错误，请重试'

else if(@inmoney<0)print'不能输入负金额'

else if(((select balance from cardinfo where cardid=@inercardid)-@inmoney)<1) print'余额不足，请确定后输入'

else

begin

insert into transinfo values(getdate(),@inercardid,'存入',@inmoney,'无')

update cardinfo set balance=balance+@inmoney where cardid=@inercardid

end

END

create trigger WARM

on cardinfo

instead of update

as

BEGIN

declare @oldmoney bigint

declare @newmoney bigint

select @oldmoney=balance from deleted;

select @newmoney=balance from inserted;

if(@newmoney<1)

begin

select cardid,balance from deleted;

print'您的余额不足'

end;

END