Соединение таблиц
Нередко возникает необходимость объединенить в одном запросе данные сразу из нескольких таблиц. Для объединения данных из разных таблиц можно применять разные способы. В данной статье рассмотрим не самый распространный, однако довольно простой способ, который представляет неявное соединение таблиц.
Допустим, у нас есть следующие таблицы, которые связаны между собой связями:
DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS customers; CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, company TEXT NOT NULL, items_count INTEGER DEFAULT 0, price INTEGER ); CREATE TABLE customers ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER NOT NULL, customer_id INTEGER NOT NULL, created_at TEXT NOT NULL, items_count INTEGER DEFAULT 1, price INTEGER NOT NULL, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE );
Здесь таблицы products и customers связаны с таблицей orders связью один ко многим. Таблица orders в виде внешних ключей product_id и customer_id содержит ссылки на столбцы id из соответственно таблиц products и customers. Также она хранит количество купленного товара (items_count) и и по какой цене он был куплен (price). И кроме того, таблицы также хранит в виде столбца created_at дату покупки.
Пусть эти таблицы будут содержать следующие данные:
INSERT INTO products (name, company, items_count, price) VALUES ('iPhone 13', 'Apple', 3, 76000), ('iPhone 12', 'Apple', 2, 51000), ('Galaxy S21', 'Samsung', 2, 56000), ('Galaxy S20', 'Samsung', 1, 41000), ('P40 Pro', 'Huawei', 5, 36000); INSERT INTO customers(name) VALUES ('Tom'), ('Bob'),('Sam'); INSERT INTO orders (product_id, customer_id, created_at, items_count, price) VALUES ( (SELECT id FROM products WHERE name='Galaxy S21'), (SELECT id FROM customers WHERE name='Tom'), '2021-11-30', 2, (SELECT price FROM products WHERE name='Galaxy S21') ), ( (SELECT id FROM products WHERE name='iPhone 12'), (SELECT id FROM customers WHERE name='Tom'), '2021-11-29', 1, (SELECT price FROM products WHERE name='iPhone 12') ), ( (SELECT id FROM products WHERE name='iPhone 12'), (SELECT id FROM customers WHERE name='Bob'), '2021-11-29', 1, (SELECT price FROM products WHERE name='iPhone 12') );
Теперь соединим две таблицы orders и customers:
SELECT * FROM orders, customers;
При такой выборке каждая строка из таблицы orders будет соединяться с каждой строкой из таблицы customers. То есть, получится перекрестное соединение. Например, в orders три строки, а в customers тоже три строки, значит мы получим 3 * 3 = 9 строк:
Но вряд ли это тот результат, который хотелось бы видеть. Тем более каждый заказ из orders связан с конкретным покупателем из customers, а не со всеми возможными покупателями.
Чтобы решить задачу, необходимо использовать выражение WHERE и фильтровать строки при условии, что поле customer_id из orders соответствует полю Id из customers:
SELECT * FROM orders, customers WHERE orders.customer_id = customers.id;
Теперь объединим данные по трем таблицам orders, customers и products. То есть получим все заказы и добавим информацию по клиенту и связанному товару:
SELECT customers.name, products.name, orders.created_at FROM orders, customers, products WHERE orders.customer_id = customers.id AND orders.product_id=products.id;
Так как здесь нужно соединить три таблицы, то применяются как минимум два условия. Ключевой таблицей остается orders, из которой извлекаются все заказы, а затем к ней подсоединяется данные по клиенту по условию orders.customer_id = customers.id и данные по товару по условию orders.product_id=products.id
В данном случае названия таблиц сильно увеличивают код, но мы его можем сократить за счет использования псевдонимов таблиц:
SELECT C.name, P.name, O.created_at FROM orders AS O, customers AS C, products AS P WHERE O.customer_id = C.id AND O.product_id=P.id;
Если необходимо при использовании псевдонима выбрать все столбцы из определенной таблицы, то можно использовать звездочку:
SELECT C.name, P.name, O.* FROM orders AS O, customers AS C, products AS P WHERE O.customer_id = C.id AND O.product_id=P.id;
Как связать 2 таблицы в sqlite
В прошлой теме было рассмотрено неявное соединение таблиц с помощью простой выборки путем сведения данных. Однако более распространенным подходом свдения данных из разных таблиц является применение оператора JOIN . SQLite поддерживает несколько вариантов оператора JOIN . В этой статье рассмотрим INNER JOIN . Общий формальный синтаксис этой версии оператора :
SELECT столбцы FROM таблица1 [INNER] JOIN таблица2 ON условие1 [[INNER] JOIN таблица3 ON условие2]
После оператора JOIN идет название второй таблицы, из которой надо добавить данные в выборку. Перед JOIN может использоваться необязательное ключевое слово INNER . Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.
Возьмем таблицы с данными из прошлой темы:
DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS customers; CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, company TEXT NOT NULL, items_count INTEGER DEFAULT 0, price INTEGER ); CREATE TABLE customers ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER NOT NULL, customer_id INTEGER NOT NULL, created_at TEXT NOT NULL, items_count INTEGER DEFAULT 1, price INTEGER NOT NULL, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE );
Пусть эти таблицы будут содержать следующие данные:
INSERT INTO products (name, company, items_count, price) VALUES ('iPhone 13', 'Apple', 3, 76000), ('iPhone 12', 'Apple', 2, 51000), ('Galaxy S21', 'Samsung', 2, 56000), ('Galaxy S20', 'Samsung', 1, 41000), ('P40 Pro', 'Huawei', 5, 36000); INSERT INTO customers(name) VALUES ('Tom'), ('Bob'),('Sam'); INSERT INTO orders (product_id, customer_id, created_at, items_count, price) VALUES ( (SELECT id FROM products WHERE name='Galaxy S21'), (SELECT id FROM customers WHERE name='Tom'), '2021-11-30', 2, (SELECT price FROM products WHERE name='Galaxy S21') ), ( (SELECT id FROM products WHERE name='iPhone 12'), (SELECT id FROM customers WHERE name='Tom'), '2021-11-29', 1, (SELECT price FROM products WHERE name='iPhone 12') ), ( (SELECT id FROM products WHERE name='iPhone 12'), (SELECT id FROM customers WHERE name='Bob'), '2021-11-29', 1, (SELECT price FROM products WHERE name='iPhone 12') );
Используя JOIN, выберем все заказы и добавим к ним информацию о товарах:
SELECT orders.created_at, orders.items_count, products.name FROM orders JOIN products ON products.id = orders.product_id;
Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например, «orders.items_count».
Используя псевдонимы для таблиц, можно сократить код:
SELECT O.created_at, O.items_count, P.name FROM orders AS O JOIN products AS P ON P.id = O.product_id;
Также можно присоединять данные сразу из нескольких таблиц. Например, добавим к заказу информацию о покупателе из таблицы customers:
SELECT orders.created_at, customers.name, products.name FROM orders JOIN products ON products.id = orders.product_id JOIN customers ON customers.id=orders.customer_id;
Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:
SELECT orders.created_at, customers.name, products.name FROM orders JOIN products ON products.id = orders.product_id JOIN customers ON customers.id=orders.customer_id WHERE products.price > 45000 ORDER BY customers.name;
Условия после ключевого слова ON могут быть более сложными по составу:
SELECT orders.created_at, customers.name, products.name FROM orders JOIN products ON products.id = orders.product_id AND products.company='Apple' JOIN customers ON customers.id=orders.customer_id ORDER BY customers.name;
В данном случае выбираем все заказы на товары, производителем которых является Apple.
Как правильно связать таблицы SQLite?
Создаю интернет-магазин. Есть таблица с размерами, где указана подробная информация о размере и есть таблица с футболками. У каждой футболки должны быть все размеры и их наличие.
Если просто для таблицы Футболки создать столбец размеры, то придется каждый раз создавать новую футболку и заного прописывать характеристики размера.
Образно должно получиться так:
- Вопрос задан более года назад
- 93 просмотра
1 комментарий
Простой 1 комментарий
Как правильно связать таблицы SQLite?
Создаю интернет-магазин. Есть таблица с размерами, где указана подробная информация о размере и есть таблица с футболками. У каждой футболки должны быть все размеры и их наличие. Если просто для таблицы Футболки создать столбец размеры, то придется каждый раз создавать новую футболку и заново прописывать характеристики размера. Не понимаю как сделать так, чтобы к одной конкретной футболке присваивались все размеры, к каждому из которых можно присваивать наличие. Образно это должно выглядить вот так:
| ID | Наименование | Цена | Размеры | Номер размера (Европа) | Номер размера (Китай) | Наличие |
|---|---|---|---|---|---|---|
| 1 | Футболка1 | 1000 | XXS | 40 | 30 | 10 |
| 1 | Футболка1 | 1000 | XS | 42 | 35 | 11 |
| 1 | Футболка1 | 1000 | S | 44 | 40 | 24 |
| 1 | Футболка1 | 1000 | M | 46 | 45 | 55 |
| 1 | Футболка1 | 1000 | L | 48 | 50 | 67 |
| 2 | Футболка2 | 1200 | XXS | 40 | 30 | 88 |
| 2 | Футболка2 | 1200 | XS | 42 | 35 | 1 |
| 2 | Футболка2 | 1200 | S | 44 | 40 | 0 |
| 2 | Футболка2 | 1200 | M | 46 | 45 | 0 |
| 2 | Футболка2 | 1200 | L | 48 | 50 | 15 |
Отслеживать
2,639 1 1 золотой знак 6 6 серебряных знаков 22 22 бронзовых знака
задан 9 дек 2022 в 18:24
rusyska5011 rusyska5011
3 2 2 бронзовых знака
Похоже, нужно отношение многие-ко-многим (many-to-many). Три таблицы: Товары, Размеры, ТоварыРазмеры.
9 дек 2022 в 19:08
@alexander-petrov, как тогда я смогу отмечать наличие для каждого размера?