从两个表查询时如何使用外键 [英] How to use foreign key when querying from two tables

问题描述

我的问题很基本. 关于如何使用外键从两个表中选择某些信息来构建查询?

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_owner是vehicles表中的外键，它引用users表的主键user_id.

car_owner is foreign key in vehicles table which references to the primary key user_id of users table.

所以有人正在搜索所有的大众汽车，而我希望将以下信息填充为html(是的，我知道这不是正确的方法-我只是为了简化示例并显示每个信息中的信息桌子上去):

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):

>    echo "Car model:". `vehicles.car_model`
>    echo "Car owner:". `users.user_name`
>    echo "Contacts: ". `users.user_phone`

提前谢谢.

推荐答案

我不确定，如果您了解使用什么外键.外键基本上说对于此条目，父表中必须有一个条目".您说了user_id is foreign key in vehicle table，这对我来说还不清楚.

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.

因此，假设您有一个这样的表定义:

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)
);

要在表中插入新用户时，user_id必须是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.

可以使用外键来实现业务规则.是否每个用户都必须是车主?或者反过来说，每辆车都必须由某人拥有吗?如果您可以用no回答这两个问题，那么在这种情况下不要实现任何外键.但是，如果您可以肯定地回答是"，那就这样做.

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

这篇关于从两个表查询时如何使用外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！