SQL 进阶学习之六

-- =========================================================================================
-- 2007/06/06 SQL学习-- 全文索引
-- =========================================================================================
--
-- 全文索引是基于标记的功能性索引，使用全文引擎服务创建和维护的。
-- 根据特定的语言的规则对词和短语的搜索，使针对语言的搜索。

-- 相关术语： 
-- 全文索引：是一种特殊的索引，能在给定的列中存储有关重要的词及为位置的信息，
--             使用这些信息可以快速的进行全文查询，搜索包括特定词或词组的行。
-- 全文目录：存储全文索引
-- 断字符与词干分析器
-- 标记
-- 筛选器
-- 填充（爬网）：创造维护全文索引的过程叫填充，也叫爬网。
-- 干扰词

--  创建全文索引的注意事项：
-- 1）全文索引是指对数据表的，只能对数据表创建全文索引，不能对数据库创建全文索引
-- 2）一个数据库中可以创建多个全文目录，每个全文目录都可以存储一个或多个全文索引，但每一个数据表只能创建
--        一个全文索引，一个全文索引中可以包含多个字段。
-- 3）要创建全文索引的标必须要有主键，或者是具备唯一性的非空索引，并且这个主键或具有唯一性的非空索引只能是
--   一个字段，不能是多个字段的组合。
-- 4）包含在全文索引里的字段只能是字符型的或Image型的字段。 

--  在 where 子句里设置全文搜索的查询条件，可以使用 contains 和 freetext 两个谓词
--  在 from 子句里设置全文搜索的查询条件，可以使用 containstable 和 freetexttable 两个行集值函数

-- 使用contains搜索
-- 1) 简单词 
-- 2）派生词
-- 3）前缀词
-- 4）加权词
-- 5）临近词
--CONTAINS 谓词可以搜索： 
--词或短语。

--词或短语的前缀。
-
--与另一个词相邻的词。

--由另一个词的词形变化而生成的词（例如，drive 一词是 drives、drove、driving 和 driven 词形变化的词干）。

--使用同义词库确定的另一个词的同义词（例如，metal 一词可能有 aluminum 和 steel 等同义词）。


CONTAINS
          ( { column_name | (column_list) | * } 
               , '< contains_search_condition >'                         [ , LANGUAGE language_term ]
        ) 
< contains_search_condition > ::= 
    { < simple_term > 
    | < prefix_term > 
    | < generation_term > 
    | < proximity_term > 
    | < weighted_term > 
    } 
    | { ( < contains_search_condition > ) 
    [ { < AND > | < AND NOT > | < OR > } ] 
    < contains_search_condition > [ ...n ] 
    } 
< simple_term > ::= 
          word | " phrase "
< prefix term > ::= 
     { "word * " | "phrase *" }
< generation_term > ::= 
     FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) 
< proximity_term > ::= 
     { < simple_term > | < prefix_term > } 
     { { NEAR | ~ }
     { < simple_term > | < prefix_term > } 
     } [ ...n ] 
< weighted_term > ::= 
     ISABOUT 
        ( { { 
  < simple_term > 
  | < prefix_term > 
  | < generation_term > 
  | < proximity_term > 
  } 
   [ WEIGHT ( weight_value ) ] 
   } [ ,...n ] 
        ) 
< AND > ::= 
     { AND | & }
< AND NOT > ::= 
     { AND NOT | & !}
< OR > ::= 
     { OR | | }

-- 简单词的搜索方式
-- 
select * from  books 
where contains(title,'us')

--
select * from  books 
where contains(title,'"us" and "C++"')
--
select * from  books 
where contains(title,'"xml" or "LET"') -- 不能写为 contains(title,'xml' or 'LET')

-- 派生词的搜索方式
-- 主要用在英文当中
-- 需要改变断字符的语言
select * from books
where contains(title,'formsof(inflectional,program)')

-- 前缀词的搜索方式
-- 也是用在英文中
select * from books
where contains(title,'"C*"')  -- 只能使用*，并且只能放在英文字母之后，如"*c","*C*"都是错误的

-- 临近词的搜索方式
--    可以搜索记录中位置相近的两个字符
--
insert into books
(book_id,title)
values(77,'中华人民共和国')
--
select * from books
where contains(title,N'"中华" near "共和"')


-- 使用freetext搜索
-- freetext的搜索方式是将一个句子中的每个单字拆分开进行搜索的.
select * from books
where freetext(title,'中华')


-- 使用 containstable 搜索
-- 其返回的记录是作为数据表出现在select语句的from子句之中的
-- key 该字段表示的是全文索引的唯一索引键的内容
-- rank 该字段时排名值字段,其排名值是系统依查询符合的程度自动生成的
select * from 
containstable(books,title,'let') as table1

-- 使用 freetexttable 搜索
select books.title,table1.* from books
join freetexttable(books,title,'let',1) as table1
on books.book_id = table1.[key]




-- SQL Server 2005 新增功能: 与全文索引相关的T-SQL语句
-- 创建全文目录
--  
create fulltext catalog TSQLFull


-- 创建全文索引
--  
create fulltext index on authers
--on authers(lastname,firstname,company type column testname)
key index PK__authers__1B0907CE
on TSQLFull


-- 要删除的全文目录必须为空,即不能包含有全文索引