9. 数据分组与排序

1. 使用GROUP BY
   对数据分组

• GROUP BY后跟的列必须在SELECT子句中给出，除了聚集函数。
• GROUP BY不能使用别名。
• 如果使用GROUP BY，那么聚集函数作用域是每个分组。
• 使用HAVING
  对分组过滤。WHERE过滤行，HAVING过滤分组，除了关键字不同，语法都是一致的。

1. 使用ORDER BY
   对数据排序

• ORDER BY默认排序类型为：升序排列ASC
  ，降序排列需要使用关键字：DESC
  。每个关键字只作用一列。
• ORDER BY可以不使用SELECT语句后的列而使用表中其它列进行排序。
• ORDER BY对多列进行排序时，只有当前一列值相同时，才会对后一列进行排序。
• ORDER BY是对上述所有结果的排序，最多只允许出现一次，且在最末（但在LIMIT之上）。

1. MySQL语句顺序

SELECT col_name [col_name2|...]
FROM tbl_name
WHERE condition_for_row
GROUP BY col_name [col_name2|...]
HAVING condition_for_group
ORDER BY col_name [ASC|DESC]
LIMIT n [,m]

复制

注意：顺序错误将会导致错误信息。

10. 子查询、联结查询和组合查询

10.1 子查询

分为相关子查询和不相关子查询。（注意区别它们的检索的先后嵌套顺序）

• 不相关子查询：子查询和外部查询无关，先进行子查询，然后再进行外部查询。

• 相关子查询：涉及外部查询的子查询

-- 不相关子查询
SELECT col_name1
FROM tbl_name1
WHERE col_name2 IN (SELECT col_name2
		    FROM tbl_name2
		    WHERE col_name3 = 'example');

-- 相关子查询
SELECT col_name1
FROM tbl_name1 AS t1
WHERE col_name2 IN (SELECT col_name2
                    FROM tbl_name2 AS t2
                    WHERE t1.id = t2.id);

复制

10.2 联结查询

将多个表联结起来查询，有自联结、内联结（也称等值联结）、外联结（左、右）。

（1）自联结

同一张表进行连接，通常需要为表取别名。

如下代码：

SELECT col_name1, col_name2
FROM tbl_name AS t1, tbl_name AS t2
WHERE t1.id = t2.id AND p2.num = '1001'

复制

也可以使用子查询来代替自联结查询，但通常情况下要测试一下这两种方式哪种的性能更好。

使用子查询代替自联结的代码如下：

SELECT col_name1, col_name2
FROM tbl_name
WHERE tbl_name.id = (SELECT id
                     FROM tbl_name
                     WHERE num = '1001');

复制

（2）内联结/等值联结：

SELECT col_name1, col_name2
FROM tbl_name1 INNER JOIN tbl_name2
ON tbl_name1.id = tbl_name2.id;
-- 等值联结使用where实现
SELECT col_name1, col_name2
FROM tbl_name1 AS t1, tbl_name2 AS t2
WHERE t1.id = t2.id;

复制

自然连接为一种特殊的等值联结，它是等值联结后消除了相同的列。

（3）外联结

-- 左外联结，左表的内容全部显示
SELECT col_name1, col_name2
FROM tbl_name1 LEFT OUTER JOIN tbl_name2
ON tbl_name1.id = tbl_name2.id;
-- 右外联结，右表的内容全部显示
SELECT col_name1, col_name2
FROM tbl_name1 RIGHT OUTER JOIN tbl_name2
ON tbl_name1.id = tbl_name2.id;

复制

（4）使用联结和联结条件

• 注意所使用的联结类型。一般使用内部联结（等值联结），但使用外部联结也是有效的。
• 始终给出联结条件，并保证联结条件的正确性。（如果不给出联结条件会返回笛卡尔积，如果联结条件错误会返回错误数据）
• 分别测试每个联结，有助于排除故障。

10.3 组合查询

使用关键字UNION
进行组合查询，但是结果会自动去掉重复行，如果想保留重复行则需使用UNION ALL
。

其他注意地方：

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用UNION关键字分开。
• UNION中的每个查询必须要包含相同的列、表达式或聚集函数（但是不要求顺序一致）。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（如不同的数值类型或不同的日期类型）

11.全文本搜索

11.1 定义全文本搜索

首先应确保将要使用全文本搜索的表引擎类型为：MyISAM
类型。其次使用全文本搜索，必须索引被搜索的列，使用关键字FULLTEXT(col_name)
指定可以全文本索引的列，如果需要也可以指定多个列。可以在创建表时指定FULLTEXT
，也可以稍后指定，在定义之后，MySQL会自动维护该索引，在增加、更新或删除行时，索引会随之更新。

注意：不要在导入数据时使用FULLTEXT
索引，因为每次导入都会更新索引会花费更多时间，应该先导入数据，然后再修改表来定义FULLTEXT
。

11.2 使用全文本搜索

对定义全文本搜索的列使用函数Match()
和Against()
进行全文本搜索，Match()用于指定被搜索的列，Against()用于指定搜索表达式。

如：

SELECT col_name
FROM tbl_name
WHERE MATCH(indexed_col) AGAINST('example')   -- 不区分搜索文本的大小写

复制

分析：从表中可全文本搜索的列中搜索含有 ' example ' 值的文本，并返回数据行。MATCH(indexed_col) AGAINST('example')
语句可以使用在WHERE
子句中，也可以用作计算字段SELECT
子句中 。

注意：传递给Match()
的值必须与FULLTEXT()
定义中相同，如果指定多个列，则必须以正确次序列出它们。

使用FULLTEXT
和LIKE
的重要区别：

• FULLTEXT
  按文本匹配程度返回数据行（匹配等级高→匹配等级低）。全文本搜索会对每个列值数据的匹配程度生成一个匹配等级值，匹配等级越高代表匹配程度越大，全文本搜索会把匹配结果排序，较高匹配等级的先返回，因为这些数据通常恰恰就是我们需要的。
• 使用FULLTEXT
  数据是被索引的，因此全文本搜索速度更快。

11.3 扩展查询

扩展查询是在使用全文本搜索时，利用查询结果再进行查询，以获得可能相关的结果。使用关键字WITH QUERY EXPANSION
进行扩展查询，如：

SELECT col_name
FROM tbl_name
WHERE MATCH(indexed_col) AGAINST('example' WITH QUERY EXPANSION)

复制

使用查询的扩展时，当表中的数据行越多时，返回的结果越好。

11.4 布尔文本搜索

使用关键字IN BOOLEAN MODE
对文本进行布尔搜索，当没有使用FULLTEXT
索引时也可以使用布尔文本搜索，只是这时其速度极慢并且当数据量增加时性能会大大降低。

全文本布尔操作符

（1）搜索匹配包含 yes 和 no 中至少一个词的行：

SELECT col_name
FROM tbl_name
WHERE MATCH(indexed_col) AGAINST('yes no' IN BOOLEAN MODE )

复制

（2）搜索匹配包含 yes 但不包含 no 的行：

SELECT col_name
FROM tbl_name
WHERE MATCH(indexed_col) AGAINST('+yes -no' IN BOOLEAN MODE )

复制

（3）搜索匹配包含 yes 和 no 中至少一个词的行，并增加 yes 的等级，降低 no 的等级

SELECT col_name
FROM tbl_name
WHERE MATCH(indexed_col) AGAINST('>yes <no' IN BOOLEAN MODE )

复制

（4）搜索匹配短语"yes no"的行：

SELECT col_name
FROM tbl_name
WHERE MATCH(indexed_col) AGAINST('"yes no"' IN BOOLEAN MODE )

复制

（5）搜索匹配词 yes 和 no，降低后者的等级：

SELECT col_name
FROM tbl_name
WHERE MATCH(indexed_col) AGAINST('+yes +(<no)' IN BOOLEAN MODE )

复制

总结：以布尔方式，可以提供关于如下内容的细节

• 要匹配的词
• 要排斥的词(如果某行包含这个词语，则不返回该行，即使它包含其他指定的词也是如此)
• 调整等级（指定某些词比其他词更重要，更重要的词等级更高）
• 表达式分组
• 其他内容

全文本搜索的使用说明：

• 仅在MyISAM
  数据库引擎中支持全文本搜索。
• 许多词出现的频率很高，但实际用处很少。因此MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE
  。
• 如果表中的行数少于3行，则全文本搜索不返回任何结果。
• 在索引全文本数据时，短词（具有3个或3个以下字符的词）被忽略且从索引中排除，短语定义可以修改。
• MySQL带有一个内建的非用词列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表。
• 忽略词中的单引号。如：I'm索引为Im。
• 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。

👉MySQL必知必会（2）——函数集合

👉MySQL必知必会笔记（1）

👉设计模式之观察者模式

👉Java代码块与代码加载顺序

👉排序算法——计数排序

✨喜欢的老铁点个关注吧✨

点击『在看』是对我最大的支持