关于《MySQL 必知必会》的学习4

        之前的学习都是简单查询，基于一张表的单条查询语句。今天的学习稍微有一点难，是关于一些进阶的查询，有子查询、联结查询，高级联结查询、组合查询和全文本搜索（索引）。

第14章：使用子查询

       子查询就是在原本的简单查询语句中嵌套上另一条查询语句来达到过滤的效果。

为了能够更好的体验查询语句，需要再建一个表：

CREATE TABLE books (
	book_id   int         NOT NULL AUTO_INCREMENT,
	user_id   int         NOT NULL,
	book_type varchar(20) NULL,
	PRIMARY KEY (book_id),
	FOREIGN KEY (user_id) REFERENCES test(user_id),
	FULLTEXT(book_type)
)ENGINE=MyISAM;

这张表中除了熟悉的前5行，第6行 FOREIGN KEY子句为表books 引入一个外键，即表books中的user_id一定是要在test表中存在的user_id；这样就规范了表中数据的完整性。

第7行的FULLTEXT子句为表books的book_type索引，这是为了后面的章节进全文本搜索而做的准备。

第8行的ENGINE=MyISAM，是为这个表指定一个引擎，MySQL中常用的引擎有两个，一个是MyISAM，它能够进行全文本搜索，也就是索引，但确不可以创建事务处理（事务处理我现在也不太懂，后面会继续学到）。另一个常用的引擎是InnoDB，它支持事务处理却不支持全文本搜索。这是简单的对两者之间的对比，以后学习加深再详细讨论。为了要学习后面的全文本搜索（反正现在也用不到事务处理），所以books表的引擎使用 MyISAM。

在表中插入几行数据：

INSERT INTO books VALUES
	(NULL,1,'Math'),
	(NULL,1,'Chinese'),
	(NULL,2,'English'),
	(NULL,2,'Chinese English'),
	(NULL,2,'Math'),
	(NULL,3,''),
	(NULL,4,'PE'),
	(NULL,5,'MySQL PHP');

简单的演示关于子查询（查询嵌套）的例子：

SELECT user_id,user_add
FROM test
WHERE user_id IN (  SELECT user_id 
		    FROM books
		    WHERE book_type='Math')
ORDER BY user_id DESC;

就是使用一个另外一个查询语句来进行一次过滤，再将结果返回作为第二次查询的条件。

第15章：联结表

        子查询利用新增查询语句来达到跨列或者是跨表查询，但很明显这样的嵌套对于性能和效率的影响也比较大。怎样使用单条的搜索语句来现实检索呢?这时可以使用联结来处理。

下面的语句检索出每个ID拥有的书的总数

SELECT test.user_id,user_add,COUNT(book_type) AS book_sum
FROM test , books
WHERE test.user_id=books.user_id
GROUP BY test.user_id;

像这种基于两个表之间的相等测试的联结称为等值联结，也称内部联结。上面的查询语句等价于：

SELECT test.user_id,user_add,COUNT(book_type) AS book_sum
FROM test INNER JOIN books
ON test.user_id=books.user_id
GROUP BY test.user_id;

使用 FROM WHERE句式的查询和使用 FROM INNER JOIN ON句式的查询结果是一样的，只是后面这种句式更符合内部联结的书写规范。

第16章：创建高级联结

        在之前的学习中有过创建字段别名，其实除了可以创建字段别名，也可以创建表别名，这样做法不仅可以把很长的表名简化，而且有时可以在对同一张表进行多次查询时提供便利。

自身联结：

SELECT t1.*,t2.user_name
FROM test AS t1,test AS t2;

上面的t1,t2其实都是同一张表，由于没有加上联结查询条件，所以返回的是笛卡尔集（两个表的乘积）。

自然联结：

自然联结就是在等值联结中把重复的属性列去掉。这需要我们自己去实现，即在搜索时指出那些唯一的例。

SELECT test.*,books.*
FROM test,books;

这样子的查询就会出现在重复的user_id项，所以去掉这些重复的列就是自然联结。

外部联结：

SELECT t.user_id,b.book_type
FROM test AS t LEFT OUTER JOIN books AS b
ON t.user_id=b.user_id;

观察上面的联结与内部联结很像，只是将内部联结的 INNER JOIN 换成了 LEFT OUTER JOIN，外部联结是可以检索出你所关联的表中值为NULL的项。像上面的LEFT OUTER JOIN 左外联结，可以检索出在test中的存在，但在books中不存在的结果。相对应的有 RIGHT OUTER JOIN　 右外联结，可以检索出右联结表中为NULL的项。

第17章：组合查询

       MySQL除了可以执行一条查询语句，也支持多个查询语句，并将结果作为单个查询结果集返回。组合查询比较简单，这是将两个查询的结果合并在一起，查询语句之间用关键字UNION连接。

SELECT user_id,user_add
FROM test
WHERE user_id>2
UNION
SELECT user_id,user_add
FROM test
WHERE Length(user_add)>8
ORDER BY user_id;

组合查询的有是限制的，从上面的例子可以看出 每个查询必须包括相同的例（表达式或者是聚集函数），而且ORDER BY子句只能在看最后的查询语句之后。

组合查询的结果就自动去掉重复行的，如果有需要返回全部的行（包括重复的行），那就使用UNION ALL 而不是 UNION。

第18章：全文本搜索

       全文本搜索类似于之前的使用通配符和正则表达式进行搜索，但是全文本搜索性能更强，精准度更高，而且更加智能。

       要开启全文本搜索得在建表时使用FULLTEXT子句，上面建表语句中有说到。

进行全文本搜索要使用Match()指示MySQL要进行搜索的列，然后用Against()指定要进行搜索的词。

SELECT user_id,book_type
FROM books
WHERE Match(book_type) Against('chinese');

返回的结果为book_type 有chinese 的记录。

SELECT user_id,book_type,Match(book_type) Against('chinese') AS rank
FROM books
WHERE Match(book_type) Against('chinese');

上面的语句在SELECT后跟上了

Match(book_type) Against('chinese') AS rank

这样可以在返回值中看到搜索词跟返回文本之间的相关性，其值越大，即相关性越强，则MySQL自动将其排在返回结果前面，可以根据自己的需要进行排列。

除了可以对关键字词直接进行搜索，还可以进行扩展查询

SELECT user_id,book_type,Match(book_type) Against('chinese') AS rank
FROM books
WHERE Match(book_type) Against('chinese' WITH QUERY EXPANSION);

上面的查询语句中除了会返回含有chinese的项，同时包含了一个只含english的项（其rank值为0），这是因为在包含chinese的返回结果中有一项的为“chinese english”，所以加上了WITH QUERY EXPANSION扩展查询，智能的搜索出相关项。

但是有时除了想找出想要的东西，我们还想不返回不想要的东西。这个时候可用布尔文本搜索。譬如我想找出有‘chinese’ 但不包含 'english'的项。

SELECT user_id,book_type
FROM books
WHERE Match(book_type) Against('chinese -eng*' IN BOOLEAN MODE);

其中的 IN BOOLEAN MODE就是布尔文本搜索模式。（布尔文本搜索可以在没有建立FULLTEXT索引的情况下使用，但速度会慢很多）。

搜索条件中'chinese -eng*'带有两个全文本布尔操作符。参考下面的表就可以知道是什么意思了。

全文本布尔操作符

---------------------------------------------------------------------------------

    布尔操作符                   说明

---------------------------------------------------------------------------------

             +                            包含，词必须存在

             -                             排除，词不能存在

             >                            包含，且增加等级值

             <                            包含，且降低等级值

             ()                            把词组成表达式

             ~                            取消一个词的排序值

             *                             词尾通配符

            " "                            定义一个短语

---------------------------------------------------------------------------------