select * from book
----------建立视图-----------
create view b_view
as
select * from book
----------调用视图-------------
select * from b_view
--------局部变量声明和赋值-----------
declare @num int
set @num=22
select @num=count(*) from book
print @num
--------全局变量(系统变量)---------
select @@version
select @@identity
select @@error
----------if else使用--------------
select * from T_person4
declare @Fsalary numeric(10,2)
select @Fsalary=avg(Fsalary) from T_person4
if @Fsalary<6000
begin
select 'A'
select top 2 * from T_Person4 order by Fsalary desc
end
else
begin
select 'B'
select top 2 * from T_person4 order by Fsalary ASC
end
---------------while 使用----------------------
--看每位员工的薪水是否大于5000,不大于就加500,再查看是否全部大于,否则继续加500。
declare @Fsalary numeric(10,2)
declare @num int
while(1=1)
select @num=count(*) from T_person4 where Fsalary>5000
begin
if @num>0
update T_person4 set Fsalary=Fsalary-500
else
begin
return
end
end
select * from deposit
select * from payment
---------事务(一个程序的执行单元,多条SQL语句,要不然同时执行,要不然都不执行)----
Begin Transaction -----开始事务
declare @fund money ----不能直接declare @fund money=400,切记
set @fund=400
declare @depositor_id char(4)
set @depositor_id='1234'
declare @errnum int
set @errnum=0
update deposit set fund=fund-@fund where depositor_id=@depositor_id
set @errnum=@errnum+@@error ----全局变量@@error,每次使用后,自动变为0
insert into payment(depositor_id,depositor_name,fund)values(@depositor_id,'李三三',@fund)
set @errnum=@errnum+@@error
if @errnum=0
begin
commit Transaction ----事务提交
select '取款成功'
end
else
begin
rollback Transaction -----事务回滚
select '取款失败'
end
SQL SERVER 视图,事务练习
最新推荐文章于 2024-08-14 11:11:30 发布