数据库学习Day4——全文搜索

向表中添加新的字段(列)
ALTER TABLE Document ADD(title VARCHAR(100) NOT NULL);
将第一行的title改为"home page":
UPDATE Document SET title = 'home page' WHERE url = 'index.html';

全文搜索：
首先创建数据库表单需要将类型设置为MyISAM。
CREATE TABLE fulltext_sample(copy TEXT,FULLTEXT(copy)) TYPE=MyISAM;
插入文本行：
INSERT INTO fulltext_sample VALUES
('It appears good from here'),
('The fureture and the past'),
('I love you'),
('All you need is love'),
('A good teacher');
使用全文搜索时需要使用MATCH和AGIANST关键字，MATCH指的是匹配那个字段，AGAINST则指明匹配的内容：
SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST('love');
结果将所有含有love字符的项给选择出来：
+----------------------+
| copy                 |
+----------------------+
| I love you           |
| All you need is love |
+----------------------+
需要注意的是，全文搜索只匹配整个单词，如果是部分单词是没有结果的，例如：
SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST('lov');
得到的结果是空的。

可以通过计算关联度来看匹配词与搜索内容的匹配度：
SELECT copy, MATCH(copy) AGAINST('love') AS relevance
FROM fulltext_sample WHERE MATCH(copy) AGAINST('love');
结果显示的匹配度为：
+----------------------+------------------+
| copy                 | relevance        |
+----------------------+------------------+
| I love you           | 0.40085528270084 |
| All you need is love | 0.40085528270084 |
+----------------------+------------------+
显示与所有内容的匹配度(关联度)：
SELECT copy, MATCH(copy) AGAINST('love') AS relevance
FROM fulltext_sample;
结果为：
+---------------------------+------------------+
| copy                      | relevance        |
+---------------------------+------------------+
| It appears good from here |                0 |
| The fureture and the past |                0 |
| I love you                | 0.40085528270084 |
| All you need is love      | 0.40085528270084 |
| A good teacher            |                0 |
+---------------------------+------------------+
也可以匹配多个单词搜素：
 SELECT copy, MATCH(copy) AGAINST('love,good') AS relevance
 FROM fulltext_sample WHERE MATCH(copy) AGAINST('love,good');
结果为：
+---------------------------+------------------+
| copy                      | relevance        |
+---------------------------+------------------+
| I love you                | 0.40085528270084 |
| All you need is love      | 0.40085528270084 |
| It appears good from here | 0.39634908797834 |
| A good teacher            | 0.39634908797834 |
+---------------------------+------------------+