4.《MySQL必知必会》分组与联合

注：博客中总结《MySQL必知必会》中的知识点,第13,14,15,16,17,18章的核心内容；

涉及到的操作符：GROUP BY，HAVING，ORDER BY，INNER JOIN，OUT JOIN，AS，UNION；

书中用到的表的介绍及其脚本文件：《Mysql必知必会》中表的介绍

分组数据

1.数据分组

SELECT vend_id, COUNT(*) AS num_prodsFROM productsGROUP BY vend_id;

2.过滤分组

SELECT cust_id,COUNT(*) AS prdersFROM ordersWHERE prod_price >=10GROUP BY cust_idHAVING COUNT(*) >= 2;

3.分组和排序

SELECT order_num,SUM(quantity*item_price) AS ordertotalFROM orderitermsGROUP BY order_numHAVING COUNT(quantity*item_price) >= 50ORDER BY ordertotal;

4.SELECT子句顺序

顺序依次是：SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT

使用子查询

在WHERE子句的IN中多用子查询

1.利用子查询进行过滤

SELECT cust_idFROM ordersWHERE order_num IN(SELECT order_num                   FROM orderitems                   WHERE prod_id='TNT2');-- 也可以多层嵌套，但是速度慢

2.利用计算字段使用子查询

SELECT cust_name,cust_state,      (SELECT COUNT(*)      FROM orders      WHERE orders.cust_id=customers.cust_id) AS ordersFROM customersORDER BY cust_name;-- orders为计算字段

联合表

1.创建联结

-- 等值联结SELECT vend_name, prod_name, prod_priceFROM vendors, productsWHERE vendors.vend_id = products.vend_idORDER BY vend_name,prod_name;-- 下面是笛卡尔积的结果，行数为两个表格行数的乘积；SELECT vend_name, prod_name, prod_priceFROM vendors, productsORDER BY vend_name,prod_name;

2.内部联结

SELECT vend_name, prod_name, prod_priceFROM vendors INNER JOIN productsON vendors.vend_id = products.vend_id;

3.联结多个表

SELECT vend_name, prod_name, prod_price，quantityFROM orderitems, products, vendorsWHERE products.vend_id = vendors.vend_id  AND oderiterms.prod_id = products.prod_id  AND order_num = 20005;

创建高级联结

1.使用表别名

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_county),')') AS vend_titleFROM vendorsORDER BY vend_name;

2.自联结

-- 检索该供应商生产的其他物品SELECT prod_id,prod_nameFROM productsWHERE vend_id = (SELECT vend_id                 FROM products                 WHERE prod_id='DTNTR')-- 也可以使用别名进行自联结SELECT p1.prod_id, p1.prod_nameFROM products AS p1, products AS p2WHERE p1.vend_id = p2.vend_id  AND p2.prod_id = 'DTNTR';

3.自然联结

SELECT c.*, o.order_num, o.order_date,       oi.prod_id, oi.quantity, oi.item_priceFROM customers AS c, orders AS o, orderitems AS oiWHERE c.cust_id = o.cust_id  AND oi.order_num = o.order_num  AND prod_id = 'FB';

4.外部联结

SELECT customers.cust_id, orders.order_numFROM customers LEFT OUTER JOIN orders  ON customers.cust_id = orders.cust_id;

5.使用带聚集函数的联结

SELECT customers.name,       customers.cust_id,       COUNT(orders.order_num) AS num_ordFROM customers INNER JOIN orders  ON customers.cust_id = orders.cust_idGROUP BY customers.cust_id;

组合查询

1.使用UNION

SELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5;UNIONSELECT vend_id, prod_id, prod_priceFROM productsWHERE vend_id IN (1001,1002);

2.包含或取消重复的行

-- UNION自动去除了重复的行；如果想返回所有的行，可使用UNION ALL；SELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5;UNION ALLSELECT vend_id, prod_id, prod_priceFROM productsWHERE vend_id IN (1001,1002);

3.对组合查询结果排序

-- ORDER BY 是对组合之后的结果结果排序；SELECT vend_id, prod_id, prod_priceFROM productsWHERE prod_price <= 5;UNIONSELECT vend_id, prod_id, prod_priceFROM productsWHERE vend_id IN (1001,1002);ORDER BY vend_id,prod_price;

全文本搜索

两个常用的引擎为MyISAM和InnoDB，前者支持全本文搜索，而后者不支持。

1.启用全文本搜索支持

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) --这里是启用全文本；)ENGING=MyISAM;

2.进行全文本搜索

-- 主要用到了两个函数Match()和Against()，Match()指定被搜索的列，Against()指定要使用的搜索表达式。SELECT note_textFROM productnotesWHERE Match(note_text) Against('rabbit');-- 上述搜索也可以简单用LIKE完成；SELECT note_textFROM productnotesWHERE note_text LIKE '%rabbit%';-- 区别：全文本搜索对结果排序；

3.使用查询扩展

-- 找到可能与搜索相关的其他所有行；SELECT note_textFROM productnotesWHERE Match(note_text) Against('rabbit' WITH QUERY EXANSION);

4.布尔文本搜索

-- 不采用50%规则（当返回值太多是，只返回出现50%以上的词）；速度很慢；SELECT note_textFROM productnotesWHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);-- 全文本布尔操作，还可以配合很多布尔操作符使用；-- 例1，匹配包含词rabbit和bait的行；SELECT note_textFROM productnotesWHERE Match(note_text) Against('+heavy +bait' IN BOOLEAN MODE);-- 例2，匹配包含词rabbit和bait中至少一个的行；SELECT note_textFROM productnotesWHERE Match(note_text) Against('heavy bait' IN BOOLEAN MODE);

全文本布尔操作符

+  包含，词必须存在；-  排除，词必须不存在；>  包含，而且增加等级值；<  包含，且减少等级值；() 把词组成子表达式；~  取消一个词的排序值；*  词尾的通配符；"" 定义一个短语；