MySQL的函数和运算符 - 聚合函数 - 检测功能依赖

林员外聊编程 2021-09-22

164

检测功能依赖

下面的讨论提供了几个 MySQL 检测功能依赖关系的示例。下面的例子使用了这种表示法：

{X} -> {Y}

将其理解为“X 唯一地决定 Y”，这也意味着 Y 在功能上依赖于X。

示例使用 world 数据库，该数据库可以从 https://dev.mysql.com/doc/index-other.html 下载。可以在同一页面上找到关于如何安装数据库的详细信息。

从键派生的功能依赖

下面的示例查询每个国家的语言数量：

SELECT co.Name, COUNT(*)
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY co.Code;

co.Code 是 co 的主键，所以 co 的所有列在功能上都依赖于它，如下所示：

{co.Code} -> {co.*}

因此，co.name 在功能上依赖于 GROUP BY 列，查询是有效的。

可以使用 NOT NULL 列上的 UNIQUE 索引来代替主键，会应用相同的功能依赖关系。(对于允许 NULL 值的 UNIQUE 索引不是这样的，因为它允许多个 NULL 值，在这种情况下惟一性就丢失了。)

从多列键和等式派生的功能依赖

这个查询列出每个国家所有语言列表，以及有多少人在说这些语言：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population / 100.0 AS SpokenBy
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

(cl.CountryCode, cl.Language) 对儿是 cl 的两列复合主键，因此这2列唯一地确定 cl 的所有列：

{cl.CountryCode, cl.Language} -> {cl.*}

此外，由于 WHERE 子句中的相等性：

{cl.CountryCode} -> {co.Code}

并且，因为 co.Code 是 co 的主键：

{co.Code} -> {co.*}

“唯一决定”关系是可传递的，因此：

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

因此，查询是有效的。

与前面的示例一样，可以使用 NOT NULL 列上的 UNIQUE 键来代替主键。

可以使用 INNER JOIN 条件代替 WHERE 条件。会应用同样的功能依赖：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl INNER JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

功能依赖的特殊情况

WHERE 条件或 INNER JOIN 条件中的相等测试是对称的，而外部连接条件中的相等测试则不是，因为表扮演不同的角色。

假设引用完整性被意外破坏，并且存在一行 countrylanguage，而在 country 中没有相应的行。考虑与前面示例相同的查询，但使用了 LEFT JOIN：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl LEFT JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

对于给定的 cl.CountryCode 值。co.Code 的值要么在匹配的行中找到(由 cl.CountryCode 确定)，要么没有匹配，由 NULL 填补(也由 cl.CountryCode 确定)。在每种情况下，这种关系都适用于：

{cl.CountryCode} -> {co.Code}

cl.CountryCode 本身在功能上依赖于 {cl.CountryCode, cl.Language}，此两列是主键。

如果连接结果中 co.Code 填补为 NULL，co.Name 也是。如果 co.Code 不是由 NULL 补齐的，那么因为 co.Code 是一个主键，它决定了 co.Name。因此，在所有情况下：

{co.Code} -> {co.Name}

这将产生：

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

因此，查询是有效的。

但是，假设表被交换了，就像下面的查询：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM country co LEFT JOIN countrylanguage cl
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

这种关系并不适用：

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

实际上，为 cl 生成的所有由 NULL 补齐的行都被放入一个组中(它们的两个 GROUP BY 列都等于 NULL)，并且在这个组中 co.Name 的值可以变化。查询是无效的，MySQL 会拒绝。

因此，外部联接中的功能依赖性与行列式列属于 LEFT JOIN 的左侧还是右侧有关。如果存在嵌套的外部连接或连接条件不完全由相等比较组成，则判断功能依赖关系将变得更加复杂。

功能依赖和视图

假设一个关于国家的视图生成了它们的代码，大写名称，以及有多少种不同的官方语言：

CREATE VIEW country2 AS
SELECT co.Code, UPPER(co.Name) AS UpperName,
COUNT(cl.Language) AS OfficialLanguages
FROM country AS co JOIN countrylanguage AS cl
ON cl.CountryCode = co.Code
WHERE cl.isOfficial = 'T'
GROUP BY co.Code;

这个定义有效是因为：

{co.Code} -> {co.*}

在视图结果中，第一个选中的列是 co.Code，它也是分组列，因此决定了所有其他选中的表达式：

{country2.Code} -> {country2.*}

MySQL 理解并使用这些信息，如下所述。

该查询通过将视图与 city 表结合，显示了国家、它们有多少种不同的官方语言以及它们有多少城市：

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM country2 AS co2 JOIN city ci
ON ci.CountryCode = co2.Code
GROUP BY co2.Code;

如前所述，这个查询是有效的：

{co2.Code} -> {co2.*}

MySQL 能够在视图的结果中发现功能依赖，并使用它来验证使用该视图的查询。如果 country2 是一个派生表(或公共表表达式)，也同样成立，如：

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM
(
 SELECT co.Code, UPPER(co.Name) AS UpperName,
 COUNT(cl.Language) AS OfficialLanguages
 FROM country AS co JOIN countrylanguage AS cl
 ON cl.CountryCode=co.Code
 WHERE cl.isOfficial='T'
 GROUP BY co.Code
) AS co2
JOIN city ci ON ci.CountryCode = co2.Code
GROUP BY co2.Code;

功能依赖的组合

MySQL 能够结合前面所有类型的功能依赖(基于键、基于相等、基于视图)来验证更复杂的查询。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/group-by-functional-dependence.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 聚合函数 - 检测功能依赖

评论