在SQL中，如何从关系数据库模式中检索信息？

I have a relational database schema looks like: 在SQL中，如何从关系数据库模式中检索信息？

我有一个关系数据库架构,如下所示:

The question is to 'find details of customers who bought products in 2009 and the products were manufactured by Honeywell in 1968'.

问题是“找到2009年购买产品的客户的详细信息,产品由霍尼韦尔于1968年制造”。

What I've done so far is:

到目前为止我所做的是:

SELECT * FROM Customer
WHERE CID IN
    (SELECT Customer_ID FROM Order
     WHERE Purchase_Date = 2009 
     AND EXISTS
        (SELECT * FROM Order_Items, Product, Manufacturer
         WHERE OID = OID AND Product_ID = PID AND MID = Manufacturer_ID AND
         Mnufacturer_Name = "Honeywell" AND Manufacturerd_Date = 1968));

I've been studying database for only about a month, so I guess it would look so complicate to expert people here.. Did I do this correctly ? Otherwise, please give any feedback where to fix..

我一直在研究数据库大约一个月,所以我想这里的专家们看起来很复杂..我这样做了吗?否则,请提供任何反馈,以解决..

Thanks in advance.

提前致谢。

1 个解决方案

#1

Typically when retrieving information from related tables in a relational database, you should JOIN the tables using a join type that suits the results you need to get from the query. Whether including not matched rows from the outer table or not. So, in your case you can do this:

通常,在从关系数据库中的相关表中检索信息时,应使用适合从查询中获取的结果的连接类型来加入表。是否包括外表中不匹配的行。所以,在你的情况下,你可以这样做:

SELECT DISTINCT c.*
FROM Customer c
INNER JOIN Order        o ON c.CID             = o.Customer_ID
INNER JOIN OrderItems  oi ON o.OID             = oi.OID
INNER JOIN Product      p ON oi.ProductID      = p.PID
INNER JOIN Manufacturer m ON p.Manufacturer_ID = m.MID
WHERE o.Purchase_Date = 2009 
  AND m.Mnufacturer_Name = "Honeywell"
  AND m.Manufacturerd_Date = 1968

However, in the query you posted:

但是,在您发布的查询中:

SELECT * FROM Customer
WHERE CID IN
    (SELECT Customer_ID FROM Order
     WHERE Purchase_Date = 2009 
     AND EXISTS
        (SELECT * FROM Order_Items, Product, Manufacturer
         WHERE OID = OID AND Product_ID = PID AND MID = Manufacturer_ID AND
         Mnufacturer_Name = "Honeywell" AND Manufacturerd_Date = 1968));

You are using the IN predicate and also joining the three tables Order_Items, Product and Manufacturer using the old join syntax .. FROM Order_Items, Product, Manufacturer WHERE OID = OID AND Product_ID = PID AND MID = Manufacturer_ID

您正在使用IN谓词,并使用旧的连接语法连接三个表Order_Items,Product和Manufacturer .. FROM Order_Items,Product,Manufacturer WHERE OID = OID AND Product_ID = PID AND MID = Manufacturer_ID

Your query might work. But if there are any NULL values for the Customer_ID then it won't return any results. But it will be easier to use JOIN instead. Here is a useful likes about JOIN:

您的查询可能有效。但是,如果Customer_ID有任何NULL值,则它不会返回任何结果。但是使用JOIN会更容易。这是一个关于JOIN的有用的喜欢:

A Visual Explanation of SQL Joins.

SQL连接的可视化解释。

Visual Representation of SQL Joins.

SQL连接的可视化表示。

#1

SELECT DISTINCT c.*
FROM Customer c
INNER JOIN Order        o ON c.CID             = o.Customer_ID
INNER JOIN OrderItems  oi ON o.OID             = oi.OID
INNER JOIN Product      p ON oi.ProductID      = p.PID
INNER JOIN Manufacturer m ON p.Manufacturer_ID = m.MID
WHERE o.Purchase_Date = 2009 
  AND m.Mnufacturer_Name = "Honeywell"
  AND m.Manufacturerd_Date = 1968