MySQL SQL 选择由外键链接的其他表中的条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18937539/
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
SQL select entries in other table linked by foreign keys
提问by Jon McPherson
I have redesigned my database structure to use PRIMARY and FOREIGN KEYs to link the entries in my 3 tables together, and I am having problems trying to write queries to select data in one table given data in a another table. Here is an example of my 3 CREATE TABLE statements:
我已经重新设计了我的数据库结构，以使用 PRIMARY 和 FOREIGN KEY 将我的 3 个表中的条目链接在一起，并且我在尝试编写查询以在另一个表中给定数据的情况下选择一个表中的数据时遇到问题。这是我的 3 个 CREATE TABLE 语句的示例：
CREATE TABLE IF NOT EXISTS players (
id INT(10) NOT NULL AUTO_INCREMENT,
username VARCHAR(16) NOT NULL,
uuid VARCHAR(200) NOT NULL DEFAULT 0,
joined TIMESTAMP DEFAULT 0,
last_seen TIMESTAMP DEFAULT 0,
PRIMARY KEY (id)
);
/* ^
One |
To
| One
v
*/
CREATE TABLE IF NOT EXISTS accounts (
id INT(10) NOT NULL AUTO_INCREMENT,
account_id INT(10) NOT NULL,
pass_hash VARCHAR(200) NOT NULL,
pass_salt VARCHAR(200) NOT NULL,
created BIGINT DEFAULT 0,
last_log_on BIGINT DEFAULT 0,
PRIMARY KEY (id),
FOREIGN KEY (account_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB;
/* ^
One |
To
| Many
v
*/
CREATE TABLE IF NOT EXISTS purchases (
id INT(10) NOT NULL AUTO_INCREMENT,
account_id INT(10) NOT NULL,
status VARCHAR(20) NOT NULL,
item INT NOT NULL,
price DOUBLE DEFAULT 0,
description VARCHAR(200) NOT NULL,
buyer_name VARCHAR(200) NOT NULL,
buyer_email VARCHAR(200) NOT NULL,
transaction_id VARCHAR(200) NOT NULL,
payment_type VARCHAR(20) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
) ENGINE=InnoDB;Say for example, I want to select all the usernames of users who purchased anything greater than $30. All the usernames are stored in the players table, which is linked to the accounts table and that is linked to the purchases table. Is this this the best way to design this relational database? If so, how would I run queries similar to the above example?
例如，我想选择购买超过 30 美元商品的用户的所有用户名。所有用户名都存储在玩家表中，该表链接到帐户表，链接到购买表。这是设计此关系数据库的最佳方式吗？如果是这样，我将如何运行类似于上述示例的查询？
I was able to get get all of a users purchase history given their username, but I did it with 2 sub-queries... Getting that data should be easier than that! Here is the SELECT query I ran to get all of a players purchase data:
根据用户名，我能够获取所有用户的购买历史记录，但我使用 2 个子查询做到了……获取数据应该比这更容易！这是我为获取所有玩家购买数据而运行的 SELECT 查询：
SELECT *
FROM purchases
WHERE account_id = (SELECT id FROM accounts WHERE account_id = (SELECT id FROM players WHERE username = 'username'));Also, when I try to make references to the other tables using something like 'players.username', I get an error saying that the column doesn't exist...
此外，当我尝试使用诸如“players.username”之类的内容引用其他表时，我收到一条错误消息，指出该列不存在...
I appreciate any help! Thanks!
我感谢任何帮助！谢谢！
采纳答案by Franco
Your design is ok in my opinion. The relation between players and account is one-to-many and not one-to-one since this way, you can have two tuples referencing a single player.
你的设计在我看来是好的。玩家和帐户之间的关系是一对多而不是一对一，因为这样，您可以有两个元组引用单个玩家。
I would write the query you need as:
我会将您需要的查询写为：
SELECT DISTINCT p.id, p.username
FROM players p INNER JOIN accounts a ON (p.id = a.account_id)
INNER JOIN purchases pc ON (a.id = pc.account_id)
WHERE (pc.price > 30);
As Sam suggested, I added DISTINCT to avoid repeating id and username in case a user have multiple purchases. Note the id is here to avoid confusion among repeated usernames.
正如 Sam 建议的那样，我添加了 DISTINCT 以避免在用户多次购买时重复 id 和用户名。注意这里的 id 是为了避免重复用户名之间的混淆。
