Быстрый старт: Визуальное проектирование базы данных в MySQL Workbench
Цель данного поста — помочь начинающему разработчику быстро освоится и спроектировать простенькую базу с помощью инструмента для визуального проектирования баз данных MySQL Workbench от компании Oracle и получить её ER-модель и SQL-дамп.
Ну что же, меньше слов да больше смысла! Внешний вид окна программы, раздел «Моделирование данных» выглядит так:
Для того, чтобы открыть существующую модель надо нажать на ссылку: Open Existing EER Model, для создания новой модели – выбрать параметр: Create New EER Model, чтобы создать модель «сущность-связь» из существующей базы данных – нажать на параметр: Create EER Model From Existing Database, а для создания EER модели из SQL скрипта нужно выбрать: Create EER Model From SQL Script.
Для создания новой модели, воспользуемся ссылкой Create New EER Model, после нажатия на неё отобразится окно с параметрами:
Для начала необходимо создать таблицы, для этого нажмём на кнопку Add Table, появится следующая форма:
Сначала создадим таблицу users, которая будет хранить данные о пользователях информационной системы, в поле table Name впишем имя таблицы, в разделе формы Columns создадим поля таблицы:
— Первое поле id будет содержать уникальный номер пользователя, зададим ему свойства: Auto Increment, Not Null, Primary key и Unique, в разделе Data type выберем целочисленный тип integer.
— Второе поле fio, где будет хранится Ф.И.О. пользователя, установим полю свойства: Not Null, Primary key,, в разделе Data type выберем строковый тип VARCHAR и зададим количество символов в 255.
— Третье поле login, будет содержать логин пользователя, оно должно быть уникальным, как и поле id, поэтому установим ему свойство Unique и зададим количество символов в 255.
— Следующие поля: password содержащее пароль, e_mail содержащее адрес электронной почты и поле type содержащее тип пользователя будут без особых свойств, со строковым типом VARCHAR длинной в 255 символов, за исключением последнего поля type которому хватит с 45 символов.
После проделанных манипуляций форма с именем таблицы users будет выглядеть так:
На диаграмме появится таблица users c полями и индексами:
Аналогичным способом создадим таблицу settings с настройками доступа к базе данных ИС, содержащую поля id, host для указания имени хоста (адреса сервера), db – имени базы данных, user и password с именем пользователя и паролем, для установки ИС на удалённый сервер.
Далее по уже известному методу создадим таблицу shops которая будет хранить данные о магазинах в полях: id типа integer – ключевое, ненулевое, уникальное с автоинкрементом, поле name хранящее название магазина, поле address – его физический адрес, поле tel – телефон магазина, site – интернет сайт магазина и поле email с электронным адресом магазина.
Затем создадим таблицу products хранящую данные о продукции магазина в полях: id типа integer – ключевое, ненулевое, уникальное с автоинкрементом, поле name хранящее название магазина, ключевое, ненулевое поле целочисленного типа shop_id хранящее номер магазина, поле type_id с информацией о номере товара из таблицы видов продукции. Поле brand – брэнд изготовителя длинной в 255 символов, поле model – с моделью товара, поле data – с данными и характеристиками товара типа Tinytext, поле img с полным адресом до изображения товара длинной в 255 символов, и поля price с ценой товара и warranty с информацией о сроках гарантии на товар длинной в 45 символов.
Созданные нами таблицы settings, shops и products выглядят следующим образом:
Далее нам понадобится таблица хранящая тип продуктов product_type, она состоит из уникального, ненулевого, ключевого поля id с автоинкрементом целочисленного типа, и уникального поля name длинной в 255 символов, которое содержит название вида продуктов.
Вид таблицы таков:
Последние две таблицы это orders и deliveries, первая содержит информацию о заказах клиентов, а последняя данные о доставке продукции.
Поля таблицы orders: id ключевое, ненулевое, уникальное поле целочисленного типа с автоинкрементом, поле shop_id содержащее номер магазина – ключевое, ненулевое целочисленного типа, поле product_id хранящее номер продукта – ключевое, ненулевое целочисленного типа, поле fio с номером пользователя совершившего заказ — ключевое, ненулевое целочисленного типа, поле date c датой заказа – типа DATE, поле quantity с количеством заказанных товаров – целочисленного типа, поле tel с номером телефона заказчика – строкового типа длинной в 255 символов и поле confirm содержащее информацию о подтверждении заказа – логического типа.
Поля таблицы deliveries: order_id с номером заказа — ключевое, ненулевое, уникальное поле целочисленного типа с автоинкрементом, поле поле fio с номером пользователя совершившего заказ — ключевое, ненулевое целочисленного типа, поле address хранящее адрес доставки товара указанный клиентом – строкового типа длинной в 255 символов, поле time хранящее желаемое время доставки товара – строкового типа длинной в 255 символов, поле date с датой совершения заказа клиентом – типа DATE и поле логического типа confirm хранящее информацию о доставке товара.
Таблицы orders и deliveries выглядят так:
Связи таблиц
Мы создали базу данных, состоящую из семи таблиц, теперь необходимо связать таблицы, мы уже создали ключевые поля целочисленного типа, они и станут основой для связывания.
Для того чтобы связать две таблицы к примеру products и product_type, необходимо дважды щёлкнуть левой кнопкой мыши на диаграмму с таблицей products и выбрать вкладку Foreign keys (внешние ключи), далее в поле Foreign key name ввести уникальное имя внешнего ключа, дважды щёлкнуть по вкладке Referenced table и выбрать таблицу product_type, затем в форме расположенной правее выбрать ссылающееся поле type_id и выбрать в всплывающем списке поле id.
Таким образом, оба поля таблицы оказываются связанны, затем нужно задать тип отношений связи между таблицами, откроем окно, кликнув на появившуюся связь между таблицами, и выберем вкладку Foreign Key и в разделе Cardinality выберем тип связи один ко многим, и закроем окно. На диаграмме отобразится связь таблиц:
Аналогичным образом связываем все ключевые поля в таблицах, чтобы они были логически взаимосвязаны, далее нам необходимо убедится, что спроектированная база данных соответствует третьей нормальной форме.
Нормальная форма — свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, которая потенциально может привести к логически ошибочным результатам выборки или изменения данных. Нормальная форма определяется как совокупность требований, которым должно удовлетворять отношение.
В реляционной модели отношение всегда находится в первой нормальной форме по определению понятия отношение. Что же касается различных таблиц, то они могут не быть правильными представлениями отношений и, соответственно, могут не находиться в первой нормальной форме. Переменная отношения находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и каждый не ключевой атрибут неприводимо (функционально полно) зависит от её потенциального ключа. База данных будет находиться в третьей нормальной форме, если она приведена ко второй нормальной форме и каждый не ключевой столбец независим друг от друга.
Таким образом наша база находится в третьей нормальной форме, т.к. каждый не ключевой столбец независим друг от друга. Это наглядно видно на диаграмме нашей базы данных:
Большинство таблиц находятся в отношении один-ко-многим, за исключением таблиц deliveries и orders находящихся в отношении одни-к-одному, т.к. доставлен, может быть только одни заказ, т.е. у одного заказа только одна доставка. Остальные связи наглядно указаны выше.
Теперь загрузим нашу базу данных на сервер. Для этого создадим новое подключение к базе данных, щёлкнув по ссылке New connection в стартовом окне программы:
Затем заполним поля в открывшимся окне:
Укажем имя соединения в поле Connection Name, выберем метод соединения в списке Connection Method, зададим имя хоста и порт во вкладке Parameters, укажем имя пользователя и пароль если он есть и нажмём на кнопку ОК. Затем откроем вкладку EER Diagram, в панели выберем пункт Database и нажмём на параметр Forward Engineer:
После того как появится окно, нажимаем на кнопку “Next”, выбираем параметр Export MySQL Table Objects и нажимаем на кнопку “Next”:
После нажатия кнопки появится вкладка с SQL кодом, можно сохранить его нажав кнопку “Save to file” если это необходимо, а затем нажать на кнопку “Next”. Появится окно с параметрами соединения:
Проверяем, верны ли параметры подключения и нажимаем на кнопку “Execute”, если в SQL коде не содержится ошибок, то после исполнения кода мы увидим окно со списком таблиц, иначе выведется сообщение об ошибке. Теперь наша база загружена на сервер.
Благодарю за внимание, скачать саму программу можно здесь.
по совету одного из пользователей я приведу небольшое пояснение о том как изменить вид связей и таблиц, для этого необходимо выбрать следующий параметр в разделе меню Relationship Notation:
После этого связи таблиц примут вид:
Также есть возможность изменить вид таблиц, для этого нужно поставить галочку в вышеуказанном разделе меню и в нижеследующем Object Notation:
Name already in use
MySQL_examples / Lab4-Part2.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Практическое задание №4 по дисциплине «Основы построения баз данных», ВШЭКН ЮУрГУ, 2020/2021 учебный год
В ходе выполнения практического задания 4 необходимо опираться на практическое задание 2, в котором вы создавали базу данных Деканат, включающую в себя несколько таблиц.
Задание 1. Создание базы данных «Деканат» с использованием языка SQL
Перед началом работы создайте файл, в который будут записываться все ваши команды, которые вы будете вводить в течение выполнения данного практического задания. Этот файл необходимо будет отправить для проверки выполнения практического задания 4. Имя файла должно быть в формате «L4-XXXFamilia.log», где XXX — номер вашей группы, после которого следует ваша фамилия латинскими буквами. Программа автоматически создаст файл с указанным именем. Для того чтобы начать запись в файл, необходимо ввести следующую команду (укажите тот путь, по которому вы хотите сохранить этот файл).
Для того чтобы остановить запись в файл, используйте команду
Используйте команду SELECT NOW(); для вывода на экран текущего времени.
Создайте новую базу данных DekanatXX, где XX – номер варианта студента. Для создания базы данных используйте выражение CREATE SCHEMA или CREATE DATABASE. Переключитесь на нее с помощью команды USE.
Создайте новую таблицу Students, которая будет содержать данные студентов. Определите следующие названия полей и их типы данных:
Обратите внимание на поля, значение которых не может быть NULL, а также на ключевое поле. В поле Stipend (стипендия) необходимо добавить значение по умолчанию ‘Нет’.
Создайте новую таблицу Courses, которая будет содержать дисциплины. Определите следующие названия полей и их типы данных:
Создайте новую таблицу Lecturers, которая будет содержать данные преподавателей. Определите следующие названия полей и их типы данных:
Вместо того, чтобы написать новую команду для создания таблицы Lecturers, вы можете использовать команду для создания таблицы Lecturers из первой части четвертого практического задания. В этом случае ваша таблица будет иметь поле Course (Дисциплина), который можно удалить, а вместо него добавить новое поле CourseID (КодДисциплины), используя оператор ALTER:
Если вы захотите поменять местами некоторые столбцы, то можете воспользоваться следующей командой
Создайте новую таблицу Marks, которая будет содержать оценки. Определите следующие названия полей и их типы данных:
Обратите внимание, в этой таблице первичным ключом будет не одно, а два поля, поскольку значения в каждом отдельном поле могут повторяться, но для каждого студента по одной дисциплине может быть только одна оценка. Добиться этого можно, указав при создании таблицы выражение PRIMARY KEY(StudentID, CourseID).
Проверьте структуру созданных таблиц с помощью выражения DESCRIBE.
Установите связи между таблицами, задайте каскадное обновление и каскадное удаление связанных записей. В MS Access вы использовали графический интерфейс для редактирования схемы данных. В MySQL для этой цели служит оператор ALTER, который позволяет установить связи между таблицами путем создания правил целостности FOREIGN KEY (внешний ключ). Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы. Общий синтаксис установки внешнего ключа на уровне таблицы:
Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут необязательные выражения ON DELETE и ON UPDATE, которые задают действие при удалении и обновлении строки из главной таблицы соответственно. При установлении каскадного обновления и каскадного удаления используется выражение CASCADE. Внешние ключи можно указывать и при создании таблиц. Подробнее прочесть про FOREIGN KEY можно в официальной документации по ссылке: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html. В вашей базе данных необходимо установить следующие связи:
Проверьте созданные внешние ключи, используя команду SHOW CREATE TABLE table_name
Заполните таблицы аналогично тому, как было указано во второй практической работе, используя оператор INSERT. Обратите внимание, что в таблице Lecturers (Преподаватели) должно быть 8 записей (строк), в таблице Students (Студенты) должно быть 20 записей (строк), в таблице Courses (Дисциплины) — 4 записи, в таблице Marks (Оценки) должны быть оценки для каждого студента по каждой дисциплине — всего 80 записей. В таблице Students в поле GroupNumber должно быть две различных группы. При заполнении таблиц вы можете просмотреть имена столбцов в таблице и их особенности с помощью выражения DESCRIBE.
Проверьте внесенные данные в таблицы с помощью команды
Если какие-то данные внесены неправильно, можете изменить их с помощью оператора UPDATE.
Задание 2. Составление запросов
Создайте запросы на информацию о студентах с заданным условием из одной таблицы. Получите информацию о студентах определенной группы с помощью запроса
Самостоятельно составьте следующие запросы:
- вся информация о студентах, получающих стипендию;
- фамилия, имя, отчество, дата рождения, стаж профессоров.
Теперь вам предстоит создавать запросы, которые используют информацию из нескольких таблиц. Для этого вам потребуется использовать выражение JOIN. Для примера получим информацию о дисциплинах, которые преподают доценты. Названия дисциплин хранятся в таблице Courses, а должности преподавателей — в таблице Lecturers. Обе таблицы содержат поле CourseID (КодДисциплины), по которому можно провести их соединение:
Выражение имя_таблицы.имя_столбца позволяет избежать двусмысленности, если разные таблицы содержат столбцы с одинаковыми именами. Для того чтобы избежать частого написания длинных названий используются подстановочные имена (алиасы), которые можно задать при помощи выражения AS:
Выражение AS можно опустить и написать такой запрос, аналогичный двум предыдущим:
Теперь получим информацию об оценках студентов заданной группы по заданной дисциплине. Для этого нам понадобиться соединение трех таблиц — Students, Marks и Courses.
Самостоятельно составьте запрос на получение оценок по всем дисцилинам для конкретного студента (указывается его фамилия).
Составьте запросы, использующие функции агрегирования (в нижеприведенных примерах замените «номер_группы» и другие выражения на свои значения соответствующих полей). Более подробную информацию об использовании функций агрегирования можете найти в лекционном материале. В данной работе вы будете использовать функцию AVG(), которая возвращает среднее значение. Для группировки результатов выборки и применение функции к выборкам, соответствующим каждой группе, применяется выражение GROUP BY. Условия выборки при использовании функций агрегирования устанавливают с помощью выражения HAVING. Например, запрос, в результате которого создастся выборка, отражающая средний балл по дисциплинам по всем студентам в группах:
Запрос, в результате которого создастся выборка, отражающая средний балл по дисциплинам в определенной группе:
В некоторых случаях может потребоваться использование подзапросов для решения поставленной задачи. Запрос, в результате которого создастся выборка, отражающая средний балл по дисциплинам по всем студентам и по каждой группе
В вышеприведенном примере использованы алиасы для подзапросов, что фактически означает создание временных таблиц с новыми именами.
Увеличьте стаж работы преподавателей на M лет, при условии, что он составляет меньше N лет, используя оператор UPDATE. Вместо M и N вставьте свои числа.
Самостоятельно составьте команду, которая увеличит стаж работы доцентов на некоторую величину.
Удалите студентов из таблицы Students. Так как удаление является чувствительной операцией, мы должны быть уверены, что удаляем именно того студента, которого должны, поэтому в условии необходимо указать полное ФИО и номер группы удаляемого студента.
Проверьте, что студент удален (таблица Students), а также, что его оценки также удалены из-за каскадного удаления (таблица Marks). Используйте для этой цели оператор SELECT.
Создайте новую таблицу, в которой будет информация об отличниках. Сначала сформируйте запрос на выборку отличников:
Теперь создайте новую таблицу на основе этого запроса:
Используйте команду SELECT NOW(); для вывода на экран текущего времени, завершите запись команд в файл «L4-P2-XXXФамилия.log», который необходимо отправить на проверку (в элементе курса «Сдать на проверку Практическое задание 4»).
Задание 3. Знакомство с MySQL Workbench
Вы уже познакомились с тем, как работать с СУБД MySQL, используя консоль. Теперь вам предстоит познакомиться с программой MySQL Workbench, которая представляет собой официальный инструмент с графическим пользовательским интерфейсом для работы с сервером и базами данных MySQL. Подробное справочное руководство по работе с MySQL Workbench доступно по ссылке: https://dev.mysql.com/doc/workbench/en/.
- Откройте программу MySQL Workbench 8.0, которая должна находиться в списке программ группы MySQL, который был создан во время установки MySQL. Так выглядит окно программы после запуска:
MySQL Workbench позволяет создавать соединения с серверами баз данных и управлять ими. Помимо возможности настройки параметров подключения, MySQL Workbench предоставляет возможность выполнять SQL-запросы по подключениям к базе данных с помощью встроенного редактора SQL. Также важной функциональной областью MySQL Workbench является возможность моделирования данных, поскольку он позволяет графически создавать схемы баз данных, осуществлять обратную и прямую связь между схемой и базой данных, а также редактировать все аспекты базы данных с помощью комплексного редактора таблиц.
- Двойным щелчком нажмите на Local instance MySQL80 — откроется редактор SQL-запросов. Теперь вы можете писать и запускать команды SQL, используя интерфейс Workbench вместо консоли.
- Откройте вкладку для создания моделей баз данных. По умолчанию вы можете открыть MySQL model (sakila_full.mwb).
Во вкладке «EER Diagram» вы видите интерфейс для создания схемы данных, похожий на соответствующий инструмент MS Access, который вы использовали для создания схемы базы данных «Деканат» во второй практической работе. Последовательность этапов (создание структуры таблиц, связей и т.п.) также похожа на то, что вы делали во второй практической работе в программе MS Access. MySQL Workbench позволяет сначала разработать схему баз данных, чтобы по ней далее создать уже саму базу данных.
- Создайте графическую схему базы данных «Деканат». Так как вы уже создали базу данных с помощью консоли, вы можете пойти обратным путем — на основе существующей базы данных создадите диаграмму. Для этого закройте вкладки модели sakila_full и выберите опцию «Create EER Model from Database» на начальном окне моделей MySQL, как показано на рисунке.
Вы попадете в режим Reverse Engineer Database, который позволяет создать диаграмму на основе существующей базы данных. Он предполагает несколько шагов. Нажмите Next (Далее).
На этапе «Select Schemas» выберите вашу базу данных DekanatXX.
Завершите создание схемы (Finish).
Посмотрите вашу схему базы данных, проверьте, все ли соответствует вашим ожиданиям. Попробуйте навести курсор на различные элементы — таблицы, связи. Исследуйте возможности графического отображения схемы базы данных.
Соединение таблиц
Нередко возникает необходимость в одном запросе получить данные сразу из нескольких таблиц. Для сведения данных из разных таблиц мы можем использовать разные способы. В данной статье рассмотрим не самый распространный, однако довольно простой способ, который представляет неявное соединение таблиц.
Допустим, у нас есть следующие таблицы, которые связаны между собой связями:
Здесь таблицы Products и Customers связаны с таблицей Orders связью один ко многим. Таблица Orders в виде внешних ключей ProductId и CustomerId содержит ссылки на столбцы Id из соответственно таблиц Products и Customers. Также она хранит количество купленного товара (ProductCount) и и по какой цене он был куплен (Price). И кроме того, таблицы также хранит в виде столбца CreatedAt дату покупки.
Пусть эти таблицы будут содержать следующие данные:
Теперь соединим две таблицы Orders и Customers:
При такой выборке каждая строка из таблицы Orders будет соединяться с каждой строкой из таблицы Customers. То есть, получится перекрестное соединение. Например, в Orders три строки, а в Customers то же три строки, значит мы получим 3 * 3 = 9 строк:
Но вряд ли это тот результат, который хотелось бы видеть. Тем более каждый заказ из Orders связан с конкретным покупателем из Customers, а не со всеми возможными покупателями.
Чтобы решить задачу, необходимо использовать выражение WHERE и фильтровать строки при условии, что поле CustomerId из Orders соответствует полю Id из Customers:
Теперь объединим данные по трем таблицам Orders, Customers и Proucts. То есть получим все заказы и добавим информацию по клиенту и связанному товару:
Так как здесь нужно соединить три таблицы, то применяются как минимум два условия. Ключевой таблицей остается Orders, из которой извлекаются все заказы, а затем к ней подсоединяется данные по клиенту по условию Orders.CustomerId = Customers.Id и данные по товару по условию Orders.ProductId=Products.Id
В данном случае названия таблиц сильно увеличивают код, но мы его можем сократить за счет использования псевдонимов таблиц:
Если необходимо при использовании псевдонима выбрать все столбцы из определенной таблицы, то можно использовать звездочку:
jtest.ru
Сегодня мы продолжаем наше путешествие в мире SQL и реляционных систем управления базами данных. В этой части мы научимся работать с несколькими таблицами связанными между собой. Сначала мы познакомимся с базовыми концепциями, а потом начнем работать с запросами JOIN в SQL.
Предыдущие статьи
Вступление
При проектировании базы данных, здравый смысл подсказывает нам, что мы должны использовать различные таблицы для разных данных. Пример: клиенты, заказы, записи, сообщения и т.д. Так же мы должны иметь взаимосвязи между этими таблицами. Например, клиент имеет заказы, а у заказа есть позиции (товары). Эти взаимосвязи должны быть отражены в базе данных. А также, когда мы получаем данные с помощью SQL, мы должны использовать определенные типы запросов JOIN, чтобы получить нужный результат.
Вот несколько типов отношений в базе данных. Сегодня мы рассмотрим следующие:
- Отношения один к одному
- Один ко многим и многие к одному
- Многие ко многим
- Связь с самим собой
Когда данные выбираются из нескольких связанных таблиц, мы будем использовать запрос JOIN. Есть несколько типов присоединения, мы познакомимся с этими:
- Cross Joins (Перекрестное соединение)
- Natural Joins (Естественное соединений)
- Inner Joins (Внутреннее соединений)
- Left (Outer) Joins (Левое (внешнее) соединение)
- Right (Outer) Joins (Правое (внешнее) соединение)
Также мы изучим предложения ON и USING.
Связь один к одному
Допустим есть таблица покупателей (customers):
Мы можем расположить информацию о адресе покупателя в другой таблице:
Теперь у нас есть связь между таблицами покупателей (Customers) и адресами (Addresses). Если каждый адрес может принадлежать только одному покупателю, то такая связь называется «Один к одному». Имейте ввиду, что такой тип отношений не очень распространен. Наша первоначальная таблица, в которой информация о покупателе и его адресе хранилась вместе, в большинстве случаев работает нормально.
Обратите внимание, что теперь поле с названием «address_id», в таблице покупателей, ссылается на соответствующую запись в таблице адресов. Оно называется внешним ключом (Foreign Key) и используется во всех видах связей в базе. Мы рассмотрим этот вопрос позже в этой статье.
Вот так можно отобразить отношения между покупателями и адресами:
Обратите внимание, что существование данных отношений не обязательно, например, может существовать запись о покупателе без связанной записи о его адресе.
Связь один ко многим и многие к одному
Этот тип отношений наиболее часто встречающийся. Рассмотрим такой сайт интернет магазина:
- У покупателей может быть несколько заказов.
- Заказ может содержать несколько товаров.
- Товары могут иметь описание на нескольких языках.
В этих случаях нам потребуется создать связь «Один ко многим». Пример:
Каждый покупатель может иметь 0 или более заказов. Но каждый заказ может принадлежать только одному покупателю.
Связь многие ко многим
В некоторых случаях требуется многочисленные связи по обе стороны отношений. Например, каждый заказ может содержать множество товаров. И каждый товар может присутствовать во многих заказах.
Для такой связи нам потребуется создать дополнительную таблицу:
Назначение таблицы «Items_Orders» только одно — создать связь «Многие ко многим» между товарами и заказами.
Так можно представить этот тип отношений:
Если добавить записи items_orders к диаграмме, то она будет выглядеть так:
Связь с собой
Такой тип используется когда у таблицы должны быть связь с собой. Допустим у Вас есть реферальная программа. Покупатели могут ссылаться на других покупателей на вашем сайте интернет магазина. Таблица может выглядеть так:
Покупатели 102 и 103 ссылаются на покупателя 101.
Этот тип похож на связь «Один ко многим», поскольку один покупатель может ссылаться на несколько покупателей. Это можно представить как древовидную структуру:
Один покупатель может ссылаться на одного покупателя, на нескольких покупателей, или вообще не ссылаться ни на одного.
Если Вы хотите создать связь внутри таблицы «многие ко многим», то потребуется создать дополнительную таблицу, такую же как и в предыдущей части.
Внешние ключи
Пока что мы говорили только о базовых вещах. Пришло время применить полученные знания на практике, используя SQL. В данной часть нам нужно понять, что из себя представляют внешние ключи.
В отношениях, обсуждаемых выше, у нас всегда было поле вида «****_id», которое ссылалось столбец в другой таблице. В нашем примере столбец customer_id, в таблице Orders, является внешним ключом:
В таких базах как MySQL есть два способа создания внешних ключей:
Задать внешний ключ явно
Создадим простую таблицу с покупателями:
Теперь создадим таблицу заказов, которая будет содержать вторичный ключ:
Оба столбца (customers.customer_id и orders.customer_id) должны быть одного типа. Если у первого тип INT, то второй не должен быть типа BIGINT, например.
Пожалуйста, помните, что в MySQL полностью поддерживает внешние ключи только подсистема InnoDB. Другие подсистемы хранения данных позволяют определять внешние ключи без каких либо ошибок. Столбцы с внешними ключами индексируются автоматически, если явно не задать другой индекс.
Без явного объявления
Некоторые таблицы заказов могут быть созданы без явного определения внешнего ключа:
Когда данные получают запросом JOIN, Вы можете использовать столбец как внешний ключ, хотя база данных не знает о этих связях.
Мы подошли к изучению запросов JOIN, которые обсудим далее в статье.
Отображение связей
В данный момент, моей любимой программой для проектирования баз данных и отображения связей является MySQL Workbench.
После того как Вы спроектировали базу данных, ее можно экспортировать в SQL и выполнить на сервере. Это очень удобно при создании больших и сложных баз данных.
Запросы JOIN
Чтобы получить связанные данные из базы данных следует использовать запросы JOIN.
Прежде чем мы начнем, давайте создадим для работы тестовые таблицы и данные.
У нас есть 4 покупателя. У одного из них два заказа, у двоих по одному заказу, и у одного вообще нет заказов. Теперь давайте посмотрим какие виды запросов JOIN мы можем выполнять с этими таблицами.
Cross Join (Перекрестное объединение)
Это вид JOIN запроса по-умолчанию, если не определено условие.
Результатом будет, так называемое, «Декартово объединение» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Т.к. в каждой таблице по 4 строки, мы получили в результате 16 строк.
Ключевое слово JOIN можно заменить на запятую, в этом случае.
Конечно такой результат почти бесполезен. Давайте взглянем на другие виды объединений.
Natural Join (Естественное объединение)
При таком виде запроса JOIN таблицы должны иметь совпадающие, по имени, столбцы. В нашем случае в обеих таблицах должен присутствовать столбец customer_id. MySQL объединит записи только в случае совпадения значений в этих столбцах.
Как Вы можете видеть, в этот раз столбец customer_id отображаются только один раз, потому что движок базы рассматривает этот столбец как общий. Мы видим два заказа Adam’а, и другие два заказа Joe и Sandy. Наконец мы получили некоторую полезную информацию.
Inner Join (Внутреннее объединение)
Если условие объединения не указан, то выполняется внутреннее объединение. В этом случае хорошей идеей будет наличие совпадений по полю customer_id в обеих таблицах. Результат должен быть аналогичен естественному объединению.
Результат почти такой же. Столбец customer_id повторяется два раза, по разу для каждой таблицы. Объясняется это тем, что мы попросили базу сравнить значение по двум столбцам. При этом не знаю, что возвращают одну и туже информацию.
Добавим побольше условий к запросу.
На этот раз возвращается только те заказы, сумма которых превышает $15.
Предложение ON
Прежде чем перейти к другим видам объединяющих запросов, нам нужно рассмотреть предложение ON. Оно служит для вставки условий JOIN в отдельные предложения.
Теперь мы можем различать условия, относящиеся к JOIN и условия в части WHERE. Но еще есть небольшая разница в функционировании. Мы увидим это, когда перейдем к примерам с LEFT JOIN.
Предложение USING
Предложение USING немного похоже на конструкцию ON. Если столбцы в таблицах называется одинаково, можно указать их здесь.
На самом деле это очень похоже на NATURAL JOIN, т.е. объединяющий столбец (customer_id) не повторяется дважды.
Left (Outer) Join (Левое внешнее соединение)
LEFT JOIN это вид внешнего соединения. В следующем запросе, если не найдены совпадения во второй таблице, записи из первой таблице все равно отобразятся.
Хотя у Andy и нет заказов, эта запись все равно отображается. Значение из второй таблицы равно NULL.
Это полезно, когда нужно найти записи, у которых нет связей. Например, мы можем найти всех покупателей, которые ничего не заказывали.
Все что мы сделали — нашли все значения NULL для order_id.
Отметим, что ключевое слово OUTER не обязательно. Вы можете использовать просто LEFT JOIN вместо LEFT OUTER JOIN.
Условия
Теперь давайте посмотрим на запросы с условиями.
Так, что случилось с Andy и Sandy? LEFT JOIN подразумевает, что мы должны получить покупателей, у которых нет заказов. Проблема в том, что условие WHERE скрывает эти результаты. Чтобы получить их, мы можем попытаться включить условие с NULL.
Появился Andy, но нет Sandy. Выглядит неправильно. Для того чтобы получить то, что мы хотим, нужно использовать предложение ON.
Теперь мы получили всех, и все заказы более $15. Как я говорил ранее, предложение ON иногда работает не так как WHERE. В таких внешних объединениях как это, столбцы включаются всегда, даже если нет совпадений в условии предложения ON.
Right (Outer) Join (Правое внешнее соединение)
Объединение RIGHT OUTER JOIN работает также, только порядок таблиц меняется на обратный.
На этот раз мы не получили результатов с NULL, потому что каждый заказ имеет сопоставление с записью покупателя. Мы можем поменять порядок таблиц и получим тот же результат, что и с LEFT OUTER JOIN.
Теперь у нас появились значения NULL, потому что таблица покупателей с правой стороны от объединения.