如何使用外键从2个表中获取所有数据 [英] How to get all data from 2 tables using foreign key

问题描述

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

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

如何从两个表中所有行都由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?

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

推荐答案

使用显式的join语法可以为您提供帮助.将查询重写为:

Using explicit join syntax could help you. Rewrite your query to:

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条件的行:

and get all rows without a WHERE condition:

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.

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