Как найти все отношения между всеми таблицами mysql?
Как найти все отношения между всеми таблицами MySQL? Если, например, я хочу знать отношение таблиц в базе данных, содержащих около 100 таблиц.
Есть ли вообще знать это?
8 ответов
Лучший способ, программно говоря, собирать данные из таблицы INFORMATION_SCHEMA.KEY_COLUMN_USAGE следующим образом:
Здесь больше информации о колонках, например ORDINAL_POSITION, которая может быть полезна в зависимости от вашей цели.
ВЫБЕРИТЕ
`TABLE_NAME`,
`COLUMN_NAME`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_SCHEMA` = ‘YOUR_DATABASE_NAME’ И
`REFERENCED_TABLE_SCHEMA` НЕ НУЛЛ И
`REFERENCED_TABLE_NAME` не является NULL и
`REFERENCED_COLUMN_NAME` НЕ НУЛЛ
не забудьте заменить YOUR_DATABASE_NAME своим именем базы данных!
Быстрый метод визуализации связей в MySQL — это обратное проектирование базы данных с помощью MySQL Workbench.
Это можно сделать и с помощью обратной инженерии, которая приведет к диаграмме сущностей-отношений, которая будет похожа на следующую (хотя вам, возможно, придется ее самостоятельно организовать после ее создания):
вы можете использовать:
1) Зайдите в свою базу данных:
use DATABASE;
2) Показать все таблицы:
show tables;
3) Посмотрите на каждый столбец таблицы, чтобы собрать, что он делает и что он сделал:
describe TABLENAME;
4) Опишите хорошо, поскольку вы можете точно определить, что делают ваши столбцы таблицы, но если вы хотите еще более внимательно рассмотреть сами данные: select * from TABLENAME
Если у вас большие таблицы, то каждая строка обычно имеет id , и в этом случае мне нравится делать это, чтобы просто получить несколько строк данных и не перегружать терминал:
select * from TABLENAME where id<5 — Вы можете поместить любое условие, которое вам нравится.
Этот метод дает вам больше информации, чем просто делать select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS; , а также предоставляет вам дополнительную информацию об размеру укуса каждый раз.
ИЗМЕНИТЬ
Как и предполагалось, вышеприведенный WHERE id < 5 был плохим выбором в качестве условного заполнителя. Не рекомендуется ограничивать идентификационный номер, тем более, что идентификатор обычно не заслуживает доверия, чтобы быть последовательным. Вместо этого добавьте LIMIT 5 в конце запроса.
MySQL Workbench — how to display relationships?
How do I make Workbench display the connecting lines between tables when I define relationships?
5 Answers 5
I had an issue generated a PNG with the relationship lines.
- WorkBench version 8.0
- MacOS Mojave 10.14.5 with Dark Theme
Disable dark theme and regenerate the diagram then export the PNG.
You need to open reverse engineer: Database->Reverse engineer CTRL+R
Complete the wizard. It ask about the database to connect and other simple questions.
Just click Next .
After wizard completion the MySQL Model tab will be opened. Click there ERR Diagram icon
When you edit a table in an EER model, you will have a tab named Foreign Keys that allows you to define well. the foreign keys. These foreign keys will be visible in the diagram.
You can change the settings for the displayed relationship by double clicking on the line that connects the tables.
SQL для начинающих: часть 3 — связи базы данных
Сегодня мы продолжаем наше путешествие в мир SQL и связанных баз данных. В третьей части этой серии мы узнаем, как работать с несколькими таблицами, которые имеют отношения друг с другом. Во-первых, мы рассмотрим некоторые базовые концепции, а затем начнем работать с JOIN queries в SQL.
Вы также можете увидеть базы данных SQL в действии, просмотрев SQL scripts, apps and add-ons на рынке Envato.
Напоминание
Введение
При создании базы данных здравый смысл подсказывает, что мы используем отдельные таблицы для разных типов сущностей. Например: клиенты, заказы, предметы, сообщения. Но нам также нужно иметь отношения между этими таблицами. Например, клиенты делают заказы, а заказы содержат предметы. Эти отношения должны быть представлены в базе данных. Кроме того, при получении данных с помощью SQL нам нужно использовать определённые типы запросов JOIN, чтобы получить то, что нам нужно.
Существует несколько типов отношений базы данных. Сегодня мы рассмотрим следующее:
- Отношения один к одному
- Отношения «один ко многим» и «многие к одному»
- «Многие ко многим» отношения
- Самостоятельные ссылки
При выборе данных из нескольких таблиц с отношениями мы будем использовать запрос JOIN. Существует несколько типов JOIN, и мы собираемся узнать следующее:
- Перекрестные соединения
- Обычные соединения
- Внутренние соединения
- Левые (внешние) соединения
- Правые (внешние) соединения
Мы также узнаем об оговорках ON и USING.
Отношения один к одному
Предположим, у вас есть таблица для клиентов:
Мы можем поместить информацию об адресе клиента в отдельную таблицу:
Теперь мы имеем отношение между таблицей Customers и таблицей Addresses. Если каждый адрес может принадлежать только одному клиенту, это отношение «Один к одному». Имейте в виду, что такого рода отношения не очень распространены. Наша начальная таблица, которая включала адрес вместе с клиентом, в большинстве случаев могла работать нормально.
Обратите внимание: теперь в таблице Customers есть поле с именем «address_id», которое ссылается на запись соответствия в таблице Address. Это называется «Foreign Key» и используется для всех видов отношений баз данных. Мы рассмотрим этот вопрос позже.
Мы можем показать отношения между клиентскими и адресными записями следующим образом:
Обратите внимание, что существование отношений может быть необязательным, например, есть запись клиента, у которой нет связанной записи адреса.
Отношения «один ко многим» и «многие к одному»
Это наиболее часто используемый тип отношений. Рассмотрим веб-сайт e-commerce со следующим:
- Клиенты могут делать много заказов.
- Заказы могут содержать много позиций.
- Позиции могут иметь описания на многих языках.
В этих случаях нам необходимо создать отношения «один ко многим». Вот пример:
У каждого клиента может быть ноль, один или несколько заказов. Но заказ может принадлежать только одному клиенту.
Отношения «многие ко многим»
В некоторых случаях вам может потребоваться несколько экземпляров с обеих сторон. Например, каждый заказ может содержать несколько элементов. И каждый элемент также может быть в нескольких заказах.
Для этих отношений нам нужно создать дополнительную таблицу:
Таблица Items_Orders имеет только одну цель, а именно, чтобы создать отношение «многие ко многим» между элементами и заказами.
Вот картинка таких отношений:
Если вы хотите включить записи items_orders в график, это может выглядеть так:
Самостоятельные ссылки
Это используется, когда таблица должна иметь отношения с самой собой. Например, у вас есть реферальная программа. Клиенты могут направлять других клиентов на ваш веб-сайт. Таблица может выглядеть так:
Клиенты 102 и 103 были переданы клиентом 101.
На самом деле это может быть похоже на отношение «один ко многим», поскольку один клиент может ссылаться на нескольких клиентов. Также он может выглядеть, как древовидная структура:
Один клиент может ссылаться на ноль, одного или несколько клиентов. К каждому клиенту может обращаться только один клиент, или вообще никто.
Если вы хотите создать самостоятельную ссылку «многие ко многим», вам понадобится дополнительная таблица, вроде той, что мы говорили в предыдущем разделе.
Foreign Keys
До сих пор мы узнали только о некоторых концепциях. Теперь пришло время воплотить их в жизнь с помощью SQL. Для этой части нам нужно понять, что такое Foreign Keys.
В приведённых выше примерах отношений мы всегда имели эти поля «**** _ id», которые ссылались на столбец в другой таблице. В этом примере столбец customer_id в таблице Orders является столбцом Foreign Key:
В базе данных типа MySQL есть два способа создания столбцов внешних ключей:
Чёткое определение Foreign Key
Давайте создадим простую таблицу клиентов:
Теперь таблицу заказов, в которой будет Foreign Key:
Оба столбца (customers.customer_id и orders.customer_id) должны иметь одинаковую структуру данных. Если один является INT, другой не должен быть BIGINT, например.
Обратите внимание, что в MySQL только механизм InnoDB имеет полную поддержку Foreign Keys. Но другие механизмы хранения данных по-прежнему позволят вам указывать их без каких-либо ошибок. Кроме того, столбец Foreign Key индексируется автоматически, если не указать для него другой индекс.
Без явной декларации
Та же таблица заказов может быть создана без явного объявления столбца customer_id как Foreign Key:
При получении данных с помощью запроса JOIN вы всё равно можете рассматривать этот столбец как Foreign Key , хотя механизм базы данных не знает об этом отношении.
Далее мы собираемся узнать о JOIN-запросах.
Визуализация отношений
Моим любимым программным обеспечением для проектирования баз данных и визуализации отношений Foreign Key является MySQL Workbench.
После разработки базы данных вы можете экспортировать SQL и запустить его на своем сервере. Это очень удобно для больших и сложных баз данных.
JOIN Queries
Для извлечения данных из базы, имеющей отношения, нам часто приходится использовать JOIN queries.
Прежде чем начать, давайте создадим таблицы и некоторые образцы данных для работы.
У нас 4 клиента. У одного клиента два заказа, у двух клиентов по одному заказу, а у одного клиента нет заказа. Теперь давайте посмотрим различные виды JOIN queries, которые мы можем запустить в этих таблицах.
Перекрестное соединение
Это тип JOIN query по умолчанию, если условие не указано.
Результатом является так называемый «Cartesian product» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Так как каждая таблица имела 4 строки, мы получили результат из 16 строк.
Ключевое слово JOIN может быть опционально заменено запятой.
Конечно, такой результат не очень полезен. Давайте посмотрим на другие типы соединений.
Обычное соединение
При таком типе JOIN query таблицы должны иметь имя соответствующего столбца. В нашем случае обе таблицы имеют столбец customer_id. Таким образом, MySQL будет присоединяться к записям только тогда, когда значение этого столбца соответствует двум записям.
Как вы можете видеть, столбец customer_id отображается только один раз, потому что ядро базы данных рассматривает это как общий столбец. Мы видим два заказа Адама, а два других — Джо и Сэнди. Наконец, мы получаем некоторую полезную информацию.
Внутреннее соединение
Когда указано условие соединения, выполняется Inner Join. В этом случае было бы неплохо иметь поле customer_id в обеих таблицах. Результаты должны быть похожими на Natural Join.
Результаты те же, за исключением небольшой разницы. Столбец customer_id повторяется дважды, один раз для каждой таблицы. Причина в том, что мы просто попросили базу данных соответствовать значениям этих двух столбцов. Но сами они не знают, что представляют одну и ту же информацию.
Давайте добавим еще несколько условий в запрос.
На этот раз мы получили заказы на сумму более $15.
ON Clause
Прежде чем перейти к другим типам соединений, нам нужно посмотреть ON clause. Это полезно для помещения условий JOIN в отдельное предложение.
Теперь мы можем отличить условие JOIN от условий WHERE. Но есть и небольшая разница в функциональности. Мы увидим это в примерах LEFT JOIN.
USING Clause
USING clause похоже на предложение ON, но оно короче. Если столбец имеет одинаковое имя в обеих таблицах, мы можем указать его здесь.
На самом деле это похоже на NATURAL JOIN, поэтому столбец join (customer_id) не повторяется дважды в результатах.
Левое (внешнее) соединение
LEFT JOIN — это тип внешнего соединения. В этих запросах, если во второй таблице не найдено совпадений, запись из первой таблицы по-прежнему отображается.
Хотя у Энди нет заказов, его запись все ещё отображается. Значения под столбцами второй таблицы имеют значение NULL.
Это полезно для поиска записей, которые не имеют отношений. Например, мы можем искать клиентов, которые не разместили какие-либо заказы.
Всё, что мы сделали, это нашли NULL для order_id.
Также обратите внимание, что ключевое слово OUTER является необязательным. Вы можете просто использовать LEFT JOIN вместо LEFT OUTER JOIN.
Условия
Теперь давайте рассмотрим запрос с условием.
Так что случилось с Энди и Сэнди? LEFT JOIN должен был вернуть клиентов без соответствующих заказов. Проблема в том, что предложение WHERE блокирует эти результаты. Чтобы их получить, мы можем попытаться включить условие NULL.
У нас Энди, но нет Сэнди. Тем не менее это выглядит не так. Чтобы получить то, что мы хотим, нам нужно использовать ON clause.
Теперь у нас есть все, и все заказы выше $ 15. Как я уже говорил, ON clause иногда имеет несколько иную функциональность, чем WHERE clause. В условии Outer Join , таком как этот, строки включаются, даже если они не соответствуют условиям ON clause.
Правое (внешнее) соединение
RIGHT OUTER JOIN работает точно так же, но порядок таблиц обратный.
На этот раз у нас нет результатов NULL, потому что каждый заказ имеет соответствующую запись клиента. Мы можем изменить порядок таблиц и получить те же результаты, что и в LEFT OUTER JOIN.
Теперь у нас есть эти значения NULL, потому что таблица Customers находится на правой стороне соединения.
Заключение
Спасибо, что прочитали статью. Надеюсь, вам понравилось! Пожалуйста, оставляйте свои комментарии и вопросы, и хорошего дня!
Не забудьте проверить SQL scripts, apps and add-ons на рынке Envato. Вы получите представление о возможностях баз данных SQL, и сможете найти идеальное решение, которое поможет вам в текущем проекте разработки.
Следуйте за нами на Twitter или подпишитесь на Nettuts + RSS Feed для получения лучших обучающих материалов по веб-разработке в Интернете.
Как узнать отношения между таблицами
У меня есть база данных в MySQL, созданная кем-то. У меня нет документации по базе данных.
Как я могу узнать взаимосвязь между таблицами?
Есть ли какой-либо запрос или процедура для создания отчета, чтобы можно было легко найти связи?
Я могу посмотреть информацию о схеме и вручную выяснить ее, но было бы здорово, если бы я мог создать отчет о взаимосвязях.
7 ответов
Поскольку предлагается быстрый способ перечислить ваши FK (ссылки на внешние ключи) с помощью представления KEY_COLUMN_USAGE:
Еще одним ценным вариантом может стать установка mysql workbench. (относится к) И попробуйте «Создать модели EER из базы данных». Вы наверняка сможете увидеть отношения между таблицами.
SchemaSpy — это инструмент на основе Java (требуется Java 5 или выше), который анализирует метаданные схемы в базе данных и генерирует ее визуальное представление в формате, отображаемом браузером.
Вот снимок экрана HTML-страницы с примером выходных данных с http://schemaspy.sourceforge.net/sample/:
Также есть приятный графический интерфейс, если вы не хотите использовать командную строку: http://schemaspygui.sourceforge.net/
Оба инструмента имеют открытый исходный код и, на мой взгляд, очень легкие и простые в использовании. Я использовал их несколько раз, когда оказывался в описанных вами ситуациях: чтобы получить обзор схемы и даже некоторые детали для более глубокого погружения. (Взгляните на отчет «Аномалии».)
Обновить
Обязательно ознакомьтесь с предстоящей версией SchemaSpy по адресу http://schemaspy.org
Вы можете получить обзор в MySql Workbench, выполнив следующие шаги:
- Перейдите в пункт меню «База данных».
- Выберите опцию «Обратный инженер».
- Будет открыт мастер, который сгенерирует диаграмму EER, которая появится.
У вас есть SELECTs , которые используют базу данных? Это может быть лучшим источником отношений.
Если вы используете phpmyadmin, тогда:
- Перейти к базе данных.
- Выберите таблицу и перейдите к ее структуре.
- Вы найдете представление отношений в нижней части структуры таблицы.
Лучше использовать верстак Mysql. Есть возможность создания диаграммы ER. Если вы используете phpmyadmin, выберите любую таблицу. Есть вкладка структуры, откуда вы можете увидеть структуру таблицы. Надеюсь, это поможет.