我第一次尝试使用mySQL来完成一个家庭学习任务，在创建了图书、用户和借阅表(这是一个图书馆模型)之后，其中一个作业要求我将所有借阅图书的用户和每个用户借阅的所有图书结合起来。我试过了，但是它给了我前面提到的错误(可能是我对mySQL的掌握有问题)，我在网上找不到任何可以帮助我的东西(或者我能理解的，我必须承认)。请在下面找到我到现在为止用过的。
创建数据库和表"Books";
CREATE DATABASE library;
CREATE TABLE Books (
title VARCHAR(100) NOT NULL,
author_1 VARCHAR(30) NOT NULL,
author_2 VARCHAR(30), -- all these other "author" fields are for books with more than one author, but they can be NULL
author_3 VARCHAR(30),
author_4 VARCHAR(30),
author_5 VARCHAR(30),
author_6 VARCHAR(30),
author_7 VARCHAR(30),
author_8 VARCHAR(30),
author_9 VARCHAR(30),
author_10 VARCHAR(30),
ISBN BIGINT NOT NULL, -- an ISBN can be between 10 and 13 num long, so not enough for an INT
PRIMARY KEY(ISBN),
stock INT NOT NULL
);
INSERT INTO Books VALUES ("Call of Cthulhu: Fantasy Roleplaying in the Worlds of HP Lovecraft", "Sandy Petersen", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780933635586, 2); -- Since I included the possibility of having multiple authors, I'm forced to write NULL for the empty fields, as I don't know how else to manage this (as of the time of me writing this)
INSERT INTO Books VALUES ("Vampire: The Masquerade", "Phil Brucato", "Tom Dowd", "Mark Rein-Hagen", "Richard E. Dansky", NULL, NULL, NULL, NULL, NULL, NULL, 9781565042490, 4);
INSERT INTO Books VALUES ("IT", "Stephen King", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780670813025, 27);
INSERT INTO Books VALUES ("Elantris", "Brandon Sanderson", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780765311771, 12);
INSERT INTO Books VALUES ("Watchmen", "Alan Moore", "Dave Gibson", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781852860240, 15);
INSERT INTO Books VALUES ("Supergod", "Warren Ellis", "Garrie Gastonny", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781592911004, 6);
INSERT INTO Books VALUES ("Necronomicon", "H. R. Giger", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780905664064, 8);
INSERT INTO Books VALUES ("The Fantastic Art of Frank Frazetta", "Frank Frazetta", "Betty Ballantine", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780684144160, 1);
INSERT INTO Books VALUES ("Trigun", "Yasuhiro Nightow", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9783551767011, 2);
INSERT INTO Books VALUES ("Ghost in the Shell", "Masamune Shirow", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781569710814, 5);
COMMIT;
创建表"Users"
USE library;
CREATE TABLE Users (
user_id MEDIUMINT AUTO_INCREMENT NOT NULL, -- MEDIUMINT because I don't think a library is going to have more than 8388607 users but it might have more than 32767 (SMALLINT)
PRIMARY KEY(user_id),
First_Name VARCHAR(15) NOT NULL,
Last_Name VARCHAR(15) NOT NULL,
Contact_Number VARCHAR(32) NOT NULL, -- Longest telephone number possible should be at max 15 num long, then there are the +, the country code, the area code and the possibility of other char like () - and so on
City VARCHAR(85) NOT NULL, -- 85 is the longest name recorded for any place
Address_1 VARCHAR(50) NOT NULL, -- Address shouldn't be more than 35-40, but just in case
Address_2 VARCHAR(50), -- can be NULL, as it's not necessary to fill
County VARCHAR(30) NOT NULL, -- longest county name in the UK is "Cambridgeshire and Isle of Ely", a total of 30 char
Postcode CHAR(7) NOT NULL -- I chose a CHAR because postcodes a pretty consistent, being 5 to 7 char, so maybe it could save up memory?
);
INSERT INTO Users VALUES (4, "Bruce", "Wayne", 07893475629, "Gotham City", "1007, Mountain Drive", "Batcave", "Gotham", "NG8 2AE");
INSERT INTO Users VALUES (6, "Tony", "Stark", 09023853948, "Point Dume", "10880, Malibu Point", NULL, "Malibu", "MRVL616");
INSERT INTO Users VALUES (49, "Jessica", "Fletcher", 93829774, "Cabot Cove", "698, CAndlewood Lane", NULL, "Maine", "FN39F");
INSERT INTO Users VALUES (287, "Sherlock", "Holmes", 2984723987, "London", "221B, Baker Street", NULL, "Greater London", "OFN38");
INSERT INTO Users VALUES (2397, "Herman", "Munster", 2593528955, "Mockingbird Heights", "1313, Mockingbird Lane", NULL, "Los Angeles", "NF93FN");
INSERT INTO Users VALUES (29374, "Phileas", "Fogg", 234829384, "London", "7, Savile Row", "Burlington Gardens", "Greater London", "984NF");
INSERT INTO Users VALUES (37, "Spongebob", "Squarepants", 293829493, "Bikini Bottom", "124, Conch Street", NULL, "Pacific Ocean", "46BF9");
INSERT INTO Users VALUES (7, "Sweeny", "Todd", 0923492734, "London", "186, Fleet Street", NULL, "Greater London", "8DK92");
INSERT INTO Users VALUES (2, "Homer", "Simpson", 0234475923, "Springfield", "742, Evergreen Terrace", NULL, "Maine", "PE02SN");
INSERT INTO Users VALUES (73387, "Harry", "Potter", 02387426, "Little Whinging", "4, Privet Drive", "The Cupboard unda the Stairs", "Surrey", "FO9FH");
COMMIT;
创建表"Loaned":
使用图书馆;
CREATE TABLE Loaned (
user_id_loaned MEDIUMINT AUTO_INCREMENT NOT NULL,
ISBN_loaned BIGINT NOT NULL,
FOREIGN KEY (user_id_loaned) REFERENCES Users(user_id),
FOREIGN KEY (ISBN_loaned) REFERENCES Books(ISBN),
date_lent DATE NOT NULL,
due_date DATE NOT NULL
);
INSERT INTO Loaned VALUES (49, 9780684144160, "1991-03-29", "1991-05-29");
INSERT INTO Loaned VALUES (287, 9780933635586, "1827-08-17", "1827-10-17");
INSERT INTO Loaned VALUES (2397, 9780670813025, "2022-05-10", "2022-07-10");
INSERT INTO Loaned VALUES (37, 9780670813025, "2022-04-19", "2022-06-19");
INSERT INTO Loaned VALUES (2397, 9780905664064, "2022-03-21", "2022-05-21");
INSERT INTO Loaned VALUES (29374, 9781592911004, "2022-05-16", "2022-07-16");
COMMIT;
最后，我卡住的部分:
SELECT * FROM Users; -- All Users
SELECT * FROM Books; -- All Books
SELECT Loaned.user_id_loaned, Users.First_Name, Users.Last_Name, Books.title, Loaned.ISBN_Loaned FROM ((Users u INNER JOIN Loaned l ON u.user_id = l.user_id_loaned) INNER JOIN Books b ON b.ISBN = l.ISBN_loaned);
问题是，当你为一个表分配别名时，你应该在select语句中使用别名，而不是真正的表名。
SELECT l.user_id_loaned,
u.First_Name,
u.Last_Name,
b.title,
l.ISBN_Loaned
FROM Users u
INNER JOIN Loaned l ON u.user_id = l.user_id_loaned
INNER JOIN Books b ON b.ISBN = l.ISBN_loaned
https://dbfiddle.uk/?rdbms=mysql_8.0&小提琴= c4137f9c712934bf60074657f72701f4