4.《MySQL必知必会》分组与联合_mysql联合分组-CSDN博客

本文链接：https://blog.csdn.net/panda_AJ/article/details/70493085

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

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

分组数据

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

全文本布尔操作符

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