Сегодня мы продолжаем наше путешествие в мир систем SQL и реляционных баз данных. В третьей части этой серии мы научимся работать с несколькими таблицами, которые связаны друг с другом. Сначала мы рассмотрим некоторые основные понятия, а затем начнем работать с запросами JOIN в SQL.
Вы также можете увидеть базы данных SQL в действии, ознакомившись с SQL-скриптами, приложениями и надстройками на Envato Market.
Наверстать
Вступление
При создании базы данных здравый смысл подсказывает, что мы используем отдельные таблицы для разных типов объектов. Вот некоторые примеры: клиенты, заказы, товары, сообщения и т. Д. Но мы также должны иметь отношения между этими таблицами. Например, клиенты делают заказы, а заказы содержат товары. Эти отношения должны быть представлены в базе данных. Кроме того, при получении данных с помощью SQL нам необходимо использовать определенные типы запросов JOIN, чтобы получить то, что нам нужно.
Существует несколько типов отношений с базой данных. Сегодня мы рассмотрим следующее:
- Отношения один к одному
- Отношения один ко многим и многие к одному
- Отношения многие ко многим
- Самостоятельные отношения
При выборе данных из нескольких таблиц со связями мы будем использовать запрос JOIN. Существует несколько типов JOIN, и мы собираемся узнать о следующем:
- Cross Joins
- Природные соединения
- Внутренние соединения
- Левые (наружные) соединения
- Правые (наружные) соединения
Мы также узнаем о предложении ON и предложении USING.
Отношения один к одному
Допустим, у вас есть таблица для клиентов:
Мы можем поместить информацию об адресе клиента в отдельную таблицу:
Теперь у нас есть связь между таблицей Customers и таблицей адресов. Если каждый адрес может принадлежать только одному клиенту, это отношение «один к одному». Имейте в виду, что такого рода отношения не очень распространены. Наша первоначальная таблица, в которой был указан адрес вместе с клиентом, могла бы работать в большинстве случаев нормально.
Обратите внимание, что теперь в таблице Customers есть поле с именем «address_id», которое ссылается на соответствующую запись в таблице Address. Это называется «внешним ключом» и используется для всех видов связей с базой данных. Мы рассмотрим эту тему позже в статье.
Мы можем визуализировать отношения между клиентом и адресными записями следующим образом:
Обратите внимание, что наличие отношения может быть необязательным, например, наличие записи клиента, которая не имеет связанной записи адреса.
Отношения один ко многим и многие к одному
Это наиболее часто используемый тип отношений. Рассмотрим сайт электронной коммерции со следующим:
- Клиенты могут сделать много заказов.
- Заказы могут содержать много предметов.
- Предметы могут иметь описания на многих языках.
В этих случаях нам необходимо создать отношения «один ко многим». Вот пример:
Каждый клиент может иметь ноль, один или несколько заказов. Но заказ может принадлежать только одному клиенту.
Отношения многие ко многим
В некоторых случаях вам может понадобиться несколько экземпляров по обе стороны отношений. Например, каждый заказ может содержать несколько позиций. И каждый элемент также может быть в нескольких заказах.
Для этих отношений нам нужно создать дополнительную таблицу:
Таблица Items_Orders имеет только одну цель — создать отношение «многие ко многим» между товарами и заказами.
Вот как мы можем визуализировать такие отношения:
Если вы хотите включить записи items_orders в график, это может выглядеть так:
Самостоятельные отношения
Это используется, когда таблица должна иметь отношения с самим собой. Например, допустим, у вас есть реферальная программа. Клиенты могут направлять других клиентов на ваш торговый сайт. Таблица может выглядеть так:
Клиенты 102 и 103 были направлены заказчиком 101.
На самом деле это также может быть похоже на отношения «один ко многим», поскольку один клиент может ссылаться на нескольких клиентов. Также это можно представить как древовидную структуру:
Один клиент может указывать ноль, одного или нескольких клиентов. Каждый клиент может быть направлен только одним клиентом, или ни одного вообще.
Если вы хотите создать собственную ссылку «многие ко многим», вам понадобится дополнительная таблица, как мы говорили в предыдущем разделе.
Иностранные ключи
Пока мы узнали только о некоторых понятиях. Теперь пришло время воплотить их в жизнь с помощью SQL. Для этой части нам нужно понять, что такое Foreign Keys.
В приведенных выше примерах отношений у нас всегда были поля «**** _ id», которые ссылались на столбец в другой таблице. В этом примере столбец customer_id в таблице Orders является столбцом внешнего ключа:
В такой базе данных, как MySQL, существует два способа создания столбцов внешних ключей:
Определение внешнего ключа явно
Давайте создадим простую таблицу клиентов:
|
1
2
3
4
|
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100)
);
|
Теперь таблица заказов, которая будет содержать внешний ключ:
|
1
2
3
4
5
6
|
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
|
Оба столбца (customer.customer_id и orders.customer_id) должны иметь одинаковую точную структуру данных. Если один INT, другой не должен быть BIGINT, например.
Обратите внимание, что в MySQL только движок InnoDB имеет полную поддержку внешних ключей. Но другие механизмы хранения по-прежнему позволяют указывать их без каких-либо ошибок. Столбец «Внешний ключ» также индексируется автоматически, если вы не укажете для него другой индекс.
Без явного заявления
Та же таблица заказов может быть создана без явного объявления столбца customer_id как внешнего ключа:
|
1
2
3
4
5
6
|
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
INDEX (customer_id)
);
|
При извлечении данных с помощью запроса JOIN вы все равно можете рассматривать этот столбец как внешний ключ, даже если ядро базы данных не знает об этом отношении.
|
1
2
|
SELECT * FROM orders
JOIN customers USING(customer_id)
|
Мы собираемся узнать о запросах JOIN далее в статье.
Визуализация отношений
В настоящее время моим любимым программным обеспечением для проектирования баз данных и визуализации отношений внешнего ключа является MySQL Workbench .
Разработав базу данных, вы можете экспортировать SQL и запустить его на своем сервере. Это очень удобно для больших и более сложных проектов баз данных.
JOIN Запросы
Для извлечения данных из базы данных, которая имеет отношения, нам часто нужно использовать запросы JOIN.
Прежде чем мы начнем, давайте создадим таблицы и некоторые примеры данных для работы.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
amount DOUBLE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO `customers` (`customer_id`, `customer_name`) VALUES
(1, ‘Adam’),
(2, ‘Andy’),
(3, ‘Joe’),
(4, ‘Sandy’);
INSERT INTO `orders` (`order_id`, `customer_id`, `amount`) VALUES
(1, 1, 19.99),
(2, 1, 35.15),
(3, 3, 17.56),
(4, 4, 12.34);
|
У нас 4 клиента. Один клиент имеет два заказа, два клиента имеют по одному заказу, а один клиент не имеет заказа. Теперь давайте посмотрим на различные типы запросов JOIN, которые мы можем выполнять в этих таблицах.
Перекрестное соединение
Это тип запроса JOIN по умолчанию, когда условие не указано.
Результатом является так называемое «декартово произведение» таблиц. Это означает, что каждая строка первой таблицы сопоставляется с каждой строкой второй таблицы. Поскольку в каждой таблице было 4 строки, мы получили 16 строк.
Вместо этого ключевое слово JOIN может быть заменено запятой.
Конечно, такой результат обычно бесполезен. Итак, давайте посмотрим другие типы соединения.
Natural Join
При таком типе запроса JOIN таблицы должны иметь совпадающее имя столбца. В нашем случае обе таблицы имеют столбец customer_id. Таким образом, MySQL будет объединять записи только тогда, когда значение этого столбца совпадает в двух записях.
Как вы можете видеть, столбец customer_id отображается только один раз, потому что ядро базы данных воспринимает это как общий столбец. Мы можем видеть два заказа, сделанные Адамом, и два других заказа Джо и Сэнди. Наконец мы получаем некоторую полезную информацию.
Внутреннее соединение
Когда указано условие соединения, выполняется внутреннее соединение. В этом случае было бы неплохо, чтобы поле customer_id совпадало в обеих таблицах. Результаты должны быть похожи на Natural Join.
Результаты одинаковы, за исключением небольшой разницы. Столбец customer_id повторяется дважды, по одному разу для каждой таблицы. Причина в том, что мы просто попросили базу данных сопоставить значения в этих двух столбцах. Но на самом деле не знают, что они представляют одну и ту же информацию.
Давайте добавим еще несколько условий к запросу.
На этот раз мы получили только заказы свыше 15 долларов.
ПОЛОЖЕНИЕ
Прежде чем перейти к другим типам соединения, нам нужно взглянуть на предложение ON. Это полезно для помещения условий JOIN в отдельное предложение.
Теперь мы можем отличить условие JOIN от условий предложения WHERE. Но есть и небольшая разница в функциональности. Мы увидим это в примерах ЛЕВОГО СОЕДИНЕНИЯ.
ИСПОЛЬЗОВАНИЕ
Предложение USING аналогично предложению ON, но оно короче. Если столбец имеет одинаковое имя в обеих таблицах, мы можем указать его здесь.
Фактически, это очень похоже на ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ, поэтому столбец соединения (customer_id) не повторяется дважды в результатах.
Left (Outer) Присоединиться
LEFT JOIN — это тип внешнего соединения. В этих запросах, если не найдено совпадений из второй таблицы, запись из первой таблицы по-прежнему отображается.
Хотя у Энди нет заказов, его запись все еще отображается. Значения под столбцами второй таблицы установлены в NULL.
Это также полезно для поиска записей, которые не имеют отношений. Например, мы можем искать клиентов, которые не разместили никаких заказов.
Все, что мы сделали, это искали значения NULL для order_id.
Также обратите внимание, что ключевое слово OUTER является необязательным. Вы можете просто использовать LEFT JOIN вместо LEFT OUTER JOIN.
Conditionals
Теперь давайте посмотрим на запрос с условием.
Так что случилось с Энди и Сэнди? LEFT JOIN должен был вернуть клиентов без соответствующих заказов. Проблема в том, что предложение WHERE блокирует эти результаты. Чтобы получить их, мы можем попытаться включить условие NULL.
У нас есть Энди, но нет Сэнди. Тем не менее это выглядит не так. Чтобы получить то, что мы хотим, нам нужно использовать предложение ON.
Теперь мы получили всех, и все заказы выше 15 долларов. Как я уже говорил ранее, предложение ON иногда имеет несколько другую функциональность, чем предложение WHERE. В таком внешнем соединении строки включаются, даже если они не соответствуют условиям предложения ON.
Право (Внешнее) Присоединиться
RIGHT OUTER JOIN работает точно так же, но порядок таблиц меняется на противоположный.
На этот раз у нас нет пустых результатов, потому что у каждого заказа есть соответствующая запись клиента. Мы можем изменить порядок таблиц и получить те же результаты, что и в LEFT OUTER JOIN.
Теперь у нас есть эти значения NULL, потому что таблица клиентов находится на правой стороне соединения.
Вывод
Спасибо, что прочитали статью. Надеюсь, вам понравилось! Пожалуйста, оставляйте свои комментарии и вопросы, и хорошего дня!
Не забудьте проверить SQL-скрипты, приложения и дополнения на Envato Market. Вы получите представление о том, что возможно с базами данных SQL, и вы можете найти идеальное решение, которое поможет вам в вашем текущем развивающемся проекте.
Подпишитесь на нас в Твиттере или подпишитесь на ленту Nettuts + RSS для получения лучших учебных материалов по веб-разработке.