MySQL数据库基础(必知必会13-18)-分组，子查询，联结表，组合查询，全文搜索

分组数据

涉及到GROUP BY子句和HAVING子句
GROUP BY子句可以包含任意数目的列

分组数据

-- 13.1 数据分组
select count(*) as num_prods from products where vend_id=1003; -- 如何获得多个供应商的商品个数

创建分组

-- 13.2 创建分组
select vend_id,COUNT(*) AS num_prods FROM products Group by vend_id; 
SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id,prod_price; 

• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
• SELECT 语句中的每个列都必须在GROUP BY子句给出 如果分组列中具有NULL值，则NULL将作为一个分组返回 GROUP
• BY子句必须出现在WHERE子句之后，ORDER BY子句之前

过滤分组

-- 13.3 过滤分组
select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;	-- 找出下了超过一次单的客户，显示id和orders
select vend_id,count(*) as num_prods from products where prod_price>=10 group by vend_id having num_prods>=2;	-- 列出含2个以上、价格为10以上的产品的供应商
SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING num_prods>=2;	-- 列出含2个以上、产品的供应商

• WHERE 的过滤指定的是行不是分组，WHERE没有分组的概念
• HAVING可以过滤分组，其支持所有WHERE的操作符
• WHERE和HAVING的差别：WHERE在数据分组前进行过滤，HAVING在数据分组
  后进行过滤

分组和排序

-- 13.4 分组和排序
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num;	-- 检索总计订单价格大于等于50的订单号和总计订单价格
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num ORDER by ordertotal;	-- 对总计订单价格进行排序输出

GROUP BY和ORDER BY的区别

ORDER BY

• 排序产生的输出
• 任意列都可以使用（甚至非选择的列也可以使用）
• 不一定需要

GROUP BY

• 分组行，但输出不是分组的顺序
• 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
• 如果与聚集函数一起使用列（或表达式），则必须使用
• 一般在使用GROUP BY子句时，应该也给出ORDER BY子句

SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表中选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索地行数	否

使用子查询

子查询，嵌套在其他查询中的查询
实际使用中会有性能限制，不能嵌套太多的子查询子查询一般与IN操作符结合使用，也可以用于测试等于、不等于等

利用子查询进行过滤

-- 14.2利用子查询进行过滤
-- 联表查询，检索出订购物品TNT2的所有客户
SELECT order_num FROM orderitems WHERE prod_id='TNT2';	-- 第一步，在orderitems中获得订单号
select cust_id from orders where order_num in(20005,20007);	-- 第二步，在order表中查询订单号对应的用户id
-- 将上述组合实现
SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id='TNT2');
-- 联表查询，检索出订购物品TNT2的所有客户的信息
select * from customers where cust_id  in(10001,10004);	-- 到customers表中查询客户信息
SELECT * FROM customers WHERE cust_id  IN(SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id='TNT2'));

作为计算字段使用子查询

-- 14.3 作为计算字段使用子查询
select cust_id,count(*) from orders group by cust_id;	-- 统计每个客户的订单数
select cust_name,cust_state,(select count(*) from orders where orders.`cust_id`=customers.`cust_id`) as orders from customers order by cust_name;

• 相关子查询，必须使用全限定类名，否则当不同的表列名相同时会引起歧义

联结表

联结

关系表

• 外键，为某一个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
• products表中列vend_id就是外键，对应的是vendors表的主键
• 可伸缩性，能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好

使用联结表的目的

• 从多个表获取数据

创建联结

-- 15.2 创建联结
select vend_name,prod_name,prod_price from vendors,products where vendors.`vend_id`=products.`vend_id` order by vend_name,prod_name;	-- 返回供应商名，商品名和商品价格，按照供应商名称和商品名称排序

• 完全限定名，在引用的列可能出现二义性时，必须使用完全限定列名。

WHERE子句的重要性

• 笛卡尔积，由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数成员以第二表中的行数

-- 15.2.1 WHERE子句的重要性
SELECT vend_name,prod_name,prod_price FROM vendors,products  ORDER BY vend_name,prod_name; -- 不正确的过滤条件将导致MySQL返回不正确的结果

内部联结

-- 15.2.2 内部联结 INNER JOIN
select vend_name,prod_name,prod_price from vendors inner join products on vendors.`vend_id`=products.`vend_id`;

联结多个表

• 联结的表越多，性能下降越厉害

-- 15.2.3 联结多个表
select vend_name,prod_name,prod_price,quantity from vendors,products,orderitems where vendors.`vend_id`=products.`vend_id` and products.`prod_id`=orderitems.`prod_id` and orderitems.`order_num`=20005;	-- 返回order_name=20005的订单，获得商品名，供应商名，商品价格，数目
select cust_name,cust_contact from customers,orders,orderitems where customers.`cust_id`=orders.`cust_id` and orderitems.`order_num`=orders.`order_num` and orderitems.`prod_id`='TNT2';	-- 返回订购产品TNT2的客户列表

高级联结

使用表列名

使用列表名的两个主要理由

• 缩短SQL语句
• 允许在单条SELECT语句中多次使用相同的表

-- 16.1 使用表列名
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 oi.`prod_id`='TNT2';	-- 返回订购产品TNT2的客户列表

使用不同类型的联结

自联结

• 能在单条SELECT语句中不止一次引用相同的表
• 使用自联结而不用子查询 自联结通常作为外部语句用来代替相同表中检索数据时使用的子查询语句。 可以比较两种方法确认性能好坏

-- 16.2 使用不同类型的联结
-- 16.2.1 自联结
select prod_id,prod_name from products where vend_id=(select vend_id from products where prod_id='DTNTR');	-- 通过子查询的方式，发现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';	-- 通过自联结的方式，发现prod_id=DTNTR有问题，想要该产品的供应商提供的所有商品是否有问题

自然联结

• 无论何时对表进行联结，应该至少有一个列出现在不止一个表中。自然联结排除多次出现，使每个列只返回一次

-- 16.2.2 自然联结
select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price 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='FB';

外部联结

• 包含没有关联行的那些行
• OUTER JOIN语法，必须使用LEFT或RIGHT关键字指定包括其所有行的表
• 没有*=操作符 MySQL不支持
• 外部联结的类型，存在两种基本形式：左外部联结和右外部联结，唯一差别是所关联的表的顺序不同。左外联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结

-- 16.2.3 外部联结
select customers.`cust_id`,orders.`order_num` from customers left outer join orders on customers.`cust_id`=orders.`cust_id`;
SELECT customers.`cust_id`,orders.`order_num` FROM customers RIGHT OUTER JOIN orders ON customers.`cust_id`=orders.`cust_id`;

使用带聚集函数的联结

-- 16.3 使用带聚集函数的联结
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`; -- 检索所有客户及每个客户所下的订单数

使用联结和联结条件

• 一般使用内部联结，但是使用外部联结也是有效的
• 保证使用正确的联结条件，否则会返回不正确的数据
• 总是提供联结条件，否则会得到笛卡尔积
• 在一个联结中可以包含多个表，甚至对于每个联结都可以采用不同的联结类型

组合查询

组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询，并将结果作为单个查询结果集返回。
有两种基本情况需要使用组合查询
– 在单个查询中从不同的表返回类似结构的数据；
– 对单个表执行多个查询，按单个查询返回数据

创建组合查询

• 利用UNION可以给出多条SELECT语句，将结果组合成单个结果集

使用UNION

• 给出每条SELECT语句，在各条语句之间放上关键字UNION

-- 17.2.1使用UNION
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);	-- 需要价格小于等于5的所有物品的一个列表，还包括供应商1001和1002生产的所有商品

UNION规则

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION中的每个查询必须包含相同的列、表达式或聚集函数
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型

包含或取消重复的行

• UNION会从查询结果集中自动去除重复行
• 如果想返回所有匹配行，可以使用UNION ALL
• UNION几乎总是完成与多个WHERE条件相同的工作，UNION ALL为UNION的一种形式，完成WHERE子句完成不了的工作

-- 17.2.3 包含或取消重复的行
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);	-- 需要价格小于等于5的所有物品的一个列表，还包括供应商1001和1002生产的所有商品
-- UNION的结果返回8行，UNION ALL 结果返回9行

对组合查询结果排序

• 在UNION组合查询时，只能使用一条ORDER BY 子句，必须出现在最后一条SELECT语句之后

-- 17.2.4对组合查询结果排序
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;	-- 需要价格小于等于5的所有物品的一个列表，还包括供应商1001和1002生产的所有商品,按照供应商和价格排序

全文本搜索

理解全文本搜索

• 并非所有引擎都支持全文本搜索，最常用的两个引擎为MyISAM和InnoDB，前者支持全文搜索，后者不支持。
• 样列表中除productnotes表都用的是InnoDB引擎
• 搜索的方式
  • LIKE关键字，利用通配操作符匹配文本。使用LIKE，能够查找包括特殊值或部分值的行
  • 基于文本的搜索作为正则表达式匹配列值。
• 上述搜索方式的限制
  • 性能：通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行。由于被搜索行数不断增加，这些搜索非常耗时
  • 明确控制：很难明确地控制匹配什么和不匹配什么
  • 智能化的结果：基于通配符和正则表达式的搜索提供了灵活的方式，但不能提供一种智能化的选择结果

使用全文本搜索

• 为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。
• 在索引之后，SELECT与Match()和Against()一起使用以实际执行搜索

启用全文本搜索支持

• 一般在创建表时启用全文本搜索
• 观察productnotes创建表语句，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对note_text创建索引
• 不要在导入数据时使用FULLTEXT，更新索引需要花时间，首先导入所有数据，然后再修改表，定义FULLTEXT，有助于更快导入数据（使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）

进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本索引

• Match()指定被搜索的列
• Against()指定要使用的搜索表达式
• 使用完整的Match()说明，传递给Match()的值必须与FULLTEXT()定义中的相同，如果指定多个列，必须列出它们
• 搜索不区分大小写
• 也可以使用LIKE关键字进行搜索
• 使用LIKE以不特别有用的顺序返回数据
• 使用全文本搜索返回以文本匹配的良好程度排序的数据。具有较高等级行的先返回
• 搜索多个搜索项，如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词的那些行高的等级高

-- 18.2.2 进行全文本搜索
select note_text from productnotes where match(note_text) Against('rabbit');
select note_text from productnotes where note_text like '%rabbit%';
SELECT note_text,MATCH(note_text) AGAINST('rabbit') as rank1 FROM productnotes; -- rank是关键字
SELECT note_text,MATCH(note_text) AGAINST('rabbit') AS rank1 FROM productnotes order by rank1; -- rank是关键字
SELECT note_text,MATCH(note_text) AGAINST('rabbit') AS rank1 FROM productnotes ORDER BY rank1 desc limit 2; -- rank是关键字

使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围

• 在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索
  • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
  • 其次，MySQL检查这些匹配行并选择所有有用的词
  • 再次，MySQL再次进行全文本搜索，不仅使用原来的条件，还使用所有有用的词
• 查询扩展极大地增加了返回的行数，但是这样做也增加了实际上不想要的行的数目

-- 18.2.3使用查询扩展
-- 简单全文本查询，只有一行包含，就只返回一行
select note_text from productnotes where match(note_text) against('anvils');
-- 使用查询扩展,第一行包含词anvils，第二行与anvils无关，但是因为它第一行中的两个词(customer和recommend)，所以也被检索
select note_text from productnotes where match(note_text) against('anvils' with query expansion);

布尔文本搜索

全文搜索的另一种方式，布尔文本搜索，可以提供以下内容的细节

• 要匹配的词
• 要排斥的词
• 排列提示
• 表达式分组
• 另外一些内容
  即使没有定义FULLTEXT索引，也可以使用它，其性能随着数据量增加而降低
• 全文布尔操作符
  • + 包含，词必须存在
  • - 排除，词必须不出现
  • > 包含，而且增加等级值
  • < 包含，且减少等级值
  • () 把词组成子表达式
  • _ 取消一个词的排序值
  • * 词尾的通配符
  • “” 定义一个短句

-- 18.2.4布尔文本搜索
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('heavy' in boolean mode); -- 匹配包含heavy的行
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('heavy -rope*' in boolean mode); 	-- 匹配包含heavy但是不包含rope开始的词的行
select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);	-- 包含rabbit和bait的行
select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode);	-- 包含rabbit和bait中的至少一个词的行
select note_text from productnotes where match(note_text) against('>rabbit <carrot' in boolean mode);	-- 匹配rabbit和carrot，增加前者等级，降低后者等级
select note_text from productnotes where match(note_text) against('+safe +(<combination)' in boolean mode);	-- 匹配搜索safe和combination，降低后者等级

全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为3个或3个以下的字符
• MySQL带有一个内建的非用词列表，这些词在索引全文本数据时总是被忽略
• 许多词出现的频率很高，对其进行搜索无意义
• 若表中行数小于3行，则全文搜索不返回结果
• 忽略词中的单引号
• 不具备词分隔符的语言不能恰当地返回全文本搜索
• 仅在MySIAM数据库引擎中支持全文本搜索

本文相关代码可到我的码云下载