Уровень 20
МНОГОТАБЛИЧНЫЕ БАЗЫ ДАННЫХ
Слишком много лишних вариантов
Грег отдаст Реджи длинный список вариантов. Несколько недель спустя Реджи звонит Грегу и говорит, что от его списка нет никакого проку: ни одна из кандидаток не имеет с ним ничего общего.
Нельзя полностью игнорировать увлечения. Должен быть другой, лучший способ...
Использовать только первое увлечение
Грег отдаст Реджи длинный список вариантов. Несколько недель спустя Реджи звонит Грегу и говорит, что от его списка нет никакого проку: ни одна из кандидаток не имеет с ним ничего общего.
Зачем Грег пишет запрос, который поможет Реджи найти свою пару. В запросе используется функция SUBSTRING_INDEX, а первым увлечением должны быть "животные"
SELECT * FROM my_contacts
WHERE gender = 'Ж'
AND status = 'Не замужем'
AND state = 'МА'
AND seeking LIKE '%Мужчина%'
AND birthday > '1950-28-08'
AND birthday < '1960-28-08'
AND SUBSTRING_INDEX(interests,' ,' , 1) = 'животные';
Пара для Реджи
Наконец-то! Грег нашел пару для Реджи:
Трагическое несоответствие
Реджи договорился с Алексис о свидании и Грег с нетерпением ждал его рассказа. Он уже начал представлять себе новую таблицу my_contacts, которая станет началом новой социальной сети.
На следующий день у двери Грега стоит Реджи - и притом очень сердитый.
Реджи кричит: "Конечно, она интересуется животными. Но ты не сказал мне, что она делает из них чучела! Там повсюду мертвые животные!"
Мозговой Штурм
Как будет выглядеть следующий запрос Грега после создания нескольких столбцов увлечения?
Создание новых столбцов Interest
Грег понимает, что одним столбцом увлечений написать правильный запрос слишком сложно. Приходится использовать LIKE, что иногда приводит к неверным совпадениям.
Но Грег умеет пользоваться командой ALTER для изменения таблиц, а также разбивать текстовые строки, поэтому он решает создать несколько столбцов с увлечениями и поместить каждое увлечение в отдельный столбец. Он решает, что четырех столбцов будет достаточно.
Начинаем заново
Грег чувствует себя виноватым за неудачу Реджи и решает попробовать еще раз. Для начала он извлекает из таблицы запись Реджи:
Все без толку...
Добавление новых столбцов никак не помогло решить основную проблему: структура таблицы усложняет написание запросов к ней. Обратите внимание: в каждой версии таблицы нарушается правило автомарности данных.
... Один момент!
Мозговой Штурм
Какую пользу принесет создание новой таблицы? И как звязать данные из новой таблицы с существующей таблицей?
А если создать отдельную таблицу, в которой хранится только информация об увлечениях?
Одной таблицы недостаточно
Итак, если мы будем ограничиваться работой с текущей таблицей, хорошего решения не существует. Мы пытались обойти недостатки структуры данных разными способами, даже изменяя структуру всей таблицы. Ни один способ не сработал.
Рамки одной таблицы оказались слишком узкими. В действительности нам нужны дополнительные таблицы, которые работают в сочетании с текущей таблицей, позволяя нам связать одного человека с несколькими увлечениями. При этом существующие данные будут полностью сохранены.
Неатомарные столбцы из существующей таблицы следует переместить в новые таблицы.
Многотабличная база данных с информацией о клоунах
Помните нашу таблицу с информацией о клоунах из предыдущего уровня? Проблема с клоунами становится все больше, поэтому мы преобразовали одну таблицу в более удобный набор из нескольких таблиц.
На нескольких ближайших страницах мы покажем, почему таблица была разбита именно так, а не иначе и что означает все эти стрелки и ключи. А после этого вы сможете по тем же принципам разбить таблицу Грега.
Мозговой Штурм
Как вы думаете, что означают линии со стрелками? А изображение ключей?
Схема базы данных clown_tracking
Представление всех структур базы данных (таблиц, столбцов и т.д.) и логических связей между ними называется схемой.
Наглядное представление базы данных поможет вам представить, как связаны между собой компонентов базы данных, однако схема также может быть записана и в виде текста.
Описание данных (столбцов и таблиц) вашей базы данных, включая все взаимосвязанные объекты и связь между ними, называется СХЕМОЙ.
Упрощенное представление таблиц
Вы видели, как была преобразована таблица с информацией о клоунах. Теперь давайте попробуем сделать тоже самое с таблицей my_contacts.
Оба способа хорошо подходят для отдельных таблиц, но когда требуется построить диаграмму из нескольких таблиц, приходится искать что-то другое.
Ниже показано упрощенное представление таблицы my_contacts.
Диаграмма помогает отделить структуру таблицы от хранящихся в ней данных.
Как из одной таблицы сделать две
Мы знаем, что написать запрос для поиска информации в столбце interests в его текущем виде довольно затруднительно, потому что в одном столбце могут храниться сразу несколько значений. Впрочем, создание нескольких раздельных столбцов упростило нашу задачу.
Справа изображена таблица my_contacts в ее текущем состоянии. Столбец interests не атомарен, и существует только один действительно хороший способ сделать его атомарным: нам понадобится новая таблица, в которой будет хранится все увлечения.
Для начала нарисуем несколько диаграмм, которые покажут, как будут выглядеть новые таблицы. Только после того как будет готова новая схема, можно будет переходить к созданию новых таблиц или модификаций данных.
Удаляем столбец interests и размещаем его в отдельной таблице.
Столбец interests перемещается в новую таблицу.
В новой таблице interestsбудут храниться все увлечения из таблицы my_contacts (отдельная запись для каждого увлечения).
Добавляем столбцы, по которым можно будет узнать, какие увлечения принадлежат тому или иному человеку из таблицы my_contacts.
Мы вынесли увлечения из таблицы my_contacts, но как определить, кому какие увлечения принадлежат. Необходимо использовать информацию из таблицы my_contacts и разместить ее в таблице interests так, чтобы эти две таблицы были связаны между собой.
Например, для этого можно включить столбцы first_name и last_name в таблицу interests.
Мозговой Штурм
Мы двигаемся в верном направлении, но first_name и last_name - не лучший способ для связывания таблиц.
Почему?
Связывание таблиц
У первой версии связанных таблиц был один серьезный недостаток: мы пытались использовать для связывания поля fisrt_name и last_name. А если в таблице my_contacts появятся записи с одинаковыми значениями first_name и last_name?
Две таблицы должны связываться через уникальный столбец. К счастью, поскольку мы уже занялись нормализацией, в my_contacts такой столбец уже имеется: это первичный ключ.
Мы можем хранить значение первичного ключа из таблицы my_contacts в таблице interests. И что еще лучше, по этому столбцу можно будет определить, какие увлечения принадлежат тому или иному человеку из таблицы my_contacts. Такой способ связывания называется внешним ключом.
ВНЕШНИЙ КЛЮЧ - столбец таблицы, в котором хранятся значения ПЕРВИЧНОГО КЛЮЧА другой таблицы.
Что нужно знать о внешних ключах
Понимаю, внешний ключ позволит мне связать две таблицы. Но какой прок от значений NULL во внешнем ключе? Можно ли сделать так, чтобы внешний ключ всегда был связан с родительским ключом?
Значение NULL во внешнем ключе означает, что в родительской таблице не существует соответствующего значения первого ключа.
Однако мы можем сделать так, чтобы внешний ключ принимал только осмысленные значения, существующие в родительской таблице. Для этого следует воспользоваться ограничением.
Ограничение внешнего ключа
Хоты вы можете создать таблицу со столбцом, который будет выполнять функции внешнего ключа, такой столбец действительно станет внешним ключом только в том случае, если вы назначите его таковым в команде CREATE или ALTER. Ключ создается в структуре, называемой ограничением.
Создание ВНЕШНЕГО КЛЮЧА как ограничение таблицы дает определенные преимущества.
При попытки нарушения правила вы получите сообщение об ошибке; таким образом предотвращаются случайные нарушения связей между таблицами.
При вставки внешний ключ будет принимать только значения, существующие в первичном ключе родительской таблицы. Это требование называется целостностью данных.
Напомним что, ПЕРВИЧНЫЙ КЛЮЧ (primary key) представляет собой один из примеров уникальных индексов и применяется для уникальной идентификации записей таблицы. Никакие из двух записей таблицы не могут иметь одинаковых значений первичного ключа. Первичный ключ обычно сокращенно обозначают как PK (primary key).
Внешний ключ должен быть связан с уникальным значением из родительской таблицы.
Это значение может и не быть значением первичного ключа, но оно обязательно должно быть уникальным.
Разделение увлечений
А теперь самое сложное: мы воспользуемся другой функцией для удаления из текущего значения interests данных, скопированных в столбец interest1. После этого можно будет продолжить заполнение остальных столбцов по тому же принципу.
Функция SUBSTR получает текст столбца interests и возвращает заданную его часть. Мы выделяем символы, которые были скопированы в interest1, а также еще два символа (запятая и пробел).
Обновление столбцов
После выполнения команды UPDATE таблица будет выглядеть так, как показано ниже.
Однако работа еще не закончена. Теперь нужно проделать то же самое для столбцов interest2, interest3 и interest4.
Вывод списка
Наконец-то увлечения разделены по разным столбцам. Для вывода можно воспользоваться простой командой SELECT - но не для всех одновременно. И команда не позволит легко извлечь их в один итоговый набор, потому что увлечения хранятся в четырех столбцах. Результат будет выглядеть примерно так.
Конечно, мы можем написать четыре отдельные команды SELECT для вывода всех значений:
SELECT interest1 FROM my_contacts; SELECT interest3 FROM my_contacts;
SELECT interest2 FROM my_contacts; SELECT interest4 FROM my_contacts;
Остается лишь понять, как вставить результат выполнения этих команд в новую таблицу. К счастью, это можно сделать, причем способ не один - их не менее трех!
Кому нужны псевдонимы таблиц?
Вам и нужны! Мы сейчас займемся соединениями с выборкой данных из нескольких таблиц. Без псевдонимов вам придется вводить имена таблиц снова и снова и вам это быстро надоест.
Псевдонимы таблиц создаются почти так же, как псевдонимы столбцов. Псевдоним таблицы указывается после первого использования имени таблицы в запросе с ключевым словом AS. В следующем примере оно сообщает, что таблица my_contacts в дальнейшем будет также доступно по имени mc.
И я должен использовать "AS" каждый раз, когда потребуется создать псевдоним?
Нет, существует сокращенный синтаксис назначения псевдонимов.
Просто не указывайте ключевое словоAS. Следующий запрос делает тоже самое, что и запрос в начале страницы.
Псевдонимы таблиц также называются параллельными именами.
Все, что вы хотели знать о внутренних соединениях
Каждый кому доводилось слышать о SQL, наверняка слышал слово "соединение". Эта тема не так сложна, как может показаться на первый взгляд. Мы покажем вам, что такое соединения, как они работают, в каких ситуациях их следует применять и в какой ситуации применяется та или иная разновидность соединений.
Но начнем мы с рассмотрения простейшей разновидности соединений (которая и полноценным соединением-то и не является!).
Она известна под разными именами. В этой книге мы будем называть ее перекрестным соединением, хотя также встречается термин "перекрестное произведение" и "декартово соединение".
...вот откуда на самом деле берутся таблицы результатов.
Предположим, имеются две таблицы: с именами детей и названиями игрушек, которые есть у этих детей. Ваша задача - узнать, какие игрушки можно подарить каждому ребенку.
Перекресное соединения
Результат следующего запроса представляет собой перекрестное соединение. Мы запрашиваем данные из обеих таблиц: столбец toy из таблицы toys и столбец boy из таблицы boys.
Перекрестное соединение создает пару из каждого значения первой таблицы и каждого значения из второй таблицы.
Перекрестное соединение (CROSS JOIN) возвращает комбинации каждой записи первой таблицы с каждой записью второй таблицы.
Результат соединения состоит из 20 записей (5 игрушек * 4 мальчиков), то есть всех возможных комбинаций.
Часто задаваемые вопросы
И зачем мне это нужно?
А если использовать запрос вида: SELECT * FROM toys CROSS JOIN boys; Что произойдет при использовании SELECT * ?
Попробуйте сами. Вы получите те же 20 записей, но в них будут все 4 столбца.
О перекрестных соединениях важно знать, потому что при экспериментах с соединениями можно случайно получить перекрестный результат. Это поможет вам исправить неправильно написанный запрос. Проверьте, такое случается. Кроме того, перекрестные соединения иногда используются для тестирования скорости РСУБД и ее конфигурации. Их обработка занимает относительно много времени, сто упрощает анализ и сравнения.
Внутренним соединением (INNER JOIN) называется перекрестное соединение, из результатов которого часть записей исключается по условию запроса.
Что произойдет при перекрестном соединении двух очень больших таблиц?
Вы получите огромное количество записей. С перекрестным соединением лучше не экспериментировать - при таком гигантском объеме возвращаемых данных ваш компьютер может "зависнуть"!
Существует ли другой синтаксис у таких запросов?
Да, существует. Вместо ключевых слов CROSS JOIN можно поставить запятую:
SELECT toys.toy, boys.boy
FROM toys, boys;
Раньше я слышал термины "внутреннее соединение". Это то же самое, что и перекрестное соединение?
Перекрестное соединение является разновидностью внутреннего соединения. В сущности, внутреннее соединение - это перекрестное соединение, из результатов которого некоторые записи исключены по критерию запроса. Внутреннее соединение вскоре будут описаны более подробно - а пока просто запомните!
Мозговой Штурм
Как вы думаете, какой результат вернет следующий запрос:
SELECT b1.boy, b2.boy
FROM boys AS b1 CROSS JOIN boys AS b2;
Попробуйте сами.
Открой свое внутреннее соединение
Понял! Так я могу связать новые таблицы с новой версией my_contacts. Мне не нужно писать десяток SELECT, достаточно включить таблицы во внутреннее соединение.
Все только начинается.
Думаете это все? Мы рассмотрим только одну разновидность одного типа соединений. И вам еще предстоит узнать много всего об этом и других видах соединений, прежде чем вы сможете эффективно и разумно применять их на практике.
Внутреннее соединение комбинирует записи двух таблиц в соответствии с заданным условием. Столбцы включаются в выходной набор только в том случае, если соединенная запись удовлетворяет условию. Давайте внимательней рассмотрим синтаксис.
Внутреннее соединение комбинирует записи из двух таблиц в соответствии с заданным условием.
Внутреннее соединение в действии: эквисоединение
Рассмотрим следующие таблицы. У каждого мальчика есть только одна игрушка. Связь относится к типу "один-к-одному", а toy_id - внешний ключ.
Все, что требуется - определить, какая игрушка принадлежит каждому из мальчиков. Мы можем воспользоваться внутренним соединением с оператором + для поиска совпадений внешнего ключа boys с первичным ключом toys.
Эквивалентное соединение - внутреннее соединение с проверкой равенств.
Внутреннее соединение в действии: неэквивалентное соединение
Неэквивалентное соединение возвращает записи, у которых заданные значения столбцов не равны. Для примера рассмотрим те же две таблицы boys и toys. Используя неэквивалентное соединение, мы можем точно узнать, каких игрушек нет у каждого из мальчиков (такой результат более удобен при поиске подарка на день рождения).
Неэквивалентное соединение проверяет несовпадение значений.
Последнее внутреннее соединение: естественное соединение
Осталась всего одна разновидность внутренних соединений - так называемые естественные соединения. Естественные соединения возможны только в том случае, если столбец, по которому выполняется соединение, имеет одинаковые имена в обеих таблицах. Давайте еще раз рассмотрим эти две таблицы.
Как и прежде, мы хотим знать, какая игрушка есть у каждого из мальчиков. Естественное соединение распознает совпадающие имена столбцов в двух таблицах и вернет соответствующие комбинации.
Естественное соединение связывает записи но значениям одноименных столбцов.
Встроеные запросы?
Грег постепенно начинает понимать возможности соединений. Он видит, что разбиение базы данных на таблицы имеет смысл, а работать с хорошо спроектированными таблицами не так уж сложно. Грег даже планирует расширить базу данных gregs_list.
Но мне по прежнему часто приходится вводить один запрос, а потом использовать его результаты на входе другого запроса, хотя удобнее было бы разместить один запрос внутри другого. Но это только мечты...
Запрос внутри другого запроса?
Так возможно?
Откровенно о псевдонимах таблиц и столбцов
Интервью недели:
СЕНСАЦИЯ! РАССЛЕДОВАНИЕ! Псевдонимы SQL, что они скрывают?
Galaxy QA Academy: Добро пожаловать. Псевдоним Таблицы и Псевдоним Столбцы. Мы рады, что вы сегодня с нами. Надеемся, вы поможете нам прояснить некоторые недопонимание.
Псевдоним таблицы: Еще бы, я тоже очень рад. И вы можете для краткости называть нас ПТ и ПС во время этого интервью (смеется).
Galaxy QA Academy: Ха-ха! Да, это будет уместно. Итак, ПС, начнем с вас. Для чего такая секретность? Вы что-то пытаетесь скрыть?
Псевдоним Столбца: Вовсе нет! Если уж на то пошло, я стараюсь все прояснить. Ведь я сейчас говорю за нас обоих - верно, ПТ?
ПТ: Конечно. В случае ПС и так понятно, что он старается сделать: он берет длинные или избыточные имена столбцов и упрощает работу с ними. Просто для удобства. Кроме того, он предоставляет таблицы результатов с понятными именами столбцов. Со мной дело обстоит немного иначе.
QA Academy: Надо признать, мы не настолько хорошо знакомы с вами, ПТ. Мы видели, как вы работаете, но еще не до конца понимаете, что именно вы делаете. Ведь когда вас используют в запросах, вы не отображаетесь в результатах.
ПТ: Да, это правда. Но по-моему, вы не улавливаете моего более высокого предназначения.
QA Academy: Высокого предназначения? Интересно, продолжайте.
ПТ: Я существую для того, чтобы упростить написание запросов.
ПС: И еще ты помогаешь мне в соединениях, ПТ.
QA Academy: Ничего не понимаю. Может, приведете пример?
ПТ: Давайте рассмотрим синтаксис. Думаю, вам будет предельно понятно, что я делаю:
SELECT mc.last_name, mc.first_name,
p.profession
FROM my_contacts AS mc
INNER JOIN
profession AS p
WHERE mc.contact_id = p.id;
QA Academy: Понятно! Повсюду, где мне пришлось бы вводить my_contacts, достаточно ввести mc. А profession заменяется на p. Так гораздо проще и намного удобнее, когда мне приходится включать два имени столбцов в один запрос.
ПТ: Особенно когда таблицы имеют похожие имена. Упрощение помогает не только написать нужный запрос, но и понять его, когда вы вернетесь к нему через какое-то время.
QA Academy: Большое спасибо, ПТ и ПС. Нам было очень... э... куда они пропали?
Новые инструменты
После главы 8 вы можете строить соединения, как настоящий SQL - профессионал. Ниже перечислены основные понятия этой главы. Полный список инструментов приведен в приложении 3.
Запросы внутри запросов
И все заметят, что я полный... (Как это называется? Утонченность? Изысканность? Элегантность?)
Мне, пожалуйста, запрос из двух частей. Соединения - хорошая шутка, но иногда возникает необходимость обратиться к базе данных сразу с несколькими вопросами. Или взять результат одного запроса и использовать его в качестве входных данных другого запроса. В этом вам помогут подзапросы, также называемые подчиненными запросами. Они предотвращают дублирование данных, делают запросы более динамичными и даже помогут вам попасть на вечеринку в высшем обществе. (А может, и нет - но два из трех тоже неплохо!)
Грег берется за поиск работы
До настоящего момента база данных gregs_list была сугубо бескорыстным делом. Она помогла Грегу подбирать пары для своих друзей, но заработка не приносила.
Внезапно Грег сообразил, что он мог бы открыть собственное кадровое агенство, в котором подбирал бы людям из своего списка различные варианты работы.
Имея новые функциональные возможности, я могу создать собственное кадровое агенство.
Грег знает, что для знакомых, которые заинтересуются его предложением, в базу данных придется добавить новые таблицы. Вместо того чтобы размещать информацию в my_contacts, он решает создать отдельные таблицы о связями "один-к-одному" по двум причинам.
Во-первых, не все участники списка my_contacts заинтересованы в его услугах. Отдельная таблица позволяет избавиться от значений NULL в my_contacts.
Во-вторых, если когда-нибудь Грег наймет людей, которые будут помогать ему вести бизнес, информация о доходах может оказаться конфиденциальной. В этом случае Грег предоставит доступ к таким таблицам только тем, кому он действительно необходим.
В списке Грега появляются новые таблицы
Грег добавил в свою базу данных новые таблицы для хранения информации об ожидаемой должности и диапазоне заработка, а также текущей должности и заработке. Также Грег создает простую таблицу для хранения информации об имеющихся вакансиях.
Грег использует внутренее соединение
Грег получил информацию об отличной вакансии и теперь пытается найти кандидатов на нее в своей базе данных. Он хочет найти наилучшее совпадение, поскольку в случае найма его кандидата он получит премиальные.
Два запроса преобразуется в запрос с подзапросом
Фактически мы всего лишь объединяем два запроса в один. Первый запрос называется внешним, а второй - внутренним.
