mysql - 如何使用外键从两个表中获取所有数据

Question

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

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

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

当只有一个表时选择单条记录:

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

如何从两个表中获取由 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
...

Answer 1

使用明确的 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'

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

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

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