mysql - SQL 选择外键链接的其他表中的条目

Question

我重新设计了我的数据库结构以使用 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;

例如，我想选择所有购买超过 30 美元的用户的用户名。所有用户名都存储在 players 表中，该表链接到 accounts 表并链接到 purchases 表。这是设计此关系数据库的最佳方式吗？如果是这样，我将如何运行与上述示例类似的查询？

我能够根据用户名获得所有用户的购买历史记录，但我是通过 2 个子查询完成的……获取该数据应该比这更容易!这是我为获取所有玩家购买数据而运行的 SELECT 查询:

SELECT * 
FROM purchases
WHERE account_id = (SELECT id FROM accounts WHERE account_id = (SELECT id FROM players WHERE username = 'username'));

此外，当我尝试使用“players.username”之类的内容引用其他表时，我收到一条错误消息，指出该列不存在...

感谢您的帮助!谢谢!

Answer 1

我认为您的设计还可以。玩家和帐户之间的关系是一对多而不是一对一，因为这样，您可以有两个元组引用单个玩家。

我会把你需要的查询写成:

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

正如 Sam 建议的那样，我添加了 DISTINCT 以避免重复 id 和 username 以防用户多次购买。请注意这里的 id 是为了避免重复用户名之间的混淆。