mysql - 遇到 mysql 关系问题？

Question

我需要两个表，一个使用三个表显示一对多关系，另一个表显示多对多关系。我设法完成了前 2 个查询，一个是只涉及一个表的查询，另一个是一对多涉及 2 个表的查询。

单表查询:

SELECT LastName, FirstName, City 
FROM Customer
WHERE City = 'Lutherville-Timonium'

涉及 2 个表的一对多:

SELECT b.BrandName, m.ModelName
FROM Brand b, Model m
WHERE b.BrandID = m.BrandID

这是我的代码:

    DROP DATABASE IF EXISTS FinalProject;

CREATE DATABASE IF NOT EXISTS FinalProject;

Use FinalProject;

CREATE TABLE IF NOT EXISTS Brand
(
 BrandID INT,
 BrandName VARCHAR(45),
 PRIMARY KEY(BrandID)
);

CREATE TABLE IF NOT EXISTS Model
(
 ModelName VARCHAR(45),
 BrandID INT,
 PRIMARY KEY(ModelName),
 FOREIGN KEY(BrandID) REFERENCES Brand(BrandID)
);

CREATE TABLE IF NOT EXISTS Platform
(
 PlatformID INT,
 Platform VARCHAR(45),
 PRIMARY KEY(PlatformID)
);

CREATE TABLE IF NOT EXISTS Computer
(
 ComputerID INT,
 ModelName VARCHAR(45),
 PlatformID INT,
 Processor VARCHAR(45),
 Memory VARCHAR(45),
 HardDrive VARCHAR(45),
 OperatingSystem VARCHAR(45),
 PRIMARY KEY(ComputerID),
 FOREIGN KEY(ModelName) REFERENCES Model(ModelName),
 FOREIGN KEY(PlatformID) REFERENCES Platform(PlatformID)
);

CREATE TABLE IF NOT EXISTS Customer
(
 CustomerID INT,
 LastName VARCHAR(45),
 FirstName VARCHAR(45),
 Address VARCHAR(45),
 Zip INT,
 City VARCHAR(45),
 State VARCHAR(2),
 PhoneNumber INT,
 Email VARCHAR(45),
 PRIMARY KEY(CustomerID)
);

CREATE TABLE IF NOT EXISTS Employee
(
 EmployeeID INT,
 Position VARCHAR(45),
 LastName VARCHAR(45),
 FirstName VARCHAR(45),
 Address VARCHAR(45),
 Zip INT,
 City VARCHAR(45),
 State VARCHAR(2),
 PhoneNumber INT,
 Email VARCHAR(45),
 PRIMARY KEY(EmployeeID)
);

CREATE TABLE IF NOT EXISTS Invoice
(
 InvoiceID INT,
 CustomerID INT,
 Total DECIMAL(19,4),
 EmployeeID INT,
 PRIMARY KEY(InvoiceID),
 FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID),
 FOREIGN KEY(EmployeeID) REFERENCES Employee(EmployeeID)
);

CREATE TABLE IF NOT EXISTS InvoiceItem
(
 InvoiceItemID INT,
 InvoiceID INT,
 ComputerID INT,
 PRIMARY KEY(InvoiceItemID),
 FOREIGN KEY(InvoiceID) REFERENCES Invoice(InvoiceID),
 FOREIGN KEY(ComputerID) REFERENCES Computer(ComputerID)
);

INSERT INTO Brand
 (BrandID, BrandName)
VALUES
 (101, 'Toshiba'),
 (102, 'ASUS'),
 (103, 'Dell'),
 (104, 'Samsung'),
 (105, 'MSI'),
 (106, 'Apple');

INSERT INTO Model
 (ModelName, BrandID)
VALUES 
 ('Satellite L50D-BBT2N22', 101),
 ('Transformer Book T100TAF', 102),
 ('Inspiron 3000', 103),
 ('ATIV One 7 Curved', 104),
 ('GE60 2PE APACHE PRO', 105),
 ('Summer 2014 13 in. MacBook Pro', 106),
 ('Wind Box DC111', 105),
 ('Alienware 17', 103),
 ('ATIV Book 9 Plus', 104);

INSERT INTO Platform
 (PlatformID, Platform)
VALUES
 (001, 'Laptop'),
 (002, 'Desktop');

INSERT INTO Computer
 ( ComputerID, ModelName, Processor, Memory, HardDrive, OperatingSystem, PlatformID)
VALUES
 (178045, 'Satellite L50D-BBT2N22', 'AMD Quad-Core A4-6210', '4GB', '1TB', 'Windows 8.1', 001),
 (178046, 'Transformer Book T100TAF', 'Intel Bay Trail-T Quad Core Z3735 1.33 GHz', '2GB', '32GB', 'Windows 8.1', 001),
 (178047, 'Inspiron 3000', 'Intel Core i5-4460', '8GB', '1TB', 'Windows 8.1', 002),
 (178048, 'ATIV One 7 Curved', 'Intel Core i5 Processor 5200U', '8GB', '1TB', 'Windows 8.1', 002),
 (178049, 'GE60 2PE APACHE PRO', 'Intel Core i7 Processor', '16GB', '1TB', 'Windows 8.1', 001),
 (178050, 'Summer 2014 13 in. MacBook Pro', '2.5GHz dual-core Intel Core i5', '4GB', '500GB', 'OS X Yosemite', 001),
 (178051, 'Wind Box DC111', 'Intel Celeron Dual Core', '4GB', ' 500GB', 'Windows 8.1', 002),
 (178052, 'Alienware 17', 'Intel Core i7', '8GB', '1TB', 'WIndows 8.1', 001),
 (178053, 'ATIV Book 9 Plus', 'Intel Core i5', '8GB', '128GB', 'Windows 8.1', 001);

INSERT INTO Customer
 (CustomerID, LastName, FirstName, Address, Zip, City, State, PhoneNumber, Email)
VALUES
 (14670, 'Franks', 'Robert', '2905 North Ave.', 21218, 'Baltimore', 'MD', 443-875-9090, 'r.franks@gmail.com'),
 (14671, 'Smith', 'Anthony', '28 Rhodes Pl.', 21093, 'Lutherville-Timonium', 'MD', 410-252-6542, 'asmith@me.com'),
 (14672, 'Anderson', 'Mary', '1784 Cranbrook Dr.', 21093, 'Lutherville-Timonium', 'MD', 410-687-8235, 'm.anderson@comcast.net'),
 (14673, 'Keith', 'Toby', '987 Rodeo Dr.', 21093, 'Lutherville-Timonium', 'MD', 443-267-0900, 'not_that_toby.keith@gmail.com'),
 (14674, 'Karwacki', 'Ryan', '16200 Yeoho Rd', 21152, 'Hereford', 'MD', 410-350-4456, 'r.karwacki44@gmail.com'),
 (14675, 'Yancey', 'Marcus', '165 Twilight Ct.', 21218, 'Baltimore', 'MD', 443-908-9087, 'm.yancey35@aol.com');

INSERT INTO Employee
 (EmployeeID, Position, LastName, FirstName, Address, Zip, City, State, PhoneNumber, Email)
VALUES
 (100001, 'CEO', 'Brocato', 'Christopher', '26 Rhodes Pl.', 21093, 'Lutherville-Timonium', 'MD', 410-812-0548, 'c.brocat0@prestigeww.com'),
 (100893, 'Location Manager', 'White', 'Walter', '687 Winning Dr.', 21117, 'Owings Mills', 'MD', 410-674-8890, 'w.white@prestigeww.com'),
 (100894, 'Computer Engineer', 'Pinkman', 'Jesse', '15 Pot Spring Cr.', 21093, 'Lutherville-Timonium', 'MD', 443-897-5467, 'j.pinkman@prestigeww.com'),
 (100895, 'Computer Engineer', 'Fring', 'Gustavo', '8796 Westmister Br.', 21117, 'Owings Mills', 'MD', 443-098-1111, 'g.fring@prestigeww.com'),
 (100896, 'Software Specialist', 'Boetticher', 'Gale', '7845 Sunny Ln.', 21093, 'Lutherville-Timonium', 'MD', 443-896-5674, 'g.boetticher@prestigeww.com'),
 (100087, 'Lawyer', 'Goodman', 'Saul', '7823 Goodman Ln.', 21093, 'Lutherville-Timonium', 'MD', 410-657-8900, 's.goodman@prestigeww.com');

INSERT INTO Invoice
 (InvoiceID, Total, CustomerID, EmployeeID)
VALUES
 (237871, 115.99, 14670, 100893),
 (237872, 87.89, 14671, 100894),
 (237873, 476.95, 14672, 100895),
 (237874, 314.95, 14673, 100896),
 (237875, 45.96, 14674, 100001),
 (237876, 79.84, 14675, 100087);

INSERT INTO InvoiceItem
 (InvoiceItemID, InvoiceID, ComputerID)
VALUES
 (1008, 237871, 178045),
 (1009, 237872, 178046),
 (1010, 237873, 178047),
 (1011, 237874, 178048),
 (1012, 237875, 178049),
 (1013, 237876, 178050); 

任何帮助将不胜感激谢谢。

Answer 1

这应该可以完成工作:

SELECT *
FROM computer c

LEFT OUTER JOIN model m ON m.ModelName = c.ModelName
LEFT OUTER JOIN brand b ON b.BrandID = m.BrandID

您可能希望根据您的要求更改返回的字段。祝项目顺利。

=== 更新 - 按照您的要求订购:

SELECT *
FROM brand b

LEFT OUTER JOIN model m ON m.BrandID = b.BrandID
LEFT OUTER JOIN computer c ON c.ModelName = m.ModelName

=== 更新 2 - 包含新的“平台”表

SELECT *
FROM brand b

LEFT OUTER JOIN model m ON m.BrandID = b.BrandID
LEFT OUTER JOIN computer c ON c.ModelName = m.ModelName
LEFT OUTER JOIN platform p ON p.platformID = c.PlatformID

=== 更新 3 - 以上涵盖了 2,3 和 4 表的一对多关系。要添加多对多关系，您可能需要查看计算机与发票的关系，因为一台计算机可以包含多个发票，而一张发票(在现实生活中)可以包含多个计算机。最初提出的数据结构不允许这样做。为 InvoiceItem 添加一个表可以实现这一点。

计算机和发票之间的关系是多对多 [计算机(许多)-(许多)发票] 这是通过在它们之间添加一个链接表来实现的:

Computer (1)-(Many) InvoiceItem (many)-(1) Invoice

=== 更新 4为此，从发票表中删除 computerID 并添加具有以下字段的新表 InvoiceItem - InvoiceItemID、InvoiceNumber、ComputerID。然后从数据库中检索发票项目的查询将是:

SELECT *
FROM Invoice i
LEFT OUTER JOIN InvoiceItem ii ON ii.InvoiceNumber = i.InvoiceNumber
LEFT OUTER JOIN Computer c ON c.computerID = ii.computerID

您可以扩展它以包括其他与计算机相关的表:

SELECT *
FROM Invoice i
LEFT OUTER JOIN InvoiceItem ii ON ii.InvoiceNumber = i.InvoiceNumber
LEFT OUTER JOIN Computer c ON c.computerID = ii.computerID
LEFT OUTER JOIN Model m ON m.ModelName = c.ModelName
LEFT OUTER JOIN Brand b ON b.BrandID = m.BrandID
LEFT OUTER JOIN platform p ON p.platformID = c.PlatformID

如果我真的很挑剔，我会放弃名称上的链接，而只使用 ID，如果模型名称稍有变化，查询将不再有效。使用纯 ID 可以防止这种情况。