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

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 TELDBon 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)gouse 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) )goif exists(select * from sysobjects where name = 'qianfei')--如果存在则删除之前的数据库drop table qianfei ;create table qianfei(Account int,TelNo   char(11),Name    varchar(20),)goAlter table [User]Add constraint pk_USER_Account primary key(Account),constraint ck_USER_balance check(balance >= 0),constraint UQ_USER_TelNo   unique(TelNo)goAlter table qianfeiAdd 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;goCreate procedure proc_open_an_Account@name     varchar(20),@balance  moneyAswhile( 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 , Namefrom dbo.[USER] where balance < 10endreturn;endelse begin--手机号码重复,重新生成手机号码continue;endendgo/*实验代码Exec proc_open_an_Account '李四' , 5Exec proc_open_an_Account '张三' , 50Exec proc_open_an_Account '易明' , 15delete   from dbo.[USER]delete   from qianfei;select * from dbo.[USER];select * from qianfei;*/----为充值建立存储过程----调用方式为：Exec proc_recharge phonenum , recharge money----所以有两个输入参数@phonenum @money--看一下这个电话号码是否在欠费表上--先得到这个电话号码的余额/*declare @balancebefore moneyselect @balancebefore from dbo.[USER] where TelNo = @phonenum if(@balancebefore >= 10)--这个电话号码的余额大于10，不再欠费表上，不用执行删除操作beginupdate dbo.[USER] set balance = balance + @money where TelNo = @phonenumendelsebegin--这个电话号码的余额小于10，在欠费表上，看是否执行删除操作update dbo.[USER] set balance = balance + @money where TelNo = @phonenumdeclare @balanceafter moneyselect @balanceafter = balance from dbo.[USER] where TelNo = @phonenum--充值了，看余额是否大于等于10，能不能删除if(@balanceafter >= 10)begindelete from qianfei where TelNo = @phonenumendend*/if exists(select * from sysobjects where name = 'proc_recharge')drop proc proc_recharge;goCreate procedure proc_recharge@phonenum char(11),@money    moneyAs--在用户表中找到这个电话号码,再充值--如果有这个号码,就充值,没这个号码给出提示if exists (select TelNo from dbo.[USER] where TelNo = @phonenum)begindeclare @balancebefore moneyselect @balancebefore from dbo.[USER] where TelNo = @phonenum if(@balancebefore >= 10)--这个电话号码的余额大于10，不再欠费表上，不用执行删除操作beginupdate dbo.[USER] set balance = balance + @money where TelNo = @phonenumendelsebegin--这个电话号码的余额小于10，在欠费表上，看是否执行删除操作update dbo.[USER] set balance = balance + @money where TelNo = @phonenumdeclare @balanceafter moneyselect @balanceafter = balance from dbo.[USER] where TelNo = @phonenum--充值了，看余额是否大于等于10，能不能删除if(@balanceafter >= 10)begindelete from qianfei where TelNo = @phonenumendendendelsebeginRaiserror('用户表里面没有这个电话号码，你可能输入错误了',16,1)endgo/*实验代码Exec proc_recharge '13262324748' , 2--不纯在的号码Exec proc_recharge '13237151857' , 50Exec proc_recharge '13262950886' , 15Exec proc_recharge '13275403394' , 20delete   from dbo.[USER]delete   from qianfei;select * from dbo.[USER];select * from qianfei;Exec proc_open_an_Account '李四' , 5Exec proc_open_an_Account '张三' , 50Exec 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;goCreate procedure proc_consume@phonenum char(11),@money    moneyAsif not exists(select TelNo from qianfei where TelNo = @phonenum )beginupdate dbo.[USER] set balance = balance - @money where TelNo = @phonenumdeclare @balanceafter moneyselect @balanceafter = balance from dbo.[USER] where TelNo = @phonenumif(@balanceafter < 10)begininsert into qianfei (Account ,TelNo ,Name )select Account , TelNo , Namefrom dbo.[USER] where TelNo = @phonenum endendelsebegindeclare @balancenow moneyselect @balancenow = balance from dbo.[USER] where TelNo = @phonenumif(@balancenow >= @money)beginupdate dbo.[USER] set balance = balance - @money where TelNo = @phonenumendelsebeginprint  '你的钱只有：' + convert(varchar(20),@balancenow) +        '块，你还想消费' + convert(varchar(10),@money) + '块，做梦了你！'endendgo/*实验代码Exec proc_recharge '13262950886' , 2--不纯在的号码Exec proc_recharge '13237151857' , 50Exec proc_recharge '13262950886' , 15Exec proc_recharge '13275403394' , 20delete   from dbo.[USER]delete   from qianfei;select * from dbo.[USER];select * from qianfei;Exec proc_open_an_Account '李四' , 5Exec proc_open_an_Account '张三' , 50Exec proc_open_an_Account '易明' , 15---钱不够花的Exec  proc_consume '13262324748' , 15Exec  proc_consume '13242687354' , 45Exec  proc_consume '13286088603' , 15Exec  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;goCreate procedure proc_delete_account@phonenum char(11)Asdeclare @moneynow moneyselect @moneynow = balance from dbo.[USER] where TelNo = @phonenumif(@moneynow >= 10)begin----只删除一个表的delete from dbo.[USER] where TelNo = @phonenumendelse begindelete from qianfei  where TelNo = @phonenumdelete from dbo.[USER] where TelNo = @phonenumendgo/*实验代码Exec proc_recharge '13262950886' , 2--不纯在的号码Exec proc_recharge '13237151857' , 50Exec proc_recharge '13262950886' , 15Exec proc_recharge '13275403394' , 20delete   from dbo.[USER]delete   from qianfei;select * from dbo.[USER];select * from qianfei;Exec proc_open_an_Account '李四' , 5Exec proc_open_an_Account '张三' , 50Exec proc_open_an_Account '易明' , 15---钱不够花的Exec  proc_consume '13217591377' , 15Exec  proc_consume '13217591377' , 2Exec  proc_consume '13286088603' , 15Exec  proc_consume '13242816704' , 15Exec  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_TELDBon 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)gouse 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) )goif exists(select * from sysobjects where name = 'qianfei')--如果存在则删除之前的数据库drop table qianfei ;create table qianfei(Account int,TelNo   char(11),Name    varchar(20),)goAlter table [User]Add constraint pk_USER_Account primary key(Account),constraint ck_USER_balance check(balance >= 0),constraint UQ_USER_TelNo   unique(TelNo)goAlter table qianfeiAdd 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;gocreate trigger trigger_open_An_Accounton dbo.[USER]                --在dbo.[USER] 表中创建触发器 for insert                   --为什么事件而触发 As                           --事件触发后要做什么事情begin transactiondeclare @balance moneyselect @balance = balance from inserted--如果余额 < 10 ,插入qianfei表中if(@balance < 10)begininsert into qianfei(Account,TelNo,Name)select Account,TelNo,Namefrom insertedendcommit trango --创建开户的存储过程if exists(select * from sysobjects where name = 'proc_open_an_Account')drop proc proc_open_an_Account;goCreate procedure proc_open_an_Account@name     varchar(20),@balance  moneyAswhile( 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;endelse begin--手机号码重复,重新生成手机号码continue;endendgo/*测试代码Exec proc_open_an_Account '李四' , 5Exec proc_open_an_Account '张三' , 50Exec proc_open_an_Account '易明' , 15select * from dbo.[user];select*  from qianfei;*/---创建充值update触发器if exists(select * from sysobjects where name = 'trigger_update_recharge_Account')drop trigger trigger_update_recharge_Account;gocreate trigger trigger_update_recharge_Accounton dbo.[USER]for updateAs--更新是先把旧数据放入deleted表，带插入新数据放入inserted表declare @balancebefore money--得到之前的余额看是否在qianfei表上select @balancebefore = balance from deleted if(@balancebefore < 10)begin--这个电话号码的余额小于10，在欠费表上，看是否执行删除操作declare @balanceafter moneyselect @balanceafter = balance from inserted--充值了，看余额是否大于等于10，能不能删除if(@balanceafter >= 10)begindeclare @phonenum char(11)select @phonenum = TelNo from inserteddelete from qianfei where TelNo = @phonenumendendgo----为充值建立存储过程if exists(select * from sysobjects where name = 'proc_recharge')drop proc proc_recharge;goCreate procedure proc_recharge@phonenum char(11),@money    moneyAs--在用户表中找到这个电话号码,再充值--如果有这个号码,就充值,没这个号码给出提示if exists (select TelNo from dbo.[USER] where TelNo = @phonenum)beginupdate dbo.[USER] set balance = balance + @money where TelNo = @phonenumendelsebeginRaiserror('用户表里面没有这个电话号码，你可能输入错误了',16,1)endgo/*实验代码Exec proc_recharge '13294163543' , 5--不纯在的号码Exec proc_recharge '13237151857' , 50Exec proc_recharge '13262950886' , 15Exec proc_recharge '13275403394' , 20delete   from dbo.[USER]delete   from qianfei;select * from dbo.[USER];select * from qianfei;Exec proc_open_an_Account '李四' , 5Exec proc_open_an_Account '张三' , 50Exec proc_open_an_Account '易明' , 15*/---创建消费update触发器if exists(select * from sysobjects where name = 'trigger_update_consume_Account')drop trigger trigger_update_consume_Account;gocreate trigger trigger_update_consume_Accounton dbo.[USER]for updateAsbegin tran---得到当前余额，看是否在qianfei表上,在qianfei表上的话，不动declare @balancenow moneyselect @balancenow = balance from deletedif(@balancenow >= 10)begin--消费之前不在qianfei表上，消费得到之后余额，再比较declare @balanceafter moneyselect @balanceafter = balance from insertedif(@balanceafter < 10)begin--消费之后余额小于10，要加入qianfei表了insert into qianfeiselect Account,TelNo,Namefrom   insertedendendcommit trango----为消费建立存储过程if exists(select * from sysobjects where name = 'proc_consume')drop proc proc_consume;goCreate procedure proc_consume@phonenum char(11),@money    moneyAs--查看这个手机号码可用余额declare @balancenow moneyselect @balancenow = balance from dbo.[USER] where TelNo = @phonenumif(@balancenow >= @money)beginupdate dbo.[USER] set balance = balance - @money where TelNo = @phonenumendelsebeginprint'你的钱只有：' + convert(varchar(20),@balancenow) +  '块，你还想消费' + convert(varchar(10),@money) + '块，做梦了你！'endgo/*实验代码Exec proc_recharge '13294163543' , 5--不纯在的号码Exec proc_recharge '13261701311' , 5Exec proc_recharge '13262950886' , 15Exec proc_recharge '13262162372' , 20delete   from dbo.[USER]delete   from qianfei;select * from dbo.[USER];select * from qianfei;Exec proc_open_an_Account '李四' , 5Exec proc_open_an_Account '张三' , 50Exec proc_open_an_Account '易明' , 15---钱不够花的Exec  proc_consume '13261701311' , 5Exec  proc_consume '13276372454' , 10Exec  proc_consume '13240102413' , 15Exec  proc_consume '13206197933' , 10Exec  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;gocreate trigger trigger_close_Account on dbo.[USER] instead of delete Asbegin tran---得到当前余额，看是否在qianfei表上,在qianfei表上的话，不动declare @balancenow moneyselect @balancenow = balance from deletedif(@balancenow < 10)begin--在销户之前余额小于10，所以在qianfei表上declare @phonenum char(11)select  @phonenum = TelNo from deleteddelete  from qianfei where TelNo = @phonenumenddelete from dbo.[USER] where TelNo = @phonenumcommit trango----为销户建立存储过程----调用方式为：Exec proc_consume phonenum ----首先查看余额，如果余额小于10，那么在欠费表上也有他，需要删除2个----否则删除一个if exists(select * from sysobjects where name = 'proc_delete_account')drop proc proc_delete_account;goCreate procedure proc_delete_account@phonenum char(11)Asdelete from dbo.[USER] where TelNo = @phonenumgo/*实验代码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触发器，在父表执行删除时，不会删除，而是触发了此触发器，在触发器里---可先删除子表再删除父表；