MySQL 如何使用外键从2个表中获取所有数据

How to get all data from 2 tables using foreign key

提问by lr100

This is the result of separating a single table in two:

这是将单个表一分为二的结果：

Table users:
    user_id (pk, ai)
    email
    password
    last_login

Table data:
    user_id (fk to users.user_id)
    data_1
    data_2

To select a single record when there was only one table:

在只有一个表时选择单个记录：

SELECT users.email, users.password, data.data_1, data.data_2
FROM users,data 
WHERE users.email='$user_email' AND users.user_id=data.user_id";

How do I get all records from both tables having the rows connected by users.user_id=data.user_id?

如何从具有由 users.user_id=data.user_id 连接的行的两个表中获取所有记录？

Row1: email, password, data_1, data2
Row2: email, password, data_1, data2
Row3: email, password, data_1, data2
Row4: email, password, data_1, data2
...

回答by VMai

Using explicit joinsyntax could help you. Rewrite your query to:

使用显式join语法可以帮助您。将您的查询重写为：

SELECT 
    users.email, users.password, data.data_1, data.data_2
FROM 
    users
INNER JOIN 
    data 
ON
    users.user_id=data.user_id
WHERE 
    users.email='$user_email'

and get all rows without a WHERE condition:

并获取没有 WHERE 条件的所有行：

SELECT 
    users.email, users.password, data.data_1, data.data_2
FROM 
    users
INNER JOIN 
    data 
ON
    users.user_id=data.user_id

It separates the concerns: conditions that join tables from conditions that restricts the result set.

它将关注点分开：连接表的条件与限制结果集的条件。

回答by flo_badea

have you tried this?

你试过这个吗？

SELECT users.email, users.password, data.data1, data.data2
FROM users,data 
WHERE users.user_id=data.user_id

or this?

或这个？

SELECT users.email, users.password, data.data1, data.data2
FROM users inner join data on users.user_id=data.user_id

回答by M.Usman

We can do it without using join query as follow...

我们可以在不使用连接查询的情况下做到这一点，如下所示...

SELECT users.email, users.password, data.data_1, data.data_2
FROM users,data 
WHERE users.user_id=data.user_id AND users.email='$user_email'

just copy and past above query you may get expected result..

只需复制并粘贴上面的查询，您可能会得到预期的结果..

回答by Caleb Palmquist

To join the userData table to Users try this:

要将 userData 表加入 Users 试试这个：

SELECT u.user_id, u.email, u.password, u.last_login
FROM users u
JOIN userData ud ON (u.userID = ud.userID)

This will return all data where the User ID in the Users table matches the User ID in the userData table.

这将返回用户表中的用户 ID 与 userData 表中的用户 ID 匹配的所有数据。

EditIn addition, there are different kinds of joins:

编辑此外，还有不同种类的连接：

• INNER
• OUTER
• LEFT
• RIGHT

• 内
• 外
• 剩下
• 对

For more information on this and their differences check out this handy reference: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

有关此及其差异的更多信息，请查看此方便的参考：http: //blog.codinghorror.com/a-visual-explanation-of-sql-joins/

回答by Tbone

SELECT users.email, users.password, data.data_1, data.data_2 FROM 
users JOIN data ON  users.user_id=data.user_id
WHERE users.email='$user_email';

回答by vivek

you have to use inner join which returns records when there is a common field matches in both tables.. for ex in your case

你必须使用内部连接，当两个表中有一个公共字段匹配时返回记录..例如在你的情况下

select * from table1 
  inner join table2 on table1.user_id=table2.user_id

will return all the records in both the table by matching the common fields

将通过匹配公共字段返回两个表中的所有记录