如何找到远程相关的表之间的关系?MySQL [英] How do I find relations between tables that are long-distance related? MySQL

问题描述

我在从 prestashop 数据库中查找表 ps_product 和 ps_carrier 之间的关系时遇到问题.该架构可在 http://doc.prestashop.com/display/PS16/Fundamentals+of+PrestaShop+Development.

I have a problem with finding relations between tables ps_product and ps_carrier from a prestashop database. The schema is available at http://doc.prestashop.com/display/PS16/Fundamentals+of+PrestaShop+Development.

我需要通过在我的商店中加入这两个表来进行更新，但我正在努力寻找好的钥匙.如何编写查询?

I need to make an update by joining these two tables in my shop but I'm struggling with finding good keys. How do I compose my query?

推荐答案

表格代表业务关系/关联.您提到的关系[ship]s"是FK(外键)，查询不需要.他们声明某些列的子行值也必须是某些键列的子行值.需要的是知道一行在表中时对当前业务情况的描述.(根据出现的情况，确定 FK 和其他约束.)

Tables represent business relationships/associations. The "relation[ship]s" you mention are FKs (foreign keys), and which are not needed for querying. They state that subrow values for some columns must also be subrow values for some key columns. What is needed is to know what a row says about the current business situation when it is in a table. (Which, given what situations arise, determine the FKs and other constraints.)

来自需要在第三个表中加入 2 个表及其 FK:

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

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

查询也有谓词.它的值也是使其谓词为真的行.它的谓词是根据它的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.

(CROSS or INNER) JOIN 在谓词之间放置 AND；UNION 在它们之间放置 OR；除了插入 AND NOT 和 ON &在哪里和在一个条件下；当 SELECT 从 T 中删除列 C 时，它会将 FOR SOME (value for) C 放在 T 的谓词前面.(其他运营商等等.)

(CROSS or INNER) JOIN puts AND between predicates; UNION puts OR between them; EXCEPT inserts AND NOT and ON & WHERE AND in a condition; when SELECT drops a column C from T, it puts FOR SOME (value for) C in front of T's predicate. (Etc for other operators.)

给定

-- rows where product [id_product] is supplied by [id_supplier] ...
ps_product(id_product, id_supplier, ...)
-- rows where carrier [id_carrier] has reference [id_reference] ...
ps_carrier(id_carrier, id_reference, ....)

我们写

    ps_product s
JOIN ...
ON s.id_product = ...
...
JOIN ps_carrier c
ON ... = id_carrier
WHERE ...

获取行的位置

    product [p.id_product] is supplied by [p.id_supplier] ...
AND ...
AND s.id_product = ...
...
AND carrier [c.id_carrier] has reference [c.id_reference] ...
AND ... = id_carrier
AND ...

您需要知道表的谓词，然后将表连接到 ON 或 WHERE 条件，以便生成的谓词用于您想要返回的行.

You need know your tables' predicates then JOIN together tables ON or WHERE conditions so that the resulting predicate is for the rows you want back.

是否有任何经验法则可以根据人类可读的描述构造 SQL 查询?

这篇关于如何找到远程相关的表之间的关系?MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！