需要将 2 个表与他们的 FK 连接到第 3 个表中

2021-11-20 00:00:00 join sql foreign-keys mysql foreign-collection

所以基本上我正在关注一个教程问题，它问我以下问题.我不太确定如何加入不包含其他 FK 的 2 个表，它们(即它们的两个 FK)位于第三个表中.我能得到一些帮助/解释吗?

so basically I `m following a tutorial question which asks me the below. I am not too sure how to join 2 tables which do not contain the others FK, their (i.e. both of their FKs) are located in a 3rd table. Could I get some help/explanation?

我的回答

SELECT Forest.Fo_name, Species.Sp_name, Species.Sp_woodtype FROM Forest INNER JOIN Species ON Tree.Tr_species=Tree.Tr_forest WHERE Fo_loc='ARTIC' ORDER BY Fo_name, Sp_name

对于在编码为ARTIC"的区域中发现的森林，请列出其中发现的森林名称和物种名称以及物种木材类型.消除任何重复项并按森林名称和物种名称对输出进行排序"

"For forests found in the regions coded as "ARTIC" list the forest name & species name and species wood type found therein. Eliminate any duplicates and order the output by forest name and species name"

物种表

+--------------+------------------+------+--------------------------------+ | Field | Type | Key | Glossary | +--------------+------------------+------+--------------------------------+ | sp_name | C(10) | PK | Species Name | | sp_woodtype | C(10) | | Wood Yielded by tree | | sp_maxht | I | | Max. Height | +--------------+------------------+------+--------------------------------+

森林表

+--------------+------------------+------+--------------------------------+ | Field | Type | Key | Glossary | +--------------+------------------+------+--------------------------------+ | Fo_name | C(10) | PK | Forest Name | | Fo_size | I | | Forest Area | | Fo_loc | C(10) | | Geographical Area | | Fo_comp | C(10) | | Forest Owner | +--------------+------------------+------+--------------------------------+

树表

+--------------+------------------+------+---------------------------------------------+ | Field | Type | Key | Glossary | +--------------+------------------+------+---------------------------------------------+ | Tr_species | C(10) | FK | (FK of species.sp_name | | Tr_forest | C(10) | FK | (FK of forest.fo_name | | Tr_numb | I | PK | Sequence number | | Tr_planted | Date | | Date of planting | | Tr_loc | C(10) | | Forest quadrant | | Tr_parent | I | FK | (FK of tree.numb) procreating tree reference| +--------------+------------------+------+---------------------------------------------+

C(10) &我代表字符 (10) &分别为整数

C(10) & I stand for character (10) & Integer respectively

推荐答案

加入表不需要外键！

因此，当表之间没有 FK 时如何连接表的答案是只是连接它们.

So the answer to how to join tables when no FK is between them is to just join them.

真正的问题是我们如何选择加入哪些表(或以任何其他方式组合)?

声明&表格

每个基表都带有一个由列名参数化的谓词--语句模板.表值是使其谓词成为真正的命题--语句的行.

Every base table comes with a predicate--statement template parameterized by column names. The table value is the rows that make its predicate into a true proposition--statement.

// species [name] yields [woodtype] and has max height [maxht] Species(name,woodtype,maxht) // forest [name] has area [size] in area [loc] and owner [comp] Forest(name,size,loc,comp) // tree group [numb] is of species [species] in forest [forest] and was planted in [planted] in quadrant [loc] on date [date] with parent tree group [parent] Tree(species,forest,numb,planted,loc,parent)

查询也有谓词.它的值也是使其谓词为真的行.它的谓词是根据它的FROM、WHERE等子句构成的.表别名为其基表命名表值，但列以别名为前缀.所以它的谓词是它的基表使用别名前缀列的谓词.

A query also has a predicate. Its value also is the rows that make its predicate true. Its predicate is built up according to its FROM, WHERE and other clauses. A table alias names a table value like its base table but with columns prefixed by the alias. So its predicate is its base table's predicate using alias-prefixed columns.

Species s

保存满足

species [s.name] yields [s.woodtype] and has max height [s.maxht]

CROSS &INNER JOIN 将 AND 放在谓词之间；UNION 在它们之间放置 OR；EXCEPT 插入 AND NOT 和 ON &WHERE AND 条件；SELECT 重命名、添加 &删除列.(其他运营商等.)所以

CROSS & INNER JOIN put AND between predicates; UNION puts OR between them; EXCEPT inserts AND NOT and ON & WHERE AND in a condition; SELECT renames, adds & drops columns. (Etc for other operators.) So

Species s CROSS JOIN Forest f

保存行

species [s.name] yields [s.woodtype] and has max height [s.maxht] AND forest [f.name] has area [f.size] in area [f.loc] and owner [f.comp]

(无论约束是什么！)如果您只想要上面的行以它们的木材类型命名森林，那么您只需通过 ... WHERE f.name=s.woodtype 因为这使得值成为满足 ... AND f.name=s.woodtype 的行.
(No matter what the constraints are!) If you wanted only the rows above having forests named after their wood type then you would just add a condition via ... WHERE f.name=s.woodtype because that makes the value be rows satisfying ... AND f.name=s.woodtype. 对于在编码为ARCTIC"的地区发现的森林，请列出森林名称 &其中发现的物种名称和物种木材类型.消除任何重复项并按森林名称和物种名称对输出进行排序. For forests found in the regions coded as "ARCTIC" list the forest name & species name and species wood type found therein. Eliminate any duplicates and order the output by forest name and species name.
这是一个很大的非正式谓词，返回的行要满足.如果我们尝试仅使用给定的谓词加上 AND、OR、AND NOT(等)来重写它，那么我们只能通过 AND 对所有三个给定谓词(因此，AND>JOIN 基表名称)并添加 AND Forest.loc='ARCTIC'(因此，ON 或 WHERE条件).

That is a big informal predicate that the rows returned are to satisfy. If we try to rewrite it using only the predicates that we have been given plus AND, OR, AND NOT (etc) then we can only do it by ANDing all three given predicates (hence, JOIN of the base table names) and adding AND Forest.loc='ARCTIC' (hence, ON or WHERE that condition).

FK(等)和查询(不是)

PK 和 FK 是完整性约束的特殊情况.给定谓词以及可能出现的情况，只能出现一些数据库值.这就是完整性约束所描述的.它们让 DBMS 将不应出现的数据库值排除在外.(另外，优化查询执行.)因为名称在 Species 中是唯一的，所以我们将其声明为键.森林名称和树麻木同上.因为树中的物种是物种中的名称，而名称是物种的键，所以我们声明 FK Tree.species->Species.name.森林和父母同上.与启用连接无关.(尽管它们暗示查询结果也满足某些约束.)

PKs and FKs are special cases of integrity constraints. Given the predicates and what situations can arise, only some database values can arise. That's what integrity constraints describe. They let the DBMS keep out database values that shouldn't arise. (Also, optimize query execution.) Because a name is unique in Species we declare it a key. Ditto for Forest name and Tree numb. Because a species in Tree is a name in Species and name is a key of Species we declare FK Tree.species->Species.name. Ditto for forest and parent. Nothing to do with enabling joins. (Although they imply that a query result satisfies certain constraints, too.)

查询约束是什么并不重要.如果由于业务规则或 Tree 或 Species 谓词不同而存在未显示为任何 Species 名称值的树种值，则不会有 FK Tree.species->Species.name.但是每个查询将继续返回满足其谓词的行以基表谓词表示.(由于可能的业务情况或谓词不同，因此输入行可能不同，因此输出行也可能不同.)

It does not matter to querying what the constraints are. If there were Tree species values that did not appear as any Species name value because the business rules or Tree or Species predicate(s) were different then there would be no FK Tree.species->Species.name. But each query would continue to return the rows satisfying its predicate as expressed in terms of base table predicates. (Since the possible business situations or predicate(s) would be different, the input rows could be different so output rows could be different.)

什么决定了查询 SQL

因此，我们如何选择要加入的表(或以任何其他方式组合)的答案是我们根据需要安排基表名称、JOIN、UNION、EXCEPT 和 WHERE(等)，以给出一个查询表达式，其谓词为我们希望我们的行满足的那个.这通常被教授为通过感觉来完成的非正式的事情，但现在您知道是什么将 SQL 与自然语言联系起来了.和约束无关.

So the answer to how we choose what tables to join (or combine in any other way) is that we arrange base table names, JOIN, UNION, EXCEPT and WHERE (etc) as appropriate to give a query expression whose predicate is the one we want our rows to satisfy. This is usually taught as something informal to be done by feel but now you know what ties the SQL to the natural language. And constraints are irrelevant.

注意:前面假设我们没有从查询中返回重复项.关系模型中表中没有重复的原因是为了使表运算符和逻辑连接词之间的上述对应关系成立.但是，SQL 表可以有重复项.SQL 不同于关系模型的地方(在很多方面)，查询变得不那么(字面上)逻辑.

NOTE: The preceding assumes that we return no duplicates from queries. The reason why there are no duplicates in tables in the relational model is so that the above correspondence between table operators and logic connectives holds. However SQL tables can have duplicates. Where SQL differs from the relational model (in its many ways), there querying becomes less (literally) logical.

按照外键重新连接
重新选择表格
是否有根据人类可读的描述构造 SQL 查询的经验法则?

相关文章