给自己看的sql

drop table account;
drop table account_log;
create database lable
create table account (
userid int primary key identity(1,1) not null,
username varchar(30) not null,
password varchar(128) not null,
mymoney  float
);
create table account_log (
account_logid int primary key identity(1,1) not null,
acc_from varchar(30),
acc_to varchar(30),
record_date datetime,
money_num float
);
select * from account
select * from account_log

insert into account values('Tom','123456',30000);
insert into account values('Jim','123456',10000);

declare @num float;
set @num=20000;

--转帐20000元
begin transaction myTran
 update account set mymoney=mymoney-@num
  where username='Tom';
 update account set mymoney=mymoney+@num
  where username='Jim';
 --记录日志信息
 insert into account_log (acc_from,acc_to,record_date,money_num)
  values('Tom','Jim',getdate(),@num);
commit transaction myTran;

select * from account;
select * from account_log;

 

declare @num float,@fromMoney float;
set @num=20000;
--转帐20000元
begin tran myTran
 update account set mymoney=mymoney-@num
  where username='Tom';
 update account set mymoney=mymoney+@num
  where username='Jim';
 select @fromMoney=mymoney from account where username='Tom';
 if @fromMoney <0
  rollback tran myTran;
 else
  begin
   insert into account_log(acc_from,acc_to,record_date,money_num)
   values('Tom', 'Jim',getdate(),@num);
   commit tran myTran;
  end;

exec sp_databases
exec sp_stored_procedures
exec sp_tables
exec sp_help account
exec sp_rename account,newaccount
exec sp_rename newaccount,account
exec sp_renamedb less3,less
exec sp_renamedb less,less3

create procedure proc_query_account
    as
select * from account where mymoney>=10000

exec proc_query_account

drop procedure proc_query_account_log
create procedure proc_query_account_log
  @acc_from varchar(30)='T%' --默认值
 as
select * from account_log
 --where acc_from like @acc_from

exec proc_query_account_log

exec proc_query_account_log 'J%'

create proc borrowMoney
@num float,
@fromName varchar(100),
@toname varchar(100)
 as
begin
 declare @fromMoney float;
 --转帐
 begin tran myTran
  update account set mymoney=mymoney-@num
   where username=@fromName;
  update account set mymoney=mymoney+@num
   where username=@toname;
  select @fromMoney=mymoney from account
   where username=@fromName;
  if @fromMoney <0
   rollback tran myTran;
  else
   begin
    insert into account_log (acc_from, acc_to,
     record_date, money_num)
     values(@fromName, @toname,getdate(),@num);
    commit tran myTran;
   end;
end;

exec borrowMoney 10000,'Jim','Tom'
select * from account;
select * from account_log;

drop proc proc_query_money
create procedure proc_query_money
  @username varchar(30),@@mymoney float out
 as
select @@mymoney=mymoney from account
 where username=@username

declare @fromMoney float ;
exec proc_query_money 'Jim',@fromMoney out
print @fromMoney

create table t_auitlog(
logid int primary key identity(1,1) not null,
title varchar(30),
msg varchar(30),
changetime datetime
);
create trigger trg_account
  on account after update
    as
insert into t_auitlog(title,msg,changetime)
 values ('帐户信息变更','account表变更',getdate())
exec borrowMoney 10000,'Tom','Jim'
select * from t_auitlog

create trigger trg_account1
  on account after update
    as
declare @oldmoney float,@newmoney float,@change float;
select * from deleted;
select * from inserted;
select @oldmoney=mymoney from deleted;
select @newmoney=mymoney from inserted;
set @change=@newmoney-@oldmoney;
insert into t_auitlog(title,msg,changetime)
 values ('帐户信息变更',@change,getdate())

select * from t_auitlog
select * from account;
update account set mymoney=100000 where username='Tom'