Mysql注意事项（二）

最近回顾了一下MySQL，发现了一些MySQL需要注意的事项，同时也作为学习笔记，记录下来。—2020年06月11日

接上一篇Mysql注意事项（一）

9、分组数据

GROUP BY

规定：

• GROUP BY子句可以包含任意数目的列
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总
• GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名
• 除聚集函数外，SELECT语句中的每个列都必须在GROUP BY子句中给出
• 如果分组中具有NULL值，则NULL将作为一个分组返回。如果有多行NULL值，它们将分为一组
• GROUP BY 子句必须出现在WHERE子句之后，ORDER BY子句之前

使用ROLLUP

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别的值，即聚合函数结果的总数。

示例1：

select vend_id,sum(prod_price) as num_prods
from products
group by vend_id WITH ROLLUP;

结果1：

示例2：

select vend_id,count(*) as num_prods
from products
group by vend_id WITH ROLLUP;

结果2：

HAVING（过滤分组）

HAVING基于完整的分组进行过滤，WHERE过滤指定的是行而不是分组。

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

分组和排序

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择表达式
不一定需要	如果与聚集函数一起使用（或表达式），则必须使用

不要忘记ORDER BY

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

10、SELECT子句及其顺序（非常重要）

从上到下，依次执行

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

11、子查询

子查询：嵌套在其他查询中的查询。

1. 子查询总是从内向外查询。

2. 列必须匹配

   在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

3. 子查询和性能

   使用子查询并不总是执行这种类型的数据检索的最有效的方法。

12、JOIN（联结）

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。

维护引用完整性 指示MySQL只允许在products表的供应商ID列中出现合法值（即出现在vendors表中的供应商）。这就是维护引用完整性。通过在表的定义中指定主键和外键来实现。

完全限定列名 在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。

不要忘了WHERE子句 应该保证所有联结都有WHERE子句，否则MySQL将返回比想象的数据多得多的数据。

性能考虑 MySQL运行在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表，联结的表越多，性能下降越厉害。

INNER JOIN（内部联结）

基于两个表中间的相等测试的联结，称为等值联结，也称为内部联结。

示例：

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

自联结

示例：

使用子查询：

SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_idFROM productsWHERE 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';

用自联结而不用子查询

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的。但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性更好。

外部联结（OUTER JOIN）

联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表，RIGHT指出的是OUTER JOIN右边的表，二LEFT指出的是OUTER JOIN左边的表。

示例：LEFT OUTER JOIN（左联结）

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

结果：

示例：RIGHT OUTER JOIN（右联结）

SELECT customers.cust_id,orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.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;

结果：

13、UNOIN（组合查询）

执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回，这些组合查询通常称为并（union）或复合查询（compound query）。

示例：

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

结果：

UNION规则

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

包含或取消重复的行

UNION从查询结果集中自动去除了重复的行。

如果想返回所有的行（不去除重复的行），可以使用UNION ALL而不是UNION

对组合查询结果排序

在使用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;

结果：

14、全文本搜索

并非所有引擎都支持全文本搜索。

为进行全文本搜索，必须索引被搜索的列。

在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。

示例：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

结果：

注意：productnotes表中的note_text必须添加索引。