学完数据库后的综合小练习

1开户 insert

a) 余额充足 不加入欠费表

b) 余额不足 加入欠费表

2充值 update

c) 余额足够 从欠费表中删除

d) 余额不足 欠费表保留

3消费 update

e) 余额足够 无操作

f) 余额不足 加入欠费表

4.销户 delete

g) 未欠费 账户表中删除

h) 已欠费 账户表中删除 欠费表中删除

-------2013.7.18星期四 综合题：手机欠费
----建数据库
use master;
--判断要创建的数据库是否已存在
if exists(select * from sysdatabases where name = 'TELDB')
	--如果存在则删除之前的数据库
	drop database TELDB ;
create database TELDB
on primary
(
	name = 'TELDB_data',
	filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TELDB\TELDB_data.mdf' ,
	size = 5,
	filegrowth = 1,
	maxsize = 20
)
log on
(
	name = 'TELDB_log',
	filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TELDB\TELDB_log.ldf' ,
	size = 1,
	filegrowth = 10%,
	maxsize = 5
)
go
use TELDB;
if exists(select * from sysobjects where name = 'USER')
	--如果存在则删除之前的数据库
	drop table [USER] ;
create table [USER]
(
	Account int identity(1000,1),
	TelNo   char(11),
	balance money,
	Name    varchar(20) 
)
go
if exists(select * from sysobjects where name = 'qianfei')
	--如果存在则删除之前的数据库
	drop table qianfei ;
create table qianfei
(
	Account int,
	TelNo   char(11),
	Name    varchar(20),
)
go
Alter table [User]
	Add constraint pk_USER_Account primary key(Account),
		constraint ck_USER_balance check(balance >= 0),
		constraint UQ_USER_TelNo   unique(TelNo)
go		
Alter table qianfei
	Add constraint FK_qianfei_Account_USER_Account foreign key (Account) References [USER](Account)
go

----为开户建立存储过程
----账户自动加 1 ，手机号以132打头后八位
----随机生成，用户只用输入姓名和开户时的金额即可
----调用方式为：Exec proc_open_an_Account '张三' , 100
----产生随机8位数的方式运用Rand()函数
---- 0 < Rand()< 1 
----产生10-15 的方式 Rand()*5 + 10
----产生10-20 的方式 Rand()*11 + 10
----print convert(int,Rand()*11 + 10)
----产生0000 0000- 9999 9999 的方式 Rand()*1 0000 0000 
----后八位号码可以以 0 打头,在前面加 1 
----即 1 0000 0000 - 1 9999 9999
----产生方式Right(共9位,后8位)
----(Rand() * (99999999 + 1)) + 100000000
----print Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
----那么随机手机号就是
--set @phonenum = '132' + Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8) 
--验证一下：
/*
declare @phonenum char(11)
set @phonenum = '132' + Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8) 
print @phonenum
*/
/*
delete   from dbo.[USER]
delete   from qianfei;
select * from dbo.[USER];
select * from qianfei;
*/
if exists(select * from sysobjects where name = 'proc_open_an_Account')
	drop proc proc_open_an_Account;
go
Create procedure proc_open_an_Account
@name     varchar(20),
@balance  money
As
	while( 1 = 1 )
	begin
		--定义手机号码
		declare @phonenum char(11)
		set @phonenum = '132' + 
		Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
		--判断手机号码是否重复,如果重复,下面的不做,重新生成手机号码
		--如果不重复继续执行下列操作
		if  not exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
		begin
			--手机没有号码重复，继续做
			insert into [USER] values(@phonenum ,@balance ,@name )
			--检查余额,如果 balance >= 10 就不加到qianfei表,用户表始终有他
			if (@balance < 10)
			begin
				-- balance < 10 加到qianfei表
				insert into qianfei (Account ,TelNo ,Name )
				select Account , TelNo , Name
				from dbo.[USER] 
				where balance < 10
			end
			return;
		end
		else 
		begin
			--手机号码重复,重新生成手机号码
			continue;
		end
	end
go
/*实验代码
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
delete   from dbo.[USER]
delete   from qianfei;
select * from dbo.[USER];
select * from qianfei;
*/
----为充值建立存储过程
----调用方式为：Exec proc_recharge phonenum , recharge money
----所以有两个输入参数@phonenum @money
--看一下这个电话号码是否在欠费表上
--先得到这个电话号码的余额
/*
declare @balancebefore money
select @balancebefore from dbo.[USER] where TelNo = @phonenum 
if(@balancebefore >= 10)
--这个电话号码的余额大于10，不再欠费表上，不用执行删除操作
begin
	update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
end
else
begin
	--这个电话号码的余额小于10，在欠费表上，看是否执行删除操作
	update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
	declare @balanceafter money
	select @balanceafter = balance from dbo.[USER] where TelNo = @phonenum
	--充值了，看余额是否大于等于10，能不能删除
	if(@balanceafter >= 10)
	begin
		delete from qianfei where TelNo = @phonenum
	end
end
*/
if exists(select * from sysobjects where name = 'proc_recharge')
	drop proc proc_recharge;
go
Create procedure proc_recharge
@phonenum char(11),
@money    money
As
	--在用户表中找到这个电话号码,再充值
	--如果有这个号码,就充值,没这个号码给出提示
	if exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
	begin
		declare @balancebefore money
		select @balancebefore from dbo.[USER] where TelNo = @phonenum 
		if(@balancebefore >= 10)
		--这个电话号码的余额大于10，不再欠费表上，不用执行删除操作
		begin
			update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
		end
		else
		begin
			--这个电话号码的余额小于10，在欠费表上，看是否执行删除操作
			update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
			declare @balanceafter money
			select @balanceafter = balance from dbo.[USER] where TelNo = @phonenum
			--充值了，看余额是否大于等于10，能不能删除
			if(@balanceafter >= 10)
			begin
				delete from qianfei where TelNo = @phonenum
			end
		end
	end
	else
	begin
		Raiserror('用户表里面没有这个电话号码，你可能输入错误了',16,1)
	end
go
/*实验代码
Exec proc_recharge '13262324748' , 2
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete   from dbo.[USER]
delete   from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
*/


----为消费建立存储过程
----调用方式为：Exec proc_consume phonenum ,consume money
----所以有两个输入参数@phonenum @money
--看一下这个电话号码是否在欠费表上,并得到这个电话号码的余额
--判断余额和comsume money 的大小，如果consume money大于余额，
--就不能消费这么多，并给出提示
--如果consume money 小于余额，消费之后得到余额看加不加入qianfei表
if exists(select * from sysobjects where name = 'proc_consume')
	drop proc proc_consume;
go
Create procedure proc_consume
@phonenum char(11),
@money    money
As
	if not exists(select TelNo from qianfei where TelNo = @phonenum )
	begin
		update dbo.[USER] set balance = balance - @money where TelNo = @phonenum
		declare @balanceafter money
		select @balanceafter = balance from dbo.[USER] where TelNo = @phonenum
		if(@balanceafter < 10)
		begin
				insert into qianfei (Account ,TelNo ,Name )
				select Account , TelNo , Name
				from dbo.[USER] 
				where TelNo = @phonenum 
		end
	end
	else
	begin
		declare @balancenow money
		select @balancenow = balance from dbo.[USER] where TelNo = @phonenum
		if(@balancenow >= @money)
		begin
			update dbo.[USER] set balance = balance - @money where TelNo = @phonenum
		end
		else
		begin
			print  '你的钱只有：' + convert(varchar(20),@balancenow) + 
			       '块，你还想消费' + convert(varchar(10),@money) + '块，做梦了你！'
		end
	end
go
/*实验代码
Exec proc_recharge '13262950886' , 2
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete   from dbo.[USER]
delete   from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
---钱不够花的
Exec  proc_consume '13262324748' , 15
Exec  proc_consume '13242687354' , 45
Exec  proc_consume '13286088603' , 15
Exec  proc_consume '13242816704' , 15
*/
----为销户建立存储过程
----调用方式为：Exec proc_consume phonenum 
----首先查看余额，如果余额小于10，那么在欠费表上也有他，需要删除2个
----否则删除一个
if exists(select * from sysobjects where name = 'proc_delete_account')
	drop proc proc_delete_account;
go
Create procedure proc_delete_account
@phonenum char(11)
As
	declare @moneynow money
	select @moneynow = balance from dbo.[USER] where TelNo = @phonenum
	if(@moneynow >= 10)
	begin
	----只删除一个表的
		delete from dbo.[USER] where TelNo = @phonenum
	end
	else 
	begin
		delete from qianfei  where TelNo = @phonenum
		delete from dbo.[USER] where TelNo = @phonenum
	end
go
/*实验代码
Exec proc_recharge '13262950886' , 2
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete   from dbo.[USER]
delete   from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
---钱不够花的
Exec  proc_consume '13217591377' , 15
Exec  proc_consume '13217591377' , 2
Exec  proc_consume '13286088603' , 15
Exec  proc_consume '13242816704' , 15
Exec  proc_delete_account '13262324748' 
Exec  proc_delete_account '13242687354' 
*/

----本题用触发器的做法就是如下：
----建数据库
use master;
--判断要创建的数据库是否已存在
if exists(select * from sysdatabases where name = 'TRIGGER_TELDB')
	drop database TRIGGER_TELDB ;
create database TRIGGER_TELDB
on primary
(
	name = 'TRIGGER_TELDB_data',
	filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TRIGGER_TELDB\TRIGGER_TELDB_data.mdf' ,
	size = 5,
	filegrowth = 1,
	maxsize = 20
)
log on
(
	name = 'TRIGGER_TELDB_log',
	filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TRIGGER_TELDB\TRIGGER_TELDB_log.ldf' ,
	size = 1,
	filegrowth = 10%,
	maxsize = 5
)
go
use TRIGGER_TELDB;
if exists(select * from sysobjects where name = 'USER')
	--如果存在则删除之前的数据库
	drop table [USER] ;
create table [USER]
(
	Account int identity(1000,1),
	TelNo   char(11),
	balance money,
	Name    varchar(20) 
)
go
if exists(select * from sysobjects where name = 'qianfei')
	--如果存在则删除之前的数据库
	drop table qianfei ;
create table qianfei
(
	Account int,
	TelNo   char(11),
	Name    varchar(20),
)
go
Alter table [User]
	Add constraint pk_USER_Account primary key(Account),
		constraint ck_USER_balance check(balance >= 0),
		constraint UQ_USER_TelNo   unique(TelNo)
go		
Alter table qianfei
	Add constraint FK_qianfei_Account_USER_Account foreign key (Account) References [USER](Account)
go

----建立inserted触发器，当向[USER]表中插入数据时，
----自动判断是否向qianfei表,插入数据
if exists(select * from sysobjects where name = 'trigger_open_An_Account')
	drop trigger trigger_open_An_Account;
go
create trigger trigger_open_An_Account
on dbo.[USER]                --在dbo.[USER] 表中创建触发器 
for insert                   --为什么事件而触发 
As                           --事件触发后要做什么事情
	begin transaction
	declare @balance money
	select @balance = balance from inserted
	--如果余额 < 10 ,插入qianfei表中
	if(@balance < 10)
	begin
		insert into qianfei(Account,TelNo,Name)
		select Account,TelNo,Name
		from inserted
	end
	commit tran
go 

--创建开户的存储过程
if exists(select * from sysobjects where name = 'proc_open_an_Account')
	drop proc proc_open_an_Account;
go
Create procedure proc_open_an_Account
@name     varchar(20),
@balance  money
As
	while( 1 = 1 )
	begin
		--定义手机号码
		declare @phonenum char(11)
		set @phonenum = '132' + 
		Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
		--判断手机号码是否重复,如果重复,下面的不做,重新生成手机号码
		--如果不重复继续执行下列操作
		if  not exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
		begin
			--手机没有号码重复，继续做
			insert into dbo.[USER] values(@phonenum ,@balance ,@name )
			return;
		end
		else 
		begin
			--手机号码重复,重新生成手机号码
			continue;
		end
	end
go
/*测试代码
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
select * from dbo.[user];
select*  from qianfei;
*/
---创建充值update触发器
if exists(select * from sysobjects where name = 'trigger_update_recharge_Account')
	drop trigger trigger_update_recharge_Account;
go
create trigger trigger_update_recharge_Account
on dbo.[USER]
for update
As
	--更新是先把旧数据放入deleted表，带插入新数据放入inserted表	
	declare @balancebefore money
	--得到之前的余额看是否在qianfei表上
	select @balancebefore = balance from deleted 
	if(@balancebefore < 10)
	begin
		--这个电话号码的余额小于10，在欠费表上，看是否执行删除操作
		declare @balanceafter money
		select @balanceafter = balance from inserted
		--充值了，看余额是否大于等于10，能不能删除
		if(@balanceafter >= 10)
		begin
			declare @phonenum char(11)
			select @phonenum = TelNo from inserted
			delete from qianfei where TelNo = @phonenum
		end
	end
go
----为充值建立存储过程
if exists(select * from sysobjects where name = 'proc_recharge')
	drop proc proc_recharge;
go
Create procedure proc_recharge
@phonenum char(11),
@money    money
As
	--在用户表中找到这个电话号码,再充值
	--如果有这个号码,就充值,没这个号码给出提示
	if exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
	begin
		update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
	end
	else
	begin
		Raiserror('用户表里面没有这个电话号码，你可能输入错误了',16,1)
	end
go
/*实验代码
Exec proc_recharge '13294163543' , 5
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete   from dbo.[USER]
delete   from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
*/
---创建消费update触发器
if exists(select * from sysobjects where name = 'trigger_update_consume_Account')
	drop trigger trigger_update_consume_Account;
go
create trigger trigger_update_consume_Account
on dbo.[USER]
for update
As
	begin tran
	---得到当前余额，看是否在qianfei表上,在qianfei表上的话，不动
	declare @balancenow money
	select @balancenow = balance from deleted
	if(@balancenow >= 10)
	begin
		--消费之前不在qianfei表上，消费得到之后余额，再比较
		declare @balanceafter money
		select @balanceafter = balance from inserted
		if(@balanceafter < 10)
		begin
			--消费之后余额小于10，要加入qianfei表了
			insert into qianfei
			select Account,TelNo,Name
			from   inserted
		end
	end
	commit tran
go
----为消费建立存储过程
if exists(select * from sysobjects where name = 'proc_consume')
	drop proc proc_consume;
go
Create procedure proc_consume
@phonenum char(11),
@money    money
As
	--查看这个手机号码可用余额
	declare @balancenow money
	select @balancenow = balance from dbo.[USER] where TelNo = @phonenum
	if(@balancenow >= @money)
	begin
		update dbo.[USER] set balance = balance - @money where TelNo = @phonenum
	end
	else
	begin
		print'你的钱只有：' + convert(varchar(20),@balancenow) + 
			 '块，你还想消费' + convert(varchar(10),@money) + '块，做梦了你！'
	end
go
/*实验代码
Exec proc_recharge '13294163543' , 5
--不纯在的号码
Exec proc_recharge '13261701311' , 5
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13262162372' , 20
delete   from dbo.[USER]
delete   from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
---钱不够花的
Exec  proc_consume '13261701311' , 5
Exec  proc_consume '13276372454' , 10
Exec  proc_consume '13240102413' , 15
Exec  proc_consume '13206197933' , 10
Exec  proc_delete_account '13242896467' 
Exec  proc_delete_account '13217591377' 
*/
---创建销户delete触发器
----为[USER]表建立delete触发器 

if exists(select * from sysobjects where name = 'trigger_close_Account')
	drop trigger trigger_close_Account;
go
create trigger trigger_close_Account 
on dbo.[USER] 
instead of delete 
As
	begin tran
	---得到当前余额，看是否在qianfei表上,在qianfei表上的话，不动
	declare @balancenow money
	select @balancenow = balance from deleted
	if(@balancenow < 10)
	begin
		--在销户之前余额小于10，所以在qianfei表上
		declare @phonenum char(11)
		select  @phonenum = TelNo from deleted
		delete  from qianfei where TelNo = @phonenum
	end
	delete from dbo.[USER] where TelNo = @phonenum
	commit tran
go

----为销户建立存储过程
----调用方式为：Exec proc_consume phonenum 
----首先查看余额，如果余额小于10，那么在欠费表上也有他，需要删除2个
----否则删除一个
if exists(select * from sysobjects where name = 'proc_delete_account')
	drop proc proc_delete_account;
go
Create procedure proc_delete_account
@phonenum char(11)
As
	delete from dbo.[USER] where TelNo = @phonenum
go
/*实验代码
select * from dbo.[USER];
select * from qianfei;
Exec  proc_delete_account '13261701311' 
Exec  proc_delete_account '13294163543' 
*/
---非常重要：因为[USER]表与qianfei表建立Account的外键，所以应该先删除子表，再删父表
---但是delete触发器要先完成delete动作在触发，SQL没有before delete 触发器，因而可用
---instead of delete触发器，在父表执行删除时，不会删除，而是触发了此触发器，在触发器里
---可先删除子表再删除父表；