php 从两个表查询时如何使用外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15969170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use foreign key when querying from two tables
提问by Ale
My question is quite basic. It is about how can i build my query with using a foreign key to select certain information from two tables?
我的问题很基本。这是关于如何使用外键从两个表中选择某些信息来构建我的查询？
table vehicles
+-------+----------+------------+
| id_car| car_model| car_owner |
+-------+----------+------------+
| 1 | VW | 132|
+-------+----------+------------+
| 2 | VW | 200|
+-------+----------+------------+
table users
+-------+----------+------------+
|user_id| user_name| user_phone |
+-------+----------+------------+
| 132 | Peter| 555-555 |
+-------+----------+------------+
| 200 | Jim | 555-333 |
+-------+----------+------------+
car_owneris foreign key in vehiclestable which references to the primary key user_idof userstable.
car_owner是vehicles表中引用表主键user_id的外键users。
So someone is searching for all VW cars and i want to have as populate the following information as html(yes, I know that this is not the correct way - i use this just to simplify the example and show which information from each table goes):
所以有人正在搜索所有大众汽车，我希望将以下信息填充为 html（是的，我知道这不是正确的方法 - 我使用它只是为了简化示例并显示每个表中的信息） ：
> echo "Car model:". `vehicles.car_model`
> echo "Car owner:". `users.user_name`
> echo "Contacts: ". `users.user_phone`
thanks in advance.
提前致谢。
回答by fancyPants
I'm not sure, if you understood what foreign keys are used for. A foreign key basically says "for this entry there has to be an entry in the parent table". You said user_id is foreign key in vehicle table, which is not clear for me.
我不确定，如果您了解外键的用途。外键基本上是说“对于这个条目，父表中必须有一个条目”。你说user_id is foreign key in vehicle table，这对我来说不清楚。
So, let's assume you have a table definition like this:
所以，让我们假设你有一个这样的表定义：
CREATE TABLE vehicles
(`id_car` int, `car_model` varchar(2), `car_owner` int);
CREATE TABLE users
(`user_id` int, `user_name` varchar(5), `user_phone` varchar(7)
, CONSTRAINT `fk_your_foreign_key` FOREIGN KEY (user_id) REFERENCES vehicles(car_owner)
);
When you want to insert a new user into the table, the user_id must be an existing entry in car_owner column in vehicles table.
当您想向表中插入新用户时，user_id 必须是车辆表 car_owner 列中的现有条目。
Foreign keys are there to implement business rules. Does every user necessarily have to be a car owner? Or the other way round, does every car have to be owned by someone? If you can answer both questions with no, then don't implement any foreign keys for this case. But do so, if you can answer yes for sure.
外键用于实现业务规则。每个用户都必须是车主吗？或者反过来说，每辆车都必须由某人拥有吗？如果您可以用“否”回答这两个问题，则不要在这种情况下实现任何外键。但是，如果您可以肯定地回答是，请这样做。
To get the information you're looking for just do
要获取您正在寻找的信息，请执行以下操作
SELECT
*
FROM
vehicles
INNER JOIN users ON vehicles.car_owner = users.user_id
回答by Adder
Use JOIN:
使用连接：
SELECT * FROM vehicles INNER JOIN users ON (vehicles.car_owner=users.user_id)
To expand: vehicles INNER JOIN userswill return only cars that have owners. If you want to display cars that have a NULL for owner, or a deleted owner, then use vehicles LEFT JOIN users.
扩展：vehicles INNER JOIN users将只返回有车主的汽车。如果要显示所有者为 NULL 或已删除所有者的汽车，请使用vehicles LEFT JOIN users.
For vehicles INNER JOIN usersthere is also a different way possible:
因为vehicles INNER JOIN users还有一种不同的方式可能：
SELECT * FROM vehicles, users WHERE vehicles.car_owner=users.user_id
The latter selects rows matching the condition from the cartesian product of the two tables.
后者从两个表的笛卡尔积中选择符合条件的行。
回答by Rishabh
You need SQL Join, something like this -
你需要 SQL Join，像这样 -
SELECT vehicles.car_model, users.user_name, users.user_phone
FROM vehicles
JOIN users ON vehicles.car_owner = users.users_id
回答by gristy
from a learner's perspective, it is striking that the foreign keys don't appear to be functional wrt linking tables in queries: i.e. the queries given in the 3 answers above work w/o foreign keys. e.g. for the last example, a mysql learner would expect mysql to implicitly infer from a foreign key that vehicle.car_owner = user.users_id so that when querying
从学习者的角度来看，令人惊讶的是，外键似乎不是查询中链接表的功能性wrt：即上面3个答案中给出的查询没有外键。例如，对于最后一个示例，mysql 学习者希望 mysql 从外键中隐式推断出 Vehicle.car_owner = user.users_id 以便在查询时
SELECT vehicles.car_model, users.user_name, users.user_phone
FROM vehicles JOIN users
should suffice. While the query code :
应该足够了。而查询代码：
ON vehicles.car_owner = users.users_id
looks like redundant explicit re-statement of the foreign key
看起来像外键的冗余显式重述
