《MySQL必知必会》知识汇总三
本篇主要介绍表的联结、组合以及全文本搜索
十三、联结表
本章将介绍什么是联结，为什么使用联结，如何编写使用联结的select语句
- 联结
简单来说，联结是一种机制，用来在一条select语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出
- 创建联结
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
这就是一个最简单的联结
注意：
where中一定要使用完全限定列名
联结的关键就在于where子句后的关联的两个表的列名
- 内部联结
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
这种inner join...on...的语法就称为内部联结，作用相当于上述的where语句
内部联结就是两个或者多个不同的表进行连接
MySQL官方建议使用INNER JOIN这种语法
- 联结多个表
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
这是前边章节通过子查询来实现的多表联查
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
实际可以通过多个where条件做等值联结
这两种方式实际的性能跟操作类型、表中数据量、是否存在索引等等有关，没有绝对的正确或者错误
十四、创建高级联结
本章将介绍另外一些联结类型，介绍如何对被联结的表使用别名和聚集函数
- 使用表别名
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';
其中where子句后的完全限定列名就可以使用表别名来指定了
使用不同类型的联结
上述我们只使用了内部联结或称为等值联结，现在看3种其他联结方式，分别是自联结、自然联结、外部联结
- 自联结
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR');
这种通过子查询来实现多表查询的
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';
所谓自联结，就一个表自己和自己做连接
这就是通过自联结来实现多表查询的，作用与子查询相同
注意：
一般用自联结而不用子查询，因为一般情况下，性能会提高很多
- 自然联结
所谓自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉
select p.*,v.*
from productinfo as p natural join vendors as v
上述并没有使用where语句就可以查询出结果，会把两个表都存在的vendname去重，只显示一个
其实也可以说是自然联结不需要有where和on去限制筛选，它会自动根据字段的只加以匹配，这就是“自然”两个字的含义，即自发的匹配。
但是，自然结合 这种自发的匹配并不是随意的 它必须有一个严格的限制条件，那就是两个表中必须要具有公共字段，而且这两个公共字段的名称必须一样，值得类型也必须一样，才能让其“ 自发 ”的匹配。即productinfo和vendors都有一个相同的字段vendname，且他们的返回值一样。
- 外部联结
其实就是在连接显示时，包含那些没有关联的行
需求：对每个客户下了多少订单进行技术，包括那些至今未下订单的客户
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
这条sql执行后将显示出 cust_id=10002,order_name=null这样的列
注意：
left outer join表示选择左边的表（customers）中选择所有行，同样，right outer join选择右边的表中的所有行
- 使用带聚集函数的联结
需求：检索所有客户及每个客户所下的订单数
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
分别使用了内部联结和外部联结做了演示
十五、组合查询
本章将介绍如何利用UNION操作符将多条SELECT语句组合成一个结果集
- 组合查询
有两种情况需要使用组合查询：
在单个查询中从不同的表返回类似的结构数据
对单个表执行多个查询，按单个查询返回数据
- 创建组合查询
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
其中两条select返回的列是一致的，用UNION分隔开两条查询
select vend_id,prod_id,prod_price
from products
where prod_price<=5 or vend_id in(1001,1002);
使用where...or...可以达到与union相同的效果
- 包含或者取消重复的行
其实UNION会从查询结果集中自动去除重复的行，这是默认的行为
如果想显示全部，需要使用UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
- 对组合查询结果进行排序
单条select语句使用order by子句进行排序。而使用union后，只能使用一条order by子句，它继续出现在最后一条select语句之后
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
ORDER BY vend_id,prod_price;
其中这条order by 子句将排序所有select语句的返回结果
注意：
本章案例union的组合查询为相同表，当然也可以应用到不同的表
使用union可以极大的简化复杂的where子句
十六、全文本搜索
并非所有的引擎都支持全文本搜索
两个最常用的引擎是MyISAM和InnoDB,其中只有前者支持全文本搜索
第八章介绍了使用LIKE关键字配合通配符进行文本匹配
第九章介绍了通过正则表达式进行文本搜索
但这些搜索机制存在限制：性能、明确控制、智能化的结果，全文本搜索机制会解决上述问题
MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行
- 使用全文本搜索
在对表设计后，MySQL会自动进行所有的索引和重新索引
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。
注意：这里的FULLTEXT索引单个列，如果需要也可以指定多个列
- 进行全文本搜索
使用Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式
select note_text
from productnotes
where Match(note_text) Against('rabbit');
通过此sql指定搜索的列note_text和搜索的文本'rabbit'
注意：
使用完整的Match()说明
传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
搜索不区分大小写
当然上述sql也可以通过LIKE关键字进行文本搜索
select note_text
from productnotes
where note_text LIKE '%rabbit%';
区别：
like查询出的结果是无序的，而Match()查询出的结果是有序的
上述两条SELECT语句都不包含ORDER BY子句。后者（使用LIKE ）以不特别有用的顺序返回数据。前者（使用全文本搜索）返回以文本匹配的良好程度排序的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。
全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）
其实使用全文本搜索过程中会为每行值设置一个等级rank，会根据行中词的数目，词的顺序等等进行计数，然后排除rank为0的值
排序多个搜索项
如果指定多个搜索项，则包含多数匹配词的那些行的行等级更高
- 使用查询扩展
需求：你搜索词anvils，但也想查出与搜索有关的所有其他行
此时就需要使用查询扩展WITH QUERY EXPANSION
搜索过程如下
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）
再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（customer和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行
- 布尔文本搜索
MySQL支持全文本搜索的另一种形式，称为布尔方式
可以提供：要匹配的词、要排斥的词、排列提示、表达式分组、另外一些内容
注意：
需要匹配的列上的词，没有FULLTEXT也可以，但会影响性能
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOLLEAN MODE);
该sql并没有指定布尔操作符，因此跟普通的全文本搜索没有区别
需求：匹配'heavy'但不包含任意以'rope'开始的词
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOLLEAN MODE);
其中-rope*指定排除以rope开始的词
两个全本搜索布尔操作符 - 是排除一个词，* 是截断操作符（可相信为用于词尾的一个通配符）
全文本搜索布尔操作符
+包含，词必须存在
-排除，词必须不出现
>包含，而且增加等级值
<包含，且减少等级值
()把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~取消一个词的排序值
*词尾的通配符
""定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）
