Уровень 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.

Во-вторых, если когда-нибудь Грег наймет людей, которые будут помогать ему вести бизнес, информация о доходах может оказаться конфиденциальной.  В этом случае Грег предоставит доступ к таким таблицам только тем, кому он действительно необходим.

В списке Грега появляются новые таблицы

Грег добавил в свою базу данных новые таблицы для хранения информации об ожидаемой должности и диапазоне заработка, а также текущей должности и заработке. Также Грег создает простую таблицу для хранения информации об имеющихся вакансиях.

Грег использует внутренее соединение

Грег получил информацию об отличной вакансии и теперь пытается найти кандидатов на нее в своей базе данных. Он хочет найти наилучшее совпадение, поскольку в случае найма его кандидата он получит премиальные.

Два запроса преобразуется в запрос с подзапросом

Фактически мы всего лишь объединяем два запроса в один. Первый запрос называется внешним, а второй - внутренним. 

Подзапросы: если одного запроса недостаточно

Подзапрос - не что иное, как запрос внутри другого запроса. "Охватывающий" запрос называется внешним, а "вложенный" - внутренним запросом, или подзапросом.

Так как подзапрос использует оператор =, он возвращает одно значение, одну запись из одного столбца (иногда называется "ячейкой", но в SQL используется термин скалярное значение). Это значение сравнивается со столбцами в условии WHERE.

Подзапрос в действии

Давайте посмотрим, как работает аналогичный запрос к таблице my_contacts. РСУБД читает скалярное значение из таблицы zip_code и сравнивает его со столбцами в условии WHERE.

Часто задаваемые вопросы

Почему тоже самое нельзя сделать с использованием соединений?

Можно, но некоторые люди считают, что работать с подзапросами проще, чем с соединениями. Хорошо иметь свободу выбора синтаксиса.

Тот же запрос можно реализовать следующим образом:

SELECT last_name, first_name

From my_contacts mc

NATURAL JOIN zip_code zc

WHERE zc.city = 'Мемфис'

AND zc.state = 'TN'

Внутренний или внешний?

ВНЕШНИЙ ЗАПРОС

Знаешь, Внутренний Запрос, ты мне вообще-то не нужен. Я прекрасно обойдусь и без тебя.

Да, конечно. Ты делаешь мне один маленький результат, а пользователям нужны данные и притом МНОГО. Я даю им эти данные. Думаю, если бы тебя не было, это бы их вполне устроило.

Не придется, если добавить условие WHERE.

Нужен, еще как.  Какая польза от одного столбца одной записи? Он просто не содержит достаточной информации.

Конечно, но я работаю сам по себе.

ВНУТРЕННИЙ ЗАПРОС

Да и я без тебя обойдусь. Думаешь это так весело - давать тебе конкретный, точный результат только для того, что-бы ты превратил его в набор подходящих записей? Количество не заменяет качество, знаешь ли.

Нет, я придаю твоим результатам некое подобие специализации. Без меня тебе придется возится со всеми данными в таблице.

Я И ЕСТЬ твое условие WHERE, и притом предельно конкретное. Собственно ты мне не так уж нужен.

Ладно. Возможно, нам все же стоит работать вместе. Я определяю направление поиска твоих результатов.

Как и я.

Правила для подзапросов

Ниже перечислены некоторые правила, которым должны удовлетворять подзапросы.  Заполните пропуски словами из следующего набора (некоторые слова могут использоваться многократно).

Коррелированный подзапрос с NOT EXISTS

Очень распространенный сценарий использования коррелированного подзапроса - поиск во внешнем запросе всех записей, у которых нет соответствующих записей в связанной таблицы. 

Допустим Грег хочет расширить круг клиентов своей службы поиска работы. Для этого он собирается разослать сообщения всем людям из my_contacts, данные которых еще не содержатся в таблице job_current. Для поиска записей он использует условие NOT EXIST.

Коррелированный подзапрос с NOT EXISTS

По аналогии с IN и NOT IN, в подзапросах также можно использовать ключевые слова EXISTS и NOT EXISTS. Приведенный ниже подзапрос возвращает данные из my_contacts, у которых значение contact_id по крайней мере один раз встречается в таблице contact_interest.

Служба поиска работы Грега принимает заказы

Грег вполне освоился с выборкой данных с использованием подзапросов. Он даже научился пользоваться ими в командах INSERT? UPDATE и DELETE.

Он снял небольшой офис и собирается провести вечеринку, чтобы отпраздновать начало нового дела.

Интересно, удастся ли мне найти своего первого работника в таблице job_desired...

Часто задаваемые вопросы

Как лучше всего строить подзапрос внутри подзапроса?

Безусловно. Количество уровней вложения подзапросов ограничено, но в большинстве РСУБД оно значительно превышает практический "потолок"

Итак, подзапрос можно вложить в другой подзапрос?

Попробуйте написать маленькие подзапросы для различных частей вопросов. Присмотритесь к ним и попробуйте скомбинировать. Если вы пытаетесь найти людей с той же зарплатой, как у самого высокооплачиваемого веб-дизайнера, разбиение запроса может выглядеть так:

Найти самого высокооплачиваемого веб-дизайнера

Найти людей зарабатывающих х

после чего подставить первый запрос вместо х.

В большинстве случаев - да можете, но сначала необходимо кое что узнать о соединениях.

Подзапросы мне не нравятся, могу ли я использовать вместо них соединения?

Левое, правое...

С другой стороны, внешнее соединения в большой степени зависит от отношениями между двумя таблицами, чем все рассмотренные ранее типы соединений.

Левое внешнее соединение (LEFT OUTER JOIN) перебирает все записи левой таблицы и ищет для каждой соответствие среди записей правой таблицы. В частности это удобно, когда между левой и правой таблицей существует связь типа "один-ко-многим".

Чтобы понять логику внешнего соединения, необходимо понять, какая таблица находится "слева", а какая "справа".

В левом внешнем соединении таблица, следующая после FROM, но ДО JOIN, считается "левой", а таблица, следующая ПОСЛЕ JOIN, считается "правой".

В левом внешнем соединении для КАЖДОЙ ЗАПИСИ ЛЕВОЙ таблицы ищется соответствие среди записей правой таблицы.

Пример левого внешнего соединения

При помощи левого внешнего соединения мы можем узнать, какая игрушка принадлежит той или иной девочке.

Ниже приведен синтаксис левого внешнего соединения на примере уже использовавшихся таблиц. Таблица girls указана первой после FROM, поэтому она считается левой таблицей; далее следуют ключевые слова LEFT OUTER JOIN; и наконец, таблица toys считается правой таблицей.

Значение NULL в результатах левого внешнего соединения означает, что правая таблица не содержит  значений, соответствующих левой таблице.

И все? Спрашивается, чего мы добились? Выходит, внешнее соединение ничем не отличается от внутреннего.

 

Отличается: внешнее соединение возвращает запись независимо от того, есть у нее совпадение в таблице или нет.

 

Отсутствие совпадений обозначается значением NULL. В нашем примере с девочками и игрушками NULL в результатах означает, что данная игрушка не принадлежит никому из девочек. Очень ценная информация! 

Правое внешнее соединение

И все? Спрашивается, чего мы добились? Выходит, внешнее соединение ничем не отличается от внутреннего.

 

Отличается: внешнее соединение возвращает запись независимо от того, есть у нее совпадение в таблице или нет.

 

Отсутствие совпадений обозначается значением NULL. В нашем примере с девочками и игрушками NULL в результатах означает, что данная игрушка не принадлежит никому из девочек. Очень ценная информация! 

Правое внешнее соединение ищет в левой таблице соответствия для правой таблицы.

Правое внешнее соединение почти полностью аналогично левому внешнему соединению, кроме того, что оно сравнивает правую таблицу с левой. Следующие два запроса возвращают абсолютно одинаковые результаты.

Создание новой таблицы

Мы можем создать таблицу с перечислением всех клоунов и идентификаторов их начальников. Вот как выглядит иерархия с идентификаторами.

В номой таблице для каждого клоуна указан идентификатор его начальника из таблицы clown_info.

Рефлексивный внешний ключ

В таблицу clown_info следует добавить новый столбец с информацией о том, кто является начальником того или иного клоуна. В новом столбце будет храниться идентификатор начальника. Мы назовем его boss_id, как в таблице clown_boss.

В таблице clown_boss столбец boss_id был внешним ключом. При добавлении в clown_info этот столбец все равно остается внешним ключом, хотя и находится в другой таблице. Такие внешние ключи, ссылающиеся на другое поле той же таблицы, называются рефлексивными.

Мы считаем, сто Мистер Снифлз является своим собственным начальником, поэтому у него значение boss_id совпадает с id.

Рефлексивным внешним ключом называется первичный ключ таблицы, используемый в той же таблицы для другой цели.

РЕФЛЕКСИВНЫЙ внешний ключ - первичный ключ таблицы, используемый в той же таблице для других целей.

Соединение таблицы с ней самой

Допустим мы хотим вывести список всех клоунов и их начальников. Список всех клоунов с идентификаторами начальников легко выводится запросом SELECT:

SELECT name, boss_id FROM clown_info;

Но нам нужно пары имен клоуна и его начальника.

Союзы

Существует еще один способ получения объединенных результатов таблиц - так называемые союзы (ключевое слово UNION).

Союз объединяет в одну таблицу результаты двух и более запросов на основании того, что указано в запросе SELECT. Союзы можно трактовать как "пересекающиеся" значения всех запросов.

Грег замечает, что в результате нет дубликатов, однако должности перечислены не по порядку, поэтому он пытается повторить запрос с добавлением условия ORDER BY в каждой команде SELECT.

Как вы думаете, что произошло при выполнении нового запроса?

Мозговой Штурм

Правила союзов в действии

Количество столбцов в командах SELECT должно быть одинаковым. Нельзя выбрать два столбца одной командой и еще один столбец другой.

Мозговой Штурм

Как вы думаете, что произойдет, если объединяемые столбцы относятся к разным типам данных?

UNION ALL

UNION ALL работает точно так же, как UNION, если не считать того, что он возвращает все значения из столбцов - вместо одного экземпляра из каждой группы дубликатов.

До настоящего момента в наших союзах использовались столбцы с совпадающим типом данных. Однако в некоторых ситуациях может возникнуть необходимость в создании союза из разнотипных столбцов.

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

Допустим, в союзе тип INTEGER объединяется с типом VARCHAR. Так как данные VARCHAR нельзя преобразовать в целое число, в полученных результатах тип INTEGER будет преобразован в VARCHAR.

Конец SQL уровней

Теперь ты настоящий SQL  джедай

Практическое задание уровня

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

Теоретический тест уровня SQL-3

Форма для отправки созданных вами SQL запросов

Для перехода на уровень 21, необходимо набрать минимум 12 баллов (60%)  за задания уровня 20.

  • Facebook Social Icon
  • Instagram
  • Vkontakte Social Icon
  • YouTube Social  Icon
  • mail_icon

© 2017 Galaxy QA Academy. All rights are protected.