sql:基础

--结束正在运行的事物

use master
go
if(exists(select * from sysdatabases where name = 'student'))
  drop database student
go
create database student
go
use student
go
if(exists(select * from sysobjects where name ='stuInfo' and xtype = 'U' ))
  drop table stuInfo
create table stuInfo
(
   stuID varchar(10) primary key,
   stuName varchar(20) not null,
   stuTelePhoneNum varchar(20) check (isNumeric(stuTelePhoneNum)=1),
   stuEmail varchar(30) check (stuEmail like('%@%.%'))
)
go
--关闭消息
set nocount on
go

declare @a int
set @a = 1
declare @stra varchar(20)
while(@a <= 50)
begin
   set @stra = ltrim(rtrim(cast(@a as varchar(10))))
   if len(@stra) < 5
   begin
   declare @i int
   set @i = len(@stra)
   while @i < 5
    begin
    set @stra = '0'+@stra
    set @i =@i+ 1  
    end 
   end
   insert into stuInfo values ('S'+@stra,'T'+cast(@a as varchar(5)),'875'+@stra,'T'+cast(@a as varchar(5))+'@'+'sina.com')
  set @a=@a+1
end
go
if(exists(select * from sysobjects where name ='Account' and xtype = 'U' ))
  drop table Account
go
create table Account
(
   stuAccountID varchar (20) primary key,
   stuID varchar(10) foreign key references stuInfo(stuID),
   stuAccount int check(stuAccount>10)
)
go
declare @a int
declare @stra varchar(20)
set @a = 1
while @a <= 25
  begin
   set @stra = ltrim(rtrim(cast(@a as varchar(10))))
   if len(@stra) < 5
   begin
   declare @i int
   set @i = len(@stra)
   while @i < 5
    begin
    set @stra = '0'+@stra
    set @i =@i+ 1  
    end 
   end
   insert into Account values (cast(@a as varchar(20)),'S'+@stra,999+@a)
   set @a = @a+1
end
--创建视图  加密
go
if (exists(select * from sysobjects where name = 'view_stu' and xtype = 'V'))
 drop view view_stu
go
create view view_stu
  WITH ENCRYPTION
  as
   select stuID ,'Name' =
   (select stuName from stuInfo where stuID =Account.stuID) ,
   'Telephon' =  (select stuTelePhoneNum from stuInfo where stuID =Account.stuID) ,
   'Email' =(select stuEmail from stuInfo where stuID =Account.stuID)
   ,stuAccountID,stuAccount from Account
--此处最好是不用连接，因为连接是基于笛卡尔集，那样的效率比叫低
go
select * from view_stu

select * from stuInfo

--分页的原理
select top 10 stuid,stuname,stutelephoneNum,stuEmail
from stuInfo
where
stuid  not in (select top 10 stuid from stuinfo order by stuid) order by stuid

go
select * from account
go
--定义事物自动回滚
set SET XACT_ABORT on
go
begin tran
update account set stuAccount = stuAccount - 1000 where stuAccountID = '1'
update account set stuAccount = stuAccount + 1000 where stuAccountID = '2'
COMMIT WORK
go
--or COMMIT WORK都可以结束一个事物
go
--判断事物运行是否正确
if(@@error > 0)
print '事物没有完成，事物回滚，转帐不成功'
else
print '转帐成功'
go
select * from account where stuAccountID = '1' or stuAccountID = '2'
go
use master