mysql - 加入4张 table

Question

我是这种关系型数据库设计的新手。我只是以这种方式设计了数据库。但是，我对 MySQL 的这个 JOIN 很困惑。加入所有此表的查询应该是什么。如果您可以看到表 users 是所有表的引用。

用户

+----------+----------------+-----------------+
| users_id | users_level_id | users_status_id |
+----------+----------------+-----------------+
|        1 |              1 |               1 |
|        2 |              2 |               1 |
+----------+----------------+-----------------+

users_credentials

+----------+---------------------------+-----------------------------+----------------------------+
| users_id | users_credential_username | users_credential_email      | users_credential_password  |
+----------+---------------------------+-----------------------------+----------------------------+
|        1 | super                     | super@gmail.com             | $5$e94e9e$vptscyHjm8rdX0j6 |
|        2 | admin                     | admin@gmail.com             | $5$fVuOmySyC0PttbiMn8in0k7 |
+----------+---------------------------+-----------------------------+----------------------------+

用户级别

+----------------+-------------------------+
| users_level_id | users_level_description |
+----------------+-------------------------+
|              1 | Super Administrator     |
|              2 | Administrator           |
+----------------+-------------------------+

用户状态

+-----------------+--------------------------+
| users_status_id | users_status_description |
+-----------------+--------------------------+
|               0 | Disabled                 |
|               1 | Enabled                  |
+-----------------+--------------------------+

Answer 1

试试这个

SELECT u.*, uc.*, ul.*, us.*
FROM users u 
INNER JOIN users_credentials uc
    ON u.users_id = uc.users_id
INNER JOIN users_level ul
    ON u.users_level_id = ul.users_level_id
INNER JOIN users_status us
    ON u.users_status_id = us.users_status_id

注意 INNER JOIN 的使用:这意味着如果用户在连接表上没有相应的记录，则不会显示；如果您需要在相关表上没有匹配记录的情况下返回每个用户，请将 INNER JOIN 更改为 LEFT JOIN。

在用户评论后编辑:
如果你只想返回一些列，将其定义为这个例子

SELECT uc.users_credential_username AS username, 
       uc.users_credential_email AS email, 
       uc.users_credential_password AS pwd, 
       ul.users_level_description AS level, 
       us.users_status_description AS status