SQL基本操作

 事务操作：

if exists(select * from sysobjects where name='bank')
drop table bank
go
create table bank
(
	customerName char(10),
	currentMoney money --check(currentMoney>=0)
)
alter table bank add constraint ch_currentMoney check(currentMoney>=1)
insert into bank values('张三',1000);
insert into bank values('李四',1);
select * from bank
 
 begin transaction
 declare @errorSum int;
 set @errorSum=0;
 update bank set currentMoney=currentMoney-1000 where customerName='张三';
 set @errorSum=@errorSum+@@ERROR;
 update bank set currentMoney=currentMoney+1000 where customerName='李四';
 if(@errorSum<>0)
 begin 
 print ('交易失败')
 rollback transaction
 end
 else
 begin
 print('交易成功')
 commit transaction
 end

索引：

------------------------索引------------------------------
if exists(select * from sysindexes where name='myIndex')
drop index StuScore.myIndex
go
create clustered index myIndex 
on  StuScore(writtenExam) with filefactor=30
----------------创建索引-----------------------
if exists(select * from sysindexes where name='IX_ShowWrittenScore')
drop index StuScore.IX_ShowWrittenScore
go
create nonclustered index IX_ShowWrittenScore on  StuScore(stuNo,writtenExam)
WITH FILLFACTOR=30;
------------------定义变量查看程序运行时间---------------------------
declare @start datetime,@end datetime
set @start=getdate()
select * from StuScore with(index=IX_ShowWrittenScore)  where writtenExam between 60 and 90
set @end=getdate()
select datediff(millisecond,@start,@end)

视图：

if exists(select * from sysobjects where name='view_stu' )
drop view view_stu
go
create view view_stu
as 
select a.stuName,a.stuNo,a.stuSex,a.stuAge,a.stuSeat,b.writtenExam,b.labExam from 
StuInfo a inner join StuScore b on a.stuNo=b.stuNo
go
select * from view_stu	

触发器：

-----------------触发器------------------------------
use StuInfo
go
if exists(select * from sysobjects where name='TG_DelStuInfo' and type='tr')
--if(object_id('TG_DelStuInfo','tr')) is not null--删除存储过程的第二种方法
drop trigger TG_DelStuInfo
go
alter trigger TG_DelStuInfo on StuInfo for delete
as
	declare @sNo varchar(6)
	set @sNo=(select stuNo from deleted)
	--select @sNo=stuNo from deleted;
	delete StuScore where stuNo=@sNo
go
delete StuInfo where stuNo='s25301'
select * from StuInfo
select * from StuScore

if exists(select * from sysobjects where name='TG_AddStu' and type='tr')
drop trigger TG_AddStu
go
alter trigger TG_AddStu on StuInfo for insert
as
insert into StuInfo values('张丽','s25319','男',18,1,'北京海淀');	
go

基础示例：

use StuInfo
go
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
create table StuInfo
(
	
	stuName varchar(10),
    stuNo varchar(6) primary key,
	stuSex varchar(2) check(stuSex='男'or stuSex='女'),
	stuAge int check(stuAge >0 and stuAge<150),
	stuSeat int,
	stuAddress varchar(50)
)
go
insert into StuInfo values('张秋丽','s25301','男',18,1,'北京海淀');
insert into StuInfo values('李文才','s25302','男',28,2,'地址不详');
insert into StuInfo values('李斯文','s25303','女',22,3,'河南洛阳');
insert into StuInfo values('欧阳俊雄','s25304','女',34,4,'地址不详');
insert into StuInfo values('梅超风','s25318','女',23,5,'地址不详');
select * from StuInfo
if exists(select * from sysobjects where name='StuScore')
drop table StuScore
create table StuScore
(
	ExamNo varchar(7),
	stuNo varchar(6),
	writtenExam float check(writtenExam>=0 and writtenExam<=100),
	labExam float check(labExam>=0 and labExam<=100),
	constraint PK_One primary key(ExamNo,stuNo)
)
insert into StuScore values('S271811','s25303',80,58);
insert into StuScore values('S271813','s25302',50,90);
insert into StuScore values('S271816','s25301',77,82);
insert into StuScore values('S271818','s25318',45,65);
select * from StuScore
--cursor temp as select * from StuScore;
declare @n int;
while (1=1)
begin
	select @n=count(*) from StuScore where writtenExam<60;
	if (@n>0)
		update StuScore set writtenExam=writtenExam+2
	else
		break;
end
select * from StuScore;

--waitfor delay '00:00:02'
--print(getdate())
select * from StuScore
select *,(writtenExam+labExam)/2 平均分,平均分等级=case
							when (writtenExam+labExam)/2>=90 then '优秀'
							when (writtenExam+labExam)/2>=80 then '良好'
							when (writtenExam+labExam)/2>=70 then '中等'
							when (writtenExam+labExam)/2>=60 then '及格'
							else '差'
						end from StuScore
select * from StuInfo where stuNo in (select stuNo from StuScore)
select * from StuInfo where stuNo not in(select stuNo  from StuScore)
go
select * from StuInfo a left join StuScore b on a.stuNo=b.stuNo
if exists(select * from StuScore where writtenExam>=80)
begin
	update StuScore set writtenExam=writtenExam+2;
end
else  
begin
	update StuScore set writtenExam=writtenExam+5;
end
---------------------------
select count(a.stuNo) 应到人数,count(b.stuNo) 实到人数,count(a.stuNo)-count(b.stuNo) 缺考 
from StuInfo a left join StuScore b on a.stuNo=b.stuNo;
go
-----------------------------
select a.stuName 姓名,a.stuNo 学号,笔试成绩 =case
										when b.writtenExam is  null then '缺考'
										else  convert(varchar(10),b.writtenExam) end,
								   机试成绩=case
										when b.labExam is null then '缺考'
										else  convert(varchar(10),b.labExam) end ,
									是否通过=case
										when b.writtenExam is not null and b.writtenExam>=60
										and b.labExam is not null and b.labExam>=60 then '是'
										else '否' end	
from StuInfo a left join StuScore b on a.stuNo=b.stuNo;
go
-------------------------------
declare @pass int;
declare @passRate float;
select @pass= count(stuNo) from StuScore where  writtenExam>=60 and  labExam>=60
select @passRate=convert(float,@pass)/count(stuNo) from StuInfo 
select count(stuNo) 总人数,@pass 通过人数,convert(varchar(10),@passRate*100)+'%' 通过率 from StuInfo 
go
--------------------------------
if exists(select * from sysobjects where name='bank')
drop table bank
go
create table bank
(
	customerName char(10),
	currentMoney money --check(currentMoney>=0)
)
go
alter table bank add constraint ch_currentMoney check(currentMoney>=1)
go
insert into bank values('张三',1000);
insert into bank values('李四',1);
go
select * from bank
------------------事务------------------------
begin transaction
declare @errorSum int;
set @errorSum=0;
update bank set currentMoney=currentMoney-1000 where customerName='张三';
set @errorSum=@errorSum+@@error;
update bank set currentMoney=currentMoney+1000 where customerName='李四';
set @errorSum=@errorSum+@@error;
if(@errorSum<>0)
begin	
    print('交易失败')
	rollback transaction
end
else
begin
	print('交易成功')
	commit transaction
end
go
select * from bank
go
-----------视图------------------
if exists(select * from sysobjects where name='view_stu' )
drop view view_stu
go
create view view_stu
as 
select a.stuName,a.stuNo,a.stuSex,a.stuAge,a.stuSeat,b.writtenExam,b.labExam from 
StuInfo a inner join StuScore b on a.stuNo=b.stuNo
go
select * from view_stu	
------------存储过程------------------
exec sp_databases
exec sp_renamedb 'Stu', 'StuInfo'
exec sp_tables
exec sp_password '123' ,'1234'/*修改当前登录用户的密码*/
-------------自定义存储过程-------------
if exists(select * from sysobjects where name='proc_GetAllStu' and type='p')
drop proc proc_GetAllStu
create proc proc_GetAllStu
as
select * from StuInfo
go
exec proc_GetAllStu
-----------------------------
--drop proc proc_UpdateById 
create proc proc_UpdateById 
@id varchar(6),
@sex varchar(2)
as
	update StuInfo set stuSex=@sex where stuNo=@id
go
exec proc_UpdateById 's25301','女'
select * from StuInfo where stuNo='s25301'
--------------修改存储过程-------------------
--alter proc proc_UpdateById 
--@id varchar(6),
--@sex varchar(2)
--as
--	update StuInfo set stuSex=@sex where stuNo=@id
--go
----------------带参数的存储过程--------------------------------
create proc proc_GetStuByPassScore
@wrt_passScore int=60,
@lab_passScore int=60
as
	if(not @wrt_passScore between 0 and 100 or(not @lab_passScore between 0 and 100))
	begin
		raiserror('及格线错误，请指定0-100以内的及格线',15,1)
		return
	end
	select a.stuNo,a.stuName,a.stuSex,b.writtenExam,b.labExam 
	from StuInfo a inner join StuScore b on a.stuNo=b.stuNo 
	where b.writtenExam>@wrt_passScore and b.labExam>@lab_passScore
go
exec proc_GetStuByPassScore /*两个参数均采用默认值*/
exec proc_GetStuByPassScore 64/*第二个参数采用默认值*/
exec proc_GetStuByPassScore 66,40/*两个参数均不采用默认值*/
exec proc_GetStuByPassScore @lab_passScore=40/*第一个参数采用默认值*/
--exec proc_GetStuByPassScore 120
exec xp_logininfo
----------------------------------
use StuInfo
create proc proc_DelStuById
@id varchar(6),
@flag int output
as
	delete StuInfo where stuNo=@id
	if(@@error<>0)
		set @flag=0--如果失败，则返回0
	else
		set @flag=1--如果成功，则返回1
go
declare @error int
exec proc_DelStuById 's25301',@error output
print @error
--select * from StuInfo
-----------------------------
create proc proc_UpdateStu
@id varchar(6),
@age int,
@flag int output
as
begin
	update StuInfo set stuAge=@age  where stuNo=@id
	if(@@error<>0)
		set @flag=0
	else
		set @flag=1
end
go
select * from StuInfo where stuNo='s25301'
declare @err int
exec proc_UpdateStu 's25301',22,@err output
-------------------------------
go
alter proc proc_AddStu
@id varchar(6),
@name varchar(10),
@age int,
@sex varchar(2),
@seat int,
@address varchar(50),
@flag int output
as
begin
	insert into StuInfo values(@name,@id,@sex,@age,@seat,@address)
	if(@@error<>0)
		set @flag=0
	else
		set @flag=1
end
go
declare @err int
exec proc_AddStu 's25319','王建',25,'男',7,'湖北武汉',@err output
select * from StuInfo
-----------作业-----------------
--------1.统计并显示出本班平均分，如果该学员的的机试+笔试成绩大于平均分则该列显示通过，否则显示未通过----------
declare @avgScore float;
select @avgScore=(sum(writtenExam)+sum(labExam))/(2*count(stuNo)) from StuScore
print @avgScore
select *,(writtenExam+labExam)/2 平均分,是否通过=case
				  when (writtenExam+labExam)/2>@avgScore then '是'
				  else '否' end from StuScore
--------2、如果该班平均分小于70分则对该班所有学生的机试，笔试成绩进行循环提分，每次提2分--------------------
declare @avgScore float;
print @avgScore
while(1=1)
begin
	select @avgScore=(sum(writtenExam)+sum(labExam))/(2*count(stuNo)) from StuScore
	if(@avgScore<70)
	begin
		update StuScore set writtenExam=writtenExam+2,labExam=labExam+2
	end
	else
		break
end
--------3、请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成 绩---------
-----------优 ：90分以上-------------
-----------良 ：80－89分-------------
-----------中 ：70－79分-------------
-----------差 ：60－69分-------------
-----------不及格 ：60分以下---------
select *,(writtenExam+labExam)/2 平均分,平均分等级=case
							when (writtenExam+labExam)/2>=90 then '优秀'
							when (writtenExam+labExam)/2 between 80 and 89 then '良好'
							when (writtenExam+labExam)/2 between 70 and 79 then '中等'
							when (writtenExam+labExam)/2 between 60 and 69 then '差'
							else '不及格'
						end from StuScore
---------4. 根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止-------
---------------------90分以上：   不加分-----------------------
---------------------80－89分：   加1分------------------------
---------------------70－79分：   加2分------------------------
---------------------60－69分：   加3分------------------------
---------------------60分以下：   加5分------------------------
declare @avgScore float
while(1=1)
begin
	select @avgScore=sum(labExam)/count(stuNo) from StuScore;
--	print @avgScore
	if(@avgScore<85)
	begin
		update StuScore set labExam=case
									when  labExam between 80 and 89 then labExam+1
									when  labExam between 70 and 79 then labExam+2
									when  labExam between 60 and 69 then labExam+3
									when  labExam <60 then labExam+5 
									else  labExam end 
	end
	else
		break
end
select * from StuScore
select sum(labExam)/count(stuNo) from StuScore
------------------------索引------------------------------
use StuInfo
go
if exists(select * from sysindexes where name='myIndex')
drop index StuScore.myIndex
go
create clustered index myIndex 
on  StuScore(writtenExam) with filefactor=30
----------------创建索引-----------------------
use StuInfo
go
if exists(select * from sysindexes where name='IX_ShowWrittenScore')
drop index StuScore.IX_ShowWrittenScore
go
create nonclustered index IX_ShowWrittenScore on  StuScore(stuNo,writtenExam)
WITH FILLFACTOR=30;
------------------定义变量查看程序运行时间---------------------------
declare @start datetime,@end datetime
set @start=getdate()
select * from StuScore with(index=IX_ShowWrittenScore)  where writtenExam between 60 and 90
set @end=getdate()
select datediff(millisecond,@start,@end)
-----------------触发器------------------------------
use StuInfo
go
if exists(select * from sysobjects where name='TG_DelStuInfo' and type='tr')
--if(object_id('TG_DelStuInfo','tr')) is not null--删除存储过程的第二种方法
drop trigger TG_DelStuInfo
go
alter trigger TG_DelStuInfo on StuInfo for delete
as
	declare @sNo varchar(6)
	set @sNo=(select stuNo from deleted)
	--select @sNo=stuNo from deleted;
	delete StuScore where stuNo=@sNo
go
delete StuInfo where stuNo='s25301'
select * from StuInfo
select * from StuScore

--if exists(select * from sysobjects where name='TG_AddStu' and type='tr')
--drop trigger TG_AddStu
--go
--alter trigger TG_AddStu on StuInfo for insert
--as
--insert into StuInfo values('张丽','s25319','男',18,1,'北京海淀');	
--go
-------------------游标(Cursor)------------------------------------
/****************1、声明游标：declare******************************
*****************2、打开游标：open**********************************
*****************3、提取数据：fetch**********************************
*****************4、关闭游标：close**********************************
*****************5、释放游标：deallocate*****************************/
if cursor_status('global','myCursor')<>-3/*游标存在，则删除，-3表示不存在*/
deallocate myCursor
declare myCursor cursor scroll for select * from StuInfo
open myCursor
--fetch absolute -1 from myCursor 
fetch next from myCursor 
update StuInfo set stuAge=27 where current of myCursor
--delete from StuInfo where current of myCursor
close myCursor
deallocate myCursor
select * from StuInfo

use StuInfo
select top 4*  from StuInfo 

游标和全文索引

游标提供了从包含多条记录的结果集中每次提取一条记录的机制，使得用户能够灵活处理SQL操作经常返回的结果集
创建游标
declare cursor_name[insensitive][scroll] cursor
for select_statement
[for{read only|update [of column_name]}]

insensitive:告诉DBMS制作查询结果集数据的临时副本，而不是使用指针数据库中真实数据行的列
select_statement不允许使用compute,compute by,for browse和into
read only 禁止通过该游标进行更新
scroll指定所有提取选项（first，last..）均可用

检索游标数据----fetch
fetch next|prior|first|last|absolute {relative {n|@nvar}} from 
cursor_name into @variable_name
absolute从游标头开始
relative从当前行开始

关闭/删除游标 close/dealllocate
close 语句用来关闭游标，但不释放游标所占用的数据结构，可以在随后的使用中随时通过open再次打开游标
deallocate语句不仅删除游标中的数据，而且将游标作为对象从数据库中删除，删除后不能使用open打开，使用deallocate包括关闭游标和释放游标两个动作

select stuName,stuNo,stuSex,stuAge from Stuinfo
where stuSex in('男')
order by stuName 
只有在查询的select子句中列出的列才能用order by子句
deallocate stu_Cursor

open stu_Cursor
declare @stuName varchar(20),@stuNo varchar(20),@stuSex varchar(2),@stuAge int
print '游标结果集中记录总数为:'+cast(@@cursor_rows as varchar(2))
fetch last from stu_Cursor into @stuName,@stuNo,@stuSex,@stuAge
print 'stuName'+@stuName+'stuNo'+@stuNo+'stuSex'+@stuSex+'stuAge'
+cast(@stuAge as char(4))
fetch absolute 1 from stu_Cursor into @stuName,@stuNo,@stuSex,@stuAge
print 'stuName'+@stuName+'stuNo'+@stuNo+'stuSex'+@stuSex+'stuAge'+ cast(@stuAge as char(4))
close stu_Cursor 


遍历游标结果集----@@fetch_status
@@fetch_status全局变量有三个不同的返回值
0  fetch命令被成功执行
-1  fetch命令失败，或者行数据超过游标数据结果集的范围
2 所读取的数据已经不存在
open stu_Cursor
declare @stuName varchar(20),@stuNo varchar(20),@stuSex varchar(2),@stuAge int
print '游标结果集中记录总数为:'+cast(@@cursor_rows as varchar(2))
--执行一次取数操作，将游标定位到第一行记录
fetch next from stu_Cursor into @stuName,@stuNo,@stuSex,@stuAge
while @@FETCH_STATUS=0  --检查已确定是否可以继续取数
begin
print 'stuName'+@stuName+'stuNo'+@stuNo+'stuSex'+@stuSex+'stuAge'+cast(@stuAge as char(4))
--定位到下一行记录
fetch next from stu_Cursor into @stuName,@stuNo,@stuSex,@stuAge
end
close stu_Cursor

使用游标修改，删除数据
使用游标修改表中的数据 ，其前提条件是游标必须被声明为可更新的游标。即在指定declare
语句进行游标声明时。没有指定for read only关键字的游标都是可更新的游标

如果游标的select子句中包含有order by子句，DBMS 将把游标限制为READ ONLY，因此不能在相同的游标SELECT语句中同时有ORDER BY子句和FOR UPDATE子句
update table_name
{set column_name=expression}  where current of cursor_name
delete from table_name where current of cursor_name
current of子句只能使用在进行UPDATE和DELETE操作的语句中

declare stuCursor scroll cursor
for
select *from Stuinfo where stuSex='女'
for update of stuAge
print '更新前的记录为'
select *from StuInfo where stuSex='女'

open stuCursor
fetch stuCursor  ---取第一行数据
while @@FETCH_STATUS=0
begin
update StuInfo
set stuAge=stuAge+1
where current of stuCursor
fetch next from stuCursor  继续取下一条记录
end 
close stuCursor
deallocate stuCursor
print '更新后的记录为'
select *from StuInfo where stuSex='女'

Transact-sql扩展游标与游标的管理

Transact-sql扩展游标语法
declare cursor_name cursor
[local|global]
[forward_only|scroll]
[static|keyset|dynamic|fast_forward]
[read_only|scroll_locks|optimistic]
[type_warning]
for select_statement
[for update[of column_name]]
local指定游标的作用域是局部的，仅限在其所在的存储过程，触发器或批处理中。当建立游标的存储过程执行结束后，local游标会被自动释放。如果在存储过程中，使用output保留字将游标传递给该存储过程的调用者，那么在存储过程执行结束后，还可以引用该游标变量。在这种情况下，直到引用该游标的最后一个变量释放时，游标才会自动释放。
global：指定该游标的作用域对连接是全局的，其作用域是整个会话层
forward_only：指定游标只能从第一行滚动到最后一行，此时，fetch next是唯一可用的提取选项
static:将提取数据放在一个临时表内
keyset：当游标被打开时，游标中列的顺序是固定的，该表的键值可唯一识别游标中某行数据
dynamic：定义一个动态游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改
fast_forward 启用性能优化后的forward_only,read_only游标。如果指定了scroll或for_update，则不能指定fast_forward
read_only:禁止通过该游标进行更新
scroll_locks：当服务器向游标添加行时，在源表的每行上放置一个锁定，这个选项确保对一个游标进行的更新和删除操作总能被成功执行
optimistic:当数据被读入游标后，如果游标中的某行数据发生了变化，那么基于游标的数据的更新或删除操作都将失败
type_warning：若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端

使用游标变量
declare @myvarible cursor --声明游标变量
declare mycursor cursor  ---创建游标
for
select stuName from stuinfo
set @myvarible=mycursor   --为游标变量赋值

只能将cursor数据类型用于output参数，如果为某个参数指定了cursor数据类型，则还需要varying和output参数



create procedure proStu  --创建存储过程
@readonly int=0,--输入参数
@rownumber int output,@table_cursor cursor varying output   --输出参数
as
if @readonly=1    --只读
set @table_cursor=cursor scroll for
select *from stuinfo for read only
else set @table_cursor=cursor scroll for 
select *from stuinfo for update
open @table_cursor
set @rownumber=@@CURSOR_ROWS  --获取游标记录数

set nocount on   --不显示操作影响的行数
declare @rowcount int
declare @mycursor cursor   --定义游标变量接受存储过程的输出参数
exec proStu 1,@rownumber=@rowcount output,  --执行存储过程
@table_cursor=@mycursor output
print '游标中结果集总数为：'+cast(@rowcount as varchar(2))
fetch next from @mycursor
while (@@FETCH_STATUS=0)    --定位到游标额第一行
begin
fetch next from @mycursor   --遍历游标结果集
end
close @mycursor   关闭游标
deallocate @mycursor   --释放游标变量


事务中游标的使用
当在事务中使用游标时，在提交或回滚事务时它将关闭所有打开的游标，此后，当继续从游标中读取数据时，就会造成错误
set curssor_close_on_commit 用于控制事务结束后是否关闭其打开的游标

以下代码会报错
set cursor_close_on_commit on ---设置为on,下面的代码报错
set nocount on ---不显示操作影响的行数
declare stuCursor1 scroll cursor  --创建游标
for
select *from stuinfo
begin transaction Tran1    ---开始事务
open stuCursor1
fetch first from stuCursor1
commit transaction Tran1
fetch last from stuCursor1
close stuCursor1
deallocate stuCursor1


游标管理
sp_cursor_list	返回当前在连接上可视的游标列表及其特性
sp_describe_cursor  说明游标属性，例如只前推额游标还是滚动游标
sp_describe_cursor_tables 说明游标所访问的基表
sP_describe_cursor_columns 说明游标结果集列的属性
sp_cursor_list系统存储过程生成连接打开的当前服务器游标列表，并对每个游标的全局属性进行说明，例如游标的可滚动性和可更新性
sp_cursor_list[@cursor_return=] cursor_variable_name output,[@cursor_scope] cursor_scope
cursor_scope用于指定要报告的游标级别
1 local游标 2 返回所有的global游标 3表示local global游标都返回
declare testcursor cursor keyset for  --创建并打开一个keyset游标
 select stuname
 from stuinfo
 open testcursor
 declare @report cursor	--定义游标变量
 exec sp_cursor_list @cursor_return=@report output,
 @cursor_scope=3	--表示将local global游标都返回
 fetch next from @report	--遍历游标变量中所有数据
 while(@@FETCH_STATUS<>-1)
 begin
 fetch next from @report
 end
 close @report	--关闭游标变量
 deallocate @report --释放游标变量
 close testcursor --关闭游标
 deallocate testcursor	--释放游标

全文检索基础
使用全文索引可以快速灵活为存储在数据库中的文本数据基于关键字的查询创建索引
全文索引在给定的列中存储有关重要的词及其位置信息
一个全文目录可以包含多个全文索引，但一个全文索引只能用于构成一个全文目录

创建全文目录
create fulltext catalog
create fulltext catalog catalog_name
[on filegroup 'filegroup']
[in path 'rootpath']
[with accent_sensitivity={on|off}]
[as default]
[authorization owner_name]
catalog_name 全文目录名称，在数据库中必须唯一
'filegroup' 包含全文目录的sql server文件组的名称，如果未指定文件组，则采用默认文件组，即数据库的主文件组
'rootpath' 该全文目录的根目录
accent_sensitivity={on|off}指定该目录的全文索引是否区分重音。在更改此属性后，必须重新生成索引
as default 指定该目录为默认目录
authorization owner_name 将全文目录的所有者设置为数据库用户名或角色的名称

create fulltext catalog test_full_search
alter fulltext catalog catalog_name
{
rebuild [with accent_sensitivity={on|off}]|reorganize|as default
}
参数reorganize表示通知SQL Server执行主合并。以将在索引进程中创建的各个较小的索引合并为一个大型索引。合并索引可以提高性能，并释放磁盘和内存资源
drop fulltext catalog语句则可以从数据库中删除全文目录

create fulltext index on table_name
 [(column_name)[type column type_column_name]]
 [language language_term]
 key index index_name
 [on fulltext_catalog_name]
 [with {change_tracking {manual|auto|off[, no population]}}]
 
 create unique index uk_name on stuinfo(stuNo)  --创建唯一索引
 create fulltext catalog test_full_search as default  --创建默认的全文目录
 create fulltext index on stuinfo(stuName) key index uk_stu   --为stuinfo 表的stuName列创建全文索引
alter fulltext index on table_name
 {enable|disable|set change_tracking {manual|auto|off}
 |add (column_name[type column type_column_name][language language_term])}
 [with no population]
|drop(column_name)[with no population]|start {full|incremental|update} population|stop population
}
enable激活全文索引 disable 关闭全文索引
stop population 停止任何正在进行的填充


使用全文谓词和全文函数进行全文检索

使用contains谓词进行全文检索
谓词用于在包含字符数据类型的列中，搜索单个词或短语的精确或模糊匹配项，一定差异范围内的相邻词或加权匹配项

-------------------游标(Cursor)------------------------------------
/****************1、声明游标：declare******************************
*****************2、打开游标：open**********************************
*****************3、提取数据：fetch**********************************
*****************4、关闭游标：close**********************************
*****************5、释放游标：deallocate*****************************/
if cursor_status('global','myCursor')<>-3/*游标存在，则删除，-3表示不存在*/
deallocate myCursor
declare myCursor cursor scroll for select * from StuInfo
open myCursor
--fetch absolute -1 from myCursor 
fetch next from myCursor 
update StuInfo set stuAge=27 where current of myCursor
--delete from StuInfo where current of myCursor
close myCursor
deallocate myCursor
select * from StuInfo

use StuInfo
select top 4*  from StuInfo