列出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屋!