mysql - 如何在此模式中进行选择？

Question

我有以下架构

mysql> describe category;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(64)            | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe subcategory;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | char(64)            | YES  |     | NULL    |                |
| category_id | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe quote;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| content        | text                | YES  |     | NULL    |                |
| category_id    | tinyint(3) unsigned | YES  |     | NULL    |                |
| subcategory_id | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


mysql> select * from quote;
+----+---------+-------------+----------------+
| id | content | category_id | subcategory_id |
+----+---------+-------------+----------------+
|  1 | text1   |           1 |              1 |
|  2 | text2   |           1 |              2 |
|  3 | text3   |           2 |              1 |
|  4 | text4   |           1 |              3 |
|  5 | text5   |           2 |              3 |
|  6 | text6   |           2 |              1 |
|  7 | text7   |           2 |              2 |
|  8 | text8   |           3 |              1 |
+----+---------+-------------+----------------+
8 rows in set (0.00 sec)

mysql> 

第一个问题是，我应该删除 quote.category_id 以获得规范化的数据库吗？

更重要的是，我愿意

选择 quote.content，其中 subcategory.name='some_name'

如何存档？

很抱歉问这个基本问题，但我是 SQL 新手。我想这涉及某种 JOIN？

Answer 1

我设法找出一种方法是:

select content, quote.category_id, quote.subcategory_id from quote, subcategory where subcategory.id=quote.subcategory_id and subcategory.name='some_name';