我继续收到1146错误代码:1146。表'world.itemsintransactions'不存在,在我继续获取许多其他不同的错误代码之前,我最终修复这些错误代码只是将其转移到另一个错误代码上。请记住,这是我的第一个MySQL作业,在此作业之前从未使用过MySQL,因此,如果我错过了一些非常明显的内容,我们深表歉意!
这是我的代码...
Create DATABASE CS_Store;
USE CS_Store;
CREATE TABLE customers (
birth_day date,
first_name VARCHAR(20),
last_name VARCHAR(20),
c_id VARCHAR(20),
CONSTRAINT PK_Customers PRIMARY KEY (c_id));
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1993-07-11','Victor','Davis',1);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('2001-03-28','Katarina','Williams',2);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1965-12-11','David','Jones',3);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1980-10-10','Evelyn','Lee',4);
CREATE TABLE employees (
birth_day date,
first_name VARCHAR(20),
last_name VARCHAR(20),
e_id int,
CONSTRAINT PK_Employees PRIMARY KEY (e_id)
);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1983-09-02','David','Smith',1);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1990-07-23','Olivia','Brown',2);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1973-05-11','David','Johnson',3);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1999-11-21','Mia','Taylor',4);
CREATE TABLE transactions (
e_id int,
c_id int,
date date,
t_id int,
CONSTRAINT PK_transactions PRIMARY KEY (t_id),
FOREIGN KEY (e_id) REFERENCES employees(e_id),
FOREIGN KEY (c_id) REFERENCES customers(c_id)
);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,1,'2020-8-11',1);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (3,1,'2020-8-15',2);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,4,'2020-9-01',3);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (2,2,'2020-9-07',4);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (4,3,'2020-9-07',5);
CREATE TABLE items (
amount int,
price_for_each int,
name VARCHAR(20),
CONSTRAINT PK_items PRIMARY KEY (name)
);
INSERT INTO items (price_for_each, amount, name) VALUES (110,22,'2lof milk');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'6 cans of lemonade');
INSERT INTO items (price_for_each, amount, name) VALUES (150,20,'Pack of butter');
INSERT INTO items (price_for_each, amount, name) VALUES (450,13,'Roast chicken');
INSERT INTO items (price_for_each, amount, name) VALUES (99,30,'Pack of rice');
INSERT INTO items (price_for_each, amount, name) VALUES (20,50,'Banana');
INSERT INTO items (price_for_each, amount, name) VALUES (200,30,'3kg sugar');
INSERT INTO items (price_for_each, amount, name) VALUES (150,15,'Toast bread');
INSERT INTO items (price_for_each, amount, name) VALUES (150,18,'Earl Grey tea');
CREATE TABLE promotions (
discount int,
p_id int,
CONSTRAINT PK_promotions PRIMARY KEY (p_id)
);
INSERT INTO promotions (discount, p_id) VALUES (99,1);
INSERT INTO promotions (discount, p_id) VALUES (200,2);
INSERT INTO promotions (discount, p_id) VALUES (150,3);
INSERT INTO promotions (discount, p_id) VALUES (150,4);
CREATE TABLE itemsinpromotions (
name VARCHAR(20),
p_id int,
amount int,
FOREIGN KEY (name) REFERENCES items(name),
FOREIGN KEY (p_id) REFERENCES promotions(p_id)
);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('6 cans of lemonade',1,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Roast chicken',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Toast bread',3,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('2lof milk',4,2);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('Banana',4,3);
INSERT INTO itemsinpromotions (name, p_id, amount) VALUES ('3kg sugar',4,2);
CREATE TABLE itemsintransactions (
name VARCHAR(20),
t_id int,
amount int,
FOREIGN KEY (name) REFERENCES items(name),
FOREIGN KEY (t_id) REFERENCES transactions(t_id)
);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES('6 cans of lemonade',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',1,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',1,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',1,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',2,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',2,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',3,3);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',3,2);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',3,1);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',4,5);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',4,20);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',4,8);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('6 cans of lemonade',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Roast chicken',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of rice',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Pack of butter',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Toast bread',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('2lof milk',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Banana',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('3kg sugar',5,10);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES ('Earl Grey tea',5,10);
CREATE VIEW DavidSoldTo AS
SELECT birth_day, first_name, last_name
FROM customers
WHERE EXISTS (SELECT 1 FROM Transactions WHERE e_id=1)
ORDER BY birth_day
;
CREATE VIEW PeopleInShop AS
SELECT DISTINCT birth_day, first_name, last_name
FROM Employees
UNION
SELECT DISTINCT birth_day, first_name, last_name
FROM Customers
WHERE EXISTS (SELECT 1 FROM Transactions WHERE date=2020-9-07)
ORDER BY birth_day
;
CREATE VIEW ItemsLeft AS
SELECT amount AS amount_left, name
FROM Items
WHERE amount_left NOT IN (SELECT COUNT(t_id) FROM Transactions)
GROUP BY name
;
-- Not yet completed
CREATE VIEW PromotionItemsSatisfiedByTransactions AS
SELECT name, p_id, COUNT(t_id) AS number_of_times
FROM Promotions NATURAL JOIN Transactions
SELECT number_of_times
WHERE number_of_times >= 1;c_id
除了与架构名称相关的错误(由@ysth解释)外,你的代码还有另一个问题。考虑以下表声明:
create table customers (
birth_day date,
first_name varchar(20),
last_name varchar(20),
c_id varchar(20),
constraint pk_customers primary key (c_id)
);
create table transactions (
e_id int,
c_id int,
date date,
t_id int,
constraint pk_transactions primary key (t_id),
foreign key (e_id) references employees(e_id),
foreign key (c_id) references customers(c_id)
);
问题是它customers(c_id)
是数据类型varchar(20)
,而引用列transactions(c_id)
是int
。因此,外键约束创建失败,表table的创建也失败transactions
。缺少的表会在脚本的其余部分传播错误。
修复数据类型之一,使它们相同。我希望一些所谓的..._id
要int
。然后,整个脚本应该可以正常工作。