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_prods FROM products GROUP BY vend_id;

2.过滤分组

SELECT cust_id,COUNT(*) AS prders FROM orders WHERE prod_price >=10 GROUP BY cust_id HAVING COUNT(*) >= 2;

3.分组和排序

SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderiterms GROUP BY order_num HAVING COUNT(quantity*item_price) >= 50 ORDER BY ordertotal;

4.SELECT子句顺序

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

使用子查询

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

1.利用子查询进行过滤

SELECT cust_id FROM orders WHERE 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 orders FROM customers ORDER BY cust_name;
-- orders为计算字段

联合表

1.创建联结

-- 等值联结
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;

-- 下面是笛卡尔积的结果，行数为两个表格行数的乘积；
SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name,prod_name;

2.内部联结

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

3.联结多个表

SELECT vend_name, prod_name, prod_price，quantity FROM orderitems, products, vendors WHERE 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_title FROM vendors ORDER BY vend_name;

2.自联结

-- 检索该供应商生产的其他物品
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';

3.自然联结

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';

4.外部联结

SELECT customers.cust_id, orders.order_num FROM 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_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

组合查询

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);

2.包含或取消重复的行

-- 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);

3.对组合查询结果排序

-- ORDER BY 是对组合之后的结果结果排序；
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;

全文本搜索

两个常用的引擎为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_text FROM productnotes WHERE Match(note_text) Against('rabbit');

-- 上述搜索也可以简单用LIKE完成；
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';

-- 区别：全文本搜索对结果排序；

3.使用查询扩展

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

4.布尔文本搜索

-- 不采用50%规则（当返回值太多是，只返回出现50%以上的词）；速度很慢；
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

-- 全文本布尔操作，还可以配合很多布尔操作符使用；
-- 例1，匹配包含词rabbit和bait的行；
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+heavy +bait' IN BOOLEAN MODE);

-- 例2，匹配包含词rabbit和bait中至少一个的行；
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy bait' IN BOOLEAN MODE);

全文本布尔操作符

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