此文件显示客户数据库和 CD 数据库。
客户是外键作为buyerIDs
。 一些客户/买家购买了一张特定CD的几份副本。
我在Notepad++中创建了该文件,将其导入phpMyAdmin,我尝试了查询部分以尝试获取示例语句:
Customer Gerald Bostick bought 3 copies of Thick as a Brick
我想出的查询是:
SELECT `customer`.`CustName`, `customer`.`CDPurchases`, `cd`.`Title`
FROM `customer`, `cd`
我得到的是表中每个客户购买每张 CD:
Joe Doe 12 Ascension
Suzy Creamcheese 3 Ascension
Jane Doe 1 Ascension
Gerald Bostick 3 Ascension
Lisa Simpson NULL Ascension
Joe Doe 12 The Velvet Rope
Suzy Creamcheese 3 The Velvet Rope
Jane Doe 1 The Velvet Rope
Gerald Bostick 3 The Velvet Rope
Lisa Simpson NULL The Velvet Rope
Joe Doe 12 The Pecan Tree
Suzy Creamcheese 3 The Pecan Tree
Jane Doe 1 The Pecan Tree
Gerald Bostick 3 The Pecan Tree
Lisa Simpson NULL The Pecan Tree
外键设置是否正确? 我把它从
CONSTRAINT FK_Buyer FOREIGN KEY FK_Buyer (BuyerId)
REFERENCES Customer (CustID)
自
CONSTRAINT FK_Buyer FOREIGN KEY (BuyerId)
REFERENCES Customer (CustID)
还是SET
语句或我的查询不正确?
我的编码是:
DROP DATABASE IF EXISTS Library;
CREATE DATABASE Library;
USE Library;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS CD;
CREATE TABLE Customer (
CustID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
CustName VARCHAR(20) NOT NULL,
CDPurchases INTEGER,
PRIMARY KEY (CustID)
);
CREATE TABLE CD (
CDID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Title VARCHAR(45) NOT NULL,
BuyerId INTEGER UNSIGNED,
Price FLOAT(6,2) UNSIGNED NOT NULL,
PRIMARY KEY (CDID),
CONSTRAINT FK_Buyer FOREIGN KEY (BuyerId)
REFERENCES Customer (CustID)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
INSERT INTO Customer VALUES (null, "Joe Doe", 12);
SET @joedoe := LAST_INSERT_ID();
INSERT INTO Customer VALUES (null, "Suzy Creamcheese", 3);
INSERT INTO Customer VALUES (null, "Jane Doe", 1);
SET @janedoe := LAST_INSERT_ID();
INSERT INTO Customer VALUES (null, "Gerald Bostick",3);
SET @geraldbostick := LAST_INSERT_ID();
INSERT INTO Customer VALUES (null, "Lisa Simpson", null);
INSERT INTO CD VALUES (null, "Thriller", @janedoe, 12.99);
INSERT INTO CD VALUES (null, "Grown and Sexy", null, 16.95);
INSERT INTO CD VALUES (null, "Ascension", null, 14.50);
INSERT INTO CD VALUES (null, "The Velvet Rope", null, 13.85);
INSERT INTO CD VALUES (null, "The Pecan Tree", null, 9.99);
INSERT INTO CD VALUES (null, "Condensate", null, 11.85);
INSERT INTO CD VALUES (null, "The Dana Owens Album", null, 9.95);
INSERT INTO CD VALUES (null, "Dustbowl", @joedoe, 17.95);
INSERT INTO CD VALUES (null, "Thick as a Brick", @geraldbostick, 10.95);
感谢您的任何帮助。
这是因为您的查询创建了交叉联接或笛卡尔积 (x * x)
SELECT customer.CustName, customer.CDPurchases, cd.Title
FROM customer, cd
WHERE customer.custid = cd.buyerid;
将两个表联接在一起时,数据库引擎不会假定联接条件 - 您必须显式声明该条件。存在外键约束的事实只会提供更好的引用完整性 - 它不会强制表连接。