列出mysql表中的数据 [英] Listing data from mysql tables

问题描述

cid | category
 1  | desserts
 2  | cakes
 3  | biscuits

食谱名称

id | recipe_name       | cid | iid
 1 | black forest cake | 1,2 | 1,2,3,4
 2 | angel cake        | 2   | 1,2,4
 3 | melting moments   | 3   | 2,5
 4 | croquembouche     | 1,3 | 1,5

成分

iid | ingredient_name
 1  | self-raising flour
 2  | milk
 3  | chocolate
 4  | baking powder
 5  | plain flour

我可以使用cid查询数据库来提取某些配方，即.甜点:

I am able to query the DB using cid to pull certain recipes, ie. desserts:

SELECT * FROM recipe_name WHERE cid='1'

但是然后如何创建像下面这样的配料列表，其中的配料用<br>列出?

But then how do I create a list of ingredients like the below where ingredients are listed with <br>?

黑森林蛋糕:
自发面粉
牛奶
巧克力
发酵粉

Black Forest Cake:
Self-Raising Flour
Milk
Chocolate
Baking Powder

我是新来的，所以请原谅任何愚蠢的问题！

I'm new to this, so please forgive any stupid questions!

推荐答案

将多值属性存储在单个逗号分隔的字段中几乎总是一个坏主意.这使得一切都很难查询.

Storing multivalued attributes in a single comma separated field is almost always a bad idea. It makes everything very difficult to query.

相反，您可能需要考虑使用两个新的交叉表来重构架构.

Instead, you may want to consider refactoring your schema, using two new intersection tables.

这两个表保持不变(只是将名称从recipe_category更改为categories，以便不与交集表冲突):

These two tables remain as they are (just changed name of recipe_category to categories in order not to clash with the intersection table):

CREATE TABLE categories (
   cid int NOT NULL PRIMARY KEY,
   category_name varchar(50)
) ENGINE=INNODB;

CREATE TABLE ingredients (
   iid int NOT NULL PRIMARY KEY,
   ingredient_name varchar(50)
) ENGINE=INNODB;

按如下所示修改recipe_name表，删除cid和iid字段:

Modify the recipe_name table as follows, removing the cid and iid fields:

CREATE TABLE recipe_name (
    id int NOT NULL PRIMARY KEY,
    recipe_name varchar(50)
) ENGINE=INNODB;

然后，您可以使用以下两个相交表定义多值关系:

Then you can define your multivalued relationships using the following two intersection tables:

CREATE TABLE recipe_ingredients (
    recipe_id int NOT NULL,
    ingredient_id int NOT NULL,
    PRIMARY KEY (recipe_id, ingredient_id),
    FOREIGN KEY (recipe_id) REFERENCES recipe_name (id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients (iid)
) ENGINE=INNODB;

CREATE TABLE recipe_categories (
    recipe_id int NOT NULL,
    category_id int NOT NULL,
    PRIMARY KEY (recipe_id, category_id),
    FOREIGN KEY (recipe_id) REFERENCES recipe_name (id),
    FOREIGN KEY (category_id) REFERENCES categories (cid)
) ENGINE=INNODB;

现在让我们用示例数据填充这些表:

Now let's populate these tables with your example data:

INSERT INTO categories VALUES (1, 'desserts');
INSERT INTO categories VALUES (2, 'cakes');
INSERT INTO categories VALUES (3, 'biscuits');

INSERT INTO ingredients VALUES(1, 'self-raising flour');
INSERT INTO ingredients VALUES(2, 'milk');
INSERT INTO ingredients VALUES(3, 'chocolate');
INSERT INTO ingredients VALUES(4, 'baking powder');
INSERT INTO ingredients VALUES(5, 'plain flour');

INSERT INTO recipe_name VALUES(1, 'black forest cake');
INSERT INTO recipe_name VALUES(2, 'angel cake');
INSERT INTO recipe_name VALUES(3, 'melting moments');
INSERT INTO recipe_name VALUES(4, 'croquembouche'); 

要定义配方及其成分和类别之间的关系，您需要按如下所示填写相交表:

To define the relationships between the recipes and their ingredients and categories, you would need to fill up the intersection tables as follows:

INSERT INTO recipe_categories VALUES (1, 1);
INSERT INTO recipe_categories VALUES (1, 2);
INSERT INTO recipe_categories VALUES (2, 2);
INSERT INTO recipe_categories VALUES (3, 3);
INSERT INTO recipe_categories VALUES (4, 1);
INSERT INTO recipe_categories VALUES (4, 3);

INSERT INTO recipe_ingredients VALUES (1, 1);
INSERT INTO recipe_ingredients VALUES (1, 2);
INSERT INTO recipe_ingredients VALUES (1, 3);
INSERT INTO recipe_ingredients VALUES (1, 4);
INSERT INTO recipe_ingredients VALUES (2, 1);
INSERT INTO recipe_ingredients VALUES (2, 2);
INSERT INTO recipe_ingredients VALUES (2, 3);
INSERT INTO recipe_ingredients VALUES (3, 2);
INSERT INTO recipe_ingredients VALUES (3, 5);
INSERT INTO recipe_ingredients VALUES (4, 1);
INSERT INTO recipe_ingredients VALUES (4, 5);

最后，建立查询就这么简单:

Finally, building your query will be as easy as this:

SELECT i.ingredient_name
FROM   recipe_ingredients ri
JOIN   ingredients i ON (i.iid = ri.ingredient_id)
WHERE  ri.recipe_id = (SELECT id 
                       FROM   recipe_name 
                       WHERE  recipe_name = 'Black Forest Cake');

结果:

+--------------------+
| ingredient_name    |
+--------------------+
| self-raising flour |
| milk               |
| chocolate          |
| baking powder      |
+--------------------+
4 rows in set (0.00 sec)

然后，您可能想要在应用程序代码而不是SQL中格式化该结果集(添加<br>).

You may then want to format that result set (adding the <br>s) in your application code instead of in SQL.

但是，如果您真的希望用SQL做到这一点，那么MySQL支持方便的 GROUP_CONCAT() 函数，其用法如下:

However if you really wish to do that in SQL, then MySQL supports the handy GROUP_CONCAT() function, which can be used as follows:

SELECT GROUP_CONCAT(i.ingredient_name separator '<BR>') output
FROM   recipe_ingredients ri
JOIN   ingredients i ON (i.iid = ri.ingredient_id)
WHERE  ri.recipe_id = (SELECT id 
                       FROM   recipe_name 
                       WHERE  recipe_name = 'Black Forest Cake');

结果:

+----------------------------------------------------------+
| output                                                   |
+----------------------------------------------------------+
| self-raising flour<BR>milk<BR>chocolate<BR>baking powder |
+----------------------------------------------------------+
1 row in set (0.00 sec)

将其转储为HTML，就可以了！

Dump that into HTML, and you're good to go!

这篇关于列出mysql表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！