mysql - 在多个联接表和相关表中查找特定结果

我无法用抽象的语言解释我的问题。这是一个非常简单的问题，但我需要通过这个非常明显的例子。它是完全合成的，因此应该与simmilar应用程序相当。
我们有一堆包含用户信息的表，所有的表都是我认为规范化的，有些值只是通过id引用到其他表。
我使用的是mySQL（以及带有mySQL I扩展的PHP，如果这很重要，我对此表示怀疑）
举个例子：

table user_data

=====================================================
|| User_ID || Name || age || gender || location_ID ||
=====================================================
|| U000001 || Paul || 30  || m      || L00001      ||
|| U000002 || John || 20  || m      || L00001      ||
|| U000003 || Mike || 25  || m      || L00002      ||
|| U000004 || Anna || 25  || f      || L00003      ||


table user_personal_info

============================================
|| User_ID || color || food  || profession||
============================================
|| U000001 || red   || pizza || architect ||
|| U000002 || blue  || pasta || policeman ||
|| U000003 || green || steak || plumber   ||
|| U000004 || pink  || salad || teacher   ||


table locations

========================================================
|| location_ID || country || state      || city       ||
========================================================
|| L00001      || USA     || New York   || New York   ||
|| L00002      || USA     || New York   || Buffalo    ||
|| L00003      || USA     || California || Sacramento ||
|| L00004      || Canada  || Ontario    || Toronto    ||
|| L00005      || Canada  || Quebec     || Montreal   ||



table user_activities

=========================================
|| activity_ID  || user_ID || priority ||
=========================================
|| A0003        || U000001 || 5        ||
|| A0005        || U000001 || 4        ||
|| A0004        || U000002 || 2        ||
|| A0006        || U000002 || 1        ||
|| A0001        || U000003 || 3        ||
|| A0002        || U000004 || 4        ||
|| A0001        || U000004 || 1        ||
|| A0003        || U000004 || 5        ||

table activities

=================================
|| activity_ID  || description ||
=================================
|| A0001        || surfing     ||
|| A0002        || exercising  ||
|| A0003        || baseball    ||
|| A0004        || theater     ||
|| A0005        || dancing     ||
|| A0006        || reading     ||

好吧，你明白了，对吧？
为了显示每个条目，我使用以下mySQL语句，然后在PHP中循环resultset，依此类推：

SELECT * FROM user_data
JOIN user_personal_info USING (User_ID)

为了显示他们最喜欢的活动，我还必须这样做：

SELECT * FROM user_activities
WHERE user_ID = (current user_id)

当然，我必须通过附加的查询来翻译activity ID代表什么，location ID代表什么。。。
（顺便问一下：是否有人对如何显示所有用户及其关联的所有字段有更好的建议，而不是执行两个查询？）
现在我想建立一个彻底的搜索功能，找到非常具体的用户。
我知道如何使用PHP过滤我的结果，但这需要我先下载整个数据库，而且一旦数据库中有几千个用户，这可能需要很长时间。
我知道如何找到用户谁是男性，女性，或两者兼而有之，谁喜欢食物或颜色，谁是从一个特定的位置（位置ID=L00001左右）。。。
我知道如何分配年龄的规则（=，>，我的问题是：
如何查找来自某个国家或州的所有用户？
*如何让mySQL只显示那些用户，他们的位置ID与位置ID数组中的一个匹配？*
如何查找具有一个和/或多个特定活动的所有用户？
如何让mySQL只显示那些用户，他们的活动数组至少与数组中的所有活动匹配（这将是AND版本）？
*如何让mySQL只显示那些用户，他们的活动数组至少包含数组中的一个活动（即OR版本）？*
现在真正重要的问题是：
如何将这些语句与上面的正常语句结合起来？
意思是：我怎样才能找到所有来自纽约州的用户谁喜欢冲浪，谁是男性谁喜欢比萨饼？
或
我如何找到所有来自美国的用户谁喜欢阅读，跳舞，谁超过30岁，谁喜欢绿色？
或
我怎样才能找到加州萨克拉门托的所有水管工和女性用户？
等等，这些例子显然是无穷无尽的！
我相信有人会告诉我“你应该研究这个关键词”。但由于我无法以自负的方式表达我的问题，我没有成功地找到很多信息。。。
更新：
谢谢你的回答。有人向我指出了一些有用的事情，以下是我不知道但现在要做的事情的摘要：
更有效地利用连接
中间接线员
GROUP BY运算符与HAVING COUNT（）结合使用
和子选择
谢谢你把那些东西指给我看！:)

最佳答案

嗯，我想你要找的关键字之一是IN运算符。

SELECT * FROM locations WHERE country IN ('USA', 'Canada', 'Denmark')

将返回所有行，其中in子句中的一个值与country字段匹配。就像写下了这样：

SELECT * FROM locations WHERE country = 'USA' OR country = 'Canada' OR country = 'Denmark'

至于你的其他问题：
对于如何显示所有用户及其关联的所有字段，而不是执行两个查询，是否有更好的建议？
简单地将它们结合在一起，比如：

SELECT * FROM user_data
JOIN locations ON user_data.location_ID = locations.location_ID
JOIN user_personal_info ON user_data.User_ID = user_personal_info.User_ID
JOIN user_activities ON user_personal_info.User_ID = user_activities.User_ID
JOIN activities ON user_activities.activity_ID = activities.activity_ID

当然，根据您使用的结构，您可以使用LEFT JOIN或RIGHT JOIN等。简单地通过SELECT *检索所有数据也不是一个好的做法，而是只选择您需要的字段。
此外，您可以/应该创建一个/多个表示所需联接数据的视图，并从中进行选择。
如何查找来自某个国家或州的所有用户？

SELECT user_data.* FROM user_data
JOIN locations ON user_data.location_ID = locations.location_ID
WHERE locations.country = 'USA' AND state = 'New York'

这取决于如何从用户那里获取数据，以及如何在PHP中为语句准备数据。例如，假设您的用户搜索一个国家并通过post方法获得该国：

<?php
  $country = sanitize($_POST['country']);  // assuming a sanitation function for user input
  // whether by doing a sub-select
  $sql = "SELECT user_data.* FROM user_data WHERE user_data.location_ID = (SELECT locations.location_ID FROM locations WHERE locations.country LIKE '%{$country}%')";

  // or doing a join
  $sql = "SELECT user_data.* FROM user_data JOIN locations ON user_data.location_ID = locations.location_ID WHERE locations.country LIKE '%{$country}%'";
?>

当然，国家也有同样的原则。
如何查找具有一个和/或多个特定活动的所有用户？
在这里，您需要根据activities表进行连接，并使用IN运算符，如上图所示。
如何将这些语句与上面的正常语句结合起来？
以你的例子，我如何找到所有来自纽约州的用户谁喜欢冲浪，谁是男性，谁喜欢比萨饼？

SELECT user_data.* FROM user_data
 JOIN locations ON user_data.locations_ID = locations.location_ID
 JOIN user_activities = user_data.User_ID = user_activities.user_ID
 JOIN activities ON user_activities.activity_ID = user_activities.activity_ID
WHERE locations.sate = 'New York'
  AND activities.description IN ('surfing')
  AND user_data.gender = 'm'
  AND user_personal_info.food = 'pizza'

希望这能帮助你找到正确的方向。
更新
当然，这里的IN运算符可以用description = 'surfing'替换，因为它只有一个值。如果你再加上一个值，比如description IN ('surfing', 'reading')，那就意味着surfing OR reading。所以如果你想让所有的用户都加入到surfing AND reading中，我想我会用一个子选择来实现：

SELECT user_data.* FROM user_data
 WHERE user_data.User_ID IN (
   SELECT user_activities.user_ID FROM user_activities
     JOIN activities ON user_activities.activity_ID = activities.activity_ID
    WHERE activities.description IN ('surfing', 'reading')
   GROUP BY user_activities.activity_ID
     HAVING COUNT(user_activities.user_ID) = 2
 )

因此，子选择意味着：对每个出现“浏览”或“阅读”的用户id进行计数，如果计数等于2（意味着两者都匹配），则检索用户id。
外部选择只是从子集的每个用户中选择数据。
现在，我没有测试这个，所以可能会有所不同。可能还有更简单的方法。至少可以做些什么来简化这个查询，创建一个我前面提到的视图并从中进行选择。

关于mysql - 在多个联接表和相关表中查找特定结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14309963/