上学时的sql项目（模拟ATM），今日复习顺便共享一下
drop database bank --删除库
drop database bank --删除库
create database bank --创建库
on
(
name='bank',
size=5mb,
filename='d:\bank\bank_mdf.mdf',
maxsize=100mb,
filegrowth=10%
)
log on --日志文件一般用于恢复数据
(
name='bank_ndf',
size=5mb,
filename='d:\bank\bank_ndf.ndf',
maxsize=100mb
)
go
use bank
go
-----------------------------------------------------
create table userinfo --创建表
(
customerid int identity(1,1),--identity自增长说明
customername nvarchar(10) not null,
pid nvarchar(20) not null,
telephone nvarchar(20) not null,
[address] nvarchar(50) --加入中括号防止与数据库私有关键字冲突）
)
--------------------------建表userinfo成功
alter table userinfo --为表创建约束
add constraint PK_customerid primary key (customerid),--主键约束
constraint UQ_pid unique (pid),--唯一约束
constraint CK_pid check (len(pid) = 15 or len(pid) = 18),--check约束（本句意思是pid字段长度必须为15或者18）
constraint ck_telephone check (len(telephone) = 11 or telephone like '%-%') --%表示任意字符，
go
----------------------------------------------------------------------------------------
--userinfo表的约束建完成
create table cardinfo
(
cardid varchar(20) not null,
curtype nvarchar(10) not null,
saveingtype nvarchar(10) not null,
opendate datetime not null,
openmoney money not null,
balance money not null,
pass nvarchar(20) not null,
isreportloss bit not null,
customerid int not null
) --cardinfo表建成
----------------------------------------------------------------
alter table cardinfo
add
constraint df_curtype default ('rmb') for curtype, --默认约束，在insert数据时，若本字段为指明值则使用默认值
constraint pk_cardid primary key (cardid), --主键
constraint ck_savingtype check( saveingtype in ('活期','定活两用','定期')), --check约束中 in表示规定可选值
constraint df_opendate default (getdate()) for opendate, --getdate()获取当前时间，使用当前时间作为默认值
constraint df_openmoney check (openmoney>=1),
constraint df_balance check (balance>=1),
constraint df_pass default ('888888') for pass,
constraint ck_pass check (len(pass)=6),
constraint df_isreportloss default (0) for isreportloss,
constraint fk_customerid_usrinfo_customerid foreign key(customerid) references userinfo(customerid) --外键约束，表示cardinfo.customerid的值必须在userinfo.customerid中存在。后者为主键，前者为外键
--cardinfo表约束创建成功
---------------------------------------------------------------------------------------------------------------
create table transinfo
(
transdate datetime not null,
cardid varchar(20) not null,
transtype nvarchar(4) not null,
transmoney money not null,
remark nvarchar(100) ,
) --transinfo表创建成功
----------------------------------------------------------------------------------------------------------------
alter table transinfo
add constraint df_transdate default(getdate()) for transdate,
constraint fK_cardid_cardinfo_cardid foreign key (cardid) references cardinfo(cardid),
constraint ck_transtype check (transtype in ('存入','支取')),
constraint ck_transmoney check (transmoney>0)
go --transinfo表约束创建成功
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
/* --插入数据
insert into userinfo(customername,pid,telephone,address)
values ('张三','123456789012345','010-67898978','北京海淀')
insert into cardinfo(openmoney,balance,saveingtype,cardid)
values (1000,1000,0,'1010357612345678')*/
------------------------------------------------------------------------
--随机值存储过程
create proc sunjizhi --proc:sql server中创建存储过程使用的关键字，在其他数据库系统中应该使用全称
@out varchar(10) output --output表示输出参数，未指明则是输入参数
as --表示存储过程开始
declare @index numeric(10,8) --声明变量
set @index=rand(datepart(mm,getdate())*100000+datepart(ss,getdate())*1000+datepart(ms,getdate())) --变量赋值有两种办法，一种是set，一种是select
set @out=convert(varchar(10),@index)
set @out=substring(@out,3,4)+' '+substring(@out,7,4)
go
------------------------------------------------------------------------------------------------------------------------
-----开户存储过程
--(返回开户产生卡号,开户名,身份证号,联系电话,
--开户金额,帐户密码,存款类型（定，活，定活）,
--用户地址,货币种类)
--drop proc newuser
go
create proc newuser
@cardid varchar(20) output,--返回开户产生卡号
@name nvarchar(10), --开户名
@pid nvarchar(20), --身份证号
@telephone nvarchar(20), --联系电话
@openmoney int, --开户金额
@pass nvarchar(20), --帐户密码
@saveingtype nvarchar(10), --存款类型（定，活，定活）
-----------------------------userinfo的数据
@address nvarchar(50)='', --用户地址
@curtype nvarchar(10)='rmb' --货币种类
as
begin tran --表示开始事物
declare @error int
set @error=0
insert into userinfo(customername,pid,telephone,address)
values (@name,@pid,@telephone,@address)
set @error=@error+@@error --向userinfo表插入语句
--@@error是当前过程已经产生的错误数量
declare @moth nvarchar(10) --产生卡号
exec sunjizhi @moth output --执行名称为sunjizhi的存储过程
set @cardid='1010 3576 '+@moth
declare @ind int
select @ind=max(customerid) from userinfo --查找在userinfo表中表示列的最大值
insert into cardinfo(cardid,openmoney,pass,saveingtype,balance,customerid)
values (@cardid,@openmoney,@pass,@saveingtype,@openmoney,@ind)
set @error=@error+@@error --向cardinfo表插入语句
if @error>0 --以上语句执行之后所累积的错误次数
begin --此处为if else 条件跳转语句
print '开户失败' --print输出，一般在t-sql开发的工程中使用
rollback tran
end
else begin
print '开户成功，帐号是：'+@cardid
commit tran
end
go
-----------------------------------------------------------------------------
-----开户存储过程
--(返回开户产生卡号,开户名,身份证号,联系电话,
--开户金额,帐户密码,存款类型（定，活，定活）,
--用户地址,货币种类)
--declare @out nvarchar(20)
--exec newuser @out output,'张东会','125345679015357','010-67898978',10,'888888','定期'
--尝试插入
-----------------------------------------------------------------------------------------------------
create proc shiwu --通过穿进的varchar的sql语句进行事物封装（传进的sql语句必须有用变量@error记录错误的sql语句'set @error=@error+@@error'）
@sql nvarchar(500)
as
declare @sqlout nvarchar(500)
declare @shiban nvarchar(20) --方案不可行
set @shiban='存储过程出现异常,事务撤销'
declare @chenggong nvarchar(20)
set @chenggong='事务执行成功'
set @sqlout='begin tran
declare @error int
set @error=0 '+@sql+'
if @error>0
begin
print @error
rollback tran
end
else begin
commit tran
end '
-- exec(@sqlout) --执行产生的sql语句
/*
declare @sql nvarchar(500)
set @sql='select * from userinfo set @error=@error+@@error '
exec shiwu @sql
*/
--------------------------------------------------------------------------------------------------------------------------------
/*
delete from cardinfo
delete from userinfo
insert into userinfo(customername,pid,telephone)
values ('张东会',123456789212345,'101-154545454')
*/
select userinfo.*,cardinfo.* from userinfo
inner join cardinfo on cardinfo.customerid=userinfo.customerid --事物约束开户存储过程验证结束（成功）
-------------------------------------------------------------------------------------------------------------------
--修改账号存储过程
create proc newpass --参数：用户卡号，用户密码，新密码
@cardid nvarchar(20),
@pass nvarchar(20),
@newpass nvarchar(20)
as
update cardinfo set pass=@newpass where cardid=@cardid and pass=@pass
if @@rowcount=1 --收上一行影响的行数（这里表示update语句操作了表中几条数据）
print '密码修改成功'
else print'数据库没有次对应用户'
go
----------------------------------------------------------------------------------------
--exec newpass '1010 3576 6230 5403','111111','11111'
-- 修改密码存储过程检验(过了)
--------------------------------------------------------------------------------------
--卡号挂失存储过程
create proc newstate --(参数：用户卡号，要修改的状态)
@cardid nvarchar(20),
@state bit
as
update cardinfo set isreportloss=@state where cardid=@cardid
if @@rowcount=1 print '状态修改成功'
else print '状态修改失败（可能是无次用户）'
go
--------------------------------------------------------------------------------
--exec newstate '1010 3576 6230 5403',0
--卡号挂失状态存储过程检验（成功）
-------------------------------------------------------------------------------
-- drop proc summoney --资金流通余额和盈利结算
create proc summoney --（参数：out（））
@sumbalance money output, --流通余额
@payoff money output, --盈利
@save_interest money=0.003 , --存款利率(默认.003)
@hold_interest money=0.008, --贷款利率(默认.008)
@state_time datetime='2008-01-01' --结算起始时间(默认建行以来)
as
declare @insum money,@outsum money
select @insum= sum(transmoney) from transinfo where transtype='存入' and transdate>@state_time
select @outsum= sum(transmoney) from transinfo where transtype='支取' and transdate>@state_time
set @sumbalance=@insum/@outsum
set @payoff= @outsum*@hold_interest - @insum*@save_interest
print '余额：'+convert(varchar(20),@sumbalance)
print '盈利：'+convert(varchar(20),@payoff)
go
----------------------------------------------------------------------------------------
declare @sumlixi money,@sumyingli money
exec summoney @sumlixi,@sumyingli
--等待transinfo表中有数据时检测
-------------------------------------------------------------------------------------
-- print datediff(day,dateadd(day,4,getdate()),getdate())--获取两个时间制定部分的区别
--drop proc chaxun
create proc chaxun --查询一星期来的开户信息
as
select userinfo.*,cardinfo.* from cardinfo,userinfo
where datediff(day,opendate,getdate())<=7 --天数差
and datediff(mm,opendate,getdate())=0 --月数相同
and datediff(yy,opendate,getdate())=0 --年份相同
and userinfo.customerid=cardinfo.customerid
go --显示一周的开户账号的信息（测试成功）
--------------------------------------------------------------------------------------
create proc mmmax --查询本月交易金额最高的卡号
@max nvarchar(20) output
as
select cardid from transinfo where datediff(mm,transdate,getdate())=0 --查询时期是当前月份的用户id
select @max=sum(transmoney) where datediff(mm,transdate,getdate())=0
--待完成………………
--------------------------------------------------------------------------------------
create proc guanshi --挂失卡号的信息
as
select userinfo.* ,cardinfo.* from userinfo
inner join cardinfo
on userinfo.customerid=cardinfo.customerid
go --未测试…………………………
------------------------------------------------------------------------------------
create proc cuikuan --催款
as
select cardid from cardinfo where balance<=200
go --测试成功
----------------------------------------------------------------------------------
create nonclustered index cardidindex --为cardinfo表中的cardid创建非聚集索引
on cardinfo(cardid)
with fillfactor=70 --填充因子
go
---------------------------------------------------------------------------------
select userinfo.*,cardinfo.* from cardinfo,userinfo (index=cardidindex) --使用索引
where cardinfo.cardid='1010 3576 0865 8647'
and userinfo.customerid=cardinfo.customerid --问题……索引咋用（什么是多重索引？）？
-------------------------------------------------------------------------------------
create view view_userinfo --创建视图
as
SELECT customerid, customername, pid, telephone, address
FROM userinfo
go
create view view_cardinfo
as
SELECT cardid, curtype, saveingtype,
opendate, balance, pass,
isreportloss, customerid
FROM cardinfo
go
create view view_transinfo
as
SELECT transdate, cardid, transtype, transmoney, remark
FROM transinfo
go
---------------------------------------------------------
--存(取)款
create proc insertmoney --参数：存取款（：是存款），账号，存取金额
@byt bit ,
@cardid nvarchar(20),
@money money
as
if @byt=0
begin
if @@error=0 begin
update cardinfo set balance =balance+@money where cardid=@cardid
print ' 存款成功'
end
end
else begin
if @@error=0
begin
update cardinfo set balance =balance-@money where cardid=@cardid
print '取款成功'
end
end
--存取款（未测试………………）
--------------------------------------------------------------------------------
--转账
create proc movemoney --参数：用户卡号,转至卡号，转账金额
@userid nvarchar(20),
@touserid nvarchar(20),
@money money
as
begin tran
declare @error int
set @error=0
update cardinfo set balance =balance-@money where cardid=@userid
set @error=@error+@@error
update cardinfo set balance =balance+@money where cardid=@touserid
set @error =@error+@@error
if @error=0
begin
print ' 转账成功'
commit tran
end
else begin
print ' 转账失败'
rollback tran
end --转账（未测试………………）
----------------------------------------------------------------------------
drop sp_addlog --创建登陆账号
exec sp_addlogin 'sysadmin','1234' --sql登陆账号
exec sp_grantdbaccess 'sysadmin','sysadmindbuser'--创建数据库登录名
grant insert,update,select,delete
on transinfo to sysadmindbuser --给sysadmindbuser用户副traninfo表的权限
