1. Главная страница » Компьютеры » Mysql связанные таблицы пример

Mysql связанные таблицы пример

Автор: | 16.12.2019

Содержание

Primary tabs

Forums:

Ситуация

Участники подают заявки на участие в номинациях в каком-то онлайн конкурсе — один участник может участвовать в любом числе номинаций (их много) и самих участников тоже может быть сколько угодно (тоже много) — а значит, здесь надо реализовать связь многие-ко-многим.

Далее будет использоваться синтаксис mysql.

Проектируем базу для связи Многие-ко-Многим — sql для создания таблиц

Нам потребуется создать три таблицы:

  1. Таблицу "Заявка"
  2. Таблицу "Номинация"
  3. и т.н. "таблицу связи"

Сделаем это (SQL):

Обратите внимание на:

  1. Свойство "ON DELETE CASCADE" —
    это значит, что если будет удалена запись в другой таблице, на которую ссылается данный кортеж (из таблицы связи), то и этот кортеж будет удалён целиком. В данном случае связь удаляется из таблицы если удалено хотя быть что-то одно из двух:
    • или заявка, на которую он ссылается
    • или номинация, на которую он ссылается

    — таким образом мы переносим задачу удаления неактуальный связей с приложения на СУБД.

    В этом примере использован составной первичный ключ PRIMARY KEY ,ведь там написано:

    — это автоматически делает (накладывает ограничение) данную комбинацию двух внешний ключей уникальной в рамках таблицы связи (т.е. уже не получится в данную таблицу два раза написать что "Вася подал заявку в номинацию "Лучший повар""), на самом деле, в ряде случаев (например, для оперирования удобным численным ключом) можно было бы просто добавить обычный численные первичный ключ, а на пару внешних ключей каждого кортежа таблицы связи наложить требование уникальности (т.н. "уникальный составной индекс") — т.е. сделать нашу таблицу связи немного другой:, итак — таблица связи (другой вариант):

    Извлечение данных для связи "многие ко многим" (SELECT)

    Возникает логичный вопрос — как же получать данные из базы, используя таблицу связи?
    Есть разные варианты для разных ситуаций, которые мы сейчас рассмотрим, но прежде чем проиллюстрировать их, заполните созданные выше таблицы данными с помощью такого sql (чтобы вы тоже могли поэкспериментировать с запросами)

    Рассмотрим задачу извлечения участников, связанных с данной номинацией — или короче "номинации, и всех, кто подал в неё заявки" (алгоритм извлечения данных в обратную сторону — т.е. "участик и все его номинации" абсолютно аналогичен).
    На практике приходится сталкиваться с двумя базовыми ситуациями:

    1. Извлечение одной сущности номинации и связанных с ней участников
    2. Извлечение списка сущностей номинаций и связанных с каждой из номинаций участников (т.е. фактически список участников для каждого элемента из списка номинаций).

    Извлечение связанных (многие-ко-многим) данных для одной сущности

    Пусть у нас известен id () номинации и мы хотим получить сведения об этой номинации и всех участниках в ней.
    Во-первых, сделать это можно двумя sql запросами:

    1. Сначала просто получим кортеж этой номинации:
    2. После, опять же зная id номинации (используем в WHERE), достаточно просто сделать LEFT JOIN между таблицей связи и таблицей участников:

    — как видим, тут мы получили вообще все колонки (т.к. в запросе указали звездочку *) двух соединённых таблиц (связи и заявок).
    Также видим что на номинации с > Все эти данные можно использовать в приложении, после выполнения запроса к БД — например записать, то что нужно в поле, хранящее массив объекта конкретной номинации.

    Читайте также:  Http 406 not acceptable

    Если вам требуется от массива связанных сущностей только одно поле (напр. имена участников), то решить задачу можно вообще одним sql запросом, используя группировку (GROUP BY) и применимую к группируемым значения колонки функцию конкатенации GROUP_CONCAT():

    Получим единственный кортеж:

    • провели сразу тройной JOIN, как бы поставив таблицу связи между таблицами номинаций и заявок.
    • нас интересовали имена участников для 4 номинации — поэтому использовали WHERE Tickets_Nominations.nomination_ >Плюсом такого подхода является то, что в приложении можно использовать готовую строку participants_names, а минусом то, что с этим значением уже нельзя работать как с массивом, явно не преобразовав.

    Извлечение списка сущностей со связанными данными

    Прежде всего можно:

    1. Cначала извлечь (SELECT) необходимые номинации (или вообще все),
    2. а потом уровне приложения в цикле извлечь связанные данные для каждой номинации отдельно (как это показано выше) — это не оптимальный способ так как он порождает много запросов к БД (так что если список номинаций — — или иных сущностей велик, то и запросы сильно скажутся на суммарном времени выполнении скрипта и нагрузке на процессор)

    HTML, CSS, JavaScript, JQuery, PHP, MySQL

    SQL для начинающих. Часть 3

    Представляю Вашему вниманию вольный перевод статьи SQL for Beginners Part 3

    Сегодня мы продолжаем наше путешествие в мире 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 или более заказов. Но каждый заказ может принадлежать только одному покупателю.

    Связь многие ко многим

    В некоторых случаях требуется многочисленные связи по обе стороны отношений. Например, каждый заказ может содержать множество товаров. И каждый товар может присутствовать во многих заказах.

    Читайте также:  Lenovo vibe c2 power обзор

    Для такой связи нам потребуется создать дополнительную таблицу:

    Назначение таблицы "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 в обеих таблицах. Результат должен быть аналогичен естественному объединению.

    Читайте также:  128Gb ssd plextor s3c

    Результат почти такой же. Столбец 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, потому что таблица покупателей с правой стороны от объединения.

    Заключение

    Спасибо за чтение статьи. Надеюсь Вам понравилось!

    Довольно распространенной задачкой на знание SQL, встречающейся на собеседованиях(на вакансии любых программистов), является задачка на создание связи типа Многие-ко-Многим. (Задачку на связь один-ко-многим и теоретическую основу я раскрыл в предыдущей статье)
    Условие примерно такое: есть две таблицы, например, таблица подразделений компании и таблица работников(можно и другой пример многих ко многим привести, например авторы и книги).

    Необходимо создать и связать эти таблицы друг с другом, если известно, что:
    Один и тот же работник может принадлежать к разным подразделениям компании, а каждое подразделение, естественно, включает несколько работников.

    Интуитивно понятно, что двумя таблицами при решении этой задачи уже не обойтись. Это действительно так. Для связи многие-ко многим нам придется создать связывающую таблицу. Как нетрудно догадаться эта таблица будет содержать связи этих двух таблиц и состоять, как минимум из двух столбцов – 1) столбец с id первой таблицы 2) столбец с id второй таблицы. Соответственно в итоге мы должны получить 3 таблицы. Создадим их:

    Добавить комментарий

    Ваш e-mail не будет опубликован. Обязательные поля помечены *